DB2 Server for VSE & VM: Application Programming

About This Manual

Audience and Purpose of This Book

Organization of This Book

Related Publications

|How to Send Your Comments

Syntax Notation Conventions

SQL Reserved Words

Conventions for Representing DBCS Characters

Components of the Relational Database Management System

Summary of Changes

|Summary of Changes for DB2 Version 7 Release 1

|Enhancements, New Functions, and New Capabilities

|Reliability, Availability, and Serviceability Improvements

|Library Enhancements

Chapter 1. Getting Started

What is the DB2 Server for VSE & VM Product?

What is SQL?

Embedding SQL Statements in Host Language Programs

Writing a Program

Chapter 2. Designing a Program

Defining the Main Parts of a Program

Creating the Prolog

Creating the Body

Creating the Epilog

Using Logical Units of Work

Defining the Logical Unit of Work

Beginning a Logical Unit of Work

Considering the CICS/VSE Logical Unit of Work (DB2 Server for VSE Only)

Ending a Logical Unit of Work

Summary

Using Host-Dependent Sample Applications

Chapter 3. Coding the Body of a Program

Defining Static SQL Statements

Naming Conventions

Coding SQL Statements to Retrieve and Manipulate Data

Retrieving Data

Defining an SQL Query

Retrieving or Inserting Multiple Rows

Retrieving Single Rows

Constructing Search Conditions

Performing Arithmetic Operations

Using Null Values

Using the Predicates of a Search Condition

Using Functions

Using Column Functions

Using Scalar Functions

Using Data Types

Assigning Data Types When the Column Is Created

Using Long Strings

Using Datetime Data Types

Using Character Subtypes and CCSIDs

Converting Data

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 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

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

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

Coding Examples

Special Considerations for C

Special Considerations for PL/I

Result Sets

|Using the DESCRIBE PROCEDURE SQL Statement

|Using the DESCRIBE CURSOR SQL Statement

Chapter 4. Preprocessing and Running a DB2 Server for VM Program

Defining the Steps to Execute the Program

Comparing Single User Mode to Multiple User Mode

Using 31-Bit Addressing

Initializing the User Machine

Using VM Implicit Connect

Preprocessing the Program

Using the SQLPREP EXEC Procedure

Preprocessing with an Unlike Application Server

Using the Preprocessor Option File

Using the Flagger at Preprocessor Time

Improving Performance Using Preprocessing Parameters

Using the INCLUDE Statement

Compiling the Program

Link-Editing and Loading the Program

Link-Editing the Program with DB2 Server for VM TEXT Files

Including the TEXT File in the Link-Editing

Creating a Load Module Using the CMS GENMOD Command

Running the Program

Using a Consistency Token

Loading the Package and Rebinding

Using Multiple User Mode

Using Single User Mode

Specifying User Parameters in Single User Mode

Distributing Packages across Like and Unlike Systems

Chapter 5. Preprocessing and Running a DB2 Server for VSE Program

Defining the Steps to Execute the Program

Using 31-Bit Addressing

How DB2 Establishes User IDs for CICS/VSE Transactions

User IDs for Remote CICS/VSE Transactions

|Using Batch for Remote CICS/VSE Transactions

Preprocessing the Program

Preprocessing by Mode

Defining the Preprocessing Parameters

Using the Preprocessor Option Member

Using the Flagger at Preprocessor Time

Using the CICS/VSE Translator

Improving Performance Using Preprocessing Parameters

Using the INCLUDE Statement

Compiling the Program

Link-Editing and Loading the Program

Link-Editing the Program with Supplementary Information

Running the Program

Using a Consistency Token

Loading the Package and Rebinding

Running by Mode

Running under CICS/VSE Support

Accessing Other DB2 Family Application Servers

Installing Applications that Access the Database Manager

Installing a Batch Application

Installing an Online CICS/VSE Application

Distributing Packages across Like and Unlike Systems

Creating a Package Using CBND

Chapter 6. Testing and Debugging

Doing Your Own Testing

Checking Warnings and Errors at Preprocessor Time

Testing SQL Statements

Using the Automatic Error-Handling Facilities

Using the SQLCA

Examining Errors

Handling Errors in a Select-List

Handling Arithmetic Errors

Handling Numeric Conversion Errors

Handling CCSID Conversion Errors

Chapter 7. Using Dynamic Statements

Dynamically Defining SQL Statements

Comparing Non-Query Statements to Query Statements

Using Non-Query Statements

Executing Non-Parameterized Statements

Executing Parameterized Statements

Using Query Statements

Executing a Non-Parameterized Select-Statement

Executing a Parameterized SELECT Statement

Executing a Parameterized Non-Query Statement

Generating a SELECT Statement

Using an Alternative to a Scanning Routine

Ensuring Data Type Equivalence in a Dynamically Defined Query

Summarizing the Fields of the SQLDA

Using the SQLN Field

Using the SQLD Field in the SQLDA

Using the PREPARE Statement

SQL Functions Not Supported in Dynamic Statements

Chapter 8. Using Extended Dynamic Statements

Contents

Using Extended Dynamic Statements to Maintain Packages

Illustrating the Use of Extended Dynamic Statements

Grouping Extended Dynamic Statements in an LUW

Mapping Extended Dynamic Statements to Static and Dynamic Statements

SQL Functions Not Supported in Extended Dynamic Statements

Chapter 9. Maintaining Objects Used by a Program

