DAtabase Reorg

tsmadmin04

Active Newcomer
Joined
Jul 10, 2012
Messages
26
Reaction score
0
Points
0
PREDATAR Control23

HI, I have a question regarding database reorganisation option in database.
earlier my data base has reorganisation daily, but today i see that it has been 4 days for last database reorganision.
From last 4 days there is no database reorganisation.
Can any one here please tell me the about database reorganisation.
if it doesnt taken place then does it effect performance of server.
To make database reorganisation what steps should i have to do.
 
PREDATAR Control23

HI, I have a question regarding database reorganisation option in database.
earlier my data base has reorganisation daily, but today i see that it has been 4 days for last database reorganision.
From last 4 days there is no database reorganisation.
Can any one here please tell me the about database reorganisation.
if it doesnt taken place then does it effect performance of server.
To make database reorganisation what steps should i have to do.

TSM schedules itself to run DB reorganization as needed. If no reorganization has run for the last few days, then it does not need it.

In other words, no worries.

To view set start time and duration for reorganization, do a 'q option' and look for REORGBEGINTIME and REORGDURATION.
 
Last edited:
PREDATAR Control23

TSM schedules itself to run DB reorganization as needed. It no reorganization has run for the last few days, then it does not need it.

In other words, no worries.

To view set start time and duration for reorganization, do q 'q option' and look for REORGBEGINTIME and REORGDURATION.

I was worried until i got information from you, after seeing your reply i got relaxed.

Thanks, I verified dsmserv.opt for REORGBEGINTIME and REORGDURATION. It is showing that REORGBEGINTIME is time in HH:MM and REORGDURATION is a number. Actually my DB backup should have complete before REORGBEGINTIME but other processes are delaying server DB backup. Whilst at the time of REORGBEGINTIME my DB backup is running, i thought may be DBbackup is not allowing it to do the reorganisation. So i delayed the DBbackup and not to start until REORGBEGINTIME, and make my DB backup to run after REORGBEGINTIME. Wonder now the database reorganisation is showing today date.
 
PREDATAR Control23

Hello All,

Even I am getting below error for during DB Reorg and DB reorg is not running which is very concern... could you please help me in resolving the issue

ANR3497W Reorganization is required on excluded table The reason code is 2..

below options are in the TSM server .OPT..

FFDCLOGNAME dsmserv.err ALLOWREORGTABLE Yes
ALLOWREORGINDEX No REORGBEGINTIME 05:00
REORGDURATION 12 DisableReorgTable
DisableReorgIndex BF_AGGREGATED_BITFI- DisableReorgClea-
LES,BF_BITFILE_EXT- nupIndex
ENTS,BACKUP_OBJECT-
S,ARCHIVE_OBJECTS
 
PREDATAR Control23

ANR3497W is not an error, it's a warning, your system is still working fine, it's just that a table needs reorg, but reorg is disabled on that table.

You can either remove the table from the exclude and hope that it can do an online reorg, or schedule an offline reorg of that table.

More info here: http://www-01.ibm.com/support/docview.wss?uid=swg21452146 Search for ANR3497W in that text.
 
PREDATAR Control23

Yes, my TSM server is running fine for now.. i will try to run the offline DB Reorg which is faster than online..
Even my DB Reorg is not running on the server..
 
PREDATAR Control23

Hi!

I was wondering what the "best practices" are regarding offline reorgs. I've just finished a reorg for one of my problematic tables, BF_AGGREGATED_BITFILES. Which reduced the database size from 2.1 TB down to 1.5 TB. We are running heavy replication and deduplication is keeping steady at about 60%. These tables can't (as far as i know?!) be included in the online reorg.

Seeing as this is the first offline reorg i'm doing on the server i'm curious on how often i can expect to be doing the offline maintenance? A rough estimate is that there is about 5 TB of new data being backed up each day, and as i said the database was previously at 2.1 TB, down to 1.5 TB now after the first table reorg finished. Perhaps a hard question to answer but anyone with similar stats that can throw out a guess?
 
PREDATAR Control23

