Useful new options added to generate SQL source for object

I have already written about these two SQL procedures:

• GENERATE_SQL
• GENERATE_SQL_OBJECTS

Both can retrieve the SQL statement(s) from a SQL object. With the release of IBM i 7.5 and 7.4 TR6 new parameters and values have been added to them both. I have to admit I do use GENERATE_SQL far more often than the other, therefore, the examples I give will be using this SQL Procedure.

Most of time I use GENERATE_SQL I am retrieving the DDL source from Tables, Indexes, Views, and Physical files. In this example I am going to use a different type of SQL object, a variable.

This is my variable:

01 CREATE VARIABLE MYLIB.TEST_VARIABLE 02 FOR SYSTEM NAME "TESTVAR" 03 VARCHAR(30) DEFAULT 'No value' ; 04 COMMENT ON VARIABLE MYLIB.TEST_VARIABLE IS 'My test variable' ;

Line 2: As the variable's name is longer than ten characters I have given it a system name too.

Line 3: The variable is variable length character of up to 30, and I have given it a default value of "No value". Normally I would make the default as null.

Line 4: I have added a comment to the variable.

I can see what my Variable looks like using the SYSVARIABLES View:

01 SELECT SYSTEM_VAR_SCHEMA,VARIABLE_NAME,SYSTEM_VAR_NAME, 02 DATA_TYPE,LENGTH,DEFAULT,LONG_COMMENT 03 FROM QSYS2.SYSVARIABLES 04 WHERE SYSTEM_VAR_SCHEMA = 'MYLIB' 05 AND VARIABLE_NAME = 'TEST_VARIABLE'

The results are:

SYSTEM_VAR_ SYSTEM_ SCHEMA VARIABLE_NAME VAR_NAME DATA_TYPE LENGTH DEFAULT LONG_COMMENT ----------- ------------- -------- --------- ------ ---------- ---------------- MYLIB TEST_VARIABLE TESTVAR VARCHAR 30 'No value' My test variable

The following code will retrieve the SQL statement I used to create the Variable:

01 CL:ADDPFM FILE(DEVSRC) MBR(TESTVAR); 02 CALL QSYS2.GENERATE_SQL 03 (DATABASE_OBJECT_NAME => 'TESTVAR', 04 DATABASE_OBJECT_LIBRARY_NAME => 'MYLIB', 05 DATABASE_OBJECT_TYPE => 'VARIABLE', 06 DATABASE_SOURCE_FILE_NAME => 'DEVSRC', 07 DATABASE_SOURCE_FILE_LIBRARY_NAME => 'MYLIB', 08 DATABASE_SOURCE_FILE_MEMBER => 'TESTVAR', 09 REPLACE_OPTION => '1' , 10 CREATE_OR_REPLACE_OPTION => '1', 11 COMMENT_OPTION => '2', 12 SYSTEM_NAME_OPTION => '1') ;

Line 1: I created a new source member in the source file DEVSRC to contain the statements for the variable. By starting the line with "CL:" I can execute the ADDPFM command within my Run SQL Scripts session.

Line 2: Call of the SQL procedure GENERATE_SQL.

Lines 3 – 5: Here is where I give the name of the SQL object, the library it is within, and the object type I want to retrieve the statement(s) from.

Lines 6 – 8: The name of the source file, source file library, and member the SQL statement(s) will be inserted into.

Line 9: REPLACE_OPTION of '1' means that the statement(s) generated replaces the existing contents of the member.

Line 10: As the CREATE_OR_REPLACE_OPTION is '1' the create statement for the variable will be CREATE OR REPLACE VARIABLE. If this option was '0' then it would be just CREATE VARIABLE.

Line 11: COMMENT_OPTION is '2' If no comment statement was used when creating the SQL object then one will be created for it.

Line 12: As SYSTEM_NAME_OPTION is '1' then a system name will be created for the Variable.

The results are written into the source member and look like:

