Restrict users to create implicit databases like DSN00001 ...

Problem Definition: Application developer can create their own tables taking away controls from DBA.

Explanation: Recently while analyzing the Catalog table space we found many tables and databases created by application developers for their testing purpose. They are sitting on the space pool that the DB2 catalog is using. This is a wired situation from a DBA point of View.

We will discuss how to prevent application users to create a table/database in DB2 environment.

How it is possible: In DB2 V9, IBM introduced a new feature of implicit Database creation. It means if you try to create a table in DB2 without mentioning table space and database, DB2 will take care of that by implicitly creating them.

On a CREATE TABLE statement, if you do not specify a database name, DB2 will use an existing implicitly created database. If an implicitly created database does not exist, DB2 creates one using the naming convention of DSNxxxxx. The DSNxxxxx values can range from DSN00001 to DSNnnnnn, where nnnnn is the maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB, with a default of 10000.

To remind you that in Pre-V9, the database is default to DSNDB04 while creating the new table.

Just imagine one Database of each table. It's really difficult task for DBA to relate the objects logically and maintain them. More over they will take the space from the storage group where DB2 catalog resides.

IBM has taken this steps for future to simplify the object creation without providing table space and database name which are not familiar to users other than DBAs. it may be beneficial for certain tools or SAP environment where lots tables to be created while setting up the environment.

Resolutions:
How to prevent the users to create the implicit databases:

Revoke the Create object privilege on database DSNDB04 from PUBLIC. You can't restrict SYSADM users in this case.
or
Restrict the cataloging of high level qualifier containing DSNxxxxx to register the dataset in VTOC
or
Alter the default maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB to 1


Cheers,
Prakash Singh
IBM Certified DB2 DBA

DSNTEP2 stops after MAXERRORS limit reached

Hi,

Problem description:
This happened when I was using multiple insert script through program DSNTEP2. Idea was to insert the record if that is not present in table.


The execution gave me MAX CC 0008. I knew that some inserts are going to fail due to duplicate record.

Observation: The number of insert statement executed was 10 out of 100. All were failed due to duplicate records.

Question: Why the utility did not process rest of the statement.

Here is why:

There is a parameter which controls the maximum error the DSNTEP2 will encounter before exit. This Parameter is MAXERRORS.

The default value for this parameter is 10.

Solution:

We can override the default value for MAXERRORS as shown below or to a specific definite number.

--#SET MAXERRORS -1

You can embed this command at the begin of the control card statement like

//SYSIN DD *
-#SET MAXERRORS -1

INSERT INTO tablex VALUES('a',1,'x');
COMMIT;

So on...


Cheers,
Prakash C. Singh
IBM Certified DBA

UNAVAILABLE RESOURCE REASON 00E70081

Hi,

Here I come again!!!

Problem:

You want to drop or alter a Table. But you are getting "DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00E70081, TYPE OF RESOURCE 00000A00, AND RESOURCE NAME

Let's see what is reason code: 00E70081

Explanation: A DROP or ALTER statement was issued but the object cannot be dropped or altered. The object is referenced by a prepared dynamic SQL statement that is currently stored in the prepared statement cache and is in use by an application.

System action: The requested operation is not performed.

Programmer response: The requested operation cannot be performed until there are no applications that reference the object using dynamic SQL. Ensure that other applications have quiesced or performed a commit operation. Then try the request again.

Problem determination: An SQLCODE -904 is issued. The message tokens for that message report the name and type of the object that was being referenced by another application.

Solution:

On quering the resource type:

TYPE Code            Type of Resource       Name, Content,Format

00000A00             Table                  RD.CR.TB

where
RD is a DB2 Release dependency mark


CR the object creator and

TB the Table name
 
 
Runstat utility will be handy to resolve this problem. Runstat with option "update none report none" will clear the dynamic statement cache so that the resource is released to be used by the other application.

Here is the control statement you need to use:

//DSNUPROC.SYSIN DD *

RUNSTATS TABLESPACE . TABLE ALL INDEX ALL

UPDATE NONE REPORT NO



Regards,
Prakash C Singh
IBM Certified DBA.

Termination of REORG TABLESPACE

Hello my blog readers...

It is truely a concern when the REORG of the table terminated. Here is one of the most important facts regarding Reorg failure and termination which I have collected from IBM manual and shared with you for your benefit.



You can terminate the REORG TABLESPACE utility.

