Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Reorg Multiple Tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sajjan jindal
Warnings : 1

New User


Joined: 09 Sep 2007
Posts: 60
Location: india

PostPosted: Mon Sep 06, 2010 10:49 am    Post subject: Reorg Multiple Tables
Reply with quote

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.

Can anyone please put some light on this?

Regards,
Sajjan
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Sep 06, 2010 12:08 pm    Post subject:
Reply with quote

try to answer these questions :
What does a rebind do?
Rebind bases its decisions on ... ?
Does a Reorg affects these ?
Back to top
View user's profile Send private message
sajjan jindal
Warnings : 1

New User


Joined: 09 Sep 2007
Posts: 60
Location: india

PostPosted: Mon Sep 06, 2010 3:04 pm    Post subject: Reply to: Reorg Multiple Tables
Reply with quote

Thanks, will try to reply all above questions.
Back to top
View user's profile Send private message
sajjan jindal
Warnings : 1

New User


Joined: 09 Sep 2007
Posts: 60
Location: india

PostPosted: Mon Sep 06, 2010 3:25 pm    Post subject: Reply to: Reorg Multiple Tables
Reply with quote

I have 1 more query regarding Reorg,

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",

"SELECT Z.TSNAME AS C1, ' ' AS C2,",
" CAST('FAROFFPOSF*100/CARDF > 10' AS CHAR(50)) AS C3",
"FROM SYSIBM.SYSINDEXPART X, SYSIBM.SYSINDEXES Y,",
"SYSIBM.SYSTABLES Z",
"WHERE X.IXCREATOR = Y.CREATOR",
"AND X.IXNAME = Y.NAME",
"AND Y.TBNAME = Z.NAME",
"AND Y.TBCREATOR = Z.CREATOR",
"AND Y.CLUSTERING = 'Y'",
"AND X.CARDF > 0",
"AND (X.FAROFFPOSF*100/X.CARDF > 10)",
"AND Y.DBNAME = '" || db_name || "'",

"UNION",

"SELECT Z.TSNAME AS C1, Y.INDEXSPACE AS C2,",
" CAST('LEAFDIST > 200' AS CHAR(50)) AS C3",
"FROM SYSIBM.SYSINDEXPART X, SYSIBM.SYSINDEXES Y,",
"SYSIBM.SYSTABLES Z",
"WHERE X.IXCREATOR = Y.CREATOR",
"AND X.IXNAME = Y.NAME",
"AND Y.TBNAME = Z.NAME",
"AND Y.TBCREATOR = Z.CREATOR",
"AND X.CARDF > 0",
"AND (X.LEAFDIST > 200 )",
"AND Y.DBNAME = '" || db_name || "'",
"ORDER BY 1,2",
"FOR FETCH ONLY


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 in advance.


Regards,
Sajjan
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Sep 06, 2010 5:41 pm    Post subject:
Reply with quote

As far as I can see your queries are based on catalog statistics, which means this will only be accurate if you runstats enough.

why don't you use DSNACCOX?
At least read the documentation about dsnaccox, it would give you some clues and formula's.
Back to top
View user's profile Send private message
sajjan jindal
Warnings : 1

New User


Joined: 09 Sep 2007
Posts: 60
Location: india

PostPosted: Tue Sep 07, 2010 9:01 am    Post subject:
Reply with quote

Hi,

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....


Regards,
Sajjan
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 07, 2010 9:08 am    Post subject:
Reply with quote

Hello,

Post what you found in the manual that is not clear and your doubt. Someone should be able to clarify.

Suggest until it is clear, you not run things.

Have you talked with your dba about this?
Back to top
View user's profile Send private message
sajjan jindal
Warnings : 1

New User


Joined: 09 Sep 2007
Posts: 60
Location: india

PostPosted: Tue Sep 07, 2010 9:54 am    Post subject:
Reply with quote

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.

Please help me, its becoming very urgent for me.

Regards,
Sajjan
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Sep 07, 2010 4:37 pm    Post subject:
Reply with quote

http://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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts Converting multiple VB files to FB fi... Viswanath Reddy JCL & VSAM 6 Mon Aug 08, 2016 11:49 pm
No new posts Execute multiple DB2 Load commands in... faizm DB2 4 Wed Aug 03, 2016 12:53 pm
This topic is locked: you cannot edit posts or make replies. JCL MULTIPLE STEP EXECUTION QUERY Susanta JCL & VSAM 18 Sat Jul 30, 2016 1:17 pm
No new posts ISPF Tables Creation newsysprg TSO/ISPF 6 Wed Jul 13, 2016 2:21 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us