Wednesday, November 9, 2022

Useful new options added to generate SQL source for object

I have already written about these two SQL procedures:

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:

 

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

No comments:

Post a Comment

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.