Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Mar 31
    2009

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

    Ruediger Kurtz
    [HUK]
    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
    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
    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
    [UBS AG]
    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."

    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact