Difference between revisions of "Sql convert from dds"
Starbuck5250 (talk | contribs) m (+categories heading, stub category) |
m |
||
Line 21: | Line 21: | ||
##include a index across select/omit fields | ##include a index across select/omit fields | ||
#Compile all logical files (including the "old physical") | #Compile all logical files (including the "old physical") | ||
+ | |||
+ | |||
+ | == Stored Procedure GENERATE_SQL() == | ||
+ | |||
+ | Beginning with Release 7.1 TR8 (and Release 7.2) the '''GENERATE_SQL()'''stored procedure is integrated in the '''QSYS2''' schema. | ||
+ | This procedure allows DDS sources to be converted into SQL and stored either as stream file or in a source physical file member. | ||
+ | Detailed Information can be found under the following Link: | ||
+ | [https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.GENERATE_SQL%28%29%20procedure QSYS2.GENERATE_SQL() procedure] | ||
==Categories== | ==Categories== |
Revision as of 05:21, 26 September 2014
WORK IN PROGRESS - please help by editing this document - include code/specific commands where needed
SQL described tables work better (faster and more efficiently) than DDS described files.
How to convert from DDS to SQL without breaking existing programs...
Big picture...create a new SQL table to hold the data, build indexes across this new table, rebuild the physical file DDS as a logical file, change the logicals to point at new physical.
- Retrieve the SQL for your physical table using API or iNavigator.
- change the name of the table to match your company standards
- for example, physical file MSPMP100, new SQL name MSPMP100SQ
- use old format name for new table
- change the name of the table to match your company standards
- Retrieve list of key fields used across the file
- Build indexes using longest number of key fields to shortest
- Rebuild the physical file DDS to be a logical file
- Change the logical files to point to new physical
- if logical uses select/omit change to use dynslt
- include a index across select/omit fields
- Compile all logical files (including the "old physical")
Stored Procedure GENERATE_SQL()
Beginning with Release 7.1 TR8 (and Release 7.2) the GENERATE_SQL()stored procedure is integrated in the QSYS2 schema. This procedure allows DDS sources to be converted into SQL and stored either as stream file or in a source physical file member. Detailed Information can be found under the following Link: QSYS2.GENERATE_SQL() procedure
Categories
This article is a stub. You can help by editing it.