DB2 Server for VSE & VM: Application Programming

Chapter 3. Coding the Body of a Program

Partial Table-of-Contents

Defining Static SQL Statements

Naming Conventions

Coding SQL Statements to Retrieve and Manipulate Data

Retrieving Data

Defining an SQL Query

Using the SELECT Clause

Using the FROM Clause

Using the WHERE Clause

Using the GROUP BY Clause

Using the HAVING Clause

Using the ORDER BY Clause

Using the FOR UPDATE OF Clause

Using the WITH Clause

Retrieving or Inserting Multiple Rows

Using the Cursor with a Select-Statement

Declaring a Cursor

Using a Cursor in an Application Program

Manipulating the Cursor

The OPEN Statement

The FETCH Statement

The PUT Statement

The Positioned DELETE Statement

The Positioned UPDATE Statement

The CLOSE Statement

Illustrating the Use of the Query Cursor

Retrieving Single Rows

Constructing Search Conditions

Performing Arithmetic Operations

Using Null Values

Using the Predicates of a Search Condition

Evaluating Predicates

Using Additional Types of Predicates

Using Functions

Using Column Functions

Using Scalar Functions

Using Data Types

Assigning Data Types When the Column Is Created

Using Long Strings

Defining Long Strings

Performing Operations on Long Strings

Programming Tip

Using Datetime Data Types

Using Character Subtypes and CCSIDs

Determining Default Subtypes and CCSIDs

Assigning Subtypes and CCSIDs When a Column Is Created

Assigning Subtypes and CCSIDs to Data in a Program

Converting Data

Summarizing Data Conversion

Truncating Data

Using a Double-Byte Character Set (DBCS)

Using Expressions

Using Arithmetic Operators

Using Special Registers

Concatenating Character and Graphic Strings

Using Host Variables

Using Host Structures

Using Constants

Using Numeric Constants

Using Character Constants

Using Graphic Constants

Using Date and Time Constants

Using Indicator Variables

Notes Common to Both Input and Output Indicator Variables

Notes on Input Indicator Variables

Notes on Output Indicator Variables

Using Views

Creating a View

Querying Tables through a View

Using Views to Manipulate Data

Dropping a View

Joining Tables

Joining Tables Using the Database Manager

Performing a Simple Join Query

Joining Another User's Tables

Analyzing How a Join Works

Using VARCHAR and VARGRAPHIC within Join Conditions

Using Nulls within Join Conditions

Joining a Table to Itself Using a Correlation Name

Rules for Table Designation

Imposing Limits on Join Queries

Using SELECT * In a Join

Grouping the Rows of a Table

Using VARCHAR and VARGRAPHIC within Groups

Using Nulls within Groups

Using Select-Lists in Grouped Queries

Using a WHERE Clause with a GROUP BY Clause

Using the HAVING Clause

Combining Joins

Illustrating Grouping with an Exercise

Nesting Queries

Using the IN Predicate with a Subquery

Considering Other Subquery Issues

Executing Subqueries Repeatedly: Correlation

Writing a Correlated Subquery

How the Database Manager Does Correlation

Illustrating a Correlated Subquery

Using a Subquery to Test for the Existence of a Row

Table Designation Rule for Correlated Subqueries

Combining Queries into a Single Query: UNION

String Columns

Numeric Columns

Datetime/Timestamp Columns

SQL Comments within Static SQL Statements

Using Stored Procedures

Writing Stored Procedures

Returning Information from the SQLCA

Language Environment^(R) (LE) Considerations

Preparing to Run a Stored Procedure

Calling Stored Procedures

Authorization

AUTHIDs

Stored Procedure Parameters

Datatype Compatibility

Conventions for Passing Stored Procedure Parameters

The GENERAL Linkage Convention

The GENERAL WITH NULLS Linkage Convention

Coding Examples

Special Considerations for C

Special Considerations for PL/I

Result Sets

|Coding Client Programs to Process Results Sets

|Result Set Processing

|Using the DESCRIBE PROCEDURE SQL Statement

|Using the DESCRIBE CURSOR SQL Statement

|Coding Summary to Process Result Sets

|Processing a Known Number of Result Sets

|Processing a Unknown Number of Result Sets

Contents

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]