Has anyone used R3load LOAD "ANY_ORDER" option?

[former_member184709]

We are testing a migration with a big (32-core) box and this option is listed in Note 1058437 as an option for R3load.

In the note, it asks for us to consult the DB2 documentation, which describes this option:

"Specifies that the load utility can process the input data in any order, resulting in better performance on symmetric multiprocessing (SMP) systems."

"By default, the load utility preserves record order of source data. When load is operating under an SMP environment, synchronization between parallel processing is required to ensure that order is preserved.

In an SMP environment, specifying the anyorder file type modifier instructs the load utility to not preserve the order, which improves efficiency by avoiding the synchronization necessary to preserve that order. However, if the data to be loaded is presorted, anyorder might corrupt the presorted order, and the benefits of presorting are lost for subsequent queries."

http://www.ibm.com/developerworks/data/library/techarticle/dm-0405melnyk/index.html

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.dm.doc/d...

Since most performance optimizations for R3load recommend exporting the data unsorted anyway, and it has to re-sort on import, this seems like it may be a good option to try out.

I was just wondering if anyone had tried this as I had not seen it any of the optimization studies for DB2/R3load.

[Frank-Martin]

Hi Derek,

very interesting question. I have seen a slight performance gain for some tables in a lab environment. However my hardware had only 2 CPU and the results may not be relyable.

I would love to see some results from real customer scenarios with a higher CPU parallelism. At least the paremeter ANY_ORDER will not hurt and degrade the performance. So it is worth a try.

Regards

Frank

[former_member184709]

Hi Frank,

Thanks for the feedback,

We have another import iteration early next week, and then I may freeze any import changes after that run, and just change the R3load parameters to "ANY_ORDER" to see what effect that may have on import times (time permitting).

If I am able to put the parameter in, I will post my results here. We have a variety of packages - separate table packages, and split tables (sequential load on split tables with LOAD_FORCED), so hopefully it will be interesting to see if the parameter behaves differently depending on what type of package it is importing.

I hope to post results here next week.

[former_member184709]

Hi Frank,

I had hoped to be able to test this option this week, but we discovered an issue in our storage subsystem, where not only our migration test server was degrading in performance, but it was also affecting other servers within the same storage network.

For now our migration tests have been put on hold until the storage issue has been solved. I am not sure when that will be, but when we are able to test again, I will conduct a baseline test, and then add the ANY_ORDER parameter to see what kind of performance difference it makes.