Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

by Hernando Bedoya, Birgitta Hauser has, Rolf AndrŽ Klaedtke, Sharon Hoffman, Warawich Sundarabhaka
Released February 2005
Publisher(s): IBM Redbooks
ISBN: None

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

In 1978 IBM introduced the System/38 as part of its midrange platform hardware base. One of the many outstanding features of this system was the built-in Relational Database Management System (RDMS) support. The system included a utility for defining databases, screens, and reports. This utility used a form named Data Description Specifications (DDS) to define the database physical (PF) and logical (LF) files (base tables, views, and indexes).

In 1988, IBM announced the AS/400. The OS/400 operating system also contained a built-in RDMS, however, IBM offered Structured Query Language (SQL) as an alternative to DDS for creating databases. In addition, SQL Data Manipulation Language (DML) statements were made available as an ad hoc query language tool. These statements could also be embedded and compiled within high level language (HLL) programs.

SQL Data Definition Language (DDL) has become the industry standard for defining RDMS databases.

Many customers are in the process of modernizing their database definition and the database access. This IBM Redbooks publication will help you understand how to reverse engineer a DDS-created database along, and provides you with tips and techniques for modernizing applications to use SQL as the database access method.

Additional Resources on DB2 for i Modernization:
DDS and SQL - The Winning Combination for DB2 for i Modernization
Case Study:Modernizing a DB2 for i Application
Moving from OPNQRYF to SQL
Case Study:DB2 for i SQL Performance
DB2 for i Modernization Workshop
Modernizing Database Access - The Madness Behind the Methods

Table of contents

  1. Notices
    1. Trademarks
  2. Preface
    1. The team that wrote this redbook
    2. Become a published author
    3. Comments welcome
  3. Part 1: Introduction and background
    1. Chapter 1: iSeries Developer Roadmap - The big picture
      1. Introduction to the iSeries Developer Roadmap
        1. Why a roadmap
        2. Why care about it
        3. The goal
      2. What is in the roadmap
        1. Better tools
        2. Better user interface
        3. Better architecture
        4. Better portability
        5. Better scalability
      3. What is in this book
    2. Chapter 2: Why modernize with SQL and DB2 UDB for iSeries
      1. Background
        1. A short look at the history of SQL
        2. The main parts of SQL
      2. Reasons to modernize
        1. Standard compliancy
        2. Openness
        3. Performance
        4. Available skills
        5. Functionality
        6. Data integrity
  4. Part 2: Data definition
    1. Chapter 3: Approaches and options
      1. Data definition considerations
      2. Accessing the database data
        1. Native record level access
        2. Data access with SQL
      3. Methodology for the modernization
        1. Reverse engineering DDS to SQL DDL (stage 1)
        2. Creating I/O modules to access DB data (stage 2)
        3. Moving business rules into the database (stage 3)
        4. Externalizing data access (stage 4)
    2. Chapter 4: Modernizing database definitions
      1. Reverse engineering DDS to SQL DDL (1/4)
      2. Reverse engineering DDS to SQL DDL (2/4)
      3. Reverse engineering DDS to SQL DDL (3/4)
      4. Reverse engineering DDS to SQL DDL (4/4)
        1. Classify the existing environment
        2. Establishing a list of all DDS files to be converted
        3. Establishing naming conventions for SQL objects
        4. Converting the DDS to SQL DDL
        5. Reviewing the generated SQL DDL
        6. Creating the new DB2 schema on the iSeries server
        7. Create all existing DDS logical files over the new SQL tables
        8. Migrate data and test existing programs
      5. Comparing the SQL objects and the DDS files
        1. SQL tables compared with physical files
        2. SQL indexes compared with keyed logical files
        3. SQL views compared with logical files
        4. SQL data types
      6. SQL system catalogs: Definitions
        1. SQL system catalogs: Example
      7. Partitioned tables
  5. Part 3: Data access
    1. Chapter 5: Creating I/O modules to access SQL objects
      1. Introduction
      2. Establish naming conventions
      3. Create SQL views based on business requirements
      4. Create service programs to access data from the SQL views
      5. Convert legacy programs to use service programs
    2. Chapter 6: Moving business rules to the database
      1. Database normalization
      2. Referential integrity
      3. Constraints
      4. Constraint coexistence considerations
      5. Column-level security
      6. Column encryption
      7. Automatic key generation and unique identifiers
      8. Accessing non-relational data
        1. User defined table functions for accessing non-relational data
        2. Datalink
        3. Large Object Support
    3. Chapter 7: Embedded SQL
      1. How to get started
      2. Creating a SQLRPG - Program/service program/module
      3. Compile command CRTSQLRPGI
        1. Missing compile options in the SQL compile command
        2. Important compile options for SQL statements
        3. SET OPTION statement
      4. Error handling - SQLCA (SQL communications area)
        1. SQLCODE
        2. SQLSTATE
      5. Host variables
        1. Single field host variable
        2. Host structure
        3. Host structure array
        4. Naming considerations for host variables
      6. Exploiting SQL scalar functions in RPG
      7. Static SQL without cursor
        1. Static SQL returning a single row
        2. Processing non-Select statements with static SQL without cursor
      8. Using a cursor (1/3)
      9. Using a cursor (2/3)
      10. Using a cursor (3/3)
        1. The DECLARE statement
        2. The OPEN statement
        3. The FETCH statement
        4. Types of cursors
        5. Updating or deleting rows using a cursor
      11. Dynamic SQL (1/2)
      12. Dynamic SQL (2/2)
        1. Defining the character string containing the SQL statement
        2. The EXECUTE IMMEDIATE statement
        3. Combining the SQL statements PREPARE and EXECUTE
        4. Combining the SQL statements PREPARE and DECLARE
        5. The SQL descriptor area
    4. Chapter 8: Externalizing data access
      1. Trigger programs (1/4)
      2. Trigger programs (2/4)
      3. Trigger programs (3/4)
      4. Trigger programs (4/4)
        1. Activation time of trigger programs
        2. Trigger events
        3. External triggers
        4. SQL triggers
        5. Getting information about triggers
      5. Stored procedures (1/3)
      6. Stored procedures (2/3)
      7. Stored procedures (3/3)
        1. External stored procedures
        2. SQL stored procedures
        3. SQL statement CREATE PROCEDURE
        4. Procedure signature and overloading
        5. Deleting or replacing a stored procedure
        6. Getting information about stored procedures
      8. User defined functions (1/3)
      9. User defined functions (2/3)
      10. User defined functions (3/3)
        1. External user defined functions
        2. SQL user defined scalar functions
        3. User defined table functions
        4. User defined function signature and overloading
        5. Deleting or replacing a user defined function
        6. Getting information about user defined functions
      11. SQL programming language (1/2)
      12. SQL programming language (2/2)
        1. Compound statement
        2. Control statements
        3. Error handling in SQL
    5. Chapter 9: Other considerations
      1. Comparing RPG and SQL data types (1/4)
      2. Comparing RPG and SQL data types (2/4)
      3. Comparing RPG and SQL data types (3/4)
      4. Comparing RPG and SQL data types (4/4)
        1. Character data types
        2. Character fields with fixed and varying length
        3. Numeric data types
        4. Date and time data types
      5. NULL values (1/2)
      6. NULL values (2/2)
        1. Handling NULL values in RPG with native I/O
        2. Using indicator variables in SQL
        3. Particular characteristics of NULL values in SQL statements
      7. Date and time calculation (1/6)
      8. Date and time calculation (2/6)
      9. Date and time calculation (3/6)
      10. Date and time calculation (4/6)
      11. Date and time calculation (5/6)
      12. Date and time calculation (6/6)
        1. Converting from numeric/character date values to real date values
        2. Converting from date fields to character or numeric representation
        3. Checking for a valid date or time
        4. Retrieving current date and time
        5. Adding and subtracting date and time values
        6. Calculating date and time differences
        7. Extracting a portion of a date, time, or timestamp
        8. Additional SQL scalar functions for date calculation
  6. Part 4: Tools
    1. Chapter 10: DB2 Development Tools
      1. WebSphere Development Studio Client for iSeries (WDSC) (1/2)
      2. WebSphere Development Studio Client for iSeries (WDSC) (2/2)
      3. iSeries Navigator (1/2)
      4. iSeries Navigator (2/2)
        1. Database Navigator
        2. Run SQL Scripts
        3. Visual Explain
        4. Graphical iSeries System Debugger
      5. OS/400 utilities
      6. DB2 Development Center
      7. DB2 Query Management Facility (1/3)
      8. DB2 Query Management Facility (2/3)
      9. DB2 Query Management Facility (3/3)
        1. Migrating existing queries
        2. Creating new queries
    2. Related publications
      1. IBM Redbooks
      2. Other publications
      3. Online resources
      4. How to get IBM Redbooks
      5. Help from IBM
    3. Index (1/2)
    4. Index (2/2)
    5. Back cover

Product information

  • Title: Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone
  • Author(s): Hernando Bedoya, Birgitta Hauser has, Rolf AndrŽ Klaedtke, Sharon Hoffman, Warawich Sundarabhaka
  • Release date: February 2005
  • Publisher(s): IBM Redbooks
  • ISBN: None