v6.2.3 released - DB2 reorg fixes

[rore]

https://www-304.ibm.com/support/docview.wss?uid=swg27021363

Do you have experiences to share regarding the DB2 reorg?. Hopefully this will improve DB performance and slow down the growing rate. Planning to update.

Edit: I never saw the field 'Last Database Reorganization' populated on the q db f=d. Do you have a value on that field on your TSM 6.2 servers? This fixpack should resolve that.

 

[terlisimo]

Hello,

I've had some issues with DB2 reorgs slowing down server operations with large deduplicated storage pools.

It seems to me that reclamation/migration of deduplicated volumes makes a lot of changes to AF_AGGREGATED_BITFILES so a reorg would be triggered while process is still running... and this would really slow down everything, including backup operations. I did manual reorgs and runstats until now, these two options should prevent reorg from running in inappropriate time.

 

[nenifled]

I upgraded 4 instances (Running on one AIX) to 6.2.3 yesterday - and for the first time we start getting, these in the act-log:

08/25/2011 06:43:12 ANR0293I Reorganization for table ADMIN_EMAILS started.
08/25/2011 06:48:12 ANR0294I Reorganization for table ADMIN_EMAILS ended.
08/25/2011 06:58:13 ANR0293I Reorganization for table ACTIVITY_LOG started.
08/25/2011 07:03:13 ANR0294I Reorganization for table ACTIVITY_LOG ended.


In total, eight tables were reorganized in three hours.

Does anyone know how many tables need to be reoganized in total? My guess is 265, since I get 265 tables from this command:

list tables for schema tsmdb1

Did I understand it correct, that all tables (265??) need to be reorganized before the index-reorganization begins? I dont have any ANR0317I in my act-log.

 

[GregE]

I never saw the field 'Last Databas... but "Last Database Reorganization" is blank.

 

[nenifled]

I have a date in LAST_REORG field on my 16 TSM-servers. They are running version 6.3.3.7. I think that this field will be filled with a date, when all tables have been reorged once.

You could check in your act-log and see if these msgno are comming:

q act msgno=0293 begind=-30

07/15/2013 04:16:48,ANR0293I Reorganization for table SERVER_CONNECT_INFO started.

...when all tables have been reorged, you will have a date in the field LAST_REORG....(Thats my guess)

But reorg will not run on the tables, if reorg is not needed!

 

[GregE]

Ahhh that makes sense. I thought it would report any reorg attempt whether needed or not. I guess not. Thank you.

I have seen mention of doing a reorg "once a month." But how is that done? If that referring to a reorg using DB2 commands outside of TSM? Because in TSM it is a daily value.

 

[rgg]

Ahhh that makes sense. I thought it would report any reorg attempt whether needed or not. I guess not. Thank you.

I have seen mention of doing a reorg "once a month." But how is that done? If that referring to a reorg using DB2 commands outside of TSM? Because in TSM it is a daily value.

You can do offline DB2 reorgs, but that's another topic entirely. Hopefully you'll never get to a point of needing that :redface:

The TSM options just enable a window in which reorgs can occur. Not every table is hit every day...and some of the larger tables/indexes can take longer than a day. Generally if a table has been reorged, theres a timer where it won't be selected for reorg for another 20 days or something like that....maybe thats the "once a month" thing you've heard about.

Here's the big kahuna reorg technote: http://www-01.ibm.com/support/docview.wss?uid=swg21452146

-rgg

 

[tsm_dude]

20 days after upgrade I got the re-org stuff
tsm: TSM2>q db f=d

Database Name: TSMDB1
Total Size of File System (MB): 800,000
Space Used by Database(MB): 270,272
Free Space Available (MB): 529,599
Total Pages: 15,886,352
Usable Pages: 15,885,552
Used Pages: 14,252,748
Free Pages: 1,632,804
Buffer Pool Hit Ratio: 99.2
Total Buffer Requests: 1,778,975,956
Sort Overflows: 0
Package Cache Hit Ratio: 99.7
Last Database Reorganization: 08/13/13 11:07:21
Full Device Class Name: LTO4
Number of Database Backup Streams: 1
Incrementals Since Last Full: 0
Last Complete Backup Date/Time: 08/13/13 05:00:20

 

[GregE]

Ok, mine reorg'ed the first time, 3 days after the upgrade.