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.

3 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

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.