If you terminate REORG TABLESPACE with the TERM UTILITY command during the UNLOAD phase, objects have not yet been changed, and you can rerun the job.

If you terminate REORG TABLESPACE with the TERM UTILITY command during the RELOAD phase, the behavior depends on the SHRLEVEL option:

•For SHRLEVEL NONE, the data records are not erased. The table space and indexes remain in RECOVER-pending status. After you recover the table space, rerun the REORG job.

•For SHRLEVEL REFERENCE or CHANGE, the data records are reloaded into shadow objects, so the original objects have not been affected by REORG. You can rerun the job.

If you terminate REORG with the TERM UTILITY command during the SORT, BUILD, or LOG phases, the behavior depends on the SHRLEVEL option:

•For SHRLEVEL NONE, the indexes that are not yet built remain in RECOVER-pending status. You can run REORG with the SORTDATA option, or you can run REBUILD INDEX to rebuild those indexes.

•For SHRLEVEL REFERENCE or CHANGE, the records are reloaded into shadow objects, so the original objects have not been affected by REORG. You can rerun the job.

If you terminate a stopped REORG utility with the TERM UTILITY command during the SWITCH phase, the following conditions apply:

•All data sets that were renamed to their shadow counterparts are renamed to their original names, so that the objects remain in their original state, and you can rerun the job.

•If a problem occurs in renaming the data sets to the original names, the objects remain in RECOVER-pending status, and you cannot rerun the job.

If the SWITCH phase does not complete, the image copy that REORG created is not available for use by the RECOVER utility. If you terminate an active REORG utility during the SWITCH phase with the TERM UTILITY command, during the rename process, the renaming occurs, and the SWITCH phase completes. The image copy that REORG created is available for use by the RECOVER utility.

The REORG-pending status is not reset until the UTILTERM execution phase. If the REORG utility abnormally terminates or is terminated, the objects remain in REORG-pending status and RECOVER-pending status, depending on the phase in which the failure occurred.

The following table lists the restrictive states that REORG TABLESPACE sets according to the phase in which the utility terminated.

Table 1. Restrictive states that REORG TABLESPACE sets. Phase Effect on restrictive status

UNLOAD No effect.

RELOAD SHRLEVEL NONE:

•Places table space in RECOVER-pending status at the beginning of the phase and resets the status at the end of the phase.

•Places indexes in RECOVER-pending status.

•Places the table space in COPY-pending status. If COPYDDN is specified and SORTKEYS is ignored, the COPY-pending status is reset at the end of the phase. SORTKEYS is ignored for several catalog and directory table spaces

SHRLEVEL REFERENCE or CHANGE has no effect.

SORT No effect.

BUILD SHRLEVEL NONE resets RECOVER-pending status for indexes and, if the utility job includes both COPYDDN and SORTKEYS, resets COPY-pending status for table spaces at the end of the phase. SHRLEVEL REFERENCE or CHANGE has no effect.

SORTBLD No effect during the sort portion of the SORTBLD phase. During the build portion of the SORTBLD phase, the effect is the same as for the BUILD phase.

LOG No effect.

SWITCH No effect. Under certain conditions, if TERM UTILITY is issued, it must complete successfully; otherwise, objects might be placed in RECOVER-pending status.

Recovering a failed REORG job

If you terminate REORG SHRLEVEL NONE in the RELOAD phase, all SYSLGRNX records associated with the reorganization are deleted. Use the RECOVER TABLESPACE utility to recover to the current point in time. This action recovers the table space to its state before the failed reorganization.

Example 1: Recovering a table space. The following control statement specifies that the RECOVER utility is to recover table space DSN8D81A.DSN8S81D to the current point in time.

RECOVER TABLESPACE DSN8D81A.DSN8S81D


Cheers.. and Merry Christmas!!!


Regards,
Prakash C. Singh
IBM Certified DB2 DBA.

DB2 EXIT Routines

Hi,

Here is one topic for you in controlling access to DB2 Subsystems:

There are two very important IBM supplied default Exit routines.

DB2 provides 2 exit points for authorization routines:

1) Connection Processing - DSN3@ATH

2) Sign-on Processing - DSN3@SGN

DB2 also provides a third exit point - DSNX@XAC which gives you the flexibility to furnish your own access control routines or use RACF (or equivalent) to perform system authorization checking.

DB2 passes 3 possible functions while invoking authorization routine

a) Initialization - DB2 Startup
b) Authorization check
c) Termination - DB2 Shutdown

There are certain situations where Exit routines may not be called

1) If the user is a Install SYSADM or Install SYSOPR
2) Grant statement is executed
3) If previous invocation indicated the routine should not be called again
4) DB2 security has been disabled (AUTH (DSNZPARM) - Use Protection is set to NO  or in DSNTIPP panel)


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

Backup Failed with REASON 00C200E1

Hello All,

Here is another classic example of backup failure with REASON 00C200E1.

You will get below message:

DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = DSQDBCTL
DSNUGUTC -  COPY TABLESPACE DSQDBCTL.DSQTSCT1 COPYDDN DSQTSCT1
DSNUGBAC - RESOURCE UNAVAILABLE
          REASON 00C200E1
          TYPE 00000220
          NAME QMF220.DSNDBC.DSQDBCTL.DSQTSCT1.I0001.A001
DSNUGBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E4010A'

If you do a QW for the above reason code, then you see

00C200E1
  Explanation:  The buffer manager (BM) subcomponent of DB2 is unable to
  open a data set that is required to be accessed by the requesting
  function. An error was returned by the media manager CONNECT function,
  which was invoked to open a VSAM data set. DB2 message DSNB204I was issued
  to inform the operator of the error condition. This reason code and the
  data set name are recorded in the cursor table (CT) and made available to
  the user in the SQLCA or in messages.

  This abend reason code is issued by the following CSECT: DSNB1OST

  System Action:  A 'resource not available' code is returned to the user.
  Abend status is recorded in SYS1.LOGREC.

  Operator Response:  Notify the system programmer.

  System Programmer Response:  Refer to system message IEC161I and DB2
  message DSNB204I for assistance in determining the cause of the error.
  Correct the situation, and notify the user to invoke the application

As you see, you need to search for IEC161I.
2 places you can able to see this, one is System LOG another place is DSN1DBM1.

It is logical to see in DBM1 as it manages buffer and the error says "he buffer manager (BM) subcomponent of DB2 is unable to open a data set"

After you look into the DBM1 started task, you may find following error messages:


ICH408I USER(SYS00   ) GROUP(SYS1    ) NAME(STC                 )  093
  QMF220.DSNDBC.DSQDBCTL.DSQTSCT1.I0001.A001 CL(DATASET ) VOL(DSN101)
  RESOURCE NOT PROTECTED
  ACCESS INTENT(UPDATE )  ACCESS ALLOWED(NONE   )
IEC161I 040(056,006,IGG0CLFT)-002,DSN1DBM1,DSN1DBM1,SYS30435,,,  094
IEC161I QMF220.DSNDBC.DSQDBCTL.DSQTSCT1.I0001.A001

Now it is confirmed that it is not a DB2 issue at all rather than RACF issue.

As you get the clue, you can contact the mainframe System admin/RACF admin to resolve the issue and rerun the job.

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

Know the power of REPAIR utility in DB2

Hello my blog readers...

In this blog, I am going to explain how you can utilize REPAIR utility the most.

Everybody know REPAIR utility is popularly used for resetting the pending states of Index and Tablespaces.



Example 1: 
Everybody know REPAIR utility is popularly used for resetting the pending states of Index and Tablespaces.

//STEP3    EXEC DSNUPROC,UID='SSTRR',                            
//            UTPROC='',SYSTEM='DSN2'                                      
//SYSIN    DD *                                                            
  REPAIR OBJECT                                                            
  SET INDEX (ALL) TABLESPACE DTSG10.SSTRR NORBDPEND                  
  SET TABLESPACE DBTEST1.STEST PART 1 NOAUXCHKP                        
  SET TABLESPACE DBTEST1.STEST PART 4 NOCHECKPEND
/*

Example 2:
Suppose you want to delete a row corresponding to a ROWID mentioned in the referential violation constraint.


REPAIR
  LOCATE TABLESPACE DTSG10.TS1 RID (X'0000000503')
    DELETE




Example 3:
Replacing damaged data and verifying replacement.

* Repair the specified page of table space DTSG10.STEST1, as indicated by the LOCATE clause.
* Verify that, at the specified offset (50), the damaged data (0A00) is found, as indicated by the VERIFY clause.
* Replace the damaged data with the desired data (0D11), as indicated by the REPLACE clause.
* Initiate a dump beginning at offset 50, for 4 bytes, as indicated by the DUMP clause. You can use the generated dump to verify the replacement.

//STEP1    EXEC  DSNUPROC,UID='IUIQU1UH',UTPROC='',SYSTEM='DSN'

//SYSIN DD *

REPAIR OBJECT

  LOCATE TABLESPACE DSN8D81A.DSN8S81D PAGE X'02'

    VERIFY OFFSET 50 DATA X'0A00'

    REPLACE OFFSET 50 DATA X'0D11'

    DUMP OFFSET 50 LENGTH 4

Example 4:

Reporting whether catalog and directory DBDs differ. The following control statement specifies that REPAIR is to compare the DBD for DTSG10 in the catalog with the DBD for DTSG10 in the directory.

REPAIR DBD TEST DATABASE DTSG10

Example 5:

Reporting differences between catalog and directory DBDs. The following control statement specifies that the REPAIR utility is to report information about the inconsistencies between the catalog and directory DBDs for DTSG10. Run this job after you run a REPAIR job with the TEST option (as shown in example 4), and the condition code is not 0. In this example, SYSREC is the output data set, as indicated by the OUTDDN option.

REPAIR DBD DIAGNOSE DATABASE DSN8D2AP OUTDDN SYSREC

Example 6:

Repairing a table space with an orphan row. After running DSN1CHKR on table space SYSDBASE, assume that you receive the following message:

DSN1812I ORPHAN ID = 20 ID ENTRY = 0190 FOUND IN

         PAGE = 0000000024

From a DSN1PRNT of page X'0000000024' and X'0000002541', you identify that RID X'0000002420' has a forward pointer of X'0000002521'.

Repair the table space by taking the following actions:

1. Submit the following control statement, which specifies that REPAIR is to set the orphan's backward pointer to zeros:

      REPAIR OBJECT LOG YES

       LOCATE TABLESPACE DSNDB06.SYSDBASE RID X'0000002420'

        VERIFY OFFSET X'0A' DATA X'0000002422'

        REPLACE OFFSET X'0A' DATA X'0000000000'

Setting the pointer to zeros prevents the next step from updating link pointers while deleting the orphan. Updating the link pointers can cause DB2 to abnormally terminate if the orphan's pointers are incorrect.

2. Submit the following control statement, which deletes the orphan:

      REPAIR OBJECT LOG YES

       LOCATE TABLESPACE DSNDB06.SYSDBASE RID X'00002420'

        VERIFY OFFSET X'06' DATA X'00002521'

        DELETE

Example 7:

Updating version information. The control statement specifies that REPAIR is to update the version information in the catalog and directory for table spaces STEST1, STEST2, and STEST3.

REPAIR VERSIONS example control statement

//STEP1    EXEC DSNUPROC,UID='JUKQU3AS.REPAIR',TIME=1440,        

//         UTPROC='',                                            

//         SYSTEM='SSTR',DB2LEV=DB2A                             

//SYSIN    DD *                                                       

 REPAIR VERSIONS TABLESPACE DTSG10.STEST1                   

  REPAIR VERSIONS TABLESPACE DTSG10.STEST1 2                   

  REPAIR VERSIONS TABLESPACE DTSG10.STEST1 3  

So arm the knowledge of REPAIR Utility and set for the adventure.

Cheers..

Prakash C Singh.

IBM Certified DB2 DBA

How to display the key corresponding to RID Value

Hi,

When you run CHECK DATA utility to verify the violating the RI constraints, you may come across the following error message:

DSNUKERK - ROW (RID=X'0000000202') HAS NO PARENT FOR TSG10.TES3.CTES3A

Here the hexadecimal value X'0000000202' is the Row Identifier of the key of the child table which violates the RI constraint CTES3A.

If someone tell you to find out the record corresponding key to the RID value, then you can follow this approach.

There is one stand alone utility DSN1PRNT by the help of which you can print the VSAM file data.

RID value is always associated with the index of the table.

Hence by searching the RID value in underlying index dataset( for example DSN2.DSNDBD.DTSG10.X1TES300.I0001.A001)  of violating child table, we can get hold of the key.

Here is the sample JCL: Note: always search hexadecimal value in two double quotes.


//TSG10P JOB ,
// CLASS=L,MSGCLASS=O,NOTIFY=TSG10,MSGLEVEL=(1,1)
//****************************************************************
//*
//* JCL TO PRINT HEXADECIMAL DUMP OF DB2 IMAGE COPY
//*
//****************************************************************
//*
//STEP1    EXEC PGM=DSN1PRNT,
//         PARM='FORMAT,VALUE(''0000000202'')'
//SYSUT1   DD DSN=DSN2.DSNDBD.DTSG10.X1TES300.I0001.A001,DISP=SHR
//SYSPRINT DD SYSOUT=*
//*

The subset of the output looks like below:
===
UNIQUE KEYS FOLLOW:
KEY ENTRY:  IPKMAP(XI)='0038'X
KEY:
8001
RID:
0000000201
KEY ENTRY:  IPKMAP(XI)='003F'X
KEY:
8002
RID:
4000000202
KEY ENTRY:  IPKMAP(XI)='0046'X
KEY:
8005
RID:
4000000203

DSN1994I DSN1PRNT COMPLETED SUCCESSFULLY,  00000005 PAGES PROCESSED

Note:

1) To find out the violating rows you can create the exception table and track those and at the same time you can bring the table to RW mode from Check pending status.

2) Or you can use the REPAIR utility for DUMP/DELETE the violating rows By LOCATE TABLESPACE command.

So do not be afraid of seeing the RID hex values, just play with it by the available utilities


Cheers...
Prakash C Singh
IBM Certified DB2 DBA

Get ready for DB2 X

Hey...now-a-days SPEED is the new buzz around the world.

So DB2 for z/OS leads the race in RDBMS category with announcement of beta version of DB2 10 or DB2 X on 9th, February 2010.



Here are some major points in DB2 X for z/OS

1) This version gives you the best CPU reductions for transactions and batches (i.e. saving more money)

2) Second major benefit is Scalability (Enhanced query parallelism)

3) More on demand enhancement improves availability (more online changes for Data definitions, utilities and Subsystem)


4) DBAs will be happy to find improved database performance, scalability, and availability

5) Reduced memory management,  so growth is much simpler (10 times more users by avoiding memory constraints)

6) To support regulatory compliance, DBA to get more flexible security (More granularity)





7) Warehousing continues to evolve, with key trends matching System z and DB2 for z/OS strengths of performance, scalability, reliability, stability, availability, resilience, and security (On the fly Data compression)

8) SQL, pureXML, and web services extend usability and application portability for this platform

9) More concurrency for Catalog, Utilities and SQL

10) Improved productivity for Database admin, System admin and application programmers




Cheers...
Prakash C Singh
IBM Certified Database Administrator

Modelling your production system

Hello Blog readers,


Most of the times it is a real challenge to refresh data from production environment to test env with limited time and resources. But it is very important to know the future access path to be produced in production env while testing in development environment. This is basically needed to know the future performance problem associated due to access path chosen by the Optimizer.


Here is the extract of the DB2 administration guide which will guide you how to generate the exact access path in test system with respect to production system.

SELECT DISTINCT 'UPDATE SYSIBM.SYSTABLESPACE SET NACTIVEF='
CONCAT STRIP(CHAR(NACTIVEF))
CONCAT',NACTIVE='CONCAT STRIP(CHAR(NACTIVE))
CONCAT ' WHERE NAME=''' CONCAT TS.NAME
CONCAT ''' AND DBNAME ='''CONCAT TS.DBNAME CONCAT''';'
FROM SYSIBM.SYSTABLESPACE TS, SYSIBM.SYSTABLES TBL
WHERE TS.NAME = TSNAME
AND TBL.CREATOR IN (table creator_list)
AND TBL.NAME IN (table_list)
AND (NACTIVEF >=0 OR NACTIVE >=0);

==
SELECT 'UPDATE SYSIBM.SYSTABLES SET CARDF='
CONCAT STRIP(CHAR(CARDF))
CONCAT',NPAGES='CONCAT STRIP(CHAR(NPAGES))
CONCAT',PCTROWCOMP='CONCAT STRIP(CHAR(PCTROWCOMP))
CONCAT ' WHERE NAME='''CONCAT NAME
CONCAT ''' AND CREATOR ='''CONCAT CREATOR CONCAT''';'
FROM SYSIBM.SYSTABLES WHERE
CREATOR IN (creator_list)
AND NAME IN (table_list)
AND CARDF >= 0;
==
SELECT 'UPDATE SYSIBM.SYSINDEXES SET FIRSTKEYCARDF='
CONCAT STRIP(CHAR(FIRSTKEYCARDF))
CONCAT ',FULLKEYCARDF='CONCAT STRIP(CHAR(FULLKEYCARDF))
CONCAT',NLEAF='CONCAT STRIP(CHAR(NLEAF))
CONCAT',NLEVELS='CONCAT STRIP(CHAR(NLEVELS))
CONCAT',CLUSTERRATIO='CONCAT STRIP(CHAR(CLUSTERRATIO))
CONCAT',CLUSTERRATIOF='CONCAT STRIP(CHAR(CLUSTERRATIOF))
CONCAT' WHERE NAME='''CONCAT NAME
CONCAT ''' AND CREATOR ='''CONCAT CREATOR CONCAT''';'
FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR IN (creator_list)
AND TBNAME IN (table_list)
AND FULLKEYCARDF >= 0;
==
SELECT 'UPDATE SYSIBM.SYSCOLUMNS SET COLCARDF='
CONCAT STRIP(CHAR(COLCARDF))
CONCAT',HIGH2KEY= X''' CONCAT HEX(HIGH2KEY)
CONCAT''',LOW2KEY= X''' CONCAT HEX(LOW2KEY)
CONCAT''' WHERE TBNAME=''' CONCAT TBNAME CONCAT ''' AND COLNO='
CONCAT STRIP(CHAR(COLNO))
CONCAT ' AND TBCREATOR =''' CONCAT TBCREATOR CONCAT''';'
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR IN (creator_list)
AND TBNAME IN (table_list)
AND COLCARDF >= 0;

SYSTABSTATS and SYSCOLDIST require deletes and inserts.
Delete statistics from SYSTABSTATS on the test subsystem for the specified tables
by using the following statement:
DELETE FROM (TEST_SUBSYSTEM).SYSTABSTATS
WHERE OWNER IN (creator_list)
AND NAME IN (table_list);

Use INSERT statements to repopulate SYSTABSTATS with production statistics that
are generated from the following statement:

SELECT 'INSERT INTO SYSIBM.SYSTABSTATS'
CONCAT '(CARD,NPAGES,PCTPAGES,NACTIVE,PCTROWCOMP'
CONCAT ',STATSTIME,IBMREQD,DBNAME,TSNAME,PARTITION'
CONCAT ',OWNER,NAME,CARDF) VALUES('
CONCAT STRIP(CHAR(CARD)) CONCAT ' ,'
CONCAT STRIP(CHAR(NPAGES)) CONCAT ' ,'
CONCAT STRIP(CHAR(PCTPAGES)) CONCAT ' ,'
CONCAT STRIP(CHAR(NACTIVE)) CONCAT ' ,'
CONCAT STRIP(CHAR(PCTROWCOMP)) CONCAT ' ,'
CONCAT '''' CONCAT CHAR(STATSTIME) CONCAT ''' ,'
CONCAT '''' CONCAT IBMREQD CONCAT ''' ,'
CONCAT '''' CONCAT STRIP(DBNAME) CONCAT ''' ,'
CONCAT '''' CONCAT STRIP(TSNAME) CONCAT ''' ,'
CONCAT STRIP(CHAR(PARTITION)) CONCAT ' ,'
CONCAT '''' CONCAT STRIP(OWNER) CONCAT ''' ,'
CONCAT '''' CONCAT STRIP(NAME) CONCAT ''' ,'
CONCAT STRIP(CHAR(CARDF)) CONCAT ');'
FROM SYSIBM.SYSTABSTATS
WHERE OWNER IN (creator_list)
AND NAME IN (table_list);

==
Delete statistics from SYSCOLDIST on the test subsystem for the specified tables
by using the following statement:
DELETE FROM (TEST_SUBSYSTEM).SYSCOLDIST
WHERE TBOWNER IN (creator_list)
AND TBNAME IN (table_list);

Use INSERT statements to repopulate SYSCOLDIST with production statistics that
are generated from the following statement:

SELECT 'INSERT INTO SYSIBM.SYSCOLDIST '
CONCAT '(FREQUENCY,STATSTIME,IBMREQD,TBOWNER'
CONCAT ',TBNAME,NAME,COLVALUE,TYPE,CARDF,COLGROUPCOLNO'
CONCAT ',NUMCOLUMNS,FREQUENCYF) VALUES( '
CONCAT STRIP(CHAR(FREQUENCY)) CONCAT ' ,'
CONCAT '''' CONCAT CHAR(STATSTIME) CONCAT ''' ,'
CONCAT '''' CONCAT IBMREQD CONCAT ''' ,'
CONCAT '''' CONCAT STRIP(TBOWNER) CONCAT ''' ,'
CONCAT '''' CONCAT STRIP(TBNAME) CONCAT ''','
CONCAT '''' CONCAT STRIP(NAME) CONCAT ''' ,'
CONCAT 'X''' CONCAT STRIP(HEX(COLVALUE)) CONCAT ''' ,'
CONCAT '''' CONCAT TYPE CONCAT ''' ,'
CONCAT STRIP(CHAR(CARDF)) CONCAT ' ,'
CONCAT 'X'''CONCAT STRIP(HEX(COLGROUPCOLNO)) CONCAT ''' ,'
CONCAT CHAR(NUMCOLUMNS) CONCAT ' ,'
CONCAT STRIP(CHAR(FREQUENCYF)) CONCAT ');'
FROM SYSIBM.SYSCOLDIST
WHERE TBOWNER IN (creator_list)
AND TBNAME IN (table_list);


Note about SPUFI:

• If you use SPUFI to execute the preceding SQL statements, you might need to increase the default maximum character column width to avoid truncation.
• Asterisks (*) appear in the examples to avoid having the semicolon interpreted as the end of the SQL statement. Edit the result to change the asterisk to a semicolon.

Access path differences from test to production: When you bind applications on the
test system with production statistics, access paths should be similar but still may
be different to what you see when the same query is bound on your production
system.

The access paths from test to production could be different for the following possible reasons:

• The processor models are different.
• The number of processors are different. (Differences in the number of processors can affect the degree of parallelism that is obtained.)
• The buffer pool sizes are different.
• The RID pool sizes are different.
• Data in SYSIBM.SYSCOLDIST is mismatched. (This mismatch occurs only if some of the previously mentioned steps mentioned are not followed exactly).
• The service levels are different.
• The values of optimization subsystem parameters, such as STARJOIN, NPGTHRSH, and PARAMDEG (MAX DEGREE on installation panel DSNTIP8) are different.
• v The use of techniques such as optimization hints and volatile tables are different.

Tools to help: If your production system is accessible from your test system, you
can use DB2 PM EXPLAIN on your test system to request EXPLAIN information
from your production system. This request can reduce the need to simulate a
production system by updating the catalog.

You can also use the DB2 Visual Explain feature to display the current
PLAN_TABLE output or the graphed access paths for statements within any
particular subsystem from your workstation environment. For example, if you have
your test system on one subsystem and your production system on another
subsystem, you can visually compare the PLAN_TABLE outputs or access paths
simultaneously with some window or view manipulation. You can then access the
catalog statistics for certain referenced objects of an access path from either of the
displayed PLAN_TABLEs or access path graphs.

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

Fileaid for DB2 Setup Error - FDBA623 CAF error 00F30034

Hi,

There are certain times after installation of Fileaid for DB2, users get this kind of error.

"FDBA623 CAF error 00F30034 - Plan name unauthorized."

Normally as part of installation/upgrade of Fileaid for DB2, DBA does the bind of the new plan.

As a security measure, DB2 does not allow the users to allow the access to the plan unless Execute privilege is granted to it explicitly.

Hence if the DBA forgets to grant Execute privilege, the uses will get the message like "FDBA623 CAF error 00F30034 - Plan name unauthorized." when one tries to connect to fileaid for DB2.

As a best practice for DBA, it is recommended that as soon as the bind finishes, he should give the require privileges to the users.

In this Example Granting Execute privilege for plan FDPN610 and FDOM610 to Public could have avoided the error message mentioned in subject line.

Cheers..
Prakash C Singh
IBM Certified DB2 DBA

Display current threads when you get DB2 Not Operational message

Hi Friends,

Here is one typical problem you usually get when you tried to Stop DB2 subsystem. You just fired the command to stop the DB2 subsystem, but DB2 is not coming down.

After having a thought...you may assume there are certain active dedicated threads from a started task (DB2 related application or any job). The interesting fact is when you try to DISPLAY Threads through DB2 Panels to execute DB2 commands, it  will give you a Message like DB2 Not Operational for the Subsystem.

Here is the trick:
If you know the Character for DB2 subsystem (You can get it from Zparms), you can fire the Display command from console.

For example in spool you can type

/+DIS THD(*)

Here '+' is the character for the Subsystem.

Now you can close the started task or kill the job to proceed further.

Regards,

Prakash Singh

IBM Certified DB2 DBA

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

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

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

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

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

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

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.

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.