0

Given the following scenario:

DB1:
EXPORT TO T.IXF OF IXF ... modified by codepage=... SELECT * FROM T;

DB2:
CREATE TABLE T (...) COMPRESS YES ADAPTIVE ORGANIZE BY ROW;
CREATE INDEX ... ON T (...) COMPRESS YES;
...
LOAD FROM T.IXF OF IXF INSERT INTO T (...);

Indexes are the same in DB1 and DB2, besides being compressed in DB2, and cluster ratio seems ok in DB1 (not sure if that matters). Code page differs between DB1 och DB2

Is there any point in doing a reorg after loading? I've found some articles from Z that seem to suggest that the way data is exported is of importance (since it where Z I did not read that carefully though so I don't know if that is relevant anymore on Z and/or LUW).

I tried on a fairly large number of tables, and did a REORGCHK after loading and everything seems fine. Comparing CALL GET_DBSIZE_INFO(?, ?, ?, 0) before and after REORG shows a slight decrease in size (<1%), but I don't know whether that is a coincidence.

Is there any particular situation where a REORG would be beneficial after loading into an empty table?

1 Answer 1

1

Db2 for LUW

REORG of compressed tables with RESETDICTIONARY might be beneficial after the initial load, because during the load the table-level compression dictionary is built using a sample of incoming data, which means that if your data patterns are unevenly distributed across the input file the dictionary might be suboptimal. REORG will use a larger sample (if not all of the data) to build the dictionary.

Other than that, REORG after LOAD is unneccesary.

Db2 for z/OS is different because in it every table would have a clustering index (implicitly defined if not declared explicitly), and if your input file is not ordered according to the target table's clustering index, clustering factor after the load will be poor.

1
  • Thanks, I noticed a minimal improvement in size after reorg, but reorging the database takes for ever, so I'll probably skip that step for this db. I may have been lucky so that the first pages of each table is enough representable for the table as a whole. Sep 1, 2020 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.