Db2 Reference Handbook April 2004

DB2 REFERENCE HANDBOOK April 2004

Developed by Administrative Technology Services (ATS) Iowa State University Ames, IA

Copyright © 1988-2004 Iowa State University Administrative Technology Services. All rights reserved. Contributors: Past and present employees of the ISU ATS Center have helped develop and maintain this handbook. DBA Contacts as of 06/18/2003: Tom Logue Dan Crim Casey Vanlandingham Al Glick All rights reserved. No part of this writing may be reproduced in any form or by any means without permission in writing from Iowa State University.

Disclaimer Although the information contained in this handbook has been used by the contributors and the ISU Administrative Technology Services, neither the contributors, ISU Administrative Technology Services, Iowa State University, nor the State of Iowa, express or imply any warranty as to the accuracy and functioning of the information, any program developed using this information, or any related materials. Distribution does not constitute any such warranty. Neither the contributors, ISU Administrative Technology Services, Iowa State University, nor the State of Iowa assume responsibility in connection with this distribution. It is understood that no claims will be made against the contributors, ISU Administrative Technology Services, Iowa State University, or the State of Iowa in connection with the information contained in this handbook or with related materials.

Preface This handbook is a reference guide to the use of IBM's DB2 System within Administrative Technology Services, Iowa State University, Ames, Iowa. It gives attention to the development steps an analyst should take in the design and implementation of DB2 application systems. This handbook is intended to be a reference source for programmer/analysts to aid them in their development of application systems using DB2. It contains many hints, tips and techniques that can be used during the development process.

Iowa State University ADP Center DB2 Reference Handbook

2

DB2 REFERENCE HANDBOOK .............................................................................................. 1 DB2 ................................................................................................................................................. 6 DB2 Objects .............................................................................................................................................................6 Database ................................................................................................................................................................6 Storage Group........................................................................................................................................................6 Table Space............................................................................................................................................................6 Table ......................................................................................................................................................................6 Index ......................................................................................................................................................................6 View ......................................................................................................................................................................6 ATS System Development.......................................................................................................................................7 Test System Development Checklist and Implementation to Production Checklist..............................................7 (Refer to the System Development Checklist on SIR) ............................................................................................7 System Design ..........................................................................................................................................................7 Table Definition.....................................................................................................................................................7 Index Definition.....................................................................................................................................................8 Guidelines for Selecting Index Columns ...............................................................................................................9 System Design Review .............................................................................................................................................9 (Refer to the System Development Checklist on SIR) ............................................................................................9 Pre-Implementation Review ..................................................................................................................................9 (Refer to the System Development Checklist on SIR) ............................................................................................9 System Documentation..........................................................................................................................................10 File Definition System - Table Definition.............................................................................................................11 JPL File Definition System Screen field descriptions .........................................................................................11 File Definition System - Index Definition.............................................................................................................13 JPL File Definition System Screen field descriptions for INDEXes ...................................................................13 File Definition System - Table Authorizations ....................................................................................................15 File Definition System - Sequential Backup File.................................................................................................16 Data Dictionary......................................................................................................................................................17 Data Dictionary - Index Information ...................................................................................................................19 Data Dictionary System Index Screen field descriptions:....................................................................................19 DB2 SYSADM - For TEST System ......................................................................................................................20 Source Control System ..........................................................................................................................................21 Batch - DP function .............................................................................................................................................21 DB2 Reminder.....................................................................................................................................................21 DB2 Authorities .....................................................................................................................................................22 DB2 Authorizations ...............................................................................................................................................22 DB2 Table Access: ..............................................................................................................................................22 Procedures: ..........................................................................................................................................................23 Execution ................................................................................................................................................................23 Required DB2 Authorization................................................................................................................................23 DB2 Execute Authorities.......................................................................................................................................23 DB2 Authorizations for ODBC Applications ......................................................................................................23 DB2 Authorizations - Changing ...........................................................................................................................24 JPL - Job Procedure Language ............................................................................................................................25 DB2 Utilities ...........................................................................................................................................................27 JCL Generation and Storage Locations ...............................................................................................................27 Program Development...........................................................................................................................................28 Accessing DB2 Tables.........................................................................................................................................28 SQL Statement Format Standards........................................................................................................................28 I/O Limits - CICS ................................................................................................................................................28 SQL Programming Standards, Guidelines and Restrictions ................................................................................28 COBOL Generator Information...........................................................................................................................30 Guidelines on the coding of SQL statements:......................................................................................................31 DB2 Null Columns ..............................................................................................................................................32 Iowa State University ADP Center DB2 Reference Handbook

3

DB2 NULL Columns - Load/Unload Dataset Structure ......................................................................................32 SQL Statement Usage using NULLS ..................................................................................................................33 CICS Browse Logic.............................................................................................................................................35 Forward and Backward Browses .........................................................................................................................35 Order By Clause ..................................................................................................................................................35 UPDATE SQL.....................................................................................................................................................36 Batch Easytrieve Plus Hint ..................................................................................................................................36 Program Compile and Bind .................................................................................................................................37 Production Compiles/Binds and SYSD/RMDS...................................................................................................38 Access Path Restrictions and Recommendations.................................................................................................39 Call/Linked Programming Standards .................................................................................................................40 Easytrieve Batch ..................................................................................................................................................40 COBOL II Batch..................................................................................................................................................40 Linked CICS Programs........................................................................................................................................40 DB2 EXPLAIN Information.................................................................................................................................41 DB2 Explain Report Reminder............................................................................................................................41 Access Path Restrictions and Recommendations.................................................................................................41 Purpose of Explain...............................................................................................................................................42 How to Use EXPLAIN ........................................................................................................................................42 PLAN_TABLE Contents.....................................................................................................................................43 DB2 Access Path Selection .................................................................................................................................45 Determining Access Paths From EXPLAIN........................................................................................................45 Previous Explain Reports ....................................................................................................................................45 SQL General Information.....................................................................................................................................46 Methods of Joining Tables...................................................................................................................................48 Sub Queries ............................................................................................................................................................51 Non-correlated Sub Queries ................................................................................................................................51 Correlated Sub Queries........................................................................................................................................52 Implementing a Correlated Sub Query ................................................................................................................52 SQL Examples .......................................................................................................................................................56 "AS CLAUSE" To Name Result Columns ..........................................................................................................56 Using "AS CLAUSE" To Name Result Columns ...............................................................................................56 Using "AS CLAUSE" In ORDER BY Clause.....................................................................................................56 Nested Table Expression With "AS CLAUSE”...................................................................................................56 OUTER JOIN - FULL .........................................................................................................................................56 OUTER JOIN - LEFT .........................................................................................................................................56 OUTER JOIN - RIGHT.......................................................................................................................................56 OUTER JOIN FULL With Using "COALESCE" Function ...............................................................................57 Three Way Join....................................................................................................................................................57 Common SQL Return Codes and Possible Solutions .........................................................................................58 Common ABEND Codes.....................................................................................................................................59 Batch Processing General Information................................................................................................................60 Limits on DB2 Internal Sort ................................................................................................................................60 Automatic Rebinds ..............................................................................................................................................60 Batch Abends.......................................................................................................................................................61 DB2 Views ..............................................................................................................................................................62 MS Query Client Education .................................................................................................................................63 MS Query Summary .............................................................................................................................................63 MS Query - Excel - Returning DB2 Data from MS-Query and Passwords.....................................................63 MS Query - Important Reminder for Users .........................................................................................................64 QMF General Information ...................................................................................................................................65 Canceling an Executing QMF Query...................................................................................................................66 Viewing a List of Saved QMF Objects................................................................................................................66 Previous Production Explain Reports ..................................................................................................................66 Explaining SQL Statements Using QMF.............................................................................................................66 QMF EXPORT DATA Command ......................................................................................................................67 QMF SAVE Command........................................................................................................................................67 QMF - Printed Output..........................................................................................................................................68 QMF - Moving Objects between Production and Test ........................................................................................68 QMF Common Command Summary...................................................................................................................69 Iowa State University ADP Center DB2 Reference Handbook

4

QMF Edit Codes - ATS Defined .........................................................................................................................70 QMF Governor Information ................................................................................................................................71 QMF End Users - Contact Guidelines .................................................................................................................72 QMF Operational Situations:...............................................................................................................................72

Batch QMF Processing ..................................................................................................................73 Batch QMF Queries.............................................................................................................................................74 Batch QMF Query and JPL .................................................................................................................................74 Code the DB2 Tables Used in Batch QMF Query...............................................................................................74 Changing DB2 Table Structures ..........................................................................................................................75 Altering a DB2 Table to Add Column(s).............................................................................................................75 Changing a DB2 Table(s) to Change a Column Name, Size or Format ..............................................................75 Steps Required to Change DB2 Table Structures ................................................................................................75 TEST DB2 ...........................................................................................................................................................75 PRODUCTION DB2 ...........................................................................................................................................76 Changing DB2 Index Structures ..........................................................................................................................78 Change an Existing Index....................................................................................................................................78 TEST DB2 ...........................................................................................................................................................78 PRODUCTION DB2 ...........................................................................................................................................78 Adding a New Index ..............................................................................................................................................79 TEST DB2 ...........................................................................................................................................................79 PRODUCTION DB2 ...........................................................................................................................................79 Sample JCL To Execute Batch COBOL Programs - Test ...............................................................................80 BATCH COBOL Programming Sample .............................................................................................................81 CICS COBOL Programming Sample - BROWSE Program:............................................................................88 CICS COBOL Programming Sample - DISPLAY Program.............................................................................91 CICS COBOL Programming Sample - UPDATE Program:.............................................................................93

SQL Warnings and SQL Error Code Expansion ..............................................97 Appendix A - Example Tablespace/Index/Load Control Statements................................................................97 Appendix B - Example GRANT Statements .......................................................................................................99 Appendix C - Example Table Create Statements .............................................................................................100 Appendix D - DB2 Example Output from LOAD Utility.................................................................................101 Appendix E - DB2 Example Output from IMAGE COPY and RUNSTATS Utilities ..................................102 Appendix F - DB2 Example Output from REORG and QUIESCE Utilities..................................................103

Iowa State University ADP Center DB2 Reference Handbook

5

DB2 DB2 is a Relational DataBase Management System (RDBMS). In a relational database, data is stored in a simple, tabular format similar to the row-and-column structure familiar to users of spreadsheet software. These structures are called tables. A database is a collection of tables and other related DB2 objects. DB2 data objects include databases, storage groups, table spaces, tables, indexes, and views. These terms are described briefly below to provide a definition for future sections of this document. A more detailed description of these objects can be found in the IBM DB2 Administration Guide.

DB2 Objects Database As described above, databases are a collection of DB2 objects. When you define a database, you give a name to an eventual collection of tables, indexes, and table spaces in which they reside. A single database, for example, may contain all the data associated with students (students, courses, grades, etc). Databases do not physically exist, but they are a logical group of DB2 objects that DB2 uses to assign certain authorities and that permit sensible management of data.

Storage Group A storage group is a named set of direct access storage device (DASD) volumes from which DB2 automatically allocates storage space, defines the necessary VSAM datasets, and extends or deletes them as required.

Table Space A table space is a VSAM dataset in which one or more tables are stored. It is a physical object for which disk space is allocated using primary and secondary quantities. A table space is broken up into pages of four kilobytes each, which DB2 uses as the unit of I/O. DB2 utilities (RUNSTATS, REORG, and COPY) all run against table spaces (not tables).

Table A table contains data about a given entity. A table is made up of rows (tuples) and columns (attributes). The rows of a table are unordered, and are physically stored in a table space. For example, a student entity (table) would consist of a row for each student and columns such as student ID, student name, and student status. A table may be defined to have a primary key, a column or set of columns whose values uniquely identify each row (student ID in the student entity).

Index An index is an ordered set of pointers to the data in a table, stored separately from the table. Each index is based on the values of data in one or more columns of a table. Once an index is created, it is maintained by DB2, and DB2 decides when to use or not to use the index to access data in a table. Indexes serve two purposes: they are used to enforce uniqueness of rows in a table, and they enhance the performance of data retrieval operations.

View A view is a logical table that is derived from combining tables and/or other views into a single, logical table. A view can also be a subset of columns from a single table or view. Like a table, a view consists of rows and columns, but unlike a table, the data in a view is not physically stored. A view is defined to DB2 by referencing other views or tables, and the definition of the view is the only thing that is physically stored in the DB2 Catalog. When a user references a view, DB2 assembles the data from the underlying tables and views according to the definition. It is essentially transparent to the user whether the base table or logical view is being used. Views are a powerful tool used in relational databases, which can be used to simplify SQL coding, and as a security device to restrict which columns of a table a user can access.

Iowa State University ADP Center DB2 Reference Handbook

6

ATS System Development Test System Development Checklist and Implementation to Production Checklist (Refer to the System Development Checklist on SIR)

System Design Start by reviewing the notebook labeled Database Design Information. See a Database Administrator for a copy of this notebook. It covers various aspects of DB2 Database Design that are very important to know before your design begins and to ensure that the production system will be flexible and operate efficiently. Write a summary of the application specifications and get agreement from the client department. Define key data elements, type of transactions (CICS, QMF, Query Tool or Web based) that will be used the majority of the time, and the reports needed by the client.

Table Definition Decide which data elements can be stored in one row and if multiple tables are necessary for data that is related by one-to-many. Experience with DB2 has shown that high CICS transaction processing can have the data stored in normalized tables. But high QMF, Query Tool, or Web based query usage requires the data to be stored in slightly de-normalized tables to avoid large numbers of table JOINS, which increase CPU usage. Try and find a balance point in between to have both flexibility AND performance. Define table structures and perform normalization of the data elements and de-normalization if required, documenting the reasons for the de-normalization. The following are some table design guidelines: 1.

There is a maximum of 750 columns per table row, and each row should not be longer than 4000 bytes so that DB2 can use the more efficient 4K pages rather than 32K pages.

2.

Minimize the number of tables with row length less than 32 bytes and greater than 2037.

3.

Use NULL as a default value for a column under the following conditions: • It is used as a Primary or Foreign key when using DB2's Referential Integrity • It is a DATE type column and the date is normally unknown

4.

Numeric data types are more efficient.

5.

Use DATE, TIME, and TIMESTAMP data types when date and time fields are required. Be aware that when using these column types, a valid DATE, TIME, or TIMESTAMP must be in the column. They cannot contain spaces when defined as NOT NULL WITH DEFAULT.

DB2 Date and Time Formats Format Abbreviation ISO USA EUR

DB2 Date Type Format Example yyyy-mm-dd 1993-08-12 mm/dd/yyyy 08/12/1993 dd.mm.yyyy 12.08.1993

Format hh.mm.ss hh.mm AM hh.mm.ss

6.

If columns are used in a JOIN, data type and length should be the same.

7.

Choose VARCHAR columns carefully • Avoid VARCHAR if sufficient disk space is available.

Iowa State University ADP Center DB2 Reference Handbook

DB2 Time Type Example 13.30.05 1:30 AM 13.30.05

7

• • • • •

Avoid VARCHAR if the maximum length of the column is less than 30 bytes. Do not use VARCHAR if the data does not vary from row to row. Do not use VARCHAR if the average length of the column is within 10 bytes of the maximum length of the column. VARCHAR columns should always be placed at the end of the row. Consider placing multiple rows with a fixed length column and sequence number in another table to provide varying lengths of data.

8.

Columns that are frequently updated together should be grouped together in the DB2 table layout to reduce the amount of DB2 logging when an UPDATE SQL statement is executed.

9.

When denormalizing for performance reasons, be sure to document the previous design and the reasons for the denormalization.

10.

When data items from two or more tables are nearly always accessed together, create a single table rather than multiple separate tables.

Index Definition Define the index or indexes that will be needed to retrieve the data from the table. The following should be considered when selecting indexes: 1.

Each DB2 table should have at least one index and it should be unique.

2.

The number of indexes per table should be kept to a minimum.

3.

Avoid unnecessary indexes. Multiple indexes per DB2 table are allowed but try and keep the number of indexes per table as low as possible.

4.

Evaluate CICS access • For performance, most functions of a CICS system require an index to access the data, since table/index scans are to be avoided. • A system may have multiple CICS browse functions defined per DB2 table by defining multiple indexes to satisfy these browse functions. • Programming logic has been developed to provide "Previous Screen" capabilities in browse programs. It will allow the client to go back to previously viewed screens by using the already defined ascending index. Contact a Database Administrator for an explanation and sample of this programming logic.

5.

Evaluate Batch access. • Avoid creating indexes that are used only for batch. It may be more efficient to use the clustering index with an index scan and sort the rows after they are returned to the program. The exception is if a batch job runs daily or more than once a week and the data needed is a small subset of the entire table.

6.

Evaluate QMF, Query Tool, or Web based access. • Examine the indexes that are already defined for batch and CICS and determine if additional indexes are required.

7.

Indexes used mostly by CICS should be defined as unique. Indexes used mostly by QMF, Query Tool, or Web based can be defined as non-unique.

8.

Consider how data will be added (Clustering index consideration). Only one index can be defined as the CLUSTERING index. This means that the physical sequence of the data in the table matches the physical sequence of the keys in this index. Be sure that the data that is to be loaded into the table is in clustering index sequence.

9.

Consider table relationships and create indexes on columns that will be used in QMF, Query Tool, or Web based and batch JOIN operations.

Iowa State University ADP Center DB2 Reference Handbook

8

10.

Maximum length of the combined key fields that make up a key to an index is 255 bytes.

11.

Avoid indexes that have a low Cluster Ratio (<45%), unless the index is to be used for direct selection. If a large number of rows are returned in a batch program, a QMF query, a Query Tool, or a Web based query that uses a low Cluster Ratio index, the elapsed time of the batch job or the QMF, Query Tool, or Web based query may actually be higher than if the index did not exist.

Guidelines for Selecting Index Columns 1.

Determine the most frequent methods the client will use to get to the data. These columns are prime candidates for indexes.

2.

Keep the length of the key as small as possible and less than 40 bytes.

3.

Avoid columns that are frequently updated.

4.

Consider columns that are frequently used in selections, projections, groupings, ordering, or NOT EXISTS.

5.

Avoid indexes on: • Tables with less than 7 data pages (4K), unless uniqueness of the rows in the table is required. • Non-unique columns. • VARCHAR columns.

System Design Review A system design review process has been established to help you with the design and implementation of new DB2 application systems. The process is broken into three parts. A Preliminary Database Design Review where the basic concepts of the new database are discussed between the database analyst and the Database Administration Team. Then a written System Design Review before any application coding takes place, and a Pre-Implementation Review, stress testing the CICS functions in TEST CICS before the programs are moved to production.

Be sure to allow time for these reviews. (Refer to the System Development Checklist on SIR)

Remember that the standards for CICS total SQL executions per transaction are as follows: 30 80

Any CICS DB2 application program coded with SELECT, UPDATE, INSERT and/or DELETE SQL statements (usually display/update programs). Any CICS DB2 application program coded with DECLARE CURSOR/FETCH SQL statements (usually browse programs). No updates, inserts or deletes.

Pre-Implementation Review (Refer to the System Development Checklist on SIR)

Iowa State University ADP Center DB2 Reference Handbook

9

System Documentation Determine the system number and define/enter system title record in the System Documentation system. The system number will be defined as follows: XX99 where XX will be the 2-character client area and the 99 will be the number of the system within the area (e.g. DB03). The system number will normally be used as the basis for the DB2 Database Number. Enter the required information in the System Documentation system under the system number that was previously defined. The information should include history of the previous system, if any, system objectives, and program flow for the CICS system. In developing the CICS system flow, the program numbers will need to be determined. Also the transaction IDs will need to be determined. Both the program numbers and the transaction IDs will be needed as input to other systems during the development process.

Iowa State University ADP Center DB2 Reference Handbook

10

File Definition System - Table Definition JPL File Definition System Screen field descriptions

Table Descr:

All DB2 TABLESPACE IDs will be defined in the File Definition System with a seven-character name as follows: xxByyyz where xx is the alphabetic twocharacter client prefix; B designates a production data base; yyy is a unique three-digit number; z is a zero (0) for a TABLESPACE. The DB2 TABLESPACE description.

Emp#:

The ATS employee number of the person responsible for this data.

Billing No:

The ATS User billing number.

System Num:

Prod Bill:

This field must contain the ‘ssss’ portion of the DATABASE identifier as used in the Data Dictionary. Every TABLESPACE must be defined within a DB2 DATABASE. All DATABASEs will be defined for the analyst and the analyst will be granted authority to CREATE new TABLESPACEs and INDEXes within this DATABASE. The DATABASE name will be DBssssP for production and DBssssT for test, where 'ssss' is the DATABASE Identifier. A Database Administrator can help to determine the appropriate value. A value of 'Y' or 'N' indicating the billing status of the TABLESPACE.

Rows:

The number of rows expected in this TABLE, six months from now.

Avg Char/Row:

The average length of each row within the TABLE. If this is a variable length table, estimate an average length for the rows. If this is a fixed length table, look at the comments in the middle of the DCLGEN copybook defined for this table. See the section entitled Data Definition and Relationships Storage in this manual for the location of this DCLGEN. Use the length defined by 'INTERNAL DB2 TABLE LENGTH = '. The maximum length of each row within the TABLE. Look at the comments in the middle of the DCLGEN copybook defined for this table. See the section entitled Data Definition and Relationships Storage in this manual for the location of this DCLGEN. Use the length defined by 'INTERNAL DB2 TABLE LENGTH = '. The value 'F' (fixed) or 'V' (variable) indicating the format.

FILE ID:

Max Char/Row:

Format: Page Size:

Page Pctfree:

Freespace every XXX PAGES:

Default is 4096. The only other option is 32768 (32K), which is highly discouraged. This means that if the table rows are longer than 4096 bytes, a 32k page size is needed, but consider splitting the table rows into multiple TABLEs before using a 32K page size. Specify a percentage of each page (4K) to be left empty when loading or reorganizing the TABLE. This space is used for adding new rows and expanding the length of existing rows. If no new rows will be added, specify 00 PCTFREE; otherwise, specify enough freespace to allow entire rows to be added. If AVG CHAR/ROW = 400, 10% freespace (4096 x .1 = 410) will allow adding 1 row per page without having to split the page. Use the ‘FS’ function to determine an efficient value. The value for this field should usually be 000. DB2 allows leaving every Nth page empty when loading a TABLE. There are 150 4K pages per cylinder on a 3380 disk drive. Assuming the TABLE is at least 1 cylinder or greater: FREEPAGE EVERY 050 PAGES would provide 3 empty pages/cyl or 2% FREEPAGE EVERY 025 PAGES would provide 6 empty pages/cyl or 4%

Iowa State University ADP Center DB2 Reference Handbook

11

FREEPAGE EVERY 010 PAGES would provide 15 empty pages/cyl or 10% The FREEPAGE and PCTFREE figures should be based upon the expected TABLE activity and growth. Segsize:

Use a value of 32 or 64.

Table Name:

This is the table name as defined to the Data Dictionary. When a new table is being entered into the File System, the message: USE Data Dictionary TO ADD TABLE NAME will be displayed in this field. The field cannot be updated. After the table name has been entered in the Dictionary, it will appear in this field the next time that this screen is displayed. This is the table name as defined to DB2 to be used in SQL. When a new table is being entered into the File System, the message: USE Data Dictionary TO ADD TABLE NAME will be displayed in this field. The field cannot be updated. After the table name has been entered in the Dictionary, the DB2 Format name is derived from the Dictionary Table Name, and it will appear in this field the next time that this screen is displayed. The DB2 Format name is used in application program SQL code and in QMF, Query Tool, or Web based. List all copybooks used with this TABLE. Each TABLE used in a COBOL program must have a DCLGEN copybook, and this copybook name will have a 'D' as the last character. The copybooks are copied from the appropriate libraries during pre-compiler processing. Be sure that the first book listed is a 'D' suffix member, then list any other books associated with the table.

DB2 Format:

Books:

