MVSFORUMS.com

nishantrk · Beginner Joined: 24 Mar 2006 Posts: 32 Topics: 16

Does pre sorting of data ,on index keys before doing a load replace on table improve the performance ?We have many jcl in our shop which have been written in that way..but i didn't get any satisfactory answer why the extra sort step is required ,as i think DB2 LOAD using DSNUPROC
itself has a sort step ???Can anyone please explain in detail??

Thanks in advance..

NASCAR9 · Posted: Tue Mar 23, 2010 1:22 pm Post subject:

These are my thoughts:

When presorting on the Clustering Key, the data is laid down how it was designed.
A reorg may need to be run if the data was not sorted in the Clustering Key sequence.

That said, DB2 running with a ZIIP processer runs DB2 utilities very fast.

Each shop is different. You will need to test.
_________________
Thanks,
NASCAR9

nishantrk · Beginner Joined: 24 Mar 2006 Posts: 32 Topics: 16

Can you please explain a bit more....
If my understanding is correct then ,you mean to say ,while loading DB2 checks if the data is sorted in the Clustering Key sequence.If not then it will run a reorg utility on the data.
So the difference in performance will be due to difference in performance of SYNCSORT and DB2 reorg utility???

NASCAR9 · Posted: Tue Mar 23, 2010 5:43 pm Post subject:

What I'm telling you is:

The data will be laid down exactly how it is received. The load does not care what sequence the data is in.

The reorg is something extra you would do to put the data into sequence. It is not mandatory. The reorg should help performance if the data is out of sequence.

It's my understanding that DB2 utilities use IBM DFSORT even if you have Syncsort in your shop.
_________________
Thanks,
NASCAR9

CZerfas · Intermediate Joined: 31 Jan 2003 Posts: 211 Topics: 8

The sort DB2 uses during a LOAD is to sort the key values of your indices on that table into the sequence needed for the index columns. The data itself is not affected by that sort.

If you want your rows to be stored in a clustered sequence into the tablespaces, it is always a good idea to presort your data before youe LOAD REPLACE it.

regards
Christian

papadi · Supermod Joined: 20 Oct 2009 Posts: 594 Topics: 1

Frank Yaeger · Posted: Wed Mar 24, 2010 3:05 pm Post subject:

papadi · Supermod Joined: 20 Oct 2009 Posts: 594 Topics: 1

And there is my "new thing" to learn for today Smile

Thanks Frank.
_________________
All the best,

di