IBM i modernization – Extended database : “DDS Versus DDL”

Modernización de IBM i: base de datos ampliada: “DDS Versus DDL”

IBM i modernization – Banco de dados estendido: “DDS Versus DDL”



Scope of this study is to move into the first big step of the pattern defined in the post:

moving to “IBM i modernization”.

In this post I would analyze and synthetize the main reasons will suggest all to realize this process (from DDS to DDL), avoiding technical details and reminding to RedBooks or more specialized posts for this scope.

The debate about DDS and DDL is a bit complex than waited and not related only to these aspects. SQL too plays a decisive role in this evolution, but project needs must be considered too, especially in case you are analyzing old applications to be transformed in modern applications. In this case you have surely two questions to answer:

  • do you need to convert all your DDS PF to DDL tables?
  • do you need to convert your traditional I/O RPG programs to embedded SQL?

But the main mission of this process is surely to separate your programs from the data model: this will give you the capability to change your tables (add, modify, delete columns) and have more flexibility to answer to the different market needs. This become to be a change of view from program-centric architecture to data-centric architecture.


A bit of history and terminology

In the past days DDS (Data Description Specification was the most common and efficient way of creating data through Physical and Logical files and Record Level Access (RLA). Although SQL was available, the SQL engine was painfully slow so developers was discouraged from using SQL in the development process. As a result many of the System i applications in use today still use DDS with record level access to the DB2-400 files through RPG and COBOL programs.

IBM has greatly improved the SQL Query Engine and now it is, generally, more efficient than DDS files. The recent improvements of IBM are addressed only to SQL and SQL Query Engine and the big evolution of the hardware (disk access speed, processor speed, etc) assure this database management to be the most efficient. So many companies are now using SQL-DDL for all new applications and they are converting their old applications.


Advantages of DDL

Different are the advantages of DDL versus DDS and here a list of them, but surely the most important is the flexibility, more than the performance that is a kind of side effect.

  • Compliance with Industry Standard

SQL/DDL is the most widely used standard across Relational Data Base Management Systems for creating database objects.

  • Data integrity

Because data validation is done when data is added as opposed to when data is read, it helps to insure the reliability of data in your database.

  • Performance

All the IBM’s investments are on the SQL area and no enhancements are dedicated to the DDS architecture.

  • Functionality

Some new functions require SQL.

  • System Openness

The use of modern technologies to maintain and access your database give you more options to integrate your database with third-party tools.

  • Flexibility

This point is surely the most important of those listed and it is the driving reason that will drive you into the process from DDS to DDL. The new approach that surely you must consider is the data-centric architecture versus the program-centric architecture.

In a program-centric approach, the programmer decides what files and determines what keys the program will access data from. The program will use traditional I/O retrieving data by one row at a time. The business rules are imbedded into the program and if the file changes, the program must be changed or recompiled. Data is related to each other by using primary keys created by using business data.

In a data-centric approach, the program requests data from the database management system (DBMS) via SQL statements and the DBMS decide the best approach for accessing data. Common business rules are created as constraints in the DBMS creating consistency and accuracy of business rule enforcement. You can make changes to the tables (i.e., add new columns, change or drop existing columns) without worrying about programs that access the table but don’t care about the column change. The DBMS understands the relationships of the tables by use of primary key (PK) and foreign key (FK) constraints. PK/FK relationships are nonbusiness data, usually via use of identity columns in each table. An identity column is a DBMS-controlled column that automatically increments when new rows are added to the table. It’s protected, and there’s no need to maintain it. We add identity columns to every table we modernize. This prepares us to replace our business keys with “dumb” identity keys so business data isn’t duplicated across our database.


DDS to DDL Conversion

Different solutions and tools are available to the DDS-DDL conversion.

As first this you could us “genddl” tool, that generate single “.sql” script file containing all DDL information in the database schema. The tool is under AFL and GNU licence.

The IBM too, developed the procedure:


The GENERATE_SQL procedure generates the SQL data definition language statements required to recreate a database object. The results are returned in the specified database source file member or as a result set.

If you want something of more complex and complete you should buy XCASE. You have the possibility to download a trial edition ( with the following content:

  • The free Xcase DB2 Modernization Diagnostic utility. This tool generates a report that details exactly what will be required to modernize your specific database from DDS to SQL DDL in accordance with IBM’s published best practices.
  • A trial version of the Xcase Evolve-DB. This includes all of the functionality you need to create, reverse engineer, modify and maintain SQL databases on the IBM i, and it remains fully functional for one month.

Here the links of the tools:!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.GENERATE_SQL()%20procedure



At the end I think no doubts, in case of new application, to write it in a modern way: DDL and embedded SQL. In case of old application you should evaluate and do it at least for main functions and most used database files and postpone or avoid this upgrade for all the other. This will let you surely to modernize the core business of your application without pay too much for a complete transformation of your environment.


For more details consult the links:–dds-vs–ddl/

Leave a Reply

Your email address will not be published. Required fields are marked *