Straight Conversion – Keeping Field Sizes Identical so Existing Code Continues to Work
This is the lowest-risk first step and the one most shops start with.
The goal is simple: turn the old DDS Physical File into an SQL table that has the same field names, same data types and same lengths. When that is true, the Record Format Level Identifier stays the same. Programs compiled against the old file open the new table without a level check and without needing a recompile.
Practical conversion steps
- Identify a good candidate table (start with something moderately used, not the absolute busiest file on day one).
- Generate the SQL definition. Two reliable methods:
- IBM i Access Client Solutions → Schemas → right-click the Physical File → Generate SQL.
- The system procedure QSYS2.GENERATE_SQL (or the newer GENERATE_SQL_OBJECTS).
- Review and lightly clean the generated script. Keep the short system names if you want zero program impact.
- Create the new table (often with a temporary name first).
- Copy the data across (CTAS or INSERT…SELECT).
- Verify the format level identifier with DSPFD.
- Test the main programs that use the file.
- Once happy, swap names or use a surrogate approach so the original name points at the new SQL table.
Example – classic customer master
Old DDS (typical AS/400 style):
A CUSNUM 7S 0
A CUSNAM 30A
A CUSADR 40A
A CUSZIP 5A
A CUSCRD 9P 2
A CUSDT 8S 0
A K CUSNUM
Generated and cleaned SQL that keeps everything the same externally:
CUSNUM DECIMAL(7, 0) NOT NULL DEFAULT 0,
CUSNAM CHAR(30) NOT NULL DEFAULT '',
CUSADR CHAR(40) NOT NULL DEFAULT '',
CUSZIP CHAR(5) NOT NULL DEFAULT '',
CUSCRD DECIMAL(9, 2) NOT NULL DEFAULT 0,
CUSDT DECIMAL(8, 0) NOT NULL DEFAULT 0,
PRIMARY KEY (CUSNUM)
)
RCDFMT CUSREC;
Because the record format name and the field definitions match, existing RPG code such as:
chain cusnum cusmast;
if %found(cusmast);
// process
endif;
continues to work exactly as before. Native I/O, OPNQRYF, OVRDBF and most CL file overrides keep functioning.
Useful tips for this stage
- CREATE OR REPLACE TABLE is extremely helpful. It can replace a DDS file while preserving data and many dependent objects.
- You can add longer SQL column names later using the FOR COLUMN clause or by renaming. The short system name stays for compatibility.
- After creation always run DSPFD and compare the Format Level Identifier with the old file.
- Journaling, authority and ownership should be reviewed and set correctly on the new table.
- If you need an interim safety net, create a surrogate Logical File with the original name that points at the new SQL table and shares the original format. Existing programs open the surrogate and never notice the change.
This straight conversion gives you the performance benefit of SQL table validation timing and better optimizer statistics while leaving your application code untouched.