ALTER COLUMN QUESTION

Dave Nance

ALTER COLUMN QUESTION
z/OS db2 v9

  I wanted to check something with you all. In my new position I can't test this
out, so I am hoping someone on list can help me out. I am having a bunch of
columns altered from NOT NULL and some NOT NULL WITH DEFAULT to NULL WITH
DEFAULT. I am being told that the table has to be dropped and recreated.
Whereas, I thought the following would do the trick:

ALTER TABLE A_AUD_ADD_TB ALTER COLUMN G_TBL_NAME_CD WITH DEFAULT NULL;

David Nance
 




________________________________
From: Chris Hoelscher <[login to unmask email]>
To: [login to unmask email]
Sent: Tue, May 3, 2011 1:18:48 PM
Subject: Re: [DB2-L] DB2 Health checker inputs

We use RTS to monitor # extents on tablespaces and indexspaces... may be an
easier alternative for you

Chris Hoelscher
IDMS & DB2 Database Administrator
502-476-2538

I refuse to repeat gossip - so listen closely the first time


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Anil Kumar
Sent: Tuesday, May 03, 2011 10:40 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 Health checker inputs

Hi All,

When I googled "db2 for zos health checker" one of the links displayed is
https://www-304.ibm.com/support/docview.wss?uid=swg27005118.This link has the
document link which reads"Health Check Your DB2 UDB for z/OS System" . However
when I click on this , it says it cannot be accessed because of some reasons.

If anyone has this document downloaded , request you to please send te same.

What I am looking for is some basic inputs on what needs to be included for
basic DB2 health check up. As a part of this we have identified a list of all
db2 objects whose extents > certain threshold. This will be one of our first
health checking criteria.May be in this line I would request to provide some
basic DB2 health checking factors that could be developed. If any of these are
available , request to please send across the links / pages to get the idea.All
inputs would be greatly appreciated.

Thanks,
Anil.

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv


The information transmitted is intended only for the person or entity to which
it is addressed and may contain CONFIDENTIAL material.  If you receive this
material/information in error, please contact the sender and delete or destroy
the material/information.


_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

Peter Backlund

Re: ALTER COLUMN QUESTION
(in response to Dave Nance)
The correct syntax is
    Alter table ttt alter column ccc set default null

My test returns SQLCode -574

Peter

On 2011-05-03 21:46, Dave Nance wrote:
z/OS db2 v9
  I wanted to check something with you all. In my new position I can't test this out, so I am hoping someone on list can help me out. I am having a bunch of columns altered from NOT NULL and some NOT NULL WITH DEFAULT to NULL WITH DEFAULT. I am being told that the table has to be dropped and recreated. Whereas, I thought the following would do the trick:
 
ALTER TABLE A_AUD_ADD_TB ALTER COLUMN G_TBL_NAME_CD WITH DEFAULT NULL;
 
David Nance
 



From: Chris Hoelscher <[login to unmask email]>
To: [login to unmask email]
Sent: Tue, May 3, 2011 1:18:48 PM
Subject: Re: [DB2-L] DB2 Health checker inputs

We use RTS to monitor # extents on tablespaces and indexspaces... may be an easier alternative for you

Chris Hoelscher
IDMS & DB2 Database Administrator
502-476-2538

I refuse to repeat gossip - so listen closely the first time


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Anil Kumar
Sent: Tuesday, May 03, 2011 10:40 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 Health checker inputs

Hi All,

When I googled "db2 for zos health checker" one of the links displayed is https://www-304.ibm.com/support/docview.wss?uid=swg27005118.This link has the document link which reads"Health Check Your DB2 UDB for z/OS System" . However when I click on this , it says it cannot be accessed because of some reasons.

If anyone has this document downloaded , request you to please send te same.

What I am looking for is some basic inputs on what needs to be included for basic DB2 health check up. As a part of this we have identified a list of all db2 objects whose extents > certain threshold. This will be one of our first health checking criteria.May be in this line I would request to provide some basic DB2 health checking factors that could be developed. If any of these are available , request to please send across the links / pages to get the idea.All inputs would be greatly appreciated.

Thanks,
Anil.

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv


The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material.  If you receive this material/information in error, please contact the sender and delete or destroy the material/information.


_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv




The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.


-- +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Rasundavagen 94 | Country Code (Sweden): 46 | | S-169 57 SOLNA | Skype: BacklundDB2 | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

Peter Vanroose

Re: ALTER COLUMN QUESTION
(in response to Peter Backlund)
The default can indeed be changed this way (in version 9), but not the nullability.
So indeed, drop/create will be needed to go from NOT NULL to nullable.
The other way around, one could consider adding a check constraint instead.

-- Peter Vanroose
ABIS Training & Consulting
www.abis.be

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information