I have a similar workload (~4 TB a day) and I have to run offline reorgs of BT_AGGREGATED_BITFILES and BT_BITFILE_EXTENTS every six months or so.
 
PREDATAR Control23

Is it possible to enable online reorganization of tables BF_AGGREGATED_BITFILES,BF_BITFILE_EXTENTS,BACKUP_OBJECTS,ARCHIVE_OBJECTS which were excluded by TSM server upgrade.
Any one found any document related to this question?
 
PREDATAR Control23

Is it possible to enable online reorganization of tables BF_AGGREGATED_BITFILES,BF_BITFILE_EXTENTS,BACKUP_OBJECTS,ARCHIVE_OBJECTS which were excluded by TSM server upgrade.
Any one found any document related to this question?
Yes it's possible, just need to edit dsmserv.opt to remove them from the list. However they were excluded for a reason and it's not recommended to do an online reorg of these.
 
PREDATAR Control23

Is it possible to enable online reorganization of tables BF_AGGREGATED_BITFILES,BF_BITFILE_EXTENTS,BACKUP_OBJECTS,ARCHIVE_OBJECTS which were excluded by TSM server upgrade.
Any one found any document related to this question?

In my experience, do not enable reorganization for these tables. TSM DB2 takes care of these tables outside of the reorganization process.
 
PREDATAR Control23

Hi,
My TSM DB size is 8TB and support has advice to run offline DB reorg which help us to save Total estimated 1827 GB. Could you please share your experience , what would be the implication? is there any chance to corrupt the TSM DB? Time taken to complete the reorg? etc

For your reference here is the summary


If SD_CHUNK_LOCATIONS were to be off line reorganized the estimated savings is Table 22 GB, Index 535 GB
If BACKUP_OBJECTS were to be off line reorganized the estimated savings is Table 26 GB, Index 28 GB
If SD_RECON_ORDER were to be off line reorganized the estimated savings is Table 22 GB, Index 356 GB
If GROUP_LEADERS were to be off line reorganized the estimated savings is Table 14 GB, Index 0 GB
If SD_REPLICATED_CHUNKS were to be off line reorganized the estimated savings is Table 6 GB, Index 204 GB
If BF_AGGREGATED_BITFILES were to be off line reorganized the estimated savings is Table 35 GB, Index 225 GB
If SD_NON_DEDUP_LOCATIONS were to be off line reorganized the estimated savings is Table 2 GB, Index 29 GB
If ACTIVITY_LOG were to be off line reorganized the estimated savings is Table 8 GB, Index 0 GB
If BF_DEREFERENCED_CHUNKS were to be off line reorganized the estimated savings is Table 0 GB, Index 2 GB
If BF_BITFILE_EXTENTS were to be off line reorganized the estimated savings is Table 55 GB, Index 129 GB
If AS_SEGMENTS were to be off line reorganized the estimated savings is Table 0 GB, Index 1 GB
If SD_REFCOUNT_UPDATES were to be off line reorganized the estimated savings is Table 9 GB, Index 114 GB
If AF_BITFILES were to be off line reorganized the estimated savings is Table 0 GB, Index 1 GB
If TSMMON_STATUS were to be off line reorganized the estimated savings is Table 3 GB, Index 0 GB
If AF_SEGMENTS were to be off line reorganized the estimated savings is Table 0 GB, Index 1 GB
If ACTIVITY_SUMMARY were to be off line reorganized the estimated savings is Table 0 GB, Index 0 GB
If BF_AGGREGATE_ATTRIBUTES were to be off line reorganized the estimated savings is Table 0 GB, Index 0 GB
If ARCHIVE_OBJECTS were to be off line reorganized the estimated savings is Table 0 GB, Index 0 GB

Total estimated savings 1827 GB
 
PREDATAR Control23

Anything with SD_ in the name I would not reorg UNLESS IBM support tells you to explicitly tells you to do so. If you are working with Level one support, I'd just verify that they really want you to touch SD_*. Perhaps escalate to Level 2.
"... Offline table reorganization is not necessary for table names starting with SD, for example, SD_REFCOUNT_UPDATES. (If you believe that offline reorganization is necessary for a table that starts with SD, contact IBM Software Support for guidance before you proceed with the reorganization. -- in doc: https://www-01.ibm.com/support/docview.wss?uid=swg21683633"
Perhaps some of the people here can chime in on that...


