Modernizing IBMi Systems: The Surrogate Method for Database Evolution

Introduction

When faced with the need to modify Physical Files (PF) by adding new columns, legacy programs encounter a significant challenge as they require recompilation, leading to a tedious and time-consuming process. As part of our IBMi modernization efforts, transitioning to SQL tables from Physical Files presents a solution to this issue. This migration facilitates updates to the database structure without necessitating the recompilation of existing RPG programs that reference specific Physical Files.

The Surrogate Method

In the surrogate method, an SQL table is created with a new name, incorporating all existing columns from the original physical file along with any additional ones required. Subsequently, a DDS logical file is generated, mirroring the name and record format of the old physical file, and is overlaid onto the new SQL table. Moreover, any pre-existing DDS logical files are retained but are redirected to the new SQL table. This method also allows for the incorporation of additional SQL indexes and views with new identifiers.

Step-by-Step Implementation

1. Generate the Data Definition Language (DDL) from an existing PF using the GENERATE_SQL procedure. This action produces the DDL from the PF object and stores it in the specified member using GENERATE_SQL. The table name can be suffixed with any character; for instance, "SQ_CALL SYS2."

2. Modify the generated source to reflect the new column names and proceed with creating the table accordingly.

3. Transfer records from the old table to the new SQL table.

4. Create the logical file using the old PF name and designate the new SQL table as the primary file (PFILE), maintaining the original record format name. For existing logical files, update the references to point to the new SQL table and recompile them as necessary.

Conclusion

Adopting the surrogate method empowers organizations to evolve their PFs into SQL tables seamlessly, circumventing the need to recompile existing RPG programs. This approach streamlines the modernization process, enhancing database flexibility and compatibility with contemporary systems while minimizing disruption to legacy applications.