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