You are previewing SQL for eServer i5 and iSeries.
O'Reilly logo
SQL for eServer i5 and iSeries

Book Description

In this book, you will learn about Structured Query Language (SQL), but with an IBM eServer i5/iSeries twist. The future seems clear on this point: SQL is the direction of the future for IBM's Universal Database. While it is true that the majority of existing databases on the i5 and iSeries were created with DDS, it's still undeniable that DDS will slowly fade from use, much like the RPG program cycle.

With a practical "how-to" style, SQL for eServer i5 and iSeries first introduces you to the basics essential for any developer to build a solid foundation. Then the book examines--in detail--the many ways in which SQL functions on the iSeries. All possible environments are explored: the traditional 5250 green-screen applications, the SQL scripting tool within iSeries Navigator, and the methods of accessing iSeries databases from within Visual Basic.

Learn through real-life examples of using SQL to interact with DB2 databases. You'll learn the ins and outs of sub-selects, joins, unions, and views, as well as tips and techniques for interacting with legacy applications, and so much more. Though intended for programmers already familiar with SQL, beginners will appreciate the book's handy tutorial Appendix to get started with SQL basics.

After reading this book, you'll see that the i5/iSeries platform is far more than the green-screen application server it's often perceived as and that it is indeed one of the finest systems available for use as a business server today. Make your database access cutting-edge with SQL for eServer i5 and iSeries.

In this book you will learn to:
- Incorporate SQL into existing iSeries business and client-side applications
- Embed SQL within RPG programs, merging the flexibility of SQL with the power of RPG
- Implement referential constraints, primary and unique key indexes, and check constraints
- Journal physical files, and find out why this task is so important
- Extend your database with user-defined functions and procedures
- Use new GUI development tools for running, debugging, and tuning SQL statements

