Tuesday, October 27, 2009

Tape backup File not found for DSN1COPY job

Hi,

Here is one of the common problems that many of you might have faced.

Problem: One of your development guys ask to restore the table to a old backup image. Now you got the tape file name from the SYSIBM.SYSCOPY, but when you put this file name in 3.4, you are not able to find the file. This is just a JCL problem rather than a Database problem.

Solution:

You can still use this file (like in job for DSN1COPY), if it is not overwritten by any other job. You cannot find this file as this might have uncatalogued by the policy you have in your system. You can use this file by providing additional information in JCL DD statement.

The additional information are:
1) Unit info
2) VOLUME Serial Number
3) File sequence number i.e LEBEL parameter

Now get your job done!!!

Cheers!!
Prakash C Singh
IBM Certified Database Administrator

Saturday, September 12, 2009

DSNE106E PLAN DSNESPCS NOT AUTHORIZED FOR SUBSYSTEM

Hello My Blog readers....

Here is another classic problem and it's solution for you...

What to do when many people are getting the error message like "DSNE106E PLAN DSNESPCS NOT AUTHORIZED FOR SUBSYSTEM DSNA AND AUTH ID TSK10"

Here you go..

Spufi uses 2 plans for execution of SQL statements on demand.
One is DSNESPRR and other one is DSNESPCS.
DSNESPRR plan uses Repeatable read where as DSNESPCS uses Cursor stability isolation level.

DSNESPRR plan is the default for Spufi.

RR isolation is highest in restriction level and provides least concurrency. This may a disaster in production environment if someone accesses the table thru DSNESPRR in spufi and went for a break without coming out from the output panel. This may lock the Table.

Solution:
1) Never allow people to use RR isolation level in Spufi. (this is the Best Practice)
2) Rebind DSNESPRR with an isolation level of CS, thereby eliminating the chance that people will use spufi with RR
3) FREE DSNESPRR plan so that nobody will access this plan.


Note: To Resolve the authorization problem mentioned at the begining of the blog.

Always grant EXECUTE on DSNESPCS to PUBLIC.




Cheers..
Prakash C. Singh
IBM Certified DB2 DBA

Saturday, August 29, 2009

Permission Required for DB2 Subsystem

All of us must have known that we require different level of access for different objects in the database. But did you think what is the access required to connect to a DB2 subsystem. What I am saying here is; you need to have permission to connect to a DB2 subsystem first and then you need second level of privileges to access different objects in the database.

Here all that are happening when an ID is tried to access any table.
1) RACF verifies whether this ID is authorized for DB2 resources.
2) There is a resource class for DB2 called DSNR which is contained in the RACF descriptor table.
3) There should be a profile defined as a member of class DSNR.
4) The ID or the group contains this ID should be included in the profile.
5) If the ID is included, then the thread is connected to DB2 Subsystem
6) Once connected to DB2, it checks in catalog tables to verify the intended access.

Following commands can be executed by RACF admin to let the user run batch jobs:
PERMIT DSN1.BATCH CLASS(DSNR) ID(TSG10) ACCESS(READ)
For taking away the access:
PERMIT DSN1.BATCH CLASS(DSNR) ID(TSG10) ACCESS(NONE)

This can be done through RACF pannels too...

Cheers...
Prakash C. Singh
IBM Certified DB2 DBA

Tuesday, July 21, 2009

Creating New Index on BIG Table

Hi,

As always Prakash promised to back with the solutions with respect to practical scenario.
Here is another one for you.

Task: You are asked to create an Index in an existing table which is very huge.

Problem: You might face this issue. When you fired this SQL through batch spufi, it will run for minutes and at last it will abend with -904 return code saying 4K page is not available.
Investigation: You will find many messages in DSN?MSTR and DSN?DBM1 whcih says the temporary tablespace DSNDB07. unable to extend itself as it might reached it's limit of 2GB.

