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')

;

[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?