[DB2-L] DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL

Ruediger Kurtz

[DB2-L] DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL
Roger,

you might want to be careful; we recently tried to change a NOT NULL column to NOT NULL WITH DEFAULT and Change Manager created an empty worklist, i.e. no changes but an "End of alter Table Section". The problem has been fixed in the meantime (PTF BPU1573 ), but still ....
We run CHANGE MANAGER 09.02.00.

Regards

Ruediger Kurtz
Abteilung Informatik Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon 09561 96-44147
Telefax 09561 96-44104
E-Mail [login to unmask email]
Internet www.HUK.de
=============
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Werner Strohmayr.
Vorstand: Rolf-Peter Hoenen (Sprecher), Wolfgang Flaßhoff, Stefan Gronbach, Klaus-Jürgen Heitmann, Dr. Christian Hofer, Dr. Wolfgang Weiler.
=============


________________________________
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Roger Hecq
Gesendet: Montag, 30. März 2009 21:36
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL

Unfortunately, changing the Null attribute or the Null default value requires dropping and recreating the table. I have been similarly disappointed. Fortunately, I have Change Manager.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492


________________________________
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Douwe van Sluis
Sent: Monday, March 30, 2009 3:05 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL

Dear list,

A colleague was surprised to find no support for ALTER TABLE to remove the NOT NULL clause of a COLUMN. I thought that should be possible via ALTER TABLE ALTER COLUMN. But so far we failed to find the syntax to do so. The syntax diagram of ALTER TABLE shows no syntax for this. Any ideas on how to achieve this via ALTER TABLE?
We know how to make the change via UNLOAD, DROP TABLE, RELOAD.


Vriendelijke groet,
Douwe van Sluis



________________________________

[ http://www.idug.org/ads/www/delivery/ai.php?filename=na09.jpg&CONTENTTYPE=jpeg ] < http://conferences.idug.org/Default.aspx?alias=conferences.idug.org/na >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >

________________________________

[ http://www.idug.org/ads/www/delivery/ai.php?filename=na09.jpg&CONTENTTYPE=jpeg ] < http://conferences.idug.org/Default.aspx?alias=conferences.idug.org/na >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *
_____________________________________________________________________

IDUG North America 2008 Attendee Testimonial-
"Each session I took away something that I could apply at my shop. I always learn something new."
_____________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

James Campbell

Re: DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL
(in response to Ruediger Kurtz)
The reason it can't be done is because a nullable column has a 'null
indicator byte'. Making a column nullable would imply stuffing the null
indicators into every row - even if they show "this column is NOT NULL'

James Campbell

On 30 Mar 2009 at 21:04, Douwe van Sluis wrote:

>
> Dear list,
>
> A colleague was surprised to find no support for ALTER TABLE to remove the NOT NULL clause
> of a COLUMN. I thought that should be possible via ALTER TABLE ALTER COLUMN. But so far
> we failed to find the syntax to do so. The syntax diagram of ALTER TABLE shows no syntax for
> this. Any ideas on how to achieve this via ALTER TABLE?
> We know how to make the change via UNLOAD, DROP TABLE, RELOAD.
>
> Vriendelijke groet,
> Douwe van Sluis
>
>
>
>
> Join us in Denver!
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an
> IDUG.org member, please register here.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *
_____________________________________________________________________

IDUG North America 2008 Attendee Testimonial-
"Each session I took away something that I could apply at my shop. I always learn something new."
_____________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Douwe van Sluis

Re: DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL
(in response to James Campbell)
James,

DB2 can handle this. It is possible to go from CHAR to VARCHAR, which means
adding two extra bytes to the column.
DB2 uses versioning to handle this.
My advice is tough to always do a REORG after these kind of changes. Not
necessary, but saver if you can spend a REORG.


Kind regards,
Douwe van Sluis

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *
_____________________________________________________________________

IDUG North America 2008 Attendee Testimonial-
"Each session I took away something that I could apply at my shop. I always learn something new."
_____________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Roger Hecq

Re: AW: [DB2-L] DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL
(in response to Douwe van Sluis)
Thanks for the warning. Yes, I always review my worklist to make sure that it isn't doing anything unexpected. Most exceptions are DUE (Dumb User Error).


Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Kurtz, Rüdiger
Sent: Tuesday, March 31, 2009 3:48 AM
To: [login to unmask email]
Subject: [DB2-L] AW: [DB2-L] DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL


Roger,

you might want to be careful; we recently tried to change a NOT NULL column to NOT NULL WITH DEFAULT and Change Manager created an empty worklist, i.e. no changes but an "End of alter Table Section". The problem has been fixed in the meantime (PTF BPU1573 ), but still ....
We run CHANGE MANAGER 09.02.00.

Regards

Ruediger Kurtz
Abteilung Informatik Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg

Telefon 09561 96-44147
Telefax 09561 96-44104
E-Mail [login to unmask email]
Internet www.HUK.de
=============
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Werner Strohmayr.
Vorstand: Rolf-Peter Hoenen (Sprecher), Wolfgang Flaßhoff, Stefan Gronbach, Klaus-Jürgen Heitmann, Dr. Christian Hofer, Dr. Wolfgang Weiler.
=============


________________________________

Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Roger Hecq
Gesendet: Montag, 30. März 2009 21:36
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL


Unfortunately, changing the Null attribute or the Null default value requires dropping and recreating the table. I have been similarly disappointed. Fortunately, I have Change Manager.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Douwe van Sluis
Sent: Monday, March 30, 2009 3:05 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL


Dear list,

A colleague was surprised to find no support for ALTER TABLE to remove the NOT NULL clause of a COLUMN. I thought that should be possible via ALTER TABLE ALTER COLUMN. But so far we failed to find the syntax to do so. The syntax diagram of ALTER TABLE shows no syntax for this. Any ideas on how to achieve this via ALTER TABLE?
We know how to make the change via UNLOAD, DROP TABLE, RELOAD.


Vriendelijke groet,
Douwe van Sluis




________________________________


Join us in Denver! < http://conferences.idug.org/Default.aspx?alias=conferences.idug.org/na >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >


________________________________

Join us in Denver! < http://conferences.idug.org/Default.aspx?alias=conferences.idug.org/na >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >


________________________________

Join us in Denver! < http://conferences.idug.org/Default.aspx?alias=conferences.idug.org/na >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >


_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *
_____________________________________________________________________

IDUG North America 2008 Attendee Testimonial-
"I made a number of contacts with other people having similar experiences to mine in some of our current initiatives, giving me additional resources to discuss issues and problems with."