Using ADMIN_TABLE_MOVE or DB6CONV to move VBDATA

[Former Member]

When trying to correct DB2 DB Deadlocks on VBDATA using saptools procedure (SAP Note1430621) we received the following error from the db2 command prompt

db2 => call saptools.online_table_move('SAPWAD','VBDATA','WAD#VBDATAD','WAD#VBDATAI','','','','','"VBKEY" VARCHAR(96) NOT NULL WITH DEFAULT '' '' ,"VBMODCNT" INTEGER NOT NULL WITH DEFAULT 0 ,"VBBLKNO" INTEGER NOT NULL WITH DEFAULT 0 ,"VBLEN" SMALLINT NOT NULL WITH DEFAULT 0 ,"VBDATA" VARCHAR(30000) FOR BIT DATA ','MOVE');

SQL0440N No authorized routine named "SAPTOOLS.ONLINE_TABLE_MOVE" of type

"PROCEDURE" having compatible arguments was found. SQLSTATE=42884

and when using the same syntax within a .sql script receive the error:

jq03a010:db2wad 58> ./onlinemove.sql

./onlinemove.sql: Syntax error at line 1: `(' not expected.

Instead of having to use the ADMIN_TABLE_MOVE procedure, can we use DB6CONV to move special flag "VBDATA" VARCHAR(30000) FOR BIT DATA ', as shown in the script below:

call saptools.online_table_move(

'SAPWAD',

'VBDATA',

'WAD#VBDATAD',

'WAD#VBDATAI',

'',

'',

'',

'',

'"VBKEY" VARCHAR(96) NOT NULL WITH DEFAULT '' '' ,

"VBMODCNT" INTEGER NOT NULL WITH DEFAULT 0 ,

"VBBLKNO" INTEGER NOT NULL WITH DEFAULT 0 ,

"VBLEN" SMALLINT NOT NULL WITH DEFAULT 0 ,

"VBDATA" VARCHAR(30000) FOR BIT DATA ',

'MOVE')

;

[Former Member]

With this error from SE38 report DB6CONV;

SQL2104N  The ADMIN_MOVE_TABLE procedure could not be completed

at this time by this user.  Reason code: "9".  SQLSTATE=5UA0M

Answer is: select the failed tables one-by-one, and click on the RESET button, then select the main job of the Tablespace and click on RESET button. Then select all the entries and DELETE them.

Click on the "New Conversion" button to reprocess the failed entries. Select the source and target Tablespace, then the target Index and L. Choose the Online radio button and SAVE. Now the tables are being reprossesed, problem solved.

Cheers, Kabelo T.

[former_member265525]

Hi Natasha,

I would need the IBM page as well to double-check

But could you as a first step execute the following query:

select key, varchar(value, 50) from systools.admin_move_table where tabname = 'VBDATA'

that should give us the current protocol entry for the table.

Best Regards

Carola

[Former Member]

Hi Carola,

That query wasn't working, the parenthesis aren't placed right and I tried giving the results you were looking for but came up with nothing. It seems this error is related to

24

A table space for regular data, large object (LOB) data, or

indexes was specified but not all three of these table spaces

were specified.

24

Specify all three table space parameters (regular data, large

object data, and index) or none of these parameters.

However, when I create a regular tablespace since it's looking for 3 parameters (for 3 tablespaces) and add this to the script you provided it doesn't like the syntax again. I created

Tablespace ID = 39

Name = WAD#REGULAR

Type = Database managed space

Contents = All permanent data. Regular table space.

State = 0x0000

Detailed explanation:

Normal

with an INITIALSIZE 100 M MAXSIZE 1 G. I added it as such:

call sysproc.admin_move_table

('SAPWAD','VBDATA',

'WAD#REGULAR',

'WAD#VBDATAD',

'WAD#VBDATAI','','','','',

'"VBKEY" VARCHAR(96) NOT NULL WITH DEFAULT '' '' ,

"VBMODCNT" INTEGER NOT NULL WITH DEFAULT 0 ,

"VBBLKNO" INTEGER NOT NULL WITH DEFAULT 0 ,

"VBLEN" SMALLINT NOT NULL WITH DEFAULT 0 ,

"VBDATA" VARCHAR(30000) FOR BIT DATA ',

' ', 'MOVE');

[former_member265525]

Hi Natasha,

as you have V9.7, you should use the admin_move_table instead of the online_table_move.

For the admin_move_table the command needs to be changed a little bit:

call sysproc.admin_move_table

('SAPWAD','VBDATA',

'WAD#VBDATAD',

'WAD#VBDATAI','','','','',

'"VBKEY" VARCHAR(96) NOT NULL WITH DEFAULT '' '' ,

"VBMODCNT" INTEGER NOT NULL WITH DEFAULT 0 ,

"VBBLKNO" INTEGER NOT NULL WITH DEFAULT 0 ,

"VBLEN" SMALLINT NOT NULL WITH DEFAULT 0 ,

"VBDATA" VARCHAR(30000) FOR BIT DATA ',

' ', 'MOVE');

The main different is the empty ' ' before 'MOVE'.

Let us know if it works

Best Regards

Carola

[Former Member]

Hi Carola,

This looks to be the correct syntax!!! However, I received this error now:

call sysproc.admin_move_table ('SAPWAD','VBDATA', 'WAD#VBDATAD', 'WAD#VBDATAI','','','','', '"VBKEY" VARCHAR(96) NOT NULL WITH DEFAULT '' '' , "VBMODCNT" INTEGER NOT NULL WITH DEFAULT 0 , "VBBLKNO" INTEGER NOT NULL WITH DEFAULT 0 , "VBLEN" SMALLINT NOT NULL WITH DEFAULT 0 , "VBDATA" VARCHAR(30000) FOR BIT DATA ', ' ', 'MOVE')

SQL2105N The ADMIN_MOVE_TABLE procedure could not be completed because a

prerequisite for running the procedure was not satisfied. Reason code: "24".

SQLSTATE=5UA0M

All the IBM links to the SQL2105N error can't be displayed, therefore not sure what prerequisite I am missing.

Thanks for all your help!

[Frank-Martin]

Hi,

the long tablespace parameter needs to be filled.

call sysproc.admin_move_table

('SAPWAD','VBDATA',

'WAD#VBDATAD',

'WAD#VBDATAI',

'WAD#VBDATAD','','','',

'"VBKEY" VARCHAR(96) NOT NULL WITH DEFAULT '' '' ,

"VBMODCNT" INTEGER NOT NULL WITH DEFAULT 0 ,

"VBBLKNO" INTEGER NOT NULL WITH DEFAULT 0 ,

"VBLEN" SMALLINT NOT NULL WITH DEFAULT 0 ,

"VBDATA" VARCHAR(30000) FOR BIT DATA ',

' ', 'MOVE');

should do the trick. If you need help with a DB2 error code use:

db2 " ? SQL2105N "

Regards

Frank

[Frank-Martin]

Hi Natasha,

I believe some escape chars need to be added to the default value in the column list since they are part of a SQL string. I am checking with the note autor.

call saptools.online_table_move(

'SAPWAD',

'VBDATA',

'WAD#VBDATAD',

'WAD#VBDATAI',

'',

'',

'',

'',

'"VBKEY" VARCHAR(96) NOT NULL WITH DEFAULT '''' '''' ,

"VBMODCNT" INTEGER NOT NULL WITH DEFAULT 0 ,

"VBBLKNO" INTEGER NOT NULL WITH DEFAULT 0 ,

"VBLEN" SMALLINT NOT NULL WITH DEFAULT 0 ,

"VBDATA" VARCHAR(30000) FOR BIT DATA ',

'MOVE')

;

Please alo note that on DB2 V9.7 as a default a BLOB type with inlining is used for the column VBDATA in table VBDATA.

Compared to the VARCHAR(30000) workaround described in this note that requires a 32K tablespace and a 32K bufferpool, LOB inlining provides similar performance improovements for data values shorter that the specified inline length.

Regards

Frank

[Frank-Martin]

Sorry. Please ignore my last answer. The syntax is correct. The corresponding ADMIN-MOVE_TABLE syntax is:

call SYSPROC.ADMIN_MOVE_TABLE(

'SAPWAD',

'VBDATA',

'WAD#VBDATAD',

'WAD#VBDATAI',

'',

'',

'',

'',

'"VBKEY" VARCHAR(96) NOT NULL WITH DEFAULT '' '' ,

"VBMODCNT" INTEGER NOT NULL WITH DEFAULT 0 ,

"VBBLKNO" INTEGER NOT NULL WITH DEFAULT 0 ,

"VBLEN" SMALLINT NOT NULL WITH DEFAULT 0 ,

"VBDATA" VARCHAR(30000) FOR BIT DATA ',

'',

'MOVE')

;

Regards

Frank

Edited by: Frank-Martin Haas on Mar 1, 2012 9:52 AM

[malte_schuenemann]

If you are on DB2 9.7, you need to use admin_move_table (AMT), instead of online_table_move (OTM). Note that the syntax is slightly different.

However, the command line given at the beginning uses a trailing semicolon - how did you call the DB2 CLP there ? If you just called it as "db2", then you need to omit the tailing semicolon. If you put the command into an SQL file, you need to run this e.g. as

db2 -tvf onlinetablemove.sql -z onlinetablemove.log

Here, option -t is required to observe the semicolon as terminating character. Option .z specifies the logfile to append the output to.

What do you get from this ?

Another questions is about the version of DB2 you are using.

Are you sure that OTM is installed ?

Malte

[Former Member]

Hi Malte,

I have used db2-tvf also and received the same error when doing it from the db2 prompt, which is:

SQL0440N No authorized routine named "SAPTOOLS.ONLINE_TABLE_MOVE" of type

"PROCEDURE" having compatible arguments was found. SQLSTATE=42884.

We are using

DB21085I Instance "db2wad" uses "64" bits and DB2 code release "SQL09074" with

level identifier "08050107".

Informational tokens are "DB2 v9.7.0.4", "special_27750", "IP23236_27750", and

Fix Pack "4".

I have confirmed with SAP that we have the proper tablespaces to use admin_table_move procedure, however do you know of another way to be sure this is installed properly?

[former_member265525]

Hi Natascha,

which special flag do you want to remove?

Regards

Carola

[former_member265525]

Hi Natasha,

the syntax of your calls is wrong (for the admin_move_table as well as for the online_table_move). Why do you not use the DB6CONV to move the tables? This would be the easiest and savest way.

Best Regards

Carola

[Former Member]

Hi Carola,

Do you know the correct syntax? I have been testing different scripts and nothing works, this is what an SAP Note states but it isn't correct. Can we use DB6CONV to move special flag "VBDATA" VARCHAR(30000) FOR BIT DATA '? I have been told this was not guaranteed, do you understand otherwise?

[Former Member]

Hello Natasha,

did you ever use the admin_move_table function in your system? For this you can check the function list of database and if function not shown you can use the command "db2 invoke db2sap" to activate the function.

Regards

Olaf Balzer

[Former Member]

Hi Olaf,

Thanks, I have executed that command and I am still having issues.