Updating System Tables

Harvey Wachtel

Updating System Tables
Somebody here claims he heard from somebody outside (I think from BMC) that
there is a system option that allows you to update any column of any
catalog table. Does such a thing exist? In what versions? How do you do
it?



[login to unmask email]

Re: Updating System Tables
(in response to Harvey Wachtel)
Harvey,

Yes, it exists - I believe in all versions of DB2 MVS. There is a 'hidden'
ZPARM option that needs to be changed to allow it. It is documented
somewhere on the web - I think maybe on BMC or Platinum websites.
Alternatively, you can dig through all the zparm macros to try and find it.

Be advised that turning it on places you in the 'Twilight Zone' as far as
IBM support goes. It is not recommended practice, and you most definitely
do it at your own risk!

Why the burning desire to update the catalog? Did you win Lotto and no
longer need a job...? :)

Cheers,

Greg Palgrave
Database Administration Group
Information Services, Level 10, Tower
Bank of Western Australia
eMail : [login to unmask email] <no fishing expeditions please>


Harvey Wachtel <[login to unmask email]> on 15/12/99 08:04:46

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

To: [login to unmask email]
cc: (bcc: Greg Palgrave/SDG/SS/BankWest)

Subject: Updating System Tables




Somebody here claims he heard from somebody outside (I think from BMC) that
there is a system option that allows you to update any column of any
catalog table. Does such a thing exist? In what versions? How do you do
it?










_______________________________________________________________________________
Unencrypted electronic mail is not secure and may not be authentic.
If you have any doubts as to the contents please telephone to confirm.

This electronic transmission is intended only for those to whom it is
addressed. It may contain information that is confidential, privileged
or exempt from disclosure by law. Any claim to privilege is not waived
or lost by reason of mistaken transmission of this information.
If you are not the intended recipient you must not distribute or copy this
transmission and should please notify the sender. Your costs for doing
this will be reimbursed by the sender.
_______________________________________________________________________________



Richard A Yevich

Re: Updating System Tables
(in response to Greg.Palgrave@BANKWEST.COM.AU)
Harvey,

A bigger is question is why would you want to or need to? There are
generally ways of accomplishing access path tuning without altering cat
stats.

Richard Yevich
+=====+======+
Information Technology Consulting, Data Modeling, Advanced Education
RYC® Inc. USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-512-476-3930
Web: < http://www.ryci.com > Email: [login to unmask email] Offices: USA and Europe
DB2® Family and Oracle® Specialists - Parallel Technologies
VLDB and Data Sharing Technologies (specialties SAP®, Peoplesoft®)
Authors of "DB2 Answers" by Osborne-McGraw Hill, May, 1999


> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
> Harvey Wachtel
> Sent: Tuesday, December 14, 1999 6:05 PM
> To: [login to unmask email]
> Subject: Updating System Tables
>
>
> Somebody here claims he heard from somebody outside (I think from
> BMC) that
> there is a system option that allows you to update any column of any
> catalog table. Does such a thing exist? In what versions? How do you do
> it?
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list can be reached
> at [login to unmask email]
>



Leslie Pendlebury-Bowe

Re: Updating System Tables
(in response to Richard A Yevich)
Harvey
sounds pretty dangerous to me .. I would be very surprised if this
kind of thing made it out onto the open market.

stick with updating the ones IBM allow you to update .. but only if
you HAVE to ..

regards

Leslie Pendlebury-Bowe
DB2 SAP OS390


______________________________ Reply Separator _________________________________
Subject: Updating System Tables
Author: Harvey Wachtel <[login to unmask email]> at Internet
Date: 12/14/99 7:04 PM


Somebody here claims he heard from somebody outside (I think from BMC) that
there is a system option that allows you to update any column of any
catalog table. Does such a thing exist? In what versions? How do you do
it?








Andy Hunt

Re: Updating System Tables
(in response to Leslie Pendlebury-Bowe)

I remember a problem back in V2.1 (I think) when some SYSCOPY entries were
corrupted by a DB2 bug. By REPAIRing the UPDATES column to 'Y', for the
appropriate column, in SYSCOLUMNS for SYSIBM.SYSCOPY it was possible to run
'UPDATE' SQL against SYSCOPY. This allowed the corrupted entries to be
corrected. Phil Grainger may remember this one (though he might not care to
admit to it now !).

I certainly would NOT recommend this approach (as many disclaimers as you can
think of) and may not possibly work for recent releases anyway. Included for
interest only.
Kind Regards,
Andy Hunt - Scottish And Southern Energy





Richard A Yevich <[login to unmask email]> on 15/12/99 03:10:43

Please respond to [login to unmask email]

To: [login to unmask email]
cc: (bcc: Andy Hunt/HAV/SSE)
Subject: Re: Updating System Tables



**********************************************************************
The information in this E-Mail is confidential and may be legally
privileged. It may not represent the views of Scottish and Southern
Energy plc.
It is intended solely for the addressees. Access to this E-Mail by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
Any unauthorised recipient should advise the sender immediately of
the error in transmission.

Scottish Hydro-Electric and Southern Electric are trading names of
Scottish and Southern Energy Group
**********************************************************************

bat-chen zeliger

Re: Updating System Tables
(in response to Andy Hunt)
Hi,

Look for SPRMCTU in macro DSN6SPRC and you should be all set.
BUT I completely agree with the other responses to your question. Don't you
dare using it on a production system ! it could be nice on a test system
though.

Have a nice day,
Bat-Chen


-----Original Message-----
From: Harvey Wachtel [mailto:[login to unmask email]
Sent: Tuesday, December 14, 1999 7:05 PM
To: [login to unmask email]
Subject: Updating System Tables


Somebody here claims he heard from somebody outside (I think from BMC) that
there is a system option that allows you to update any column of any
catalog table. Does such a thing exist? In what versions? How do you do
it?








Venkat (PCA) Pillay

Re: Updating System Tables
(in response to Harvey Wachtel)
Harvey

There is a bit in the macro DSN6SPRC which has to be turned on in
order to allow catalog update. You have to change the following in the macro
and reassemble ZPARM.

&SPRMCTU SETC '1'

HTH
Venkat Pillay

> -----Original Message-----
> From: Harvey Wachtel [SMTP:[login to unmask email]
> Sent: Tuesday, December 14, 1999 7:05 PM
> To: [login to unmask email]
> Subject: Updating System Tables
>
> Somebody here claims he heard from somebody outside (I think from BMC)
> that
> there is a system option that allows you to update any column of any
> catalog table. Does such a thing exist? In what versions? How do you do
> it?
>
>
>
>
>



Harvey Wachtel

Re: Updating System Tables
(in response to bat-chen zeliger)
Thanks to all who responded to my question abour updating restricted
systemn tables. We're aware of the dangers and would of course test our
changes ver-r-r-r-y carefully on our test database.

This inquiry is related to my initial inquiry to this list about a month
ago which was widely ignored, probably because its title contained the
phrase "field procedure", which is apparently something that few
installation besides us have had occasion to try. (This is a common
situation at our shop; I'm not sure if it's because we're more clever than
everyone else or, more likely, because we're more foolish; it goes back to
around 1970, when we were bitten by writing our own access methods with
EXCP, and I can tell you a fairly interesting war story about the Move
Inverse instruction. However, as I have a tendency to do, I digress.)

We have written a field procedure that we want to apply to a column that
appears in most of the tables in a database. The values already on the
database will not be changed by encoding, so I think they don't need to be
changed on the database. The field procedure will affect only values to be
added later. Without cheating, we would have to take the system down for a
week to unload the tables, redefine them with the field proc specification,
and reload them with exactly the same data they have now. We're hoping we
can just insert the appropriate definition into SYSFIELDS and flip on the
fieldproc indicators in SYSCOLUMNS, saving a week of downtime.

Details: The field proc is a solution to a Y2K (academic year 2000,
Doomsday=2000-07-01) problem. The column involved holds academic semesters
of the form "yyn" and we want them to sort properly. The procedure encodes
first-digit values in the range '0'-'5' to X'FA'-X'FF' so they will sort
above 99x. All values currently on the database begin with "7", "8" or
"9", so these aren't changed by encoding. Except for a few glitches that I
mentioned in a recent post (UNION with a constant expression, QMF SAVE
DATA) the proc seems to be doing what we need.

Thanks again.



[login to unmask email]

Re: Updating System Tables
(in response to Venkat (PCA) Pillay)
Harvey,

Good Luck!

Would you be able to post the success (or otherwise) of the method to the
list at some stage - with any 'war stories' of course!? Like the cat, I'm
just curious....and you never know when you might need to try these things!

Regards,

Greg Palgrave
Database Administration Group
Information Services, Level 10, Tower
Bank of Western Australia
eMail : [login to unmask email] <no fishing expeditions please>


Harvey Wachtel <[login to unmask email]> on 15/12/99 23:14:05

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

To: [login to unmask email]
cc: (bcc: Greg Palgrave/SDG/SS/BankWest)

Subject: Re: Updating System Tables




Thanks to all who responded to my question abour updating restricted
systemn tables. We're aware of the dangers and would of course test our
changes ver-r-r-r-y carefully on our test database.

This inquiry is related to my initial inquiry to this list about a month
ago which was widely ignored, probably because its title contained the
phrase "field procedure", which is apparently something that few
installation besides us have had occasion to try. (This is a common
situation at our shop; I'm not sure if it's because we're more clever than
everyone else or, more likely, because we're more foolish; it goes back to
around 1970, when we were bitten by writing our own access methods with
EXCP, and I can tell you a fairly interesting war story about the Move
Inverse instruction. However, as I have a tendency to do, I digress.)

We have written a field procedure that we want to apply to a column that
appears in most of the tables in a database. The values already on the
database will not be changed by encoding, so I think they don't need to be
changed on the database. The field procedure will affect only values to be
added later. Without cheating, we would have to take the system down for a
week to unload the tables, redefine them with the field proc specification,
and reload them with exactly the same data they have now. We're hoping we
can just insert the appropriate definition into SYSFIELDS and flip on the
fieldproc indicators in SYSCOLUMNS, saving a week of downtime.

Details: The field proc is a solution to a Y2K (academic year 2000,
Doomsday=2000-07-01) problem. The column involved holds academic semesters
of the form "yyn" and we want them to sort properly. The procedure encodes
first-digit values in the range '0'-'5' to X'FA'-X'FF' so they will sort
above 99x. All values currently on the database begin with "7", "8" or
"9", so these aren't changed by encoding. Except for a few glitches that I
mentioned in a recent post (UNION with a constant expression, QMF SAVE
DATA) the proc seems to be doing what we need.

Thanks again.










_______________________________________________________________________________
Unencrypted electronic mail is not secure and may not be authentic.
If you have any doubts as to the contents please telephone to confirm.

This electronic transmission is intended only for those to whom it is
addressed. It may contain information that is confidential, privileged
or exempt from disclosure by law. Any claim to privilege is not waived
or lost by reason of mistaken transmission of this information.
If you are not the intended recipient you must not distribute or copy this
transmission and should please notify the sender. Your costs for doing
this will be reimbursed by the sender.
_______________________________________________________________________________



Harvey Wachtel

Re: Updating System Tables
(in response to Greg.Palgrave@BANKWEST.COM.AU)
I certainly will report on our experiences.

By the way, for anyone also curious about specific glitches with field
procedures, our extensive testing has now uncovered a third: a S0C4 in the
bowels of DB2 when using MAX(SEMESTER) in a query that defines a nested
table. We've got IBM looking into this one.



[login to unmask email]
EST.COM.AU To: [login to unmask email]
Sent by: DB2 Data cc:
Base Discussion Subject: Re: Updating System Tables
List
<[login to unmask email]>


1999-12-15 20:10
Please respond to
DB2 Data Base
Discussion List





Harvey,

Good Luck!

Would you be able to post the success (or otherwise) of the method to the
list at some stage - with any 'war stories' of course!? Like the cat, I'm
just curious....and you never know when you might need to try these things!

Regards,

Greg Palgrave
Database Administration Group
Information Services, Level 10, Tower
Bank of Western Australia
eMail : [login to unmask email] <no fishing expeditions please>


Harvey Wachtel <[login to unmask email]> on 15/12/99 23:14:05

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

To: [login to unmask email]
cc: (bcc: Greg Palgrave/SDG/SS/BankWest)

Subject: Re: Updating System Tables




Thanks to all who responded to my question abour updating restricted
systemn tables. We're aware of the dangers and would of course test our
changes ver-r-r-r-y carefully on our test database.

This inquiry is related to my initial inquiry to this list about a month
ago which was widely ignored, probably because its title contained the
phrase "field procedure", which is apparently something that few
installation besides us have had occasion to try. (This is a common
situation at our shop; I'm not sure if it's because we're more clever than
everyone else or, more likely, because we're more foolish; it goes back to
around 1970, when we were bitten by writing our own access methods with
EXCP, and I can tell you a fairly interesting war story about the Move
Inverse instruction. However, as I have a tendency to do, I digress.)

We have written a field procedure that we want to apply to a column that
appears in most of the tables in a database. The values already on the
database will not be changed by encoding, so I think they don't need to be
changed on the database. The field procedure will affect only values to be
added later. Without cheating, we would have to take the system down for a
week to unload the tables, redefine them with the field proc specification,
and reload them with exactly the same data they have now. We're hoping we
can just insert the appropriate definition into SYSFIELDS and flip on the
fieldproc indicators in SYSCOLUMNS, saving a week of downtime.

Details: The field proc is a solution to a Y2K (academic year 2000,
Doomsday=2000-07-01) problem. The column involved holds academic semesters
of the form "yyn" and we want them to sort properly. The procedure encodes
first-digit values in the range '0'-'5' to X'FA'-X'FF' so they will sort
above 99x. All values currently on the database begin with "7", "8" or
"9", so these aren't changed by encoding. Except for a few glitches that I
mentioned in a recent post (UNION with a constant expression, QMF SAVE
DATA) the proc seems to be doing what we need.

Thanks again.










_______________________________________________________________________________

Unencrypted electronic mail is not secure and may not be authentic.
If you have any doubts as to the contents please telephone to confirm.

This electronic transmission is intended only for those to whom it is
addressed. It may contain information that is confidential, privileged
or exempt from disclosure by law. Any claim to privilege is not waived
or lost by reason of mistaken transmission of this information.
If you are not the intended recipient you must not distribute or copy this
transmission and should please notify the sender. Your costs for doing
this will be reimbursed by the sender.
_______________________________________________________________________________