James Allman | JA Technology Solutions LLC
DDS to SQL: Modernizing IBM i Database Definitions
Most IBM i databases were defined with DDS. Moving that definition to SQL DDL is one of the most concrete, low-risk steps in a modernization effort, and it unlocks capabilities DDS cannot provide.
If you have spent time on IBM i, you know DDS. Every physical file has a source member in QDDSSRC, a columnar format with field names in positions 19 through 28, type codes in position 35, and lengths and decimal positions in the columns after that. For decades this was the only way to define a database object on the platform, and thousands of production systems still rely on it.
SQL DDL is available on IBM i as well, and IBM has recommended it for new development for some years now. Moving existing DDS-defined files to SQL is one of the most concrete steps in an IBM i modernization effort. It does not require rewriting RPG programs. It does not move data off the platform. It changes how the database objects are defined and managed, and that change unlocks capabilities that DDS cannot provide. This article covers what DDS is, how it maps to SQL DDL, what you gain by moving, and what to watch during the transition.
What DDS defines and what it produces
DDS is used to define three kinds of IBM i objects. A physical file (PF) defines a table: its fields, their types and lengths, and an optional keyed access path. A logical file (LF) defines an access path over one or more physical files: a selection of fields, an optional record-selection predicate, and a key sequence. A display file (DSPF) defines a 5250 screen layout: fields, constants, function-key attributes, and the row and column positions of everything on the screen. The compiler produces an IBM i object from the source, and programs reference those objects by name.
Field names in DDS are limited to ten characters. Types are expressed as single-letter codes: A for character, P for packed decimal, S for zoned decimal, B for binary, L for date, T for time, Z for timestamp. Keywords appear in positions 45 onward and control behavior: UNIQUE marks a key constraint, DESCEND inverts a key direction, COLHDG provides a column heading for display use. The format is compact and precise, but it is also entirely IBM i specific: a DDS source member is not readable to anyone outside the platform ecosystem without explanation.
How DDS maps to SQL DDL
A DDS physical file maps directly to a SQL CREATE TABLE statement. Each field becomes a column. The DDS type codes translate to standard SQL types: A maps to CHAR or VARCHAR, P to DECIMAL, S to DECIMAL or NUMERIC, B to SMALLINT or INTEGER, L to DATE, T to TIME, Z to TIMESTAMP. The ten-character DDS field name becomes the column name in SQL, though this is the most common place where teams take the opportunity to use longer, more descriptive names.
A DDS logical file maps to either a SQL CREATE INDEX or a CREATE VIEW, depending on what the LF does. A keyed LF with no field selection and no record selection maps cleanly to an index. An LF that selects a subset of fields, reorders them, or applies a record-selection predicate maps to a view. Some LFs combine both behaviors, which requires a view built over an indexed base table. The distinction matters when converting: a program that opens an LF for keyed reads is using it as an index; one that reads it sequentially with field selection is using it as a view.
DDS key fields map to primary key and unique constraints in SQL, or to an index with the same key columns if the constraint belongs on the base table separately. The DESCEND keyword, which inverts the sort direction of a key field, maps to DESC in the index definition. The DDS to SQL / SQL to DDS Converter handles these mappings in both directions across five SQL dialects, and the DDS Screen Explorer can show you a DDS source member in a readable layout before you start converting.
What you gain by moving to SQL DDL
Column names longer than ten characters are the most immediately visible benefit. DDS field names are limited to ten characters, so names like ORDNUM, CUSNAM, ORDAMT, and INVDTE are common. SQL has no such limit. The same fields can become ORDER_NUMBER, CUSTOMER_NAME, ORDER_AMOUNT, and INVOICE_DATE without any change to the underlying data. Every tool that reads the table sees the descriptive names: IBM i Navigator, SQL clients, JDBC-connected reporting tools, and any program that uses embedded SQL.
Beyond naming, SQL DDL unlocks database features that DDS cannot provide. Identity columns generate sequential values automatically, replacing the application-level sequence logic many RPG programs carry today. Check constraints enforce value rules at the database level rather than in every program that writes to the file. Referential integrity constraints define foreign-key relationships that the database enforces rather than trusting each program to maintain. Triggers run SQL or external programs automatically on insert, update, or delete. None of these are available through DDS.
The DB2 for i query optimizer also works better with SQL-defined objects. SQL tables accumulate optimizer statistics that the optimizer uses to choose access paths for complex queries. Logical files defined in DDS are simpler access paths; SQL views and indexes give the optimizer more information and more flexibility. For any shop that runs embedded SQL in RPG or queries the database from external tools, the optimizer improvement is real.
Journal definitions, which are required for commitment control and replication, can be specified in DDL when creating a SQL table. This brings journaling configuration into the same DDL source that defines the table, rather than leaving it as a separate manual step. It also makes the configuration visible to anyone reading the DDL, instead of being implicit in the job log from when the file was created.
What to watch during the transition
RPG programs compiled against a DDS physical file include the record format name as part of their object binding. The format name is the first keyword in the DDS source, typically the file name with an R suffix: a file named ORDHDR has a format named ORDHDRR. When a program uses native I/O operations to read or write that file, the compiled object includes the format name and the field layout at compile time. If you rename the file or change the format definition in ways that break the expected layout, programs that use native I/O will fail at runtime with a record format level check error.
SQL-defined tables do not use record formats in the same way. A program using embedded SQL to access the table is not bound to a format name; it selects columns by name. A program using native I/O through a DDS-defined format is still bound to the format. The practical consequence is that the safest migration path is to convert the DDS source to SQL DDL and recreate the same object with the same external field names and the same format name if possible, verify that programs compile and run correctly, then expand column names and add constraints in a second step. Changing everything at once is harder to validate.
Logical files that are used by programs for keyed access need a corresponding SQL index. Programs that open an LF and read it sequentially with a record selection depend on a view that produces the same field set and selection as the LF. When an LF does both, the view and the index need to work together. Most cases are straightforward once you categorize how each LF is actually used; the complication comes from LFs that are shared across many programs, some using keyed access and others sequential.
DDS keywords with no SQL equivalent need individual attention. ALIAS, which provides an alternate column name for display use, is handled differently in SQL through LABEL ON statements. COLHDG, the column heading for 5250 display use, is also a LABEL ON. REFSHIFT, CHKMSGID, and other DDS-specific keywords have no SQL equivalent at all. These are typically display or validation behaviors that belong in the application layer anyway, so removing them from the file definition is usually correct; they should not be silently discarded without understanding what behavior they provided.
Display files stay in DDS
The one DDS object type that has no SQL equivalent is the display file (DSPF). Display files define 5250 screens: field positions, protection attributes, function-key definitions, error message handling, and the row and column layout of every element on the screen. There is no SQL DDL equivalent for any of this, because SQL describes data, not screen geometry.
Moving from a 5250 green-screen interface to a web-based or mobile interface is a separate modernization question that involves the presentation layer, not the database layer. The usual approach is to wrap the RPG business logic in web service calls (as described in the RPG modernization article) and build a modern front end that calls those services, leaving the display files behind entirely. Converting the DDS database definition to SQL DDL does not require any change to the display files, and migrating the display files does not require changing the database definition. The two efforts are independent and can proceed separately.
If you are exploring what a 5250 display file actually contains before deciding what to do with it, the DDS Screen Explorer renders the screen layout in a browser and shows the field attributes, positions, and lengths.
Doing it incrementally
A useful rule for this kind of work: convert files that are actively causing problems first, not the entire schema at once. A physical file where RPG programs use embedded SQL to access it is already partway there; the programs do not depend on the DDS format name for their SQL access paths. Convert that file to a SQL table first, validate the programs, and use what you learn from the first conversion to calibrate the effort for the rest.
Files that are accessed exclusively through native I/O by compiled RPG programs carry the highest conversion risk, because those programs must be recompiled after the file definition changes. Identify them with a cross-reference tool or DSPPGMREF before you change the file definition. The cross-reference tells you every program that references the file and what operations it performs. That list is your regression test scope for that file.
For most shops, a complete DDS-to-SQL conversion of the entire database is a multi-year effort, not a sprint. The value comes from starting, not from finishing everything at once. Each converted file is a file where you can use standard SQL tooling, add constraints, and write clean embedded SQL without a DDS format in the way. The DDS to SQL / SQL to DDS Converter converts individual file definitions in either direction as a starting point for each conversion. For the broader effort of mapping cross-file dependencies, validating the schema, and coordinating the program recompiles, that is where IBM i modernization consulting fits in.
The IBM i platform is built around DB2 for i, and DB2 for i supports the full SQL standard. DDS was the original way to reach it; SQL DDL is the current way, and the one IBM has invested in for new features. Moving existing DDS definitions to SQL is not a prerequisite for everything else in a modernization effort, but it is one of the safest and most concrete steps available: the data stays in place, the platform does not change, and the programs can be migrated one at a time.
If you are working through a DDS-to-SQL conversion and want to talk through the approach for your specific environment, get in touch or use Ask James to describe what you are working with. The IBM i consulting work I do often starts exactly here, and modernization services cover the full spectrum from file definition conversion through RPG code updates and application layer changes.