Table of Contents

  1. Copyright
  2. Acknowledgments
  3. Preface
  4. 1. Interactive SQLs
    1. Examples of Basic SELECTs
      1. Tool of Choice
      2. Sample Database
    2. Getting Started with STRSQL
      1. Modifying the Columns Selected
    3. Common Problems
      1. Non-externally Defined Database Files
      2. Handling Negatives Numbers in Non-Database Files
      3. Handling Packed Numbers in Non-Externally Defined Database Files
      4. Dates Not Stored as Date Data Types
        1. MMDDCCYY Dates
        2. CYYMMDD Dates
        3. Dates from Flat Files
        4. Date Math with Date Fields
        5. Date Math with Non-date Fields
      5. Updating Files
    4. Tricks in the Interactive Environment
      1. Redirecting the Output from SELECT
    5. Summary
  5. 2. SQL in iSeries Navigator
    1. Installing the Database Component of iSeries Navigator
    2. Getting Started with the SQL Scripting Tool
      1. Configuring the SQL Script Environment
      2. Running Multiple SQL Scripts
      3. Saving and Loading SQL Scripts
      4. JDBC Setup
      5. Redirecting the Output
    3. Using Visual Explain
      1. Launching Visual Explain
      2. Understanding Visual Explain
  6. 3. Embedded SQL
    1. Why Embed SQL within RPG IV?
    2. How to Embed SQL within RPG IV
      1. Static SQL
      2. Testing for Failed SQL Statements
        1. SQLCOD Error Code Values
        2. SQLSTT Error Code Values
      3. Executing Other SQL Statements
      4. Dynamic SQL
        1. Handling Quotes around Character Values
        2. SELECTing Multiple Rows
        3. Testing for End-of-File and Other Conditions
    3. Prepared SELECT Statements
    4. Updating the Data in the Result Set
      1. Record Lock
      2. Strategies for Dealing with Record Lock
        1. Running with No Commit
        2. Running with Repeatable Read
      3. Jumping around the Result Set
      4. Load All Subfiles with a SELECT and FETCH Loop
      5. Position to Logic
    5. Compile Options
      1. Parameter Markers
      2. Single-Page Subfile
      3. Null Values
    6. Summary
  7. 4. Advanced SQL Examples
    1. Joins
      1. Join without the Join Keyword
      2. Using the Join Keyword
      3. Using a Left Outer Join
      4. Using a Right Outer Join
      5. Using an Exception Join
      6. Using a Cross Join
      7. Vertical Joins with Unions
      8. Sorting Unions by Column Number
      9. Handling Inconsistent Data Types in Unions
      10. Handling Missing Data in Unions
    2. Handling Multimember Files with Aliases
    3. Using Sub-SELECTs
      1. IN Clause with Sub-SELECT
      2. Using Sub-SELECTs in the SET Clause
      3. Handling Null Values from Sub-SELECTs
      4. Joining to Sub-SELECTs
      5. Joining with a Calculated Field
      6. Using WITH to Simplify SQL Statements
    4. Using CASE to Handle Tricky Situations
      1. Using CASE in an UPDATE Statement
      2. Using CASE in a SELECT Statement
    5. Using Commitment Control
      1. Journaling
      2. Using Transaction Processing to Group Updates Together
    6. Summary
  8. 5. Optimizing SQL
    1. Using Debug to Optimize SQL Statements
    2. A Bit about Indexes
      1. Index Creation Rule of Thumb
      2. Index Overhead
      3. Binary Radix Tree vs. Encoded Vector Indexes
      4. Create Index Syntax
      5. Create Index Order
      6. Index Search Order
      7. Combining Smaller Indexes
    3. Query Attributes File QAQQINI
    4. Optimizing SELECTs
    5. Views on Views
    6. SETOBJACC and Loading Files into Memory
      1. Memory Paging
    7. Traditional or Legacy Files and Index Issues
      1. Access Path Maintenance
      2. Key Composition
    8. Database Statistics
    9. Parallel Processing
    10. GUI Tools
      1. Visual Explain
    11. SQL Performance Monitor
    12. Optimize the Machine or Optimize the People?
    13. Suggestions
    14. Summary
  9. 6. Creating Database Objects
    1. Schemas, Collections, Libraries, and Databases
      1. Tables
      2. Record Format Name
      3. Column Headings
      4. Identity Columns
      5. Row ID
      6. Row ID vs. Identity Column
      7. Default Values
      8. Null Capability
    2. Constraints
      1. Primary Key Constraint
      2. Unique Key Constraint
      3. Referential Constraint
      4. Check Constraint
    3. Working with Varchars
    4. Working with Datalinks
    5. Working with BLOBs
    6. Working with User-Defined Data Types
    7. Views
    8. Aliases and Multimember Processing
    9. Catalogs
    10. Journaling
    11. Altering Your Database
    12. Adding Comments to Your Database
    13. Build Procedures
    14. Summary
  10. 7. Creating Functions
    1. Creating Functions with RPGLE
      1. Function to Convert Legacy Data into a Date with RPGLE
      2. Function to Accumulate a Running Total
      3. Setting SQLSTATE
      4. Function to Calculate the Current Price of an Item
      5. Changing GetPrice to Perform User-Controlled File Opens
    2. Creating Functions with SQL
      1. Convert Date Function with SQL
      2. Creating Table Functions with SQL
    3. Creating Sourced Functions
    4. Summary
  11. 8. Creating Stored Procedures
    1. Creating a Stored Procedure to Post Inventory Transactions
      1. Adding Simple Error Handling
      2. Adding Commitment Control
    2. Processing a Transaction File
      1. Calling a Procedure from within a Procedure
      2. Returning a Result Set
    3. Scheduling with Procedures
    4. Archiving Data with Procedures
    5. Calling a CL Program as a Stored Procedure
      1. Call a CL Program to Initialize a Tape
      2. Call a CL Program to Backup the Database
      3. Call a CL Program to Perform End-of-Day Processes
      4. Recalculate Item Costs
    6. Archiving Data
    7. Calling Stored Procedures with Embedded SQL
      1. Calling Stored Procedures That Return Data Structures as Result Tables
      2. Calling Stored Procedures That Return SQL Cursors as Result Tables
      3. Testing Stored Procedures
    8. Summary
  12. 9. Creating SQL-based Tools
    1. Using QM Query
      1. Use QM Query to Perform Any SQL Statement
      2. Passing Character Literals within Parameters to QM Query
      3. Passing Character Literals from CL Programs to QM Query
      4. Passing Multiple Parameters to a QM Query
      5. Use QM Query to Move Records between Two Tables
      6. Performing Multiple Tests with a Single QM Query
      7. Running Reports with the ADHOC QM Query
      8. Defining a Generic ADHOC Report Format Using QM Query
      9. Adding Variable Headings to a QM Query Form
    2. Using RPGLE to Create SQL-based Tools
      1. RunSql and Ad Hoc SQL Subprocedure
      2. GetSqlVal to Calculate a Single Value from a SELECT Statement
      3. DoSqlLoop to Select and Fetch Records in a Loop
    3. Summary
  13. 10. Using SQL from within Microsoft Office
    1. How to Access iSeries Data from a PC
      1. Configuring the ODBC Driver
    2. How to Access iSeries Data from Excel
      1. Using VBA and ADO to Access iSeries Data from Excel
      2. Registering ADO within Excel
      3. Getting Started with ADO and Excel
      4. Adding Column Headings with ADO and Excel
      5. Connecting with OLE DB
    3. Using Forms with ADO
    4. Uploading Data to the iSeries
      1. Changing Data on the iSeries with ADO and Excel
    5. Integrating iSeries Data into Word Using ADO
      1. Integrating iSeries Data into Word Mail Merge
      2. Inserting Tables of iSeries Data into Word Using ADO
    6. Calling a Stored Procedure through ADO
    7. Summary
  14. A. SQL Basics for Beginners
    1. SELECT Statement
    2. Field List
    3. FROM Clause
    4. WHERE Clause
    5. ORDER BY Clause
    6. Column Functions
    7. Summary Functions
    8. GROUP BY Clause
    9. HAVING Clause
    10. INSERT Statement
    11. UPDATE Statement
    12. DELETE Statement