You are currently on IBM Systems Media’s archival website. Click here to view our new website.



IBM has long been advising IBM i developers and architects about the benefits of moving to an SQL-based development model. This begins with using DDL (Data Definition Language) versus DDS (Data Description Specification) to define how are data is registered on IBM i.

Many have listened to but few have heeded the advice. But that all changes when your boss or client walks into your work area and declares, “We need to adopt SQL and convert all our DDS files to DDL files…now.”

To help you when that moment inevitably comes, this article will show how to start the process by reverse engineering your DDS-based physical files/logical files to DDL source using a common development approach and a utility called GENDDL.

In 2002, Ted Holt authored an article about a utility he wrote that he called GENDDL. You can now download it from This utility executes the Generate Data Definition Language (QSQGNDDL) API that IBM provided in V5R1. You can read about it on the IBM website.

Converting existing DDS created tables, and any DDL created tables for that matter, to DDL source members is just the start to your DB2 modernization efforts. The conversion of the DDS to DDL source is just the first of a long list of tasks that must be undertaken to fully adopt the IBM roadmap of becoming data centric and adopting SQL as the preferred development framework.

Front-End Processing

I began by coding a command interface to gather data required for Holt’s utility GENDDL program execution. Our command shown in Code Sample 1 accepts three parameters: table schema, generated source file and schema.

Our panel group shown in Code Sample 2 can be accessed by pressing the F1=Help key anywhere on the command screen and is cursor position sensitive.

Our validation-checking program shown in Code Sample 3 validates entered command parameter data before initiating the program to process.

Once all input has been validated, control is passed to our program to process module Code Sample 4 and execution continues.

I used the System Cross Reference File (QADBXREF) in the QSYS schema to retrieve all the physical files, logical files, tables, indexes and views found in the specified schema in our command input. Reading through these retrieved values, I bypass any nondata tables (DBXTYP <> ‘D’) and based on the file attribute (DBXATR) execute the GENDDL processor passing values retrieved from the QADBXREF table to create a DDL source member in the specified source file.

Executing the Utility and Reviewing the Output

To execute the utility from a command line, prompt the GENDDLSRC command and enter the required parameters for your test.

The results of the processing will be DDL source members in our specified source file as shown in Figure 1. The utility gives us the basis for a start to our DB2 modernization efforts as the source has been generated for us. This sample shows our successfully generated source Figure 2 and, in the event there was a problem in the conversion, the following source is generated Figure 3.

Look to the Future

The IBM roadmap is a phased approach of discovering, migration, isolation and integrity for your existing data and applications so automating what can be done to help is a great stepping-off point. By combining an existing utility with a process to automate the generation of DDL source for our existing files, we can begin the process of reviewing and rebuilding how our data is defined to our system as part of our discovering and migration.

David Andruchuk is the senior architect for Computer Systems Design Associates Inc.



2019 Solutions Edition

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


A Debate: DDS vs. DDL

Should you switch your DDS defined files to DDL?

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