Converting an old Physical File (PF) to a modern SQL table can significantly enhance your database’s functionality and performance. Here are the general steps to achieve this:

1. Analyze the Existing PF: Understand the structure and data types used in your PF. This will help in mapping the fields correctly to SQL data types.
2. Create the SQL Table: Define the new SQL table using the CREATE TABLE statement. Ensure that the data types and constraints match those of the original PF.
3. Data Migration: Transfer the data from the PF to the new SQL table. This can be done using SQL scripts or data migration tools.
4. Update Application Code: Modify any application code that interacts with the PF to use the new SQL table instead. This might involve updating SQL queries and data access logic.
5. Testing: Thoroughly test the new SQL table to ensure that it functions correctly and that all data has been accurately migrated.

Here’s a simple example of how you might convert a PF to an SQL table:

-- Example PF structure
CREATE TABLE OldPF (
    ID INT,
    Name VARCHAR(100),
    DateCreated DATE
);

-- New SQL table
CREATE TABLE NewSQLTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    DateCreated DATE DEFAULT CURRENT_DATE
);

-- Data migration
INSERT INTO NewSQLTable (ID, Name, DateCreated)
SELECT ID, Name, DateCreated FROM OldPF;

Creating SQL TABLES is easy – so How do we convert old DDS to SQL?

Converting old AS400 DDS (Data Description Specifications) to IBM i SQL (Structured Query Language) can be done using a few different methods. Here are some steps you can follow:

1. Using the QSQGNDDL API:
   • IBM provides the QSQGNDDL API, which can convert DDS files into SQL DDL (Data Definition Language) tables. This API creates a new DDL table object with the same structure as the DDS file.
2. Using SQL Scripts:
   • You can manually write SQL scripts to recreate the DDS file structure in SQL. This involves defining the table structure, data types, and constraints in SQL.
3. Using Tools and Utilities:
   • There are tools available that can automate the conversion process. For example, RTVDDLSRC can read the DDS file and generate the corresponding SQL DDL statements.
4. IBM i Access Client Solutions:
   • IBM i Access Client Solutions (ACS) can generate SQL from physical files. This feature is available through the Run SQL Scripts tool within ACS. You can use the Generate SQL option to convert physical files to SQL tables.

How about trying this in the real world?

After an email from a subscriber, let’s take his real-world DDS Physical File and modernize it to SQL using two different techniques: