Resarting a LOAD utility after an abend DB2V8 ZOS

Mike Backes

Resarting a LOAD utility after an abend DB2V8 ZOS
Heres the situation.
Loading approx 200,000 rows into a 60 million row partitioned
table/space resume yes.
The reload phase completes indicating all rows were loaded to the
table/space,
Then dfsort thows a message during the sort phase that states sort
capacity exceeded, abeding the utility with a s04e.
When the utility was cancelled the the tablespace was fine but all of
the indexes were in rebuild pending, which once done
Completed the load basically.

I am of the opinion that terminating the utility and rebuilding the
indexes in this way was the only way to proceed with this load, without
the obvious restore and try again.

Other staff in the office are asserting that I could have done a
restart(current) or a restart(phase) of the utility after correcting the
space issue with the sortwrks. However the manual on restarting a load
utility has two notes related to table 42, on restarting in the sort
phase.
4. The utility can be restarted with either RESTART or RESTART(PHASE).
However, because this phase does not take checkpoints, RESTART is always
re-executed from the beginning of the phase.
10. |Any job that finished abnormally in the RELOAD, SORT, BUILD, |or
SORTBUILD phase restarts from the beginning of the RELOAD phase.

Which means to my way of thinking that it would reload 200,000
duplicate rows, and really mess up the build index phase because there
would be 200,000 rows on the table with no keys to put into the index.
which basically means there is no restart capability for a load utility.

Am I interpreting this right or am I missing something?

Mike Backes
ph: 522-5829



****************************
CONFIDENTIALITY STATEMENT:
This e-mail and any attachments are intended only for those to which it is addressed and may contain information which is privileged, confidential and prohibited from disclosure and unauthorized use under applicable law. If you are not the intended recipient of this e-mail, you are hereby notified that any use, dissemination, or copying of this e-mail or the information contained in this e-mail is strictly prohibited by the sender. If you have received this transmission in error, please return the material received to the sender and delete all copies from your system.

---------------------------------------------------------------------------------
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

Hanne Lyssand

Re: Resarting a LOAD utility after an abend DB2V8 ZOS
(in response to Mike Backes)
I would go for the rebuild of indexes, and since sort capasity seems to be
the problem, to do one index at the time.
A restart will not help because You have the to unload and sort of index
records anyway.

best regards
Hanne



**********************************************************************
For Your service, this message has been checked for viruses
http://www.vps.no
**********************************************************************

---------------------------------------------------------------------------------
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

Resarting a LOAD utility after an abend DB2V8 ZOS
(in response to Hanne Lyssand)
Mike,

I assume you are asking about Load Shrlevel None and failure after the
reload phase. (Shrlevel Change is a different beast). Your situation may
or may not be more complicated than you describe.

Do you need the processing in the indexval, enforce, discard, or report
phases?
In other words, are either of these conditions possible:
1. rows with duplicate keys, where a uniques index disallows this.
2. referential integrity violations (children with no parents).

The load utility adds to the table every row that passes the format edit.
Rows that should be disallowed are added anyway.
1. Disallowed duplicates are not detected and eliminated until the sort (or
sortbld) and indexval phases.
2. RI violations are not resolved until the enforce phase.

If you have problems in either area, then terminate load / rebuild indices
may not work. If you do not need to worry about duplicates or RI, then
term and rebuild is a viable strategy.

Once the reload phase is successfully complete, and failure occurs in sort
or build, you may or may not be able to restart the load. It depends on
whether you have sysut1 and sortout files which survive the failure. If
so, then you can restart in the failed phase. Specify either RESTART or
RESTART(PHASE). The result will be the same: the entire phase will be
rerun. You will save the time and effort to recover and the time to rerun
the entire load from the beginning. If you use parameter SORTKEYS, then
there are no sysut1 and sortout files, and restart in those phases is not
possible. I don't use SORTKEYS with the Load utility for that reason.
(But I do use SORTKEYS with reorg).

Since Load handles table rows and index entries in different phases, it is
necessary upon failure to get the data back in sync with the indices (at
the very least), so you are wise to have made such plans.

The worst table screwup I ever saw involved a botched restart of Load
Resume Yes Shrlevel None.
1. Load resume (not Load replace, which should have been used).
2. Reload phase adds 1M+ rows.
3. Sort fails on sortworks too small.
At this point the table has 1M+ rows; the indices are empty.
4. Term Load / fix sortworks / rerun Load resume from the beginning.
<--worst mistake possible
5. Same 1M+ rows added again.
6. Sort and Build run without failing. Rows from original run are not
indexed; rows from rerun are indexed.
7. Zero duplicates detected (duplicate detection based on indices).
8. Reorg attempted months later. 1M+ duplicates on unique index. It took
a month to fix this mess.
Please don't do this.

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

