Generate Data Definition Language (QSQGNDDL) API

The Generate Data Definition Language (QSQGNDDL) API generates the SQL data definition language statements required to recreate a database object. The results are returned in the specified database source file member.

Database physical files or logical files that were created using an interface other than SQL may be specified. For example, files created from DDS and the CRTPF or CRTLF commands may be specified. Even if the object was created using SQL, the Standards option may restrict what can be generated. In either of these cases:

• the operation may succeed with warnings that are generated in the SQL statement source, or
• the operation may fail for certain non-relational files or objects not supported by the specified Standards option.

If a database object was created using an SQL interface, the resulting SQL statements may be slightly different than the SQL statements that created the object originally. For example:

• When there is more than one way to specify an attribute in SQL, the more standard syntax is generally chosen. For example, if a user creates a table with a FLOAT(52) column, DOUBLE PRECISION is generated.
• When a clause is not specified in the original SQL statement and a default is taken instead, a clause may be generated to explicitly show the default. For example, if the default value for a nullable column is the null value, the clause DEFAULT NULL is generated.
• When a Standards option is used to restrict the generated SQL to the ANS and ISO standard or the DB2^® family, an attribute may be omitted. For example, if the ALLOCATE clause is specified on a VARCHAR column, the ALLOCATE clause is not generated unless the Standards option allows DB2 for IBM^® i extensions.

For more information, see the Severity level field within the SQLR0100 Format.

You can use the QSQGNDDL API with database objects only. DDM files (other than SQL aliases) are not supported. File overrides do not affect the specified object names. File overrides do affect the specified source file names.

Authorities and Locks

Object Library Authority

*EXECUTE

Source File Library Authority

*EXECUTE

Object Authorities

*EXECUTE for *LIB objects.
*USE for the *DTADCT object in a library (if SCHEMA is specified for the object type).
*USE for *FILE objects (not including aliases).
*USE to QSYS2/SYSPARMS for functions and procedures.
*USE to QSYS2/SYSROUTINE for functions and procedures.
*USE to QSYS2/SYSSEQOBJ for sequences.
*USE to QSYS2/SYSTYPES for types.
*USE to QSYS2/SYSTABLES for aliases.
*USE to QSYS2/SYSVARS for global variables.
*USE to QSYS2/RTNPRIV for function and procedure privileges.
*USE to QSYS2/UDTPRIV for type privileges.
*USE to QSYS2/USAGEPRIV for sequence object privileges.
*USE to QSYS2/VARPRIV for global variable privileges.
*USE to SYSIBM/SQLCOLPRIV for table and view privileges.
*USE to SYSIBM/SQLTBLPRIV for table and view privileges.

Source File Authority

*OPER and *ADD.
If replace is specified, *DLT and either *OBJMGT or *OBJALTER is required also.

Object Lock

*SHRRD for *LIB objects.
*SHRNUP for *FILE objects (not including aliases). (See note below.)
*SHRRD to QSYS2/SYSFUNCS for functions.
*SHRRD to QSYS2/SYSPARMS for functions and procedures.
*SHRRD to QSYS2/SYSPROCS for procedures.
*SHRRD to QSYS2/SYSSEQOBJ for sequences.
*SHRRD to QSYS2/SYSTYPES for types.
*SHRRD to QSYS2/SYSTABLES for aliases.
*SHRRD to QSYS2/SYSVARS for global variables.

Note: If the object is a *FILE object, the lock is acquired only on the file definition and not the data. Applications that modify data can run concurrently with this API.

Source File Lock

*EXCLRD.

Required Parameter Group

Input template

INPUT;CHAR(*)

A structure that contains the input options used to generate DDL for the requested database object. For the format of this parameter, see SQLR0100 Format.

Length of input template

INPUT; BINARY(4)

A variable that contains the length of the input template. The length must be greater than zero and large enough to contain all the template fields up to and including the Header Option. The length must not be larger than 32767.

Input template format name

INPUT; CHAR(8)

The format of the input template being used. The possible value is:

SQLR0100

Basic template

For more information, see SQLR0100 Format.

Error code

I/O; CHAR(*)

The structure in which to return error information. For the format of the structure, see Error code parameter. If this parameter is omitted, diagnostic and escape messages are issued to the application.

SQLR0100 Format

