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,

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