DB2 V8 Load Issue

Mohit Tyagi

DB2 V8 Load Issue
Hi



I am a new DB2 Version 8 user. I have been working in V7 all this time



I am trying to load a table having 4 indexes but am facing problem in
the sort step.



The error is

DSNU395I DSNURPIB - INDEXES WILL BE BUILT IN PARALLEL, NUMBER OF
TASKS = 8

DSNU016I DSNUGSAT - UTILITY BATCH MEMORY EXECUTION ABENDED,
REASON=X'016D'

DSNU016I DSNUGSAT - UTILITY BATCH MEMORY EXECUTION ABENDED,
REASON=X'016D'

DSNU016I DSNUGSAT - UTILITY BATCH MEMORY EXECUTION ABENDED,
REASON=X'016D'

DSNU016I DSNUGSAT - UTILITY BATCH MEMORY EXECUTION ABENDED,
REASON=X'016D'



I tried using the options SORTDEVT and SORTNUM as well as putting new
sort work datasets manually for the parallel index build.

But It didn't help.



Is there any special privilege or steplibs that are required for the
DFSORT to work?



Can anyone help me on this?



Regards

Mohit ZT








---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David Simpson

Re: DB2 V8 Load Issue
(in response to Mohit Tyagi)


You may be hitting a little known feature of Version 8. The sort tool
used by utilities changes from whatever your shop commonly uses (perhaps
Syncsort) in V7 to IBM's DFSORT in V8.



In my case, I had spent 6 years tuning Syncsort for my utilities... had
to start over. I have spent the last 18 months making large utilities
work again under DFSORT. If you can send me the entire output of the
job privately, I would love to look at it.



A couple of things to look for:



You want to allocate a lot of memory. This was the most common failure
for us.



The estimates passed by DB2 to Sort are sometimes not what you would
expect. Since your job is doing 8 subtasks, you may want to divide the
number of rows to be sort by 8 and send the estimate in via the DFSPARM.



There has also been a bug related to DFSORT's use of hiperpools. We
have had to disable this by specifying HIPRMAX=0 also in the DFSPARM.



Again, feel free to send me the job output and I'll compare it to some
of our failures.



------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ale Eba

Re: DB2 V8 Load Issue
(in response to David Simpson)
Hello,
DB2 8.1 uses DFSORT. There is no need to specify SORTWKxx datasets in the
JCL. Without sort work datasets, DFSORT will allocate the datasets
dynamically will perform parallel index rebuild. The number of sort work
datasets will depend on SORTNUM and SORTDEVT parameter. If you don't
specify SORTNUM and SORTDEVT, DFSORT will take it from sort installation
default parameter DYNALOC. There is a lot of detail about tuning DFSORT
parameter. See APAR II14047.
You can print sort installation default parameter using ICETOOL. A sample
job to print the default parameters is in DFSORT user guide.

Ale

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark McCormack

DB2 V8 Load Issue
(in response to Ale Eba)
<You want to allocate a lot of memory. This was the most common failure
for us.>
I wish to reinforce David Simpson's suggestion. The change from
Syncsort to DFSORT caused us to make serious changes to our reorg procs.
We increased region size from 6M to 256M.
Mark


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm