Title: DB2 UDB for z/OS V8 Application Programming and SQL Guide
Document Number: SC18-7415-02
Build Date: 03/28/05 01:34:05 Build Version: 1.3.1 of BUILD/VM Version: UG03951 DropDate: Wednesday September 22 2004
Document Path: /home/webapps/epubs/htdocs/book/dsnapj12.boo
CONTENTS Table of Contents
Summarize COVER Book Cover NOTICES Notices EDITION Edition Notice CONTENTS Table of Contents FIGURES Figures TABLES Tables PREFACE About this book PREFACE.1 Who should read this book PREFACE.2 Terminology and citations PREFACE.3 How to read the syntax diagrams PREFACE.4 Accessibility PREFACE.5 How to send your comments CHANGES Summary of changes to this book 1.0 Section 1. Using SQL queries 1.1 Chapter 1-1. Retrieving data 1.1.1 Result tables 1.1.2 Data types 1.1.3 Selecting columns: SELECT 1.1.3.1 Selecting all columns: SELECT * 1.1.3.2 Selecting some columns: SELECT column-name 1.1.3.3 Selecting derived columns: SELECT expression 1.1.3.4 Eliminating duplicate rows: DISTINCT 1.1.3.5 Naming result columns: AS 1.1.4 Selecting rows using search conditions: WHERE 1.1.5 Putting the rows in order: ORDER BY 1.1.5.1 Specifying the sort key 1.1.5.2 Referencing derived columns 1.1.6 Summarizing group values: GROUP BY 1.1.7 Subjecting groups to conditions: HAVING 1.1.8 Merging lists of values: UNION 1.1.8.1 Using UNION to eliminate duplicates 1.1.8.2 Using UNION ALL to keep duplicates 1.1.9 Creating common table expressions: WITH 1.1.9.1 Using WITH instead of CREATE VIEW 1.1.9.2 Using common table expressions with CREATE VIEW 1.1.9.3 Using common table expressions when you use INSERT 1.1.9.4 Using recursive SQL 1.1.10 Accessing DB2 data that is not in a table 1.1.11 Using 15-digit and 31-digit precision for decimal numbers 1.1.12 Finding information in the DB2 catalog 1.1.12.1 Displaying a list of tables you can use 1.1.12.2 Displaying a list of columns in a table 1.2 Chapter 1-2. Working with tables and modifying data 1.2.1 Working with tables 1.2.1.1 Creating your own tables: CREATE TABLE 1.2.1.2 Working with temporary tables 1.2.1.3 Dropping tables: DROP TABLE 1.2.2 Working with views 1.2.2.1 Defining a view: CREATE VIEW 1.2.2.2 Changing data through a view 1.2.2.3 Dropping views: DROP VIEW 1.2.3 Modifying DB2 data 1.2.3.1 Inserting rows: INSERT 1.2.3.2 Selecting values as you insert: SELECT from INSERT 1.2.3.3 Updating current values: UPDATE 1.2.3.4 Deleting rows: DELETE 1.3 Chapter 1-3. Joining data from more than one table 1.3.1 Inner join 1.3.2 Full outer join 1.3.3 Left outer join 1.3.4 Right outer join 1.3.5 SQL rules for statements containing join operations 1.3.6 Using more than one join in an SQL statement 1.3.7 Using nested table expressions and user-defined table functions in joins 1.3.8 Using correlated references in table specifications in joins 1.4 Chapter 1-4. Using subqueries 1.4.1 Conceptual overview 1.4.1.1 Correlated and uncorrelated subqueries 1.4.1.2 Subqueries and predicates 1.4.1.3 The subquery result table 1.4.1.4 Tables in subqueries of UPDATE, DELETE, and INSERT statements 1.4.2 How to code a subquery 1.4.2.1 Basic predicate 1.4.2.2 Quantified predicate : ALL, ANY, or SOME 1.4.2.3 IN keyword 1.4.2.4 EXISTS keyword 1.4.3 Using correlated subqueries 1.4.3.1 An example of a correlated subquery 1.4.3.2 Using correlation names in references 1.4.3.3 Using correlated subqueries in an UPDATE statement 1.4.3.4 Using correlated subqueries in a DELETE statement 1.5 Chapter 1-5. Using SPUFI to execute SQL from your workstation 1.5.1 Allocating an input data set and using SPUFI 1.5.2 Changing SPUFI defaults. 1.5.3 Changing SPUFI defaults - panel 2. 1.5.4 Entering SQL statements 1.5.4.1 Using the ISPF editor 1.5.4.2 Retrieving Unicode UTF-16 graphic data 1.5.4.3 Entering comments 1.5.4.4 Setting the SQL terminator character 1.5.4.5 Controlling toleration of warnings 1.5.5 Processing SQL statements 1.5.6 When SQL statements exceed resource limit thresholds 1.5.7 Browsing the output 1.5.7.1 Format of SELECT statement results 1.5.7.2 Content of the messages 2.0 Section 2. Coding SQL in your host application program 2.1 Chapter 2-1. Basics of coding SQL in an application program 2.1.1 Conventions used in examples of coding SQL statements 2.1.2 Delimiting an SQL statement 2.1.3 Declaring table and view definitions 2.1.4 Accessing data using host variables, variable arrays, and structures 2.1.4.1 Using host variables 2.1.4.2 Using host variable arrays 2.1.4.3 Using host structures 2.1.5 Checking the execution of SQL statements 2.1.5.1 Using the SQL communication area (SQLCA) 2.1.5.2 SQLCODE and SQLSTATE 2.1.5.3 The WHENEVER statement 2.1.5.4 Handling arithmetic or conversion errors 2.1.5.5 The GET DIAGNOSTICS statement 2.1.5.6 Calling DSNTIAR to display SQLCA fields 2.2 Chapter 2-2. Using a cursor to retrieve a set of rows 2.2.1 Accessing data by using a row-positioned cursor 2.2.1.1 Step 1: Declare the cursor 2.2.1.2 Step 2: Open the cursor 2.2.1.3 Step 3: Specify what to do at end-of-data 2.2.1.4 Step 4: Execute SQL statements 2.2.1.5 Step 5: Close the cursor 2.2.2 Accessing data by using a rowset-positioned cursor 2.2.2.1 Step 1: Declare the rowset cursor 2.2.2.2 Step 2: Open the rowset cursor 2.2.2.3 Step 3: Specify what to do at end-of-data for a rowset cursor 2.2.2.4 Step 4: Execute SQL statements with a rowset cursor 2.2.2.5 Step 5: Close the rowset cursor 2.2.3 Types of cursors 2.2.3.1 Scrollable and non-scrollable cursors 2.2.3.2 Held and non-held cursors 2.2.4 Examples of using cursors. 2.3 Chapter 2-3. Generating declarations for your tables using DCLGEN 2.3.1 Invoking DCLGEN through DB2I 2.3.2 Including the data declarations in your program 2.3.3 DCLGEN support of C, COBOL, and PL/I languages 2.3.4 Example: Adding a table declaration and host-variable structure to a library 2.3.4.1 Step 1. Specify COBOL as the host language 2.3.4.2 Step 2. Create the table declaration and host structure 2.3.4.3 Step 3. Examine the results 2.4 Chapter 2-4. Embedding SQL statements in host languages 2.4.1 Coding SQL statements in an assembler application 2.4.1.1 Defining the SQL communications area 2.4.1.2 Defining SQL descriptor areas 2.4.1.3 Embedding SQL statements 2.4.1.4 Using host variables 2.4.1.5 Declaring host variables 2.4.1.6 Determining equivalent SQL and assembler data types 2.4.1.7 Determining compatibility of SQL and assembler data types 2.4.1.8 Using indicator variables 2.4.1.9 Handling SQL error return codes 2.4.1.10 Macros for assembler applications 2.4.2 Coding SQL statements in a C or C++ application 2.4.2.1 Defining the SQL communication area 2.4.2.2 Defining SQL descriptor areas 2.4.2.3 Embedding SQL statements 2.4.2.4 Using host variables and host variable arrays 2.4.2.5 Declaring host variables 2.4.2.6 Declaring host variable arrays 2.4.2.7 Using host structures 2.4.2.8 Determining equivalent SQL and C data types 2.4.2.9 Determining compatibility of SQL and C data types 2.4.2.10 Using indicator variables and indicator variable arrays 2.4.2.11 Handling SQL error return codes 2.4.2.12 Coding considerations for C and C++ 2.4.3 Coding SQL statements in a COBOL application 2.4.3.1 Defining the SQL communication area 2.4.3.2 Defining SQL descriptor areas 2.4.3.3 Embedding SQL statements 2.4.3.4 Using host variables and host variable arrays 2.4.3.5 Declaring host variables 2.4.3.6 Declaring host variable arrays 2.4.3.7 Using host structures 2.4.3.8 Determining equivalent SQL and COBOL data types 2.4.3.9 Determining compatibility of SQL and COBOL data types 2.4.3.10 Using indicator variables and indicator variable arrays 2.4.3.11 Handling SQL error return codes 2.4.3.12 Coding considerations for object-oriented extensions in COBOL 2.4.4 Coding SQL statements in a Fortran application 2.4.4.1 Defining the SQL communication area 2.4.4.2 Defining SQL descriptor areas 2.4.4.3 Embedding SQL statements 2.4.4.4 Using host variables 2.4.4.5 Declaring host variables 2.4.4.6 Determining equivalent SQL and Fortran data types 2.4.4.7 Determining compatibility of SQL and Fortran data types 2.4.4.8 Using indicator variables 2.4.4.9 Handling SQL error return codes 2.4.5 Coding SQL statements in a PL/I application 2.4.5.1 Defining the SQL communication area 2.4.5.2 Defining SQL descriptor areas 2.4.5.3 Embedding SQL statements 2.4.5.4 Using host variables and host variable arrays 2.4.5.5 Declaring host variables 2.4.5.6 Declaring host variable arrays 2.4.5.7 Using host structures 2.4.5.8 Determining equivalent SQL and PL/I data types 2.4.5.9 Determining compatibility of SQL and PL/I data types 2.4.5.10 Using indicator variables and indicator variable arrays 2.4.5.11 Handling SQL error return codes 2.4.6 Coding SQL statements in a REXX application 2.4.6.1 Defining the SQL communication area 2.4.6.2 Defining SQL descriptor areas 2.4.6.3 Accessing the DB2 REXX Language Support application programming interfaces 2.4.6.4 Embedding SQL statements in a REXX procedure 2.4.6.5 Using cursors and statement names 2.4.6.6 Using REXX host variables and data types 2.4.6.7 Using indicator variables 2.4.6.8 Setting the isolation level of SQL statements in a REXX procedure 2.5 Chapter 2-5. Using constraints to maintain data integrity 2.5.1 Using check constraints 2.5.1.1 Check constraint considerations 2.5.1.2 When check constraints are enforced 2.5.1.3 How check constraints set CHECK-pending status 2.5.2 Using referential constraints 2.5.2.1 Parent key columns 2.5.2.2 Defining a parent key and a unique index 2.5.2.3 Defining a foreign key 2.5.2.4 Referential constraints on tables with multilevel security with row-level granularity 2.5.3 Using informational referential constraints 2.6 Chapter 2-6. Using DB2-generated values as keys 2.6.1 Using ROWID columns as keys 2.6.1.1 Defining a ROWID column 2.6.1.2 Direct row access 2.6.2 Using identity columns as keys 2.6.2.1 Defining an identity column 2.6.2.2 Parent keys and foreign keys 2.6.3 Using values obtained from sequence objects as keys 2.6.3.1 Creating a sequence object 2.6.3.2 Referencing a sequence object 2.6.3.3 Keys across multiple tables 2.7 Chapter 2-7. Using triggers for active data 2.7.1 Example of creating and using a trigger 2.7.2 Parts of a trigger 2.7.2.1 Trigger name 2.7.2.2 Subject table 2.7.2.3 Trigger activation time 2.7.2.4 Triggering event 2.7.2.5 Granularity 2.7.2.6 Transition variables 2.7.2.7 Transition tables 2.7.2.8 Triggered action 2.7.3 Invoking stored procedures and user-defined functions from triggers 2.7.4 Passing transition tables to user-defined functions and stored procedures 2.7.5 Trigger cascading 2.7.6 Ordering of multiple triggers 2.7.7 Interactions between triggers and referential constraints 2.7.8 Interactions between triggers and tables that have multilevel security with row-level granularity 2.7.9 Creating triggers to obtain consistent results 3.0 Section 3. Using DB2 object-relational extensions 3.1 Chapter 3-1. Introduction to DB2 object-relational extensions 3.2 Chapter 3-2. Programming for large objects 3.2.1 Introduction to LOBs 3.2.2 Declaring LOB host variables and LOB locators 3.2.3 LOB materialization 3.2.4 Using LOB locators to save storage 3.2.4.1 Deferring evaluation of a LOB expression to improve performance 3.2.4.2 Indicator variables and LOB locators 3.2.4.3 Valid assignments for LOB locators 3.2.4.4 Avoiding character conversion for LOB locators 3.3 Chapter 3-3. Creating and using user-defined functions 3.3.1 Overview of user-defined function definition, implementation, and invocation 3.3.1.1 Example of creating and using a user-defined scalar function 3.3.1.2 User-defined function samples shipped with DB2 3.3.2 Defining a user-defined function 3.3.2.1 Components of a user-defined function definition 3.3.2.2 Examples of user-defined function definitions 3.3.3 Implementing an external user-defined function 3.3.3.1 Writing a user-defined function 3.3.3.2 Preparing a user-defined function for execution 3.3.3.3 Testing a user-defined function 3.3.4 Implementing an SQL scalar function 3.3.5 Invoking a user-defined function 3.3.5.1 Syntax for user-defined function invocation 3.3.5.2 Ensuring that DB2 executes the intended user-defined function 3.3.5.3 Casting of user-defined function arguments 3.3.5.4 What happens when a user-defined function abnormally terminates 3.3.5.5 Nesting SQL statements 3.3.5.6 Recommendations for user-defined function invocation 3.4 Chapter 3-4. Creating and using distinct types 3.4.1 Introduction to distinct types 3.4.2 Using distinct types in application programs 3.4.2.1 Comparing distinct types 3.4.2.2 Assigning distinct types 3.4.2.3 Using distinct types in UNIONs 3.4.2.4 Invoking functions with distinct types 3.4.3 Combining distinct types with user-defined functions and LOBs 4.0 Section 4. Designing a DB2 database application 4.1 Chapter 4-1. Planning for DB2 program preparation 4.1.1 Planning to process SQL statements 4.1.2 Planning to bind 4.1.2.1 Binding DBRMs with packages and plans 4.1.2.2 Planning for changes to your application 4.2 Chapter 4-2. Planning for concurrency 4.2.1 Definitions of concurrency and locks 4.2.2 Effects of DB2 locks 4.2.2.1 Suspension 4.2.2.2 Timeout 4.2.2.3 Deadlock 4.2.3 Basic recommendations to promote concurrency 4.2.3.1 Recommendations for database design 4.2.3.2 Recommendations for application design 4.2.4 Aspects of transaction locks 4.2.4.1 The size of a lock 4.2.4.2 The duration of a lock 4.2.4.3 The mode of a lock 4.2.4.4 The object of a lock 4.2.5 Options for tuning locks 4.2.5.1 Bind options 4.2.5.2 Isolation overriding with SQL statements 4.2.5.3 The LOCK TABLE statement 4.2.5.4 Access paths 4.2.6 LOB locks 4.2.6.1 Relationship between transaction locks and LOB locks 4.2.6.2 Hierarchy of LOB locks 4.2.6.3 LOB and LOB table space lock modes 4.2.6.4 LOB lock and LOB table space lock duration 4.2.6.5 Instances when LOB table space locks are not taken 4.2.6.6 The LOCK TABLE statement for LOBs 4.3 Chapter 4-3. Planning for recovery 4.3.1 Unit of work in TSO batch and online 4.3.2 Unit of work in CICS 4.3.3 Unit of work in IMS online programs 4.3.3.1 Planning ahead for program recovery: Checkpoint and restart 4.3.3.2 When are checkpoints important? 4.3.3.3 Checkpoints in MPPs and transaction-oriented BMPs 4.3.3.4 Checkpoints in batch-oriented BMPs 4.3.3.5 Specifying checkpoint frequency 4.3.4 Unit of work in DL/I and IMS batch programs 4.3.4.1 Commit and rollback coordination 4.3.4.2 Restart and recovery in IMS batch 4.3.5 Using savepoints to undo selected changes within a unit of work 4.4 Chapter 4-4. Planning to access distributed data 4.4.1 Planning for DRDA and DB2 private protocol access 4.4.1.1 Advantages of DRDA access 4.4.1.2 Moving from DB2 private protocol access to DRDA access 4.4.1.3 Bind processes for DRDA and DB2 private protocol access 4.4.1.4 Precompiler and bind options for DRDA access 4.4.2 Coding methods for distributed data 4.4.2.1 Using three-part table names to access distributed data 4.4.2.2 Using explicit CONNECT statements to access distributed data 4.4.3 Coordinating updates to two or more data sources 4.4.3.1 Working without two-phase commit 4.4.3.2 Update restrictions on servers that do not support two-phase commit 4.4.3.3 Forcing update restrictions by using CONNECT (Type 1) 4.4.4 Maximizing performance for distributed data 4.4.4.1 Coding efficient queries 4.4.4.2 Maximizing LOB performance in a distributed environment 4.4.4.3 Using bind options to improve performance for distributed applications 4.4.4.4 Using block fetch in distributed applications 4.4.4.5 Limiting the number of DRDA network transmissions 4.4.4.6 Limiting the number of rows returned to DRDA clients 4.4.5 Working with distributed data 4.4.5.1 SQL limitations at dissimilar servers 4.4.5.2 Executing long SQL statements in a distributed environment 4.4.5.3 Retrieving data from ASCII or Unicode tables 4.4.5.4 Accessing data with a scrollable cursor when the requester is down-level 4.4.5.5 Accessing data with a rowset-positioned cursor when the requester is down-level 4.4.5.6 Maintaining data currency by using cursors 4.4.5.7 Copying a table from a remote location 4.4.5.8 Transmitting mixed data 5.0 Section 5. Developing your application 5.1 Chapter 5-1. Preparing an application program to run 5.1.1 Steps in program preparation 5.1.1.1 Step 1: Process SQL statements 5.1.1.2 Step 2: Compile (or assemble) and link-edit the application 5.1.1.3 Step 3: Bind the application 5.1.1.4 Step 4: Run the application 5.1.2 Using JCL procedures to prepare applications 5.1.2.1 Available JCL procedures 5.1.2.2 Including code from SYSLIB data sets 5.1.2.3 Starting the precompiler dynamically 5.1.2.4 An alternative method for preparing a CICS program 5.1.2.5 Using JCL to prepare a program with object-oriented extensions 5.1.3 Using ISPF and DB2 Interactive 5.1.3.1 DB2I help 5.1.3.2 DB2I Primary Option Menu 5.1.3.3 DB2 Program Preparation panel 5.1.3.4 DB2I Defaults Panel 1 5.1.3.5 DB2I Defaults Panel 2 5.1.3.6 Precompile panel 5.1.3.7 Bind/Rebind/Free selection panel 5.1.3.8 Bind Package panel 5.1.3.9 Bind Plan panel 5.1.3.10 Rebind Package panel 5.1.3.11 Rebind Trigger Package panel 5.1.3.12 Rebind Plan panel 5.1.3.13 Free Package panel 5.1.3.14 Free Plan panel 5.1.3.15 The Defaults for Bind or Rebind Package or Plan panels 5.1.3.16 System Connection Types panel 5.1.3.17 Panels for entering lists of values 5.1.3.18 Program Preparation: Compile, Link, and Run panel 5.1.3.19 The Run panel 5.2 Chapter 5-2. Testing an application program 5.2.1 Establishing a test environment 5.2.1.1 Designing a test data structure 5.2.1.2 Filling the tables with test data 5.2.2 Testing SQL statements using SPUFI 5.2.3 Debugging your program 5.2.3.1 Debugging programs in TSO 5.2.3.2 Debugging programs in IMS 5.2.3.3 Debugging programs in CICS 5.2.4 Locating the problem 5.2.4.1 Analyzing error and warning messages from the precompiler 5.2.4.2 SYSTERM output from the precompiler 5.2.4.3 SYSPRINT output from the precompiler 5.3 Chapter 5-3. Processing DL/I batch applications 5.3.1 Planning to use DL/I batch applications 5.3.1.1 Features and functions of DB2 DL/I batch support 5.3.1.2 Requirements for using DB2 in a DL/I batch job 5.3.1.3 Authorization 5.3.2 Program design considerations 5.3.2.1 Address spaces 5.3.2.2 Commits 5.3.2.3 SQL statements and IMS calls 5.3.2.4 Checkpoint calls 5.3.2.5 Application program synchronization 5.3.2.6 Checkpoint and XRST considerations 5.3.2.7 Synchronization call abends 5.3.3 Input and output data sets for DL/I batch jobs 5.3.3.1 DB2 DL/I batch input 5.3.3.2 DB2 DL/I batch output 5.3.4 Preparation guidelines for DL/I batch programs 5.3.4.1 Precompiling 5.3.4.2 Binding 5.3.4.3 Link-editing 5.3.4.4 Loading and running 5.3.5 Restart and recovery 5.3.5.1 JCL example of a batch backout 5.3.5.2 JCL example of restarting a DL/I batch job 5.3.5.3 Finding the DL/I batch checkpoint ID 6.0 Section 6. Additional programming techniques 6.1 Chapter 6-1. Coding dynamic SQL in application programs 6.1.1 Choosing between static and dynamic SQL 6.1.1.1 Flexibility of static SQL with host variables 6.1.1.2 Flexibility of dynamic SQL 6.1.1.3 Limitations of dynamic SQL 6.1.1.4 Dynamic SQL processing 6.1.1.5 Performance of static and dynamic SQL 6.1.2 Caching dynamic SQL statements 6.1.2.1 Using the dynamic statement cache 6.1.2.2 Using the statement cache table 6.1.2.3 Keeping prepared statements after commit points 6.1.3 Limiting dynamic SQL with the resource limit facility 6.1.3.1 Writing an application to handle reactive governing 6.1.3.2 Writing an application to handle predictive governing 6.1.3.3 Using predictive governing and down-level DRDA requesters 6.1.3.4 Using predictive governing and enabled requesters 6.1.4 Choosing a host language for dynamic SQL applications 6.1.5 Dynamic SQL for non-SELECT statements 6.1.5.1 Dynamic execution using EXECUTE IMMEDIATE 6.1.5.2 Dynamic execution using PREPARE and EXECUTE 6.1.5.3 Dynamic execution of a multiple-row INSERT statement 6.1.5.4 Using DESCRIBE INPUT to put parameter information in an SQLDA 6.1.6 Dynamic SQL for fixed-list SELECT statements 6.1.6.1 Declaring a cursor for the statement name 6.1.6.2 Preparing the statement 6.1.6.3 Opening the cursor 6.1.6.4 Fetching rows from the result table 6.1.6.5 Closing the cursor 6.1.7 Dynamic SQL for varying-list SELECT statements 6.1.7.1 What your application program must do 6.1.7.2 Preparing a varying-list SELECT statement 6.1.7.3 Executing a varying-list SELECT statement dynamically 6.1.7.4 Executing arbitrary statements with parameter markers 6.1.7.5 How bind options REOPT(ALWAYS) and REOPT(ONCE) affect dynamic SQL 6.1.8 Using dynamic SQL in COBOL 6.2 Chapter 6-2. Using stored procedures for client/server processing 6.2.1 Introduction to stored procedures 6.2.2 An example of a simple stored procedure 6.2.3 Setting up the stored procedures environment 6.2.3.1 Defining your stored procedure to DB2 6.2.3.2 Refreshing the stored procedures environment (for system administrators) 6.2.3.3 Moving stored procedures to a WLM-established environment (for system administrators) 6.2.4 Writing and preparing an external stored procedure 6.2.4.1 Language requirements for the stored procedure and its caller 6.2.4.2 Calling other programs 6.2.4.3 Using reentrant code 6.2.4.4 Writing a stored procedure as a main program or subprogram 6.2.4.5 Restrictions on a stored procedure 6.2.4.6 Using COMMIT and ROLLBACK statements in a stored procedure 6.2.4.7 Using special registers in a stored procedure 6.2.4.8 Accessing other sites in a stored procedure 6.2.4.9 Writing a stored procedure to access IMS databases 6.2.4.10 Writing a stored procedure to return result sets to a DRDA client 6.2.4.11 Preparing a stored procedure 6.2.4.12 Binding the stored procedure 6.2.4.13 Writing a REXX stored procedure 6.2.5 Writing and preparing an SQL procedure 6.2.5.1 Comparison of an SQL procedure and an external procedure 6.2.5.2 Statements that you can include in a procedure body 6.2.5.3 Declaring and using variables in an SQL procedure 6.2.5.4 Parameter style for an SQL procedure 6.2.5.5 Terminating statements in an SQL procedure 6.2.5.6 Handling SQL conditions in an SQL procedure 6.2.5.7 Examples of SQL procedures 6.2.5.8 Preparing an SQL procedure 6.2.6 Writing and preparing an application to use stored procedures 6.2.6.1 Forms of the CALL statement 6.2.6.2 Authorization for executing stored procedures 6.2.6.3 Linkage conventions 6.2.6.4 Using indicator variables to speed processing 6.2.6.5 Declaring data types for passed parameters 6.2.6.6 Writing a DB2 UDB for z/OS client program or SQL procedure to receive result sets 6.2.6.7 Accessing transition tables in a stored procedure 6.2.6.8 Calling a stored procedure from a REXX procedure 6.2.6.9 Preparing a client program 6.2.7 Running a stored procedure 6.2.7.1 How DB2 determines which version of a stored procedure to run 6.2.7.2 Using a single application program to call different versions of a stored procedure 6.2.7.3 Running multiple stored procedures concurrently 6.2.7.4 Running multiple instances of a stored procedure concurrently 6.2.7.5 Accessing non-DB2 resources 6.2.8 Testing a stored procedure 6.2.8.1 Debugging the stored procedure as a stand-alone program on a workstation 6.2.8.2 Debugging with the Debug Tool and IBM VisualAge COBOL 6.2.8.3 Debugging an SQL procedure or C language stored procedure with the Debug Tool and C/C++ Productivity Tools for z/OS 6.2.8.4 Debugging with Debug Tool for z/OS interactively and in batch mode 6.2.8.5 Using the MSGFILE run-time option 6.2.8.6 Using driver applications 6.2.8.7 Using SQL INSERT statements 6.3 Chapter 6-3. Tuning your queries 6.3.1 General tips and questions 6.3.1.1 Is the query coded as simply as possible? 6.3.1.2 Are all predicates coded correctly? 6.3.1.3 Are there subqueries in your query? 6.3.1.4 Does your query involve aggregate functions? 6.3.1.5 Do you have an input variable in the predicate of an SQL query? 6.3.1.6 Do you have a problem with column correlation? 6.3.1.7 Can your query be written to use a noncolumn expression? 6.3.1.8 Can materialized query tables help your query performance? 6.3.1.9 Does the query contain encrypted data? 6.3.2 Writing efficient predicates 6.3.2.1 Properties of predicates 6.3.2.2 Predicates in the ON clause 6.3.3 General rules about predicate evaluation 6.3.3.1 Order of evaluating predicates 6.3.3.2 Summary of predicate processing 6.3.3.3 Examples of predicate properties 6.3.3.4 Predicate filter factors 6.3.3.5 Column correlation 6.3.3.6 DB2 predicate manipulation 6.3.3.7 Predicates with encrypted data 6.3.4 Using host variables efficiently 6.3.4.1 Changing the access path at run time 6.3.4.2 Rewriting queries to influence access path selection 6.3.5 Writing efficient subqueries 6.3.5.1 Correlated subqueries 6.3.5.2 Noncorrelated subqueries 6.3.5.3 Conditions for DB2 to transform a subquery into a join 6.3.5.4 Subquery tuning 6.3.6 Using scrollable cursors efficiently 6.3.7 Writing efficient queries on tables with data-partitioned secondary indexes 6.3.8 Special techniques to influence access path selection 6.3.8.1 Obtaining information about access paths 6.3.8.2 Fetching a limited number of rows: FETCH FIRST n ROWS ONLY 6.3.8.3 Minimizing overhead for retrieving few rows: OPTIMIZE FOR n ROWS 6.3.8.4 Favoring index access 6.3.8.5 Using the CARDINALITY clause to improve the performance of queries with user-defined table function references 6.3.8.6 Reducing the number of matching columns 6.3.8.7 Creating indexes for efficient star-join processing 6.3.8.8 Rearranging the order of tables in a FROM clause 6.3.8.9 Updating catalog statistics 6.3.8.10 Using a subsystem parameter 6.4 Chapter 6-4. Using EXPLAIN to improve SQL performance 6.4.1 Obtaining PLAN_TABLE information from EXPLAIN 6.4.1.1 Creating PLAN_TABLE 6.4.1.2 Populating and maintaining a plan table 6.4.1.3 Reordering rows from a plan table 6.4.2 Asking questions about data access 6.4.2.1 Is access through an index? (ACCESSTYPE is I, I1, N or MX) 6.4.2.2 Is access through more than one index? (ACCESSTYPE=M) 6.4.2.3 How many columns of the index are used in matching? (MATCHCOLS=n) 6.4.2.4 Is the query satisfied using only the index? (INDEXONLY=Y) 6.4.2.5 Is direct row access possible? (PRIMARY_ACCESSTYPE = D) 6.4.2.6 Is a view or nested table expression materialized? 6.4.2.7 Was a scan limited to certain partitions? (PAGE_RANGE=Y) 6.4.2.8 What kind of prefetching is expected? (PREFETCH = L, S, D, or blank) 6.4.2.9 Is data accessed or processed in parallel? (PARALLELISM_MODE is I, C, or X) 6.4.2.10 Are sorts performed? 6.4.2.11 Is a subquery transformed into a join? 6.4.2.12 When are aggregate functions evaluated? (COLUMN_FN_EVAL) 6.4.2.13 How many index screening columns are used? 6.4.2.14 Is a complex trigger WHEN clause used? (QBLOCKTYPE=TRIGGR) 6.4.3 Interpreting access to a single table 6.4.3.1 Table space scans (ACCESSTYPE=R PREFETCH=S) 6.4.3.2 Index access paths 6.4.3.3 UPDATE using an index 6.4.4 Interpreting access to two or more tables (join) 6.4.4.1 Definitions and examples of join operations 6.4.4.2 Nested loop join (METHOD=1) 6.4.4.3 Merge scan join (METHOD=2) 6.4.4.4 Hybrid join (METHOD=4) 6.4.4.5 Star join (JOIN_TYPE='S') 6.4.5 Interpreting data prefetch 6.4.5.1 Sequential prefetch (PREFETCH=S) 6.4.5.2 Dynamic prefetch (PREFETCH=D) 6.4.5.3 List prefetch (PREFETCH=L) 6.4.5.4 Sequential detection at execution time 6.4.6 Determining sort activity 6.4.6.1 Sorts of data 6.4.6.2 Sorts of RIDs 6.4.6.3 The effect of sorts on OPEN CURSOR 6.4.7 Processing for views and nested table expressions 6.4.7.1 Merge 6.4.7.2 Materialization 6.4.7.3 Using EXPLAIN to determine when materialization occurs 6.4.7.4 Using EXPLAIN to determine UNION activity and query rewrite 6.4.7.5 Performance of merge versus materialization 6.4.8 Estimating a statement's cost 6.4.8.1 Creating a statement table 6.4.8.2 Populating and maintaining a statement table 6.4.8.3 Retrieving rows from a statement table 6.4.8.4 The implications of cost categories 6.5 Chapter 6-5. Parallel operations and query performance 6.5.1 Comparing the methods of parallelism 6.5.2 Enabling parallel processing 6.5.3 When parallelism is not used 6.5.4 Interpreting EXPLAIN output 6.5.4.1 A method for examining PLAN_TABLE columns for parallelism 6.5.4.2 PLAN_TABLE examples showing parallelism 6.5.5 Tuning parallel processing 6.5.6 Disabling query parallelism 6.6 Chapter 6-6. Programming for the Interactive System Productivity Facility 6.6.1 Using ISPF and the DSN command processor 6.6.2 Invoking a single SQL program through ISPF and DSN 6.6.3 Invoking multiple SQL programs through ISPF and DSN 6.6.4 Invoking multiple SQL programs through ISPF and CAF 6.7 Chapter 6-7. Programming for the call attachment facility 6.7.1 CAF capabilities and requirements 6.7.1.1 CAF capabilities 6.7.1.2 CAF requirements 6.7.2 How to use CAF 6.7.2.1 Summary of connection functions 6.7.2.2 Accessing the CAF language interface 6.7.2.3 General properties of CAF connections 6.7.2.4 CAF function descriptions 6.7.2.5 CONNECT: Syntax and usage 6.7.2.6 OPEN: Syntax and usage 6.7.2.7 CLOSE: Syntax and usage 6.7.2.8 DISCONNECT: Syntax and usage 6.7.2.9 TRANSLATE: Syntax and usage 6.7.2.10 Summary of CAF behavior 6.7.3 Sample scenarios 6.7.3.1 A single task with implicit connections 6.7.3.2 A single task with explicit connections 6.7.3.3 Several tasks 6.7.4 Exit routines from your application 6.7.4.1 Attention exit routines 6.7.4.2 Recovery routines 6.7.5 Error messages and dsntrace 6.7.6 CAF return codes and reason codes 6.7.7 Program examples for CAF 6.7.7.1 Sample JCL for using CAF 6.7.7.2 Sample assembler code for using CAF 6.7.7.3 Loading and deleting the CAF language interface 6.7.7.4 Connecting to DB2 for CAF 6.7.7.5 Checking return codes and reason codes for CAF 6.7.7.6 Using dummy entry point DSNHLI for CAF 6.7.7.7 Variable declarations for CAF 6.8 Chapter 6-8. Programming for the Resource Recovery Services attachment facility 6.8.1 RRSAF capabilities and requirements 6.8.1.1 RRSAF capabilities 6.8.1.2 RRSAF requirements 6.8.2 How to use RRSAF 6.8.2.1 Summary of connection functions 6.8.2.2 Implicit connections 6.8.2.3 Accessing the RRSAF language interface 6.8.2.4 General properties of RRSAF connections 6.8.2.5 Summary of RRSAF behavior 6.8.3 RRSAF function descriptions 6.8.3.1 Register conventions 6.8.3.2 Parameter conventions for function calls 6.8.3.3 IDENTIFY: Syntax and usage 6.8.3.4 SWITCH TO: Syntax and usage 6.8.3.5 SIGNON: Syntax and usage 6.8.3.6 AUTH SIGNON: Syntax and usage 6.8.3.7 CONTEXT SIGNON: Syntax and usage 6.8.3.8 SET_ID: Syntax and usage 6.8.3.9 SET_CLIENT_ID: Syntax and usage 6.8.3.10 CREATE THREAD: Syntax and usage 6.8.3.11 TERMINATE THREAD: Syntax and usage 6.8.3.12 TERMINATE IDENTIFY: Syntax and usage 6.8.3.13 TRANSLATE: Syntax and usage 6.8.4 RRSAF connection examples 6.8.4.1 Example of a single task 6.8.4.2 Example of multiple tasks 6.8.4.3 Example of calling SIGNON to reuse a DB2 thread 6.8.4.4 Example of switching DB2 threads between tasks 6.8.5 RRSAF return codes and reason codes 6.8.6 Program examples for RRSAF 6.8.6.1 Sample JCL for using RRSAF 6.8.6.2 Loading and deleting the RRSAF language interface 6.8.6.3 Using dummy entry point DSNHLI for RRSAF 6.8.6.4 Connecting to DB2 for RRSAF 6.9 Chapter 6-9. CICS-specific programming techniques 6.9.1 Controlling the CICS attachment facility from an application 6.9.2 Improving thread reuse 6.9.3 Detecting whether the CICS attachment facility is operational 6.10 Chapter 6-10. Using WebSphere MQ with DB2 6.10.1 Introduction to WebSphere MQ message handling and the AMI 6.10.1.1 WebSphere MQ messages 6.10.1.2 WebSphere MQ services 6.10.1.3 WebSphere MQ policies 6.10.2 Using WebSphere MQ functions from DB2 applications 6.10.2.1 Commit environment for WebSphere MQ functions 6.10.2.2 How to use WebSphere MQ functions 6.10.3 Asynchronous messaging in DB2 UDB for z/OS and OS/390 6.10.3.1 MQListener in DB2 for OS/390 and z/OS 6.10.3.2 Configuring and running MQListener in DB2 UDB for OS/390 and z/OS 6.10.3.3 Configuring MQListener tasks 6.10.3.4 Creating a sample stored procedure to use with MQListener 6.10.3.5 MQListener examples 6.11 Chapter 6-11. Programming techniques: Questions and answers 6.11.1 Providing a unique key for a table 6.11.2 Scrolling through previously retrieved data 6.11.2.1 Using a scrollable cursor 6.11.2.2 Using a ROWID or identity column 6.11.3 Scrolling through a table in any direction 6.11.4 Updating data as it is retrieved from the database 6.11.5 Updating previously retrieved data 6.11.6 Updating thousands of rows 6.11.7 Retrieving thousands of rows 6.11.8 Using SELECT * 6.11.9 Optimizing retrieval for a small set of rows 6.11.10 Adding data to the end of a table 6.11.11 Translating requests from end users into SQL statements 6.11.12 Changing the table definition 6.11.13 Storing data that does not have a tabular format 6.11.14 Finding a violated referential or check constraint APPENDIX1 Appendixes APPENDIX1.1 Appendix A. DB2 sample tables APPENDIX1.1.1 Activity table (DSN8810.ACT) APPENDIX1.1.2 Department table (DSN8810.DEPT) APPENDIX1.1.3 Employee table (DSN8810.EMP) APPENDIX1.1.4 Employee photo and resume table (DSN8810.EMP_PHOTO_RESUME) APPENDIX1.1.5 Project table (DSN8810.PROJ) APPENDIX1.1.6 Project activity table (DSN8810.PROJACT) APPENDIX1.1.7 Employee to project activity table (DSN8810.EMPPROJACT) APPENDIX1.1.8 Unicode sample table (DSN8810.DEMO_UNICODE) APPENDIX1.1.9 Relationships among the sample tables APPENDIX1.1.10 Views on the sample tables APPENDIX1.1.11 Storage of sample application tables APPENDIX1.1.11.1 Storage group APPENDIX1.1.11.2 Databases APPENDIX1.1.11.3 Table spaces APPENDIX1.2 Appendix B. Sample applications APPENDIX1.2.1 Types of sample applications APPENDIX1.2.2 Using the sample applications APPENDIX1.2.2.1 TSO APPENDIX1.2.2.2 IMS APPENDIX1.2.2.3 CICS APPENDIX1.3 Appendix C. Running the productivity-aid sample programs APPENDIX1.3.1 Running DSNTIAUL APPENDIX1.3.2 Running DSNTIAD APPENDIX1.3.3 Running DSNTEP2 and DSNTEP4 APPENDIX1.4 Appendix D. Programming examples APPENDIX1.4.1 Sample COBOL dynamic SQL program APPENDIX1.4.1.1 Pointers and based variables APPENDIX1.4.1.2 Storage allocation APPENDIX1.4.1.3 Example APPENDIX1.4.2 Sample dynamic and static SQL in a C program APPENDIX1.4.3 Sample DB2 REXX application APPENDIX1.4.4 Sample COBOL program using DRDA access APPENDIX1.4.5 Sample COBOL program using DB2 private protocol access APPENDIX1.4.6 Examples of using stored procedures APPENDIX1.4.6.1 Calling a stored procedure from a C program APPENDIX1.4.6.2 Calling a stored procedure from a COBOL program APPENDIX1.4.6.3 Calling a stored procedure from a PL/I program APPENDIX1.4.6.4 C stored procedure: GENERAL APPENDIX1.4.6.5 C stored procedure: GENERAL WITH NULLS APPENDIX1.4.6.6 COBOL stored procedure: GENERAL APPENDIX1.4.6.7 COBOL stored procedure: GENERAL WITH NULLS APPENDIX1.4.6.8 PL/I stored procedure: GENERAL APPENDIX1.4.6.9 PL/I stored procedure: GENERAL WITH NULLS APPENDIX1.5 Appendix E. Recursive common table expression examples APPENDIX1.6 Appendix F. REBIND subcommands for lists of plans or packages APPENDIX1.6.1 Overview of the procedure for generating lists of REBIND commands APPENDIX1.6.2 Sample SELECT statements for generating REBIND commands APPENDIX1.6.3 Sample JCL for running lists of REBIND commands APPENDIX1.7 Appendix G. Reserved schema names and reserved words APPENDIX1.7.1 Reserved schema names APPENDIX1.7.2 Reserved words APPENDIX1.8 Appendix H. Characteristics of SQL statements in DB2 UDB for z/OS APPENDIX1.8.1 Actions allowed on SQL statements APPENDIX1.8.2 SQL statements allowed in external functions and stored procedures APPENDIX1.8.3 SQL statements allowed in SQL procedures APPENDIX1.9 Appendix I. Program preparation options for remote packages APPENDIX1.10 Appendix J. DB2-supplied stored procedures APPENDIX1.10.1 WLM environment refresh stored procedure (WLM_REFRESH) APPENDIX1.10.1.1 Environment for WLM_REFRESH APPENDIX1.10.1.2 Authorization required for WLM_REFRESH APPENDIX1.10.1.3 WLM_REFRESH syntax diagram APPENDIX1.10.1.4 WLM_REFRESH option descriptions APPENDIX1.10.1.5 Example of WLM_REFRESH invocation APPENDIX1.10.2 The CICS transaction invocation stored procedure (DSNACICS) APPENDIX1.10.2.1 Environment for DSNACICS APPENDIX1.10.2.2 Authorization required for DSNACICS APPENDIX1.10.2.3 DSNACICS syntax diagram APPENDIX1.10.2.4 DSNACICS option descriptions APPENDIX1.10.2.5 DSNACICX user exit routine APPENDIX1.10.2.6 Example of DSNACICS invocation APPENDIX1.10.2.7 DSNACICS output APPENDIX1.10.2.8 DSNACICS restrictions APPENDIX1.10.2.9 DSNACICS debugging APPENDIX1.10.3 IMS transactions stored procedure (DSNAIMS) APPENDIX1.10.3.1 Environment for DSNAIMS APPENDIX1.10.3.2 Authorization required for DSNAIMS APPENDIX1.10.3.3 DSNAIMS syntax diagram APPENDIX1.10.3.4 DSNAIMS option descriptions APPENDIX1.10.3.5 Examples of DSNAIMS invocation APPENDIX1.10.3.6 Connecting to multiple IMS subsystems with DSNAIMS BACK_1 Notices BACK_1.1 Programming interface information BACK_1.2 Trademarks GLOSSARY Glossary BIBLIOGRAPHY Bibliography INDEX Index COMMENTS Readers' Comments -- We'd Like to Hear from You
© Copyright IBM Corp. 1983, 2004
IBM Library Server Copyright 1989, 2005 IBM Corporation. All rights reserved.