ADBGDDL stored procedure

Use the ADBGDDL stored procedure to get the DDL for a single object from the Db2 catalog.

The input parameters identify the object for which you want the DDL. The output parameters provide information about whether the operation to generate the DDL was successful. The core function that ADBGDDL uses to generate the DDL is the GEN function.

ADBGDDL returns DDL in a result set and optionally returns a second result set with the GEN report.

Environment

The ADBGDDL stored procedure runs in a WLM-established stored procedure address space. For setup instructions, see Setting up the provided REST APIs.

Recommendation: IBM strongly recommends that ADBGDDL only is configured to run in the same WLM environment as the Db2-supplied stored procedure ADMIN_INFO_SYSPARM if the WLM environment is configured with MNSPAS>1. (MNSPAS is the parameter for the number of address spaces.)

ADBGDDL calls ADMIN_INFO_SYSPARM to retrieve Db2 subsystem parameter information. ADMIN_INFO_SYSPARM must run in a WLM environment with NUMTCB=1. Therefore, if these two procedures run in the same WLM environment with NUMTCB=1 and MNSPAS=1, ADBGDDL will wait for the call to ADMIN_INFO_SYSPARM to fail, because no WLM address space is available. ADBGDDL will continue after this failure but not be able to optimize the DDL based on the subsystem parameters. Also, the response time will be significantly higher because of the wait for ADMIN_INFO_SYSPARM.

Authorization

To execute the CALL statement, the owner of the package or plan that contains the CALL statement must have EXECUTE privilege on the ADBGDDL stored procedure.

Syntax

Notes:

¹ qual is required for objects that can be qualified. Otherwise, this parameter is invalid.
² version is optional for functions, procedures, and triggers. Otherwise, this parameter is invalid.

Input parameters

type

The object type. You can specify one of the following values:

AL: Alias
DB: Database
DT: Data type
FU: Function
GV: Global variable
IX: Index
MK: Column mask
PM: Permission
QA: Sequence alias
RO: Role
SG: Storage group
SP: Stored procedure
SQ: Sequence
SY: Synonym
TB: Table
TC: Trusted context
TG: Trigger
TS: Table space
VW: View

qual

The object qualifier.

name

The object name.

version

The version of the object.

parm

An optional parameter. Currently, you can specify only the following parameter:

RPT=Y: Indicates that the GEN report is to be returned. The default is to not produce a report.

Output parameters

rc

The return code.

Type:: SMALLINT

message

A message that describes the result of the operation, such as a formatted SQL message. This message can be a success or error message.

Type:: VARCHAR(800)

Result sets

Result set	Description
C_SQL	The generated DDL. If an error occurred, this result set is empty. The format of this result set is the same as the SQL result set for ADB2RE. For a description of the columns in this result set, see SQL result set.
C_RPT	An optional result set to contain the GEN REPORT. This result set is returned if the parameter RPT=Y was specified. The format of this result set is the same as the report result set for ADB2RE. For a description of the columns in this result set, see Report result set.

Example

Issue the following CALL statement to generate a CREATE DATABASE statement for the Db2 catalog database DSNDB06 and return result sets for the SQL and the report, a return code, and a message:

CALL ADBGDDL('DB',
            '',
            'DSNDB06',
            '',
            :rc, 
            :msg,
            'RPT=Y');