In our database, there are aroud 200 tables. I need to create a Job which Reorg all Tables.
Is it essential to rebind all packages associated to these tables? What difference it will make if we rebind or if we dont rebind.
In our System, we are using 1 query to list out all tablespaces and Indexspaces to be reorged. Query is :
sqlstmt1="SELECT B.TSNAME AS C1, ' ' AS C2,",
" CAST('CLUSTERED N' AS CHAR(50)) AS C3",
"FROM SYSIBM.SYSINDEXES A, SYSIBM.SYSTABLES B",
"WHERE (A.CLUSTERING = 'Y' AND A.CLUSTERED = 'N')",
"AND FULLKEYCARD > 0",
"AND A.TBCREATOR = B.CREATOR",
"AND A.TBNAME = B.NAME",
"AND B.DBNAME = '" || db_name || "'",
"UNION",
"SELECT TSNAME AS C1, ' ' AS C2,",
" CAST('NEARINDREF+FARINDREF)*100/CARD>10' AS CHAR(50))",
" AS C3",
"FROM SYSIBM.SYSTABLEPART",
"WHERE (CARD > 0",
"AND (NEARINDREF + FARINDREF)*100/CARD > 10)",
"AND DBNAME ='" || db_name || "'",
"UNION",
"SELECT TSNAME AS C1, ' ' AS C2,",
" CAST('PERCDROP > 0' AS CHAR(50)) AS C3",
"FROM SYSIBM.SYSTABLEPART",
"WHERE PERCDROP > 0",
"AND DBNAME ='" || db_name || "'",
"UNION",
"SELECT B.TSNAME AS C1, ' ' AS C2,",
" CAST('CLUSTERRATIO < 97' AS CHAR(50)) AS C3",
"FROM SYSIBM.SYSINDEXES A, SYSIBM.SYSTABLES B",
"WHERE (A.CLUSTERING = 'Y' AND",
"CLUSTERRATIO < 97)",
"AND FULLKEYCARD > 0",
"AND A.TBCREATOR = B.CREATOR",
"AND A.TBNAME = B.NAME",
"AND B.DBNAME = '" || db_name || "'",
"UNION",
We have identified some more tables to be reorged, that are not coming into the output of above query. The Tables that are not coming into the output of above query, that are basically spanning into the multiple extents of dataset.
Can anyone help me in finding out more conditions to add in above query so that query output is perfect( It should result all tables that need reorg).
Thanks... I went thru manual for DSNACCOR, but i cud understand to very little extent.
If u already know how to run this stored proc, please do let me know....
I have been asked to not take help from our DBA. Probably he might not be aware of DB2 Stored proc DSNACCOR.
I found below desc about DSNACCOR :
DSNACCOR uses the set of criteria for recommending actions to evaluate table spaces and index spaces. By default, DSNACCOR evaluates all table spaces and index spaces in the subsystem that have entries in the real-time statistics tables. However, you can override this default through input parameters.
DSNACCOR uses following Formula to recommend a REORG on a table space.
((QueryType='REORG' OR QueryType='ALL') AND
(ObjectType='TS' OR ObjectType='ALL')) AND
(REORGLASTTIME IS NULL OR
((REORGINSERTS+REORGDELETES+REORGUPDATES)*100)/TOTALROWS>RRTInsDelUpdPct OR 1
(REORGUNCLUSTINS*100)/TOTALROWS>RRTUnclustInsPct OR 2
(REORGDISORGLOB*100)/TOTALROWS>RRTDisorgLOBPct OR 3
((REORGNEARINDREF+REORGFARINDREF)*100)/TOTALROWS>RRTIndRefLimit OR 4
REORGMASSDELETE>RRTMassDelLimit OR 5
EXTENTS>ExtentLimit)
Now, I am not sure how to run this stored Proc, or can we run above formula in the form of Query.
publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.perf/db2z_sp_dsnaccox.htm
shows a COBOL example of calling dsnaccox .
It's not that difficult if you bother to read the manuals:
Learn something about Real time statistics : Google DB2 RTS.
Learn something about DSNACCOX : Google DB2 DSNACCOX
If you don't want to use the SP you can always implement the query in your own program : the lowercase variables are variables of the SP so those you have to replace by your programs, the uppercase variables are columns of the RTS table.
If your still < DB2 9 and RTS is not set up : fire your DBA.
