Unicode Migration on MaxDB - create indices - R3Load

[Former Member]

Hi Folks!

Currently we are performing an Unicode Migration (CU&UC) ECC 6.0 on Windows 2003 / MaxDB 7.6.

We are using migration monitor for parallel export and import with 12 R3Load processes on each side.

Export is finished successfully but import is taking very long for index creation.

There is one strange behaviour:

import_state.propeties -> all packages are marked with "+" except one:

S006=?

When I take a look at S006.STR there is only one table (S006) and two indices (S006_{ADQ, S006}Z00) listed.

Taking a look at Import installDir:

S006.TSK shows:

T S006 C ok

P S006~0 C ok

D S006 I ok

I S006~ADQ C ok

S006.TSK.bck shows:

T S006 C xeq

P S006~0 C xeq

D S006 I xeq

I S006~ADQ C xeq

I S006~Z00 C xeq

S006.log shows:

(...)

(DB) INFO: S006 created#20081203192452

(DB) INFO: Savepoint on database executed#20081203192453

(DB) INFO: S006~0 created#20081203192453

(DB) INFO: Savepoint on database executed#20081203193256

(IMP) INFO: import of S006 completed (4858941 rows) #20081203193256

(DB) INFO: S006~ADQ created#20081203193351

(DB) INFO: S006~ADQ created#20081203193351

(DB) INFO: COSP~1 created#20081203193504

(DB) INFO: COSP~2 created#20081203193920

(DB) INFO: RESB~M created#20081203194032

(...)

(and many many more)

There is only one R3Load running at the moment (others are finished)

I am not a MaxDB pro but what it looks like (to me):

All packages have been imported successfully and import_monitor is creating MaxDB indices using just one R3Load for creation and S006.log to log all the (secondary) indices which have been created.

This procedure is taking very long (more than 12 hours now). Is this a normal behaviour or is there any way to speed up index creation after data has been imported?

If I recall correctly, using Oracle each R3Load creates table, imports data and creates indices right afterwards.

On MaxDB it looks like index creation is performed with just one R3Load after all tables have been imported.

I have read a note about MaxDB parallel index creation which might be the reason for this behaviour (only one index creation at a time using parallel server tasks). But taking a look at the import runtime this doesn't seem to be efficient.

Any ideas or suggestions?

Thanks a lot.

/cheers

[markus_doehr2 · Accepted solution]

You´re right with what you see/saw. That behaviour is caused by the task how MaxDB created indexes. It uses "server tasks" to get all the blocks/pages from disk and create the index. If more than one index creation would run in parallel, it would take much more time because they would "fight" for the max. number of server tasks.

You can see what the database is doing by executing

x_cons <SID> sh ac 1

Unfortunately there is no way of changing that behaviour....

Markus

[Former Member · Accepted solution]

Thanks for your response.

I hardly can believe what I have just read... -.-

...so I got 16 CPU cores on my machine and have no other choice except watching one process doing all the index creation work after data load has been finished?!

Unbelievable...

[markus_doehr2 · Accepted solution]

An index creation is not CPU but I/O bound so it's basically the number of volumes and number of disks/spindles you have.

Markus

[Former Member · Accepted solution]

So the index creation taking ~15-20 hours points to a suboptimal storage device/configuration?

[markus_doehr2 · Accepted solution]

How many volumes do you have, how big are they and on how many physical disks are they distributed?

Markus