Jim Ruddy

Re: Resarting a LOAD utility after an abend DB2V8 ZOS
(in response to Mark McCormack)
If you have any unique indexes, cheek contraints, or RI contraints and you
allowed discarding then you should have restarted the LOAD. If you get any
uniqueness violations during REBUILD you get to resolve them manually with
REPAIR. If you have any check or RI constraints, you need to run CHECK DATA.

Jim Ruddy
DB2 for z/OS Development.

---------------------------------------------------------------------------------
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

Mike Backes

Re: Resarting a LOAD utility after an abend DB2V8 ZOS
(in response to Jim Ruddy)
Mark, yes that's what im talking about load shrlevel none.
There are no dup keys or ri in the load so the conditions you mentioned
do not come into play.
What we are doing is a load resume yes as you mentioned of around 200k
rows into a 60mil row table(ie. Replace is not an option). I guess I
wasn't asking if term util and rebuild was a viable option, I guess I
was saying, that according to my reading of the load restart manual,
it's the only option, after a sucessful reload and an abend in the sort
phase due to the fact that either a restart(current) or restart(phase)
will start the utility from the beginning of the reload step regardless.
In effect creating the situation that you described in your worst case
example. At least the way I read table 42, term util and rebuild is the
only answer. Short of recovering and rebuilding and trying again.

If not can you explain it to me how you would restart the load and avoid
the problem you mentioned? Because even though you say this 'If so, then
you can restart in the failed phase. ' note 10 from table 42 says
otherwise. I would agree with you that you could restart in the middle
of the reload phase if your files survived, but once that is done, you
cant restart without messing up the table.

Am I missing something?


Mike Backes
ph: 522-5829
=

Once the reload phase is successfully complete, and failure occurs in
sort or build, you may or may not be able to restart the load. It
depends on whether you have sysut1 and sortout files which survive the
failure. If so, then you can restart in the failed phase. Specify
either RESTART or RESTART(PHASE). The result will be the same: the
entire phase will be rerun. You will save the time and effort to
recover and the time to rerun the entire load from the beginning. If
you use parameter SORTKEYS, then there are no sysut1 and sortout files,
and restart in those phases is not possible. I don't use SORTKEYS with
the Load utility for that reason.
(But I do use SORTKEYS with reorg).

Since Load handles table rows and index entries in different phases, it
is necessary upon failure to get the data back in sync with the indices
(at the very least), so you are wise to have made such plans.

The worst table screwup I ever saw involved a botched restart of Load
Resume Yes Shrlevel None.
1. Load resume (not Load replace, which should have been used).
2. Reload phase adds 1M+ rows.
3. Sort fails on sortworks too small.
At this point the table has 1M+ rows; the indices are empty.
4. Term Load / fix sortworks / rerun Load resume from the beginning.
<--worst mistake possible
5. Same 1M+ rows added again.
6. Sort and Build run without failing. Rows from original run are not
indexed; rows from rerun are indexed.
7. Zero duplicates detected (duplicate detection based on indices).
8. Reorg attempted months later. 1M+ duplicates on unique index. It
took a month to fix this mess.
Please don't do this.

Mark


***********************************
CONFIDENTIALITY STATEMENT:
This e-mail and any attachments are intended only for those to which it is addressed and may contain information which is privileged, confidential and prohibited from disclosure and unauthorized use under applicable law. If you are not the intended recipient of this e-mail, you are hereby notified that any use, dissemination, or copying of this e-mail or the information contained in this e-mail is strictly prohibited by the sender. If you have received this transmission in error, please return the material received to the sender and delete all copies from your system.



---------------------------------------------------------------------------------
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

Resarting a LOAD utility after an abend DB2V8 ZOS
(in response to Mike Backes)
Mike,

I have reread several times the notes to the restart instructions in both
the v7 and v8 manuals, and I now notice some differences I didn't see
before. I am now as puzzled as you are about restart that would go back to
the reload phase.

The points about duplicates and RI are valid, as Jim Ruddy has confirmed.
My other suggestions seem to me to be accurate for load replace but not for
load resume yes shrlevel none.

It appears that some of the restrictions on restart whle using SORTKEYS are
removed. Perhaps that is involved. But that is speculation on my part
and not helpful. So I apologize for my inaccurate response.

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