In my last post I wrote about how to convert DDS files to SQL DDL using the QSQGNDDL API, you can read it here, and I mentioned that in IBM i 7.2 and 7.1 TR9 there was an alternative, the GENERATE_SQL SQL procedure.
Having used both I prefer GENERATE_SQL as it is simpler to use, just one statement in STRSQL, and it has the ability to use wild cards for mass conversions. Many of its parameters are in English, which makes it easier for others to understand my code. The has six mandatory parameters:
- DDS file name
- Library the file is in
- The input SQL object type
- Source file for DDL member
- Library containing source file
- Source member
All of the others are optional, and I will describe two I recommend you should use.
Before running the procedure you need to ensure that the output source member does exist as the procedure does not create a source member.
I am going to use two of the files I used last week:
- TESTFILE – DDS physical file
- TESTFILEL – DDS logical file built "over" TESTFILE
As I mentioned I can run the procedure in STRSQL, the example I am converting the DDS file TESTFILE in MYLIB and creating the DDL source in the member TESTFILE in the source file OTHERSRC in MYLIB.
call qsys2/generate_sql('TESTFILE','MYLIB','TABLE',
'OTHERSRC','MYLIB','TESTFILE',
replace_option => '1')
|
I am using the replace_option parameter, with '1', to replace the existing contents of the TESTFILE member. It is not keying error I do need to have => rather than just =. If I did not want to clear the member I could either use '0' or just not use this parameter.
What does this look like? First the source code for the DDS file, followed by the DDL source code for the equivalent as a SQL table:
| DDS |
A UNIQUE
A R TESTFILER
A FLD001 1A TEXT('Alphanumeric field 1')
A COLHDG('Alpha' '1')
A FLD002 3P 0 TEXT('Packed numeric field 2')
A COLHDG('Pack' '2')
A FLD003 5S 2 TEXT('Signed numeric field 3')
A COLHDG('Sign' '3')
A FLD004 L DATFMT(*USA)
A TEXT('Date field in *USA format 4')
A COLHDG('Date' '4')
A FLD005 T TEXT('Time field 5')
A COLHDG('Time' '5')
A FLD006 Z TEXT('Timestamp field 6')
A COLHDG('Timestamp' '6')
A FLD007 1A VALUES('Y' 'N')
A TEXT('Alphanumeric field with value-
A s 7')
A COLHDG('Alpha' '7')
A FLD008 200A VARLEN
A TEXT('Alphanumeric field with varyi-
A ng length 8')
A COLHDG('Alpha' '8')
A FLD009 30P 0 TEXT('Packed numeric field 9')
A COLHDG('Pack' '9')
A FLD010 R REFFLD(REF001 TESTFREF)
A K FLD001
|
| SQL DDL generated for Table |
-- Generate SQL -- Version: V7R2M0 140418 -- Generated on: DD/DD/DD TT:TT:TT -- Relational Database: XXXXXXXX -- Standards Option: DB2 for i CREATE TABLE MYLIB.TESTFILE ( FLD001 CHAR(1) CCSID 273 NOT NULL DEFAULT '' , FLD002 DECIMAL(3, 0) NOT NULL DEFAULT 0 , FLD003 NUMERIC(5, 2) NOT NULL DEFAULT 0 , FLD004 DATE NOT NULL DEFAULT CURRENT_DATE , -- SQL150D 10 DATFMT in column FLD004 ignored. FLD005 TIME NOT NULL DEFAULT CURRENT_TIME , FLD006 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , FLD007 CHAR(1) CCSID 273 NOT NULL DEFAULT '' , -- SQL150D 10 VALUES in column FLD007 ignored. FLD008 VARCHAR(200) CCSID 273 NOT NULL DEFAULT '' , FLD009 DECIMAL(30, 0) NOT NULL DEFAULT 0 , FLD010 CHAR(10) CCSID 273 NOT NULL DEFAULT '' , PRIMARY KEY( FLD001 ) ) RCDFMT TESTFILER ; LABEL ON COLUMN MYLIB.TESTFILE ( FLD001 IS 'Alpha 1' , FLD002 IS 'Pack 2' , FLD003 IS 'Sign 3' , FLD004 IS 'Date 4' , FLD005 IS 'Time 5' , FLD006 IS 'Timestamp 6' , FLD007 IS 'Alpha 7' , FLD008 IS 'Alpha 8' , FLD009 IS 'Pack 9' , FLD010 IS 'Ref 1' ) ; LABEL ON COLUMN MYLIB.TESTFILE ( FLD001 TEXT IS 'Alphanumeric field 1' , FLD002 TEXT IS 'Packed numeric field 2' , FLD003 TEXT IS 'Signed numeric field 3' , FLD004 TEXT IS 'Date field in *USA format 4' , FLD005 TEXT IS 'Time field 5' , FLD006 TEXT IS 'Timestamp field 6' , FLD007 TEXT IS 'Alphanumeric field with values 7' , FLD008 TEXT IS 'Alphanumeric field with varying length 8' , FLD009 TEXT IS 'Packed numeric field 9' , FLD010 TEXT IS 'Reference field 1' ) ; GRANT ALTER , DELETE , INDEX , INSERT , REFERENCES , SELECT , UPDATE ON MYLIB.TESTFILE TO RPGPGM WITH GRANT OPTION ; |
The conversion does not convert the DATFMT keyword on FLD004 and the VALUES keyword on FLD007.
The DDS source for the logical file, TESTFILEL, is straight forward:
A R TESTFILER PFILE(TESTFILE) A K FLD002 A K FLD001 |
SQL regards DDS logical file as Views, therefore, the SQL input object type will be 'VIEW'. If I used 'INDEX' as the SQL object type the procedure fails.
There are three choices for output:
- Index
- View
- View with an additional Index
I would think we would all want the output to be an index. The parameters to generate the Index would be:
call qsys2/generate_sql('TESTFILEL','MYLIB','VIEW',
'OTHERSRC','MYLIB','TESTFILELI',
index_instead_of_view_option => '1',
replace_option => '1') ;
|
I have to use the index_instead_of_view_option to produce an Index. If I do not I generate a View. The Index generated looks like:
-- Generate SQL -- Version: V7R2M0 140418 -- Generated on: DD/DD/DD TT:TT:TT -- Relational Database: XXXXXXXX -- Standards Option: DB2 for i CREATE INDEX MYLIB.TESTFILEL ON MYLIB.TESTFILE ( FLD002 ASC , FLD001 ASC ) RCDFMT TESTFILER ; |
If I wanted to generate DDL for a View instead of an Index the call to the procedure would look like:
call qsys2/generate_sql('TESTFILEL','MYLIB','VIEW',
'OTHERSRC','MYLIB','TESTFILELV',
replace_option => '1') ;
|
The View's DDL would look like:
-- Generate SQL -- Version: V7R2M0 140418 -- Generated on: DD/DD/DD TT:TT:TT -- Relational Database: XXXXXXXX -- Standards Option: DB2 for i CREATE VIEW MYLIB.TESTFILEL ( -- SQL1506 30 Key or attribute for TESTFILEL in MYLIB ignored. FLD001 , FLD002 , FLD003 , FLD004 , FLD005 , FLD006 , FLD007 , FLD008 , FLD009 , FLD010 ) AS SELECT FLD001 , FLD002 , FLD003 , FLD004 , -- SQL150D 10 DATFMT in column FLD004 ignored. FLD005 , FLD006 , FLD007 , -- SQL150D 10 VALUES in column FLD007 ignored. FLD008 , FLD009 , FLD010 FROM MYLIB.TESTFILE RCDFMT TESTFILER ; LABEL ON COLUMN MYLIB.TESTFILEL ( FLD001 IS 'Alpha 1' , FLD002 IS 'Pack 2' , FLD003 IS 'Sign 3' , FLD004 IS 'Date 4' , FLD005 IS 'Time 5' , FLD006 IS 'Timestamp 6' , FLD007 IS 'Alpha 7' , FLD008 IS 'Alpha 8' , FLD009 IS 'Pack 9' , FLD010 IS 'Ref 1' ) ; LABEL ON COLUMN MYLIB.TESTFILEL ( FLD001 TEXT IS 'Alphanumeric field 1' , FLD002 TEXT IS 'Packed numeric field 2' , FLD003 TEXT IS 'Signed numeric field 3' , FLD004 TEXT IS 'Date field in *USA format 4' , FLD005 TEXT IS 'Time field 5' , FLD006 TEXT IS 'Timestamp field 6' , FLD007 TEXT IS 'Alphanumeric field with values 7' , FLD008 TEXT IS 'Alphanumeric field with varying length 8' , FLD009 TEXT IS 'Packed numeric field 9' , FLD010 TEXT IS 'Reference field 1' ) ; GRANT ALTER , DELETE , INSERT , REFERENCES , SELECT , UPDATE ON MYLIB.TESTFILEL TO RPGPGM WITH GRANT OPTION ; |
The third option was the View with an additional Index. The call to the procedure would look like:
call qsys2/generate_sql('TESTFILEL','MYLIB','VIEW',
'OTHERSRC','MYLIB','TESTFILELU',
additional_index_option => '1',
replace_option => '1') ;
|
The additional_index_option is the parameter to generate both. The output would look like:
-- Generate SQL -- Version: V7R2M0 140418 -- Generated on: DD/DD/DD TT:TT:TT -- Relational Database: XXXXXXXX -- Standards Option: DB2 for i CREATE VIEW MYLIB.TESTFILEL ( -- SQL1506 30 Key or attribute for TESTFILEL in MYLIB ignored. FLD001 , FLD002 , FLD003 , FLD004 , FLD005 , FLD006 , FLD007 , FLD008 , FLD009 , FLD010 ) AS SELECT FLD001 , FLD002 , FLD003 , FLD004 , -- SQL150D 10 DATFMT in column FLD004 ignored. FLD005 , FLD006 , FLD007 , -- SQL150D 10 VALUES in column FLD007 ignored. FLD008 , FLD009 , FLD010 FROM MYLIB.TESTFILE RCDFMT TESTFILER ; LABEL ON COLUMN MYLIB.TESTFILEL ( FLD001 IS 'Alpha 1' , FLD002 IS 'Pack 2' , FLD003 IS 'Sign 3' , FLD004 IS 'Date 4' , FLD005 IS 'Time 5' , FLD006 IS 'Timestamp 6' , FLD007 IS 'Alpha 7' , FLD008 IS 'Alpha 8' , FLD009 IS 'Pack 9' , FLD010 IS 'Ref 1' ) ; LABEL ON COLUMN MYLIB.TESTFILEL ( FLD001 TEXT IS 'Alphanumeric field 1' , FLD002 TEXT IS 'Packed numeric field 2' , FLD003 TEXT IS 'Signed numeric field 3' , FLD004 TEXT IS 'Date field in *USA format 4' , FLD005 TEXT IS 'Time field 5' , FLD006 TEXT IS 'Timestamp field 6' , FLD007 TEXT IS 'Alphanumeric field with values 7' , FLD008 TEXT IS 'Alphanumeric field with varying length 8' , FLD009 TEXT IS 'Packed numeric field 9' , FLD010 TEXT IS 'Reference field 1' ) ; GRANT ALTER , DELETE , INSERT , REFERENCES , SELECT , UPDATE ON MYLIB.TESTFILEL TO RPGPGM WITH GRANT OPTION ; CREATE INDEX MYLIB.TESTFILEL_QSQGNDDL_00001 ON MYLIB.TESTFILE ( FLD002 ASC , FLD001 ASC ) ; |
The part that generates the additional Index is the last statement in the DDL above.
As I mentioned at the start of this post one advantage of the GENERATE_SQL procedure is that I can use wildcards. The wildcard symbol is the percent ( % ). If I use just the wildcard symbol then every file is selected. If I wanted just file start with ‘AP’ I would use the wildcard ‘AP%’.
If I wanted to convert all of the physical files in a library I would just use the following:
call qsys2/generate_sql('%','MYLIB','TABLE',
'OTHERSRC','MYLIB','PF_FILES',
replace_option => '0') ;
|
I can only have one output source member, therefore, I have to have the replace_option to be zero so not to clear the member before each file’s output is written to the source member PF_FILES. As I have given TABLE as the object type only physical files are processed.
If I wanted to just convert the logical files that start with ‘AP’ to Indexes the statement would look like:
call qsys2/generate_sql('AP%','MYLIB','VIEW',
'OTHERSRC','MYLIB','LF_FILES',
index_instead_of_view_option => '1',
replace_option => '0') ;
|
All of the DDL for the Indexes are written to the member LF_FILES.
You can learn more about the GENERATE_SQL procedure on the IBM website here.
This article was written for IBM i 7.2, and it should work for release 7.1 TR9 as well.
Thanks for the article Simon. This certainly makes it easier to transition to DDL. I noticed that the source always contains the schema. Is there a way to control which schema will get the table outside of the source file so it can easily be created in development, QA and production without editing the source?
ReplyDeleteI have been tasked with creating the DDL source as individual members, instead of just one source member. Has anyone tried to use GENERATE_SQL in an RPGLE program, passing in DDS source names? On 1st inspection, I wonder how to handle the REPLACE_OPTION => '1' as a parameter. I'm still researching but any help would be appreciated.
ReplyDeleteVery useful..
ReplyDeleteSimon, thanks as usual for all your contributions, and here is the thing. I need to convert the DDS to DDL, but in the process I need to rename the column name by concatenating column headings, so the table may look a modern one, that contains readable column name. Here is an example:
ReplyDeleteTable Name:MYTABLE
Columns:fld1 char(2) column heading "Long Field Name 1"
When generating output, I would want it as "Long_Field_Name_1" for fld1. I can write a program to read DSPFFD output and generate DDL on a table that has the same attributes of a source file and then run those statements.
Is there an easier way?
If your DDS file has ALIAS then it will use those for the long field names.
DeleteYou could write a program to create the alias name in a DDS source member.
Yes that was my next option. Read DDS source file as a table, scan for column heading, and if found concatenate them by picking them from multiple records, and generate an additional entry for alias.
DeleteI was hoping there would be a simple solution. One thing in my mind was to read the DSPFFD output and rename the column. Much easier. How the ALTER TABLE ... RENAME COLUMN ... is not there in 7.1.
If you know which release it is coming, or a TR has it, I will be much grateful.
If you are on 7.1 I would go to TR11, the last TR for that release.
DeleteWill you Power server support releases 7.2 or 7.3?
A move to the latest releases and TRs would give you so much "cool" stuff.
Just found this article and it has been very useful, thanks! I'm trying to convert a DDS file that has a select in it. The DDL that is generated is fine and here is the sql for the new logical file:
ReplyDeleteCREATE INDEX LEP.PIDL02_QSQGNDDL_00001
ON LEP.PID ( PISKU# ASC )
WHERE
PISTAT = 'A' ;
However, I'm getting an unknown error on the index where it cannot be read by a RPG program or viewed with QRY or SQL. Any help would be appreciated.
Yes that is the problem with Indexes. You cannot use Query, STRSQL, or RPG to see their contents.
ReplyDeletePersonally I build Views instead of Indexes. By all means I don't have the key within the View, but I can do a whole lot more with it. And give the key in the Fetch statement.
We are at v7r1 TR 11 ... generate_sql/qsys2 is not an option.
ReplyDeleteI feel for you. Fortunately there is an API that does the same thing, just not as elegantly. You will find information about how to use it here.
DeleteBruno Kuper With "IBM i Access Client" or "System i Navigator", you have functions to generate enSQL DLL statements from the PF or LF file.
ReplyDeleteWith IBM i Access Client: Click on Database, Select Collection and, Right click on Table and choose option "Generation of SQL statements"
You can save the source in a source physical file, in the IFS, or open the source generated with the SQL Queryer
This was the first way I found to convert DDS files to their DDL equivalents. I am sure it uses the same logic that this procedure does.
DeleteThe reason I no longer use this approach is I have to save what is generated to a source member. If I use the procedure a new member is generated with the DDL within it for me.
Can we use fetch in SQLRPGLE for a INDEX file?
ReplyDeleteYou should always build your SQL statements over the phyiscal file or table. Let the Db2 for i make the determination as what is the best index to use.
DeleteUnfortunately it won't support logical file with multiple record formats
ReplyDelete