Learning Objectives
After completing this session, you will be able to:
Identify important bind parameters
Explain common SQL Error Codes
List the Application Programming Guidelines
Describe about EXPLAIN
Bind Parameters
Some important bind parameters are as follows:
MEMBER(&MEMBER.) -
QUALIFIER(NAME) -
ACTION(REPLACE) - RETAIN - VALIDATE(BIND) - ACQUIRE(USE) - RELEASE(COMMIT) -
ISOLATION(CS) - DEGREE(ANY) - EXPLAIN(YES)
MEMBER(&MEMBER.): Specifies the name of the DBRM to be bound to a plan.
QUALIFIER: Specifies an identifier to be used by the bind process to qualify all tables referenced by SQL statements in the DBRM being bound.
For example, the DSN8510.TB_DEPT table, is accessed if the following statement is embedded in a program bound to a plan specifying a QUALIFIER of DSN8510:
EXEC SQL
SELECT DEPT_NO, DEPT_NAME INTO :DEPT-NO, :DEPT-NAME FROM TB_DEPT
END-EXEC.
ACTION: You can specify two types of actions, namely ADD or REPLACE
o ADD indicates that the plan is new.
o REPLACE indicates that an old plan by the same name will be replaced.
o Specifying ACTION (REPLACE) for a new plan does not cause the bind to fail—it merely causes confusion.
RETAIN: Indicates that all bind and execute authority granted for this plan will be
VALIDATE: A validation strategy refers to the method of checking for the existence and validity of DB2 tables and DB2 access authorization.
o You can use two types of validation strategies: VALIDATE(BIND) or VALIDATE(RUN).
o VALIDATE(BIND), the preferred option, validates at bind time. If a table is invalid or proper access authority has not been granted, the bind fails.
o VALIDATE(RUN) validates DB2 table and security each time the plan is executed.
This capability is useful if a table is changed or authority is granted after the bind is issued. It does, however, impose potentially severe performance degradation because each SQL statement is validated each time it is executed.
ACQUIRE: You have seen this in detail in previous sessions. The options for the ACQUIRE parameter are USE and ALLOCATE.
o When you specify USE, tablespace locks are taken when the tablespace is accessed.
o With ALLOCATE, table space locks are taken when the plan is first allocated.
o The preferred options for online application is USE and for batch is ALLOCATE.
RELEASE: The options for RELEASE are COMMIT and DEALLOCATE.
o When you specify the COMMIT option, locks are released at commit time.
o When you specify DEALLOCATE, all locks are held until the plan finishes and is de-allocated.
o The preferred option for online application is COMMIT and for batch is DEALLOCATE.
ISOLATION: You have seen this in detail in previous sessions. The isolation level determines the mode of page / row locking implemented by the program as it runs.You can specify the following four isolation levels:
o Repeatable read (RR)
o Read stability (RS)
o Cursor stability (CS)
o Uncommitted read (UR)
DEGREE: When DEGREE(ANY) is specified, DB2 will attempt to execute queries using parallel engines whenever possible. Parallel queries are typically deployed against partitioned table spaces
EXPLAIN: EXPLAIN(YES) allows the proper monitoring of the access path selection made by DB2.
Common SQL Errors
Following list shows some of the common SQL error codes:
SQLCODE Description
-104 Illegal symbol encountered in SQL statement.
-118 Table or view is illegally named in both data modification clause (UPDATE or DELETE) and the FROM clause.
-181 Not a valid DATE, TIME, or TIMESTAMP value.
-204 Undefined object name.
-305 Null indicator variable is missing.
SQLCODE Description
-408 Value cannot be inserted or updated because it is incompatible with the column's data type.
-532 Deletion violates the named referential constraint.
-545 INSERT or UPDATE caused a check constraint violation.
-803 Cannot insert row because it would violate the constraints of a unique index.
-811 Must use a cursor when more than one row is returned as the result of an embedded select statement.
-818 Plan <—> load module timestamp mismatch. The DBRM in the executing plan was not created from the same precompilation as the load module.
-904 The specified resource is unavailable
-905 Resource limit has been exceeded.
-911 The current unit of work has been rolled back.
-913 Unsuccessful execution caused by either a deadlock or a timeout.
Application Programming Guidelines
Code modular DB2 programs and make them as small as possible
Use unqualified SQL statements; this enables movement from one environment to another (Test to Production)
Never use Select * in an embedded SQL program
Use Joins rather than subqueries
Use WHERE clause wherever possible and filter out data
Use cursors when fetching multiple rows, though they add overheads
Use FOR UPDATE OF clause for UPDATE or DELETE with cursor - this ensures data integrity.
Use INSERTs minimally; use LOAD utility instead of INSERT, if the inserts are not application dependent.
Using EXPLAIN
You can use the EXPLAIN feature to detail the access paths chosen by the DB2 optimizer for SQL statements. EXPLAIN should be a key component for the performance monitoring strategy. A single SQL statement or a series of SQL statements in a package or plan, can be the subject of an EXPLAIN.
When EXPLAIN is requested, the SQL statements are passed through the DB2 optimizer, and the following three activities are performed:
The access paths that DB2 chooses are externalized, in coded format, into a PLAN_TABLE.
Cost estimates for the SQL statements are formulated and inserted into a DSN_STATEMNT_TABLE.
The user-defined functions that will be used are placed into a DSN_FUNCTION_TABLE.
To EXPLAIN a single SQL statement, precede the SQL statement with the EXPLAIN command as follows:
EXPLAIN ALL SET QUERYNO = integer FOR SQL statement ;
It can be executed in the same way as any other SQL statement.
QUERYNO, which you can set to any integer, is used for identification in the PLAN_TABLE.
Example:
The following EXPLAIN statement populates the PLAN_TABLE with the access paths chosen for the indicated sample table query:
EXPLAIN ALL SET QUERYNO = 1 FOR SELECT FIRSTNME, MIDINIT, LASTNAME FROM DSN8610.EMP
WHERE EMPNO = '000240';
Another method of issuing an EXPLAIN is as a part of the BIND command.
If you indicate EXPLAIN(YES) when binding a package or a plan, DB2 externalizes the access paths chosen for all SQL statements in that DBRM (or DBRMs) to the PLAN_TABLE.
Summary
In this session you have seen the common bind parameters, common SQL error codes, application programming guidelines, and how to apply EXPLAIN.
Test Your Understanding
1. Explain about the different bind parameters.
2. Explain the common SQL error codes.
3. Explain the guidelines for application programming.
4. Describe about EXPLAIN.
Session 55: Miscellaneous
Learning Objectives
After completing this session, you will be able to:
Describe DB2 Catalog
Explain DB2 Directory Table-Based Infrastructure of DB2
DB2 has a set of tables that functions as a repository for all DB2 objects. These tables define the infrastructure of DB2, enabling simple detection of and access to DB2 objects.
Two sets of tables store all the data related to DB2 objects which are:
DB2 Catalog
DB2 Directory DB2 Catalog
The entire DBMS relies on the system catalog, or the DB2 Catalog.
If the DB2 optimizer is the heart and soul of DB2, then DB2 Catalog is its brain or memory.
The knowledge base of every object known to DB2 is stored in the DB2 Catalog.
Following list is a short description of each table in the DB2 Catalog:
Table Contents
IPNAMES To set up distributed TCP/IP connections
LOCATIONS Contains distributed location information for every accessible remote server
LULIST Contains the list of LUNAMEs for a given distributed location (when multiple LUNAMEs are associated with a single location)
LUMODES Information on distributed conversation limits
LUNAMES Contains information for every SNA client or server that communicates with the DB2 subsystem
MODESELECT Information assigning mode names to conversations supporting outgoing SQL requests
SYSAUXRELS Information on the auxiliary tables required for LOB columns SYSCHECKDEP Column references for CHECK constraints
SYSCHECKS CHECK constraint specifications
SYSCOLAUTH The UPDATE privileges held by DB2 users on table or view columns SYSCOLDIST The non-uniform distribution statistics for the 10 most frequently occurring
Table Contents SYSCOLSTATS The partition statistics for selected columns
SYSCOLUMNS Information about every column of every DB2 table and view
SYSCONSTDEP Information regarding columns that are dependent on check constraints and user-defined defaults
SYSCOPY Information on the execution of DB2 utilities required by DB2 recovery SYSDATABASE Information about every DB2 database
SYSDATATYPES Information about the user-defined distinct types defined to the DB2 subsystem
SYSDBAUTH Database privileges held by DB2 users
SYSDBRM DBRM information only for DBRMs bound into DB2 plans
SYSDUMMY1 Contains no information; this table is for use in SQL statements requiring a table reference without regard to data content
SYSFIELDS Information on field procedures implemented for DB2 tables SYSFOREIGNKEYS Information about all columns participating in foreign keys
SYSINDEXES Information about every DB2 index
SYSINDEXPART Information about the physical structure and storage of every DB2 index SYSINDEXSTATS Partitioned index statistics by partition
SYSKEYS Information about every column of every DB2 index SYSLINKS Information about the links between DB2 Catalog tables SYSLOBSTATS Statistical information for LOB tablespaces
SYSPACKAGE Information about every package known to DB2 SYSPACKAUTH Package privileges held by DB2 users
SYSPACKDEP A cross-reference of DB2 objects required for DB2 packages SYSPACKLIST The package list for plans bound specifying packages SYSPACKSTMT All SQL statements contained in each DB2 package
SYSPARMS Parameters for defined routines
SYSPKSYSTEM The systems (such as CICS, IMS or batch) enabled for DB2 packages SYSPLAN Information about every plan known to DB2
SYSPLANAUTH Plan privileges held by DB2 users
SYSPLANDEP A cross-reference of DB2 objects required by DB2 plans
SYSPLSYSTEM The systems (such as CICS, IMS, or batch) enabled for DB2 plans
Table Contents
SYSPROCEDURES The stored procedures available to the DB2 subsystem
SYSRELS The referential integrity information for every relationship defined to DB2 SYSRESAUTH Resource privileges held by DB2 users
SYSROUTINEAUTH Privileges held by DB2 users on routines
SYSROUTINES Information about every routine (that is, user-defined functions and stored procedures) defined to the DB2 subsystem
SYSSCHEMAAUTH Schema privileges granted to users
SYSSTMT All SQL statements contained in each DB2 plan bound from a DBRM SYSSTOGROUP Information about every DB2 storage group
SYSSTRINGS Character conversion information SYSSYNONYMS Information about every DB2 synonym
SYSTABAUTH Table privileges held by DB2 users
SYSTABLEPART Information about the physical structure and storage of every DB2 tablespace
SYSTABLES Information about every DB2 table SYSTABLESPACE Information about every DB2 tablespace
SYSTABSTATS Partitioned tablespace statistics by partition
SYSTRIGGERS Information about every trigger defined to the DB2 subsystem SYSUSERAUTH System privileges held by DB2 users
SYSVIEWDEP A cross-reference of DB2 objects required by DB2 views SYSVIEWS The SQL CREATE VIEW statement for every DB2 view
SYSVLTREE A portion of the internal representation of complex or long views SYSVOLUMES A cross-reference of DASD volumes assigned to DB2 storage groups
SYSVTREE The first 4000 bytes of the internal representation of the view; the remaining portion of longer or complex views is stored in SYSVLTREE USERNAMES Outbound and inbound ID translation information
When a CREATE, DROP or ALTER statement is issued, information is recorded or updated in the DB2 Catalog. The same is true for security SQL data control language statements. The GRANT and REVOKE statements cause information to be added or removed from DB2 Catalog tables. Data manipulation language SQL statements use the DB2 Catalog to ensure that the statements
Following figure shows the effect of DDL on the DB2 catalog:
Following figure shows the effect of DCL on the DB2 catalog:
DB2 Directory
DB2 uses a second dictionary-like structure in addition to the DB2 Catalog. This is the DB2 Directory. This is used for storing detailed, technical information about aspects of DB2's operation and DB2 Directory is for DB2's internal use only.
Summary
Two sets of tables store all the data related to DB2 objects, which are as follows:
DB2 Catalog
DB2 Directory Test Your Understanding
1. Explain about the different DB2 Catalog tables.
2. What is a DB2 Directory?
Session 56: Miscellaneous
Learning Objectives
After completing the session, you will be able to:
Identify the different DB2 Utilities Utilities - Introduction
DB2 has a comprehensive collection of utility programs to help us organize and administer DB2 databases.
DB2 utility programs are divided into four broad categories:
Data Consistency Utilities
Backup and Recovery Utilities
Data Organization Utilities
Catalog Manipulation Utilities Data Consistency Utilities
Following are the data consistency utilities:
CHECK
REPAIR
REPORT
DIAGNOSE
CHECK Utility
The CHECK utility checks the integrity of DB2 data structures.
It has the following purposes.
The first is to check referential integrity between two tables, displaying and potentially resolving referential constraint violations by moving the erroneous rows to exception tables.
The second purpose of the CHECK utility is to ensure that data values conform to the check constraints specified for the table.
The third and final purpose is to check DB2 indexes for consistency. This consists of comparing the key values of indexed columns to their corresponding table values, as well as evaluating RIDs (Row Identifiers) in the tables and indexes being checked.
The CHECK utility can delete invalid rows and copy them to an exception table.
Sample JCL code which runs CHECK utility is as follows:
//* This CHECK DATA statement checks DSN8510.DEPT for //* referential constraint violations, deletes all //* offending rows, and places them into the exception //* table, DSN8510.DEPT_EXCPTN.
//*
//DSNUPROC.SYSIN DD *
CHECK DATA TABLESPACE DSN8D61A.DSN8S61D FOR EXCEPTION IN DSN8610.DEPT
USE DSN8610.DEPT_EXCPTN SCOPE ALL DELETE YES
/*
REPAIR Utility
The REPAIR utility is designed to modify DB2 data and associated data structures when there is an error or problem.
You can use the REPAIR utility to perform the following tasks:
Test DBD (database descriptor) definitions
o The REPAIR utility can be used to test, maintain, and modify DB2 database information.
o DB2 maintains database information in the DB2 Catalog SYSIBM.SYSDATABASE table.
o An object known as a DBD is also maintained in the DB2 Directory in the SYSIBM.DBD01 table.
Reset a pending status on a table space or index
Verify the contents of data areas in table spaces and indexes
Replace the contents of data areas in table spaces and indexes
Delete a single row from a table space
Produce a hexadecimal dump of an area in a table space or index
The REPAIR utility can be used to test, maintain, and modify DB2 database information. DB2 maintains database information in the DB2 Catalog SYSIBM.SYSDATABASE table. An object known as a DBD is also maintained in the DB2 Directory in the SYSIBM.DBD01 table.
A sample JCL to REPAIR the DBD for the DSN8D51A sample database is as follows:
Example:
//* UTILITY INPUT CONTROL STATEMENTS
//* The first REPAIR statement builds a DBD based on //* the DB2 Catalog and compares it to the corresponding //* DBD in the DB2 Directory.
//* The second REPAIR statement reports inconsistencies, //* if any exist.
//*
//DSNUPROC.SYSIN DD *
REPAIR DBD TEST DATABASE DSN8D61A
REPAIR DBD DIAGNOSE DATABASE DSN8D61A OUTDDN SYSREC /*
When the REPAIR utility is executed with the SET option, it can be used to reset copy pending, check pending, and recover pending flags.
Pending flags can be set at the partition level, as well as at the table space level.
In general, these flags are maintained by DB2 to indicate the status of table spaces and indexes.
When DB2 turns on a flag for a table space or index, it indicates that the object is in an indeterminate state.
When the copy pending flag is set, it indicates that the COPY utility must be used to back up the table space or partition to ensure adequate recoverability.
Copy pending status is set when unlogged changes have been made to DB2 table spaces, or when a reference to a full image copy is no longer available in the DB2 Catalog.
The check pending flag indicates that the CHECK DATA utility should be run because data has been inserted into a table containing a referential constraint without ensuring that the data conforms to the referential integrity.
The recover pending flag indicates that the table space or the index must be recovered because a utility operating on that object has ended abnormally, possibly causing inconsistent or corrupted data.
The rebuild pending flag indicates that an index does not match the table data and needs to be rebuilt.
Sometimes, however, these flags are set by DB2 but the corresponding utility does not need to be run because of other application factors. In this case, the REPAIR SET utility can be run to reset the appropriate pending flag.
JCL that can be used to reset check pending, copy pending, and recover pending restrictions for the sample table spaces. It also contains a REPAIR statement to reset the recover pending status for an index on one of the sample tables.
Example:
//****************************************************************
//*
//* DB2 REPAIR UTILITY : : RESET PENDING FLAGS //*
//****************************************************************
//*
//UTIL EXEC DSNUPROC,SYSTEM=DSN,UID='REPRSETP',UTPROC='' //*
//* UTILITY INPUT CONTROL STATEMENTS
//* 1. The first REPAIR statement resets the copy pending //* status for the named tablespace.
//* 2. The second REPAIR statement resets the check pending //* status for two tablespaces.
//* 3. The third REPAIR statement resets the recover pending
//*
//DSNUPROC.SYSIN DD *
REPAIR SET TABLESPACE DSN8D61A.DSN8S61E NOCOPYPEND REPAIR SET TABLESPACE DSN8D61A.DSN8S61E NOCHECKPEND SET TABLESPACE DSN8D61A.DSN8S61C NOCHECKPEND REPAIR SET TABLESPACE DSN8D61A.DSN8S61R NORCVRPEND REPAIR SET INDEX DSN8610.XPROJAC1 NORCVRPEND /*
REPORT Utility
Two types of reports can be generated with the REPORT utility:
The first is a table space set report showing the names of all table spaces and tables tied together by referential integrity.
The second type deals with recovery.
Sample code for REPORT TABLESPACESET utility is as follows:
The input to the utility is a single table space.
The output is a report of all related table spaces and tables.
//****************************************************************
//*
//* DB2 REPORT TABLESPACESET UTILITY //*
//****************************************************************
//*
//UTIL EXEC DSNUPROC,SYSTEM=DSN,UID='REPORTTS',UTPROC='' //*
//* UTILITY INPUT CONTROL STATEMENTS
//* The REPORT statement generates a report of all objects //* referentially tied to the named table space
//*
//DSNUPROC.SYSIN DD *
REPORT TABLESPACESET TABLESPACE DSN8D61A.DSN8S61D /*
DIAGNOSE Utility
The DIAGNOSE utility is an online utility that can be used to diagnose problems, especially problems with other DB2 utilities.
Sample JCL is as follows:
//* Display all records in the SYSIBM.SYSUTIL DB2 Directory table //*
//DSNUPROC.SYSIN DD *
DIAGNOSE DISPLAY SYSUTIL /*
Backup and Recovery Utilities Backup and Recovery utilities are:
REPORT RECOVERY Utility
COPY Utility
The COPY utility is used to create an image copy backup data set for a complete tablespace, a single partition of a tablespace, or a complete indexspace.
It can be executed so that a full image copy or an incremental image copy is created.
A full image copy is a complete copy of all the data stored in the tablespace, tablespace partition, or index being copied.
An incremental image copy is a copy of only the tablespace pages that have been modified due to inserts, updates, or deletes since the last full or incremental image copy.
Sample JCL for a full image copy for a DB2 tablespace:
Sample JCL for a full image copy for a DB2 tablespace: