Are you a developer or system administrator struggling with database migrations, replications, or simply understanding an object's structure?  

You are not alone.  

In modern IBM i environments, where SQL is the preferred method for creating database objects, the original source code is often not kept in production. This can make it difficult to see an object's complete definition, especially when you need it for troubleshooting or impact analysis. 

Fortunately, IBM i offers a powerful solution: retrieving the Data Definition Language (DDL). 

Method 1: The GENERATE_DDL Function 

For quick, on-the-fly DDL retrieval, the QSYS2.GENERATE_DDL function is your go-to tool. This function returns a single row with the full DDL statement for a specified object. 

To use it, simply provide the library name (schema) and the object name. You can also specify the object type for more precise results. 

Example: 

To get the DDL for a table called ORDERS in the SALESDATA library, you can run this query: 

SELECT DDL  

FROM TABLE(QSYS2.GENERATE_DDL(OBJECT_SCHEMA => 'SALESDATA',  

                        OBJECT_NAME => 'ORDERS', 

                        OBJECT_TYPE => 'TABLE')); 
 

This is an efficient way to get the exact CREATE statement needed for tasks like documentation or quick object recreation. 

Method 2: The GENERATE_SQL_OBJECTS Procedure 

For a more automated approach, especially when you need to generate DDL for multiple objects or save the results to a file, use the QSYS2.GENERATE_SQL_OBJECTS procedure. 

Step 1: Create an Input Table 

First, create a temporary table to list the objects you want to process. This table must have three specific columns: OBJECT_SCHEMA, OBJECT_NAME, and SQL_OBJECT_TYPE. 

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

Step 2: Insert Object Details 

Next, insert the details of the SQL objects into your new table. For example, to generate the DDL for a table named TRS in the QTEMP library, you would use this command: 

INSERT INTO QTEMP.INPUTTBL VALUES ('QTEMP', 'TRS', 'TABLE'); 
 

You can add as many objects as you need to this table. 

Step 3: Run the Procedure 

Finally, call the GENERATE_SQL_OBJECTS procedure, telling it where to find your input list and where to save the output. The DDL will be saved as a source member in a specified library and source file. 

CALL QSYS2.GENERATE_SQL_OBJECTS( 
    SYSTEM_TABLE_NAME => 'INPUTTBL', 
    SYSTEM_TABLE_SCHEMA => 'QTEMP', 
    DATABASE_SOURCE_FILE_NAME => 'QDDSSRC', 
    DATABASE_SOURCE_FILE_LIBRARY_NAME => 'PIOLIB', 
    DATABASE_SOURCE_FILE_MEMBER => 'TDDLSRC' 
);