The following table shows the format of the input template parameter for the SQLR0100 format. For detailed descriptions of the fields in the table, see Field Descriptions.

Offset		Type	Field
Dec	Hex
0	0	CHAR(258)	Database object name
258	102	CHAR(258)	Database object library name
516	204	CHAR(10)	Database object type
526	20E	CHAR(10)	Database source file name
536	218	CHAR(10)	Database source file library name
546	222	CHAR(10)	Database source file member name
556	22C	BINARY(4)	Severity level
560	230	CHAR(1)	Replace option
561	231	CHAR(1)	Statement formatting option
562	232	CHAR(3)	Date format
565	235	CHAR(1)	Date separator
566	236	CHAR(3)	Time format
569	239	CHAR(1)	Time separator
570	23A	CHAR(3)	Naming option
573	23D	CHAR(1)	Decimal point
574	23E	CHAR(1)	Standards option
575	23F	CHAR(1)	Drop option
576	240	BINARY(4)	Message level
580	244	CHAR(1)	Comment option
581	245	CHAR(1)	Label option
582	246	CHAR(1)	Header option
583	247	CHAR(1)	Trigger option
584	248	CHAR(1)	Constraint option
585	249	CHAR(1)	System name option
586	24A	CHAR(1)	Privileges option
587	24B	CHAR(1)	CCSID option
588	24C	CHAR(1)	Create or Replace option
589	24D	CHAR(1)	Obfuscate option
590	24E	CHAR(1)	Reserved
591	24F	CHAR(1)	Reserved
592	250	CHAR(1)	Qualified name option
593	251	CHAR(1)	Additional index option
594	252	CHAR(1)	Index instead of view option
595	253	CHAR(*)	Reserved

Field Descriptions

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 the Standards option is '2', additional index option ‘1’ is not valid.

If the Index instead of view option is ‘1’, additional index option ‘1’ is not valid.

The default is '0'.

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 the Standards option is '2', comment option '1' is not valid.

Constraint option. The constraint option specifies whether constraints should be generated when the object type is a TABLE. The valid values are:

The default is '1'.

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 the standards option is '0' and '0' is specified, the CCSID clause, FOR MIXED DATA, FOR SBCS DATA, or FOR BIT DATA is not generated for the column definition.

If the standards option is '1' and '0' is specified, FOR MIXED DATA, FOR SBCS DATA, or FOR BIT DATA is not generated for the column definition.

If the standards opttion is '2' the CCSID option is ignored.

The default is '1'.

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 the Standards option is '1' or '2', the CREATE OR REPLACE option is not valid.

Database object name. 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. 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.

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. The name of the library containing the object for which DDL will be generated. The name is case sensitive. If delimiters are required for the name to be valid, they must be specified. This name is ignored if the specified object type is SCHEMA. You can use these special values for the library name:

Database object type. 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. If the Standards option is '2', an ALIAS object type is not valid.
CONSTRAINT	The object attribute is a constraint.
FUNCTION	The object is an SQL function.
INDEX	The object is an SQL index or keyed logical file. If the Standards option is '2', an INDEX object type is not valid.
PROCEDURE	The object is an SQL procedure.
SCHEMA	The object is an SQL schema (collection) or library.
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. If the Standards option is '2', an VARIABLE object type is not valid.
VIEW	The object is an SQL view or logical file.
XSR	The object is an XML schema repository object. If the Standards option is '2', an XSR object type is not valid.

Database source file name. The name of the source file that contains the SQL statements generated by the API. 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.

Database source file library name. The name of the library containing the source file that contains the SQL statements generated by the API. 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:

Database source file member name. The name of the source file member that contains the SQL statements generated by the API. 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.

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, or CREATE PROCEDURE statement. The valid values are:

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, or CREATE PROCEDURE statement. The date separator is only applicable if the date format is MDY, DMY, YMD, or JUL. The valid values are:

Decimal point. The decimal point used for numeric constants. The valid values are:

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:

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:

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 non-join logical file. The valid values are:

If the Standards option is '2', index instead of view option ‘1’ is not valid.

If the Additional index option is ‘1’, index instead of view option ‘1’ is not valid.

The default is '0'.

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 the Standards option is '1' or '2', label option '1' is not valid.

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.

