Convert Logical Files from DB2 AS/400

DDS (Data Description Specifications) is a specification language used to describe the structure of tables, indexes, and views. It was introduced in early versions of DB2 for AS/400, prior to full support for SQL DDL. In older versions of DB2 for AS/400 (up to approximately V5R4), DDS was the only standard method for defining database files. In modern versions of IBM i (7.x and later), SQL DDL is the standard. DDS remains supported for backward compatibility with legacy applications; however, it is no longer actively developed. For example, DDS does not support temporal tables or advanced constraints.
Thus, a DDS object in DB2/400 is created using a plain-text DDS source file, which contains object definitions such as: Physical files, which correspond to tables and include:

1. Columns (fields) with their data types, lengths, nullability, etc.
2. Keys — primary keys (key fields) that define record uniqueness and physical data order.

Physical files do not include:

1. Constraints such as CHECK, FOREIGN KEY, or UNIQUE, which are common in standard SQL — these are not defined in DDS for physical files.
2. Data integrity constraints other than keys are absent.
3. Logical constraints (e.g., value validations) are typically implemented at the application level or through logical files.

Logical files are used to create indexes and view-like objects. They can implement alternate keys, filters, and sorting.

The DDS source file is compiled into a physical database file using special commands such as CRTPF and CRTLF.

For example:

CRTPF FILE(MYLIB/EMPLOYEE) SRCFILE(MYLIB/QDDSSRC) SRCMBR(EMPLOYEE)

1. FILE — the name of the created database file object.
2. SRCFILE — the source physical file containing the DDS definitions.
3. SRCMBR — the member in the source file that contains the DDS source.

After execution, a physical database file matching the DDS structure appears in the MYLIB library.

Objects created via DDS (using CRTPF or CRTLF) do not always appear in SQL system catalog tables. Starting with IBM i version 5.4, they are partially visible, and only from version 6.1 and later do they fully populate the system catalog tables. Because of this incomplete or missing visibility in system tables, conversion of such objects can present challenges.

In order to convert logical files from DB2 AS400, we need to have DDS file for each logical file that should be converted. A path to the folder where these DDS files are located on the machine where the conversion process is running should be specified in “Advanced” window on “Choose a Source Database”.

After that on “Specify Database Objects” page you will need to select the required logical files from the list of extracted objects.

Our tool will read information about objects extracted from DB2 database and when the tool will define that the object is a Logical File, it will check the folder with DDS files and will try to read the structure of this logical file from the appropriate DDS file.

Here you can find a step by step guide on how to extract the DDS files for logical files that should be converted: How to Extract Logical and Physical Files from DB2 iSeries (AS/400).

Please note:
If you are using DB2 iSeries 7.2 version or higher, you don't need to extract the DDS files and provide a path to them. You just need to specify logical files for conversion on the “Specify Database Objects” page, the way you do it with other object types.

If you have any additional questions regarding the migration of Logical files from DB2 AS400, please contact our support team: support@ispirer.com