Managing Dbspaces

Defining Dbspaces

Modifying the Size of Dbspaces

Automatically Locking Dbspaces

Overriding Automatic Locking

Deleting the Contents of Dbspaces

Other Data Definition Statements

Using Tables, Indexes, Statistics, Synonyms, Comments, and Labels

Using Stored Procedures and PSERVERS

Chapter 10. Assigning Authority and Privileges

Defining User Access to the Database

Defining Authority Types for the Database

Granting Authority to Users

Revoking Authority from Users

Defining Privileges

Defining Privileges on Tables and Views

Defining Privileges on Packages

Chapter 11. Special Topics

Using Datetime Values with Durations

Using Durations

Resolving Peculiarities of Date Arithmetic

Using Field Procedures

Assigning Field Procedures to Columns

Understanding Field Procedure Rules

Using CMS Work Units (DB2 Server for VM)

Using Work Units in Application Programs

How Locking Works with CMS Work Units

Environmental Considerations

Ensuring Data Integrity

Ensuring Entity Integrity

Using Unique Constraints

When Creating a View

Ensuring Referential Integrity

Switching Application Servers

Identifying Switching Options

Comparing Switching to Other Methods (DB2 Server for VM)

How to Switch Servers (DB2 Server for VSE)

Accessing a New Application Server

Illustrating Sample Code

Preprocessing the Program on Multiple Application Servers

Condition Handling with LE/VSE (DB2 Server for VSE)

Appendix A. Using SQL in Assembler Language

Using ARIS6ASD, an Assembler Language Sample Program (DB2 Server for VSE Only)

Using ARIS6ASC, an Assembler Language Sample Program (DB2 Server for VM Only)

Acquiring the SQLDSECT Area

Imposing Usage Restrictions on the SQLDSECT Area

Rules for Using SQL Statements in Assembler Language

Identifying Rules for Case

Declaring Host Variables

Embedding SQL Statements

Using the INCLUDE Statement

Using Host Variables in SQL Statements

Using DBCS Characters in Assembler Language

Handling SQL Errors

Using Dynamic SQL Statements in Assembler Language

Defining DB2 Server for VSE & VM Data Types for Assembler Language

Using Reentrant Assembler Language Programs

Using Stored Procedures

Appendix B. Using SQL in C

A C Sample Program

Rules for Using SQL in C

Placing and Continuing SQL Statements

Delimiting SQL Statements

Identifying Rules for Case

Identifying Rules for Character Constants

Using the INCLUDE Statement

Using the CONNECT Statement (DB2 Server for VSE)

Using the C Compiler Preprocessor

Declaring Host Variables

Using Host Variables in SQL Statements

Using the Pointer Type Attribute

Using Host Variables as Function Parameters

Using C Variables in SQL: Data Conversion Considerations

Using C NUL-Terminated Strings and Truncation

Calculating Dates

Using Trigraphs

Using DBCS Characters in C

Considering Preprocessor-Generated Statements

Handling SQL Errors

Using Dynamic SQL Statements in C

Defining DB2 Server for VSE & VM Data Types for C

Using Reentrant C Programs

Using Stored Procedures

Appendix C. Using SQL in COBOL

A Sample COBOL Program

Rules for Using SQL in COBOL

Placing and Continuing SQL Statements

Delimiting SQL Statements

Identifying Rules for Case

Declaring Host Variables

Using Host Variables in SQL Statements

Using Long VARCHAR Host Variables (DB2 Server for VSE)

Using Preprocessor Options

Handling SQL Errors

Using Dynamic SQL Statements in COBOL

Defining DB2 Server for VSE & VM Data Types for COBOL

Using Reentrant COBOL Programs

Using the DYNAM Compiler Option

Using Stored Procedures

Appendix D. Using SQL in FORTRAN

A FORTRAN Sample Program

Rules for Using SQL in FORTRAN

Placing and Continuing SQL Statements

Placing Data Statements

Using FORTRAN Common Areas (DB2 Server for VSE)

Identifying Rules for Case

Declaring Host Variables

Embedding SQL Statements

Using Host Variables in SQL Statements

Using Variable Length Character Strings

Using DBCS Characters in FORTRAN

Using the INCLUDE Statement

Using FORTRAN Variables in SQL: Data Conversion Considerations

Handling SQL Errors

Handling Program Interrupts

Using Dynamic SQL Statements in FORTRAN

Restrictions When Using the FORTRAN Preprocessor

Defining DB2 Server for VSE & VM Data Types for FORTRAN

Appendix E. Using SQL in PL/I

Using PL/I Sample Programs

Rules for Using SQL in PL/I

Placing and Continuing SQL Statements

Delimiting SQL Statements

Using the INCLUDE Statement

Declaring Static External Variables

Identifying Rules for Case

Declaring Host Variables

Using Host Variables in SQL Statements

Using PL/I Variables in SQL: Data Conversion Considerations

Using DBCS Characters in PL/I

Using SQL Statements in PL/I Subroutines

Coding the SIZE Parameter in VSE JCL (DB2 Server for VSE)

Handling SQL Errors

Handling Program Interrupts

Using Dynamic SQL Statements in PL/I

Defining DB2 Server for VSE & VM Data Types for PL/I

Using Stored Procedures

Appendix F. Decision Tables to Grant Privileges on Packages

How to Use the Decision Tables

Decision Tables

Notices

Programming Interface Information

Trademarks

Bibliography

Index

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