Implementing Practical DB2 Applications

Implementing Practical DB2 Applications provides a concise source of information for the development and implication of applications using IBM's DB2 relational database package in the MVS environment. The book describes the flagship DB2 version, namely that implemented for the MVS operating system environment where DB2 operates with the MVS transaction processing subsystems: CICS, IMS and TSO. The book is intended for both beginners and experts. It describes how the various components of SQL are used to provide practical applications. Containing tips and notes that were discovered the hard way - through hands on experience - this book will be welcomed by all those looking to implement applications in DB2.

1. Introduction.- 1.1 DATABASE2 (DB2) environment.- 1.2 DB2 data objects.- 1.3 Access to DB2 tables.- 1.4 Use of SQL.- 1.5 Use of SQL from applications.- 2. Application Design.- 2.1 Application design.- 2.2 Application considerations.- 2.2.1 Dialogue or batch?.- 2.2.2 Command procedure.- 2.2.3 Static or dynamic SQL?.- 2.2.4 Changes to the database.- 2.2.5 Authorisation considerations.- 2.3 Which product to use.- 3. QMF Facilities.- 3.1 Introduction.- 3.2 QMF objects.- 3.2.1 QMF query.- 3.2.2 QMF data.- 3.2.3 QMF form.- 3.2.4 QMF report.- 3.2.5 QMF procedure.- 3.2.6 QMF profile.- 3.2.7 QMF chart.- 3.3 QMF invocation.- 3.4 QMF command facilities.- 3.4.1 Transfer of QMF items to/from host system.- 3.4.2 Administration of QMF objects.- 3.4.3 Invoke host system component.- 3.5 Invocation of QMF.- 3.5.1 QMF files.- 3.5.2 Sample CLIST for invocation of QMF.- 3.6 QMF callable interface.- 3.6.1 The DSQCOMM communications area.- 3.6.2 Callable interface invocation syntax.- 3.6.3 START command.- 3.6.4 Examples.- 3.7 QMF command interface.- 3.7.1 QMF status.- 3.7.2 Passing parameters to a QMF command.- 4. QMF Commands.- 4.1 Introduction.- 4.2 Commands.- 4.2.1 DISPLAY - Display an object.- 4.2.2 END - End current operation.- 4.2.3 ERASE - Remove object from database.- 4.2.4 EXIT - Terminate QMF session.- 4.2.5 EXPORT - Transfer object to external dataset.- 4.2.6 HELP - Display help information.- 4.2.7 IMPORT - Import object into the QMF environment.- 4.2.8 INTERACT - Provide interactive support.- 4.2.9 ISPF - Invoke ISPF/PDF panel.- 4.2.10 LIST - Display list of QMF objects.- 4.2.11 MESSAGE - Display message.- 4.2.12 PRINT - Print content of object.- 4.2.13 QMF - Execute explicit QMF command.- 4.2.14 RESET - Clear object.- 4.2.15 RESET QUERY - Clear query object.- 4.2.16 RESET GLOBAL - Clear global variable.- 4.2.17 RUN - Execute object.- 4.2.18 SAVE - Save contents of work area.- 4.2.19 SAVE DATA - Save contents of data area.- 4.2.20 SAVE PROFILE - Save profile.- 4.2.21 SET (PROFILE) - Set profile.- 4.2.22 SET GLOBAL - Set global variable.- 4.2.23 STATE - Return QMF status.- 4.2.24 TSO - Invoke TSO command.- 4.3 Example.- 4.4 External QMF data.- 4.5 QMF external data format.- 4.6 IXF data.- 4.7 QMF external data.- 4.8 QMF data format.- 4.9 QMF report data.- 5. Program Environment.- 5.1 Introduction.- 5.2 JCL procedures.- 5.2.1 PL/I preprocessor.- 5.2.2 DB2 precompiler.- 5.2.3 Compile.- 5.2.4 Pre-linkedit.- 5.2.5 Linkedit.- 5.2.6 Bind.- 5.3 Example.- 5.4 DB2I (DB2 Interactive).- 6. DB2 program Invocation.- 6.1 Introduction.- 6.2 Commands pertaining to DB2.- 6.2.1 DSN - Invoke DB2 session.- 6.2.2 DSNH - Prepare program in foreground.- 6.3 DB2 subcommands.- 6.3.1 BIND PACKAGE - Build application package.- 6.3.2 BIND PLAN - Build application plan.- 6.3.3 DCLGEN - Table declaration generator.- 6.3.4 END - Terminate DB2 session.- 6.3.5 FREE - Free application plan.- 6.3.6 REBIND - Rebind application plan.- 6.3.7 RUN - Invoke DB2 program.- 6.3.8 SPUFI - SQL Processor Using File Input.- 6.4 DB2I (DATABASE2 Interactive).- 6.4.1 Invoke SPUFI.- 6.4.2 Invoke DCLGEN.- 6.4.3 Perform DB2 Program Preparation.- 6.4.4 Perform DB2 Precompile.- 6.4.5 Perform DB2 Bind function.- 6.4.6 Run an SQL program.- 6.4.7 Set DB2I global default parameters.- 6.5 Invoke program independent of DB2 (CAF interface).- 6.5.1 CLOSE function.- 6.5.2 CONNECT function.- 6.5.3 DISCONNECT function.- 6.5.4 OPEN function.- 6.5.5 TRANSLATE function.- 6.5.6 CAF interface program.- 7. Issuing SQL statements from within a program.- 7.1 Introduction.- 7.2 SQL status.- 7.2.1 DSNTIAR subroutine.- 7.3 Program host variables.- 7.3.1 PL/I host variables.- 7.3.2 COBOL host variables.- 7.3.3 Assembler host variables.- 7.3.4 C/370 host variables.- 7.4 Indicator variables.- 7.5 Use of SQL from programs.- 7.5.1 Static SQL.- 7.5.2 Static SQL select with a fixed number of columns.- 7.5.3 Dynamic SQL.- 7.5.4 Dynamic SQL with a variable number of columns.- 8. Embedded SQL Statements.- 8.1 Introduction.- 8.2 Statements.- 8.2.1 BEGIN DECLARE - Start declaration of host variables.- 8.2.2 CLOSE - Close cursor.- 8.2.3 COMMIT - Make outstanding database changes permanent.- 8.2.4 DECLARE CURSOR - Define cursor.- 8.2.5 DECLARE STATEMENT - Define SQL statement name.- 8.2.6 DECLARE TABLE - Define table structure.- 8.2.7 DELETE - Delete rows.- 8.2.8 DESCRIBE - Obtain information about a prepared statement.- 8.2.9 END DECLARE - Terminate declarations.- 8.2.10 EXECUTE - Execute a prepared statement.- 8.2.11 EXECUTE IMMEDIATE - Prepare and execute an executable SQL statement.- 8.2.12 EXPLAIN - Obtain processing statistics.- 8.2.13 FETCH - Position cursor to next row.- 8.2.14 INCLUDE - Include definitions.- 8.2.15 INSERT - Insert rows.- 8.2.16 LOCK TABLE - Set lock.- 8.2.17 OPEN - Create results table for select.- 8.2.18 PREPARE - Prepare SQL statement.- 8.2.19 ROLLBACK - Revoke outstanding database changes.- 8.2.20 SELECT INTO - Produce single-row results table.- 8.2.21 UPDATE - Update database rows.- 8.2.22 WHENEVER - Define exception processing.- 9. Transaction Processing.- 9.1 Introduction.- 9.2 SQL transaction concepts.- 9.3 Consistency.- 9.4 Concurrency.- 10. Testing.- 10.1 Introduction.- 10.2 DB2 testing tools.- 10.3 Testing of programs using DB2 (SQL) facilities.- 10.3.1 Testing using the CAF interface.- 10.3.2 Testing by creating the DB2 environment.- 10.3.3 Testing using ISPF/PDF Dialog Test.- 10.4 Commit/Rollback in the testing environment.- 10.5 Summary.- 11. Worked Example.- 11.1 Introduction.- 11.2 Problem specification.- 11.3 QMF solution.- 11.4 Program solution.- 11.5 Summary.- Appendix A. Syntax Notation.- Appendix B. SQL Control Areas.- Appendix C. Glossary.