Use this roadmap to learn how to reverse engineer your database objects and replace all DDS-created physical files and logical files with SQL-DDL created tables, views, and indexes. You can also discover more roadmaps for related tasks.
To take advantage of additional roadmap features, such as managing and tracking your progress, you must sign in.
The SQL® programming language and the DB2® for i database are two modern tools available to help IBM® i programmers navigate through the IBM i Developer Road Atlas and modernize their existing applications and toolsets. A vital, but often overlooked, component in the overall IBM i Developer Roadmap process is the modernization of the database. The database is the foundation of most applications, and as such, needs to be given equal consideration in this enhancement process. Without database modernization, applications would continue to use native record-level access methods and would not be able to take advantage of many new database features and enhancements that are only available through the DB2 for i SQL interfaces. A deeper review of the benefits of using SQL to modernize databases is found in the DDS and SQL - The Winning Combination for DB2 for i white paper.
The complete database modernization process is divided into three roadmaps. In this, the first roadmap,Modernizing Data Definitions and Usage, we cover the process of migrating database definitions and usage over to SQL. In the second roadmap, Modernizing Data Access with SQL, we focus on the conversion of your application's data access methods from native record-level access interfaces to SQL. In the third and final roadmap, Optimizing SQL Performance, we detail the process of optimizing SQL-based database access.
While DB2 for i includes a utility to reverse engineer database objects created via Data Definition Specification (DDS) to SQL Data Definition Language (DDL) schema. A point that must be made clear is that the conversion process detailed in this roadmap is not required nor is it a prerequisite step for roadmap two (Modernizing Data Access with SQL) or roadmap three (Optimizing SQL Performance). A move to databases created with SQL is, however, recommended for the following reasons:
- Enables usage of database features such as the XML data type and identity column attribute that are only available to SQL-created databases.
- Provides better integration with client-based data modeling and management tools that only support SQL databases.
- Provides better compatibility with other IBM DB2 products and strategic application development platforms such as Rational Developer for Power Systems Software.
Once you have modernized data definitions by creating your database objects with SQL using the methodology described in this roadmap, legacy programs can continue to use native I/O record-level access. While there are a few instances where programs need to be recompiled, one of the goals of this roadmap is to minimize the impact of the SQL conversion to the existing programs.
As an alternative, if you want to use a new DB2 feature that?s only supported by SQL, such as Grouping Sets, and do not have time to convert the database objects from DDS to SQL, your programs could be changed to use SQL to aggregate the data even though the underlying objects are not defined with SQL. Similarly, you could implement SQL constraints or triggers against DDS-created files. The flexibility of the DB2 for i database means you can mix and match the usage SQL and native non-SQL interfaces, regardless of whether the objects were created by DDS or SQL.
Again, keep in mind that converting from DDS to SQL is not a required step in the overall database modernization process. The objective of this roadmap is to provide you with enough information to make the best decision for your company and help you perform the DDS to SQL conversion successfully and with minimal disruption.