Question Take a step backwards? 8k block back to 4k - Crazy or not?

[Chris Hughes]

Hi all

I thought I'd give an update having done my first type 2 migration.

I read a lot, then read some more, and more and then gave it a go. This is what I did.

Created a new 8k block database.
Imported the structure from the old DB.
Trunacted bi as recommended higher in this thread.
I decided space wasn't at a premium so all data cluster sizes were to be 512, one idex area with cluster size of 8.

b .
d "Schema Area":6,64;1 .
d "DataXL":10,16;512 .
d "DataL":11,32;512 .
d "DataM":12,64;512 .
d "DataS":13,128;512 .
d "IndexM":14,1;8 .

I used excel with the output of dbanlys to calculate what table goes where based on its mean record size (using Toms calculation (THANKS)).
I used excel to generate me a script to move my tables,

CALL proutil openacc -C tablemove PUB.oa_address DataM IndexM

Then loaded all my data back, then kicked off an index rebuild

proutil openacc -C idxbuild all -T z:\tmp

The result scatter factors obliterated, indexes all nice and tight and a DB that runs super fast.

Downsides, space usage up 50% (because of my lazy cluster sizing), still lots of space to expand into!
Looks like the app programmers have something wrong to fix in their code!

Thanks again for your help.

 

[Rob Fitzpatrick]

Good job Chris.

 

[cj_brandt]

I would recommend looking at the pfactor option to idxbuild. By default idxbuild packs all the index entries tight and fills 100% of the space. Now when records are updated, the database must do a block split to create the new index entries - a block split is very expensive. A pfactor of 80% would help reduce block splits on tables that have high update activity.

Also - congrats on the first D&L.