Naming option. The naming convention used for qualified names in the generated SQL statements. The valid values are:

Obfuscate option. The obfuscate option specifies whether an obfuscated SQL statement should be returned for SQL functions or SQL procedures 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 or procedure. 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:

The default is '0'.

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:

To generate privileges for an external routine, the external routine must exist.

Only SQL privileges will be generated for the specified database object. Authorities acquired through a group user profile, authorization list, special authority (such as *ALLOBJ) or any authority granted through GRTOBJAUT that does not map directly to an SQL privilege are not 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:

The default is '0'.

Replace option. The replace option for the database source file member. The valid values are:

Reserved. A reserved field. It must contain hexadecimal zeroes.

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 API to fail.

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:

If option 1 or 2 is chosen, the SQL statements generated may not completely represent the object in DB2 for i; however, the statements will be compatible with the specified DB2 Family or ANSI and ISO standards option.

If the object is an SQL function, SQL procedure, SQL trigger, or SQL view, the SQL statements in the body of the object are included in the generated SQL statement. Hence, if the option 1 or 2 is chosen, the generated SQL statement may not conform to the specified standards option since the statements within the body of the SQL object may not conform to the specified standard. For example, if a CREATE INDEX statement exists in the body of an SQL procedure, the generated CREATE PROCEDURE statement will contain the CREATE INDEX statement even if option 1 or 2 is chosen.

There is no attempt to take product specific limits into account. For example, a table name in DB2 for i can be 128 bytes, but other products may not support table names that are that long. Thus, even if the generated SQL statement is standard, it still may not work on other products if they have smaller limits than those on DB2 for i.

If option 1 is specified,

• The naming option must be SQL.
• The date format must be ISO, USA, EUR, or JIS.
• The time format must be ISO, USA, EUR, or JIS.
• The decimal point must be the period.
• The LABEL option must be ‘0’.
• The System name option must be ‘0’.
• The Create or Replace option must be ‘0’.
• The Obfuscate option must be ‘0’.

If option 2 is specified,

• The naming option must be SQL.
• The date format must be ISO.
• The time format must be ISO.
• The decimal point must be the period.
• The LABEL option must be ‘0’.
• The COMMENT option must be ‘0’.
• The System name option must be ‘0’.
• The CCSID option must be ‘0’.
• The Create or Replace option must be ‘0’.
• The Obfuscate option must be ‘0’.
• The Additional index option must be ‘0’.
• The Index instead of view option must be ‘0’.
• An ALIAS, VARIABLE, or XSR object type must not be specified.

Statement formatting option. The formatting option used in the generated SQL statements. The valid values are:

System name option. The system name option specifies whether a RENAME statement should be generated for the system name when it is different from the SQL name and the object type is an INDEX, TABLE, or VIEW. The valid values are:

If the Standards option is '1' or '2', system name option '1' is not valid. The default is '0'.

Time format. The format used for time constants in a generated SQL CREATE TABLE statement. The time format may not apply to date constants that are in ISO, EUR, USA, or JIS format in a CREATE VIEW, CREATE TRIGGER, CREATE FUNCTION, or CREATE PROCEDURE statement. in the generated SQL statements. The valid values are:

Time separator. The time separator used for time constants in a generated SQL CREATE TABLE statement. The time separator may not apply to date constants that are in ISO, EUR, USA, or JIS format in a CREATE VIEW, CREATE TRIGGER, CREATE FUNCTION, or CREATE PROCEDURE statement. The time separator is only applicable if the time format is HMS. in the generated SQL statements. The valid values are:

Trigger option. The trigger option specifies whether triggers should be generated when the object type is a TABLE or VIEW. The valid values are:

The default is '1'.

Usage Notes

If the value of the statement formatting option is 0, the generated SQL statements will be minimally formatted by adding blanks. For example:

CREATE TABLE mjatst.table_one (
  column_one INTEGER,
  column_two INTEGER,
  column_three CHAR(4000));

If the value of the statement formatting option is 1, the generated SQL statements will be formatted by inserting end-of-line characters, tab characters, and spaces. For example:

CREATE TABLE mjatst.table_one (
        column_one INTEGER,
        column_two INTEGER,
        column_three CHAR(4000));

Error Messages

[ Back to top | Database and File APIs | APIs by category ]