GENERATE_SQL procedure

The GENERATE_SQL procedure generates the SQL data definition language statements required to recreate a database object. The results are returned in the specified database source file member, Start of changesource stream fileEnd of change, or as a result set.

The database source file member Start of changeor integrated file system (IFS) stream fileEnd of change will contain the generated SQL statements. If the output source file is QTEMP/Q_GENSQL with a member name of Q_GENSQL, the source file is returned as a result set as well.

Authorization:

When writing to a database source physical file the user must have:
  • *EXECUTE to the library containing the source physical file
  • To add a member:
    • *OBJOPR and *ADD to the source physical file
  • To replace a member:
    • *OBJOPR, *DELETE, *ADD, and either *OBJMGT or *OBJALTER to the source physical file
When writing to an IFS stream file:
  • Execute (*X) data authority to each directory preceding the stream file being written and
  • When the stream file exists:
    • Write (*W) data authority to the stream file
  • When the stream file does not exist:
    • Write and Execute (*WX) authority to the parent directory of the stream file
To generate source for an object type, the following authorities are needed:
  • TABLE, VIEW, CONSTRAINT, or TRIGGER
    • *EXECUTE and *OBJOPR to the library, and
    • *OBJOPR to the *FILE object
  • INDEX
    • *EXECUTE and *OBJOPR to the library, and
    • *OBJOPR to the *FILE object, and
    • *OBJOPR to the base *FILE object
  • MASK or PERMISSION
    • *EXECUTE and *OBJOPR to the library, and at least one of the following:
      • *OBJOPR to the *FILE object
      • QIBM_DB_SECADM function usage
  • ALIAS
    • *EXECUTE and *OBJOPR to the library, and
    • *OBJOPR to the *FILE object
  • FUNCTION or PROCEDURE
    • *OBJOPR to the library
  • TYPE
    • *EXECUTE and *OBJOPR to the library, and
    • *OBJOPR to the *SQLUDT object
  • SCHEMA
    • *OBJOPR and either *READ or *EXECUTE to the *LIB object
  • SEQUENCE
    • *EXECUTE and *OBJOPR to the library, and
    • *USE to the *DTAARA object
  • VARIABLE
    • *EXECUTE and *OBJOPR to the library, and
    • *OBJOPR to the *SRVPGM object
  • XSR
    • *EXECUTE and *OBJOPR to the library, and
    • *OBJOPR to the *SQLXSR object
