Re: Extract SQL statement used to create table

Hi Dan,

I've spent very extensive time in the system catalog views (up through v7r1
only) and don't recall ever seeing a table's CREATE SQL statement.

I think your best bet is to use the QSYS2.GENERATE_SQL procedure, or to use
Navigator to generate the SQL.

In my case, I made two SQL functions that act as wrappers around the call
to QSYS2.GENERATE_SQL. One function accepts all the same input parameters
as used by GENERATE_SQL, and the second accepts only 3 parameters: object
name, object library, and object type. The second function applies default
values to all the other parameters and calls the first function, so I don't
have to specify the very long list of parameters that QSYS2.GENERATE_SQL
requires. Both functions return a CLOB(1M) containing the generated SQL.

Those functions allow me to do fetches like these to get the CREATE SQL:

values M_GENERATE_SQL( 'MY_TABLE', 'MY_LIB', 'TABLE' )

or

select T.TABLE_NAME, T.TABLE_SCHEMA, GS.GENERATED_SQL
from QSYS2.SYSTABLES T
cross join lateral (
values M_GENERATE_SQL( T.TABLE_NAME, T.TABLE_SCHEMA, 'TABLE' )
) as GS ( GENERATED_SQL )
where T.TABLE_SCHEMA = 'MY_LIB'
and T.TABLE_TYPE = 'T'

The latter generates the CREATE SQL for all SQL tables in a schema.

Mike

date: Tue, 27 Jun 2017 12:20:56 -0400

from: Dan <dan27649@xxxxxxxxx>
subject: Extract SQL statement used to create table

I know I've seen DSPFD show the SQL statement used to create a table or
view. I've got a table that I'm 99% certain was created with SQL, but
DSPFD shows no SQL statement. DSPOBJD shows no source file/library/member
data for this table.

This table was very likely created with a CREATE TABLE x as (select ...)
WITH DATA. Is there a system view that would have this information?

Google is either not my friend or I'm searching with the wrong terms.

- Dan