BALDAT online reorganization

[Former Member]

Hello,

I am about to start an online reorganization of table BALDAT with the following command on OS level:

SBMJOB CMD(RGZPFM FILE(R3P00DATA/BALDAT) KEYFILE(RPLDLTRCD) RBDACCPTH(NO) ALWCANCEL(YES) LOCK(SHRUPD)) JOB(RGZONL_P00) JOBQ(QINTER)

Since we're talking about the application log table, do you know if there's any problem on reorganizing it online or should it be done offline? Our offline window is only 2 hours every sunday...

thanks

Antonio

[former_member188883 · Accepted solution]

Hi,

How big is your BALDAT table ?

Regards,

Deepak Kori

[Former Member · Accepted solution]

Hi,

thanks for answering...

BALDAT is 199GB in size.

[former_member188883 · Accepted solution]

Hi Antonio,

An offline reorg will complete faster than an online reorg but the tables are not vailable for other transactions to access while they are being reorged. In an online reorg, the table remains accessible but the reorg will take longer to complete.

Regarding space requirements for the reorg:

1. offline with temporary table space

Yes this will PSAPTEMP, I cant give a definite size but 2.5 times the

size of the table would be a good indicator, so for 20Gb table, expect

PSAPTEMP tabelspace to grow to around 40 - 60 GB. Ensure their is

sufficient space on filesystem level for PSAPTEMP to grow.

As it is offline, the tables will be unavailable to other jobs while

the reorg is running on them, but the reorg will complete quicker than

online (I cannot give an estimate on how long exactly)

2.offline with out temporary table space

This will not use PSAPTEMP tablespace but will be reorganised within

the tablespace where the table currently resides. Again you will need

to ensure you have sufficent size (2.5 times the table size)

Based on experience,. most customers do not use this option as it is

easier to allow PSAPTEMP to grow and release the space after the reorg

is complete.

For 199GB data table the downtime of 2 hrs is not feasible. You need to go for the online reorg.

You can also pause or stop the online reorg from db02 itself if any performance degradation is witnesses.The online reorg in db2 is also termed as inpalce reorg and can be started from db02.

Hope this information is useful.

Regards,

Deepak Kori

[former_member188883 · Accepted solution]

Hi Antonio,

Additionally you can schedule program SBAL_DELETE to delete all application log entries which are not useful.

This will help in reducing the number of entries in the table and thereby reducing the reorg timelines.

Regards,

Deepak Kori

[Former Member · Accepted solution]

Hi Antonio,

If data deletion causes concerns, you could archive the application log with archiving object BC_SBAL - The logs will be readable when needed.

If certain types of logs don't have any value at all, there might be ways to turn off those logs.

Best regards,

Victor

[christian_bartels · Accepted solution]

Hi Deepak,

that answer may be valid for other databases, but for DB2 on IBM i it is not. Data is not organized in table spaces on IBM i, and the reorganization cannot be controlled through transaction DB02.

For table reorganization on IBM i, see SAP Note 84081. When doing an online reorganization, you have to consider two things:

1. For tables containing variable length data (VARCHAR, VARGRAPHIC, VARBINARY, BLOB, CLOB or DBCLOB type columns), the online reorganization does not free up as much space as the offline reorganization.

2. The online reorganization moves data from the end of the table to gaps in the middle of the table that were left when other rows were deleted. If you access the table while it is being reorganized (SELECT ... FROM TABLE WHERE ... WITH UR), some of the rows that are being moved may be missed. If your application logic cannot tolerate that and you cannot prevent users from running the application while the reorganize is running, you should do an offline reorganization.

The run time of the reorganization (online or offline) depends heavily on the hardware resources (disk speed, main storage size), so it is more or less unpredictable. The only way to find out is to try on a test system with similar resources.

Kind regards,

Christian Bartels.

[Former Member · Accepted solution]

Hi Christian, thanks for the answer....

I knew about that note but it doesn't completely answers my question about the BALDAT table specifically. Due to the fact that this table is being used by the SAP system to store the application log, one can assume it will not be used with SELECT statements but rather with INSERT statements, right? At the same time, it is a heavily used table due to its nature...

My main concern is with the system performance while the online reorganization is running. I don't think the offline window we have available every sunday will be enough to reorganize the entire BALDAT table, although we have a lot of resources available.

We have, however, a QAS system available with much less resources than the productive system, so one of two tests can be done:

- Online reorganization of the BALDAT table to have an idea of the performance impact it might have (although the system load is far from what is experienced on production)

- Offline reorganization of the BALDAT table to have an idea of how much time would it take to run

Thanks for all the answers!

Antonio

[christian_bartels · Accepted solution]

Hi Antonio,

just one question: If you think that there are mainly INSERT operations on table BALDAT, then why would you want to reorganize it? Does it show very many deleted rows that you don't expect to be reused any time soon? Or does the LRAW column CLUSTD use unusually much space? If the latter, the offline reorganization would be the only option that would really clean up the unused space.

Kind regards,

Christian Bartels.

[Former Member]

thanks for the answers....