[DB2-L] Check Data Shrlevel Change

Walter Janißen

[DB2-L] Check Data Shrlevel Change
Lyon

If you load with ENFORCE NO, the child-tables are in check-pending and not accessible by SQL.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Lyon, Lockwood
Gesendet: Dienstag, 4. Januar 2011 21:26
An: [login to unmask email]
Betreff: Re: [DB2-L] Check Data Shrlevel Change

Kapil,

I haven't seen a reply yet, so I'll have a go.

CHECK DATA is sometimes used after multiple LOAD ... ENFORCE NO jobs. In this case, the ENFORCE NO has bypassed RI checking. (Among other things, this allows you to load a set of referentially-connected tables with pre-validated data in any order without causing RI errors.). So there will be cases where you might be run CHECK DATA "just to be safe", while allowing simultaneous access to tables you 99% believe to be fine.

HTH

Lock Lyon
Fifth Third Bancorp

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Kapil Mathur
Sent: Tuesday, January 04, 2011 11:50 AM
To: [login to unmask email]
Subject: [DB2-L] Check Data Shrlevel Change

Hello DB2-L members,

In DB2 v9 for z/OS, there is a utility CHECK DATA with option SHRLEVEL CHANGE ... that generates individual REPAIR statements for data rows that are violating RI constraints ... I would appreciate any help with the following question about that option:-

What functionality does running CHECK DATA with SHRLEVEL CHANGE provide for a child tablespace (vs. running with a SHRLEVEL REFERENCE) ... i.e. if a child tablespace is in check-pending status, isn't it supposed to be inaccessible to any SQL whatsoever, for reading as well as writing purposes? If yes, who/what would benefit from the potentially higher availability gained by running CHECK DATA with SHRLEVEL CHANGE?

If a tablespace is in check-pending status to begin with (i.e. data integrity is potentially compromised), why would a DBA want applications to be able to read the data before a CHECK DATA has completed running-ok and gracefully removed all RI constraint violators .... and if they wanted that option anyway, could they not just as well run a REPAIR NOCHECKPEND against the tablespace, in the first place and then use SQL to identify orphan child-table rows (and potentially generate appropriate DELETE statements that refer to the primary key of orphan record, so identified)

thanks

This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Lockwood Lyon

Re: AW: [DB2-L] Check Data Shrlevel Change
(in response to Walter Janißen)
Walter,

Correct.

I tried to describe a situation common in publish-subscribe or data replication scenarios. You are given a referentially-correct set of data in one environment, and you wish to Load it into another environment. You use LOAD with the ENFORCE NO option, since there is no point to force DB2 to verify the foreign key values.

Of course, in such a situation after the successful LOAD you would do a REPAIR SET ... NOCHECKPEND on all the tablespaces.

My apologies for the lack of clarity in my original e-mail.

- Lock Lyon
Fifth Third Bancorp


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: Wednesday, January 05, 2011 4:11 AM
To: [login to unmask email]
Subject: [DB2-L] AW: [DB2-L] Check Data Shrlevel Change

Lyon

If you load with ENFORCE NO, the child-tables are in check-pending and not accessible by SQL.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Lyon, Lockwood
Gesendet: Dienstag, 4. Januar 2011 21:26
An: [login to unmask email]
Betreff: Re: [DB2-L] Check Data Shrlevel Change

Kapil,

I haven't seen a reply yet, so I'll have a go.

CHECK DATA is sometimes used after multiple LOAD ... ENFORCE NO jobs. In this case, the ENFORCE NO has bypassed RI checking. (Among other things, this allows you to load a set of referentially-connected tables with pre-validated data in any order without causing RI errors.). So there will be cases where you might be run CHECK DATA "just to be safe", while allowing simultaneous access to tables you 99% believe to be fine.

HTH

Lock Lyon
Fifth Third Bancorp

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Kapil Mathur
Sent: Tuesday, January 04, 2011 11:50 AM
To: [login to unmask email]
Subject: [DB2-L] Check Data Shrlevel Change

Hello DB2-L members,

In DB2 v9 for z/OS, there is a utility CHECK DATA with option SHRLEVEL CHANGE ... that generates individual REPAIR statements for data rows that are violating RI constraints ... I would appreciate any help with the following question about that option:-

What functionality does running CHECK DATA with SHRLEVEL CHANGE provide for a child tablespace (vs. running with a SHRLEVEL REFERENCE) ... i.e. if a child tablespace is in check-pending status, isn't it supposed to be inaccessible to any SQL whatsoever, for reading as well as writing purposes? If yes, who/what would benefit from the potentially higher availability gained by running CHECK DATA with SHRLEVEL CHANGE?

If a tablespace is in check-pending status to begin with (i.e. data integrity is potentially compromised), why would a DBA want applications to be able to read the data before a CHECK DATA has completed running-ok and gracefully removed all RI constraint violators .... and if they wanted that option anyway, could they not just as well run a REPAIR NOCHECKPEND against the tablespace, in the first place and then use SQL to identify orphan child-table rows (and potentially generate appropriate DELETE statements that refer to the primary key of orphan record, so identified)

thanks

This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv