Estimate log space reorg index will use

[Former Member]

Hi

We need to reorg the index of a table in order to enable Large RIDS. The index size is approaching the 16K tablespace limit, however using DB6CONV to move this table and indexes will take about 10-12 hours and we cannot afford this much downtime.

I would like to try a reorg indexes all on this table, however am concerned about the amount of log space this will use and would like to try to estimate this. Is there any way to do this? Also, is there any way to estimate how long an index reorg will take?

Thanks

Leigh

[Former Member]

Hello, Joachim

I read notes 963602 and 1039544 and try to test stored procedure ONLINE_TABLE_MOVE on my windows-sandbox with DB2 8.1.14 and NW2004s installed.

I unzip the online_table_move_sp.dll to my SQLLIB\function dir and connect to my database from DB2 CLP

Next i a try command :

db2 invoke online_table_move_sp

but this fail with error :

SQL0444N Routine "*_move_sp" (specific name "SQL071101155925640") is

implemented with code in library or path "...e_table_move_sp", function

"online_table_move_sp" which cannot be accessed. Reason code: "4".

SQLSTATE=42724

What i can doing wrong ?

I little confused with command INVOKE (i not find this command on DB2 command and sql references) and i think was external stored procedures must be registered in DB2 system catalog with sql statement CREATE PROCEDURE ...... with EXTERNAL NAME parameter ???

Also, can you please provide how this procedure work on DB2 level ??

Big thank's

With best regards, Dmitry

[Former Member]

Hello Dimitri,

I can't help you, because the SP is developed and shipped by SAP.

Please contact SAP for further information. You can open a message

for this problem.

Also I hoep the developer is reading this Community and can help you.

Best regards,

Joachim

[Former Member]

Hello Dimitri,

the command "db2 invoke online_table_move_sp" is correct.

Do you use the 32-bit (ntintel) or the 64-bit(ntamd64) version ? What DB2 version do you use 32-bit or 64-bit ?

The x86 (ntintel) DLL has an dependancy to VS 2005 DLLs (MSVCR80.DLL and MSVCP80.DLL).

If these libraries are missing on your system, you may need to install the MS Visual Studio 2005 redistributable package from

http://www.microsoft.com/downloads/details.aspx?familyid=200B2FD9-AE1A-4A14-984D-389C36F85647&displa...

Best regards, Jens

[Former Member]

Hello, Jens

Thanks for your advise

On my windows server installed Win2003 SP1 x64 and DB2 8.1.4 32-bit

I install both 32-bit and 64-bit MS VS2005 redistributable packages, but problem is not resolved

Also, i install ONLINE_TABLE_MOVE_SP on my AIX LPAR (its TSM-box for backup validation purposes with DB2 9.1.2 installed and without NS2004s)

When i calling stored procedure :

db2 => call SAPTOOLS.ONLINE_TABLE_MOVE('SAPNPD','DOKCLU', 'NPD#ONTESTD','NPD#ONTESTI','','','','MOVE')

its fail with message in db2diag.log :

SQL0552N "DB2NPD" does not have the privilege to perform operation "CHECK TRANSFER OWNERSHIP AUTHORIZATION". SQLSTATE=4250

I check the SAPNOTE 1039544 and see what same problem was fixed in version 1.2 of this proctdure, but i download archive from this note and think what it is same 1.2 version

How i can check version of my ONLINE_TABLE_MOVE_SP ? The procedure archive not contain any "readme"

Thank's

With best regards, Dmitry.

[Former Member]

Hello Dimitry,

the online table move procedure requires V8.2.2 or higher.

For the AIX problem: Instance owner DB2NPD does not have the SECADM right per default, which is required for TRANSFER OWNERSHIP calls. You need to GRANT this right explicitly. On SAP systems, it is recommended to use the stored procedure as connect user sap<sapsid>/sapr3. This avoids any TRANSFER OWNERSHIP calls.

Regards, Jens

[Former Member]

Hi Dimitry,

did you install DB2 8.1.14 or DB2 8.1.4 on your windows-sandbox?

Best regards Siegfried

[Former Member]

