Database export problem

[Former Member]

Hello DB2 guru's,

we are performing a AIX 4.3 DB2 V7 database extract using R3LOAD. (DBEXPORT.R3S)

The database size is 350GB. (with a table of 50GB, and 8x 20GB)

The challange is that we only have 3 days to perform the export.

This is already the 3th try and still nog luck.

13 of 15 processes finish succesfully but he 2 left keep on running it seems.

Allthough the TEMPSPACE tablespaces keep growing, so I can imagine it is exporting the 50GB table.

Is there a way to determine which table it is processing, and maybe to have an indication of completion time ?

If found a java tool : MIGMON but have no experience in that.

All help or comments are welcome...

Thank you for your time

Hans

[Frank-Martin]

Hi,

the SAP R3load tool performs a

SELECT * FROM table ORDER BY PRIMARY KEY

to export tables. With DB2 V7 the optimizer sometimes decided to do a table scan + SORT to perform this select. This results in a high tempspace usage and long runtimes . I guess that this causes your current problem.

If this is your problem, you can try to force DB2 to do a primary key scan instead. To do this you can try to set the table VOLATILE or to change the tablespace settings ( see note SAP 857348 ) .

Regards

Frank

[Former Member]

Hi Frank,

if you force a primary key scan, the problem with the temporary tablespace will be solved, but not the performance problem. Normally the database decides correct from a performance perspective. The sort and tablescan is in those cases really better than the primary key scan. Better is to export unsorted.

Regards

Ralph

[Former Member]

Hi guys,

thank you for your feedback.

We get to 83% in the export process. 13 of the 15 processes completed.

If I then check the TEMP tablespaces I see them growing to 50 a 60GB.

And then they are emptied and I loose connection and can start over again.

My assumption is that the (1X50GB, 10X20GB tables are the cause)

What can you advice specifically to solve the problem with those big tables.

Can anyone give an example of the MIGMON / SPLIT tool ? Or another advice ?

very much appreciated !!!

thanks

Hans

[Former Member]

Hans,

perhaps this is a problem with older AIX versions(64GB Filesize limit).

I can remember that we had a similar problem a long time ago.

Please look for limits on AIX 4.2.

Best regards,

Joachim Müller

[Former Member]

Sorry, AIX 4.3.

regards,

Joachim Müller

[Former Member]

Hi Joachim,

the thing is that it splits automatically in files of 20GB.

or do you think the system sees them as a whole ?

I will check anyway.

[Former Member]

Hans,

I believe it has something to do with the underlying filesystem.

In AIX 4.3 it is not possible to use more than 64GB on journal files system.

Are the splitfiles all in one filesystem or are they striped over more than one each

with a own volume group.

Perhaps I'm also wrong, but if I google with 'file size limit 64GB AIX' I found

a lot of answers.

Best regards,

Joachim Müller

[Former Member]

Hi Hans,

You may use MIGMON, but your problem seems to be that you don't use a split tool. If you know how to stop R3SETUP during the load, you may stop the Export after R3szchk has run. After that you should run the Java SPLIT tool. It will split the standard packaged into smaller chunks. You find the tool on the same page in SAP Service Maketplace as MIGMON. Then you may run the export again. This should help a bit. If you use also MIGMON and your target database is also DB2 or Oracle, you may also try to export the big tables without order by primary key. Read the pdf Document which comes with MIGMON and also SPLIT for detailed descriptions. There is also a tool called MIGTIME which creates a report of the tables taking most time to export (or import).

Regards

Ralph