Read syntax diagramSkip visual syntax diagramGENERATE_SQL( DATABASE_OBJECT_NAME =>  database-object-name , DATABASE_OBJECT_LIBRARY_NAME =>  database-object-library-name , DATABASE_OBJECT_TYPE =>  database-object-type ,DATABASE_SOURCE_FILE_NAME => database-source-file-name,DATABASE_SOURCE_FILE_LIBRARY_NAME => database-source-file-library-name,DATABASE_SOURCE_FILE_MEMBER => database-source-file-member,SEVERITY_LEVEL => severity-level,REPLACE_OPTION => replace-option,STATEMENT_FORMATTING_OPTION => statement-formatting-option,DATE_FORMAT => date-format,DATE_SEPARATOR => date-separator,TIME_FORMAT => time-format,TIME_SEPARATOR => time-separator,NAMING_OPTION => naming-option,DECIMAL_POINT => decimal-point,STANDARDS_OPTION => standards-option,DROP_OPTION => drop-option,MESSAGE_LEVEL => message-level,COMMENT_OPTION => comment-option,LABEL_OPTION => label-option,HEADER_OPTION => header-option,TRIGGER_OPTION => trigger-option,CONSTRAINT_OPTION => constraint-option,SYSTEM_NAME_OPTION => system-name-option,PRIVILEGES_OPTION => privileges-option,CCSID_OPTION => ccsid-option,CREATE_OR_REPLACE_OPTION => create-or-replace-option,OBFUSCATE_OPTION => obfuscate-option,ACTIVATE_ROW_AND_COLUMN_ACCESS_CONTROL_OPTION => activate-access-control-option,MASK_AND_PERMISSION_OPTION => mask-and-permission-option,QUALIFIED_NAME_OPTION => qualified-name-option,ADDITIONAL_INDEX_OPTION => additional-index-option,INDEX_INSTEAD_OF_VIEW_OPTION => index-instead-of-view-option,TEMPORAL_OPTION => temporal-option,SOURCE_STREAM_FILE => source-stream-file,SOURCE_STREAM_FILE_END_OF_LINE => source-stream-file-end-of-line,SOURCE_STREAM_FILE_CCSID => source-stream-file-ccsid)
The schema is QSYS2.
database-object-name
A character or graphic string expression that identifies the name of the database object for which DDL will be generated. Either the SQL name or the system name may be specified. The name is case sensitive. Delimiters must not be specified. For example, a file with a name of "abc" must be specified as abc. A file with a name of ABC must be specified in upper case. If the object type is a FUNCTION or PROCEDURE, this name must be the specific name of the function or procedure. If TABLE or VIEW is specified for the object type, the object name may identify an alias. In this case, the object that the alias points to will be generated. A CREATE ALIAS statement will be generated only if ALIAS is specified for the object type.
A '%' wildcard character can be used to select multiple objects of the same type. For example, a name of 'TSTV%' will process all objects of database-object-type that start with the characters 'TSTV'
database-object-library-name
A character or graphic string expression that identifies the name of the library containing the object for which DDL will be generated. Either the SQL name or the system name may be specified. The name is case sensitive. Delimiters must not be specified. This name is ignored if the specified object type is SCHEMA. A '%' wildcard character can be used to select multiple libraries.
database-object-type
A character or graphic string expression that identifies the type of the database object or object attribute for which DDL is generated. You can use these special values for the object type:
ALIAS
The object is an SQL alias.
CONSTRAINT
The object attribute is a constraint.
FUNCTION
The object is an SQL function.
INDEX
The object is an SQL index.
MASK
The object is an SQL column mask.
PERMISSION
The object is an SQL row permission.
PROCEDURE
The object is an SQL procedure.
SCHEMA
The object is an SQL schema.
SEQUENCE
The object is an SQL sequence.
TABLE
The object is an SQL table or physical file.
TRIGGER
The object attribute is a trigger.
TYPE
The object is an SQL type.
VARIABLE
The object is an SQL global variable.
VIEW
The object is an SQL view or logical file.
XSR
The object is an XML schema repository object.
database-source-file-name
A character or graphic string expression that identifies the name of the source file that contains the SQL statements generated by the procedure. The name must be a valid system name. The name is case sensitive. If delimiters are required for the name to be valid, they must be specified. For example, a file with a name of "abc" must be specified with the surrounding quotes. A file with a name of ABC must be specified in upper case.
The record length of the specified source file must be greater than or equal to 92.
Start of changeCan contain the following special value:
*STMF
The output is to be written to source-stream-file.
End of change
If database-source-file-name is not specified, Q_GENSQL will be used.
database-source-file-library-name
A character or graphic string expression that identifies the name of the library containing the source file that contains the SQL statements generated by the procedure. The name must be a valid system name. The name is case sensitive. If delimiters are required for the name to be valid, they must be specified. You can use these special values for the library name:
*CURLIB
The job's current library
*LIBL
The library list
If database-source-file-library-name is not specified, QTEMP will be used.
database-source-file-member
A character or graphic string expression that identifies the name of the source file member that contains the SQL statements generated by the procedure. The name must be a valid system name. The name is case sensitive. If delimiters are required for the name to be valid, they must be specified. You can use these special values for the member name:
*FIRST
The first database physical file member found.
*LAST
The last database physical file member found.

If values are provided for database-source-file-library-name, database-source-file-name, and database-source-file-member the object must exist.

If database-source-file-member is not specified, Q_GENSQL will be used.