Solution: There are some fact you must know while creating index in a big table.
When you are creating the index, by default it will try to build the index instantly. CREATE INDEX statement uses RDS Sort to sort the keys. RDS Sort is very efficient for smaller tables. But when there is a huge table you must defer the creation of index by specifying DEFER YES in CREATE INDEX statement so that index for the table is registered in the DB2 catalog but the new index is in Rebuild pending status giving warning while creation of index.
REBUILD INDEX uses the the EXTERNAL SORT which outperforms the RDS Sort if the table size is significant. Even the performance of REBUILD INDEX is improved with parallel partition key extract and parallel index build.

By deferring the index creation and rebuilding the index, your index creation is over in few minutes and you will not get any extend failure message in MSTR started task.

Cheers..
Prakash C. Singh
IBM Certified DB2 DBA

Tuesday, July 14, 2009

Understanding DSN1CHKR Utility

Hello Everybody!!

Sometimes it is worth to verify the intigrity of DB2 directory and Catalog table spaces. DSN1CHKR utility is designed for this.
This utility checks the specified tablespace for

1) Broken Links
2) Broken Hash Chanins
3) Orphan records

This is a service aid and a diagonistic tool which executes outside the control of DB2.

Restriction:
You should not DSN1CHKR in a tablspace while it is active under DB2. Tablespace should be in STOPPED status before running this utility.

Privilege Required:
RACF auth required on the specified tablespace. No other privileges required to run this utility

Recommendation:
Copy the stopped tablespace into a similar VSAM dataset with DB2 naming convention and then run the utility on the copy.

Never run this utility on following tablespaces:

DSNDB06.SYSCOPY
DSNDB06.SYSDDF
DSNDB06.SYSGPAUT
DSNDB06.SYSPKAGE
DSNDB06.SYSSTATS
DSNDB06.SYSSTR
DSNDB06.SYSUSER
DSNDB01.SCT02
DSNDB01.SPT01
DSNDB01.SYSLGRNX
DSNDB01.SYSUTILX

Sample Job Step:

//**********************************************************************000
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************000
//**********************************************************************000
//DH000701 EXEC PGM=DSN1CHKR,PARM='FORMAT' 000
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 000
//SYSPRINT DD SYSOUT=(2,,CRTL) 000
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB01.DBD01.I0001.A001,DISP=SHR 000
//**********************************************************************000
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************001
//**********************************************************************001
//DH000702 EXEC PGM=DSN1CHKR,PARM='FORMAT' 001
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 001
//SYSPRINT DD SYSOUT=(2,,CRTL) 001
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSDBASE.I0001.A001,DISP=SHR 001
//**********************************************************************001
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************001
//**********************************************************************001
//DH000703 EXEC PGM=DSN1CHKR,PARM='FORMAT' 001
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 002
//SYSPRINT DD SYSOUT=(2,,CRTL) 002
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSDBAUT.I0001.A001,DISP=SHR 002
//**********************************************************************002
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************002
//**********************************************************************002
//DH000704 EXEC PGM=DSN1CHKR,PARM='FORMAT' 002
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 002
//SYSPRINT DD SYSOUT=(2,,CRTL) 002
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSGROUP.I0001.A001,DISP=SHR 002
//**********************************************************************003
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************003
//**********************************************************************003
//DH000705 EXEC PGM=DSN1CHKR,PARM='FORMAT' 003
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 003
//SYSPRINT DD SYSOUT=(2,,CRTL) 003
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSPLAN.I0001.A001,DISP=SHR 003
//**********************************************************************003
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************003
//**********************************************************************003
//DH000706 EXEC PGM=DSN1CHKR,PARM='FORMAT' 004
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 004
//SYSPRINT DD SYSOUT=(2,,CRTL) 004
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSVIEWS.I0001.A001,DISP=SHR 004
//*

