Difference between revisions of "Sql convert from dds"

From MidrangeWiki
Jump to: navigation, search
 
(5 intermediate revisions by 4 users not shown)
Line 4: Line 4:
  
  
−
SQL described tables work better (faster and more efficiently) than DDS described files.
+
SQL-described tables offer more functionality than DDS-described files.
  
 
How to convert from DDS to SQL without breaking existing programs...
 
How to convert from DDS to SQL without breaking existing programs...
Line 10: Line 10:
 
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.
 
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.
  
−
1. Retrieve the SQL for your physical table using API or iNavigator.
+
#Retrieve the SQL for your physical table using API or iNavigator.
−
  - change the name of the table to match your company standards
+
##change the name of the table to match your company standards
−
    - for example, physical file MSPMP100, new SQL name MSPMP100SQ
+
###for example, physical file MSPMP100, new SQL name MSPMP100SQ
−
  - use old format name for new table  
+
##use old format name for new table  
−
2. Retrieve list of key fields used across the file
+
#Retrieve list of key fields used across the file
−
3. Build indexes using longest number of key fields to shortest
+
#Build indexes using longest number of key fields to shortest
−
4. Rebuild the physical file DDS to be a logical file
+
#Rebuild the physical file DDS to be a logical file
−
5. Change the logical files to point to new physical
+
#Change the logical files to point to new physical
−
  - if logical uses select/omit change to use dynslt
+
##if logical uses select/omit change to use dynslt
−
  - include a index across select/omit fields
+
##include a index across select/omit fields
−
6. 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]
 +
 +
It has two modes of operations. It can write SQL to a source member or return it as a recordset, which creates a source member in QTEMP behind the scenes. Here is an example usage of common parameters:
 +
 +
  CALL QSYS2.GENERATE_SQL(
 +
    'TABLEPF', 'BARLIB', 'TABLE',        -- We want to script out the object TABLEPF from schema (libary) BAR of type table (physical file)
 +
    -- 'QSQLSRC', 'BAR', 'TABLEPF',      -- Uncoment to write this to a source member instead of outputing as a resultset
 +
    STATEMENT_FORMATTING_OPTION => 0,    -- Setting to 1 will cause SEU's syntax highlighting to be confused.
 +
    REPLACE_OPTION => 1,                -- Set to 0 to append to the source member as opposed to overwriting it.
 +
    CREATE_OR_REPLACE_OPTION => 1,      -- If the system supports CREATE OR REPLACE syntax on this object, generate it.
 +
    CONSTRAINT_OPTION => 1,              -- Script out constraints
 +
    DROP_OPTION => 1,                    -- Precede the CREATE statement with a DROP DDL statement.
 +
    NAMING_OPTION => 'SQL',
 +
    QUALIFIED_NAME_OPTION => 0          -- Set to 1 to fully qualify all objects in the generated DDL
 +
);
 +
 +
 +
It should be noted that you may use the % wildcard in the object and library parameter to generate DDL for multiple objects. However, you will need to set the REPLACE_OPTION parameter to 0 in that case or you will only get the last object generated. Also, the source member you intend to write to must exist.
 +
 +
===Troubleshooting===
 +
 +
If QSYS2.GENERATE_SQL() returns an error, its usually not helpful. However the joblog generates helpful messages. You can retrieve the joblog via the usual greenscreen methods or [http://www.rpgpgm.com/2015/06/using-sql-to-get-information-from-job.html select * from table(qsys2.joblog_info('*')) a].
 +
 +
==Categories==
 
[[Category:SQL|DDS to SQL DDL]]
 
[[Category:SQL|DDS to SQL DDL]]
 
[[Category:DDS|DDS to SQL DDL]]
 
[[Category:DDS|DDS to SQL DDL]]
 +
{{stub}}

Latest revision as of 20:32, 8 March 2016

WORK IN PROGRESS - please help by editing this document - include code/specific commands where needed


SQL-described tables offer more functionality 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.

  1. Retrieve the SQL for your physical table using API or iNavigator.
    1. change the name of the table to match your company standards
      1. for example, physical file MSPMP100, new SQL name MSPMP100SQ
    2. use old format name for new table
  2. Retrieve list of key fields used across the file
  3. Build indexes using longest number of key fields to shortest
  4. Rebuild the physical file DDS to be a logical file
  5. Change the logical files to point to new physical
    1. if logical uses select/omit change to use dynslt
    2. include a index across select/omit fields
  6. 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

It has two modes of operations. It can write SQL to a source member or return it as a recordset, which creates a source member in QTEMP behind the scenes. Here is an example usage of common parameters: 

 CALL QSYS2.GENERATE_SQL(
   'TABLEPF', 'BARLIB', 'TABLE',        -- We want to script out the object TABLEPF from schema (libary) BAR of type table (physical file)
   -- 'QSQLSRC', 'BAR', 'TABLEPF',      -- Uncoment to write this to a source member instead of outputing as a resultset
   STATEMENT_FORMATTING_OPTION => 0,    -- Setting to 1 will cause SEU's syntax highlighting to be confused.
   REPLACE_OPTION => 1,                 -- Set to 0 to append to the source member as opposed to overwriting it.
   CREATE_OR_REPLACE_OPTION => 1,       -- If the system supports CREATE OR REPLACE syntax on this object, generate it.
   CONSTRAINT_OPTION => 1,              -- Script out constraints
   DROP_OPTION => 1,                    -- Precede the CREATE statement with a DROP DDL statement.
   NAMING_OPTION => 'SQL',
   QUALIFIED_NAME_OPTION => 0           -- Set to 1 to fully qualify all objects in the generated DDL
);


It should be noted that you may use the % wildcard in the object and library parameter to generate DDL for multiple objects. However, you will need to set the REPLACE_OPTION parameter to 0 in that case or you will only get the last object generated. Also, the source member you intend to write to must exist.

Troubleshooting

If QSYS2.GENERATE_SQL() returns an error, its usually not helpful. However the joblog generates helpful messages. You can retrieve the joblog via the usual greenscreen methods or select * from table(qsys2.joblog_info('*')) a.

Categories

This article is a stub. You can help by editing it.

Retrieved from "https://wiki.midrange.com/index.php?title=Sql_convert_from_dds&oldid=12024"