severity-level
The severity level at which the operation fails. If errors occur that have a severity level greater than this value, the operation ends. The valid values are in the range 0 through 39 inclusive. Any severity 40 error will cause the procedure to fail.
If severity-level is not specified, 39 will be used.
replace-option
The replace option for the database source file member Start of changeor source stream fileEnd of change. The valid values are:
0
The resulting SQL statements are appended to the end of the database source file member Start of changeor source stream fileEnd of change.
1
The database source file member Start of changeor source stream fileEnd of change is cleared prior to adding the resulting SQL statements. If this option is chosen, the clear might happen even if an error is returned from the procedure.
If replace-option is not specified, 1 will be used.
statement-formatting-option
The formatting option used in the generated SQL statements. The valid values are:
0
No additional formatting characters are added to the generated SQL statements.
1
Additional end-of-line characters and tab characters are added to the generated SQL statements.
If statement-formatting-option is not specified, 1 will be used.
date-format
The date format used for date constants in a generated SQL CREATE TABLE statement. The date format may not apply to date constants that are in ISO, EUR, USA, or JIS format in a CREATE VIEW, CREATE TRIGGER, CREATE FUNCTION, CREATE PROCEDURE, CREATE MASK, or CREATE PERMISSION.
If date-format is not specified, ISO will be used.
date-separator
The date separator used for date constants in a generated SQL CREATE TABLE statement. The date separator may not apply to date constants that are in ISO, EUR, USA, or JIS format in a CREATE VIEW, CREATE TRIGGER, CREATE FUNCTION, CREATE PROCEDURE, CREATE MASK, or CREATE PERMISSION statement.
If date-separator is not specified, - will be used.
time-format
The format used for time constants in a generated SQL CREATE TABLE statement. The time format may not apply to time constants that are in ISO, EUR, USA, or JIS format in a CREATE VIEW, CREATE TRIGGER, CREATE FUNCTION, CREATE PROCEDURE, CREATE MASK, or CREATE PERMISSION statement.
If time-format is not specified, ISO will be used.
time-separator
The time separator used for time constants in a generated SQL CREATE TABLE statement. The time separator may not apply to time constants that are in ISO, EUR, USA, or JIS format in a CREATE VIEW, CREATE TRIGGER, CREATE FUNCTION, CREATE PROCEDURE, CREATE MASK, or CREATE PERMISSION statement.
If time-separator is not specified, . will be used.
naming-option
The naming convention used for qualified names in the generated SQL statements. The valid values are:
SQL
schema.table syntax
SYS
library/file syntax
If naming-option is not specified, SQL will be used.
decimal-point
The decimal point used for numeric constants. The valid values are:
.
Period separator
,
Comma separator
If decimal-point is not specified, . will be used.
standards-option
The standards option specifies whether the generated SQL statements should contain Db2 for i extensions or whether the statements should conform to the Db2 family SQL or to the ANS and ISO SQL standards. The valid values are:
0
Db2 for i extensions may be generated in SQL statements.
1
The generated SQL statements must conform to SQL statements common to the Db2 family.
2
The generated SQL statements must conform to the ANSI and ISO SQL standards.
If standards-option is not specified, 0 will be used.
drop-option
The drop option specifies whether DROP (or ALTER) SQL statements should be generated prior to the CREATE statement to drop the specified object. The valid values are:
0
DROP statements should not be generated.
1
DROP statements should be generated.
If drop-option is not specified, 0 will be used.
message-level
The severity level at which the messages are generated. If errors occur that have a severity level greater than this value, a message is generated in the output. The valid values are in the range 0 through 39 inclusive. The message level must be less than or equal to the severity level.
If message-level is not specified, 0 will be used.
comment-option
The comment option specifies whether COMMENT SQL statements should be generated if a comment exists on the specified database object. If comments are not supported by the specified database object, the comment option is ignored. The valid values are:
0
COMMENT SQL statements should not be generated.
1
COMMENT SQL statements should be generated. If the specified database object type is a table or view, COMMENT SQL statements will also be generated for columns of the table or view.
Start of change2End of change
Start of changeCOMMENT SQL statements should be generated. If the specified database object has no comment and its type is INDEX, SEQUENCE, TABLE, TYPE, VARIABLE, VIEW, or XSR, the system object text will be used for the comment. End of change
Start of changeIf the specified database object type is a table or view, COMMENT SQL statements will also be generated for columns of the table or view.End of change
If comment-option is not specified, 1 will be used.
label-option
The label option specifies whether LABEL SQL statements should be generated if a label exists on the specified database object. If labels are not supported by the specified database object, the label option is ignored. The valid values are:
0
LABEL SQL statements should not be generated.
1
LABEL SQL statements should be generated. If the specified database object type is a table or view, LABEL SQL statements will also be generated for columns of the table or view.
If label-option is not specified, 1 will be used.
header-option
The header option specifies whether a header should be generated prior to the CREATE statement. The header consists of comments that describe the version, date and time, the relational database, and some of the options used to generate the SQL statements. The valid values are:
0
A header should not be generated.
1
A header should be generated.
If header-option is not specified, 1 will be used.
trigger-option
The trigger option specifies whether triggers should be generated when the object type is a TABLE or VIEW. The valid values are:
0
Triggers should not be generated.
1
Triggers should be generated.
If trigger-option is not specified, 1 will be used.
constraint-option
The constraint option specifies whether constraints should be generated when the object type is a TABLE. The valid values are:
0
Constraints should not be generated.
1
Constraints should be generated.
2
Constraints should be generated as part of the CREATE TABLE statement.
If constraint-option is not specified, 1 will be used.
system-name-option
The system name option specifies whether a Start of changeFOR SYSTEM NAME clauseEnd of change should be generated for the system name when it is different from the SQL name and the object type is an INDEX, TABLE, VIEW, Start of changeSEQUENCE,End of change or Start of changeVARIABLEEnd of change. The valid values are:
0
A Start of changeFOR SYSTEM NAME clauseEnd of change should not be generated.
1
A Start of changeFOR SYSTEM NAME clauseEnd of change should be generated.
If system-name-option is not specified, 1 will be used.
privileges-option
The privileges option specifies whether GRANT SQL statements should be generated on the specified database object. If privileges are not supported by the specified database object, the privileges option is ignored. The valid values are:
0
GRANT SQL statements should not be generated.
1
GRANT SQL statements should be generated.
If privileges-option is not specified, 1 will be used.
ccsid-option
The CCSID option specifies whether the CCSID attribute should be generated for column definitions when the object type is a TABLE. The valid values are:
0
CCSID attribute should not be generated.
1
CCSID attribute should be generated.
If ccsid-option is not specified, 1 will be used.
create-or-replace-option
The create or replace option specifies whether CREATE OR REPLACE should be generated for the specified database object on the CREATE statement. This option is ignored if the specified database object does not support CREATE OR REPLACE. The valid values are:
0
CREATE OR REPLACE should not be generated.
1
CREATE OR REPLACE should be generated.
If create-or-replace-option is not specified, 0 will be used.
obfuscate-option
The obfuscate option specifies whether an obfuscated SQL statement should be returned for SQL functions, SQL procedures, or SQL triggers that were not created using obfuscated statements. This option is ignored if the standards option is not ‘0’. This option is also ignored if the object is not an SQL function, procedure, or trigger. This option is ignored if the object is already obfuscated. Setting Obfuscate option = 0 cannot be used as a means of obtaining the unobfuscated SQL statement for an obfuscated object. The valid values are:
0
An obfuscated statement should not be generated.
1
An obfuscated statement should be generated for SQL functions, SQL procedures, or SQL triggers.
If obfuscate-option is not specified, 0 will be used.
activate-access-control-option
The activate row and column access control option specifies whether an ALTER TABLE to activate row and column access control should be generated when the object type is a TABLE. This option is ignored if the standards option is not '0' or '1'. The valid values are:
0
Activate row and column access control should not be generated.
1
Activate row and column access control should be generated.
If activate-access-control-option is not specified, 1 will be used.
mask-and-permission-option
The mask and permission option specifies whether row permissions and column masks should be generated when the object type is a TABLE. This option is ignored if the standards option is not '0' or '1'. The valid values are:
0
Permissions and masks should not be generated.
1
Permissions and masks should be generated.
If mask-and-permission-option is not specified, 1 will be used.
qualified-name-option
The qualified name option specifies whether qualified or unqualified names should be generated for the specified database object. The valid values are:
0
Qualified object names should be generated. Unqualified names within the body of SQL routines will remain unqualified.
1
Unqualified object names should be generated when the a library is found which matches the database object library name. Any SQL object or column reference that is RDB qualified will be generated in its fully qualified form. For example, rdb-name.schema-name.table-name and rdb-name.schema-name.table-name.column-name references will retain their full qualification.
If qualified-name-option is not specified, 0 will be used.
additional-index-option
The additional index option specifies whether additional CREATE INDEX statements will be generated for DDS-created keyed physical or logical files. The valid values are:
0
Additional CREATE INDEX statements will not be generated.
1
An additional CREATE INDEX statement will be generated that matches the index for a DDS-created keyed physical file. If the physical file has a PRIMARY KEY constraint, a CREATE INDEX statement is not generated.
An additional CREATE INDEX statement will be generated that matches the index for a DDS-created keyed logical file. If a value of ‘1’ is specified for the index instead of view option, an additional CREATE INDEX statement is not generated. Additional CREATE INDEX statements will also be generated that match the join indexes of a DDS-created join logical file.
If additional-index-option is not specified, 0 will be used.
index-instead-of-view-option
The index instead of view option specifies whether a CREATE INDEX or CREATE VIEW statement will be generated for a DDS-created keyed logical file. The valid values are:
0
A CREATE VIEW statement will be generated.
1
A CREATE INDEX statement will be generated that matches the index for a DDS-created keyed logical file.
If index-instead-of-view-option is not specified, 0 will be used.
Start of changetemporal-optionEnd of change
Start of changeThe temporal option specifies whether a CREATE TABLE and an ALTER TABLE statement will be generated when the object type is a TABLE and the table is defined as a temporal table. This option is ignored if the object is not a temporal table or if the standards option is not ‘0’ or '1'. The valid values are:
0
A CREATE TABLE statement will be generated.
1
A CREATE TABLE statement will be generated and an ALTER TABLE statement will be generated to add versioning.
2
Only an ALTER TABLE statement will be generated to add versioning.
End of change
Start of changeIf temporal-option is not specified, 0 will be used.End of change
Start of changesource-stream-fileEnd of change
Start of changeA character or graphic string expression that identifies the source stream file that contains the SQL statements generated by the procedure. This parameter is ignored unless database-source-file-name has a value of *STMF.

