Check Data Shrlevel Change

Kapil Mathur

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

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

Lockwood Lyon

Re: Check Data Shrlevel Change
(in response to Kapil Mathur)
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

Fred Edgar

Re: Check Data Shrlevel Change
(in response to Lockwood Lyon)
Kapil,

I agree with Lock. One other thing to consider is the value of available
data compared to correct data. I'm sure you may have worked somewhere in
your past where it was more important to have tables available than it was
to have the correct data in them. I'm not saying I agree with that, but
sometimes management makes decisions w/o consulting me.

Fred

--- On Tue, 1/4/11, Lyon, Lockwood <[login to unmask email]> wrote:

> From: Lyon, Lockwood <[login to unmask email]>
> Subject: Re: [DB2-L] Check Data Shrlevel Change
> To: [login to unmask email]
> Date: Tuesday, January 4, 2011, 2:25 PM
> 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 *
* 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