SAP Note 21495 - Transparent update tables

Symptom:

Update tables VBHDR, VBMOD, and VBDATA are in cluster VBLOG in the R/3 standard system. You want to make these tables transparent.

Cause and prerequisites

An update request exceeds the maximum permissible size for one cluster record of 7.5 MB.

Solution

Attention !!! Attention !!! Attention !!!
-----------------------------------------------------------------------
If you are using INFORMIX as database system, proceed as described in
Note 23568 to solve the above problem.
----------------------------------------------------------------------
When you use R/3 systems based on the MS-SQL Server database system,
a considerable loss of performance can be expected after converting to
transparent update tables. Contact the SAP hotline in this case.
-----------------------------------------------------------------------

To make the update tables transparent, perform the following steps. No actions other than those described are necessary!

1. Through organizational means, make sure that no new update requests can be written (for example, by starting R/3 in single-user mode).

2. Make sure all update requests have been processed and table VBLOG is empty. You can do so either directly with database means (ORACLE: "sqlplus", ADABAS-D: "xquery", DB2/400: "strsql" or "sqltool", MSSQL: "isql/w", DB2forAIX: "db2") with the Statement "SELECT COUNT(*) FROM VBLOG" or write and execute the following small ABAP/4-Program:
PROGRAM progname.
DATA: CNT TYPE I.
EXEC SQL. SELECT COUNT(*) FROM VBLOG INTO :CNT
ENDEXEC.
WRITE: CNT.

When VBLOG is empty, proceed with point 4), otherwise carry out point 3) first.

3. Use the transport utility R3trans to export the contents of tables VBHDR, VBMOD, and VBDATA. Once these tables have been exported successfully, delete the contents of table VBLOG (DELETE FROM VBLOG).

4. Set tables VBHDR, VBMOD, and VBDATA (in that sequence) to "transparent" in the ABAP/4 Dictionary and then activate them. To do this, perform the following for each of the tables:

• Transaction SE11 (ABAP/4 Dictionary) --> Change table (you will have to open a repair at this point).

• Menu Extras --> Change table category --> Transparent database table --> Choose

• --> Activate

5. Now use the database utilities to define these three tables as well as their primary indexes in the database. To do this, call Transaction SE14 and select the function "Process tables". Enter the name of the table you want to create in field "Table name". Before you really create the table, however, you should first mantain the physical DB parameters when using ORACLE (this is no longer necessary for the databases ADABAS-D, DB2/400, MS-SQL Server and DB2forAIX. You can continue directly with creating the table here (see below)) This is important, since inadequate configuration can cause loss of performance. To do this, proceed as follows:

• --> DB parameters (DB2 for AIX does not use the parameters InitExt, NextExt, BMinExt, MaxExt referenced below). The physical parameters for the CREATE TABLE statement can be entered on this screen. Go to the line that contains the entry "USR" in column "Source", press the "Set" button, and enter the values for InitExt, NextExt, MinExt, MaxExt, and tablespace name as indicated below:
  
  Table | InitExt NextExt MinExt MaxExt Tablespace name
  ------------------------------------------------------------
  VBHDR | 64 128 1 300 PSAPCLUD
  VBMOD | 64 128 1 300 PSAPCLUD
  VBDATA | 10240 10240 1 300 PSAPCLUD
  

• --> Save

• --> Index parameters. The physical parameters for the primary index can be configured here. Enter the index identifier '0' and press "Return." Do the same for the table parameters above, with the exception of the tablespace name: instead of PSAPCLUD, enter the index tablespace PSAPCLUI. For all other parameters, (InitExt, ...) use the same parameters as listed above.

• --> Save and back (<--)

• --> Online - Create. Now the table together with its primary index will be created on the database with the parameters defined before. It is therefore unnecessary to define the primary index separately in the ABAP/4 Dictionary or to create it on the database! No other indexes to these tables are required !

6. Now check whether the update works again. To do this, call Transaction SM12 and select the function "Diagnosis in update" in the "Extras" menu. If the text is successful, you can then reimport the dataset from tables VBHDR, VBMOD, and VBDATA that you may have exported above, and can then release R/3 for general use.

Additional key words

DBIF_RTAB_NO_MEMORY