If the file does not exist, it will be created using the CCSID specified by source-stream-file-ccsid. As lines are written to the file, source-stream-file-end-of-line determines the end of line sequence, if any, to be appended to each line.

When source-stream-file is used, values provided for database-source-file-library-name and database-source-file-member are ignored.

Writing to the QSYS.LIB file system is not supported.

End of change
Start of changesource-stream-file-end-of-lineEnd of change
Start of changeA character or graphic string expression that defines the end of line character(s) which will be appended to the end of each line when writing to source-stream-file. The carriage return character is always X’0D’. Based on the CCSID of the source stream file, the line feed character is X’25’ for an EBCDIC CCSID and X’0A’ for ASCII and UTF-8 CCSIDs. The valid values are:End of change
Start of change
CR
A carriage return is appended.
CRLF
A carriage return and line feed are appended.
LF
A line feed is appended.
LFCR
A line feed and carriage return are appended.
End of change
Start of change

If source-stream-file-end-of-line is not specified, CRLF will be used.

This parameter is ignored when writing to a source file.

End of change
Start of changesource-stream-file-ccsidEnd of change
Start of changeAn integer value that defines the CCSID to use if a new source stream file is created. If source-stream-file is specified and the source stream file does not exist, it will be created with this CCSID value. A value of 0 indicates that the default job CCSID is to be used. A CCSID of 65535 cannot be specified.

