Generating DDL Source Using a CL Command

CL command QSQGNDDL provides a mechanism to obtain SQL DDL source.

CL command QSQGNDDL provides a mechanism to obtain SQL DDL source.

The Generate Data Definition Language (QSQGNDDL) API interface can be fairly intimidating upon first exposure, especially if you want to employ the API for a variety of SQL objects. This article contains an easy-to-use, front-end CL command along with a C program containing a QSQGNDDL API example for you to modify and use for your needs. The CL command gives you a mechanism to use interactively to obtain SQL data definition language (DDL) source or in an application program.

Programmers need tools to help simplify programming. If the source code for the creation of an object is lost or misplaced, then the following question arises: What's the easiest, simplest and most cost-effective solution to regenerate or replace the source code?

This article demonstrates a tool (with the source code) that can be used to generate SQL statements from the original database object. You'll find the source code, which you can extract and modify for the tool, here.

The tool is composed of a CL command, a CL program and a C program. The CL command, Gen-erate DDL Source (GENDDLSRC), consists of a set of parameters that have prompt support - or parameters to specify the object, type, generated source-code destination - and a set of attributes to be used in generating the SQL statements. The CL program is the Command Processing Program (CPP) for the command. It passes information to the C program, which invokes the QSQGNDDL API with the user-specified parameters.

Some readers may wonder why we're writing about a CL command when the IBM* iSeries* Navigator provides a Generate SQL interface. Both iSeries Navigator and this CL command have advantages. Some programmers prefer the iSeries Navigator's GUI over green screens. But the advantage of a CL command is that it can be used interactively and coded in programs or batch jobs. Also, if you don't have the iSeries Navigator client installed, it would be easier to use the GENDDLSRC command. A CL command can also be fast and easy to modify for CL variables in a CL program. So, while both iSeries Navigator and this CL command provide the capability to simplify programming, it's nice to have options. This CL command interface is another tool you can use for a needed solution.

We've provided the source code for all three objects (via the aforementioned Web address) and a recommended command to create each object. You can tailor the source code to fit your environment. One approach is to have a scaled-down version of the CL command that has fewer parameters and use more constants for the QSQGNDDL attributes in the C program. For this example, we used a CL program so the job attributes could be extracted for the date and time parameters to be passed to the C program.

Code Sample 1 is the full prompt version of the GENDDLSRC command. The GENDDLSRC command supports most of the parameters allowed by the QSQGNDDL API. The available parameters specified on the command are passed to the QSQGNDDL API. This means the QSQGNDDL API documents all of the values the command supports.

Mark W. Theuer is an advisory software engineer with IBM. Mark can be reached at

Mike Venz is an advisory software engineer in the System i database organization. Mike can be reached at

Rafal P. Konik is a staff software engineer in the System i database organization. Rafal can be reached at

comments powered by Disqus



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Are You Multilingual?

Rational enables development in multiplatform environments

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters