Front cover -- Contents -- Notices -- Trademarks -- Preface -- The team that wrote this redbook -- Become a published author -- Comments welcome -- Part 1 Introduction and background -- Chapter 1. iSeries Developer Roadmap - The big picture -- 1.1 Introduction to the iSeries Developer Roadmap -- 1.1.1 Why a roadmap -- 1.1.2 Why care about it -- 1.1.3 The goal -- 1.2 What is in the roadmap -- 1.2.1 Better tools -- 1.2.2 Better user interface -- 1.2.3 Better architecture -- 1.2.4 Better portability -- 1.2.5 Better scalability -- 1.3 What is in this book -- Chapter 2. Why modernize with SQL and DB2 UDB for iSeries -- 2.1 Background -- 2.1.1 A short look at the history of SQL -- 2.1.2 The main parts of SQL -- 2.2 Reasons to modernize -- 2.2.1 Standard compliancy -- 2.2.2 Openness -- 2.2.3 Performance -- 2.2.4 Available skills -- 2.2.5 Functionality -- 2.2.6 Data integrity -- Part 2 Data definition -- Chapter 3. Approaches and options -- 3.1 Data definition considerations -- 3.2 Accessing the database data -- 3.2.1 Native record level access -- 3.2.2 Data access with SQL -- 3.3 Methodology for the modernization -- 3.3.1 Reverse engineering DDS to SQL DDL (stage 1) -- 3.3.2 Creating I/O modules to access DB data (stage 2) -- 3.3.3 Moving business rules into the database (stage 3) -- 3.3.4 Externalizing data access (stage 4) -- Chapter 4. Modernizing database definitions -- 4.1 Reverse engineering DDS to SQL DDL -- 4.1.1 Classify the existing environment -- 4.1.2 Establishing a list of all DDS files to be converted -- 4.1.3 Establishing naming conventions for SQL objects -- 4.1.4 Converting the DDS to SQL DDL -- 4.1.5 Reviewing the generated SQL DDL -- 4.1.6 Creating the new DB2 schema on the iSeries server -- 4.1.7 Create all existing DDS logical files over the new SQL tables -- 4.1.8 Migrate data and test existing programs.

4.2 Comparing the SQL objects and the DDS files -- 4.2.1 SQL tables compared with physical files -- 4.2.2 SQL indexes compared with keyed logical files -- 4.2.3 SQL views compared with logical files -- 4.2.4 SQL data types -- 4.3 SQL system catalogs: Definitions -- 4.3.1 SQL system catalogs: Example -- 4.4 Partitioned tables -- Part 3 Data access -- Chapter 5. Creating I/O modules to access SQL objects -- 5.1 Introduction -- 5.2 Establish naming conventions -- 5.3 Create SQL views based on business requirements -- 5.4 Create service programs to access data from the SQL views -- 5.5 Convert legacy programs to use service programs -- Chapter 6. Moving business rules to the database -- 6.1 Database normalization -- 6.2 Referential integrity -- 6.3 Constraints -- 6.4 Constraint coexistence considerations -- 6.5 Column-level security -- 6.6 Column encryption -- 6.7 Automatic key generation and unique identifiers -- 6.8 Accessing non-relational data -- 6.8.1 User defined table functions for accessing non-relational data -- 6.8.2 Datalink -- 6.8.3 Large Object Support -- Chapter 7. Embedded SQL -- 7.1 How to get started -- 7.2 Creating a SQLRPG - Program/service program/module -- 7.3 Compile command CRTSQLRPGI -- 7.3.1 Missing compile options in the SQL compile command -- 7.3.2 Important compile options for SQL statements -- 7.3.3 SET OPTION statement -- 7.4 Error handling - SQLCA (SQL communications area) -- 7.4.1 SQLCODE -- 7.4.2 SQLSTATE -- 7.5 Host variables -- 7.5.1 Single field host variable -- 7.5.2 Host structure -- 7.5.3 Host structure array -- 7.5.4 Naming considerations for host variables -- 7.6 Exploiting SQL scalar functions in RPG -- 7.7 Static SQL without cursor -- 7.7.1 Static SQL returning a single row -- 7.7.2 Processing non-Select statements with static SQL without cursor -- 7.8 Using a cursor -- 7.8.1 The DECLARE statement.

7.8.2 The OPEN statement -- 7.8.3 The FETCH statement -- 7.8.4 Types of cursors -- 7.8.5 Updating or deleting rows using a cursor -- 7.9 Dynamic SQL -- 7.9.1 Defining the character string containing the SQL statement -- 7.9.2 The EXECUTE IMMEDIATE statement -- 7.9.3 Combining the SQL statements PREPARE and EXECUTE -- 7.9.4 Combining the SQL statements PREPARE and DECLARE -- 7.9.5 The SQL descriptor area -- Chapter 8. Externalizing data access -- 8.1 Trigger programs -- 8.1.1 Activation time of trigger programs -- 8.1.2 Trigger events -- 8.1.3 External triggers -- 8.1.4 SQL triggers -- 8.1.5 Getting information about triggers -- 8.2 Stored procedures -- 8.2.1 External stored procedures -- 8.2.2 SQL stored procedures -- 8.2.3 SQL statement CREATE PROCEDURE -- 8.2.4 Procedure signature and overloading -- 8.2.5 Deleting or replacing a stored procedure -- 8.2.6 Getting information about stored procedures -- 8.3 User defined functions -- 8.3.1 External user defined functions -- 8.3.2 SQL user defined scalar functions -- 8.3.3 User defined table functions -- 8.3.4 User defined function signature and overloading -- 8.3.5 Deleting or replacing a user defined function -- 8.3.6 Getting information about user defined functions -- 8.4 SQL programming language -- 8.4.1 Compound statement -- 8.4.2 Control statements -- 8.4.3 Error handling in SQL -- Chapter 9. Other considerations -- 9.1 Comparing RPG and SQL data types -- 9.1.1 Character data types -- 9.1.2 Character fields with fixed and varying length -- 9.1.3 Numeric data types -- 9.1.4 Date and time data types -- 9.2 NULL values -- 9.2.1 Handling NULL values in RPG with native I/O -- 9.2.2 Using indicator variables in SQL -- 9.2.3 Particular characteristics of NULL values in SQL statements -- 9.3 Date and time calculation -- 9.3.1 Converting from numeric/character date values to real date values.

9.3.2 Converting from date fields to character or numeric representation -- 9.3.3 Checking for a valid date or time -- 9.3.4 Retrieving current date and time -- 9.3.5 Adding and subtracting date and time values -- 9.3.6 Calculating date and time differences -- 9.3.7 Extracting a portion of a date, time, or timestamp -- 9.3.8 Additional SQL scalar functions for date calculation -- Part 4 Tools -- Chapter 10. DB2 Development Tools -- 10.1 WebSphere Development Studio Client for iSeries (WDSC) -- 10.2 iSeries Navigator -- 10.2.1 Database Navigator -- 10.2.2 Run SQL Scripts -- 10.2.3 Visual Explain -- 10.2.4 Graphical iSeries System Debugger -- 10.3 OS/400 utilities -- 10.4 DB2 Development Center -- 10.5 DB2 Query Management Facility -- 10.5.1 Migrating existing queries -- 10.5.2 Creating new queries -- Related publications -- IBM Redbooks -- Other publications -- Online resources -- How to get IBM Redbooks -- Help from IBM -- Index -- Back cover.