Wednesday, May 13, 2015

Converting DDS files to SQL DDL using SQL

file dds to sql ddl conversion using sql generate_sql

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:

  1. DDS file name
  2. Library the file is in
  3. The input SQL object type
  4. Source file for DDL member
  5. Library containing source file
  6. 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:

  1. Index
  2. View
  3. 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.

16 comments:

  1. 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?

    ReplyDelete
  2. I 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.

    ReplyDelete
  3. Simon, 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:
    Table 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?

    ReplyDelete
    Replies
    1. If your DDS file has ALIAS then it will use those for the long field names.
      You could write a program to create the alias name in a DDS source member.

      Delete
    2. 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.

      I 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.

      Delete
    3. If you are on 7.1 I would go to TR11, the last TR for that release.

      Will 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.

      Delete
  4. 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:

    CREATE 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.

    ReplyDelete
  5. Yes that is the problem with Indexes. You cannot use Query, STRSQL, or RPG to see their contents.

    Personally 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.

    ReplyDelete
  6. We are at v7r1 TR 11 ... generate_sql/qsys2 is not an option.

    ReplyDelete
    Replies
    1. I 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.

      Delete
  7. Bruno Kuper With "IBM i Access Client" or "System i Navigator", you have functions to generate enSQL DLL statements from the PF or LF file.
    With 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

    ReplyDelete
    Replies
    1. 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.

      The 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.

      Delete
  8. Can we use fetch in SQLRPGLE for a INDEX file?

    ReplyDelete
    Replies
    1. You 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.

      Delete
  9. Unfortunately it won't support logical file with multiple record formats

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.