Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

Chapter 3. Approaches and options 25

constructed, and prepared at run time. The source form of the statement is a character or

graphic string that is passed to the database manager by the program using the static SQL

PREPARE or EXECUTE IMMEDIATE statement. The operational form of the statement

persists for the duration of the connection or until the last SQL program leaves the call stack.

Access plans associated with dynamic SQL may not persist after a database connection or

job is ended.

Extended dynamic SQL

An extended dynamic SQL statement is neither fully static nor fully dynamic.

The QSQPRCED API (Process Extended Dynamic SQL) provides users with extended

dynamic SQL capability. Like dynamic SQL, statements can be prepared, described, and

executed using this API. Unlike dynamic SQL, SQL statements prepared into a package by

this API persist until the package or statement is explicitly dropped.

The iSeries Access Open Database Connectivity (ODBC) driver and Java Database

Connectivity (JDBC) driver both have extended dynamic SQL options available. They

interface with the QSQPRCED API on behalf of the application program.

For more information about SQL in DB2 UDB for iSeries, refer to the DB2 Universal Database

for iSeries SQL Reference in the iSeries Information Center at:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/ic2924/index.htm?info/db2/rbafzmst.ht

mL

3.3 Methodology for the modernization

The IBM Rochester iSeries Services Group has developed a methodology for reverse

engineering a DDS-created database along with modernizing applications to use SQL as the

database access method. We will be using this methodology in this book. This methodology

consists of four stages, which will be briefly described in this section. In the following chapters

we go into much more detail.

The four stages of the methodology are:

1. Reverse engineering DDS to SQL DDL

2. Creating I/O modules to access DB2 data

3. Moving business rules to the database

4. Implementing advanced database functions

This methodology is based on existing applications that access the database via high level

language (HLL) I/O operations commonly referred to as native support.

3.3.1 Reverse engineering DDS to SQL DDL (stage 1)

The main objective of this stage is to replace the DDS-created physical files and access paths

with SQL-created tables and indexes. There may be some instances where programs need to

be recompiled. At this point legacy programs can continue to use native I/O techniques.

Database changes will be done via SQL.

In Chapter 4, “Modernizing database definitions” on page 29, we cover all of the steps

involved in this first stage of the process.