Ooops! Sorry for my mistake in previos post.

Of course the DB2 version on windows-sandbox is DB2 8.1.14 (Fixpak FP14SAP)

Also, I grant SECADM authority to user DB2NPD on AIX box and try using stored proc again.

But it is failed with message :

db2 => call SAPTOOLS.ONLINE_TABLE_MOVE('SAPNPD','DD03T','NPD#ONTESTD','NPD#ONTESTI','','','','MOVE')

SQL0443N Routine "ONLINE_TABLE_MOVE" (specific name "ONLINE_TABLE_MOVE") has

returned an error SQLSTATE with diagnostic text "Table layout different

(fixable)". SQLSTATE=38021

What is "Table layout different" ?

And can somebody provide what this procedure doing on DB2 level ?

Thank's

With best regards, Dmitry

[Former Member]

Hello Dimitry,

I did a test installation on a Win 2003 X64 System with DB2 V8.1.14 32 bit. DB2LEVEL shows:

DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08027" with

level identifier "03080106".

Informational tokens are "DB2 v8.1.14.2562", "SAP_17012", "WR21377_17012", and

FixPak "14".

Product is installed at "C:\PROGRA~2\IBM\SQLLIB".

Registering online_table_move_sp SP worked fine.

Please be shure, online_table_move_sp.dll can be accessed through the file system, and dependent modules are resolved. You can check this with a dependency walker (i.e. "depends.exe")

Kind regards Siegfried

[Former Member]

Hi Dimitry,

if you do have still open problems on that issue, please open an OSS message. Please provide the trace file and the DDL as described in note 1039544.

Kind regards Siegfried

[Former Member]

Hi, Siegfried

The problem on my Windows-box was solved (it was my dumb mistake with placement procedure module on "SQLLIB\function\routine" path instead of

rigth place it to "SQLLIB\function" path.

Sorry and thank's for your help!

With best regards, Dmitry

[Former Member]

Hi Leigh,

have you see SAP-Hint 963602 DB6: Moving tables online and

362325 use db2conv to execute an Online table move?

I think that's a solution for your problem...

Best regards

Joachim Müller

[Former Member]

Thanks Joachim -

Do you by any chance know how the logging works for this? Again - concerned about running out of db logs (we currently have 250 X 200Mb logs.

[Former Member]

Hi,

I cannot image that you reach the limit of your index tablespace. Index only tablespaces have a limit a 2TB (terra byte).

In version 8, the utility db2reg2large can be used to convert the index tablespace. In DB2 9 you can use ALTER TABLESPACE ... CONVERT TO LARGE.

Best regards, Jens

[Former Member]

Hi Jens

We have recently upgraded to DB2 V9.1 We did not use the db2reg2large in V8. We have issued the alter tablespace ... convert to large, however thought we would have to reorg the indexes for it to take effect. This is a 16K tablespace, containing only one index (201GB). The table is the one that is 170GB.

Are you saying that because it is an index only tablespace, it does not have the same limit as a normal table tablespace? I know with DB2 V9 the tablespace limits are extended (therefore for a 16K page to to 8TB), however an index reorg is required to enable this after you convert the tablespace to large. Is this not applicable for an index only tablespace? Can you point me to further information regarding this?

Thanks

Leigh

[Former Member]

Hi,

right, the 256GB limit for 16K tablespaces is the limit for the data tablespace. If you have separate long data and index-only tablespaces, they can use up to 2TB in size, in addition.

I don't know why this DB2 V8 feature is so well hidden. The only documentation I found about db2reg2large is in OSS note 362325. The fact that db2reg2large is obsolete in DB2 9 and that is has been replaced by "ALTER TABLESPACE ... CONVERT TO LARGE" is documented in the DB2 9 documentation.

The large RID support of DB2 9 allows that the (regular) data part of an table can overcome the well known limits of DMS tablespaces (256GB for 16K). This change requires the recreation of the indexes, because all RIDs in the indexes need to be changed from 31 bytes to 42 bytes.

To avoid that the index rebuild becomes an offline operation you can use the online table move method as mentioned by Joachim.

Regards, Jens