As to time, I've a 2TB db and BF_AGGREGATED_BITFILES takes around 2 hours if I recall correctly. All the other's take very little time (at least in my experience). My DB's on SSD but I have to carve space on 7200 spin drives to handle the temp space for the reorg. Which slows me down.

Always do a FULL database backup before you perform any offline reorg work. I cannot stress this enough. If something does go south, you have a very recent restore point.
 
PREDATAR Control23

Thank you for your feedback
Here is total no. of Rows for each table space. Do you have any idea how long does it takes for re-org below table space?

1520677870148.png
 
PREDATAR Control23

Do you have any idea how long does it takes for re-org below table space?
Varies too much from system to system. Do the smallest table and then extrapolate for the larger ones.
 
PREDATAR Control23

Hi,
My TSM DB size is 8TB and support has advice to run offline DB reorg which help us to save Total estimated 1827 GB. Could you please share your experience , what would be the implication? is there any chance to corrupt the TSM DB? Time taken to complete the reorg? etc

For your reference here is the summary


If SD_CHUNK_LOCATIONS were to be off line reorganized the estimated savings is Table 22 GB, Index 535 GB
If BACKUP_OBJECTS were to be off line reorganized the estimated savings is Table 26 GB, Index 28 GB
If SD_RECON_ORDER were to be off line reorganized the estimated savings is Table 22 GB, Index 356 GB
If GROUP_LEADERS were to be off line reorganized the estimated savings is Table 14 GB, Index 0 GB
If SD_REPLICATED_CHUNKS were to be off line reorganized the estimated savings is Table 6 GB, Index 204 GB
If BF_AGGREGATED_BITFILES were to be off line reorganized the estimated savings is Table 35 GB, Index 225 GB
If SD_NON_DEDUP_LOCATIONS were to be off line reorganized the estimated savings is Table 2 GB, Index 29 GB
If ACTIVITY_LOG were to be off line reorganized the estimated savings is Table 8 GB, Index 0 GB
If BF_DEREFERENCED_CHUNKS were to be off line reorganized the estimated savings is Table 0 GB, Index 2 GB
If BF_BITFILE_EXTENTS were to be off line reorganized the estimated savings is Table 55 GB, Index 129 GB
If AS_SEGMENTS were to be off line reorganized the estimated savings is Table 0 GB, Index 1 GB
If SD_REFCOUNT_UPDATES were to be off line reorganized the estimated savings is Table 9 GB, Index 114 GB
If AF_BITFILES were to be off line reorganized the estimated savings is Table 0 GB, Index 1 GB
If TSMMON_STATUS were to be off line reorganized the estimated savings is Table 3 GB, Index 0 GB
If AF_SEGMENTS were to be off line reorganized the estimated savings is Table 0 GB, Index 1 GB
If ACTIVITY_SUMMARY were to be off line reorganized the estimated savings is Table 0 GB, Index 0 GB
If BF_AGGREGATE_ATTRIBUTES were to be off line reorganized the estimated savings is Table 0 GB, Index 0 GB
If ARCHIVE_OBJECTS were to be off line reorganized the estimated savings is Table 0 GB, Index 0 GB

Total estimated savings 1827 GB

Hello,

I'm going through similar issues with growing DB. I was wondering how did you get the information above? Is there a command or did support provide it?

Thanks,
 
PREDATAR Control23

No worries.
Best to read the whole page linked and get an idea of what you are looking at. Now, you didn't mention what version of TSM Server you are running so keep in mind that its for 7.1.1.200 and later servers.
 
PREDATAR Control23

No worries.
Best to read the whole page linked and get an idea of what you are looking at. Now, you didn't mention what version of TSM Server you are running so keep in mind that its for 7.1.1.200 and later servers.

Perfect thank you. We are at 8.1.5. I'm working support on this as well but find 1st level is not forthcoming in suggesting solutions. I need to ask the right questions which this thread has helped a lot with.

Cheers,
 
Top