MaxDB: index queueing during system copy

[jgleichmann]

Hello MaxDB specialists,

may be you are familiar with the index queueing feature while using R3load methode for migration / system copy.

Sometimes this sequential methode is not really performant when the needed data is not in the data cache anymore.

During migration / system copy you are facing the following scenario:

So you just see one last R3load process of a package (before SAPVIEW package) during import which is taking a lot of time looking like this:

R3load -i SWWLOGHIST__DTP.cmd -dbcodepage 1100 -k <MIGkey> -l SWWLOGHIST__DTP.log -nolog -c 50000 -force_repeat -loadprocedure dbsl

The name of the package is not relevant because it is choosen randomly. But is every time a DTP package.

1. But you want to know how long it will take to finish.
2. You want to know how many indexes are still queued.
3. May be you want to speed up the procedure and skip this index creation. Just to finish the import and create the missing indexes in your postprocedure.

First about the index queueing ( 1464560 - FAQ: R3load in MaxDB  😞

Q: Why are the indexes created after loading?

A: The MaxDB has an optimized "internal parallel create index" procedure, which is much faster than maintaining the indexes during loading. You also cannot use the fast loader if indexes already exist in the table.

Q: What is index queueing in the R3load for MaxDB?

A: The optimized "internal parallel create Index" can be used by a user task of the database. Other create indexes in a user task are created serially in the slow create index mode.

Exactly one R3load uses exactly one user task of the database, which means that parallel R3loads cannot use the "internal parallel create index" simultaneously.

For MaxDB, therefore, an index queueing via the table "/MAXDB/INDEXSTATEMENTS" is used so that only one R3load "create index" executes.

Q: What is the importance of the R3load option "-para_cnt <count>"?

A: The option R3load "-para_cnt <count>" specifies how many R3load processes the user has started and affects the index queueing.

R3load does not use the "internal parallel create index" during Create Index for all tables in which indexes are to be created and that use less space in the database than the nth part of the data cache (where n = count).

R3load creates these indexes serially as create index mode. For these tables, you can assume that the data records are still in the data cache during the create index.

In this case, it is useful to avoid the "internal parallel create index" for the entire runtime of the import because the serial mode is only marginally slower than the "internal parallel create index" if the data records are in the cache.

So all big indexes won't be created in an own package even if there is a seperate DTP package (package without load data, create table and create primary index) created via omit option (DTPIV). They will be queued in the table /MAXDB/INDEXSTATEMENTS.

short explanation about the omit option (Source: Migration Monitor documentation):

-o D : omit data; do not load data

-o T: omit tables; do not create tables

-o P: omit primary keys; do not create primary keys

-o I: omit indexes; do not create indexes

-o V: omit views; do not create views

Only in case of HANA database:

-o M: omit merge; do not merge

-o U: omit unload; do not unload table

So when a index is choosen for index queueing methode you will read this in the log of the affected package:

JEST__DTP.log:(DB) INFO: JEST~I created later with other R3load process#20141108094527

JEST__DTP.log:(DB) INFO: JEST~Z01 created later with other R3load process#20141108094527

If you want to know the current content of the queue you can use sqlcli

sqlcli -d <DBSID> -u <SchemaUser>,<PW>

sqlcli <SID>=> \dc /MAXDB/INDEXSTATEMENTS

Table "<SCHEMA>./MAXDB/INDEXSTATEMENTS"

| Column Name | Type          | Length | Nullable | KEYPOS |

| ----------- | ------------- | ------ | -------- | ------ |

| TABLESIZE   | FIXED         | 18     | NO       | 1      |

| TABLENAME   | VARCHAR ASCII | 40     | NO       | 2      |

| INDEXNAME   | VARCHAR ASCII | 40     | NO       | 3      |

| SESSION     | FIXED         | 10     | YES      |        |

| THISNODE    | VARCHAR ASCII | 64     | YES      |        |

| STMT        | CLOB ASCII    | -      | YES      |        |

sqlcli <SID>=>select * from /MAXDB/INDEXSTATEMENTS

Tablesize	Tablename	Indexname	Session	Thisnode
-1	## this row locks parallel create index	###	247	<hostname.DOMAIN>
227353	JEST	JEST~Z01	?	?
487161	SWW_CONTOB	SWW_CONTOB~A	?	?
487161	SWW_CONTOB	SWW_CONTOB~ZZ1	?	?
2709569	COEJ	COEJ~1	?	?
2892158	COEP	COEP~4	?	?
2892158	COEP	COEP~Z1	?	?
2892158	COEP	COEP~Z2	?	?

But there is no estimate how long one or all indexes are take to finish its work. You just can refer to the tablesize and compare with other index creation runtimes.

If you want to skip this, just kill the R3load process, delete/truncate or drop the table /MAXDB/INDEXSTATEMENTS. Check the TSK file (if used) of the aborted DTP package to "ok" or "ign" if any "err" status.

Set status of the DTP package in import_monitor_cmd to "+" and restart the import monitor and the procedure will continue with the last package SAPVIEW.

You can create the missing indexes with a mass create action in DB02/SE14

or

if you want still to use R3load index queueing feature than you mustn't drop or delete the entries in /MAXDB/INDEXSTATEMENTS.

Just skip the DTP Package and wait until SAPVIEW package, because if any error during index creation occur the package will also abort.

Check the following tables:

          /MAXDB/INDEXSTATE

          /MAXDB/INDEXSTMTS

          /MAXDB/INDEXSTMTS_NEW

          /MAXDB/INDEXSTATEMENTS

If they have entries => backup them to temp tables, because the tables will be dropped in postprocedure from SWPM. Just copy the table back after the sapinst has completed its work.

Afterwards start R3load (with import environment) with option "-create_queued_indexes". This will process the queue again.

At the end of the day just drop the /MAXDB/INDEX* tables to avoid issues regarding unknown DDIC objects. (780043 - Additional "/MAXDB/INDEX*" tables exist in the system)

I hope I could help you to understand the feature and optimize/speed up your import procedure.

If you have any further questions, don't hestate to comment the blog or contact me or one of my colleagues at Q-Partners ( info_at_qpcm_dot_de )

Best Regards,

Jens Gleichmann

Technology Consultant at Q-Partners (www.qpcm.eu )</p>