[DB2 V11 z/os] LOAD with duplicates didn't use DISCARD

Philip Sevetson

[DB2 V11 z/os] LOAD with duplicates didn't use DISCARD
Looking for some advice about DISCARDDN in a LOAD.

I thought that duplicate key errors were written to DISCARDDN when they occur during a LOAD (this is with RESUME, if it matters). Apparently not; I put up a TEMPLATE SYSDISC, and a large number of duplicates were reported in my load, and none of them made it to the DISCARD file (which was never used).

Is this WAD, or am I doing something wrong?

--Thanks,

Phil
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Michael Hannan

RE: [DB2 V11 z/os] LOAD with duplicates didn't use DISCARD
(in response to Philip Sevetson)

In Reply to Philip Sevetson:

Looking for some advice about DISCARDDN in a LOAD.

I thought that duplicate key errors were written to DISCARDDN when they occur during a LOAD (this is with RESUME, if it matters). Apparently not; I put up a TEMPLATE SYSDISC, and a large number of duplicates were reported in my load, and none of them made it to the DISCARD file (which was never used).

Is this WAD, or am I doing something wrong?

Did your Utility make it to the Discard Phase or fail earlier? I guess dup keys are discovered during index Build but loaded rows removed later by Discard phase. The dups are removed at this time and written to Discard file, provided not more than the maximum (default 0 unlimited). We assume you did not IGNORE DUPKEY. You provided the SYSERR file or what ever it is called for input to Discard phase? Don't know if any of this is useful or too basic.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jul 12, 2018 - 04:10 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jul 12, 2018 - 04:11 AM (Europe/Berlin)

Gautam Sahu

[DB2 V11 z/os] LOAD with duplicates didn't use DISCARD
(in response to Philip Sevetson)
Hi Phil,

The LOAD job terminates when the number of rows discarded reaches the
number specified in the ‘DISCARDS n’ parameter and the discarded rows are
not written to the discard file. The discard file will be empty.

For LOAD to record the discarded rows in the discard file the number of
rows discarded should be less than the number specified in DISCARD
parameter.

You can either rerun with a larger value of DISCARD parameter and hope the
number of discards are lesser or you can just run with DISCARDS 0.

Hope this helps.

Regards,
Gautam

On Wed, 11 Jul 2018 at 11:09 PM, Sevetson, Phil <[login to unmask email]>
wrote:

> Looking for some advice about DISCARDDN in a LOAD.
>
>
>
> I thought that duplicate key errors were written to DISCARDDN when they
> occur during a LOAD (this is with RESUME, if it matters). Apparently not;
> I put up a TEMPLATE SYSDISC, and a large number of duplicates were reported
> in my load, and none of them made it to the DISCARD file (which was never
> used).
>
>
>
> Is this WAD, or am I doing something wrong?
>
>
>
> --Thanks,
>
>
>
> Phil
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
> -----End Original Message-----
>
--
Thanks,
Gautam

Philip Sevetson

[DB2 V11 z/os] LOAD with duplicates didn't use DISCARD
(in response to Michael Hannan)
I specified SYSDISC but not SYSERR. The load completed with RC=04. I’ll add SYSERR next time and report back.

From: Michael Hannan [mailto:[login to unmask email]
Sent: Wednesday, July 11, 2018 10:06 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 V11 z/os] LOAD with duplicates didn't use DISCARD


In Reply to Philip Sevetson:
Looking for some advice about DISCARDDN in a LOAD.

I thought that duplicate key errors were written to DISCARDDN when they occur during a LOAD (this is with RESUME, if it matters). Apparently not; I put up a TEMPLATE SYSDISC, and a large number of duplicates were reported in my load, and none of them made it to the DISCARD file (which was never used).

Is this WAD, or am I doing something wrong?

Did your Utility make it to the Discard Phase or fail earlier? I guess dup keys are discovered during index Build but removed later by Discard phase. The dups are removed at this time and written to Discard file, provided not more than the maximum (default 0 unlimited). We assume you did not IGNORE DUPKEY. You provided the SYSERR file or what ever it is called? Don't know if any of this is useful or too basic.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Philip Sevetson

[DB2 V11 z/os] LOAD with duplicates didn't use DISCARD
(in response to Gautam Sahu)
Gautam,

There’s something wrong in what you’re telling me, because the load simultaneously (1) succeeded for most of its rows (670 000 in the input file), (2) rejected a lot of input for duplicate key violations (170 000 records/rows), and (3) didn’t write anything to the DISCARDDN.

Also, I’m guessing that the default DISCARD value is 0, because I didn’t specify a value for it.


From: Gautam Sahu [mailto:[login to unmask email]
Sent: Thursday, July 12, 2018 1:30 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 V11 z/os] LOAD with duplicates didn't use DISCARD

Hi Phil,

The LOAD job terminates when the number of rows discarded reaches the number specified in the ‘DISCARDS n’ parameter and the discarded rows are not written to the discard file. The discard file will be empty.

For LOAD to record the discarded rows in the discard file the number of rows discarded should be less than the number specified in DISCARD parameter.

You can either rerun with a larger value of DISCARD parameter and hope the number of discards are lesser or you can just run with DISCARDS 0.

Hope this helps.

Regards,
Gautam

On Wed, 11 Jul 2018 at 11:09 PM, Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Looking for some advice about DISCARDDN in a LOAD.

I thought that duplicate key errors were written to DISCARDDN when they occur during a LOAD (this is with RESUME, if it matters). Apparently not; I put up a TEMPLATE SYSDISC, and a large number of duplicates were reported in my load, and none of them made it to the DISCARD file (which was never used).

Is this WAD, or am I doing something wrong?

--Thanks,

Phil
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Michael Hannan

RE: [DB2 V11 z/os] LOAD with duplicates didn't use DISCARD
(in response to Philip Sevetson)

In Reply to Philip Sevetson:

I specified SYSDISC but not SYSERR. The load completed with RC=04. I’ll add SYSERR next time and report back.

Philip,

I believe the manual says you must have a SYSERR, and I think that may communicate the duplicate errors to the DISCARD phase. The Discard phase has to know which things it is going to remove somehow.

I maybe slightly wrong about how it works (not my special area), but SYSERR is needed based on what I read.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jul 13, 2018 - 07:03 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jul 13, 2018 - 07:06 AM (Europe/Berlin)

Gautam Sahu

[DB2 V11 z/os] LOAD with duplicates didn't use DISCARD
(in response to Philip Sevetson)
Yes Phil, DISCARD 0 is the default. LOAD should write the records to
SYSDISC.

You mentioned that you did not specify SYSERR but I think LOAD will fail.
It fails with the message 'A REQUIRED DD CARD OR TEMPLATE IS MISSING.
NAME=SYSERR'.

Could you please mention the LOAD parameters you used?

Regards,
Gautam

On Thu, Jul 12, 2018 at 6:07 PM, Sevetson, Phil <[login to unmask email]>
wrote:

> Gautam,
>
>
>
> There’s something wrong in what you’re telling me, because the load
> simultaneously (1) succeeded for most of its rows (670 000 in the input
> file), (2) rejected a lot of input for duplicate key violations (170 000
> records/rows), and (3) didn’t write anything to the DISCARDDN.
>
>
>
> Also, I’m guessing that the default DISCARD value is 0, because I didn’t
> specify a value for it.
>
>
>
>
>