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

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.

How to Revoke SYSADM previlege without cascading effect

Installation system admin have given SYSADM authority to a userid (USR10). Through that userid, the user created many objects, had bind many packages and given many privileges.
Now when Installation system admin tried to revoke the SYSADM auth from that user, the mainframe screen seems to be hanged and locked for hours. Even the subsystem might be crashed.

How to Approach this problem..

We made the Userid(USR10) as Installation SYSADM id through one job which make the change in Z-Parm of DB2 subsystem.
Here is the member:
D710.DSN4.SDSNSAMP(DSNTIJUZ) -- second qualifier is the subsystem name.
and parameters are
SYSADM=USR10, -- put userid over here from which you want to revoke the SYSADM auth.
SYSADM2=USR10,

you need to stop/start the subsystem to make it effect. After that Shoot the REVOKE statement. This will be executed in a second. After this do not forgot to change the SYSADM to it's initial value.

Catch is When you revoke SYSADM from a installation sysadm, you won't have cascading effect.



Regards,
Prakash
IBM Certified DB2 DBA