Re: Extract SQL statement used to create table

Hi, Dan,

If you do a DSPFD it should tell you whether the physical file is really an SQL DDL created "TABLE" or not ... you will see this:

Data Base File Attributes
Externally described file . . . . . . . . . : Yes
* SQL file type . . . . . . . . . . . . . . . : TABLE *
File level identifier . . . . . . . . . . . : 1170627131347

Otherwise, if you do not see the "SQL file type" line under "Data Base File Attributes" it was created with CRTPF and possibly DDS source.

Do a google search for "GENDDLSRC" or "RTVSQLSRC" -- or see:

http://www.mcpressonline.com/programming-other/rtvsqlsrc
or
http://www.ibmsystemsmag.com/ibmi/developer/general/Generating-DDL-Source-Using-a-CL-Command/Source-Code/

Also, there are some other tools to retrieve DDS source fora PFor LF, based on a DSPFFD to an *OUTFILE from some years ago...

Note that you can also generate DDL using the above tools, even for physical files originally created with DDS, since all externally described database files on OS/400 or IBM i are "*FILE PF-DTA" ... whether created from DDS or SQL DDL... So, you can maintain those files using SQL DDL, going forward, if desired.

Hope that helps,

Mark S. Waterbury

> On 6/27/2017 12:20 PM, Dan wrote:

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