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 or as a result set.
The database source file member 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.
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.
- 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.
- database-source-file-library
- 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.
- 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.
- replace-option
- The replace option for the database source file member. The valid
values are:If replace-option is not specified, 1 will be used.
- 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 multiple objects are being generated, the member is cleared for each object so only the last generated statement will remain in the member.
- 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.
- 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.
- 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.
- 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.
- 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.
- naming-option
- The naming convention used for qualified names in the generated
SQL statements. The valid values are:If naming-option is not specified, SQL will be used.
- SQL
- schema.table syntax
- SYS
- library/file syntax
- decimal-point
- The decimal point used for numeric constants. The valid values
are:If decimal-point is not specified, . will be used.
- .
- Period separator
- ,
- Comma separator
- 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.
- 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:If drop-option is not specified, 0 will be used.
- 0
- DROP statements should not be generated.
- 1
- DROP statements should be generated.
- 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.
- 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:If comment-option is not specified, 1 will be used.
- 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.
- 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:If label-option is not specified, 1 will be used.
- 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.
- 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:If header-option is not specified, 1 will be used.
- 0
- A header should not be generated.
- 1
- A header should be generated.
- trigger-option
- The trigger option specifies whether triggers should be generated
when the object type is a TABLE or VIEW. The valid values are:If trigger-option is not specified, 1 will be used.
- 0
- Triggers should not be generated.
- 1
- Triggers should be generated.
- constraint-option
- The constraint option specifies whether constraints should be
generated when the object type is a TABLE. The valid values are:If constraint-option is not specified, 1 will be used.
- 0
- Constraints should not be generated.
- 1
- Constraints should be generated using ALTER TABLE statements.
- 2
- Constraints should be generated as part of the CREATE TABLE statement.
- 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.
- 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:If privileges-option is not specified, 1 will be used.
- 0
- GRANT SQL statements should not be generated.
- 1
- GRANT SQL statements should be generated.
- 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:If ccsid-option is not specified, 1 will be used.
- 0
- CCSID attribute should not be generated.
- 1
- CCSID attribute should be generated.
- 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:If create-or-replace-option is not specified, 0 will be used.
- 0
- CREATE OR REPLACE should not be generated.
- 1
- CREATE OR REPLACE should be generated.
- 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:If obfuscate-option is not specified, 0 will be used.
- 0
- An obfuscated statement should not be generated.
- 1
- An obfuscated statement should be generated for SQL functions, SQL procedures, or SQL triggers.
- 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:If activate-access-control-option is not specified, 1 will be used.
- 0
- Activate row and column access control should not be generated.
- 1
- Activate row and column access control should be generated.
- 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:If mask-and-permission-option is not specified, 1 will be used.
- 0
- Permissions and masks should not be generated.
- 1
- Permissions and masks should be generated.
- 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:If qualified-name-option is not specified, 0 will be used.
- 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.
- 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:If additional-index-option is not specified, 0 will be used.
- 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.
- 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:If index-instead-of-view-option is not specified, 0 will be used.
- 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.
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');