After updating the table information be sure to execute the JPLFILE procedure or the 'AC' function so that the create table SQL statements will be updated in the appropriate PDS. Executing JPLFILE or the 'AC' function on a table ID will also check to see if any of the indexes require updating, and if so, they will also be updated. Examples of these statements can be found in Appendix A. If the DBA analyst ID (Emp# field) for a DB2 table is changed on the File Definition system, refer to the execute authority scenario chart in the section: DB2 Authorizations. The chart will indicate whether or not other steps are required for proper execution of associated programs and procedures.

Iowa State University ADP Center DB2 Reference Handbook

12

File Definition System - Index Definition JPL File Definition System Screen field descriptions for INDEXes FILE ID:

Index Descr: Emp#:

System Num: Rows:

Key Length:

Page Size: Page Pctfree:

Freepage every XXX PAGES:

Table Name:

DB2 Format:

Unique Index:

All DB2 INDEX IDs will be defined in the File Definition System with a seven-character name as follows: xxByyyz where xx is the alphabetic twocharacter client prefix; B designates a production data base; yyy is a unique three digit number; z is a number starting with 1 for the first index (clustering index) and increasing up to 9 for each index associated with a table. The DB2 INDEX description The ATS employee number of the person responsible for this index. This field is ASKIP and the information is pulled from the associated TABLE record. The system number. This field is ASKIP and the information is pulled from the associated TABLE record. The number of rows expected in this INDEX. If the index is going to be unique, the number of rows should be equal to the number of rows in the table. If the index is going to be non-unique, the number of rows for this index may be less than the number of rows in the table. To determine the number of rows for a non-unique index, determine how many values will be represented by the columns that make up the key of the index. This value should be your number of rows for a non-unique index. If the row count on the tablespace is increased, the row count of each associated index is automatically increased in proportion as to whether the index is unique or non-unique. The total length of all columns that make up the INDEX. This field is ASKIP, and the information is derived from the index entry in the Data Dictionary. Default is 4096. The only other option is 32768 (32K), which is highly discouraged. Specify a percentage of each page (4K) to be left empty when loading or reorganizing the INDEX. This space is used for adding new rows and expanding the length of existing rows. If no new rows will be added, specify 00 PCTFREE; otherwise, specify enough freespace to allow entire rows to be added. If Key Length = 40, 10% freespace (4096 x .1 = 410) will allow adding 10 row per page without having to split the page The value for this field should usually be 000. DB2 also allows leaving every Nth page completely empty when loading or reorganizing an INDEX. Assuming the TABLE is at least 1 cylinder or greater: FREEPAGE EVERY 050 PAGES would provide 3 empty pages/cyl or 2% FREEPAGE EVERY 025 PAGES would provide 6 empty pages/cyl or 4% FREEPAGE EVERY 010 PAGES would provide 15 empty pages/cyl or 10%. The FREEPAGE and PCTFREE figures should be based upon the expected TABLE activity and growth This is the TABLE name as defined in the Data Dictionary that this index will be created for. This field is ASKIP, and the information is pulled from the associated TABLE record. This is the TABLE name as defined in DB2 that this index will be created for. This field is ASKIP, and the information is pulled from the associated TABLE record. Used only when defining a DB2 INDEX. This value 'Y' or 'N' indicates whether DB2 should require an index value (could be multiple columns) to be unique. DB2 will guarantee no two rows have the same index value.

Iowa State University ADP Center DB2 Reference Handbook

13

Cluster the index:

Index Cols:

Name:

ASC/DESC:

Used only when defining the first DB2 INDEX. Only 1 INDEX can be declared the CLUSTERing INDEX. Clustering means that the physical sequence of the data in the table matches the sequence of the CLUSTERing index. When loading a DB2 TABLE, the rows must be in the same sequence as the CLUSTERing INDEX. A value of 'Y' indicates CLUSTER, and a value of 'N' indicates non-CLUSTERed. These are the DB2 column names and the order (ascending or descending) of the INDEX. Up to ten columns may be included in each index. The index column information is entered in the Data Dictionary System and displayed on this screen once it is entered into the Data Dictionary. If the index information has not been entered into the Data Dictionary, a message is displayed: USE THE Data Dictionary System TO ADD THE INDEX COLUMN NAMES. This is the column name as defined in the Data Dictionary System. The fields are ASKIP, and the index columns are pulled from the Data Dictionary System. 'A' is ascending order, and 'D' is descending order. The fields are ASKIP, and the indicators are pulled from the Data Dictionary System.

After updating the index information be sure to execute the JPLFILE procedure or the 'AC' function so that the create index SQL statements will be updated in the appropriate PDS. Executing JPLFILE or the 'AC' function on the associated table ID will also check to see if any of its indexes require updating, and if so, they will also be updated. Examples of these statements can be found in Appendix A.

Iowa State University ADP Center DB2 Reference Handbook

14

File Definition System - Table Authorizations The File Definition System has four functions for DB2 table authorization: Browse, Update, Delete and Rebuild Grant Authorizations. Details of how to use the functions can be found in the documentation for the File Definition System. Anyone with the File Definition System on their ADIN menu may browse authorizations for a table. Modification to the authorizations is limited to the DB2 table owner, team leader of the DB2 table owner, or a Database Administrator. Since the DB2 table owner has DBADM (Database Administrator) authority, which includes all capabilities for a table, there is no need to enter his/her client ID with these new functions, for their tables. The following is an overview of the functions and their purpose: 'BA' - Browse Authorization Records within a Table The authorization IDs that have already been entered may be browsed ten IDs at a time for a desired DB2 table ID. The browse shows the user ID and the capabilities that were entered. A function field in the lefthand column is available to enter the update or delete functions for the desired user ID. 'UA' - Update Table Authorization Records This function will be used to add/update user ID's that need SELECT, UPDATE, INSERT, and DELETE capabilities to a DB2 table. The ID's will be of the format adopted for the ADIN sign-on procedure (e.g. A D P033) with the spaces included. The ID will be converted to an ID that contains no spaces for use within the DB2 system. Both ID's are displayed and stored in the file. A 'T' or a 'P' may be placed next to each capability that the client may need to perform against the table. If a 'T' is placed next to the capability, the authority will be valid for TEST ONLY. If a 'P' is placed next to the capability, the authority will be valid for TEST and PRODUCTION DB2 tables. 'DA' - Delete Table Authorization Records This function allows for deleting a user ID and all capabilities associated with the user ID. 'RG' - Rebuild Grant Authorizations for a Table This function submits a batch job that actually GRANTS or REVOKES client capabilities for a given DB2 table. The output from the job can be viewed on SYSD as three jobs, one for test and one for production. The jobs will be named XXXGRNTT, and XXXGRNTP where XXX is the initials of the analyst who is the DB2 table owner. NOTE: The adds, updates, and deletes are applied to the requested DB2 table when the 'RG' function is executed during working hours. If the 'RG' function is not executed then the changes will be applied during a nightly batch procedure. Also, authorizations are only needed for DB2 tables, not for DB2 indexes. The following are several examples of the purpose of table authorizations: 1.

An analyst who is not the owner of the DB2 table (DBADM), needs to write a program that selects data from that table.

2.

A client department wants to perform QMF, Query Tool, or Web based selects against the DB2 tables that contains their data. The clients' ADIN ID would be used.

Examples of the GRANT statements can be found in Appendix B.

Iowa State University ADP Center DB2 Reference Handbook

15

File Definition System - Sequential Backup File Procedures will require a sequential backup file to perform drop/create/load functions. Determine whether it should be a tape file or a disk generation file. •

•

•

This permanent file ID will have an LRECL equal to the DB2 table structure, and one row at a time will be written to the file by an application program or a DB2 Unload utility. The number of records should equal the number of rows in the table. The data in the file is in a normal sequential format and can be used for any application purpose. Look at the comments in the middle of the DCLGEN copybook (D suffix book) for this table, to determine the exact length of this file. See the section entitled Data Definition and Relationships Storage in this manual for the location of this DCLGEN. Use the length defined by 'EXTERNAL LENGTH = ' for the length of this sequential file. This sequential backup file should be used to backup a DB2 table before a large number of updates are done to ensure that if there was a program error, the table can be reloaded as it was before the program that contained the error was executed. This file is also used when an UNLOAD or DROP/CREATE/LOAD procedures are used. Any application program can use the data contained in the file.

Specify at least 3-6 rotations for the sequential backup file. Look in the JPL - Job Procedure Language section for a list of sample procedures that use these files. The sample procedures can be copied to your own procedure numbers and then modified to use the appropriate table names. The following is a suggested method for naming the sequential backup file: If the table was named Sequential backup copy could be

DBB5010 DB95010 or DB05010

Iowa State University ADP Center DB2 Reference Handbook

16

Data Dictionary The Data Dictionary System is where the data element names that will make up the structure of the DB2 tables and indexes are entered. The system number and the file IDs are required when the table names and index definitions are entered. Be sure to enter all element names before using the 'PT' function of the Dictionary System, which will be discussed below. Data elements that are defined as numeric should have an 'S' prefix in picture clause to make it a signed field. This will make the COBOL definition compatible with the DB2 column definition. After all of the column names for the DB2 books have been entered, use the 'PT' function in the Data Dictionary to: • Create the DCLGEN (Member name = booknamD e.g. DDELMNTD) • Create the SQL Data Definition Language (DDL) statements to define the table and each column in the TABLE , in the DB01.PROD.DB2.TABLEDEF partitioned dataset. (Member name = fileid e.g. DDT0710D) • Create the DB2 Load Control Records for the TABLE, in the DB01.PROD.DB2.TABLEDEF partitioned dataset. (Member name = fileid e.g. DDT0710L) After the data names have been approved for production, use the 'PP' function in the Data Dictionary to: • Create the DCLGEN (Member name = booknamD e.g. DDELMNTD) • Create the SQL Data Definition Language (DDL) statements to define the table and each column in the TABLE , in the DB01.PROD.DB2.TABLEDEF partitioned dataset. (Member name = fileid e.g. DDB0710D) • Create the DB2 Load Control Records for the TABLE, in the DB01.PROD.DB2.TABLEDEF partitioned dataset. (Member name = fileid e.g. DDB0710L) Refer to the Data Dictionary Manual for further information regarding general Data Dictionary information. This is a sample of the SQL Data Definition Language (DDL) member. MEMBER=DDT0710D which is stored in DB01.PROD.DB2.TABLEDEF CREATE TABLE DD.ELEMENT 88052741 (REF_NUM INTEGER NOT NULL WITH DEFAULT,88052741 OWNER_NAME CHAR(20) NOT NULL WITH DEFAULT,88052741 FLD_FRMT CHAR(1) NOT NULL WITH DEFAULT,88052741 FLD_LEN SMALLINT NOT NULL WITH DEFAULT,88052741 DECIMAL_PLACE SMALLINT NOT NULL WITH DEFAULT,88052741 DEPEND_REF_NUM INTEGER NOT NULL WITH DEFAULT,88052741 DFALT_HDNG VARCHAR(30) NOT NULL WITH DEFAULT,88052741 DFALT_MASK VARCHAR(30) NOT NULL WITH DEFAULT,88052741 OCCURS_CLAUSE VARCHAR(30) NOT NULL WITH DEFAULT)88052741 IN DBDD01T.DDT0710T; COMMENT ON TABLE DD.ELEMENT IS 'The DB2 table containing the data dictionary element formats. '; COMMENT ON DD.ELEMENT (REF_NUM IS 'A nine-digit unique number assigned to each data name. It is the primar y key used to reference the dictionary, map, element, documentation, an d alias tables. ', OWNER_NAME IS 'The name of the department which is responsible for maintaining a data item. ', FLD_FRMT IS 'This field defines the format of the data item. alphanumeric: pic x( n) packed: pic 9(n) comp-3 binary: pic 9(n) comp numeric: pic 9(n) ', FLD_LEN IS 'Specifies the number of positions, including the decimal point, which a re required to display a data item. ', DECIMAL_PLACE IS 'Specifies the number of digits to the right of the decimal point of a d

Iowa State University ADP Center DB2 Reference Handbook

17

ata item. ', DEPEND_REF_NUM IS 'The reference number of the data name used as a segment counter for var iable length data items. ', DFALT_HDNG IS 'Specifies the default heading of a data item in Easytrieve Plus copyboo ks. ', DFALT_MASK IS 'Specifies the default mask of a data item in Easytrieve Plus copybooks. ', OCCURS_CLAUSE IS 'Specifies the number of times a data item occurs. Valid formats: 9999 9999 to 9999 ');

This is a sample DCLGEN member. EXEC SQL DECLARE DD.ELEMENT TABLE (REF_NUM INTEGER NOT NULL, OWNER_NAME CHAR(20) NOT NULL, FLD_FRMT CHAR(1) NOT NULL, FLD_LEN SMALLINT NOT NULL, DECIMAL_PLACE SMALLINT NOT NULL, DEPEND_REF_NUM INTEGER NOT NULL, DFALT_HDNG VARCHAR(30) NOT NULL, DFALT_MASK VARCHAR(30) NOT NULL, OCCURS_CLAUSE VARCHAR(30) NOT NULL) END-EXEC. *TABLE LAYOUT: System NAME=DATA DICTIONARY SYSTEM * FILE NAME=DATA DICTIONARY ELEMENT FORMATS * SKIP PREFIX=YES * TABLE NAME=DD.ELEMENT * EXTERNAL LENGTH = 129 * INTERNAL DB2 TABLE LENGTH = 100 * CREATE DATE=05/27/88 * ADP CENTER EMPLOYEE NUM= 41 * DATABASE NAME=DBDD01P * TABLESPACE NAME=DDB0710T * 01 DDELD-DD-ELEMENT-TBL. 12 DDELD-DD-REF-NUM PIC S9(9) COMP. 12 DDELD-DD-OWNER-NAME PIC X(20). 12 DDELD-DD-FLD-FRMT PIC X(1). 12 DDELD-DD-FLD-LEN PIC S9(4) COMP. 12 DDELD-DD-DECIMAL-PLACE PIC S9(4) COMP. 12 DDELD-DD-DEPEND-REF-NUM PIC S9(9) COMP. 12 DDELV-DD-DFALT-HDNG. 49 DDELL-DD-DFALT-HDNG PIC S9(4) COMP. 49 DDELD-DD-DFALT-HDNG PIC X(30). 12 DDELV-DD-DFALT-MASK. 49 DDELL-DD-DFALT-MASK PIC S9(4) COMP. 49 DDELD-DD-DFALT-MASK PIC X(30). 12 DDELV-DD-OCCURS-CLAUSE. 49 DDELL-DD-OCCURS-CLAUSE PIC S9(4) COMP. 49 DDELD-DD-OCCURS-CLAUSE PIC X(30).

DDELMNTD DDELMNTD DDELMNTD DDELMNTD DDELMNTD DDELMNTD DDELMNTD DDELMNTD DDELMNTD DDELMNTD DDELMNTD 1 1 5 25 26 28 30 34 34 36 66 66 68 98 98 100

An example of the CREATE TABLE statements can be found in Appendix C.

Iowa State University ADP Center DB2 Reference Handbook

18

Data Dictionary - Index Information Data Dictionary System Index Screen field descriptions: NAME

TABLE NAME EMPLOYEE NUMBER COLUMN NAMES

ASC/DSC

This is the DB2 INDEX IDs that was previously defined in the File Definition System with the seven-character name as follows: xxByyyz where xx is the alphabetic two-character client prefix; B designates a production data base; yyy is a unique three digit number; z is a number starting with 1 for the first index (clustering index) and increasing up to 9 for each index associated with a table. This is the name of the DB2 table that this index is associated with. This field is ASKIP. The ATS employee responsible for this table/index. These are the Data Dictionary data element names that will make up the definition and order of the index. Up to ten predefined data elements may be included in each index. This field will indicate the order of data for each column in the index. 'A' is ascending order and 'D' is descending order.

The Referential Integrity information located on the right side of the screen has been added to allow for the definition of Referential Integrity (data validation across tables) when DB2 tables/indexes are constructed. See a Database Administrator if you need to use this feature.

Iowa State University ADP Center DB2 Reference Handbook

19

DB2 SYSADM - For TEST System After the Database Administrator(s) have approved the initial design review, an Administrator will create a new database in DB2 so that the analyst can create TABLESPACES, TABLES, and INDEXES which are defined in the Data Dictionary and File Definition Systems. The Administrator will GRANT the analyst in charge Database Administrator (DBADM) authority. When creating a new data base or adding a new table to an existing data base, the table information and the authorization information may be added to the File Definition System. See a Database Administrator before attempting to use JPLFILE or the 'RG' function. This is only when the DB2 table is new and is going to be DROP/CREATEd for the first time. If either JPLFILE or the 'RG' function is tried, a message will be returned: "See a DB2 Administrator". The Database Administrator will establish the table, and the owner of the DB2 table will then be able to perform all DBADM capabilities on that table. After the Database Administrator has established the table for the first time, submit your 'JPLFILE' member to update the DB2 file definitions in a PDS. These members are used in the batch JCL jobs for defining DB2 tables and indexes. The following are examples of the naming conventions for these members: • • • • • • • • • •

DBB0010T DBB0010D DBB0010L DBB0011I DBB0012I DBT0010T DBT0010D DBB0010L DBT0011I DBT0012I

Production DB2 CREATE TABLESPACE Definition statements Production DB2 CREATE TABLE Definition statements Production DB2 Load Control Statements Production DB2 CREATE INDEX Definition statements Production DB2 CREATE INDEX Definition statements TEST DB2 CREATE TABLESPACE Definition statements TEST DB2 CREATE TABLE Definition statements Production DB2 Load Control Statements TEST DB2 CREATE INDEX Definition statements TEST DB2 CREATE INDEX Definition statements

When a table is going into production, give the table numbers to the Database Administrators so that a daily job can be set up to do a full image copy or a partial image copy of the tables for recovery purposes.

Iowa State University ADP Center DB2 Reference Handbook

20

Source Control System Enter the Program Titles/Plans for the programs to be used in the system. Enter the program record in the Source Control system selecting the appropriate options for the new program. Also enter the following information for EACH program:

Batch - DP function •

• •

For the ‘Plan Execute Authority Ids’, enter IDs of other analysts who will be using this program in their procedure. The authority will be granted to the analyst(s) at bind time. If this program calls another DB2 program, the called program ID will need to be listed in the ‘Package Members to bind into this plan’ section. If this is a called program, enter ID’s of other analysts who will be calling this program in the ‘Package Execute Authority Ids’ section. This is usually handled by entering ‘PUBLIC’.

DB2 Reminder Before changing the owner of a DB2 program on the Source Control, be sure that the new owner has all of the DB2 table privileges granted that are required for the SQL contained in the program. If this is not verified, the rebind jobs that are automatically initiated could fail.

Iowa State University ADP Center DB2 Reference Handbook

21

DB2 Authorities A DB2 database can consist of one or more tables. DBA (DataBase Administrator) authority can be granted at the database level to one or more analysts. With this authority, the analyst has the ability to perform all possible operations against every table in that specific database. These operations include drop, create, utility execution, grant, revoke, select, insert, update, and delete. The File Definition System shows the IDs of the analysts who have DBA authority in Production DB2. When a DB2 table is displayed using the DS function, the DB2 DBA IDs are shown on the lower left side of the screen. The analyst for each ID has the capability to grant and revoke table authorizations (select, insert, update, and delete) using the UA function in the File Definition system. An edit check in the UA function prevents DBAs from granting authority to themselves. This authority is not needed because it is implied with the DBA authority. Also, the DB2 DBA IDs are shown on the BA screen in the File Definition system. DB2 plan/package authorities are completely independent of DBA authority. If analyst-1 owns a program and analyst-2 wishes to include that program in a JPL procedure, analyst-1 must grant execute authority to analyst-2, using the DP screen in the Source Control system.

DB2 Authorizations DB2 authorizations are granted to individual USER ID(s). In limited cases the DB2 authorization is granted to the special USER ID named 'PUBLIC' which means any USER ID can perform the function.

Action

Required Authorization

Drop/Create a DB2 table Execute most DB2 Utilities Select, Insert, Update,or Delete rows

DBA DBA DBA or Specific table access GRANT Program Owner or Specific EXECUTE PLAN GRANT Program Owner or Specific EXECUTE PACKAGE GRANT

Execute a DB2 Program Use a called DB2 Program

DBA authorization is granted to a USER ID by a DB2 System Administrator. Specific table access GRANT(s) are maintained in the File System. For changes to be made effective execute the 'RG' function in the File System or wait until the next day. Specific EXECUTE GRANT(s) are maintained in the Source System. For changes to be made effective the appropriate BIND must be performed.

DB2 Table Access: Access Via

Required DB2 Authorization

QMF, Query Tool, or Web based SPUFI DB2 COBOL Program Owner DB2 COBOL Program Modifier DB2 EZT+ Program Owner DB2 EZT+ Program Modifier

DBA or specific table access GRANT(s) DBA or specific table access GRANT(s) DBA or specific table access GRANT(s) None DBA or specific table access GRANT(s) None

Iowa State University ADP Center DB2 Reference Handbook

22

Procedures: The owner of a procedure must either OWN or have EXECUTE authority for ALL DB2 programs in the procedure.

Execution

Required DB2 Authorization

XPEDITOR DB2 COBOL Program DB2 EZT+ Program DB2 Utility - LOAD DB2 Utility - REORG DB2 Utility - RUNSTATS DB2 Utility - IMAGE COPY DB2 Utility - QUIESCE DSNTUSQL DB2UNLD

Program Owner or specific EXECUTE grant Program Owner or specific EXECUTE grant Program Owner or specific EXECUTE grant DBA DBA DBA PUBLIC PUBLIC DBA or table SELECT authority DBA or table SELECT authority

DB2 Execute Authorities DB2 Plan and Package execute authority grants and revokes are processed on a nightly basis. If userids are entered or removed from the Execute Authority IDS lists on the DP Panel in the Source Control System, a nightly job is executed that will detect the changes and ensure that the appropriate grant and/or revoke is issued in test and production DB2 for each authority change that is made. If you want the changes in authority to become effective immediately, you will need to execute the XT and the XP functions in the Source Control System for each program, to issue the grants and/or revokes to test and production DB2.

DB2 Authorizations for ODBC Applications DB2 tables are visible in the table lists of ODBC applications immediately after executing the ‘RG’ function in the File Definition System. This means that a client using MS-Query or MS-Access will be able to access the table the same day that the grants are issued to DB2. If the 'RG' function is not executed, the grants are then issued to DB2 overnight and the table(s) will be visible in table lists of ODBC applications the next day.

Iowa State University ADP Center DB2 Reference Handbook

23

DB2 Authorizations - Changing The following chart indicates the authorizations needed for DB2 table(s), program(s)/QMF, Query Tool, or Web based that access the DB2 table(s), and JPL procedures/Xpediter that execute the program(s). There are five scenarios depending on which analysts own the particular objects. If the owner of a program/procedure changes, the scenario chart needs to be reviewed to determine if any actions need to be taken to maintain valid execution of the programs and procedures.

Object: =========== DB2 Table Program Procedure / Xpeditor Action Required:

Scenario 1 Owner =========== Analyst-1 Analyst-1 Analyst-1

Scenario 2 owner =========== Analyst-1 Analyst-1 Analyst-2

Scenario 3 owner =========== Analyst-1 Analyst-2 Analyst-2

Scenario 4 owner =========== Analyst-1 Analyst-2 Analyst-3

Scenario 5 owner =========== Analyst-1 Analyst-2 Analyst-1

Note #1

Note #2

Note #3

Note #4

Note #5

Action Required Notes: 1.

No action required.

2.

Analyst #1 must update the Execute Authority Ids ('DP' function of the Source Control system) with Analyst #2's ID (without spaces). Analyst #1 must then issue the 'XT' and 'XP' functions of the Source Control system to actually grant the execute authority in DB2.

3.

Analyst #1 must update the Table Authorizations ('UA' function of the File Definition system) with Analyst #2's ID and determine what authority to provide: SELECT, INSERT, UPDATE or DELETE. Analyst #1 can then let the system grant the defined authority that night or execute the 'RG' function of the File Definition system to grant the defined authority immediately.

4.

Analyst #1 must update the Table Authorizations ('UA' function of the File Definition system) with Analyst #2's ID and determine what authority to provide: SELECT, INSERT, UPDATE or DELETE. Analyst #1 can then let the system grant the defined authority that night or execute the 'RG' function of the File Definition system to grant the defined authority immediately. Analyst #2 must update the Execute Authority Ids ('DP' function of the Source Control system) with Analyst #3's ID (without spaces). Analyst #2 must then issue the 'XT' and 'XP' functions of the Source Control system to actually grant the execute authority in DB2.

5.

Analyst #2 must update the Execute Authority Ids ('DP' function of the Source Control system) with Analyst #1's ID (without spaces). Analyst #2 must then issue the 'XT' and 'XP' functions of the Source Control system to actually grant the execute authority in DB2.

Iowa State University ADP Center DB2 Reference Handbook

24

JPL - Job Procedure Language For the JPL System, procedures are basically the same as for VSAM except that DB2 tables are listed in the procedure but no actual JCL is generated since the tables are accessed through the DB2 Plan. It is important to code all of the DB2 tables used in the program with their appropriate I, I-O, or O access type in the JPL procedure. The JCL generator will use this information for job sequencing purposes and also for generating QUIESCE JCL steps in front of any JCL step that performs I/O against DB2 tables. This is very important so that the tables can be recovered to the QUIESCE point should the program execute in error. Define job procedures that use DB2 tables as CRITICAL, using the same criteria as for VSAM files. If any application performs I/O or executes DB2 utilities against DB2 tables in a procedure, then the procedure must be defined as CRITICAL. The following procedures should be created for each table: 1.

Unload procedure using the DB2UNLD utility. (sample: 00-37-66) • • • • •

2.

The DB2 utility program DB2UNLD has been created to unload a DB2 table and write the data to a sequential file The DB2UNLD utility program should be used instead of an application program DB2UNLD will use the LRECL and BLKSIZE parameters in the JCL: 1. If they are correct 2. If LRECL is larger than required and BLKSIZE is a multiple of LRECL DB2UNLD will override the LRECL and BLKSIZE parameters in the JCL if they are incorrect without abending. Messages are printed in the SYSOUT dataset if either JCL parameter are overridden. Use the 'EXTERNAL LENGTH =' listed in the documentation section of the DCLGEN book as the LRECL for the sequential file into which the DB2 table data will be unloaded. This is critical for DB2 tables that contain DATE, TIME and TIMESTAMP columns.

Drop/Create/Load procedure. (sample: 00-37-62) This procedure will only be used the first time the table is created or when physical table changes need to be done. The programmer/analyst is responsible to insure that the data being loaded satisfies the uniqueness restrictions of all indexes defined on the table. If the table structure is going to be changed, three JPL procedures are required to prevent the loss of data during the conversion: 1. Unload procedure 2. Conversion procedure. 3. Drop/Create/Load procedure

Examples of the above procedures and other types, are located on the JPL system under the following procedure numbers. They can be copied to specific procedure numbers using the 'CP' function of the JPL system. Be sure to change all of the file IDs in the procedures after they are copied. Also change the owner ID on the newly created procedure. 00-37-62 00-37-65 00-37-66 00-37-68 00-37-69

DROP/CREATE/LOAD a DB2 table LOAD a DB2 table UNLOAD using DB2UNLD utility CREATE/RUNSTATS a new DB2 index DROP/CREATE/RUNSTATS an existing DB2 index

Iowa State University ADP Center DB2 Reference Handbook

25

Be sure that if an unload is performed and the unloaded file is to be used as input into the load utility, to unload the data in the clustering index sequence. And also, if the DB2 table is being loaded from some other source, be sure the data in the input file is sorted into the clustering index sequence. Each month, DB2 tables and indexes are evaluated and reorganized if necessary by the DBA Group. If the analyst ID for a DB2 JPL procedure is changed on the JPL system, refer to the execute authority scenario chart in the section: DB2 Authorizations. The chart will indicate whether or not other steps are required for proper execution of the procedure.

Iowa State University ADP Center DB2 Reference Handbook

26

DB2 Utilities DSNLOAD

This utility takes an input file of data and loads the data into the specified DB2 table. Also, key information for each associated index is extracted and sorted. Each index is then loaded with the keys and appropriate record identifiers to the rows of data in the table. Uniqueness of all index key values is checked during the load. Use the DSNLOAD utility without a DROP/CREATE step to reset or reload tables. Use a dummy input to delete all the current rows. The ONLY time a DROP/CREATE is required is if the table structure changes. The DROP/CREATE deletes all recovery information about the table and invalidates any DB2 plans that use the table. These plans must be rebound before their next execution. This rebind process is automatic overnight, but is a lot of extra work that is not necessary.

DSNREORG

This utility is used to reorganize tables and/or indexes after some amount of update/insert/delete activity. This makes the table and/or index more efficient since freespace is reallocated.

DSNRUNST

This utility is executed against a table and its associated indexes to collect statistics about them and store the information in the DB2 catalog. This information is used by the DB2 Optimizer to determine the most efficient access path to the data.

DSNTIAD

This utility allows certain SQL statements to be executed dynamically in a batch environment.

DB2UNLD

Unloads a DB2 table to a sequential file in clustering index order.

DSNTUSQL

This utility performs an unload of a DB2 table to a sequential file and allows the use of a WHERE clause. SQL statement, including ORDER BY, must be coded in the procedure through the use of control cards.

DSNUTILB

This utility executes certain DB2 commands to be executed dynamically in a batch environment.

JCL Generation and Storage Locations Input: JOB PROCEDURE System File Definition System Source Control System DB01.PROD.DB2.TABLEDEF DB01.PROD.DB2.GRANTLIB Output: TEST JCL (ADPxxx.DVLP.JCL(TxxxxxxA) PRODUCTION JCL (JPL.PROD.JCL(P0xxxxxx)

Iowa State University ADP Center DB2 Reference Handbook

27

Program Development Accessing DB2 Tables When multiple programs are to access the same set of DB2 tables access them (if possible) in the same sequence in all the programs. This will help prevent DB2 deadlocks and/or timeouts.

SQL Statement Format Standards When coding SQL statements, the syntax will be as follows: • Code each SQL Clause on a separate line, left justified • Code each DB2 column on a separate line • Code each working storage item on a separate line • Code each DB2 table name used in SQL statement on a separate line • Code each conditional statement of the WHERE clause on a separate line with any connectives at the beginning of the condition • Be sure to use parenthesis to group appropriate AND/OR conditions in the WHERE clause • Code each ORDER BY DB2 column on a separate line with the ASC or DESC indicator • Align DB2 column names and working storage host variables to make an easily readable SQL statement The Sample COBOL Code listed in the back of this handbook shows examples of the standard syntax that will be followed for the DECLARE CURSOR, FETCH, SELECT, INSERT, UPDATE and DELETE SQL statements in application programs:

I/O Limits - CICS The following are the total number of SQL EXEC executions and VSAM I/O executions that are allowed per CICS transaction: 30 80

Any CICS DB2 application program coded with SELECT, UPDATE, INSERT and/or DELETE SQL statements (usually display/update programs). Any CICS DB2 application program coded with DECLARE CURSOR/FETCH SQL statements (usually browse programs). No updates, inserts or deletes.

A working storage field WSWA-SQL-EXEC-CTR is generated into each new CICS/DB2 program and is automatically incremented each time an SQL statement is executed. Be sure and code a check in the program to verify that the value in this field does not exceed the above limits.

SQL Programming Standards, Guidelines and Restrictions 1. General •

• • • • • • • •

If a Tablespace scan is indicated on the Explain report, verify that an index is not available to access the data. If an index does exist that the SQL statement could use, modify the syntax of the SQL statement to try and get the DB2 Optimizer to choose the index. A tablespace scan is very efficient if all of the rows are retrieved. Avoid coding complex SQL statements. They are difficult to debug and maintain. Host variables must be coded or included in the Data Division before they can be used in an SQL statement. Also they should use the same data type and length. Do not code a 'SELECT *' statement Avoid DISTINCT because this usually invokes a sort. Use the SQL command BETWEEN instead of (col_1=> :hvar_1 AND col_1 <= :hvar_2). Exception in the following case: WHERE :hvar_1 >= column_1 AND :hvar_1 <= column_2. Where feasible use 'IN' or 'BETWEEN' instead of 'LIKE' Code the 'FOR FETCH ONLY' clause for any 'SELECT' statement that is only used for data retrieval

Iowa State University ADP Center DB2 Reference Handbook

28

• • • • • • • • •

Specify only columns to be updated in the 'FOR UPDATE OF' clause. SELECT and UPDATE only the required columns in a given program, not all columns unless the program requires it. The program generators generate all columns for SQL statements. Remove the columns that are not used in the application program. Do not include the columns in a UPDATE SQL statement that are used in the WHERE clause for that statement. Avoid arithmetic expressions in the WHERE clause on the highest order column of the index being used to access the data. The SQLCODE must be checked after each SQL statement execution. In a sub query that uses negation logic use 'NOT EXISTS' instead of 'NOT IN' Use a constant for existence checking. When using 'EXISTS' specify a constant as the only entry in the 'SELECT' list of the sub query. Never code a 'JOIN' statement without a predicate. Use the AS clause to name derived columns.

2. CICS •

The following SQL commands and/or functions will not be allowed in CICS application programs: SELECT * DROP

•

WHENEVER LOCK TABLE

GROUP BY AVG

SUM HAVING

MAX

The following SQL commands and/or functions can be used in CICS application programs ONLY IF the statement is evaluated with the QMF EXPLAIN function to verify that indexes are used to satisfy the data request. Also a member of Database Administration team must be contacted to check the SMF performance data on the statement. An SQL statement that includes a JOIN should be restricted to maximum of two tables. If the statement does not meet the performance standards be prepared to break it into separate SQL statements. Left Outer Join

• •

ALTER

The following SQL commands and/or functions can be used in CICS application programs ONLY IF the amount of data that the function is operating against is a small subset (25 rows or less). This can be determined by the column conditions used in the WHERE clause that is used for the SQL statement. The SQL statement must also use an index to satisfy the request. COUNT(*) MIN

•

CREATE GRANT

Subselect

LIKE

UNION

Avoid Index Scans, Table Scans, and Sorts in CICS application programs. If an SQLCODE return code indicates that an UPDATE, INSERT or DELETE statement did NOT execute normally, the CICS transaction should issue the following statement to rollback all DB2 changes made by previous SQL executions in the CICS transaction. The CICS COBOL generator currently generates this rollback code for any CICS/DB2 program that updates a DB2 table. EXEC CICS SYNCPOINT ROLLBACK END-EXEC.

3. Batch •

The following SQL commands and/or functions will not be allowed in batch programs: DROP WHENEVER

• •

CREATE ALTER

GRANT SELECT *

REVOKE

JOIN statements are permitted in batch, but never join more than three (3) DB2 tables in one SQL statement. All JOIN statements must include a WHERE or HAVING clause.

Iowa State University ADP Center DB2 Reference Handbook

29

• • • •

•

•

• •

•

Code the WHERE clause of a cursor so that rows are eliminated internally by DB2, rather than returning all of the rows to the program and eliminating them by program logic. Eliminating the rows BEFORE they get to the program saves considerable processing costs. When multiple rows are inserted into a table, be sure that they are sorted into the clustering sequence of the table before the inserts are performed. When joining tables and specifying a search criteria, specify the join condition redundantly. In the case where A = B and B = C, also code A = C in order for DB2 to recognize this fact. In "long running" batch programs that perform inserts, updates and deletes, a CHECKPOINT/RESTART routine should be incorporated into the program to execute a COMMIT SQL statement after a predetermined number of updates (approx. 500-1000). This value should be input into the program through a control card or parm. For each insert, update, or delete, a counter should be incremented. When the predetermined value is reached, execute an EXEC SQL COMMIT END-EXEC. command to release all locks to DB2 tables. Also, the first key of the input records used for the update, after the COMMIT is issued, should be displayed on the console or written to a file. This is because if the program were to abend, all DB2 changes are backed out to the last COMMIT point. This requires that you know the first key to restart your batch job from. This key value should be input back into the program and have program logic to reposition the program to where it left off before the abend. See a Database Administrator if you need more information. The production JCL generator automatically generates a DSNQUIES step in front of any application step that performs I/O against DB2 tables. This step will set a point of consistency for a table or tables, for recovery purposes, before the updates are done. The table(s) can then be recovered to the DSNQUIES point which will restore the data to the point in time just before the execution of the program that updated the table(s) in error. In a batch program that is going to open and close a cursor multiple times to retrieve rows randomly, it is a good idea to determine the number times the cursor will be opened and closed. Caution must be used in that a large number of opens/closes can have a negative impact on the performance of the program. If the number of opens/closes is going to be large, it may be more efficient to open the cursor once, fetching the selected rows sequentially and matching to the other files used in the program. A Join of the tables can eliminate multiple opens and closes. If a single DB2 table is going to be updated, it is best for performance to declare a cursor that has an ORDER BY in the clustering index sequence. The sequence of the rows fetched with the cursor and the update records read from the input file must be identical. As the rows are fetched, make the appropriate changes to the columns and perform an UPDATE SQL statement. This is much more efficient than performing random updates. If multiple DB2 tables are going to be updated using a single update input file, special care should be taken to ensure consistency and performance: • If all tables have the same clustering sequence, declare the cursors with ORDER BYs in the clustering index orders; fetch the rows, and perform the updates. • If the clustering sequence between the tables is different, the following technique will increase performance at the expense of consistency risks. This technique will also increase program complexity: • The program should update all tables that have the same clustering sequence with the update input file in that sequence also. Then write the update input file to an internal sort to sort the records into the clustering sequence of the next table to be updated. After sorting the rows, open that table's cursor and perform the updates. Continue sorting the rows until all tables are updated in their respective clustering sequences. This will increase performance and all updates are done in the same program. If an abend were to occur, the updates from all of the tables would be backed out to the beginning of the program or the last COMMIT point.

COBOL Generator Information Use the COBOL Generators to generate new Batch and CICS programs. Both are set-up to handle DB2 tables and will generate the proper SQL code in batch depending on random, sequential, dynamic options, and in CICS for display, update, browse, add and delete functions. The COBOL generators are set up to handle the various types of read, write, update, insert, delete, etc. operations that are necessary in DB2. In CICS the I-O operation will correspond to the type of program that is being created. Iowa State University ADP Center DB2 Reference Handbook

30

(e.g. Browse). In batch it is necessary to put the table name in the DOC information with the type of access that is desired. The options are the same as for VSAM, S=Seq, R=Random, D=Dynamic. The appropriate SQL statements will be generated into the program using the columns defined for the table which are stored in the Data Dictionary.

Guidelines on the coding of SQL statements: •

• • • •

In CICS browse programs, use an ORDER BY on the DECLARE CURSOR to ensure that the data is displayed in proper sequence, also be sure that an index exists for the given order so that a sort is avoided. When coding the order by clause, be sure to include all of the columns in the specific order of the index that DB2 is expected to use. ATS standards call for the clause 'OPTIMIZE FOR 1 ROW' to be coded in each declare cursor of all CICS application programs. This clause helps the DB2 optimizer select an efficient path to maintain sub-second response time. If a SQL SELECT statement contains an ORDER BY clause, the columns listed in the ORDER BY clause must also be SELECTED. Be sure and delete any columns from DECLARE CURSOR, FETCH, SELECT and UPDATE SQL statements that will not be used in the application program. DB2 only has to deal with the data that you want to manipulate, not an entire row at a time. Any DB2 columns that are not coded in the SQL INSERT statement, will be initialized to the columns default value if the columns are defined as NOT NULL WITH DEFAULT. The column will be initialized as follows according to the column definition: CHAR VARCHAR DECIMAL INTEGER SMALLINT DATE TIME TIMESTAMP

• • • • • • • • •

• •

Spaces A String of Length 0 Zeros Zeros Zeros (current date) (current time) (current timestamp)

Avoid the use of 'OR' and '^=' in the WHERE clause if possible, because of the potential for DB2 doing a full table scan. There is an exception to the 'OR' and that is with the 1 CURSOR method for CICS Browse programs. A sample can be found in the programming samples in back of this manual. Host variables used in the WHERE clause must be of the same data type and length as the columns they are being compared against. When declaring host variables in the Working Storage section, code any COMP-3 definitions on each host variable, not at the group level. There are some restrictions when using FILLER for host variables involved in a redefines. See a Database Administrator for details. Host variables used in the WHERE clause should be defined as the same length as the column it is being compared against. The reason for this is that if the lengths are different, the DB2 optimizer may pick a much less efficient index at bind time to retrieve the data. Set the value of the host variables in an SQL cursor before the OPEN CURSOR SQL statement is executed. Use the clustered indexes in the JOIN function. Be sure and issue a CLOSE CURSOR statement for all open cursors in batch and CICS programs. Even if the program is to be abended intentionally, issue the CLOSE CURSOR statement. If in the application program, the same table is to be accessed simultaneously by separate SQL statements, be sure and code a separate SQLCODE working storage area for each condition checking routine. This will ensure that the SQL return code being checked is really the value for the appropriate table access. The COBOL generators currently generate only one SQLCODE working storage area for each DB2 table accessed in a program. Us the 'AS' clause to name derived columns If a SQL statement contains a NESTED TABLE expression a DCLGEN for the NESTED TABLE must be manually coded.

Iowa State University ADP Center DB2 Reference Handbook

31

Several restrictions apply to variables in DB2 WHERE clauses: 1.

FD variables cannot be used.

2.

Group level variables can be used, BUT only at the lowest two levels, and also only in the portion of an SQL statement that deals with column assignments, NOT in the WHERE clause.

3.

Redefined names cannot be used.

4.

In a batch COBOL program, a PARM variable cannot be used as a host variable in a DECLARE CURSOR SQL statement because the variable must be defined before using it in an SQL statement. The solution is to move the parm value to a working storage variable which in turn is used in the WHERE clause of the DECLARE CURSOR SQL statement.

If a data element from a non-DB2 DCLGEN, VSAM or sequential copybook is used in an SQL statement, the record description needs to be INCLUDED instead of COPIED in at precompile time, otherwise the DB2 precompiler will flag the variable as an unrecognizable host variable. This is because the precompiler does not recognize the COPY statement. An example is as follows: EXEC SQL INCLUDE ALUMSTRB END-EXEC. Be sure and put the period after the END-EXEC or unexpected results will occur. Standards call for the EXEC statement to begin in Col. 12 of the program source record. Variables that are to be used in an SQL statement should be defined in the Data Division of the program. When the variable name is coded within the SQL statement, it must be preceded by a ':' so that the DB2 precompiler will recognize it as a Host Variable.

DB2 Null Columns DB2 supports the concept of columns defined as NULL. DB2 uses a special value indicator, called a null value, to stand for an unknown or missing value. A null value is a value and not a zero value, a blank, or an empty string. It is a special value interpreted by DB2 to mean that no data has been supplied. A null is not equal to another null since a null is unknown. When designing a new table, some date columns are good candidates for being defined as null. An example would be termination date in an employee table. The column would be null for most of the rows in the table, since most of the employees are active employees. The following are instructions and guidelines on how to use null values in a table: 1.

Date type columns are the only columns that should be defined as null.

2.

If a date column is used in an index, it should not be defined as null.

3.

A COBOL and/or Easytrieve book is required to process a table in its load/unloaded dataset structure.

DB2 NULL Columns - Load/Unload Dataset Structure DB2 needs an indicator in the load dataset to know whether or not a column is going to be loaded with a data value or will be set to null. This is done by having a 1 byte character field immediately following the data field that can contain nulls.

Iowa State University ADP Center DB2 Reference Handbook

32

1.

If the data field contains data and will not be null, then move a space to the 1 byte indicator field. This tells the load utility that the column will contain data.

2.

If the data field will be assigned a null value, then move spaces to the data field, then move a question mark (?) to the 1 byte indicator field. This tells the load utility to make the column null.

Example of load dataset records where the columns are id, last name, first name, date (allow nulls), address, city, and state: 123456789Doe 156789000Doe

John Steve

1995-05-15 Box 94 ?RR#3

Flint Miami

MI FL

The first record will load a valid date into the date column and it will not be null. The space after the date value indicates to not assign nulls to this column. The second record will load a null value into the date column because the 1 byte after the date field contains a question mark (?). When a DB2 table is unloaded using the DB2UNLD utility, the output dataset will contain the 1 byte following each null column. The field will contain either a space or a question mark (?) to indicate the status of the data for the column. If a question mark (?) is in the 1 byte following the column data, the column data is low values. There is no need to define elements in the Data Dictionary for the indicator fields for null columns. Once the column has been defined as null in a DB2 table definition, the Dictionary generates the appropriate indicator variables in the DCLGEN, COBOL and Easytrieve books. Examples: DCLGEN: DECLARE TABLE ...... (YEAR ID-NUM 01 UICCD-INFO-TBL. 12 UICCD-YEAR 12 UICCD-ID-NUM 01 UICCN-INFO-TBL. 12 UICCN-ID-NUM COBOL: 01 UICCD-INFO-TBL. 12 UICC-YEAR 12 UICC-ID-NUM 12 UICCI-ID-NUM

CHAR(4) CHAR(9))

NOT NULL WITH DEFAULT,

PIC X(4). PIC X(9). PIC S9(4) COMP.

PIC X(4). PIC X(9). PIC X.

SQL Statement Usage using NULLS The DCLGEN will contain a S9(4) Comp indicator variable for each column that is defined as null. This indicator variable must be used when the column that can contain nulls, is referenced in an SQL statement. In the following examples ID_NUM is defined as null: SELECT YEAR , ID_NUM INTO :UICCD-YEAR ,:UICCD-ID-NUM :UICCN-ID-NUM FROM .... UPDATE ...... SET YEAR = :UICCD-YEAR ,ID_NUM = :UICCD-ID-NUM :UICCN-ID-NUM WHERE ..... Iowa State University ADP Center DB2 Reference Handbook

33

INSERT INTO ..... ( YEAR ,ID_NUM) VALUES (:UICCD-YEAR , :UICCD-ID-NUM :UICCN-ID-NUM) The first host identifier designates the data item variable and the second host identifier designates the null indicator variable. They are NOT separated by a comma. The purpose of the indicator variable is to specify the null value. After the Select statement is executed, DB2 places data into the host variables. If the indicator variable UICCN-IDNUM contains a negative value, the column is null and the host variable UICCD-ID-NUM is unchanged. It is important to initialize the data item variable before the Select statement is issued, because previously selected data can remain in the field if the next row contains nulls in the column. If the indicator variable UICCN-ID-NUM contains a non-negative value, the column is not null and data is returned into the UICCD-ID-NUM host variable. The two host identifiers are also used for Insert and Update SQL statements. • •

When a null is desired, move a -1 value to the indicator variable and spaces to the data item variable. When a valid value is desired, move a 0 (zero) to the indicator variable and data to the data item variable.

The following are examples of coding for nulls in the Where clause: WHERE IS NULL WHERE IS NOT NULL

Iowa State University ADP Center DB2 Reference Handbook

34

CICS Browse Logic If a CICS BROWSE is desired using an index that contains more than one column, it is necessary to code one cursor with a very specific syntax to perform the desired browse function. The purpose of this cursor is to allow for a partial key to start the browse. In the past, multiple cursors were coded to accomplish this same task. The following coding technique has been developed to eliminate the need for multiple cursors (per table) in CICS browse programs. Basically, this technique combines multiple cursor WHERE clauses into one cursor. To assist the DB2 Optimizer in choosing a efficient access path, the SQL must be coded using the following concept. The following is an example; also, a sample CICS program using this logic is located in the back of this manual: DECLARE TESTCUR CURSOR FOR SELECT col1, col2, .. .. FROM table_name WHERE indx_col_1 >= :WS-INDX-COL-1 AND ((indx_col_1 = :WS-INDX-COL-1 AND indx_col_2 = :WS-INDX-COL-2 AND indx_col_3 >= :WS-INDX-COL-3) OR (indx_col_1 = :WS-INDX-COL-1 AND indx_col_2 > :WS-INDX-COL-2) OR (indx_col_1 > :WS-INDX-COL-1)) ORDER BY indx_col_1 ASC, indx_col_2 ASC, indx_col_3 ASC OPTIMIZE FOR 1 ROW The previous example shows three multiple WHERE clauses combined into one cursor. Additionally, notice that the first column of the index appears outside the parenthesis that contains the OR conditions. Be sure to check the EXPLAIN report after binding the browse program to verify that the desired index is selected and that the MATCH COLUMNS number is equal to 1. Unless the cursor is past end of file, this will start the browse. Continue to select rows from the cursor until the screen is full, I-O limits have been reached, or EOF for that cursor. Close the cursor when the browse is complete, or when exiting the program.

Forward and Backward Browses A system may have multiple CICS browse functions defined per DB2 table by defining multiple indexes to satisfy these browse functions. The number of indexes per table should be kept to a minimum. Programming logic has been developed to provide "Previous Screen" capabilities in browse programs. It will allow the client to go back to previously viewed screens by using the already defined ascending index. Contact a Database Administrator for an explanation and sample of this programming logic.

Order By Clause The Order By clause of any SQL statement, batch or CICS, must use the column name from the table or the name assigned by a 'AS' clause. Positional (e.g. Order By 2 Desc) are not allowed in an Order By clause.

Iowa State University ADP Center DB2 Reference Handbook

35

UPDATE SQL When an UPDATE SQL statement is executed that contains numeric columns, there are two ways to update the column values. In the first example the numeric data will completely replace the value in the column disregarding what was the previous value was. ***********(EXAMPLE #1)******************* UPDATE DB2.TBL_STATS SET TBL_ROW_COUNT = :WSWA-ROW-COUNT WHERE TBL_ID = :WSWA-TBL-ID

In the second example the working storage value will be added to the existing value of the column.

************(EXAMPLE #2)******************* UPDATE DB2.TBL_STATS SET TBL_ROW_COUNT = TBL_ROW_COUNT + :WSWA-ROW-COUNT WHERE TBL_ID = :WSWA-TBL-ID The reason for this is that our standard is to SELECT the row when the update CICS program is initiated, then add the amounts and perform an update. Our standard with DB2 is to NOT lock the row when it is SELECTed. Example #2 shown above ensures that the value being updated is the correct value at the moment of update. Select only columns that are needed for a particular program, and update only columns that are changed in a program. It is not necessary to do every column in a table for processing. Make sure that the columns listed in the CURSOR statement match the host variable names in the FETCH statement. If you are unsure if a column is used in a program, look at the program cross reference listing for the COBOL/DB2 column name. If the only place the column is referenced is at the beginning of the Procedure Division in the precompiler generated section, the column is not used and should be removed. If the DB2 table contains a VARCHAR element, the length of the field must be moved to the length variable in the DCLGEN before an INSERT or an UPDATE is performed on the column. One way is to define an area in working storage that can be scanned backward one character at a time until a non-space value is found. This will define the length and this value should be moved to the 49 level length field.

Batch Easytrieve Plus Hint To use a DB2 table in synchronized file processing, the SQL Select statement must be coded as part of the Easytrieve Plus File statement. See the example below. FILE SYS007 SQL (SELECT col1 ,col2 ,coln FROM clause WHERE clause ORDER BY clause INTO :hv1 ,:hv2 ,:hvn)

+ + + + + + + + + + +

* Host Variables could be from an Easytrieve Plus Macro or coded as follows. hv1 hv2

1 11

10 A 30 A

Iowa State University ADP Center DB2 Reference Handbook

36

. . hvn hv1-p1 %EZTP macro FILE SYS008 %EZTP macro SY08-KEY JOB INPUT

xx 1

1

10 A 5A

5A

(SYS007 KEY (hv1-p1) + SYS008 KEY (SY08-KEY))

If you have questions, contact a Database Administrator.

Program Compile and Bind • • •

•

•

Once the program has been coded, it can be prepared for execution. Make sure the correct options are specified for the program in the Source Control system. A DB2 bind job is started automatically after each successful test compile or production check-in compile. An Explain report which describes the access path chosen by DB2 is listed in the output of the bind job. Always evaluate the Explain report to be sure that the access path chosen by DB2 is what you expected. If not, determine the reason, correct the SQL code, and recompile the program. If the access path chosen is still not what you expected, contact a Database Administrator for assistance. Binding a program after a successful compile and link is required before any execution of the program is allowed. The BIND will actually reference the DB2 CATALOG tables and will pick an optimum path to retrieve the data based upon the available indexes, number of rows, and the SQL statement used. The result of the BIND process is that DB2 creates an application PACKAGE and/or PLAN and stores that PACKAGE and/or PLAN in DB2 CATALOG tables. The PACKAGE and/or PLAN name will be the same as the program name for batch programs. For a BATCH programs the first time a BIND is performed the FREE will not be successful because the PLAN does not exist. This is normal. For each subsequent BIND, the FREE is successful because of the previous successful BIND. If, during the course of development, the BIND fails, the next time a BIND is performed, the FREE will not be successful because it was already FREEd in the previous attempt to BIND.

Iowa State University ADP Center DB2 Reference Handbook

37

Production Compiles/Binds and SYSD/RMDS CICS Compiles/Binds

When a CICS program is checked-in during the day, the following actions are performed: 1.

A compile of the program is performed, and the object code and DBRM are stored in staging libraries.

2.

The test object code and test DBRM are deleted, and a test bind job is submitted to bind the staged production code to test DB2, using the production DBRM.

3.

That night, the object code and DBRM are moved from the staging libraries to the production libraries and a production bind is submitted.

After this initial staging of the production program is complete, you may want to move the code into production immediately for an emergency situation. The function 'LI' in the Source Control system must be executed to do this. The following actions are then performed: 1.

The object code and DBRM are immediately moved from the staging libraries to the production libraries.

2.

A production bind is submitted for execution.

3.

A production NEWCOPY is performed in the appropriate CICS region.

4.

Be sure that the compile AND bind jobs are complete before trying the transaction. Since these are two separate jobs, there may be a small time delay between the compile and bind jobs.

CAUTION --- In CICS, the production bind will hold the plan so that the client can not use the CICS function until the bind is complete, and any client in the middle of a transaction using this function will ABEND. The output from the test and production compiles can be found on RMDS and SYSD. The production compiles stay on RMDS, and the production binds with the Explain report are on RMDS for a limited time. If the bind/Explain report no longer is on RMDS, use the test or production DB2 Information Systems to submit a job that will list the current test or production Explain report. Batch Compiles/Binds

When a batch program is checked-in during the day, the following actions are performed: 1.

A compile of the program is performed, and the object code and DBRM are moved directly into the production libraries.

2.

The test object code and DBRM are deleted, and a test bind job is submitted to bind the production code to test DB2, using the production DBRM.

3.

A production bind is submitted upon successful compile.

Be sure to check whether or not a BIND is successful before trying to execute a DB2 application program. If the BIND was not successful and the program is executed, a negative SQL error code will be returned indicating that the plan is not currently available or that a connection failure has occurred. The 'XT' function in the Source Control system is for performing a test bind without doing a compile. Iowa State University ADP Center DB2 Reference Handbook

38

Note: This does not work for an Easytrieve Plus program that is not checked out. The 'XP' function in the Source Control system is for performing a production bind without doing a compile. The default parameter for the EXPLAIN option of the bind is set to YES so that each time a bind is done to test or production a row is added for each SQL statement to a DB2 table. This table is called ADPXXX.PLAN_TABLE, and the added row(s) indicate whether or not the SQL statement is using an index and whether or not a sort is being done. Also other information is provided to indicate the efficiency of the SQL code, which is very important to the performance of the program/system. After each BIND, the output on the EXPLAIN report must be viewed to ensure an efficient access path has been selected for the plan. It is very important that this information be viewed and if any questions arise, be sure and see a Database Administrator. If a DB2 plan has an inefficient access path, it is possible that by altering the SQL statement in the program, efficiency can be gained. Previous versions of Explain reports (up to 5) are stored and are available for viewing using QMF or JFT in SYSD. In QMF, enter PREV_PLANS on the command line and press <enter>. A prompt will display asking which plan ID to display. In SYSD, a JFT proc has been setup to submit a job to list the previous plans. Choose option 8-JFT, then 1-DB2, then 1-Prev. A prompt will display asking which plan ID and which printer to direct the output to. Enter SUB on the command line to submit the job.

Access Path Restrictions and Recommendations 1. • • •

CICS SQL ranks of 1 and 3 are good path selections. SQL ranks of 2, 4 and 5 are not allowed. If a rank of 1 or 3 also has a SORT associated with it, the SQL statement must be evaluated to determine if changing the statement will eliminate the SORT. If no solution can be found, the number of rows to be sorted must be determined and be a reasonable number (less than 20). Determine the number of rows that will be sorted by looking at the selection criteria in the WHERE clause.

2. • •

Batch SQL ranks of 1, 2, and 3 are good path selections. SQL ranks of 4 and 5 are allowed, but caution must be taken to verify that the entire index or table is needed by the application program. If a SORT is encountered, be aware that this is acceptable up to approximately 8,000,000 bytes (e.g. 20,000 rows with a 400 LRECL). This can be calculated by adding up the lengths of the columns in the SQL statement that has the sort in the EXPLAIN report. Determine the number of rows that will be returned by looking at the selection criteria in the WHERE clause. Multiply the number of rows returned times the LRECL to determine the number of bytes that will be sorted.

•

Iowa State University ADP Center DB2 Reference Handbook

39

Call/Linked Programming Standards • •

•

• • • • •

Called/Linked programs are written in COBOL II or Assembler; they cannot be written in Easytrieve Plus If the called/linked program is DB2 and is not called by Easytrieve batch, it is defined as the following in the Source Control System: Language -> COBOL II or Assembler DB2 Called or Linked If the called/linked program is DB2 and is called by Easytrieve batch, it is defined as the following in the Source Control System: Language -> COBOL II or Assembler DB2 Called DB2 COBOL CAF If the called/linked program is not DB2, it is defined as the following in the Source Control System: Language -> COBOL II or Assembler Called or Linked All Called/Linked DB2 programs used for validation purposes should be granted public authorization. The called/linked program should return a status indicator and message to the calling program indicating any errors in the processing by the calling program. If the called/linked program is DB2, the SQLCODE should also be returned to the calling program. All Linked CICS programs should check the EIBCALEN field to ensure that it contains the correct length for the common area being passed before any processing is done. If the length of the common area is not correct, the called program should call abend using the last four bytes of the called program name in the ABCODE field.

Easytrieve Batch • • •

An Easytrieve program that calls a DB2 program must be coded as a DB2 program. To do this, include the macro DB2DUMYP prior to the Job statement. The DB2 called program number must be entered in the Source Control System as a Package member on the DP screen of calling program The data passed to and from the called program must be defined in a virtual file with a Put statement in the Start proc. The called program can be accessed by executing the following statement: Call <program name> using

COBOL II Batch •

• •

The called program name must be defined as a working storage variable in the calling program. By using this working storage field when making the call to the called program allows the called program to have a dynamic link with the calling program. This link allows the called program to be changed without having to recompile all of the programs that call it. If the calling program calls a DB2 program then it must be coded as a DB2 program. If the calling program is not DB2 it may be changed to DB2 by including the DB2DUMYD dummy copybook in its Working Storage Section. In the Source Control System the DB2 called program number must be entered as a Package member on the DP screen of the calling program

Linked CICS Programs Be sure that any CICS system that accesses DB2 tables in application programs or linked programs has the appropriate information in the test and production CICS tables. For proper execution of linked DB2 programs in CICS, all programs linked to in CICS should be placed in Collection COMMCL on the Source Control DT screen.

Iowa State University ADP Center DB2 Reference Handbook

40

DB2 EXPLAIN Information When a compile of a DB2 program is successful, another job is started to bind the program(s). The SQL access paths are evaluated (EXPLAINed) during the bind process and the results are stored in a PLAN_TABLE. The results are selected from the PLAN_TABLE and a report is generated describing the access paths selected by the DB2 OPTIMIZER. The section PLAN_TABLE Contents describes the PLAN_TABLE column names and information about what is stored in each column. The section DB2 Access Path Selection describes the five access methods that DB2 uses to retrieve data and the section Determining Access Paths from EXPLAIN describes how to determine the access path selected by the DB2 OPTIMIZER. Previous versions of Explain reports (up to 5) are stored and are available for viewing using QMF or JFT in SYSD. In QMF, enter PREV_PLANS on the command line and press <enter>. A prompt will display asking which plan ID to display. In SYSD, a JFT proc has been setup to submit a job to list the previous plans. Choose option 8-JFT, then 1-DB2, then 1-Prev. A prompt will display asking which plan ID and which printer to direct the output to. Enter SUB on the command line to submit the job. Explain reports for test programs remain on SYSD for a limited amount of time. If the report is no longer on SYSD, a current copy of the Explain report can be obtained by using the TEST CICS DB2 Information system to submit a job to list the report. Explain reports for production programs remain on RMDS for a limited amount of time. If the report is no longer on RMDS, a current copy of the Explain report can be obtained by using the PRODC DB2 Information system to submit a job to list the report.

DB2 Explain Report Reminder CICS programs do not allow DB2 table scans and index scans because the scans can have a dramatic impact on overall response time in the entire host system. Be sure to check the DB2 Explain report on RMDS the day after each DB2 program is checked into production. In the past, slow response time was experienced across many host-based systems because a CICS/DB2 program contained several table scans. DB2 chose the access path because the production tables were empty when the program was compiled and bound six months before. Since the number of rows in the tables was very small, there was no performance impact during that six-month time period. However, the number of rows began to increase. Large amounts of CPU resources were required to satisfy the SQL statements. To remedy the performance problem, the program was rebound. Before the program was rebound, Prod-A CICS was using between 60 to 70 percent of the CPU. After rebinding the program, Prod-A CICS usage was normal, between 25 to 35 percent of the CPU. It is very important that the Explain reports be reviewed when SQL is added after the formal review process. Work is being done to help notify the analyst of scans that occur in CICS programs. The DB2 Explain report places an “eye catcher” statement on the right side of the report indicating any table or index scans. If you encounter this in any CICS program, it must be addressed to prevent potential performance problems. Various steps can be taken to eliminate scans depending on the situation. Contact a DBA if you are having problems with scans in CICS programs.

Access Path Restrictions and Recommendations 1. CICS • • •

SQL ranks of 1 and 3 are good path selections. SQL ranks of 2, 4 and 5 are not allowed. If a rank of 1 or 3 also has a SORT associated with it, the SQL statement must be evaluated to determine if changing the statement will eliminate the SORT. If no solution can be found, the number of rows to be sorted must be determined and be a reasonable number (less than 20). Determine the number of rows that will be sorted by looking at the selection criteria in the WHERE clause.

Iowa State University ADP Center DB2 Reference Handbook

41

2. Batch • •

•

SQL ranks of 1, 2, and 3 are good path selections. SQL ranks of 4 and 5 are allowed, but caution must be taken to verify that the entire index or table is needed by the application program. If a SORT is encountered, be aware that this is acceptable up to approx. 8,000,000 bytes or 20,000 rows with a 400 LRECL. This can be determined by adding up the lengths of the columns in the SQL statement that has the sort in the EXPLAIN report. Determine the number of rows that will be returned by looking at the selection criteria in the WHERE clause. Multiply the number of rows returned times the LRECL to determine the number of bytes that will be sorted.

Purpose of Explain 1.

SQL statements specify WHAT is wanted; DB2 determines HOW TO GET IT: • All SQL statements processed by BIND process • BIND invokes DB2 OPTIMIZER • OPTIMIZER determines how data is accessed

2.

Knowledge of access paths is important for: • Proper data base design • Efficient application program coding • Writing efficient queries • Performance measurement and tuning • Capacity planning

3.

The DB2 EXPLAIN statement: • Provides access path information • Identifies how SQL statement is processed • What INDEXES are being accessed

How to Use EXPLAIN 1.

EXPLAIN can be used: • In interactive or imbedded SQL statements • When BINDING an application PLAN • When REBINDING an application PLAN

2.

EXPLAIN information stored in: • Special user-defined table: Table Name: ADPXXX.PLAN_TABLE • Each analyst has a TEST and PRODUCTION PLAN_TABLE • Table information queried using SQL DML

3.

EXPLAIN provides information about: • Access path chosen, if indexes were used • Order which tables are accessed and joined • JOIN method selected • Whether sorts are performed • LOCKING strategy

Iowa State University ADP Center DB2 Reference Handbook

42

PLAN_TABLE Contents COLUMN QUERYNO QBLOCKNO APPLNAME PROGNAME PLANNO

METHOD

CREATOR TNAME TABNO ACCESSTYPE

MATCHCOLS ACCESSCREATOR ACCESSNAME INDEXONLY

SORTN_UNIQ SORTN_JOIN SORTN_ORDERBY SORTN_GROUPBY SORTC_UNIQ

USAGE "SET QUERYNO = integer" for interactive SQL or the DB2 Precompiler statement number for imbedded SQL. (the precompiler listing is not kept) The position of the query in the statement being explained (1 for the outermost query, 2 for the next query, and so forth), Name of application plan or blank for dynamic SQL. Name of program or package containing the statement to be explained. Number indicating STEP of application plan containing subselect identified by QBLOCKNO is processed. Each new table accessed requires a new STEP in the plan. Processing method used in this step: 0 = Access first table (PLANNO = 1) 1 = Nested loop join (no sorting) 2 = Merge scan join (sorting required) 3 = Additional sorts are required for ORDER BY, GROUP BY, DISTINCT, UNION 4 = Hybrid join Creator of TABLE (TNAME) accessed in the step; Blank if METHOD = 3 Name of TABLE accessed in this step; Blank if METHOD = 3 Reference number to distinguish different accesses to same table; Zero if METHOD = 3. Method of accessing table (TNAME); I = Index used which is identified by ACCESSCREATOR and ACCESSNAME fields. I1 = One fetch index scan. N = Index scan when matching predicate contains IN keyword. R = Tablespace scan; no index is used. M = Multiple index scan; followed by MX, MI, or MU. MX = Index scan on the index named in ACCESSNAME. MI = Intersection of multiple indexes. MU = Union of multiple indexes. Blank = one of the following: QBLOCKNO = 1 if INSERT; the INSERT statement is accessed by first index created on table -orUPDATE and DELETE statements that access by cursor using "WHERE CURRENT OF cursor-name" Number of index columns used to match predicate (ACCESSTYPE = I, I1, N, or MX); Zero if no index columns used or ACCESSTYPE not = I. Creator of INDEX named in ACCESSNAME (ACCESSTYPE = I, I1, N, or MX); otherwise, blank Name of INDEX (ACCESSTYPE = I, I1, N, or MX); otherwise, blank Whether access to an index alone is enough to carry out the request, or whether data too must be accessed: Y = Only INDEX needs to be accessed N = Data must also be accessed in addition to the index Sort performed on real (NEW) table to eliminate duplicate rows? Yes (Y) or No (N) Sort performed on real (NEW) table for METHOD = 2 join? Yes (Y) or No (N) Sort performed on real (NEW) table for ORDER BY? Yes (Y) or No (N) Sort performed on real (NEW) table for GROUP BY? Yes (Y) or No (N) Sort performed on COMPOSITE table (used for join) to remove duplicate rows? Yes (Y) or No (N)

Iowa State University ADP Center DB2 Reference Handbook

43

SORTC_JOIN SORTC_ORDERBY SORTC_GROUPBY TSLOCKMODE

Sort performed on COMPOSITE table (used for join) for METHOD = 2 join? Yes (Y) or No (N) Sort performed on COMPOSITE table (used for join) for ORDER BY? Yes (Y) or No (N) Sort performed on COMPOSITE table (used for join) for GROUP BY? Yes (Y) or No (N) Lock mode used on tablespace containing real (NEW) table; IS = INTENT SHARE Lock owner can read data in tablespace but not change it. Other users can both read and change data. Concurrent users reading data acquire a share (S) lock on page being read. S = SHARE Lock owner and other concurrent users can read but not change data in tablespace. IX = INTENT EXCLUSIVE Lock owner and other concurrent users can read and change data in tablespace. When owner changes data, an exclusive (X) page lock is acquired. SIX = SHARE WITH INTENT EXCLUSIVE Lock owner can read and change data in tablespace. Concurrent users can read but cannot change data. When owner reads data, a share (S) page lock is acquired. When owner changes data, an exclusive (X) page lock is acquired.

TIMESTAMP REMARKS PREFETCH

COLUMN_FN_EVAL

MIXOPSEQ

VERSION COLLID JOIN_TYPE QBLOCK_TYPE

X = EXCLUSIVE Lock owner can read and change data in tablespace. No other users can either read or change the data. DATE and TIME the EXPLAIN statement was processed (yyyymmddhhmmssth) Empty character string not used by EXPLAIN; can be used by user. A one-character indicator that tells whether data pages were read in advance by prefetch. S = sequential prefetch; L = List prefetch; blank = prefetch is not activated or unknown. A one-character indicator that tells when a column function of an SQL statement was evaluated. R = data retrieval time; S = at sort time; blank = at data manipulation time or unknown. A value that indicates the numerical sequence of the steps in multi-index operations: 1,2, ... n when ACCESSTYPE is MX, MI, or MU 0 when ACCESSTYPE is I, I1, M, N, R, or blank Version identifier for the package. Blank if not applicable. Collection ID for the package. Blank if not applicable. Type of OUTER JOIN. F – full outer, L – left outer Type of SQL statement. INSERT, UPDATE, DELETE, SELECT, etc.

****** NOTE ****** The columns: CREATOR, ACCESSCREATOR and REMARKS are the only columns not used in the EXPLAIN report.

Iowa State University ADP Center DB2 Reference Handbook

44

DB2 Access Path Selection When DB2 is used, SQL statements specify what data is needed, not how to get it. The optimal access path to data is determined by DB2 at BIND time by invoking OPTIMIZER which makes decisions based on the expected CPU utilization and number of I/O operations required to satisfy the request. This decision process is called the ACCESS PATH SELECTION or APS. The optimum access path selected is primarily based on the use of available indexes. The cost of accessing data using an index will generally be less than if a scan of a table space(s) is performed. In DB2, the five different access methods to data are shown below and are listed in probable order of efficiency: (1) MATCHING INDEX SCAN WITHOUT DATA REFERENCE Requests are satisfied entirely using an index; no data pages are read. In addition, the levels of indexes in the index tree structure can be used to only read index LEAF pages that satisfy the request. (2) NON-MATCHING INDEX SCAN WITHOUT DATA REFERENCE Requests are satisfied entirely using an index; no data pages are read. The levels of indexes in the index tree structure CANNOT be used to satisfy the request; all of the index LEAF pages must be scanned. (3) MATCHING INDEX SCAN WITH DATA REFERENCE The levels of indexes in the index tree structure can be used to only read index LEAF pages that satisfy the request. Only data pages with qualifying rows are then accessed. (4) NON-MATCHING INDEX SCAN WITH DATA REFERENCE The levels of indexes in the index tree structure CANNOT be used to satisfy the request; all of the index LEAF pages must be scanned. Only data pages with qualifying rows are then accessed. (5) TABLE SPACE SCAN All data pages in a table space are scanned; no indexes are used.

Determining Access Paths From EXPLAIN The combination of ACCESSTYPE, MATCHCOLS, and INDEXONLY indicate what type of access paths were chosen by DB2, identified by the heading "SQL RANK" on the EXPLAIN report. ACCESS TYPE I I I I R

MATCH COLS >0 0 >0 0 N/A

INDEX ONLY Y Y N N N/A

SQL RANK 1 2 3 4 5

ACCESS PATH SELECTED Matching index scan without data reference Non-matching index scan without data reference Matching index scan with data reference Non-matching index scan with data reference Table space scan; no indexes used

On the EXPLAIN report are 4 columns on the right hand side which indicate SORT, LOCK, JOIN, SCAN. The program that prints the report uses the information from the chart, to determine whether or not the operation exists. If it does, an indicator is placed in the appropriate report column.

Previous Explain Reports Each time a DB2 application program is checked in and bound to Production, a copy of the current Explain report is loaded to a cumulative plan table. This cumulative plan table maintains the last five Explain reports for each program. To view a previous Explain report for a specific program, use the DE function in the DB2 Information System on PRODC. A batch job is started to retrieve the Explain information.

Iowa State University ADP Center DB2 Reference Handbook

45

SQL General Information A complete description of the various types of SQL statements can be found in the IBM manual 'Introduction to SQL'. SQL error codes will appear while testing the DB2 programs. Explanations of the common error messages can be found in the back of this handbook. The complete list of SQL error codes can be found in the IBM manual entitled 'SQL Messages and Codes' which is accessible on the Administrative Technology Service's Internal Web Resources page. Indexes are never explicitly specified in a program or dynamic SQL. DB2 analyzes the catalogs at bind time to see if an available index can be used to access the data more efficiently. When updating VSAM and DB2 tables in the same CICS program, perform the DB2 updates before the VSAM updates for the following reason. If an SQL Error code that is more severe than just a warning is returned on one of the DB2 updates, the EXEC CICS SYNCPOINT ROLLBACK END-EXEC. can be issued, and all DB2 changes will be rolled back. If the VSAM changes where processed before the DB2 changes, the DB2 changes would be rolled back but the VSAM would not, thus creating file discrepancies. There are 5 types of SQL statements that are most commonly used in application programs. They are: SELECT INTO

UPDATE

INSERT

DELETE

DECLARE CURSOR/FETCH

This statement is used to select columns for only ONE row of a DB2 table. If multiple rows are desired, the DECLARE CURSOR/FETCH statements are needed. This statement is used to update columns for one or more rows of a DB2 table. Care must be taken when coding the WHERE clause to ensure that only the desired row(s) will be updated. If the WHERE clause is left out of the statement, all rows in the table are updated. This statement is used to insert ONE row into a DB2 table. No WHERE clause is needed, since DB2 uses the clustering index to determine the location in the table to insert the row. All indexes associated with the table are updated to reflect the existence of the new row. This statement is used to delete one or more rows of a DB2 table. Care must be taken when coding the WHERE clause to ensure that only the desired row(s) will be deleted. If the WHERE clause is left out of the statement, all rows in the table are deleted. The keys to the deleted rows of the table are also deleted from the associated indexes. This is a two part set of statements used to select one or more rows from a DB2 table. The DECLARE CURSOR statement is coded in the Working Storage section of the program, while the FETCH statement is coded in the IO section of the program. The 'Order By' and 'For Update of' clauses are mutually exclusive. The 'For Update of' clause lets you use 'Where Current of Cursor' in the update and delete SQL statements associated with the cursor, but the access path chosen by DB2 for the cursor may not be the desired order for the data being retrieved. If the order of the data is important, use the 'Order By' clause instead, and use explicit key columns in the update and delete SQL statements associated with the cursor.

Use DATE, TIME, and TIMESTAMP data types when date and time fields are required. Be aware that when using these column types, a valid DATE, TIME, or TIMESTAMP must be in the column, they cannot contain spaces. When performing an INSERT statement, the current date, current time or current timestamp can be inserted into the column of the row by leaving that particular column name out of the INSERT statement. DB2 recognizes that the column is not in the INSERT statement and assigns a default, which is the current value. SQL provides methods of performing more complex queries. The following is a description and example of 3 types of methods: Iowa State University ADP Center DB2 Reference Handbook

46

•

JOIN - This capability is available if you want to write a query that combines data from two or more tables or views, based on a comparison of column values. Example: SELECT A.NAME, B.DEPT_NAME FROM ISU.STAFF A, ISU.ORG B WHERE A.ID = B.MANAGER The above example lists manager's name and department name for all departments that have a manager. To make this list, data had to be selected from two tables. The department names are in ISU.ORG; the managers' names are in ISU.STAFF. To select from both tables efficiently, they must be joined using a column of data that appears in both. In this case, the ID column in ISU.STAFF and the MANAGER column in ISU.ORG represent the employee number. The A and B are used to qualify the columns. The WHERE clause dictates that only rows be selected in which the employee number for MANAGER is the same as that for the employee ID.

•

UNION - This method can be used to merge values from two or more tables into the same columns, but different rows, for the same report Example: SELECT NAME, 'EMPLOYEE ' FROM ISU.STAFF WHERE YEARS < 3 UNION SELECT NAME, 'APPLICANT' FROM ISU.APPLICANT WHERE EDLEVEL > 14 The above example will list the name of the individual and whether the person is an EMPLOYEE or an APPLICANT on the same report. The important thing to remember when using UNION is that you are interleaving the rows of the reports from two (or more) queries. To make sense, these rows should relate to one another, have the same width, and have the same data type.

•

Subselect/Sub Query - A subselect is used within a SELECT statement to supply an unknown value to a condition of the main query. It is a complete SQL query in itself that appears in a WHERE clause of the main query. The subselect query is executed by DB2 before the main query to provide a value or a set of values to be used in the condition portion of the main query. Example: SELECT NAME, SALARY FROM ISU.STAFF WHERE SALARY > (SELECT AVG(SALARY) FROM ISU.STAFF) The above example determines the average salary for all rows in the table ISU.STAFF then uses that average value to determine the names of the staff members that have a salary which is greater than the overall average.

***NOTE*** The performance of an SQL statement will change very little between its execution in QMF, Query Tool, or Web based and in batch/CICS programs.

Iowa State University ADP Center DB2 Reference Handbook

47

Methods of Joining Tables Inner Join Until stated otherwise, avoid coding the INNER JOIN syntax with ON clauses in DB2 V4. When using the INNER JOIN SYNTAX with the ON clause, all WHERE clauses will be applied after the join is complete. This is damaging for performance as it is important that some filtering be done earlier. As shown below, there are two options now. The best one should be chosen in each case. Either stay with the old syntax that uses commas in the FROM clause and WHERE predicates so that normal join algorithms are used to apply predicates earlier in the process, or use Table Expression to move the filtering as early in the process as possible. Wrong Way SELECT parent.column1,child.column2 FROM parent INNER JOIN child ON child.id = parent.id WHERE child.age < 10 Right Way SELECT parent.column1,child.column2 FROM parent, child WHERE child.id = parent.id AND child.age < 10 In the WRONG WAY, the all rows are first joined by ID and after the composite table is built the rows are filtered for children under the age of 10. In the RIGHT WAY, the child table is filtered for children under the age of 10 prior to the join. BIG DIFFERENCE! Watch for this to change (WRONG WAY) in the future, maybe even on a maintenance tape. Wrong Way SELECT parent.column1,child.column2 FROM parent INNER JOIN child ON child.id = parent.id WHERE child.age < 10 Right Way SELECT parent.column1,child.column2 FROM parent INNER JOIN (SELECT column2 FROM child WHERE age < 10) AS child ON child.id=parent.id In the RIGHT WAY, the child table is forced to be filtered prior to the JOIN.

Iowa State University ADP Center DB2 Reference Handbook

48

Outer Join Considerations Better performance than DB2 releases before DB2 V4 You can expect full outer join to perform better than the previous home-grown solutions because data is read only once, whereas it is read two or three times with the UNIONs used in DB2 V3 to simulate an outer join. There is also no need for application-defined null values. Predicate sequence It is important to remember, and to evaluate the related effects, that the SQL statement is executed in steps with the following precise sequence: • FROM • Outer or inner join with the ON conditions • WHERE • GROUP BY • HAVING • SELECT You must check the sequence of your SQL and the data model of your table if you want to avoid meaningless results; local predicates that do not apply to the results of the join must be coded in a nested table expression. For example, say you need a list of all departments and the average salary by department if an individual's salary exceeds 28000. You use left outer join because some department might have no employee with SALARY > 28000, and issue the statement: SELECT DEPTNAME, AVG(SALARY) AS AVGSAL FROM DEPT LEFT OUTER JOIN EMP ON DEPTNO = WORKDEPT WHERE SALARY > 28000 GROUP BY DEPTNAME ADMINISTRATION SYSTEMS INFORMATION CENTER MANUFACTURING SYSTEMS OPERATIONS PLANNING SPIFFY COMPUTER SERVICE DIV. SUPPORT SERVICES

31230.00000000 31696.66666666 30643.33333333 29750.00000000 41250.00000000 40850.00000000 40175.00000000

The result will be wrong because DB2 first executes the outer join and then applies the WHERE clause removing the employees with SALARY <= 28000. Departments with no employees, or departments where all employees get a SALARY <= 28000, will not appear in the result table. To ensure the appropriate sequence and that local predicates apply to a table, you must use nested table expression as shown in the following SQL statement: SELECT DEPTNAME, AVG(SALARY) FROM DEPT LEFT OUTER JOIN (SELECT WORKDEPT,SALARY FROM EMP WHERE SALARY > 28000) AS SAL ON DEPTNO=WORKDEPT GROUP BY DEPTNAME ADMINISTRATION SYSTEMS BRANCH OFFICE F2 BRANCH OFFICE G2 BRANCH OFFICE H2 BRANCH OFFICE I2

31230.00000000

Iowa State University ADP Center DB2 Reference Handbook

49

BRANCH OFFICE J2 DEVELOPMENT CENTER INFORMATION CENTER MANUFACTURING SYSTEMS OPERATIONS PLANNING SOFTWARE SUPPORT SPIFFY COMPUTER SERVICE DIV. SUPPORT SERVICES

31696.66666666 30643.33333333 29750.00000000 41250.00000000 40850.00000000 40175.00000000

There is no execution of right outer join as such. If you specify RIGHT OUTER JOIN in the SQL statement, DB2 uses the left outer join by switching the position of the tables. The sequence in which you name the tables in the FROM clause of a left or right outer join is important to ensure that the result table is correct. An outer join on more than two tables must be performed in steps. Outer join operates on only two tables at a time. If you have more (and it is difficult to predict the results) you must join in steps. With a full outer join you can only use the EQUAL operator and the AND; BETWEEN, LIKE, IN, OR, and NOT are not allowed. In expressions following the ON clause • Scalar functions are not allowed. • The COALESCE function is permitted only with a full outer join. • The operands can only be columns, not constants. When two or more tables are JOINED, DB2 Access Path Selection (APS) will choose one of the following JOIN techniques and record the information in the METHOD column in the PLAN_TABLE: (1) NESTED LOOP JOIN (METHOD = 1) • In most cases, if indexes are available on the JOIN columns specified in the WHERE clause, APS will choose the NESTED LOOP JOIN since no sorting is involved before scanning for qualified rows. • One of the tables in a JOIN is selected as the OUTER table, the other tables becomes the INNER table. Generally, the table with the most selective predicates is chosen as the OUTER table. • The OUTER table is scanned for a match on non-join predicate (s). When one row is found, the INNER table is scanned for qualifying rows. Qualifying INNER table rows are then JOINED to the OUTER table row. • Scanning resumes in the OUTER table to find the next qualifying row and the process continues. (2) MERGE SCAN JOIN (METHOD = 2) • Sorting of the tables in a JOIN is performed prior to scanning for qualifying rows. The tables are ordered in the sequence of the columns in the JOIN predicate. • The tables are scanned only once. When a qualifying row is found in the first table, the second table is scanned and matching rows are JOINED to the first table. • Scanning continues with the next qualifying row of the first table and the process continues. (3) HYBRID JOIN (METHOD = 4) • The Hybrid join uses list prefetch much more efficiently than the Nested Loop join, and it also processes duplicate rows more efficiently.

Iowa State University ADP Center DB2 Reference Handbook

50

Sub Queries When you write a SELECT statement, you can place another SELECT statement within the WHERE clause. Each additional SELECT starts a sub query. A sub query can, in turn, include another sub query whose value is substituted into its WHERE clause. In addition, a WHERE clause can include sub queries in more than one search condition. The sub query can refer to tables and columns that are different than the ones used in the main query. The following statement selects the division and location from the ORG table of the employee whose ID in the STAFF table is 280: SELECT DEPTNAME, MGRNO FROM DB2.DEPT WHERE DEPTNO = (SELECT WORKDEPT FROM DB2.EMP WHERE EMPNO = ‘000010’) When processing this statement, DB2 first determines the result of the sub query. The result is 66, since the employee with ID 280 is in department 66. Then the final result is taken from the row of the ORG table whose DEPTNUMB column has the value of 66. The final result is: DEPTNAME SPIFFY COMPUTER SERVICE DIV.

MGRNO 000010

When you use a sub query, the database manager evaluates it and substitutes the resulting value directly into the WHERE clause.

Non-correlated Sub Queries A non-correlated sub query makes no reference to the outer query. The following example is an non-correlated sub query that lists the employee number and name of employees in department 'A00' with a salary greater than the average salary of the department: SELECT EMPNO, LASTNAME FROM DB2.EMP WHERE WORKDEPT = 'A00' AND SALARY > (SELECT AVG(SALARY) FROM DB2.EMP WHERE WORKDEPT = 'A00') EMPNO 000010 000110 200010

LASTNAME HAAS LUCCHESI HEMMINGER

WORKDEPT A00 A00 A00

Iowa State University ADP Center DB2 Reference Handbook

51

Correlated Sub Queries A correlated sub query makes at least one reference to a column in the outer query. If you want to know the average salary for every department, the sub query needs to be evaluated once for every department. You can do this by using the correlation capability of SQL, which permits you to write a sub query that is executed repeatedly, once for each row of the table identified in the outer-level query. This type of correlated sub query is used to compute some property of each row of the outer-level table that is needed to evaluate a predicate in the sub query. This example shows all the employees whose salary is higher than the average salary of their department: SELECT E1.EMPNO, E1.LASTNAME, E1.WORKDEPT FROM DB2.EMP E1 WHERE SALARY > (SELECT AVG(SALARY) FROM DB2.EMP E2 WHERE E2.WORKDEPT = E1.WORKDEPT) ORDER BY E1.WORKDEPT In this query, the sub query is evaluated once for every department. The result is: EMPNO 000010 000110 200010 000030 000060 000150 000200 000220 200220 000070 000240 000270 200240 000090 000280 200280 000100 000330 200330

LASTNAME HAAS LUCCHESI HEMMINGER KWAN STERN ADAMSON BROWN LUTZ JOHN PULASKI MARINO PEREZ MONTEVERDE HENDERSON SCHNEIDER SCHWARTZ SPENSER LEE WONG

WORKDEPT A00 A00 A00 C01 D11 D11 D11 D11 D11 D21 D21 D21 D21 E11 E11 E11 E21 E21 E21

To write a query with a correlated sub query, use the same basic format of an ordinary outer query with a sub query. However, in the FROM clause of the outer query, just after the table name, place a correlation name. The sub query may then contain column references qualified by the correlation name. For example, if E1 is a correlation name, then E1.WORKDEPT means the WORKDEPT value of the current row of the table in the outer query. The sub query is (conceptually) reevaluated for each row of the table in the outer query. By using a correlated sub query, you let the system do the work for you and reduce the amount of code you need to write within your application.

Implementing a Correlated Sub Query When would you want to use a correlated sub query? The use of a column function is sometimes a clue.

Iowa State University ADP Center DB2 Reference Handbook

52

Let's say you want to list the employees whose level of education is higher than the average for their department. First, you must determine the select-list items. The problem says "List the employees". This implies that the EMPNO from the DB2.EMP table should be sufficient to uniquely identify employees. The problem also states the level of education (EDLEVEL) and the employees' departments (WORKDEPT) as conditions. While the problem does not explicitly ask for columns to be displayed, including them in the select-list will help illustrate the solution. A part of the query can now be constructed: SELECT LASTNAME, WORKDEPT, EDLEVEL FROM DB2.EMP Next, a search condition (WHERE clause) is needed. The problem statement says, "...whose level of education is higher than the average for that employee's department". This means that for every employee in the table, the average education level for that employee's department must be computed. This statement fits the description of a correlated sub query. Some property (average level of education of the current employee's department) is being computed for each row. A correlation name is needed for the DB2.EMP table: SELECT LASTNAME, WORKDEPT, EDLEVEL FROM DB2.EMP E1 The sub query needed is simple. It computes the average level of education for each department. The complete SQL statement is: SELECT LASTNAME, WORKDEPT, EDLEVEL FROM DB2.EMP E1 WHERE EDLEVEL > (SELECT AVG(EDLEVEL) FROM DB2.EMP E2 WHERE E2.WORKDEPT = E1.WORKDEPT) The result is: LASTNAME HAAS KWAN PULASKI HENDERSON LUCCHESI PIANKA SCOUTTEN JONES LUTZ MARINO JOHNSON SCHNEIDER MEHTA GOUNOT HEMMINGER JOHN MONTEVERDE SCHWARTZ ALONZO

WORKDEPT A00 C01 D21 E11 A00 D11 D11 D11 D11 D21 D21 E11 E21 E21 A00 D11 D21 E11 E21

EDLEVEL 18 20 16 16 19 17 17 17 18 17 16 17 16 16 18 18 17 17 16

Suppose that instead of listing the employee's department number, you list the department name. The information you need (DEPTNAME) is in a separate table (DEPT). The outer-level query that defines a correlation variable can also be a join query . When you use joins in an outer-level query, list the tables to be joined in the FROM clause, and place the correlation name next to any of these table names. Iowa State University ADP Center DB2 Reference Handbook

53

To modify the query to list the department's name instead of its number, replace WORKDEPT by DEPTNAME in the select-list. The FROM clause must now also include the DEPT table, and the WHERE clause must express the appropriate join condition. This is the modified query: SELECT LASTNAME, DEPTNAME, EDLEVEL FROM DB2.EMP E1, DB2.DEPT WHERE E1.WORKDEPT = DEPTNO AND EDLEVEL > (SELECT AVG(EDLEVEL) FROM DB2.EMP E2 WHERE E2.WORKDEPT = E1.WORKDEPT) LASTNAME HAAS KWAN PULASKI HENDERSON LUCCHESI PIANKA SCOUTTEN JONES LUTZ MARINO JOHNSON SCHNEIDER MEHTA GOUNOT HEMMINGER JOHN MONTEVERDE SCHWARTZ ALONZO

DEPTNAME SPIFFY COMPUTER SERVICE DIV. INFORMATION CENTER ADMINISTRATION SYSTEMS OPERATIONS SPIFFY COMPUTER SERVICE DIV. MANUFACTURING SYSTEMS MANUFACTURING SYSTEMS MANUFACTURING SYSTEMS MANUFACTURING SYSTEMS ADMINISTRATION SYSTEMS ADMINISTRATION SYSTEMS OPERATIONS SOFTWARE SUPPORT SOFTWARE SUPPORT SPIFFY COMPUTER SERVICE DIV. MANUFACTURING SYSTEMS ADMINISTRATION SYSTEMS OPERATIONS SOFTWARE SUPPORT

EDLEVEL 18 20 16 16 19 17 17 17 18 17 16 17 16 16 18 18 17 17 16

The above examples show that the correlation name used in a sub query must be defined in the FROM clause of some query that contains the correlated sub query. However, this containment may involve several levels of nesting. Suppose that some departments have only a few employees and therefore their average education level may be misleading. You might decide that in order for the average level of education to be a meaningful number to compare an employee against, there must be at least five employees in a department. So now we have to list the employees whose level of education is higher than the average for that employee's department, and only consider departments with at least five employees. The problem implies another sub query because, for each employee in the outer-level query, the total number of employees in that person's department must be counted: SELECT COUNT(*) FROM DB2.EMP E3 WHERE E3.WORKDEPT = E1.WORKDEPT Only if the count is greater than or equal to 5 is an average to be computed: SELECT AVG(EDLEVEL) FROM DB2.EMP E2 WHERE E2.WORKDEPT = E1.WORKDEPT AND 5 <= (SELECT COUNT(*) FROM DB2.EMP E3 WHERE E3.WORKDEPT = E1.WORKDEPT) Finally, only those employees whose level of education is greater than the average for that department are included: SELECT LASTNAME, DEPTNAME, EDLEVEL Iowa State University ADP Center DB2 Reference Handbook

54

FROM DB2.EMP E1, DB2.DEPT WHERE E1.WORKDEPT = .DEPTNO AND EDLEVEL > (SELECT AVG(EDLEVEL) FROM DB2.EMP E2 WHERE E2.WORKDEPT = E1.WORKDEPT AND 5 <= (SELECT COUNT(*) FROM DB2.EMP E3 WHERE E3.WORKDEPT = E1.WORKDEPT)) This statement produces the following result: LASTNAME HAAS PULASKI HENDERSON LUCCHESI PIANKA SCOUTTEN JONES LUTZ MARINO JOHNSON SCHNEIDER MEHTA GOUNOT HEMMINGER JOHN MONTEVERDE SCHWARTZ ALONZO

DEPTNAME SPIFFY COMPUTER SERVICE DIV. ADMINISTRATION SYSTEMS OPERATIONS SPIFFY COMPUTER SERVICE DIV. MANUFACTURING SYSTEMS MANUFACTURING SYSTEMS MANUFACTURING SYSTEMS MANUFACTURING SYSTEMS ADMINISTRATION SYSTEMS ADMINISTRATION SYSTEMS OPERATIONS SOFTWARE SUPPORT SOFTWARE SUPPORT SPIFFY COMPUTER SERVICE DIV. MANUFACTURING SYSTEMS ADMINISTRATION SYSTEMS OPERATIONS SOFTWARE SUPPORT

Iowa State University ADP Center DB2 Reference Handbook

EDLEVEL 18 16 16 19 17 17 17 18 17 16 17 16 16 18 18 17 17 16

55

SQL Examples "AS CLAUSE" To Name Result Columns SELECT EMPNO, LASTNAME, SALARY+BONUS+COMM AS COMPENSATION FROM DB2.EMP WHERE EMPNO < '000100' ORDER BY EMPNO ;

Using "AS CLAUSE" To Name Result Columns Using "AS CLAUSE" In ORDER BY Clause Note: "AS CLAUSE" can not be used in a “GROUP BY CLAUSE” SELECT YEAR(HIREDATE) AS YEAR1 , (SALARY+BONUS+COMM) AS COMPEN1 FROM DB2.EMP ORDER BY YEAR1 ;

Nested Table Expression With "AS CLAUSE” Note: the second select creates a "temporary table" Note: you can group by a "AS COLUMN" definition from the temporary table SELECT YEAR1 , SUM(COMPEN1) AS COMPEN FROM (SELECT YEAR(HIREDATE) AS YEAR1 , SALARY+BONUS+COMM AS COMPEN1 FROM DB2.EMP) AS TEMP GROUP BY YEAR1 ;

OUTER JOIN - FULL Notice the "ON" clause - it is used for the join condition (the columns on which the join is being performed) in previous db2 releases these columns would be in the "WHERE” clause SELECT EMPNO, LASTNAME, WORKDEPT,DEPTNO,DEPTNAME FROM DB2.EMP FULL OUTER JOIN DB2.DEPT ON WORKDEPT = DEPTNO ORDER BY EMPNO ;

OUTER JOIN - LEFT SELECT EMPNO, LASTNAME, WORKDEPT,DEPTNO,DEPTNAME FROM DB2.EMP LEFT OUTER JOIN DB2.DEPT ON WORKDEPT = DEPTNO ORDER BY EMPNO ;

OUTER JOIN - RIGHT SELECT EMPNO, LASTNAME, WORKDEPT,DEPTNO,DEPTNAME FROM DB2.EMP RIGHT OUTER JOIN DB2.DEPT ON WORKDEPT = DEPTNO ORDER BY EMPNO ;

Iowa State University ADP Center DB2 Reference Handbook

56

OUTER JOIN FULL With Using "COALESCE" Function The "COALESCE" function is equivalent to the "VALUE” function SELECT EMPNO, LASTNAME, COALESCE(WORKDEPT,DEPTNO) AS DEPT,DEPTNAME FROM DB2.EMP FULL OUTER JOIN DB2.DEPT ON WORKDEPT = DEPTNO ORDER BY DEPT,EMPNO ;

Three Way Join SELECT EMPNO ,LASTNAME ,COALESCE(WORKDEPT,DEPT1) AS DEPT ,DEPTNAME ,PROJNAME FROM DB2.EMP LEFT OUTER JOIN (SELECT COALESCE(D.DEPTNO,P.DEPTNO) AS DEPT1 ,DEPTNAME ,PROJNAME FROM DB2.DEPT D FULL OUTER JOIN DB2.PROJ P ON D.DEPTNO = P.DEPTNO) AS TEMP1 ON WORKDEPT = DEPT1 WHERE EMPNO < '000100' ORDER BY EMPNO ;

Iowa State University ADP Center DB2 Reference Handbook

57

Common SQL Return Codes and Possible Solutions DB2 SQL return codes that can be classified as warnings are “record not found” and “end of file” (SQL CODE = +100 for both). A complete list of SQL error codes is available on the ATS SIR page. If the last character of the error code is alpha, decode to numeric, I=0, J=1, K=2, etc. The following is a list of the common SQL error codes and items to check to POSSIBLY resolve the error condition. By no means are these meant to cover every possible cause and solution. Lookup the SQL code via the ATS Internal Web Resource page for the latest reasons for the sqlcode: -303

-305

-551

-803

-805

-811

-818

-904

-911

This error indicates that there is a possible mismatch between the DB2 columns selected and the working storage host variables that the DB2 columns are being selected into. Check to be sure that the host variables are in the same order as the columns being selected. A possible cause of this error is that a SELECT statement with column functions did not return a row and tried to assign a null value to the host variable. This is similar to a +100 return code for a SELECT statement without any column functions. If you determine that this "not found" condition is OK, you may want to add another 88 level to the WS-SQLCODE field that has a value of -305, and add the 88 level item to your SQL error checking code to prevent an abend. This message indicates that the user does not have authority to perform an operation. This message is misleading in that it usually indicates that the object named in the error message does not exist. The usual problem is that a column, table, or index name is misspelled. In a Drop/Create step, a -551 may be received when there are previous errors in the step. Look at each SQL statement starting at the top of step to determine the first SQL statement that was in error as it may have cause the subsequent -551 errors. An INSERT or UPDATE SQL statement may return this error code indicating that one or more unique indexes contains columns of the table such that no two rows can contain duplicate values. Check the data that is causing the error to determine what columns are violating the unique constraints and then correct the data. Plan does not contain a program module. For a CICS application, this could indicate that the CICS New Copy function failed. Contact a DB2 System Administrator for verification. In batch, this could indicate that a called program is not bound into the batch programs application plan. The means that a direct select in a program has resulted in more than one row. The Where clause for the statement must be coded to return a result set of just one row. Normally a direct select should use a unique key index. This can be determined by looking at the Explain report information. The timestamps do not match between the compiled object code and the DBRM library member that was created during a bind. Check to be sure the bind was successful. If not, correct problem and bind the plan. Another possible cause is that the JOBLIB statement has been changed and the wrong object code member is being executed against the wrong DB2 subsystem. Another possible cause is that the program has been compiled and executed before the most recent bind is complete. The bind is a separate job from the compile and may get held up if the initiators are very busy. Always verify that the bind is complete before execution. In a CICS update program, this could indicate that a full image copy is required before any updating of the table can take place. Perform a Display Database using the DB2 Information System to determine if an image copy is pending on the requested tablespace. If a copy is pending, perform a full image copy on the tablespace to remove the copy pending. This message possibly indicates that either a LOCK is currently placed on the resource requested or the resource is STOPPED. Check to make sure that a QMF, Xpeditor or CEDF session is not holding the resource. A batch job could be executing that is performing updates/inserts/deletes that is holding locks on given pages until it completes. You may want to perform a DISPLAY DATABASE using the DB2 Information System to display the status of the requested resource.

Iowa State University ADP Center DB2 Reference Handbook

58

-922 (CICS)

-922 (Batch)

-923

-927

CICS connection authorization failure. This error code may mean that the plan/transaction ID entry is missing from the RCT CICS tables. Another possibility is that a program compiled, but the DB2 Bind was not successful. In a batch DB2 program, this error could mean that an analyst tried to execute a program/plan that belongs to another analyst without having execute authority. The analyst who owns the plan needs to go to the Source Control system, enter the analyst's ATS ID (e.g. ADP033) in the execute authorities list on the plan record and bind the plan. This will grant the execute authority for the plan. Another possibility is that a program compiled, but the DB2 Bind was not successful. Check the bind of the program being executed. If it is an existing plan on which a bind has not been done recently, perform a bind on the plan and check the bind report for any errors. Use the Display Database function of the DB2 Information System to verify the status of the tables and indexes that are being accessed by the plan that is getting the error. This error usually means that the JCL being used to execute a DB2 program is coded for a non-DB2 program. Verify that the program is defined as DB2 on the Source Control system and regenerate the JCL.

Common ABEND Codes The following are a list of some common abend codes that might be encountered when working with DB2 programs. Listed with the common abends are some Possible reasons and Possible solutions. See a member of the Data Administration team for the “DSNC” and “04E” errors otherwise see the appropriate member of the Systems team. S737-24

AEY9 SC03

DSNC

S047

S04E

ASPE

This abend usually occurs in a Drop/Create step of a procedure. It indicates that one of the PDS members listed under SYSIN does not exist or that the PDS member named can not be found. Be sure that JPLFILE, the Dictionary 'PP' and/or 'PT' functions have been executed successfully against the objects that the batch job is trying to reference. CICS abend error code. May indicate the program compiled but the DB2 Bind was not successful. Abend with return code = 004. This indicates a missing close statement for a non-DB2 file in a COBOL program. This is a CICS Call Attach abend and it could be caused by one or more numeric variables used in SQL UPDATE or INSERT statements not being initialized to zeros before the SQL statement is executed (similar to a COBOL S0C7). Another possible cause was that a user was executing the CICS system when an analyst performed a bind on the plan. The user's transaction was abended. 'Problem Program Attributes' message - The STEPLIB needs to be used instead of a JOBLIB for DSNT.DSNLOAD and DSNA.DSNLOAD libraries. In batch, a possible error is that a decimal packed field contained non-numeric data when an INSERT or an UPDATE SQL statement was executed. Another possibility is that an SQL WHERE clause in a cursor contains a packed numeric working storage field that contains non-numeric data when the OPEN CURSOR statement is executed. Initialize the field or remove it from the SQL statement if not used. A 'CICS SYNCPOINT ROLLBACK' statement was executed after a -922 SQL code was encountered in a CICS/DB2 update program. The CICS RCT table had not been updated with the transaction Id, and the dynamic transaction backout parameter had not been set. Have a CICS Support analyst verify the entries in the CICS RCT table and correct if necessary.

Iowa State University ADP Center DB2 Reference Handbook

59

Batch Processing General Information Performing an operation on a program, plan, or table will have a direct impact on anyone currently using any one of the objects. Monthly, the DB2 RUNSTATS utility is executed against every production DB2 table. DB2 Catalog statistics are collected to determine if tables and/or indexes require reorganization. If a table/index is targeted as a candidate for reorganization, a E-MAIL message is sent to the analyst in charge of the object. If a REORG or an UNLOAD/LOAD is currently scheduled to take place against the table/index within a week of receiving the EMAIL message, then no other steps are required. If not, follow the instructions provided in the E-MAIL message to schedule a reorganization. REORG procedures should be scheduled as soon as possible after notification to reduce client costs and increase performance. If a DB2 table is dropped/created or reorganized, a full image copy is required before any updates to the table can be made. SQL selects can be performed against the table but not updates, inserts, or deletes. The image copy sets a point of consistency for DB2 recovery. The Image Copy step is automatically generated by the JPL system. It is not necessary to perform a DROP/CREATE if the desired result is to delete all of the rows in the table. Just execute a LOAD with a dummy input for the LOAD dataset. The first thing that occurs when the LOAD utility is started is that all of the rows in the table (and its indexes) are deleted.

Limits on DB2 Internal Sort If a SORT is encountered, be aware that this is acceptable up to approximately 8,000,000 bytes (e.g. 20,000 rows with a 400 LRECL). This can be calculated by adding up the lengths of the columns in the SQL statement that has the sort in the bind report. Determine the number of rows that will be returned by looking at the selection criteria in the WHERE clause. Multiply the number of rows returned times the LRECL to determine the number of bytes that will be sorted. If the limit is exceeded, the solution is to code a declare cursor in the program to select the desired rows, but WITHOUT an Order By clause. Write the selected records to a file, and sort them in the next step using SyncSort.

Automatic Rebinds Currently a nightly procedure runs that selects all production DB2 application plans that have been invalidated for one reason or another. Usually, the invalidation of a plan or package is caused by a table being DROP/CREATED. This procedure performs a REBIND of the application plan and loads the EXPLAIN information into the analyst's plan_table that owns the plan. A E-MAIL message will be sent to each analyst who has one or more plans selected for REBIND. The REBIND is different from a BIND in that an EXPLAIN report is not automatically printed. Reviewing the report(s) will be the responsibility of the analyst. Use the test or production DB2 Information Systems to submit a job that will list the current test or production Explain report.

Iowa State University ADP Center DB2 Reference Handbook

60

Batch Abends If the batch program abends, the DB2 commit will not be performed. This means that DB2 will rollback all changes made to the data, restoring the table or tables back to the way they were before the task started. For batch, the task is defined as from the start of the job until the end of the job, or to the point of the last COMMIT statement execution. This feature can have some implications when working with both VSAM and DB2 tables/files in the same program. Information about the CHECKPOINT/RESTART routine can be found in the Program Development section of this handbook.

Iowa State University ADP Center DB2 Reference Handbook

61

DB2 Views A DB2 view is an alternative representation of data from one or more DB2 tables. A view can include all or some of the columns contained in tables on which it is defined. A view can also be used to restrict what rows can be accessed through the view. The owner (analyst), of a view as defined on the Source Control System, must have select authority on all tables in the view. Views can be setup by an analyst for use by clients using query products (NOT IN APPLICATION PROGRAMS). The view is an additional method to control client access to the DB2 data. Steps to create a DB2 View: 1.

2. 3.

4. 5. 6. 7.

8.

Use the ‘DT’ function of the Source Control system to add a new member. a. Naming convention is xxV999, xx=area, V=constant (for view), 999=sequential number b. Enter a view qualifier (must be a valid Data Dictionary keyword, e.g. RGSTN) c. Enter a view name (the client will use the qualifier.name combination to access data, it can’t be the same as an existing DB2 table or view). d. Set the type to ‘DB2 View’ In Test QMF, code and test the SQL SELECT statement. After verifying the SQL works correctly, add the following lines to the query: CREATE VIEW view_name AS (followed by your SELECT statement) Run the create view statement in test QMF just as you would run a query. You should receive a message indicating the database has been modified. Save the create view SQL statement as a query within QMF (e.g. SAVE QUERY AS DBV001) using the name that was entered in the Source Control system. Drop the view from test QMF (e.g. DROP VIEW TBL_INDX_STATS). You should receive a message indicating the database has been modified. In Source Control, check-in the view. Contact a DB2 System Administrator to add any view authorizations that are required and to build the view in test and/or production DB2. When the view is created by the DB2 System Administrator, the view name will consist of the view qualifier and the view name, which were entered on the ‘DT’ screen in Source Control. Example: DB2.TBL_INDX_STATS Contact a Database Administrator if you have questions or are ready to create a new view.

Examples: CREATE VIEW TBL_STATS_CURR AS SELECT NAME, CREATOR, DATE, NPAGES, NACTIVE, JPLFILE_CNT FROM DB2.TBL_STATS WHERE DATE = ’1994-01-01’

CREATE VIEW DEPT_SUMMARY (DEPTNAME, TOT_SALARY, TOT_EMPL) AS SELECT DEPTNAME, SUM(SALARY), COUNT(*) FROM Q.STAFF, Q.ORG WHERE DEPT = DEPTNUMB GROUP BY DEPTNAME • • • • •

Do not code any statement beyond column 72. When column names are included as part of the create view statement, as in the 2nd example, the column names must be the same as the column names from the table, unless the names are for derived columns. It is very important that the word ‘AS’ is on a separate line. The name of the view should the ‘View Name’ entered in the Source Control system. Do not use the view qualifier at this time.

Iowa State University ADP Center DB2 Reference Handbook

62

MS Query Client Education Any client who needs access to the DB2/MVS database using MS Query or predefined Excel spreadsheets, must attend the ATS MS Query course. The only exception to this is if the head of the department signs off with ATS, indicating that the client from that department does not need the course. The MS Query course currently is scheduled once a month. Billing note: Since clients are the only ones who can control their queries, it follows that ATS must bill them for any resources they use. ATS generally will not approve credit for invalid or excessively long queries.

MS Query Summary 1.

Do not leave Excel and MS-Query open when it is not in use. Together, Excel and MS-Query use a lot of resources. Remember: minimizing the application in Windows does NOT release the resources. Save your Excel spreadsheet and close the Excel application.

2.

MS-Query is designed to move smaller amounts of data from a database into Excel and Word applications. Only select the columns of data that you need. Extra columns of unnecessary data are a waste of time and resources.

3.

When using Excel and MS-Query, do one of the following steps as soon as possible after executing a query: • Return the data to Excel • Go to the bottom of the selected data • Exit the application if finished By doing one of these steps, DB2 locks are released from the data. The same locking concerns that exist in QMF, also are present when using MS Query. If one of the steps listed above is not taken, it is possible to lock out clients or other analysts from updating this data through CICS or batch

4.

To stop a query, press the ESC key twice. You can also stop a query by holding the ALT key and pressing the BREAK key at the same time.

5.

If a query is executing longer than 5 minutes, stop the query and contact your supporting analyst. Your analyst will determine what needs to be done to the SQL to speed up the query. A long-running query is very costly.

6.

QMF queries can be downloaded to MS-Query. Contact your supporting analyst if you need to use any existing QMF queries in MS-Query.

7.

Not all queries can be represented graphically, but as long as the SQL is syntactically correct, the SQL statement will execute in MS-Query. Also, some SQL functions will have to be entered in the SQL window since MS-Query cannot generate every feature of DB2 SQL.

MS Query - Excel - Returning DB2 Data from MS-Query and Passwords In Excel, be aware that a Save Password switch is set to on, when data is returned to Excel from MS-Query. If the password is saved as part of the spreadsheet, it can make the password accessible by others. To set the Save Password switch to off: • After clicking on the Return Data toolbar icon in MS-Query • The Excel spreadsheet is redisplayed with a window called Returning External Data to Microsoft Excel • In this window there is a button called Properties • Click on it and un-check the Save Password box • Click OK Iowa State University ADP Center DB2 Reference Handbook

63

•

Now click OK to return the data to Excel.

If the password is already saved as part of the spreadsheet, do the following to remove it: Open the spreadsheet in which you have saved the password with the query: • Click somewhere in the range of data from your query. • Select Data • Then Get External Data • Then Data Range Properties • Remove the check in front of Save Password and save your spreadsheet The next time you refresh the data or edit the query, you should be prompted for your password.

MS Query - Important Reminder for Users When accessing DB2 data with MS-Query, make sure to exit Excel and MS-Query, unless you are using the applications. After you return the data from MS-Query to your Excel spreadsheet, save the spreadsheet and close the Excel application. This is very important, because leaving the application open prevents others from using the resources. Minimizing the application in Windows does not release the resources. Also, when using Excel and MS-Query, do one of the following steps as soon as possible after executing a query: 1. 2. 3.

Return the data to Excel. Go to the bottom of the selected data. Exit the application if finished.

This closes the cursor to DB2 and releases all page locks. If one of these steps is not taken, portions of the database will contain locks that can prevent other applications from updating the locked data. This is especially important when queries are executed against production data, in that the locks can cause Production A CICS applications to receive “-911 Unavailable Resource” messages.

Iowa State University ADP Center DB2 Reference Handbook

64

QMF General Information It is important for all clients of QMF to get in and out as quickly as possible to reduce the number of slow CICS transaction response times or batch DB2 abends, due to DB2 resources being held during the QMF session. The following is a list of actions that can be taken to assure that the DB2 resources are not being held longer than necessary: •

• • •

Completion of the SQL statement • The query results fit on one Report screen • Enter the BOTTOM command on the Report screen and press ENTER • Using the PF8 key to page to the bottom of the results Correct response to the VERIFY prompt(s) Enter the RESET DATA command and press ENTER Exit QMF completely, not just exiting to another QMF screen

Use the QMF TABLE EDITOR in production only when necessary, and access only the required rows. If an SQL UPDATE or DELETE statement must be executed in production, code the WHERE clause to access a minimal number of rows. If necessary, execute the SQL statement multiple times, changing the WHERE clause for each execution to affect only small groups of rows. The following describes how QMF operates and when locking takes place: 1.

An SQL cursor is opened.

2.

Execution of the SQL statement is initiated.

3.

Some data PAGE(s) are locked until the cursor is closed. • SQL SELECT statements may display results before their completion and will lock data PAGE(s) containing the rows for subsequent result screens. The lock(s) will remain until one of the actions listed below is performed. • SQL INSERT, UPDATE and DELETE statements will lock any data PAGE that contains a row that is affected. The lock(s) are not released until the unit of work has been COMMITed by responding to the VERIFY prompt and its resulting screen has been displayed. • TABLE EDITOR locks any data PAGE(s) from which a row has been accessed. The PAGE(s) remain locked until the edit session is completed, the VERIFY prompt is responded to, and the resulting screen has been displayed.

4.

The SQL CURSOR is closed by one of the following actions: • Completion of the SQL statement • The query results fit on one Report screen • Enter the BOTTOM command on the Report screen and press ENTER • Using the PF8 key to page to the bottom of the results • Correct response to the VERIFY prompt(s) • Enter the RESET DATA command and press ENTER • Exit QMF completely, not just exiting to another QMF screen

Data PAGE(s) that are locked during QMF execution may have a direct affect on any CICS transaction or batch job that is currently trying to access the data on the locked PAGE(s). A CICS transaction may have either slow response times or be TIMED OUT due to the lock(s) held by the QMF session. Also a batch job may abend due to the unavailable resource.

Iowa State University ADP Center DB2 Reference Handbook

65

Canceling an Executing QMF Query Query An executing QMF query can be canceled by performing the following steps, if the TSO/QMF session was started under NetView: 1. 2. 3. 4. 5. 6.

7. 8. 9. 10. 10.

Press the RESET key. Use your Netview defined ESCAPE key to return to the Netview home panel. On the NetView Command line type: ATTN x (where x is the Netview ID of the TSO session that is executing the QMF query that you wish to cancel) then press ENTER. A message will be displayed: DSQ50465 QMF command interrupted! Clear screen and press enter Clear the screen and press the ENTER key again. A message will be displayed: DSQ50466 QMF command interrupted! Do one of the following: ===> To continue QMF command, type "CONT". ===> To cancel QMF command, type "CANCEL". ===> To enter QMF debug, type "DEBUG". Type the word CANCEL and press ENTER A message will be displayed: DSQ50469 OK, trying to cancel QMF command. Wait for three asterisks: *** Press the ENTER key, and the screen will return to the QMF panel. DO NOT ENTER THE "DEBUG" COMMAND!

Viewing a List of Saved QMF Objects QMF contains the facilities to list saved QMF objects (queries, forms and procs) for your user ID or someone else's user ID. Enter the command LIST ? on the command line and press <enter>. A prompt will display asking what type of objects to display. The prompt will also default to your user ID. If you wish to display another user ID's objects, just enter the other user ID in the prompt panel. After entering the information on the prompt panel, press <enter>. A list will be displayed with the names of the requested saved objects. If you changed the default user ID on the prompt panel, the list will only contain the save objects for that user Id, that have been saved as SHARE=YES.

Previous Production Explain Reports Previous versions of Explain reports (up to 5) are stored and are available for viewing using QMF or JFT in SYSD. In QMF, enter PREV_PLANS on the command line and press <enter>. A prompt will display asking which plan ID to display. In SYSD, a JFT proc has been setup to submit a job to list the previous plans. Choose option 8-JFT, then 1-DB2, then 1-Prev. A prompt will display asking which plan ID and which printer to direct the output to. Enter SUB on the command line to submit the job.

Explaining SQL Statements Using QMF A QMF procedure has be developed to EXPLAIN SQL statements using QMF. During the execution of this procedure the SQL statement is not actually executed and rows are not returned. Perform the following steps to use QMF to EXPLAIN an SQL statement: 1.

On the QMF SQL panel, enter the following statement above the actual SQL statement to be EXPLAINed: EXPLAIN PLAN SET QUERYNO = 99999 FOR Example: EXPLAIN PLAN SET QUERYNO = 99999 FOR SELECT ID, LAST_NAME, FIRST_NAME FROM ISU.STAFF WHERE DEPT IN ('001', '005', '021') AND STATE = ?

Iowa State University ADP Center DB2 Reference Handbook

66

Always set the 'QUERYNO =' value between 90001 and 99999. This value is used by the QMF procedure to extract the EXPLAIN information from the analysts plan table, display it, and then delete it from the plan table. If the SQL statement used a host variable in an application plan, change each host variable in the WHERE clause to a question mark (?) so that the DB2 optimizer will evaluate each question mark as if it were a host variable. This is important because DB2 evaluates host variables differently than literals. This way host variables don't have to be changed to literals for EXPLAIN purposes. 2.

Position the cursor on the COMMAND line and type EXPLAIN and then press ENTER.

3.

This procedure will execute and the EXPLAIN information will be displayed as a report. The report is very similar to the BIND EXPLAIN report that is generated after a successful compile of an application program. Also, information about this report can be found in the EXPLAIN section of this manual.

4.

After viewing the report, press the PF6 key to return to the SQL screen. The SQL statement can then be changed and EXPLAINed again by entering the command EXPLAIN and then pressing ENTER. This process can be repeated over and over until the desired results are achieved. A helpful tip: type a question mark (?) on the COMMAND line and press ENTER. This will return the last command entered so that is does not need to be retyped each time.

If an error is encountered, follow the instructions on the QMF screen to correct the error, and proceed.

QMF EXPORT DATA Command The EXPORT DATA exports data that is selected from a QMF query. This allows the data to be used by an application program. The BOTTOM command must be entered before exporting the data to be sure that all of the data has been retrieved from the table. The dataset name should be 'ADP0XX.TEST.XXXXXXXX' (quotes are required) where XXXXXXXX is an alpha/numeric name that starts with an alpha character. This dataset is cataloged upon completion of the EXPORT command. To determine the format, use SYSD to browse/edit the dataset that is created. Example:

EXPORT DATA TO 'ADP0XX.TEST.QMFDATA'

QMF SAVE Command Queries, Forms and Procs can be saved in the common QMF database tables for future use. Save only objects that will be used on an ongoing basis, not objects that are used only once. Type the following depending on the type of object to be saved: SAVE QUERY AS ? SAVE FORM AS ? SAVE PROC AS ? A QMF SAVE prompt panel will be displayed. Follow the instructions on the panel, but be sure to at least enter a date (MM/DD/YY) as the first piece of data on the COMMENT line of the panel. This is important for management purposes, as it can be used later with the LIST command to very easily determine the age of the object you have stored. Data can also be saved in the common QMF database tables. This common tablespace is small and ONLY temporary. Data can be saved, but it should be erased as soon as it is no longer needed. Save data is usually done in a QMF procedure to create a temporary table for the next SELECT statement. To delete the saved data an ERASE command should be entered in the QMF procedure immediately after the RUN QUERY statement that uses the saved data. An example of a QMF procedure using the SAVE DATA and ERASE DATA commands is as follows: RUN QUERY1 BOTTOM SAVE DATA AS QRY1DATA (CONFIRM=NO Iowa State University ADP Center DB2 Reference Handbook

67

RUN QUERY2 BOTTOM ERASE QRY1DATA (CONFIRM=NO DISPLAY REPORT

QMF - Printed Output ATS has implemented the QMF command synonym OUTPUT that will allow a QMF client to route QMF report output to specific laser printers. After the query has executed, proceed to the bottom of the output report by executing the BOTTOM command or using PF8. After reaching the bottom, it is recommended that the FORM for the output be examined (PF9) to determine the current width of the report. Modify the report by manipulating the FORM until the report is 132 characters wide or less. Use the PF12 key to return to the report. Enter the command OUTPUT on the command line and press ENTER. This will display a prompt panel which has some options for printing the output. After selecting the desired options, press ENTER, and the report will be sent to the printer that was selected, unless the report is longer that 4000 lines. In this case the destination is overridden, and the output is sent automatically to the system printers in the computer room. A message will be displayed on the QMF report screen indicating the location of the printer that the report will be printed on.

QMF - Moving Objects between Production and Test If a query or proc is too large to efficiently use Cut and Paste functions, it is possible to move queries, forms, and procs between Test QMF and Production QMF using the Export/Import commands. EXPORT the member from one system, then sign on to the other system and IMPORT the object. The commands are as follows: EXPORT QUERY TO filename EXPORT FORM TO filename EXPORT PROC TO filename IMPORT QUERY FROM filename IMPORT FORM FROM filename IMPORT PROC FROM filename Filename should follow current ATS standards (e.g. DCBROTH.TEMP.QUERY.name).

Iowa State University ADP Center DB2 Reference Handbook

68

QMF Common Command Summary Command DISPLAY QUERY DISPLAY PROC DISPLAY FORM DISPLAY REPORT DISPLAY PROFILE DISPLAY objectname RESET QUERY RESET DATA BOTTOM TOP EDIT QUERY EDIT PROC DRAW tablename ? LIST ? LIST SHOW OUTPUT SAVE QUERY AS ? SAVE FORM AS ? SAVE PROC AS ? SAVE DATA AS ?

Description Display QUERY panel Display PROC panel Display FORM panel Display Current Report Display PROFILE panel Display a QMF/DB2 object (e.g. a saved query or form) Clear the QUERY panel Clear the REPORT panel and close the cursor which will release any DB2 locks on the data Go to bottom of the current panel Go to top of the current panel Enter the ISPF edit facility Enter the ISPF edit facility Have QMF generate an SQL statement which includes all of the columns of the table Retrieve the last command entered Generate a new list of saved objects Return to a previous LIST of saved objects Display a menu showing all of the QMF panels Display an option panel to print a report Display a panel to save the current query Display a panel to save the current form Display a panel to save the current proc Display a panel to save the current data

Iowa State University ADP Center DB2 Reference Handbook

69

QMF Edit Codes - ATS Defined ATS has developed a QMF routine to define additional edit codes to be used in the EDIT column of the QMF Form.Main panel. This allows character (DB2 CHAR and VARCHAR) data to be formatted in various ways for printed reports. Only one delimiter is allowed (if desired) in an edit code and it can be coded in either position 4 or 5. The default delimiter is a blank.

Generic edit codes: Vxxyy

Examples:

V x y

V9214 V925V123/ V73# V234 V56 V7

is a constant can be a number from 1 to 9 can be a number from 1 to 9, blank or a non-alpha character to be used as a delimiter XXXXXXXXX XX X XXXX XXXXXXXXX-XX-XXXXX X/XX/XXX XXXXXXX#XXX XX XXX XXXX XXXXX XXXXXX XXXXXXX

Specific edit codes: VDATE VDAT/ VFA VFAB VFASP VFASB VPH VPHA VPHA( VPO VPOB VSSN VSSNB

MM-DD-YYYY MM/DD/YYYY 999-99-99 999 99 99 999-99-99-99-9999 999 99 99 99 9999 999-9999 999-999-9999 (999) 999-9999 XX-XXXXX-XX XX XXXXX XX 999-99-9999 999 99 9999

(field data before edit: YYYYMMDD) (field data before edit: YYYYMMDD) Fund account Fund account with blanks Fund account section project Fund account section project with blanks Phone # without area code Phone # with area code Phone # with area code Purchase order # Purchase order # with blanks Social security # Social security # with blanks

Iowa State University ADP Center DB2 Reference Handbook

70

QMF Governor Information QMF contains a governor exit routine that can be activated to control the number of rows read and/or the amount of time spent on a query. The governor uses two QMF tables to control the resource usage. The first table contains a row for each TSO user ID that QMF limits will be setup for. The ID is assigned to a Resource Group. Multiple ID's can be assigned to a single Resource Group and to a second table that contains the specific limits for I/O and CPU usage for different Resource Groups. The following are the definitions for each of the 6 QMF Governor control options: SCOPE

TIMEPROMPT TIMELIMIT

TIMECHECK

ROWPROMPT

ROWLIMIT

Determines whether any governing will occur. A non-zero value (including null) inhibits all governing for the specified Resource Group. A value of zero allows governing, as determined by the other options. Specifies the amount of CPU time that can elapse on a governing cycle before a cancellation prompt. A null, zero, or negative value inhibits prompting. Specifies the amount of CPU time that can elapse on a governing cycle. When this value is reached, the command is unconditionally canceled. A null, zero, or negative value inhibits this type of cancellation. Specifies the maximum amount of time that can elapse between time checks for both cancellation prompting and command cancellation. Based on this value, the governor sets a timer at the start of each governing cycle. When the timer decrements to zero, the governor makes the timing checks appropriate to the TIMEPROMPT and TIMELIMIT options. A null, zero, or negative value inhibits all time based cancellations, regardless of the values of those options. Specifies the number of rows that can be retrieved for the current data item before a cancellation prompt. A null, zero, or negative value inhibits prompting. Specifies the maximum number of rows that can be fetched for the current data item. When this number is exceeded, the retrieval operation is canceled. A null, zero, or negative value inhibits this type of cancellation.

Iowa State University ADP Center DB2 Reference Handbook

71

QMF End Users - Contact Guidelines The following information establishes a set of procedures that an end client of QMF should follow should a problem arise while using QMF. The supporting analyst (you) should go over these guidelines with the end client to ensure that proper channels are used when situations arise. While using QMF, different situations may arise that require assistance from ATS staff. The following information is broken down by QMF Operational situations and system problems.

QMF Operational Situations: If during the course of executing a QMF query, the query executes longer than anticipated, does not return the desired results or a question arises about the proper coding of a query:

•

During Normal Working Hours: Contact the supporting analyst(s). If they are currently out of their office, determine the urgency of resolving the situation: Critical: Locate the supporting analyst to resolve the situation, even if it requires an interruption of a meeting. If none of your supporting analyst(s) are available, contact the ATS Help Center at 294-8034. Non-Critical: Leave a message with your supporting analyst(s) to contact you upon their return, to solve your situation.

•

After Normal Working Hours: Work out contact procedures with your supporting analyst(s).

System Problem: (All Hours) Examples of a system problem could be items such as: • Not being able to LOGON • Receiving a message stating that a "SYSTEM ERROR HAS OCCURRED" Contact the ATS Help Center at 294-8034. The ATS Help Center will contact anyone else on the ATS staff, if necessary, to resolve the situation. Give a complete description of the steps that were taken just prior to the situation and any screen messages displayed by the terminal at the time of the situation. The ATS Help Center will contact your supporting analyst, or any other ATS staff that are needed to resolve the situation.

Iowa State University ADP Center DB2 Reference Handbook

72

Batch QMF Processing Scenario: A client contacts an ATS analyst to get a one-time report. The analyst uses QMF and SQL (provided the data is in DB2 tables) to generate a laser printed report and delivers it to the client. Several days later the client contacts the analyst and indicates that he/she would like to receive the report on a weekly basis. In the past, the analyst had only two choices. The analyst had to remember to execute the SQL statement on the requested day (not a good choice), or the analyst had to take the SQL statement and code an application program, which then could be scheduled through the JPL system (better choice). Using the application program approach would turn an average SQL statement of 10-15 lines into a program with about 300 lines. Now, a third and better alternative is available. Why not take the SQL statement, which already exists, and just schedule it to be executed in the JPL system? Work to support this ability through ATS in-house systems has been completed and is ready to be used to satisfy these types of requests from clients. Only QMF queries and forms (not procs) are the objects the in-house systems support. Steps required to schedule an SQL query through the JPL system: 1.

Set up a program name on the Source Control System for the SQL query statement. Naming convention: xxQ999, where xx is the two-digit support area, and Q is in the third position followed by a three-digit sequence number. Example: DBQ001

2.

QMF forms are not required. If, however, you do have QMF forms associated with the QMF query, you will need to set up a program name on the Source Control System for each form. Naming convention: xxQ999F1, where the first six characters are the same as the program name assigned to the query; and F is in the seventh position and is followed by 1. Example: DBQ001F1. Note: Each form associated with a query must have the same first six-character program name. This is crucial for the successful application of a form to a query during the execution of the JPL procedure, and makes identifying the form associated with a query easier. Example: DBQ001 (SQL query) DBQ001F1 (form of the query)

3.

In TEST QMF, code, test, and save the QMF query that you want to schedule using the program name that was set up in the Source Control System. Use the following command to perform the save: SAVE QUERY AS xxQ999 (S=Y

4.

In TEST QMF, code, test, and save each QMF form that you want to use with the query, using the program names that were set up in the Source Control System. Use the following command to perform the save: SAVE FORM AS xxQ999F1 (S=Y

5.

In the Source Control System, check-in the query. The form will be checked in automatically for you.

6.

Copy the sample procedure 00-59-10 to an appropriate procedure number. Follow the instructions in the sample procedure to code the control cards correctly. Generate test JCL and test the procedure.

7.

After the testing phase is complete, the procedure is ready to be scheduled. Check-out of the query is to TEST QMF (the associated form is checked out automatically). If you have any questions, contact a DBA.

Iowa State University ADP Center DB2 Reference Handbook

73

Batch QMF Queries It is recommended that all batch queries that will be scheduled through the JPL system be Explained to verify that an efficient access path will be chosen during the execution of the query. Use the method "Explaining SQL Statements Using QMF." The generated Explain report has comments at the bottom to assist you in determining the efficiency of the SQL statement.

Batch QMF Query and JPL Reviewers should observe the following standards when reviewing QMF Queries: • •

All batch QMF queries must have comments at the beginning explaining the purpose of the query and explain the possible values for any input parameter. Align SQL clauses (SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING, UNION) in the first position on separate lines.

The QMF review process enables ATS staff to learn more about SQL by viewing examples and being aware of an alternative programming method for clients. As ATS gains experience using this option, the standards may be formalized and documented. Any comments and/or suggestions should be directed to a DBA..

Code the DB2 Tables Used in Batch QMF Query When coding a JPL procedure to execute a batch QMF query, be sure to code the DB2 tables that are used in the query, as INPUT DISK. This is the only way for the job to be sequenced properly in the job sequencing process.

Iowa State University ADP Center DB2 Reference Handbook

74

Changing DB2 Table Structures Altering a DB2 Table to Add Column(s) New columns can be added to a table by using the ‘AR’ function of the DB2 Information System. This will send an email request to the DBA Group. They will notify you after the table has been altered to include the new column(s). The column will be initialized as follows: CHAR VARCHAR DECIMAL INTEGER SMALLINT DATE TIME TIMESTAMP

Spaces A String of Length 0 Zeros Zeros Zeros 0001-01-01 00.00 0001-01-01-00.00.00.000000

Changing a DB2 Table(s) to Change a Column Name, Size or Format To change or drop columns, the existing DB2 table must be dropped and recreated in the new format. To provide complete backup and recovery of the data during this process three JPL procedures are required which perform the following steps: (Note: The first and third procedures should already exist.) 1.

Unload procedure (should already exist) • Unload the table using the DB2UNLD utility

2.

Convert procedure • Read the unloaded format sequential file into conversion program and write out the new format file which matches the new DB2 table structure

3.

Drop/Create, Load procedure (should already exist) • Drop/Create the DB2 table and indexes • Use Load utility to load the new sequential file into the DB2 table

Steps Required to Change DB2 Table Structures The following steps must be followed in order to ensure that all the components associated with the DB2 table are updated with the appropriate changes. Based on the type of change you will do either the ‘If Adding Column(s)’ or the ‘If Changing’ step.

TEST DB2 Data Dictionary • • •

Add the column(s) to the book(s) associated with the DB2 table. Have the new data name(s) reviewed and approved. Perform the 'PT' function to update the DECLGEN and the LOAD CONTROL members.

Iowa State University ADP Center DB2 Reference Handbook

75

•

Perform the 'PT' function to update the COBOL book if one exists. This also updates the Easytrieve book at the same time, if one exists.

• •

Change the LRECL for the DB2 table definition. Create a new file ID for the sequential backup with the new LRECL and BLKSIZE.

• •

Use the DB2 Information System ‘AR’ function to request the new column(s). A new file ID was added for the sequential backup so any procedures (except the unload procedure) that used the old sequential file ID must be changed to use the new sequential file ID.

• • • •

Run the Unload Procedure Run the Convert Procedure Run the Drop/Create, Load Procedure If a new file ID was added for the sequential backup so any procedures (except the unload procedure) that used the old sequential file ID must be changed to use the new sequential file ID.

•

If an application UNLOAD program exists, the UNLOAD program must be changed to account for the change in LRECL of the sequential backup if columns have been added, deleted or their names changed in the altered DB2 table. Change any program that selects the entire table or needs to access the new/changed column(s) Change any other program that uses the sequential backup file to account for the change in LRECL of the altered DB2 table.

• •

File Definition System

If Adding Column(s)

If Changing a Column Name, Size or Format

Application Program(s)

PRODUCTION DB2 Data Dictionary

• • •

Have the data name status changed from Development to PRODUCTION. Perform the 'PP' function to update the DECLGEN and the LOAD CONTROL members. Perform the 'PP' function to update the COBOL book if one exists. This also updates the Easytrieve book at the same time, if one exists.

•

Verify that a new sequential backup file ID has been setup to reflect the change in the LRECL of the altered DB2 table. Verify that the DB2 table definition has been changed to reflect the change in LRECL.

•

• •

File System

If Adding Column(s) Use the DB2 Information System ‘AR’ function to request the new column(s). A new file ID was added for the sequential backup so any procedures that used the old sequential file ID must be changed to use the new sequential file ID.

Iowa State University ADP Center DB2 Reference Handbook

76

• • • •

If Changing a Column Name, Size or Format Run the Unload Procedure Run the Convert Procedure Run the Drop/Create, Load Procedure If a new file ID was added for the sequential backup any procedures that used the old sequential file ID must be changed to use the new sequential file ID.

JPL Procedures

•

If a new sequential backup file ID was added, verify that ALL procedures using the old sequential file ID have been changed to use the new sequential file ID.

•

After testing any programs that were changed to access the new column(s), or that were changed to use the updated/new sequential backup file ID, check them in to production after the DBA Group notifies you that the table has been ALTERed.

Application Program(s)

Iowa State University ADP Center DB2 Reference Handbook

77

Changing DB2 Index Structures Change an Existing Index The following steps must be followed in order to ensure that all of the components associated with the DB2 index are updated with the appropriate changes.

TEST DB2 Check Existing Index Usage

• •

Have a DB2 System Administrator check which programs currently use this index Verify the impact of the index change on each program

•

Use ‘IA’ function to change the columns assigned to the existing index

•

Execute the ‘AC’ function to generate the create index SQL (or it will be automatically generated overnight)

• • •

Copy the sample procedure 00-37-69 to a new procedure number Change the DEFINE TBL-SPACE record in the first step to the new index Id Change the hard coded JCL to reflect the name of the DB2 index. This hard coded JCL will be setup for production DB2. The ‘DB2.’ prefix on the index name needs to be changed to reflect the creator Id associated with your table. For example: If your table name is EMPL.DAILY_QUOTA then the creator Id is EMPL. So the index name would be EMPL.EMB0011I, where EMB0011 is the index that is to be changed. The ‘I’ suffix is required on all indexes. Change the hard coded JCL line DSN=DB01.PROD.DB2.TABLEDEF(DDB9011I), replacing DBB9011I with the name of the index to be changed (example: EMB0011I) Change the IN-OUTPUT DISK record in the second step to the table Id for the index being changed

• •

• •

Data Dictionary File Definition System JPL Procedure

Test JCL

•

Generate test JCL for new procedure Change the hard coded JCL so that the ‘B’ in the third position of the index name is a ‘T’. This will need to be changed in two places in the first step. Execute the test JCL

• •

Change the SQL in programs that will be affected by this index change Compile/bind and check the Explain report to verify changed index usage

Application Program(s)

PRODUCTION DB2 •

• •

JPL Procedure Schedule the create index procedure for a non-prime time execution.

Auto Rebind When the index is drop/created, all DB2 plans/packages that use the index become invalid. A nightly job is executed to rebind these plans/packages.

Iowa State University ADP Center DB2 Reference Handbook

78

• •

Source Control The next day, check-in programs that required changes because of the index change Check the Explain report to verify changed index usage

Adding a New Index The following steps must be followed in order to ensure that all of the components associated with the DB2 index are updated with the appropriate changes.

TEST DB2 File Definition System

•

Create a new Index Definition

•

Use ‘IA’ function to assign DB2 table column names to the newly defined index

•

Execute the ‘AC’ function to generate the create index SQL (or it will be automatically generated overnight)

• • •

Copy the sample procedure 00-37-68 to a new procedure number Change the DEFINE TBL-SPACE record in the first step to the new index Id Change the IN-OUTPUT DISK record in the second step to the table Id for the new index

•

Generate test JCL for new procedure and execute it in test DB2

Data Dictionary File Definition System JPL Procedure

Test JCL

PRODUCTION DB2 •

JPL Procedure Schedule the create index procedure for a non-prime time execution

Iowa State University ADP Center DB2 Reference Handbook

79

Sample JCL To Execute Batch COBOL Programs - Test The JCL generators will generate the appropriate JCL to execute a COBOL DB2 program in a batch mode. The entries with notes in the following JCL statements, are the changes that are required to execute (RUN) the application program: //TWL5901A JOB 004800,LOGUE#33,MSGLEVEL=(1,1),MSGCLASS=X, //* TYPRUN=HOLD, //* RESTART=SSSSS#NN, // USER=ADP033, Note 1 // CLASS=8 //* //******************************************************************* //JOBLIB DD DSN=COB1.TESTLIB,DISP=SHR // DD DSN=COB1.LINKLIB,DISP=SHR // DD DSN=DSNT.DSNLOAD,DISP=SHR //DB995#01 EXEC PGM=IKJEFT01,DYNAMNBR=4,TIME=1 Note 2 //* COND=(0,LE) //SYSTSIN DD * DSN SYSTEM(DSNT) Note 3 RUN PROGRAM(DB995) PLAN(DB995) PARMS('0123') END //SYS007 DD DSN=WK83.DB30010,DISP=SHR Note 4 //SYS005 DD SYSOUT=* //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSOUT DD SYSOUT=* //CPXMRPTS DD SYSOUT=* //CPXCSL DD DSN=CAPX.LISTLIB,DISP=SHR //CPXTIMSV DD DSN=ADP033.PGMDB995,DISP=(NEW,DELETE), // UNIT=DISK,VOL=REF=ADPC.TESTVOL, // SPACE=(TRK,(30,30),RLSE) //CPXMOPTS DD * XCOUNT OFF /* //SYS029 DD DSN=ADPC.JP10000,DISP=SHR

NOTES: 1. The USER=ADPXXX parameter is added to execute the TSO batch program. The user must be the owner of the program(s) or have been granted EXECUTE authority of the programs for the job to execute. 2. This is the entry point program name for executing under TSO. 3. Control statements for the TSO program: DSN SYSTEM(DSNT) Indicates what version of DB2 (Test or Production). PROGRAM(DB995) Indicates what program to execute. PLAN(DB995) Indicates what plan to execute. PARMS('0123') Indicates the parms to use in the program. 4. The rest of the JCL is the same as that generated for COBOL programs. Note that there are no JCL statements for the DB2 TABLES.

Iowa State University ADP Center DB2 Reference Handbook

80

BATCH COBOL Programming Sample The following are samples of code used in Batch and CICS programs to execute DB2 SQL statements. These samples are not complete programs, but they do show the code that is necessary in the Working Storage and Procedure Division sections. Included are samples of the DECLARE CURSOR/FETCH, SELECT, UPDATE, INSERT, and DELETE SQL statements. The COBOL generators will generate the appropriate SQL code as stated earlier in this handbook, but these samples are included here for reference purposes only. ******************* * WORKING-STORAGE * ******************* 01 SYS-COUNTS 12 DBB5010-FT PIC S9(7) 12 DBB5010-UP PIC S9(7) 12 DBB5020-SL PIC S9(7) 12 DBB5020-IN PIC S9(7) 12 DBB5020-DL PIC S9(7) * 01 SQLCODES. 12 WS-SQLERR-MSG. 16 WS-SQLERR-MSG-LEN PIC S9(4) 16 WS-SQLERR-MSG-TEXT PIC X(80) 12 WS-SQLERR-MSG-TEXT-LEN PIC S9(9) 12 WS-SQLERR-SUB PIC S9(4) 12 WS-SQLERR-MAX-12 PIC S9(4) 12 WS-EDITED-SQLCODE PIC -9(9) 12 DBTS-SQLCODE PIC S9(9) 88 DBTS-SQLCODE-VALID-OPEN 88 DBTS-SQLCODE-VALID-FETCH 88 DBTS-SQLCODE-VALID-NOT-FOUND 88 DBTS-SQLCODE-VALID-EOF 88 DBTS-SQLCODE-VALID-INSERT 88 DBTS-SQLCODE-VALID-UPDATE 88 DBTS-SQLCODE-VALID-DELETE 88 DBTS-SQLCODE-INVALID-DUP-KEY 88 DBTS-SQLCODE-VALID-CLOSE 12 DBIS-SQLCODE PIC S9(9) 88 DBIS-SQLCODE-VALID-SELECT 88 DBIS-SQLCODE-VALID-NOT-FOUND 88 DBIS-SQLCODE-VALID-INSERT 88 DBIS-SQLCODE-VALID-UPDATE 88 DBIS-SQLCODE-VALID-DELETE 88 DBIS-SQLCODE-INVALID-DUP-KEY * EXEC SQL INCLUDE DBTSTATD END-EXEC. * EXEC SQL INCLUDE DBISTATD END-EXEC. * EXEC SQL INCLUDE SQLCA END-EXEC. * EXEC SQL DECLARE DBTS-CURSOR CURSOR FOR SELECT TBLSP_NAME, KBYTE_ALLOC, TBL_ACTV_PAGE_CNT, ROW_NEAR_HOME, ROW_FAR_HOME, TBL_PCT_ACTV, TBL_NAME,

Iowa State University ADP Center DB2 Reference Handbook

COMP-3. VALUE VALUE VALUE VALUE VALUE

+0. +0. +0. +0. +0.

VALUE +960 COMP. OCCURS 12 TIMES. VALUE +80 COMP. VALUE +0 COMP. VALUE +12 COMP. VALUE ZERO. VALUE +000. VALUE +000. VALUE +000. VALUE +100. VALUE +100. VALUE +000. VALUE +000. VALUE +000. VALUE -803. VALUE +000. VALUE +000. VALUE +000. VALUE +100. VALUE +000. VALUE +000. VALUE +000. VALUE -803.

81

FROM WHERE ORDER BY

TBL_ROW_CNT, TBL_ACTV_PAGE_PCT, DB_ID, OBJ_ID, TBLSP_ID, CRTR_ID, TBL_COL_CNT, CREATE_DATE, TRK_ALLOC, NUM_EXTNT DB2.TBL_STATS ###COLUMN-NAME####CONDITION##### ###COLUMN-NAME### ###COLUMN-NAME### ###COLUMN-NAME###

ASC, ASC, ASC

********************************************************************* * The COBOL generators generate this cursor with both the ORDER BY * and the FOR UPDATE OF statements. They are mutually exclusive. * The program can use one or the other, but not both at the same time. ********************************************************************* FOR UPDATE OF TBLSP_NAME, KBYTE_ALLOC, TBL_ACTV_PAGE_CNT, ROW_NEAR_HOME, ROW_FAR_HOME, TBL_PCT_ACTV, TBL_NAME, TBL_ROW_CNT, TBL_ACTV_PAGE_PCT, DB_ID, OBJ_ID, TBLSP_ID, CRTR_ID, TBL_COL_CNT, CREATE_DATE, TRK_ALLOC, NUM_EXTNT END-EXEC. * ********************** * PROCEDURE DIVISION * ********************** * 890-SP055-ABEND-RTN. PERFORM 898-DISPLAY-SQLERR-MSG THRU PERFORM 990-DISPLAY-SYSCOUNTS-RTN THRU PERFORM 999-CLOSE-FILES-RTN THRU PERFORM 891-CALL-SP055-RTN THRU 890-EXIT. EXIT. * 898-DISPLAY-SQLERR-MSG. IF SQLCODE NOT < 0 GO TO 898-EXIT. CALL 'DSNTIAR' USING SQLCA WS-SQLERR-MSG WS-SQLERR-MSG-TEXT-LEN. PERFORM 899-NEXT-SQLERR-LINE THRU VARYING WS-SQLERR-SUB FROM +1 BY +1 UNTIL WS-SQLERR-SUB > WS-SQLERR-MAX-12. 898-EXIT. EXIT. * 899-NEXT-SQLERR-LINE. IF WS-SQLERR-MSG-TEXT (WS-SQLERR-SUB) = SPACES

Iowa State University ADP Center DB2 Reference Handbook

898-EXIT. 990-EXIT. 999-EXIT. 891-EXIT.

899-EXIT

82

GO TO 899-EXIT. DISPLAY 'DB995-SQLERR-' WS-SQLERR-MSG-TEXT (WS-SQLERR-SUB) UPON CONSOLE. 899-EXIT. EXIT. * 901-OPEN-DBTS-CURSOR. EXEC SQL OPEN DBTS-CURSOR END-EXEC. MOVE SQLCODE

TO DBTS-SQLCODE WS-EDITED-SQLCODE.

IF DBTS-SQLCODE-VALID-OPEN NEXT SENTENCE ELSE DISPLAY 'DB997-INVALID-OPEN DBTS-DBB5010 - SQLCODE = WS-EDITED-SQLCODE ' *P901* ' UPON CONSOLE PERFORM 890-SP055-ABEND-RTN THRU 890-EXIT. 901-EXIT. EXIT.

'

* 910-FETCH-DBTS. EXEC SQL FETCH DBTS-CURSOR INTO :DBTSD-DB2-TBLSP-NAME, :DBTSD-DB2-KBYTE-ALLOC, :DBTSD-DB2-TBL-ACTV-PAGE-CNT, :DBTSD-DB2-ROW-NEAR-HOME, :DBTSD-DB2-ROW-FAR-HOME, :DBTSD-DB2-TBL-PCT-ACTV, :DBTSD-DB2-TBL-NAME, :DBTSD-DB2-TBL-ROW-CNT, :DBTSD-DB2-TBL-ACTV-PAGE-PCT, :DBTSD-DB2-DB-ID, :DBTSD-DB2-OBJ-ID, :DBTSD-DB2-TBLSP-ID, :DBTSD-DB2-CRTR-ID, :DBTSD-DB2-TBL-COL-CNT, :DBTSD-DB2-CREATE-DATE, :DBTSD-DB2-TRK-ALLOC, :DBTSD-DB2-NUM-EXTNT END-EXEC. MOVE SQLCODE TO DBTS-SQLCODE WS-EDITED-SQLCODE. IF DBTS-SQLCODE-VALID-EOF GO TO 910-EXIT. IF DBTS-SQLCODE-VALID-FETCH ADD +1 TO DBB5010-FT ELSE DISPLAY 'DB997-INVALID-FETCH DBTS-DBB5010 - SQLCODE = WS-EDITED-SQLCODE ' *P910* ' DBTSD-###COPY-BOOK-NAME#### UPON CONSOLE PERFORM 890-SP055-ABEND-RTN THRU 890-EXIT. 910-EXIT. EXIT.

'

* 912-SELECT-DBIS. EXEC SQL SELECT INDXSP_NAME, UNIQUE_INDX, INDX_CLSTR_IND, INDX_CLSTR_OPT, KBYTE_ALLOC, INDX_LEVEL, INDX_LEAF_PAGE_CNT, INDX_PAGE_SIZE,

Iowa State University ADP Center DB2 Reference Handbook

83

INTO

FROM WHERE END-EXEC. MOVE SQLCODE

INDX_FREE_PAGE, INDX_PCT_FRSPC, INDX_ROW_CNT, ROW_NEAR_HOME, ROW_FAR_HOME :DBISD-DB2-INDXSP-NAME, :DBISD-DB2-UNIQUE-INDX, :DBISD-DB2-INDX-CLSTR-IND, :DBISD-DB2-INDX-CLSTR-OPT, :DBISD-DB2-KBYTE-ALLOC, :DBISD-DB2-INDX-LEVEL, :DBISD-DB2-INDX-LEAF-PAGE-CNT, :DBISD-DB2-INDX-PAGE-SIZE, :DBISD-DB2-INDX-FREE-PAGE, :DBISD-DB2-INDX-PCT-FRSPC, :DBISD-DB2-INDX-ROW-CNT, :DBISD-DB2-ROW-NEAR-HOME, :DBISD-DB2-ROW-FAR-HOME DB2.INDX_STATS ###COLUMN-NAME####CONDITION##### TO DBIS-SQLCODE WS-EDITED-SQLCODE.

IF DBIS-SQLCODE-VALID-NOT-FOUND GO TO 912-EXIT. IF DBIS-SQLCODE-VALID-SELECT ADD +1 TO DBB5020-SL ELSE DISPLAY 'DB997-INVALID-SELECT DBIS-DBB5020 - SQLCODE = WS-EDITED-SQLCODE ' *P912* ' DBISD-###COPY-BOOK-NAME#### UPON CONSOLE PERFORM 890-SP055-ABEND-RTN THRU 890-EXIT. 912-EXIT. EXIT.

'

* * 952-UPDATE-DBTS. EXEC SQL UPDATE DB2.TBL_STATS SET TBLSP_NAME KBYTE_ALLOC TBL_ACTV_PAGE_CNT

= :DBTSD-DB2-TBLSP-NAME, = :DBTSD-DB2-KBYTE-ALLOC, = :DBTSD-DB2-TBL-ACTV-PAGE-CNT, ROW_NEAR_HOME = :DBTSD-DB2-ROW-NEAR-HOME, ROW_FAR_HOME = :DBTSD-DB2-ROW-FAR-HOME, TBL_PCT_ACTV = :DBTSD-DB2-TBL-PCT-ACTV, TBL_NAME = :DBTSD-DB2-TBL-NAME, TBL_ROW_CNT = :DBTSD-DB2-TBL-ROW-CNT, TBL_ACTV_PAGE_PCT = :DBTSD-DB2-TBL-ACTV-PAGE-PCT, DB_ID = :DBTSD-DB2-DB-ID, OBJ_ID = :DBTSD-DB2-OBJ-ID, TBLSP_ID = :DBTSD-DB2-TBLSP-ID, CRTR_ID = :DBTSD-DB2-CRTR-ID, TBL_COL_CNT = :DBTSD-DB2-TBL-COL-CNT, CREATE_DATE = :DBTSD-DB2-CREATE-DATE, TRK_ALLOC = :DBTSD-DB2-TRK-ALLOC, NUM_EXTNT = :DBTSD-DB2-NUM-EXTNT WHERE CURRENT OF DBTS-CURSOR END-EXEC. MOVE SQLCODE TO DBTS-SQLCODE WS-EDITED-SQLCODE. IF DBTS-SQLCODE-VALID-UPDATE ADD SQLERRD(3) TO DBB5010-UP ELSE DISPLAY 'DB997-INVALID-UPDATE DBTS-DBB5010 - SQLCODE = '

Iowa State University ADP Center DB2 Reference Handbook

84

WS-EDITED-SQLCODE ' *P952* ' DBTSD-###COPY-BOOK-NAME#### PERFORM 890-SP055-ABEND-RTN 952-EXIT. EXIT.

UPON CONSOLE THRU 890-EXIT.

* 953-UPDATE-DBTS. EXEC SQL UPDATE DB2.TBL_STATS SET TBLSP_NAME KBYTE_ALLOC TBL_ACTV_PAGE_CNT

WHERE END-EXEC. MOVE SQLCODE

= :DBTSD-DB2-TBLSP-NAME, = :DBTSD-DB2-KBYTE-ALLOC, = :DBTSD-DB2-TBL-ACTV-PAGE-CNT, ROW_NEAR_HOME = :DBTSD-DB2-ROW-NEAR-HOME, ROW_FAR_HOME = :DBTSD-DB2-ROW-FAR-HOME, TBL_PCT_ACTV = :DBTSD-DB2-TBL-PCT-ACTV, TBL_NAME = :DBTSD-DB2-TBL-NAME, TBL_ROW_CNT = :DBTSD-DB2-TBL-ROW-CNT, TBL_ACTV_PAGE_PCT = :DBTSD-DB2-TBL-ACTV-PAGE-PCT, DB_ID = :DBTSD-DB2-DB-ID, OBJ_ID = :DBTSD-DB2-OBJ-ID, TBLSP_ID = :DBTSD-DB2-TBLSP-ID, CRTR_ID = :DBTSD-DB2-CRTR-ID, TBL_COL_CNT = :DBTSD-DB2-TBL-COL-CNT, CREATE_DATE = :DBTSD-DB2-CREATE-DATE, TRK_ALLOC = :DBTSD-DB2-TRK-ALLOC, NUM_EXTNT = :DBTSD-DB2-NUM-EXTNT ###COLUMN-NAME####CONDITION##### TO DBTS-SQLCODE WS-EDITED-SQLCODE.

IF DBTS-SQLCODE-VALID-UPDATE ADD SQLERRD(3) TO DBB5010-UP ELSE DISPLAY 'DB997-INVALID-UPDATE DBTS-DBB5010 - SQLCODE = WS-EDITED-SQLCODE ' *P953* ' DBTSD-###COPY-BOOK-NAME#### UPON CONSOLE PERFORM 890-SP055-ABEND-RTN THRU 890-EXIT. 953-EXIT. EXIT.

'

* 956-INSERT-DBIS. EXEC SQL INSERT INTO DB2.INDX_STATS (INDXSP_NAME, UNIQUE_INDX, INDX_CLSTR_IND, INDX_CLSTR_OPT, KBYTE_ALLOC, INDX_LEVEL, INDX_LEAF_PAGE_CNT, INDX_PAGE_SIZE, INDX_FREE_PAGE, INDX_PCT_FRSPC, INDX_ROW_CNT, ROW_NEAR_HOME, ROW_FAR_HOME, INDX_LEAF_DSTNC, CRTR_ID, CREATE_DATE, TRK_ALLOC, NUM_EXTNT) VALUES (:DBISD-DB2-INDXSP-NAME, :DBISD-DB2-UNIQUE-INDX,

Iowa State University ADP Center DB2 Reference Handbook

85

:DBISD-DB2-INDX-CLSTR-IND, :DBISD-DB2-INDX-CLSTR-OPT, :DBISD-DB2-KBYTE-ALLOC, :DBISD-DB2-INDX-LEVEL, :DBISD-DB2-INDX-LEAF-PAGE-CNT, :DBISD-DB2-INDX-PAGE-SIZE, :DBISD-DB2-INDX-FREE-PAGE, :DBISD-DB2-INDX-PCT-FRSPC, :DBISD-DB2-INDX-ROW-CNT, :DBISD-DB2-ROW-NEAR-HOME, :DBISD-DB2-ROW-FAR-HOME, :DBISD-DB2-INDX-LEAF-DSTNC, :DBISD-DB2-CRTR-ID, :DBISD-DB2-CREATE-DATE, :DBISD-DB2-TRK-ALLOC, :DBISD-DB2-NUM-EXTNT) END-EXEC. MOVE SQLCODE

TO DBIS-SQLCODE WS-EDITED-SQLCODE.

IF DBIS-SQLCODE-VALID-INSERT ADD +1 TO DBB5020-IN ELSE DISPLAY 'DB997-INVALID-INSERT DBIS-DBB5020 - SQLCODE = WS-EDITED-SQLCODE ' *P956* ' DBISD-###COPY-BOOK-NAME#### UPON CONSOLE PERFORM 890-SP055-ABEND-RTN THRU 890-EXIT. 956-EXIT. EXIT.

'

* 958-DELETE-DBTS. EXEC SQL DELETE FROM DB2.TBL_STATS WHERE CURRENT OF DBTS-CURSOR END-EXEC. MOVE SQLCODE TO DBTS-SQLCODE WS-EDITED-SQLCODE. IF DBIS-SQLCODE-VALID-DELETE ADD SQLERRD(3) TO DBB5010-DL ELSE DISPLAY 'DB997-INVALID-DELETE DBTS-DBB5010 - SQLCODE = WS-EDITED-SQLCODE ' *P958* ' DBTSD-###COPY-BOOK-NAME#### UPON CONSOLE PERFORM 890-SP055-ABEND-RTN THRU 890-EXIT. 958-EXIT. EXIT.

'

* * 960-DELETE-DBIS. EXEC SQL DELETE FROM DB2.INDX_STATS WHERE ###COLUMN-NAME####CONDITION##### END-EXEC. MOVE SQLCODE TO DBIS-SQLCODE WS-EDITED-SQLCODE. IF DBIS-SQLCODE-VALID-DELETE ADD SQLERRD(3) TO DBB5020-DL ELSE DISPLAY 'DB997-INVALID-DELETE DBIS-DBB5020 - SQLCODE = WS-EDITED-SQLCODE ' *P960* ' DBISD-###COPY-BOOK-NAME#### UPON CONSOLE PERFORM 890-SP055-ABEND-RTN THRU 890-EXIT. 960-EXIT. EXIT.

'

* 990-DISPLAY-SYSCOUNTS-RTN. DISPLAY 'DB997 DBB5010 = ' DBB5010-FT '-FT DBB5010-IN '-IN

Iowa State University ADP Center DB2 Reference Handbook

' '

86

DBB5010-UP DBB5010-DL DISPLAY 'DB997 DBB5020 = ' DBB5020-SL DBB5020-IN DBB5020-DL 990-EXIT. EXIT.

'-UP '-DL '-SL '-IN '-DL

' ' UPON CONSOLE. ' ' ' UPON CONSOLE.

* 991-CLOSE-DBTS-CURSOR. EXEC SQL CLOSE DBTS-CURSOR END-EXEC. MOVE SQLCODE

TO DBTS-SQLCODE WS-EDITED-SQLCODE.

IF DBTS-SQLCODE-VALID-CLOSE NEXT SENTENCE ELSE DISPLAY 'DB997-INVALID-CLOSE DBTS-DBB5010 - SQLCODE = WS-EDITED-SQLCODE ' *P991* ' UPON CONSOLE PERFORM 891-CALL-SP055-RTN THRU 891-EXIT. 991-EXIT. EXIT.

Iowa State University ADP Center DB2 Reference Handbook

'

87

CICS COBOL Programming Sample - BROWSE Program: This is an example of the CICS/DB2 browse logic using a multiple column index. Refer to the comments located above the WHERE clause of the DECLARE CURSOR statement. ******************* * WORKING-STORAGE * ******************* * 12 WSWA-SQL-EXEC-CTR PIC S9(4) VALUE +0 COMP. 12 WSWA-SQLCODE PIC S9(9) VALUE +0. 12 FILLER REDEFINES WSWA-SQLCODE. 16 FILLER PIC X(6). 16 WSWA-SQLCODE-SHORT PIC S999. 12 WSWA-SQLCODE-ERROR-MSG. 16 WSWA-SQL-ERROR-CONDITION. 20 FILLER PIC X(8) VALUE 'SQLCODE='. 20 WSWA-SQLCODE-DISPLAY PIC -999 VALUE ZERO. 16 FILLER PIC X VALUE SPACES. 16 WSWA-PARAGRAPH-NUMBER PIC XXX VALUE SPACES. 16 FILLER PIC X VALUE '-'. 16 WSWA-TABLE-TYPE-ACCESS PIC X(7) VALUE SPACES. 16 FILLER PIC XX VALUE SPACES. 16 WSWA-TABLE-NAME PIC X(7) VALUE SPACES. 12 WSWA-ACTION-MSG. 16 FILLER PIC X(36) VALUE SPACES. * * 01 SQLCODES. 12 DBTS-SQLCODE PIC S999 VALUE +000. 88 DBTS-SQLCODE-VALID-OPEN VALUE +000. 88 DBTS-SQLCODE-VALID-FETCH VALUE +000. 88 DBTS-SQLCODE-VALID-SELECT VALUE +000. 88 DBTS-SQLCODE-VALID-NOT-FOUND VALUE +100. 88 DBTS-SQLCODE-VALID-EOF VALUE +100. 88 DBTS-SQLCODE-VALID-CLOSE VALUE +000. * EXEC SQL INCLUDE DBTSTATD END-EXEC. * EXEC SQL INCLUDE SQLCA END-EXEC. * EXEC SQL DECLARE DBTS-BROWSE-FWD CURSOR FOR SELECT DBTS.KBYTE_ALLOC, DBTS.TBL_ACTV_PAGE_CNT, DBTS.ROW_NEAR_HOME, DBTS.ROW_FAR_HOME, DBTS.TBL_PCT_ACTV, DBTS.TBL_NAME, DBTS.TBL_ROW_CNT, DBTS.TBL_ACTV_PAGE_PCT, DBTS.DB_ID, DBTS.OBJ_ID, DBTS.TBLSP_ID, DBTS.CRTR_ID, DBTS.TBL_COL_CNT FROM DB2.TBL_STATS DBTS * ******************************************************* * ** Review the information under PROGRAM Development ** * ** titled 'CICS Browse Logic' for a complete ** * ** description of the following coding technique **

Iowa State University ADP Center DB2 Reference Handbook

88

* *

** for CICS browse cursors. ** ******************************************************* WHERE DBTS.DB_NAME >= :WSWA-RK-DB_NAME

* AND

((DBTS.DB_NAME = AND DBTS.TBLSP_NAME = AND DBTS.CREATE_DATE = AND DBTS.CRTR_ID >=

:WSWA-RK-DB_NAME :WSWA-RK-TBLSP-NAME :WSWA-RK-CREATE-DATE :WSWA-RK-CRTR-ID)

* OR

(DBTS.DB_NAME AND DBTS.TBLSP_NAME AND DBTS.CREATE_DATE

= :WSWA-RK-DB_NAME = :WSWA-RK-TBLSP-NAME > :WSWA-RK-CREATE-DATE)

OR

(DBTS.DB_NAME AND DBTS.TBLSP_NAME

= :WSWA-RK-DB_NAME > :WSWA-RK-TBLSP-NAME)

OR

(DBTS.DB_NAME

*

* > :WSWA-RK-DB_NAME))

* ORDER BY DBTS.DB_NAME DBTS.TBLSP_NAME DBTS.CREATE_DATE DBTS.CRTR_ID OPTIMIZE FOR 1 ROW END-EXEC. ********************** * PROCEDURE DIVISION * ********************** EXEC SQL OPEN DBTS-BROWSE-FWD END-EXEC. MOVE SQLCODE

ASC, ASC, ASC, ASC

TO DBTS-SQLCODE WSWA-SQLCODE.

IF DBTS-SQLCODE-VALID-OPEN NEXT SENTENCE ELSE MOVE '200' TO WSWA-PARAGRAPH-NUMBER MOVE 'OPEN ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBTSTAT TO WSWA-TABLE-NAME MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. * * PERFORM 810-FETCH-NEXT-DBTSTAT

THRU 810-EXIT.

PERFORM 810-FETCH-NEXT-DBTSTAT

THRU 810-EXIT.

PERFORM 810-FETCH-NEXT-DBTSTAT .. .. ..

THRU 810-EXIT.

PERFORM 810-FETCH-NEXT-DBTSTAT PERFORM 830-END-OF-TABLE GO TO 800-SET-HIDDEN-KEY.

THRU 810-EXIT. THRU 830-EXIT.

* *

*

* 810-FETCH-NEXT-RECORD. EXEC SQL FETCH DBTS-BROWSE-FWD INTO :DBTSD-DB2-KBYTE-ALLOC, :DBTSD-DB2-TBL-ACTV-PAGE-CNT, :DBTSD-DB2-ROW-NEAR-HOME, :DBTSD-DB2-ROW-FAR-HOME, :DBTSD-DB2-TBL-PCT-ACTV, :DBTSD-DB2-TBL-NAME, :DBTSD-DB2-TBL-ROW-CNT, :DBTSD-DB2-TBL-ACTV-PAGE-PCT,

Iowa State University ADP Center DB2 Reference Handbook

89

:DBTSD-DB2-DB-ID, :DBTSD-DB2-OBJ-ID, :DBTSD-DB2-TBLSP-ID, :DBTSD-DB2-CRTR-ID, :DBTSD-DB2-TBL-COL-CNT END-EXEC. ADD +1 MOVE SQLCODE

TO WSWA-SQL-EXEC-CTR. TO DBTS-SQLCODE WSWA-SQLCODE.

IF DBTS-SQLCODE-VALID-EOF GO TO 830-END-OF-TABLE. IF DBTS-SQLCODE-VALID-SELECT NEXT SENTENCE ELSE MOVE '810' TO WSWA-PARAGRAPH-NUMBER MOVE 'FETCH ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBTSTAT TO WSWA-TABLE-NAME MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. IF SQLWARN1 = 'W' MOVE '810' TO WSWA-PARAGRAPH-NUMBER MOVE 'FETCH ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBTSTAT TO WSWA-TABLE-NAME MOVE 'SQLWARN1=W' TO WSWA-SQL-ERROR-CONDITION MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. IF SQLWARN3 = 'W' MOVE '810' TO WSWA-PARAGRAPH-NUMBER MOVE 'FETCH ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBTSTAT TO WSWA-TABLE-NAME MOVE 'SQLWARN3=W' TO WSWA-SQL-ERROR-CONDITION MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. 810-EXIT. EXIT. * 830-END-OF-TABLE. PERFORM 840-CLOSE-BROWSE-FWD-CURSOR THRU 840-EXIT. MOVE 'END OF TABLE' TO M02MSGRO. GO TO 800-SET-HIDDEN-KEY. * 840-CLOSE-BROWSE-FWD-CURSOR. EXEC SQL CLOSE DBTS-BROWSE-FWD END-EXEC. * MOVE SQLCODE

TO DBTS-SQLCODE WSWA-SQLCODE.

IF DBTS-SQLCODE-VALID-CLOSE NEXT SENTENCE ELSE MOVE '840' TO WSWA-PARAGRAPH-NUMBER MOVE 'CLOSE ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBTSTAT TO WSWA-TABLE-NAME MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. 840-EXIT. EXIT. * 895-MOVE-SQL-ERROR-MESSAGE. MOVE WSWA-SQLCODE-ERROR-MSG MOVE WSWA-ACTION-MSG MOVE WSWA-CURSOR-ON MOVE DFHBMASK

TO TO TO TO

M34MSGLO. M34MSGRO. M34FUNCL. WSWA-ATTRIBUTE.

Iowa State University ADP Center DB2 Reference Handbook

90

CICS COBOL Programming Sample - DISPLAY Program ******************* * WORKING-STORAGE * ******************* 12 WSWA-SQL-EXEC-CTR PIC S9(4) 12 WSWA-SQLCODE PIC S9(9) 12 FILLER REDEFINES WSWA-SQLCODE. 16 FILLER PIC X(6). 16 WSWA-SQLCODE-SHORT PIC S999. 12 WSWA-SQLCODE-ERROR-MSG. 16 WSWA-SQL-ERROR-CONDITION. 20 FILLER PIC X(8) 20 WSWA-SQLCODE-DISPLAY PIC -999 16 FILLER PIC X 16 WSWA-PARAGRAPH-NUMBER PIC XXX 16 FILLER PIC X 16 WSWA-TABLE-TYPE-ACCESS PIC X(7) 16 FILLER PIC XX 16 WSWA-TABLE-NAME PIC X(7) 12 WSWA-ACTION-MSG. 16 FILLER PIC X(36) * 01 SQLCODES. 12 DBTS-SQLCODE PIC S999 88 DBTS-SQLCODE-VALID-OPEN 88 DBTS-SQLCODE-VALID-FETCH 88 DBTS-SQLCODE-VALID-SELECT 88 DBTS-SQLCODE-VALID-NOT-FOUND 88 DBTS-SQLCODE-VALID-EOF 88 DBTS-SQLCODE-VALID-CLOSE * EXEC SQL INCLUDE DBTSTATD END-EXEC. * EXEC SQL INCLUDE SQLCA END-EXEC. * ********************** * PROCEDURE DIVISION * ********************** * EXEC SQL SELECT DBTS.TBLSP_NAME, DBTS.KBYTE_ALLOC, DBTS.TBL_ACTV_PAGE_CNT, DBTS.ROW_NEAR_HOME, DBTS.ROW_FAR_HOME, DBTS.TBL_PCT_ACTV, DBTS.TBL_NAME, DBTS.TBL_ROW_CNT, DBTS.TBL_ACTV_PAGE_PCT, DBTS.DB_ID, DBTS.OBJ_ID, DBTS.TBLSP_ID, DBTS.CRTR_ID, DBTS.TBL_COL_CNT, DBTS.CREATE_DATE, DBTS.TRK_ALLOC, DBTS.NUM_EXTNT INTO :DBTSD-DB2-TBLSP-NAME, :DBTSD-DB2-KBYTE-ALLOC, :DBTSD-DB2-TBL-ACTV-PAGE-CNT,

Iowa State University ADP Center DB2 Reference Handbook

VALUE +0 VALUE +0.

VALUE VALUE VALUE VALUE VALUE VALUE VALUE VALUE

COMP.

'SQLCODE='. ZERO. SPACES. SPACES. '-'. SPACES. SPACES. SPACES.

VALUE SPACES.

VALUE VALUE VALUE VALUE VALUE VALUE VALUE

+000. +000. +000. +000. +100. +100. +000.

91

FROM WHERE END-EXEC. ADD +1 MOVE SQLCODE

:DBTSD-DB2-ROW-NEAR-HOME, :DBTSD-DB2-ROW-FAR-HOME, :DBTSD-DB2-TBL-PCT-ACTV, :DBTSD-DB2-TBL-NAME, :DBTSD-DB2-TBL-ROW-CNT, :DBTSD-DB2-TBL-ACTV-PAGE-PCT, :DBTSD-DB2-DB-ID, :DBTSD-DB2-OBJ-ID, :DBTSD-DB2-TBLSP-ID, :DBTSD-DB2-CRTR-ID, :DBTSD-DB2-TBL-COL-CNT, :DBTSD-DB2-CREATE-DATE, :DBTSD-DB2-TRK-ALLOC, :DBTSD-DB2-NUM-EXTNT DB2.TBL_STATS DBTS DBTS.###COLUMN-NAME### = :WSWA-READ-KEY-DBTSTAT TO WSWA-SQL-EXEC-CTR. TO DBTS-SQLCODE WSWA-SQLCODE.

IF DBTS-SQLCODE-VALID-NOT-FOUND GO TO 110-RECORD-NOT-FOUND-DBTSTAT. IF DBTS-SQLCODE-VALID-SELECT NEXT SENTENCE ELSE MOVE '100' TO WSWA-PARAGRAPH-NUMBER MOVE 'SELECT ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBTSTAT TO WSWA-TABLE-NAME MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. IF SQLWARN1 = 'W' MOVE '100' TO WSWA-PARAGRAPH-NUMBER MOVE 'SELECT ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBTSTAT TO WSWA-TABLE-NAME MOVE 'SQLWARN1=W' TO WSWA-SQL-ERROR-CONDITION MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. IF SQLWARN3 = 'W' MOVE '100' TO WSWA-PARAGRAPH-NUMBER MOVE 'SELECT ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBTSTAT TO WSWA-TABLE-NAME MOVE 'SQLWARN3=W' TO WSWA-SQL-ERROR-CONDITION MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. * 895-MOVE-SQL-ERROR-MESSAGE. MOVE WSWA-SQLCODE-ERROR-MSG MOVE WSWA-ACTION-MSG MOVE WSWA-CURSOR-ON MOVE DFHBMASK PERFORM 890-SET-FIELDS-TO-ASKIP

TO TO TO TO

Iowa State University ADP Center DB2 Reference Handbook

M34MSGLO. M34MSGRO. M34FUNCL. WSWA-ATTRIBUTE. THRU 890-EXIT.

92

CICS COBOL Programming Sample - UPDATE Program: ******************* * WORKING-STORAGE * ******************* 12 WSWA-SQL-EXEC-CTR PIC 12 WSWA-SQLCODE PIC 12 FILLER REDEFINES WSWA-SQLCODE. 16 FILLER PIC 16 WSWA-SQLCODE-SHORT PIC 12 WSWA-SQLCODE-ERROR-MSG. 16 WSWA-SQL-ERROR-CONDITION. 20 FILLER PIC 20 WSWA-SQLCODE PIC 16 FILLER PIC 16 WSWA-PARAGRAPH-NUMBER PIC 16 FILLER PIC 16 WSWA-TABLE-TYPE-ACCESS PIC 16 FILLER PIC 16 WSWA-TABLE-NAME PIC 12 WSWA-ACTION-MSG. 16 FILLER PIC * 01 SQLCODES. 12 DBTS-SQLCODE PIC 88 DBIS-SQLCODE-VALID-OPEN 88 DBIS-SQLCODE-VALID-FETCH 88 DBIS-SQLCODE-VALID-SELECT 88 DBIS-SQLCODE-VALID-UPDATE 88 DBIS-SQLCODE-VALID-INSERT 88 DBIS-SQLCODE-VALID-DELETE 88 DBIS-SQLCODE-VALID-NOT-FOUND 88 DBIS-SQLCODE-VALID-EOF 88 DBIS-SQLCODE-VALID-CLOSE * EXEC SQL INCLUDE DBTSTATD END-EXEC. * EXEC SQL INCLUDE SQLCA END-EXEC. * ********************** * PROCEDURE DIVISION * ********************** EXEC SQL SELECT INDXSP_NAME, UNIQUE_INDX, INDX_CLSTR_IND, INDX_CLSTR_OPT, KBYTE_ALLOC, INDX_LEVEL, INDX_LEAF_PAGE_CNT, INDX_PAGE_SIZE, INDX_FREE_PAGE, INDX_PCT_FRSPC, INDX_ROW_CNT, ROW_NEAR_HOME, ROW_FAR_HOME, INDX_LEAF_DSTNC, CRTR_ID, CREATE_DATE, TRK_ALLOC, NUM_EXTNT

Iowa State University ADP Center DB2 Reference Handbook

S9(4) VALUE +0 S9(9) VALUE +0.

COMP.

X(6). S999.

X(8) -999 X XXX X X(7) XX X(7)

VALUE VALUE VALUE VALUE VALUE VALUE VALUE VALUE

'SQLCODE='. ZERO. SPACES. SPACES. '-'. SPACES. SPACES. SPACES.

X(36) VALUE SPACES.

S999

VALUE VALUE VALUE VALUE VALUE VALUE VALUE VALUE VALUE VALUE

+000. +000. +000. +000. +000. +000. +000. +100. +100. +000.

93

INTO

FROM WHERE END-EXEC. ADD +1 MOVE SQLCODE

:DBISD-DB2-INDXSP-NAME, :DBISD-DB2-UNIQUE-INDX, :DBISD-DB2-INDX-CLSTR-IND, :DBISD-DB2-INDX-CLSTR-OPT, :DBISD-DB2-KBYTE-ALLOC, :DBISD-DB2-INDX-LEVEL, :DBISD-DB2-INDX-LEAF-PAGE-CNT, :DBISD-DB2-INDX-PAGE-SIZE, :DBISD-DB2-INDX-FREE-PAGE, :DBISD-DB2-INDX-PCT-FRSPC, :DBISD-DB2-INDX-ROW-CNT, :DBISD-DB2-ROW-NEAR-HOME, :DBISD-DB2-ROW-FAR-HOME, :DBISD-DB2-INDX-LEAF-DSTNC, :DBISD-DB2-CRTR-ID, :DBISD-DB2-CREATE-DATE, :DBISD-DB2-TRK-ALLOC, :DBISD-DB2-NUM-EXTNT DB2.INDX_STATS ###COLUMN-NAME####CONDITION##### TO WSWA-SQL-EXEC-CTR. TO DBTS-SQLCODE WSWA-SQLCODE.

IF DBIS-SQLCODE-VALID-NOT-FOUND GO TO 110-RECORD-NOT-FOUND-DBISTAT. IF DBIS-SQLCODE-VALID-SELECT NEXT SENTENCE ELSE MOVE '100' TO WSWA-PARAGRAPH-NUMBER MOVE 'SELECT ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBISTAT TO WSWA-TABLE-NAME MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. IF SQLWARN1 = 'W' MOVE '100' TO WSWA-PARAGRAPH-NUMBER MOVE 'SELECT ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBISTAT TO WSWA-TABLE-NAME MOVE 'SQLWARN1=W' TO WSWA-SQL-ERROR-CONDITION MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. IF SQLWARN3 = 'W' MOVE '100' TO WSWA-PARAGRAPH-NUMBER MOVE 'SELECT ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBISTAT TO WSWA-TABLE-NAME MOVE 'SQLWARN3=W' TO WSWA-SQL-ERROR-CONDITION MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. * * EXEC SQL INSERT INTO DB2.INDX_STATS (INDXSP_NAME, UNIQUE_INDX, INDX_CLSTR_IND, INDX_CLSTR_OPT, KBYTE_ALLOC, INDX_LEVEL, INDX_LEAF_PAGE_CNT, INDX_PAGE_SIZE, INDX_FREE_PAGE, INDX_PCT_FRSPC, INDX_ROW_CNT, ROW_NEAR_HOME, ROW_FAR_HOME, INDX_LEAF_DSTNC, CRTR_ID,

Iowa State University ADP Center DB2 Reference Handbook

94

CREATE_DATE, TRK_ALLOC, NUM_EXTNT) VALUES (:DBISD-DB2-INDXSP-NAME, :DBISD-DB2-UNIQUE-INDX, :DBISD-DB2-INDX-CLSTR-IND, :DBISD-DB2-INDX-CLSTR-OPT, :DBISD-DB2-KBYTE-ALLOC, :DBISD-DB2-INDX-LEVEL, :DBISD-DB2-INDX-LEAF-PAGE-CNT, :DBISD-DB2-INDX-PAGE-SIZE, :DBISD-DB2-INDX-FREE-PAGE, :DBISD-DB2-INDX-PCT-FRSPC, :DBISD-DB2-INDX-ROW-CNT, :DBISD-DB2-ROW-NEAR-HOME, :DBISD-DB2-ROW-FAR-HOME, :DBISD-DB2-INDX-LEAF-DSTNC, :DBISD-DB2-CRTR-ID, :DBISD-DB2-CREATE-DATE, :DBISD-DB2-TRK-ALLOC, :DBISD-DB2-NUM-EXTNT) END-EXEC. ADD +1 MOVE SQLCODE

TO WSWA-SQL-EXEC-CTR. TO DBIS-SQLCODE WSWA-SQLCODE.

IF DBIS-SQLCODE-VALID-INSERT NEXT SENTENCE ELSE MOVE '800' TO WSWA-PARAGRAPH-NUMBER MOVE 'INSERT ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBISTAT TO WSWA-TABLE-NAME MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. IF SQLWARN1 = 'W' MOVE '800' TO WSWA-PARAGRAPH-NUMBER MOVE 'INSERT ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBISTAT TO WSWA-TABLE-NAME MOVE 'SQLWARN1=W' TO WSWA-SQL-ERROR-CONDITION MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. IF SQLWARN3 = 'W' MOVE '800' TO WSWA-PARAGRAPH-NUMBER MOVE 'INSERT ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBISTAT TO WSWA-TABLE-NAME MOVE 'SQLWARN3=W' TO WSWA-SQL-ERROR-CONDITION MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. * EXEC SQL UPDATE DB2.INDX_STATS SET INDXSP_NAME UNIQUE_INDX INDX_CLSTR_IND INDX_CLSTR_OPT KBYTE_ALLOC INDX_LEVEL INDX_LEAF_PAGE_CNT INDX_PAGE_SIZE INDX_FREE_PAGE INDX_PCT_FRSPC INDX_ROW_CNT ROW_NEAR_HOME ROW_FAR_HOME INDX_LEAF_DSTNC

= :DBISD-DB2-INDXSP-NAME, = :DBISD-DB2-UNIQUE-INDX, = :DBISD-DB2-INDX-CLSTR-IND, = :DBISD-DB2-INDX-CLSTR-OPT, = :DBISD-DB2-KBYTE-ALLOC, = :DBISD-DB2-INDX-LEVEL, = :DBISD-DB2-INDX-LEAF-PAGE-CNT, = :DBISD-DB2-INDX-PAGE-SIZE, = :DBISD-DB2-INDX-FREE-PAGE, = :DBISD-DB2-INDX-PCT-FRSPC, = :DBISD-DB2-INDX-ROW-CNT, = :DBISD-DB2-ROW-NEAR-HOME, = :DBISD-DB2-ROW-FAR-HOME, = :DBISD-DB2-INDX-LEAF-DSTNC,

Iowa State University ADP Center DB2 Reference Handbook

95

CRTR_ID = :DBISD-DB2-CRTR-ID, CREATE_DATE = :DBISD-DB2-CREATE-DATE, TRK_ALLOC = :DBISD-DB2-TRK-ALLOC, NUM_EXTNT = :DBISD-DB2-NUM-EXTNT WHERE ###COLUMN-NAME####CONDITION##### END-EXEC. ADD +1 TO WSWA-SQL-EXEC-CTR. MOVE SQLCODE TO DBIS-SQLCODE WSWA-SQLCODE. IF DBIS-SQLCODE-VALID-UDPATE NEXT SENTENCE ELSE MOVE '850' TO WSWA-PARAGRAPH-NUMBER MOVE 'UPDATE ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBISTAT TO WSWA-TABLE-NAME MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. IF SQLWARN1 = 'W' MOVE '850' TO WSWA-PARAGRAPH-NUMBER MOVE 'UPDATE ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBISTAT TO WSWA-TABLE-NAME MOVE 'SQLWARN1=W' TO WSWA-SQL-ERROR-CONDITION MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. IF SQLWARN3 = 'W' MOVE '850' TO WSWA-PARAGRAPH-NUMBER MOVE 'UPDATE ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBISTAT TO WSWA-TABLE-NAME MOVE 'SQLWARN3=W' TO WSWA-SQL-ERROR-CONDITION MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. * EXEC SQL DELETE FROM DB2.INDX_STATS WHERE ###COLUMN-NAME####CONDITION##### END-EXEC. ADD +1 TO WSWA-SQL-EXEC-CTR. MOVE SQLCODE TO DBIS-SQLCODE WSWA-SQLCODE. IF DBIS-SQLCODE-VALID-DELETE NEXT SENTENCE ELSE MOVE '900' TO WSWA-PARAGRAPH-NUMBER MOVE 'DELETE ' TO WSWA-TABLE-TYPE-ACCESS MOVE WSWA-TABLE-NAME-DBISTAT TO WSWA-TABLE-NAME MOVE 'CONTACT ADP ANALYST ' TO WSWA-ACTION-MSG GO TO 895-MOVE-SQL-ERROR-MESSAGE. * 895-MOVE-SQL-ERROR-MESSAGE. * EXEC CICS SYNCPOINT ROLLBACK END-EXEC. * MOVE WSWA-SQLCODE-ERROR-MSG MOVE WSWA-ACTION-MSG MOVE WSWA-CURSOR-ON MOVE DFHBMASK PERFORM 890-SET-FIELDS-TO-ASKIP

TO TO TO TO

Iowa State University ADP Center DB2 Reference Handbook

M34MSGLO. M34MSGRO. M34FUNCL. WSWA-ATTRIBUTE. THRU 890-EXIT.

96

SQL Warnings and SQL Error Code Expansion The SQLCA area of an application program is updated with a wealth of information after an SQL statement in the program is executed. Currently, most application programs are evaluating the SQLCODE field to verify the validity of the SQL statement execution. The field SQLERRD(3) is important in that it will contain the number of rows updated, inserted, or deleted by DB2 after an SQL statement is executed. Another SQLCA field that is important is SQLWARN0. If this field is set to 'W', this means that at least one of the SQL warning flags (SQLWARN1 through SQLWARN7) has also been set. The warning flags that could affect programs at ATS are as follows:

• •

SQLWARN1 - If 'W', at least one column's value was truncated when it was stored into a host variable. SQLWARN3 - If 'W', the number of host variables specified in the SQL statement is unequal to the number of columns in the table or view being operated on by the statement.

The CICS COBOL generator currently generates checks for the two warnings listed above. The batch COBOL generator has been changed to generate an IBM-supplied SQL error code routine that expands an SQL error code into the complete DB2 error message found in the back of this Handbook. This will be very useful in that a large number of the SQL error messages contain variables that are filled in and displayed when this error routine is executed. This should facilitate easier debugging when SQL errors are encountered. The code can also be added to any existing batch DB2 COBOL or batch DB2 Easytrieve Plus program. The following code will be generated into new batch DB2 COBOL programs: (Working storage under: 01 SQLCODES) 12

WS-SQLERR-MSG. 16 WS-SQLERR-MSG-LEN 16 WS-SQLERR-MSG-TEXT 12 WS-SQLERR-MSG-TEXT-LEN 12 WS-SQLERR-SUB 12 WS-SQLERR-MAX-12

PIC PIC PIC PIC PIC

S9(4) X(80) S9(9) S9(4) S9(4)

VALUE +960 COMP. OCCURS 12 TIMES. VALUE +80 COMP. VALUE +0 COMP. VALUE +12 COMP.

(Procedure Division in paragraph: 890-SP055-ABEND-RTN) PERFORM 898-DISPLAY-SQLERR-MSG

THRU 898-EXIT.

(Procedure Division - new paragraphs) 898-DISPLAY-SQLERR-MSG. IF SQLCODE NOT < 0 GO TO 898-EXIT. CALL 'DSNTIAR' USING SQLCA WS-SQLERR-MSG WS-SQLERR-MSG-TEXT-LEN. PERFORM 899-NEXT-SQLERR-LINE THRU 899-EXIT VARYING WS-SQL-ERR-SUB FROM +1 BY +1 UNTIL WS-SQLERR-SUB > WS-SQLERR-MAX-12. 898-EXIT. EXIT. 899-NEXT-SQLERR-LINE. IF WS-SQLERR-MSG-TEXT(WS-SQLERR-SUB) = SPACES GO TO 899-EXIT. DISPLAY 'XX999-SQLERR- 'WS-SQLERR-MSG-TEXT(WS-SQLERR-SUB) UPON CONSOLE. 899-EXIT. EXIT.

The coding technique will be the same for Easytrieve Plus, but just use the appropriate language syntax.

Appendix A - Example Tablespace/Index/Load Control Iowa State University ADP Center DB2 Reference Handbook

97

Statements The following statements are generated out of the File Definition System when the JPLFILE procedure is run against a DB2 table and DB2 index. The statements are stored in a PDS called DB01.PROD.DB2.TABLEDEF and are used in the first step of the DROP/CREATE/LOAD procedure. DROP TABLESPACE DBDB02P.DBB5010T; COMMIT; CREATE TABLESPACE DBB5010T IN DBDB02P LOCKSIZE ANY CLOSE NO SEGSIZE 32 USING STOGROUP SDB0072 PRIQTY 1200 SECQTY 1200 ERASE NO FREEPAGE 009 PCTFREE 10; COMMIT; CREATE UNIQUE INDEX DB2.DBB5011I ON DB2.TBL_STATS (TBLSP_NAME CREATE_DATE USING STOGROUP SDB0075 PRIQTY 600 SECQTY 600 CLOSE NO SUBPAGES 4 CLUSTER FREEPAGE 009 PCTFREE 10;

92112041 92112041 92112041 92112041 92112041 92112041 92112041 92112041 92112041 92112041 92112041 92112041 92112041

ASC, ASC)

LOAD DATA INDDN SYS001 REPLACE LOG NO DISCARDS 1 INTO TABLE DB2.TBL_STATS (TBLSP_NAME, KBYTE_ALLOC, TBL_ACTV_PAGE_CNT, ROW_NEAR_HOME, ROW_FAR_HOME, TBL_PCT_ACTV, TBL_NAME, TBL_ROW_CNT, TBL_ACTV_PAGE_PCT, DB_ID, OBJ_ID, TBLSP_ID, CRTR_ID, TBL_COL_CNT, CREATE_DATE, TRK_ALLOC, NUM_EXTNT, DB_NAME, TBL_ID, JPL_FILE_REC_CNT)

Iowa State University ADP Center DB2 Reference Handbook

92112041 92112041 92112041 92112041 92112041 92112041 92112041 92112041 92112041 92112041 92112041 92112041 92112041

89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012441 89012403

98

Appendix B - Example GRANT Statements The following statements are generated out of the File Definition System when the RG function is executed. The statements are stored in a PDS called DB01.PROD.DB2.GRANTLIB and are used in the first step of the DROP/CREATE/LOAD procedure. GRANT SELECT ON TABLE DB2.TBL_STATS TO ADP099; GRANT UPDATE ON TABLE DB2.TBL_STATS TO ADP099; GRANT INSERT ON TABLE DB2.TBL_STATS TO ADP099; GRANT DELETE ON TABLE DB2.TBL_STATS TO ADP099;

Iowa State University ADP Center DB2 Reference Handbook

99

Appendix C - Example Table Create Statements The following statements are generated out of the Data Dictionary System when the PT (for test) and PP (for production) functions are executed. The statements are stored in a PDS called DB01.PROD.DB2.TABLEDEF and are used in the first step of the DROP/ CREATE/LOAD procedure. CREATE TABLE DB2.TBL_STATS (TBLSP_NAME CHAR(8) KBYTE_ALLOC INTEGER TBL_ACTV_PAGE_CNT INTEGER ROW_NEAR_HOME INTEGER ROW_FAR_HOME INTEGER TBL_PCT_ACTV SMALLINT TBL_NAME CHAR(18) TBL_ROW_CNT INTEGER TBL_ACTV_PAGE_PCT SMALLINT DB_ID SMALLINT OBJ_ID SMALLINT TBLSP_ID SMALLINT CRTR_ID CHAR(8) TBL_COL_CNT SMALLINT CREATE_DATE CHAR(8) TRK_ALLOC SMALLINT NUM_EXTNT INTEGER DB_NAME CHAR(8) TBL_ID SMALLINT JPL_FILE_REC_CNT DECIMAL(9,0) IN DBDB02P.DBB5010T;

NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT

Iowa State University ADP Center DB2 Reference Handbook

NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

WITH WITH WITH WITH WITH WITH WITH WITH WITH WITH WITH WITH WITH WITH WITH WITH WITH WITH WITH WITH

89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT,89012441 DEFAULT)89012403

100

Appendix D - DB2 Example Output from LOAD Utility The following messages are the result of running the LOAD utility. DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = P0005911.DSNLO02 DSNUGUTC - LOAD DATA INDDN SYS001 REPLACE LOG NO DISCARDS 1 DSNURWI INTO TABLE ADP.SRC_TITLE DSNURWI (PGRMR_NUM POSITION(1), DSNURWI PROGRAM_NAME POSITION(9), DSNURWI TYPE_REC POSITION(11), DSNURWI PGRMR_INIT POSITION(15), DSNURWI PROGRAM_STATUS POSITION(18), DSNURWI STATUS_CHNG_DATE POSITION(19), DSNURWI DATE_INCLUDED POSITION(27), DSNURWI TYPE_LANG POSITION(35), DSNURWI PROGRAM_TYPE POSITION(42), DSNURWI SOURCE_STMTS POSITION(43), DSNURWI LAST_YEAR_USED POSITION(48), DSNURWI LAST_MONTH_USED POSITION(50), DSNURWI SYS_NUM POSITION(52), DSNURWI LAST_JOB_USED POSITION(65), DSNURWI SPURT_MOVE_DATE POSITION(71), DSNURWI TEAM_NUM POSITION(79), DSNURWI TEAM_LEADER_IND POSITION(82), DSNURWI PROGRAM_DESCR POSITION(85)) DSNURRST - EXISTING RECORDS DELETED FROM TABLESPACE DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=12690 FOR TABLE ADP.SRC_TITLE DSNURILD - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=12690 DSNURILD - (RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:02:10 DSNUGSOR - SORT PHASE STATISTICS NUMBER OF RECORDS=25380 ELAPSED TIME=00:00:34 DSNURBXA - BUILD PHASE STATISTICS - NUMBER OF KEYS=12690 FOR INDEX ADP.SSB1001I DSNURBXA - BUILD PHASE STATISTICS - NUMBER OF KEYS=12690 FOR INDEX ADP.SSB1002I DSNURBXD - BUILD PHASE STATISTICS - NUMBER OF INDEXES=2 DSNURBXD - BUILD PHASE COMPLETE, ELAPSED TIME=00:00:23 DSNUGSRX - IMAGE COPY REQUIRED FOR TABLESPACE= SSB1000T DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=4

Iowa State University ADP Center DB2 Reference Handbook

101

Appendix E - DB2 Example Output from IMAGE COPY and RUNSTATS Utilities The following messages are the result of running the COPY and RUNSTATS utility statements. DSNU000I DSNU050I DSNU400I

DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = P0005910.DSNCO03 DSNUGUTC - COPY TABLESPACE DBDB01P.DBB5010T FULL YES COPYDDN SYS001 DSNUBIC0 - IMAGE COPY COMPLETE NUMBER OF PAGES=331 AVERAGE PERCENT FREE SPACE PER PAGE = 14.29 PERCENT OF CHANGED PAGES = 0.33 ELAPSED TIME=00:00:02 DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0 DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = P0005910.DSNRU02 DSNU050I DSNUGUTC - RUNSTATS TABLESPACE DBDB01P.DBB5010T INDEX(ALL) DSNU610I - DSNUSUTP - SYSTABLEPART CATALOG UPDATE FOR DBDB01P.DBB5010T SUCCESSFU DSNU610I - DSNUSUTS - SYSTABLESPACE CATALOG UPDATE FOR DBDB01P.DBB5010T SUCCESSF DSNU610I - DSNUSUTB SYSTABLES CATALOG UPDATE FOR DB2.TBL_STATS SUCCESSFUL DSNU610I - DSNUSUIP - SYSINDEXPART CATALOG UPDATE FOR DB2.DBB5011I SUCCESSFUL DSNU610I - DSNUSUIX SYSINDEXES CATALOG UPDATE FOR DB2.DBB5011I SUCCESSFUL DSNU610I - DSNUSUCO SYSCOLUMNS CATALOG UPDATE FOR DB2.DBB5011I SUCCESSFUL DSNU610I - DSNUSUIP - SYSINDEXPART CATALOG UPDATE FOR DB2.DBB5012I SUCCESSFUL DSNU610I - DSNUSUIX SYSINDEXES CATALOG UPDATE FOR DB2.DBB5012I SUCCESSFUL DSNU610I - DSNUSUCO SYSCOLUMNS CATALOG UPDATE FOR DB2.DBB5012I SUCCESSFUL DSNU610I - DSNUSUFL SYSFIELDS CATALOG UPDATE FOR DB2.DBB5012I SUCCESSFUL DSNU381I - DSNUGSRX - IMAGE COPY REQUIRED FOR TABLESPACE= DBB5010T DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=4 ************************************************************************

Iowa State University ADP Center DB2 Reference Handbook

102

Appendix F - DB2 Example Output from REORG and QUIESCE Utilities The following messages are the result of running the REORG and QUIESCE utility statements. DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = TWLREORT.DSNRE01 DSNUGUTC - REORG TABLESPACE DBDB02T.DBT5010T LOG NO UNLDDN SYS001 DSNURFIT - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS=414 FOR DBDB02T.DBT5010T DSNURULD - UNLOAD PHASE COMPLETE, ELAPSED TIME=00:00:04 DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=414 FOR DB2.TBL_STATS DSNURILD - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=4144 DSNURILD - (RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:00:03 DSNUGSOR - SORT PHASE STATISTICS NUMBER OF RECORDS=8288 ELAPSED TIME=00:00:01 DSNURBXA - BUILD PHASE STATISTICS - NUMBER OF KEYS=414 FOR INDEX DB2.DBT5011I DSNURBXA - BUILD PHASE STATISTICS - NUMBER OF KEYS=414 FOR INDEX DB2.DBT5012I DSNURBXD - BUILD PHASE STATISTICS - NUMBER OF INDEXES=2 DSNURBXD - BUILD PHASE COMPLETE, ELAPSED TIME=00:00:04 DSNUGSRX - IMAGE COPY REQUIRED FOR TABLESPACE= DBT5010T DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=4 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = P0005918.DSNQU01 DSNUGUTC - QUIESCE TABLESPACE DBDB02P.DBB5010T TABLESPACE DBDB02P.DBB5020T DSNUQUIA - QUIESCE SUCCESSFUL FOR TABLESPACE DBDB02P.DBB5010T DSNUQUIA - QUIESCE SUCCESSFUL FOR TABLESPACE DBDB02P.DBB5020T DSNUQUIA - QUIESCE AT RBA 00095069845C DSNUQUIB - QUIESCE UTILITY COMPLETE, ELAPSED TIME= 00:00:00 DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

Iowa State University ADP Center DB2 Reference Handbook

103

INDEX Abend Codes - Common, 59 Access Methods, 45 Access Path, 27 Access Path Recommendations, 39, 41 Access Path Restrictions, 39, 41 Access Path Selection, 45 Adding New Index, 79 Altering a DB2 Table to Add Column(s), 75 Application Specifications, 7 AS CLAUSE TO NAME RESULT COLUMNS, 56 Authorization - Browse, 15 Authorization - Delete, 15 Authorization - Grant/Revoke, 15 Authorization - Scenarios, 22, 24 Authorization - Table, 15 Authorization - Update, 15 Automatic Bind, 37 Automatic Rebinds, 60 Backout - Batch Program, 61 Backout - CICS Transaction, 29 Backup File - Sequential, 16 Batch - Abends, 61 Batch - Access Evaluation, 8 Batch - Access Path Recommendations, 39, 42 Batch - Compiles/Binds, 38 Batch - DB2 Sort, 39, 42 Batch - Easytrieve Plus Hint, 36 Batch - Opening and Closing a Cursor, 30 Batch - Programming Guidelines, 29 Batch - QMF Processing, 73 Batch - QMF Queries, 74 Batch - QMF Query and JPL, 74 Bind - Automatic, 37 Browse - CICS Logic, 35 Browse - Previous Screen, 35 Browses - CICS - Backward, 35 Browses - CICS - Forward, 35 Call/Linked Programming Standards, 40 Called Programs, 22 Change an Existing Index, 78 Changing a DB2 Table(s) to Change a Column Name, Size or Format, 75 Changing DB2 Index Structures, 78 Changing DB2 Table Structures, 75, 78 Checklist - Production, 7 Checklist - Test, 7 Checkpoint/Restart Guideline, 30 CICS - Access Evaluation, 8 CICS - Access Path Recommendations, 39, 41 CICS - Browse Previous Screen, 8, 35 CICS - Compiles/Binds, 38 CICS - I/O Limits, 28 CICS - Programming Guidelines, 29 CICS - SQL Guidelines, 9 COBOL - INCLUDE vs COPY, 32 COBOL - PARM Usage, 32 COBOL - Program Generator, 30 COBOL II Batch, 40 Iowa State University ADP Center DB2 Reference Handbook

104

Code the DB2 Tables Used in Batch QMF Query, 74 Column Definition - CHAR, 31 Column Definition - DATE, 31 Column Definition - DECIMAL, 31 Column Definition - INTEGER, 31 Column Definition - SMALLINT, 31 Column Definition - TIME, 31 Column Definition – TIMESTAMP, 31 Column Definition - VARCHAR, 31 Conceptual Database Design Review, 9 Correlated Subqueries, 52 Data Dictionary, 17, 19, 75, 76 Database, 6 DB2, 6 DB2 - DATE Guidelines, 7 DB2 - NULL Guidelines, 7 DB2 - TIME Guidelines, 7 DB2 - TIMESTAMP Guidelines, 7 DB2 - VARCHAR Guidelines, 7 DB2 Authority, 22 DB2 Authorizations, 22 DB2 Authorizations - Changing, 24 DB2 Authorizations for ODBC Applications, 23 DB2 Execute Authorities, 23 DB2 Explain Report Reminder, 41 DB2 Internal Sort Limits, 60 DB2 Null Columns, 32 DB2 NULL Columns - Load/Unload Dataset Structure, 32 DB2 Objects, 6 DB2 Utilities, 22, 27 DB2 Views - Creating, 62 DBADM Analyst ID Change, 12 Disclaimer, 2 DSNTUNLV - Unload Utility Program, 25 Easytrieve Batch, 40 EXPLAIN - Access Path Determination, 45 EXPLAIN - How to Use, 42 EXPLAIN - Purpose, 42 EXPLAIN Information, 41 Explaining SQL Statements Using QMF, 66 FD Variables, 32 File Definition System, 11, 13, 15, 16, 76 General Information - Batch, 60 General Information - SQL, 46 Group Level Variables, 32 Host Variables, 28, 31 Implementing a Correlated Subquery, 52 Index, 6 Index - ASC/DESC Indicator, 14 Index - Cluster Ratio, 9 Index - CLUSTERING Guideline, 8 Index - Column Guidelines, 9 Index - Columns, 14 Index - Columns Updated, 9 Index - Definition, 8, 13 Index - Description, 13 Index - Freepage, 13 Index - Guideline Number Of, 8 Index - Key Length, 13 Index - Maximum Length, 9 Iowa State University ADP Center DB2 Reference Handbook

105

Index - Minimum Number Of, 8 Index - Name, 14 Index - Non-unique Columns, 9 Index - Page Size, 13 Index - Row Count, 13 Index - VARCHAR Columns, 9 Inner Join, 48 JCL, 27, 80 Join - Hybrid, 50 Join - Merge Scan, 50 Join - Methods, 48 Join - Nested Loop, 50 JPL, 25 JPLFILE, 12, 14 Limits on DB2 Internal Sort, 60 Linked CICS Programs, 40 Load Utility, 27 MS Query - Client Education, 63 MS Query - Excel 97 - Returning DB2 Data from MS-Query and Passwords, 63 MS Query - Important Reminder for Users, 64 MS Query - Summary, 63 Nested Table Expression With "AS CLAUSE, 56 Non-correlated Subqueries, 51 OUTER JOIN - FULL, 56 OUTER JOIN FULL With Using "COALESCE" Function, 57 OUTER JOIN - LEFT, 56 Outer Join Considerations, 49 Plan Table, 43 Preface, 2 Pre-Implementation Review, 9 Previous Explain Reports, 45 Previous Production EXPLAIN Reports, 66 Procedures (JPL) - Required, 25 Production Bind Using 'XP' Function in the Source Control, 39 Production Compiles/Binds, 38 Production DB2 - Altering, 76 Program Compile and Bind, 37 Program Development, 28 Programming Guidelines - General, 28 QMF, 22 QMF Access - Evaluation, 8 QMF Common Command Summary, 69 QMF Edit Codes - ATS Defined, 70 QMF End Users - Contact Guidelines, 72 QMF EXPORT DATA Command, 67 QMF General Information, 65 QMF Governor Information, 71 QMF List Objects, 66 QMF Operation Principal, 65 QMF Operational Situations:, 72 QMF Printed Output, 68 QMF Query - Canceling, 66 QMF SAVE Command, 67 QMF TABLE EDITOR, 65 Redefined Names, 32 Reorg Utility, 27 Return Codes, 58 Runstats Utility, 27 Sample JCL - Test - Batch, 80 Sample Program - Batch - COBOL Program, 81 Iowa State University ADP Center DB2 Reference Handbook

106

Sample Program - CICS - BROWSE, 88 Sample Program - CICS - DISPLAY, 91 Sample Program - CICS - Update, 93 Source Control - Changing OwnershipDB2 Reminder, 21 Source Control System, 21, 38 SQL - BETWEEN Clause, 28 SQL - CLOSE CURSOR, 31 SQL - DECLARE CURSOR/FETCH Statements, 46 SQL - DELETE Statement, 46 SQL - INSERT Statement, 46 SQL - OR Logic in WHERE Clause, 31 SQL - ORDER BY Clause, 31 SQL - ORDER BY Clause and Selected Columns, 31 SQL - ORDERBY Clause, 35 SQL - SELECT / UPDATE Columns, 29 SQL - SELECT INTO Statement, 46 SQL - UPDATE Statement, 36, 46 SQL - WHERE clause, 29, 31 SQL Avoid Coding Complex Statements, 28 SQL Error Return Codes and Possible Solutions, 58 SQL Examples, 56 SQL General Information, 46 SQL INSERT Statement, 31 SQL Join, 47 SQL Programming Standards, Guidelines and Restrictions, 28 SQL Return Code - Expansion, 97 SQL Return Code - Warnings, 97 SQL Statement - Restrictions, 32 SQL Statement - Types, 46 SQL Statement Format Standards, 28 SQL Statement Guidelines, 31 SQL Statement Usage using NULLS, 33 SQL Statements, 42 SQL Statements NOT Allowed in CICS application, 29 SQL Statements NOT Aloowed in Batch Programs, 29 SQL Subselect/Sub Query, 47 SQL Union, 47 SQLCA - SQL Common Area, 97 SQLERRD(3) - Count of Rows, 97 SQLWARN1, 97 SQLWARN3, 97 Storage Group, 6 Sub Queries, 51 SYSADM, 20 SYSD/RMDS, 38 System Design, 7, 9 System Development Checklist, 7 System Documentation, 10, 11 System Number, 11 Table, 6 Table - Avg Char/Row, 11 Table - Description, 11 Table - Freespace, 11 Table - Maximum Character / Row, 11 Table - Number of Rows, 11 Table - Page Pctfree, 11 Table - Page Size, 11 Table - Segsize, 12 Table Access, 22 Table Definition, 7, 11 Iowa State University ADP Center DB2 Reference Handbook

107

Table Design Guidelines, 7 Table space, 6 Test Bind Using 'XT' Function in the Source Control, 38 TEST DB2 - Altering, 75 Three Way Join, 57 Unload Utility, 27 Updating a Single DB2 Table, 30 Updating Multiple DB2 tables, 30 Using "AS CLAUSE" In ORDER BY Clause, 56 Using "AS CLAUSE" To Name Result Columns, 56 View, 6

Iowa State University ADP Center DB2 Reference Handbook

108