This is what IBM specified for the Sample JCL:
//YOUR JOBCARD
//*
//JOBCAT DD DSNAME=DSNCAT1.USER.CATALOG,DISP=SHR
//STEP1 EXEC PGM=IDCAMS
//********************************************************************
//* ALLOCATE A TEMPORARY DATA SET FOR SYSDBASE *
//********************************************************************
//SYSPRINT DD SYSOUT=A
//SYSUDUMP DD SYSOUT=A
//SYSIN DD *
DELETE -
(TESTCAT.DSNDBC.TEMPDB.TMPDBASE.I0001.A001) -
CATALOG(DSNCAT)DEFINE CLUSTER -
( NAME(TESTCAT.DSNDBC.TEMPDB.TMPDBASE.I0001.A001) -
NONINDEXED -
REUSE -
CONTROLINTERVALSIZE(4096) -
VOLUMES(XTRA02) -
RECORDS(783 783) -
RECORDSIZE(4089 4089) -
SHAREOPTIONS(3 3) ) -
DATA -
( NAME(TESTCAT.DSNDBD.TEMPDB.TMPDBASE.I0001.A001)) -
CATALOG(DSNCAT)
/*
//STEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//********************************************************************
//* STOP DSNDB06.SYSDBASE *
//********************************************************************
//STEPLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=A
//SYSPRINT DD SYSOUT=A
//SYSTSIN DD *
DSN SYSTEM(V61A)
-STOP DB(DSNDB06) SPACENAM(SYSDBASE)END
/*
//STEP3 EXEC PGM=DSN1COPY,PARM=(CHECK)
//********************************************************************
//* CHECKSYSDBASE AND RUN DSN1COPY *
//********************************************************************
//STEPLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=A
//SYSUT1 DD DSN=DSNCAT.DSNDBC.DSNDB06.SYSDBASE.I0001.A001,DISP=SHR
//SYSUT2 DD DSN=TESTCAT.DSNDBC.TEMPDB.TMPDBASE.I0001.A001,DISP=SHR
/*
Example 2:

To Run DSN1CHKR on an actual table space. STEP1 stopsdatabase DSNDB06 with the STOP DATABASE command. STEP2 runsDSN1CHKR on the target table space; its output is identical to the output inExample 1. STEP3 restarts the database with the START DATABASE command.
//STEP4 EXEC PGM=IKJEFT01,DYNAMNBR=20
//********************************************************************
//* START DSNDB06.SYSDBASE *
//********************************************************************
//STEPLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=A//SYSPRINT DD SYSOUT=A
//SYSTSIN DD *DSN SYSTEM(V61A)
-START DB(DSNDB06) SPACENAM(SYSDBASE)END
/*
//STEP5 EXEC PGM=DSN1CHKR,PARM='MAP=RID(00000201,06,00000B01,06)',
// COND=(4,LT)
//********************************************************************
//* CHECKLINK S OF SYSDBASE *
//********************************************************************
//STEPLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=A
//SYSUT1 DD DSN=TESTCAT.DSNDBC.TEMPDB.TMPDBASE.I0001.A001,DISP=SHR
/*

Thanks for reading this blog

Cheers
Prakash C. Singh
IBM Certified DB2 DBA

Thursday, July 9, 2009

Calculating size of Database or Table in DB2 for z/OS

Hi All,

Sometimes people around you ask simple question:
What is the size of a particular database or a particular table?

How will you calculate???

Option 1: Get the total number of rows multiplied by row length.. What if you have variable columns...
Option 2: Get the total number of pages allocated for individual tables and multiply by 4K. What if there are large number of tables in a databases??
Option 3: List the underlying VSAM dataset of tablespace and indexspace. Get the Hi Used RBA and Low Used RBA and calculate the difference. Same Constrains as avove option.

Here you go ...
IBM provided a stand alone alone utility which will calculate the space for you within minutes.
The Utility name is STOSPACE. All you need to give the input is the Storage Group of the underlying dataset of spacific table or index. This will update the catalogue tables of DB2 regarding space information from where you can get by firing simple Select query.

Authorization required:
STOSPACE privilege SYSCTRL or SYSADM authority

This JCL step should be like:
//*---------------------------------------------------------------------
//* STOSPACE UTILITY TO UPDATE INFORMATION IN DSN1 CATALOG
//*---------------------------------------------------------------------
//DH011510 EXEC DSNUPROC,SYSTEM=DSN1,UID='TSG11510',UTPROC=''
//*
//SYSPRINT DD SYSOUT=*
//DSNUPROC.SYSIN DD *
STOSPACE STOGROUP (GDSN1)
//*

After Executing the utility, you may get following messages in SYSPRINT
Information you get in SYSPRINT
DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = DH011510
DSNU050I DSNUGUTC - STOSPACE STOGROUP(GDSN1)
DSNU640I - DSNUGSPC - DATA SET= 0 OF TABLESPACE= SPRODDET IN DATABASE= DISSUES2 HAS BEEN MIGRATED BY HSM
DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

After successful execution of STOSPACE utility you can query the catalogue tables as below and add the tablespace and index's space value to calculate the size of a database.
Calculating all index space in a database:
SELECT DBNAME, SUM(SPACE)
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = 'DTSG10'
GROUP BY DBNAME;

Calculating all index space in a database:
SELECT DBNAME, SUM(SPACE)
FROM SYSIBM.SYSINDEXES
WHERE DBNAME = 'DTSG10'
GROUP BY DBNAME;

You can query SYSIBM.SYSSTOGROUP to findout total space in KB aquired by a particular storage group.

More about STOSPACE Utility:

STOSPACE output stored in columns as mentioned below:

The output from STOSPACE consists of updated values in the columns and tables in the following list. In each case, an amount of space is given in kilobytes (KB).
SPACE - in SYSIBM.SYSINDEXES shows the amount of space that is allocated to indexes. If the index is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSTABLESPACE shows the amount of space that is allocated to table spaces. If the table space is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSINDEXPART shows the amount of space that is allocated to index partitions. If the partition is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSTABLEPART shows the amount of space that is allocated to table partitions. If the partition is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSSTOGROUP shows the amount of space that is allocated to storage groups. STATSTIME in SYSIBM.SYSSTOGROUP shows the timestamp for the time at which STOSPACE was last executed.

Note: If the value is too large to fit in the SPACE column, the SPACEF column is updated.

Concurrency and compatibility for STOSPACE:
STOSPACE does not set a utility restrictive state on the target object.
STOSPACE can run concurrently with any utility on the same target object. However, because STOSPACE updates the catalog, concurrent STOSPACE utility jobs or other concurrent applications that update the catalog might cause timeouts and deadlocks.

Terminating or restarting STOSPACE:
You can terminate a STOSPACE utility job with the TERM UTILITY command if you have submitted the job or have SYSOPR, SYSCTRL, or SYSADM authority.
You can restart a STOSPACE utility job, but it starts from the beginning again.

Cheers...
Prakash C. Singh
IBM Certified DB2 DBA

Sunday, June 28, 2009

Performance Consideration of SQL and Index for DB2 on z/OS

Hi,
Here is Prakash Back ... This is with interesting one which is related to Performance which is one of my favorite topic. This post is also has the link with my previous post as well related to Explain the SQL statements.
Here are some eye openers:
1) Matching Index scan: (MATCHCOLS > 0)
This comes into picture when our predicates match either leading index key columns or all of them hence give us the filtering criteria for specific index and data pages. If it is able to filter high number of records, then this type of access path is efficient.

There are 3 types of predicates:
1. Index Predicates
a. Matching predicates
b. Index screening predicates
2. Stage 1 predicates - this applied first to filter out most of the records enhancing query performance
3. Stage 2 predicates - This is applied to the rows returned from stage 1 predicates

From DB2 point of view a. first all equal predicates/ IS NULL, b. Then all Range Predicates/ IS NOT NULL c. then at last all other predicates are applied to the SQL Query.
Therefore developer should code most rstrictive predicates first.

Index Screening:
This predicates are specified on index-key columns but are not part of matching columns. In other words, they are not leading columns of the index. They used to search the index first before going to the data pages.

Nonmatching Index Scan: (ACCESSTYPE = I and MATCHCOLS = 0)
No matching columns are in index, hence all the index keys must be examined.
Sometimes it provides an efficient access path if the path is index only and index is smaller than the tablespace.

IN-list index scan: (ACCESSTYPE = N)
This is a special case of matching index scan. This is equivalent to matching equal predicate.
Exception:noncorrelated IN Subquery or in MX access or list prefetch.

Multiple-index access: (ACCESSTYPE is M, MX, MI, MU)
Multiple index access is table access by more than one index.
RID lists are constructed for each if the index and final list is retrieved after AND/OR operation. This type of access is extention to List Prefetch.

One-Fetch access: (ACCESSTYPE = I1)
This is required to retrive only 1 row. For example Max/Min column function. Most efficient access path.

Index Only Access: (INDEXONLY = Y)
If all the information in a query is available in index itself, it only access the index.

Equal Unique Index (MATCHCOLS = Number of Index Columns)
This guarantees the excat 1 row retrieval. This is next most efficient access path to One-Fetch access.

Other Considerations:
Avoiding Sort:
1) DISTINCT sort can be avoided by by using Unque index
2) ORDER BY, GROUP BY sort can be avoided by ordering index by ASC/DESC order
3) OPTIMIZE FOR n ROWS can eliminate most expensive sort with ordered index

Below information are spefically for Dynamic SQL:
Dynamic SQL performance is key to many applications.

Few things that can be considered are:
1) Reorganize DB2 Catalogue
2) Reorganize and Runstat the application tablr and index space
3) Use dynamic statement caching
4) SET CURRENT DEGREE = 'ANY' for parallelism

Query Parallelism:
This is less than 1% additional CPU overhead for long running queries and less than 10% for short running queries.

I/O and CPU Parallelism: (PARALLELISM_MODE = I or O)
The preffered method is CPU parallelism. It can be decided at both Bind time (DEGREE = ANY) and Runtime. If it is not chosen at Bind time, it can be chosen at runtime. Even if it is chosen at bind time, it may not be used at Runtime. It all depends on the current environment when the query will run.

Queries best suited for Parallelism:
1. Long running, read only queries
2. Tablespace scan
3. Joins
4. Nested loops
5. Merge scans
6. Hybrid Join
7. Sorts
8. Aggregate functions

Prallelism should not considered for:
1. Queries that materialized views
2. Queries using direct row access
3. Queries that perform materialization because of nested table expressions
4. Queries performing a merge-scan join of more than one column

Sysplex Parallelism not to be considered:
1) Queries with list prefetch and multiple index access
2) Queries accessing LOB data

Other Considerations:
1) Can not be considered if system is already CPU constrained
2) Can not be used when a CURSOR is defined WITH HOLD.

Sysplex Query Parallelism: (PARALLELISM_MODE = X)
1. Complex query to run across multiple members in a data sharing group of multi tasking env
2. Best used with ISOLATION level UR to avoid excess lock propagation


The information gives some eye sight regarding Performace.

See all you in my next blog .. Till then happy tunning...

Cheers....
Prakash C. Singh
IBM certified DB2 DBA.

Saturday, April 25, 2009

How to EXPLAIN your SQL query

Hi,

In continuation of my discussion regarding Plan and DSN_STATEMNT table, I am now giving you the code thru which you can EXPLAIN your SQL statements and find the parameters populated in both of the tables mentioned above. This will help you in your analysis of performance regarding SQL you are going to run.

Consideration:
1) You should have all the objects (Referenced in your SQL) created in the same env where you will Explain the SQL.
2) You tables must be runstated before running Explain
3) You can replace host variables with any Char/Int Literals in the SQL statements.
4) Make sure your PLAN_TABLE and DSN_STATEMNT_TABLE present in the same Env.

Here is one example of SQL code regarding How to Explain your query.
DELETE FROM PLAN_TABLE WHERE QUERYNO = 99999;
COMMIT;

EXPLAIN PLAN SET QUERYNO = 99999 FOR

SELECT B.STORE_NO, B.STOCK_ITEM, B.DELIVERY_DATE
FROM V1FOGH01 B

WHERE DELIVERY_DATE = (SELECT MAX(DELIVERY_DATE)
FROM V1FOGH01 A
WHERE A.STORE_NO = B.STORE_NO
AND A.STOCK_ITEM = B.STOCK_ITEM
AND A.QTY_TYPE ='7'
AND A.DELIVERY_DATE <= CURRENT DATE)
AND B.QTY_TYPE ='7';

SELECT * FROM PLAN_TABLE
WHERE QUERYNO = 99999
ORDER BY TIMESTAMP,QUERYNO,QBLOCKNO,PLANNO,MIXOPSEQ;

SELECT * FROM DSN_STATEMNT_TABLE
WHERE QUERYNO = 99999;
========

I have Added the Select statement so that immediately you can view the result. You can run this statement in Batch as well as Online (Spufi).

I have already discussed some important columns of PLAN_TABLE and DSN_STATEMNT_TABLE. So Enjoy while analysing the values you got for these 2 tables.


Regards,
Prakash C Singh
IBM Certified DB2 DBA.

Sunday, March 15, 2009

How to read DSN_STATEMNT_TABLE

Hi,



While analyzing the SQL queries, it is really worth to calculate the cost of the query. This cost is populated into DSN_STATEMNT_TABLE by the explain SQL statement.



Here are some important column info



1) QUERYNO:A no. identifying the statement being explained.



2) STMT_TYPE:

SELECT - Select

INSERT - Insert

UPDATE - Update

DELETE - Delete

SELUPD - Select with FOR UPDATE OF

DELCUR - DELETE WHERE CURRENT OF CURSOR

UPDCUR - UPDATE WHERE CURRENT OF CURSOR



3) COST_CATEGORY:

A - DB2 has enough information to calculate cost without using default values

B - DB2 forced to use default values. See REASON column why DB2 choose default values



4) PROCMS:The estimated processor cost in milliseconds for SQL statements. This is the most important column to look at while analyzing the SQL query



5) PROCSU:The estimated processor cost in Service Units for SQL statements. This is also worth to look at.



6) REASON:Gives the reason why it is in cost category B.

Having ClauseHost Variables - Also Parameter Markers or Special Registers

REFERENTIAL CONSTRAINTS - CASCADE and SET NULL exist for DELETE statement

TABLE CARDINALITY - Cardinality statistics missing

UDF TRIGGERS - defined on target table of INSERT, UPDATE or DELETE statement





Cheers...

Prakash C Singh

IBM Certified DB2 DBA

How to read PLAN_TABLE after EXPLAIN

Hi,

Here are some information you should be looking at various columns of the PLAN_TABLE. This will help you analysing the SQL queries and it's various attributes.

1) QUERYNO: A number identifying the statement being explained.

2) METHOD:
0 - First table accessed, continuation of previous table accessed or not used
1 - Nested Loop Join
2 - Merge Scan Join
3 - Sorts required by ORDER BY, GROUP BY, SELECT DISTINCT, uNION
4 - Hybrid Join

3) ACCESSTYPE:
I - By an Index
I1 - One fetch Index Scan
M - Multiple index scan
MX - By Index mentioned in ACCESSNAME
MI - Intersection of Multiple indexes
MU - Union of multiple indexes
N - Index scan when matching predicated in IN keyword
R - Tablespace scan
RW - Work file scan of a materialized user defined table funtion
T - By a spare index - Star join work files
V - By buffers for an INSERT statement within a SELECT
Blank - NA

4) INDEXONLY:
Whether access to an Index alone is enough to carry out the step

5) TSLOCKMODE:
IS - Intent Share Lock
IX - Intent Exclusive lock
S - Share Lock
U - Update Lock
X - Exclusive Lock
SIX - Share with Intent Exclusive lock
N - UR Isolation: No Lock
NS - For CS, RS, RR an S Lock
NIS - For CS, RS, RR an IS Lock
NSS - For CS, RS an IS Lock and for RR an S Lock
SS - For UR, CS, RS an IS Lock and for RR an S Lock

6) PREFETCH:
S - Pure Sequential
L - thru a page List
D - Optimizer expects dynamic prefetch
Blank - Unknown at bind time or NA

7)ACCESS_DEGREE:
Number of Parallel tasks or operations activated by a Query
0 - if there is a host variable

8) PARALLELISM_MODE:
I - Query I/O parallelism
C - Query CP parallelism
X - Sysplex query parallelism

9) PAGE_RANGE:
Whether the table qualifies for page-range(scan only the partitions those are needed)
Y - Yes
Blank - No

10) JOIN_TYPE:
F - Full Outer Join
L - Left Outer Join
S - Star Join
Blank - Inner Join or No join
Note: Right Outer Join always converted to Left Outer Join

11) WHEN_OPTIMIZE:
Blank: At bind time, using a default filter factor for any host variables, parameter markers or special registers
B - Above facts + Bind option REOPT (ALWAYS) or REOPT (ONCE) must be specified.
R - At Runtime, using input variables, parameter markers or special registers. Bind option REOPT (ALWAYS) or REOPT (ONCE) must be specified.

12) QBLOCK_TYPE:
SELECT - Select
INSERT - Insert
UPDATE - Update
DELETE - Delete
SELUPD - Select with FOR UPDATE OF
DELCUR - DELETE WHERE CURRENT OF CURSOR
UPDCUR - UPDATE WHERE CURRENT OF CURSOR
CORSUB - Correlated Subquery
NCOSUB - NonCorrelated Subquery
TABLEX - Table Expression
TRIGGER - WHEN caluse on CREATE TRIGGER
UNION - Union
UNIONA - Union All

13) PRIMRY-ACCESSTYPE:
D - Direct Row access
Blank - No Direct Row access

14) TABLE_TYPE:
B - Buffers for an INSERT stament within a SELECT
C - Common Table Expression
F - Table Function
M - Materialized Qery Table
Q - Temp intermediate table(Not Materialized), name of the viewor nested table expression, Contains a UNION ALL where materialization was vertual not actual.
RB - Recursive Common Table Expression
T - Table
W - Work file (Materialized)

15) TABLE_ENCODE:
A - ASCII
E - EBCDIC
U - Unicode
M - when multiple CCSID is in one table


We will be discussing more on these on my post regarding Performance.

Cheers,
Prakash C Singh
IBM Certified DB2 DBA

Sunday, March 8, 2009

EXPLAIN Tables (PLAN_TABLE ,DSN_STATEMNT_TABLE, DSN_FUNCTION_TABLE)

Just mangaed to get some time to post this information.

This is helpful while creating Explain tables to anayse a badly performing query.

I am currently working on DB2 V7 product.

There is one member in the installation library of DB2 production where you will extract the defination of EXPLAIN tables (PLAN_TABLE ,DSN_STATEMNT_TABLE, DSN_FUNCTION_TABLE). Also you can see the defination of the Optimizer hint tables too.

Here is the member you should look for D710.DSN1.SDSNSAMP(DSNTESC)

For the benefit of many I am putting the defination of these tables here


CREATE TABLE SCHEMANM.PLAN_TABLE
( "QUERYNO" INTEGER NOT NULL,
"QBLOCKNO" SMALLINT NOT NULL,
"APPLNAME" CHAR(8) NOT NULL,
"PROGNAME" CHAR(8) NOT NULL,
"PLANNO" SMALLINT NOT NULL,
"METHOD" SMALLINT NOT NULL,
"CREATOR" CHAR(8) NOT NULL,
"TNAME" CHAR(18) NOT NULL,
"TABNO" SMALLINT NOT NULL,
"ACCESSTYPE" CHAR(2) NOT NULL,
"MATCHCOLS" SMALLINT NOT NULL,
"ACCESSCREATOR" CHAR(8) NOT NULL,
"ACCESSNAME" CHAR(18) NOT NULL,
"INDEXONLY" CHAR(1) NOT NULL,
"SORTN_UNIQ" CHAR(1) NOT NULL,
"SORTN_JOIN" CHAR(1) NOT NULL,
"SORTN_ORDERBY" CHAR(1) NOT NULL,
"SORTN_GROUPBY" CHAR(1) NOT NULL,
"SORTC_UNIQ" CHAR(1) NOT NULL,
"SORTC_JOIN" CHAR(1) NOT NULL,
"SORTC_ORDERBY" CHAR(1) NOT NULL,
"SORTC_GROUPBY" CHAR(1) NOT NULL,
"TSLOCKMODE" CHAR(3) NOT NULL,
"TIMESTAMP" CHAR(16) NOT NULL,
"REMARKS" VARCHAR(254) NOT NULL,
"PREFETCH" CHAR(1) NOT NULL WITH DEFAULT,
"COLUMN_FN_EVAL" CHAR(1) NOT NULL WITH DEFAULT,
"MIXOPSEQ" SMALLINT NOT NULL WITH DEFAULT,
"VERSION" VARCHAR(64) NOT NULL WITH DEFAULT,
"COLLID" CHAR(18) NOT NULL WITH DEFAULT,
"ACCESS_DEGREE" SMALLINT,
"ACCESS_PGROUP_ID" SMALLINT,
"JOIN_DEGREE" SMALLINT,
"JOIN_PGROUP_ID" SMALLINT,
"SORTC_PGROUP_ID" SMALLINT,
"SORTN_PGROUP_ID" SMALLINT,
"PARALLELISM_MODE" CHAR(1),
"MERGE_JOIN_COLS" SMALLINT,
"CORRELATION_NAME" CHAR(18),
"PAGE_RANGE" CHAR(1) NOT NULL WITH DEFAULT,
"JOIN_TYPE" CHAR(1) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" CHAR(8) NOT NULL WITH DEFAULT,
"IBM_SERVICE_DATA" VARCHAR(254) NOT NULL WITH DEFAULT,
"WHEN_OPTIMIZE" CHAR(1) NOT NULL WITH DEFAULT,
"QBLOCK_TYPE" CHAR(6) NOT NULL WITH DEFAULT,
"BIND_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"OPTHINT" CHAR(8) NOT NULL WITH DEFAULT,
"HINT_USED" CHAR(8) NOT NULL WITH DEFAULT,
"PRIMARY_ACCESSTYPE" CHAR(1) NOT NULL WITH DEFAULT,
"PARENT_QBLOCKNO" SMALLINT NOT NULL WITH DEFAULT,
"TABLE_TYPE" CHAR(1)
)
IN DBNAME.TABLESPC
CCSID EBCDIC;



CREATES THE V7.1 SAMPLE DSN_FUNCTION_TABLE

CREATE TABLE SCHEMANM.DSN_FUNCTION_TABLE
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT,
"QBLOCKNO" INTEGER NOT NULL WITH DEFAULT,
"APPLNAME" CHAR(8) NOT NULL WITH DEFAULT,
"PROGNAME" CHAR(8) NOT NULL WITH DEFAULT,
"COLLID" CHAR(18) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" CHAR(8) NOT NULL WITH DEFAULT,
"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"SCHEMA_NAME" CHAR(8) NOT NULL WITH DEFAULT,
"FUNCTION_NAME" CHAR(18) NOT NULL WITH DEFAULT,
"SPEC_FUNC_NAME" CHAR(18) NOT NULL WITH DEFAULT,
"FUNCTION_TYPE" CHAR(2) NOT NULL WITH DEFAULT,
"VIEW_CREATOR" CHAR(8) NOT NULL WITH DEFAULT,
"VIEW_NAME" CHAR(18) NOT NULL WITH DEFAULT,
"PATH" VARCHAR(254) NOT NULL WITH DEFAULT,
"FUNCTION_TEXT" VARCHAR(254) NOT NULL WITH DEFAULT
)
IN DBNAME.TABLESPC
CCSID EBCDIC;



CREATES THE V7.1 SAMPLE DSN_STATEMNT_TABLE

CREATE TABLE
SCHEMANM.DSN_STATEMNT_TABLE
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT,
"APPLNAME" CHAR(8) NOT NULL WITH DEFAULT,
"PROGNAME" CHAR(8) NOT NULL WITH DEFAULT,
"COLLID" CHAR(18) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" CHAR(8) NOT NULL WITH DEFAULT,
"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"STMT_TYPE" CHAR(6) NOT NULL WITH DEFAULT,
"COST_CATEGORY" CHAR(1) NOT NULL WITH DEFAULT,
"PROCMS" INTEGER NOT NULL WITH DEFAULT,
"PROCSU" INTEGER NOT NULL WITH DEFAULT,
"REASON" VARCHAR(254) NOT NULL WITH DEFAULT
)
IN
DBNAME.TABLESPC
CCSID EBCDIC;



We will discuss more on this in my next post.


Cheers,
Prakash C Singh
IBM Certified DB2 DBA