GENERATE_SQL_OBJECTS procedure

The GENERATE_SQL_OBJECTS procedure generates the SQL data definition language (DDL) statements required to recreate a set of database objects. The results are returned in the specified database source file member or as a result set. The procedure will generate the objects in the source file member such that dependent objects are generated after depended on objects.

The database source file member will contain the generated SQL statements. If the output source file is QTEMP/Q_GENSQOBJ, the source file is returned as a result set as well.

The schema is QSYS2.

system-table-name

A character or graphic string expression that identifies the name of a table that contains the names and types of the database objects for which DDL will be generated. The system name of the table must be specified. The name is case sensitive. Delimiters must be specified if they are required. 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. Wildcard characters are not supported.

The specified table must contain three columns that contain the names and types of the objects for which DDL will be generated. The column names of the table must be OBJECT_SCHEMA, OBJECT_NAME, and SQL_OBJECT_TYPE, in that order.

For example, create the table like this:

CREATE TABLE QTEMP.INORDER (OBJECT_SCHEMA VARCHAR(258), 
                            OBJECT_NAME VARCHAR(258), 
                            SQL_OBJECT_TYPE CHAR(10));

The contents of the columns must be specified according to the following rules for the corresponding parameters in the QSQGNDDL API. Each row in the table must identify an object that is distinct from every other object in the table.

OBJECT_SCHEMA

Identifies the schema name of an object for which DDL will be generated. The name must be delimited if delimiters are required in an SQL statement. This name is ignored if the specified object type is SCHEMA. *LIBL and *CURLIB are not allowed.

OBJECT_NAME

Identifies the name of an object for which DDL will be generated. The name must be delimited if delimiters are required in an SQL statement. 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 must not identify an alias.

SQL_OBJECT_TYPE

Identifies the SQL object type of an object for which DDL will be generated.

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.

system-table-schema

A character or graphic string expression that identifies the name of the library of the table that contains the names and types of the database objects for which DDL will be generated. The system name of the schema must be specified. The name is case sensitive. Delimiters must be specified if they are required. For example, a schema with a name of "lib1" must be specified as "lib1". A schema with a name of LIB1 must be specified in upper case. Wildcard characters are not supported. *LIBL and *CURLIB are not allowed.

The default is QTEMP.

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.

If database-source-file-name is not specified, Q_GENSQOBJ 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.

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.

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_GENSQOBJ 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. The valid values are:

0: The resulting SQL statements are appended to the end of the database source file member.
1: The database source file member is cleared prior to adding the resulting SQL statements. If this option is chosen, the member may be cleared 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 ON 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 ON SQL statements should not be generated.
1: COMMENT ON SQL statements should be generated. If the specified database object type is a table or view, COMMENT ON SQL statements will also be generated for columns of the table or view.

If comment-option is not specified, 1 will be used.

label-option

The label option specifies whether LABEL ON 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 ON SQL statements should not be generated.
1: LABEL ON SQL statements should be generated. If the specified database object type is a table or view, LABEL ON 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 first generated 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 FOR SYSTEM NAME clause should be generated for the system name when it is different from the SQL name and the object type is an INDEX, TABLE, VIEW, or VARIABLE. The valid values are:

0: A FOR SYSTEM NAME clause should not be generated.
1: A FOR SYSTEM NAME clause 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 tirigger. 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.

Notes

If an error occurs while generating the DDL for an object, the source file will contain the error and processing will continue to the next object. After processing the last object, a warning SQLSTATE '01H52' will be returned.

Objects are generated in the following order:
1. Schemas
2. Types
3. Sequences
4. Aliases
5. Non-MQT tables and any constraints and indexes on those tables
6. Functions
7. Procedures
8. Variables
9. Views, DDS-created logical files and MQTs and any constraints and indexes on those tables
10. Triggers
11. Masks
12. Permissions
13. XSR objects

Restrictions

One use of this procedure is to create a clone of a set of objects in another library by using QUALIFIED_NAME_OPTION=>1, setting the current schema and path, and then running the generated script.
- If a depended on object is not included in the list of objects for which DDL will be generated, errors may occur when attempting to run the generated script. For example, if view V1 is based on table T1, but only V1 is specified, the attempt to run the generated script will fail because T1 was not included.
- The QSQGNDDL API, on which this procedure is based, generates a qualified name in some cases. Thus, it may be necessary to make minor modifications to the script prior to running it. For more information see the Qualified name option parameter in Generate Data Definition Language (QSQGNDDL) API.
A function or procedure that has a parameter with a DEFAULT clause that references a variable, view, or MQT will not create when running the generated script. This is because variables, views, and MQTs are generated after functions and procedures. Note that references to variables, views, and MQTs within the body of function or procedure are soft dependencies and will not prevent the create.
A variable that contains a DEFAULT clause that references a view or MQT will not create when running the generated script. This is because views and MQTs are generated after variables.

Examples

Generate ordered DDL for the objects listed in the QTEMP.INORDER file.
```
CALL QSYS2.GENERATE_SQL_OBJECTS('INORDER', 'QTEMP');
```