Database Modernization: How to Convert DDS to DDL

Part 1 of this series focused on IBM’s ongoing major initiative across the IBM i platform called database modernization. Part 2 answered why you’d want to convert your system to DDL and embedded SQL. Here in Part 3, I’ll show you how to convert your existing DDS-described PF/LF to DDL-described Tables/Indexes/Views and how to do it without recompiling your existing programs.

Some good articles on the subject are already written, including “DDL Your DDS” by my friend David Andruchuk, which is a good starting point to understanding how to get from DDS to DDL. But the real challenge is how to convert your data to SQL-based tables and then implement the changes without touching all your programs. This article provides the specifics.

The Scenario

Let’s say we have a Customer Master PF and LFs in production as shown in Figure 1.

The goal of our modernization process would result in defining the CUSMAS table as a DDL-based SQL table, creating SQL Indexes and then creating LFs to match what our existing production programs are already defined over. Following are the steps I took when I modernized these PF and LFs.

  1. Create a new SQL Table using the definition from the old PF. We name the SQL table filenameSQL. So in our example, we’d name it CUSMASSQL.
  2. Now add any new columns you wish to the new SQL Table. We add an identity column as a Primary Key (PK) and also some change timestamps.
  3. We also add long column and table names to the new SQL Table source. This lets us refer to the table/columns by two different names (i.e., CUSNUM and CUSTOMER_NUMBER ). Our business community likes not being confined to maximum of 10 characters for the column and table name.
  4. If the existing PF is keyed (as in this example), create an index equivalent to the key on the PF. We name the new index filenameIDX. So in our example, we’d name the new index CUSMASIDX.
  5. Create an SQL index for each keyed LF that doesn’t have a SELECT/OMIT criteria or is not a Joined LF. As a standard, our indexes are suffixed with “I0” and “I1” for indexes just like “L0” and “L1” are used for LF.
  6. Change the existing PF to be a LF. In our example, CUSMAS would be changed to a LF and the PFILE keyword is added to point to our new SQL table CUSMASSQL. (e.g., PFILE(CUSMASSQL)).
  7. Change existing LF to now point to the new PF, PFILE(CUSMASSQL).
  8. Change existing LF to add the keyword FORMAT. This will point to the CUSMAS LF. So existing LF will now have the following new line in the source: FORMAT(CUSMAS).

Your new structure should look like Figure 2. It may look a little ominous with all the new objects. However, let me explain what’s going on.

  • CUSMASSQL is now the new SQL table
  • CUSMAS (old PF) is now a keyed logical file with index support from CUSMASIDX
  • CUSMASL0 now has index support from CUSMASI0
  • CUSMASL1 now has index support from CUSMASI1

By index support, I’m referring to the fact that, since the keys are the same, the OS creates one access path and shares it between the index and the LF. Therefore, we’re NOT increasing the number of access paths that must be maintained. Three access paths had to be maintained originally and three access paths must be maintained now. In other words, CUSMAS and CUSMASIDX, CUSMASL0 and CUSMASI0, and CUSMASL1 and CUSMASI1 share access paths and don’t put more maintenance work on the system. In fact, the system is now working less to maintain the same three access paths since index access paths are easier to maintain than LF access paths.

You may ask why we create the indexes if the access paths are exactly the same and are shared between the index and the LF. Indexes serve up 64K page sizes of data into memory where LFs serve up 8K pages of data into memory. That means just by adding indexes to your system with the same key structures as LFs, you’ll recognize a performance gain when accessing data on your system. The key step is the compile of the indexes first. The access path is created using the index definition of 64K page sizes. When you compile the LF second, the OS will piggyback the LF onto the existing 64K page size indexes. This will give a performance boost to your programs as they access the existing LF.

Some caveats:

  • This doesn’t work with multimember files.
  • This won’t work on JOIN LF. Just change it to connect to the new SQL Table but don’t create an index for it.
  • The indexes must be created first on your system so the LF can connect and share the access path.
  • To review the changes we’ve now made, I’ve included the before (Code Sample 1) and after (Code Sample 2) source of our tables, LFs and indexes. You’ll note a new column CUSMASPK in our CUSMASSQL table. This is an IDENTITY column that we add to every table we convert. I’ll explain the usage of new identity columns in the final part of the series. It’s important to note that it doesn’t affect the Format Level ID calculation and can be added without having to touch the programs.

    Additionally, we want to check the format level IDs of our old and new tables. Figure 3 showing the before and Figure 4 showing the after demonstrate how, with changes implemented, our format level IDs haven’t changed—meaning our existing production programs won’t error out when executed. Since the format level IDs are exactly the same before and after, you can put this into production without having to touch your existing programs.

Jim Ritchhart is the manager of database administration for shipping-supplies company, Uline, and is responsible for DB2 for i, SQL Server and Oracle.



2018 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