Re: Catalog Shadow Tables

Eric Pearson

Re: Catalog Shadow Tables
(in response to Michael Dempsey)
We allow fairly open access to our catalog tables with the understanding
that
if anyone starts causing locking problems they may lose their access.

If you do the shadow routine (I have done this elsewhere), UNLOAD/LOAD is
a very easy way. If you need, you can also propagate changes via a tool
like Platinum/CA Log Analyzer (I suppose BMC and possibly CDB have
similar tools available).

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Dempsey, Michael [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2001 2:29 PM
To: [login to unmask email]
Subject: Catalog Shadow Tables


Hey Folks...
Is anyone out there using catalog shadow tables. Our apps folks are
asking to query the catalog. Are there other reason's besides the locking
contention on the catalog that would want me to create shadow tables. If
so, would it be best to unload from the catalog tables and load into the
shadow tables, or delete from the shadow tables and insert from the catalog
tables on a nightly basis. Thanks!

Michael Dempsey
UNC Health Care System








Michael Dempsey

Catalog Shadow Tables
Hey Folks...
Is anyone out there using catalog shadow tables. Our apps folks are
asking to query the catalog. Are there other reason's besides the locking
contention on the catalog that would want me to create shadow tables. If
so, would it be best to unload from the catalog tables and load into the
shadow tables, or delete from the shadow tables and insert from the catalog
tables on a nightly basis. Thanks!

Michael Dempsey
UNC Health Care System



Michael Deer

Re: Catalog Shadow Tables
(in response to Eric Pearson)
I worked at a shop a few years ago that used a shadow catalog tables.
We used unload/load every night. I dont think they really did much
to improve access (most dba's queried the actual catalog anyway).
However, it did save the day when a new DBA (with SYSADM authority) ran
a "FREE PLAN *". The user attentioned out of it after about 95% of
the plans in the subsystem were FREE'd. I was able to use
CA/Platinum's migrator to create a script that bound all the missing
plans by pointing migrator at the alternate catalog (which still had the
FREE'd plans in them, of course). The script put everything back in a
few minutes (whew!).


-Mike

----------
From: MDempse
Sent: Tuesday, January 09, 2001 1:28 PM
To: DB2-L
Cc: MDempse
Subject: Catalog Shadow Tables

Hey Folks...
Is anyone out there using catalog shadow tables. Our apps folks
are
asking to query the catalog. Are there other reason's besides the
locking
contention on the catalog that would want me to create shadow tables.
If
so, would it be best to unload from the catalog tables and load into
the
shadow tables, or delete from the shadow tables and insert from the
catalog
tables on a nightly basis. Thanks!

Michael Dempsey
UNC Health Care System



visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
the list can



John Cameron

Re: Catalog Shadow Tables
(in response to Michael Deer)
If locking is the issue, have your app people add "WITH UR" to their SELECT
statements.

John Cameron
[login to unmask email]






"Pearson, Eric
L," To: [login to unmask email]
<Eric.Pearson@N cc:
SCORP.COM> Subject: Re: Catalog Shadow Tables
Sent by: DB2
Data Base
Discussion List
<[login to unmask email]
>


01/09/01 01:17
PM
Please respond
to DB2 Data
Base Discussion
List






We allow fairly open access to our catalog tables with the understanding
that
if anyone starts causing locking problems they may lose their access.

If you do the shadow routine (I have done this elsewhere), UNLOAD/LOAD is
a very easy way. If you need, you can also propagate changes via a tool
like Platinum/CA Log Analyzer (I suppose BMC and possibly CDB have
similar tools available).

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Dempsey, Michael [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2001 2:29 PM
To: [login to unmask email]
Subject: Catalog Shadow Tables


Hey Folks...
Is anyone out there using catalog shadow tables. Our apps folks are
asking to query the catalog. Are there other reason's besides the locking
contention on the catalog that would want me to create shadow tables. If
so, would it be best to unload from the catalog tables and load into the
shadow tables, or delete from the shadow tables and insert from the catalog
tables on a nightly basis. Thanks!

Michael Dempsey
UNC Health Care System



the










Eric Pearson

Re: Catalog Shadow Tables
(in response to Glenn Mackey)
Does UR no longer leave CLAIMs against the objects?
When we had the problem UR solved the locks but not
the claims. Maybe maint has fixed that (it was quite a while ago).

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Mackey, Glenn [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2001 3:12 PM
To: [login to unmask email]
Subject: Re: Catalog Shadow Tables


I allow access to the catalog, and get the programmers etc. to use the "WITH
UR" clause always to stop contention.

-----Original Message-----
From: Dempsey, Michael [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2001 1:29 PM
To: [login to unmask email]
Subject: Catalog Shadow Tables


Hey Folks...
Is anyone out there using catalog shadow tables. Our apps folks are
asking to query the catalog. Are there other reason's besides the locking
contention on the catalog that would want me to create shadow tables. If
so, would it be best to unload from the catalog tables and load into the
shadow tables, or delete from the shadow tables and insert from the catalog
tables on a nightly basis. Thanks!

Michael Dempsey
UNC Health Care System













Glenn Mackey

Re: Catalog Shadow Tables
(in response to John Cameron)
I allow access to the catalog, and get the programmers etc. to use the "WITH
UR" clause always to stop contention.

-----Original Message-----
From: Dempsey, Michael [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2001 1:29 PM
To: [login to unmask email]
Subject: Catalog Shadow Tables


Hey Folks...
Is anyone out there using catalog shadow tables. Our apps folks are
asking to query the catalog. Are there other reason's besides the locking
contention on the catalog that would want me to create shadow tables. If
so, would it be best to unload from the catalog tables and load into the
shadow tables, or delete from the shadow tables and insert from the catalog
tables on a nightly basis. Thanks!

Michael Dempsey
UNC Health Care System








Kirk Hampton

Re: Catalog Shadow Tables
(in response to Eric Pearson)
We also populate a shadow catalog every night using Data Refresher
(formerly DXT) to unload to a flat file, then a LOAD REPLACE LOG NO
into the shadow. Same as Michael, it has saved our behind more than once
to be able to reconstruct something after an Oops.
We do allow full catalog access in the Development subsystem, but
not in Production. Also, in production we keep not only a 1-day-old shadow,
but also one that is only refreshed every two weeks, in case a problem is
not noticed the same day that it happened.
I think a DELETE and INSERT from the catalog to the shadow would
run a much greater risk of tying up your system due to locking, as well as
being
slower to execute.





Michael Deer <[login to unmask email]> on 01/09/2001 01:33:30 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Kirk Hampton/Texas Utilities)
Subject: Re: Catalog Shadow Tables



I worked at a shop a few years ago that used a shadow catalog tables.
We used unload/load every night. I dont think they really did much
to improve access (most dba's queried the actual catalog anyway).
However, it did save the day when a new DBA (with SYSADM authority) ran
a "FREE PLAN *". The user attentioned out of it after about 95% of
the plans in the subsystem were FREE'd. I was able to use
CA/Platinum's migrator to create a script that bound all the missing
plans by pointing migrator at the alternate catalog (which still had the
FREE'd plans in them, of course). The script put everything back in a
few minutes (whew!).


-Mike

----------
From: MDempse
Sent: Tuesday, January 09, 2001 1:28 PM
To: DB2-L
Cc: MDempse
Subject: Catalog Shadow Tables

Hey Folks...
Is anyone out there using catalog shadow tables. Our apps folks
are
asking to query the catalog. Are there other reason's besides the
locking
contention on the catalog that would want me to create shadow tables.
If
so, would it be best to unload from the catalog tables and load into
the
shadow tables, or delete from the shadow tables and insert from the
catalog
tables on a nightly basis. Thanks!

Michael Dempsey
UNC Health Care System



visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
the list can