-- Generate SQL -- Version: V7R5M0 220415 -- Generated on: DD-DD-DD TT:TT:TT -- Relational Database: DEV750 -- Standards Option: Db2 for i SET PATH *LIBL ; CREATE OR REPLACE VARIABLE MYLIB.TEST_VARIABLE FOR SYSTEM NAME TESTVAR VARCHAR( 30) DEFAULT 'No value' ; COMMENT ON VARIABLE MYLIB.TEST_VARIABLE IS 'My test variable' ; GRANT ALTER , READ , WRITE ON VARIABLE MYLIB.TEST_VARIABLE TO RPGPGM WITH GRANT OPTION ;

As I said before the CREATE has become CREATE OR REPLACE, and there is a COMMENT statement.

If I change the following parameters:

10 CREATE_OR_REPLACE_OPTION => '0', 11 COMMENT_OPTION => '0', 12 SYSTEM_NAME_OPTION => '0') ;

The resulting source member changes too:

SET PATH *LIBL ; CREATE VARIABLE RPGPGM1.TEST_VARIABLE VARCHAR( 30) DEFAULT 'No value' ; GRANT ALTER , READ , WRITE ON VARIABLE RPGPGM1.TEST_VARIABLE TO RPGPGM WITH GRANT OPTION ;

Changes:

• CREATE statement:
  • OR REPLACE is missing
  • No system name
• COMMENT statement:
  • Absent

This second example recreates the SQL statements that were used to create a Table:

01 CREATE OR REPLACE TABLE MYLIB.LONG_TABLE_NAME 02 FOR SYSTEM NAME "TESTTABLE1" 03 (COLUMN_1 CHAR(2)) ; 04 LABEL ON TABLE TESTTABLE1 IS 'TEST TABLE 1' ;

When I use the GENERATE_SQL procedure:

01 CALL QSYS2.GENERATE_SQL 02 (DATABASE_OBJECT_NAME => 'TESTTABLE1', 03 DATABASE_OBJECT_LIBRARY_NAME => 'MYLIB', 04 DATABASE_OBJECT_TYPE => 'TABLE', 05 DATABASE_SOURCE_FILE_NAME => 'DEVSRC', 06 DATABASE_SOURCE_FILE_LIBRARY_NAME => 'MYLIB', 07 DATABASE_SOURCE_FILE_MEMBER => 'TESTTABLE2', 08 REPLACE_OPTION => '1' , 09 CREATE_OR_REPLACE_OPTION => '1', 10 COMMENT_OPTION => '2', 11 SYSTEM_NAME_OPTION => '1') ;

Which gives the following in the source member:

-- Generate SQL -- Version: V7R5M0 -- Generated on: DD/DD/DD TT:TT:TT -- Relational Database: DEV750 -- Standards Option: Db2 for i CREATE OR REPLACE TABLE MYLIB.LONG_TABLE_NAME FOR SYSTEM NAME TESTTABLE1 ( COLUMN_1 CHAR(2) CCSID 37 DEFAULT NULL ) RCDFMT TESTTABLE1 ; COMMENT ON TABLE MYLIB.LONG_TABLE_NAME IS 'TEST TABLE 1' ; LABEL ON TABLE MYLIB.LONG_TABLE_NAME IS 'TEST TABLE 1' ; GRANT DELETE , INSERT , SELECT , UPDATE ON MYLIB.LONG_TABLE_NAME TO PUBLIC ; GRANT ALTER , DELETE , INDEX , INSERT , REFERENCES , SELECT , UPDATE ON MYLIB.LONG_TABLE_NAME TO GRPPRF01 WITH GRANT OPTION ;

The statement to create the Table is now CREATE OR REPLACE. It also has a system name for the Table.

A comment statement was generated using the same text as the label statement does.

They are not big changes, but make the generated statements better than they were before the enhancements were made.

 

You can learn more about this from the IBM website:

• GENERATE_SQL SQL procedure
• GENERATE_SQL_OBJECTS SQL procedure

 

This article was written for IBM i 7.5 and 7.4 TR6.