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_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