If source-stream-file-ccsid is not specified, 0 will be used.

This parameter is ignored when writing to a source file.

End of change

Examples

  • Generate DDL for all tables in a schema and return the source as a result set.
    CALL QSYS2.GENERATE_SQL('%', 'SAMPLE_CORPDB', 'TABLE', REPLACE_OPTION => '0');
  • Generate DDL for all indexes starting with ‘X’ within the SAMPLE_CORPDB schema, place the output in a file named DDLSOURCE/GENFILE member INDEXSRC.
    CALL QSYS2.GENERATE_SQL('X%', 'SAMPLE_CORPDB', 'INDEX', 
                            'GENFILE', 'DDLSOURCE', 'INDEXSRC',
                            REPLACE_OPTION => '0');
  • Generate DDL for a single table and include the constraints within a CREATE OR REPLACE TABLE statement.
    CALL QSYS2.GENERATE_SQL('EMPLOYEE', 'SAMPLE_CORPDB', 'TABLE', 
                            'GENFILE', 'DDLSOURCE', 'MASTERSRC',
                            CREATE_OR_REPLACE_OPTION => '1',
                            CONSTRAINT_OPTION => '2');
  • Start of changeGenerate DDL for MYLIB.MYTABLE, placing the output in source stream file /usr/mytable_ddl.
    CALL QSYS2.GENERATE_SQL(DATABASE_OBJECT_NAME => 'MYTABLE', 
                            DATABASE_OBJECT_LIBRARY_NAME => 'MYLIB', 
                            DATABASE_OBJECT_TYPE => 'TABLE', 
                            DATABASE_SOURCE_FILE_NAME =>'*STMF', 
                            SOURCE_STREAM_FILE =>'/usr/mytable_ddl');
    End of change