online reorg

[Former Member]

Hi there!

How can I tell if there is a online reorg going on that is started from db13?

"show detail" on tablespaces and "get snapshot" from db2 cl does not show any changes in the tablespace state. Normally when reorg is runnig the state should be 0x400, but when running online reorg from db13 the state remain in 0x0000.

The reason I need to be able to check this is to know when the following online backup can start.

[Former Member · Accepted solution]

Hi Tomas

This would probably help

<a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0021997.htm">http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0021997.htm</a>

Best regards

dirk

[Former Member · Accepted solution]

Thanks but I think this only applies to db2 V9. I forgott the info regarding our db2 level which is db2 v8.2

[Former Member]

You can use this query to find if reorg is running or not

db2 "select TABLE_NAME , PAGE_REORGS, REORG_CURRENT_COUNTER , REORG_MAX_COUNTER from table (SNAPSHOT_TBREORG('<DB_NAME>',-2)) as snaptest where REORG_END is NULL"

see if it works !

[Former Member]

Hi there!

Thanks for the tip but it does not work when running reorg from SAP. the job I'm running is "REORG of TABLES in Tablespace(s)”

Regards

Tomas

[Former Member]

Tom:

If it's on UNIX, you can do a "ps -ef|grep reorg (or the job name)".

[Former Member]

Hi,

No, it is not shown in ps. In fact I just checked this but there was no process that even have the matching start time.

My approach now is to do..

find /sapmnt/[SAPid]/global/001JOBLG/* -mtime -1 -ls

and then

grep -i finished

(sapmnt/[SAPid]/global/001JOBLG/ is where the logfile you can se from db13 is located..)

Thanks anyway

Regards

Tomas

[Former Member]

This fuction is simuar as get snapshot command and when starting a reorg from db13 it does not show anything. I did this test.

db2 GET SNAPSHOT FOR TABLES ON [db_name] > file1

then I started REORG of Tables in Tablespace(s) from db13

after about 5 min (the job takes ~30min) I took a snapshot again.

db2 GET SNAPSHOT FOR TABLES ON [db_name] > file2

Then I did

diff file1 file2

The only thing that differs is timestamps and size nothing about a reorg is running.

strange?

anyway thanks for your help.

[Former Member]

Hi,

I managed to "catch" the 400 flag by creating a while loop executing the following..

db2 list tablespaces show detail | grep -p [tablespace_name] | grep -i state |grep 400

Regards

Tomas

[Former Member]

Hi Tomas

In Version 8 you could use

SNAPSHOT_TBREORG table function

>>-SNAPSHOT_TBREORG--(--dbname--,--dbpartitionnum--)-----------><
The schema is SYSPROC.

The SNAPSHOT_TBREORG function returns table reorganization information in the form of a result set. 
If no tables have been reorganized, 0 rows are returned. 
To obtain real-time snapshot information, the user must have SYSADM,SYSCTRL, or SYSMAINT authority. 

In V8 SNAPSHOT_TBREORG the REORG_STATUS is BIGINT in difference to the V9 SNAP_GET_TAB_REORG , where it is VARCHAR and more readable .

REORG_STATUS

Description

The status of an in-place (online) table reorganization. This is not applicable to classic (offline) table reorganizations.

• Started/Resumed: SQLM_REORG_STARTED

• Paused: SQLM_REORG_PAUSED

• Stopped: SQLM_REORG_STOPPED

• Completed: SQLM_REORG_COMPLETED

• Truncate: SQLM_REORG_TRUNCATE

see sqlmon.h for the corresponding values .

Hth + best regards

dirk