Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Alter Table Drop Column

    Is this possible in DB2 OS/390 V 7.2?

    Apparently the answer seems to be no.

    UNLOAD/DROP/CREATE/LOAD/GRANT/REBIND

    What a total pain in the donkey

    Any other alternative?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That is why most shops have tools like BMC ALTER which will do all of that for you automatically. There is little incentive for IBM to make these changes online because of the widespread use of these tools.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Marcus_A
    That is why most shops have tools like BMC ALTER which will do all of that for you automatically. There is little incentive for IBM to make these changes online because of the widespread use of these tools.

    I say this with all the love I can muster because I got into this business because of DB2 Big Iron....

    I hate IBM
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not that I'm a big fan of the evil empire either....


    What does BMC stand for btw
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    It depends on what you really want to achieve.

    * If it's sufficient to make that column invisible to users, you may create a view.
    * If it's really for saving space, and the column is a VARCHAR, make all entries empty (and optionally create the view).
    * If it's not a VARCHAR, and you're on v8, first
    ALTER TABLE tab-name ALTER col-name SET DATA TYPE VARCHAR(n)
    with n sufficiently large, then change the values.

    Dropping and re-creating the table, the only "real" solution, has the big disadvantage that everything referring that table (like views, referential constraints, packages, indexes, ...) are either dropped or invalidated. That's what BMC (http://www.bmc.com/) tries to compensate for, but in most cases you're better off keeping the column...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    read this out!!! There is an excellent solution to solve the problem:
    http://dbforums.com/showthread.php?t=1217586
    http://dbforums.com/showthread.php?t=1217517

    To drop column in db2 v8.2 for LUW (don't know is this works for DB2 for zOS):
    Code:
    CREATE TABLE SCHEM1.TB_TEST1 (COL1 SMALLINT NOT NULL, COL2 DEC(7,4));
    INSERT INTO SCHEM1.TB_TEST1 VALUES (1,1);
    SELECT * FROM SCHEM1.TB_TEST1;
    
    DESCRIBE TABLE SCHEM1.TB_TEST1;
    
    CALL SYSPROC.ALTOBJ ('APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE SCHEM1.TB_TEST1 (COL1 SMALLINT NOT NULL)', -1, ? );
    
    DESCRIBE TABLE SCHEM1.TB_TEST1;
    
    SELECT * FROM SCHEM1.TB_TEST1;
    The beauty: column is deleted and no export is needed!

    Note: be very very very carful what are you doing with this sysproc.altobj store procedure, because there is no way back.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 05-31-06 at 04:48.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Brett Kaiser
    What does BMC stand for btw
    Something like Bonner, Moore, and Cluer (3 guys who started the company). I met one of these guys (forgot which one) in Houston about 1981 when they were a small IT consulting company who also had started developing some IMS utility tools.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Brett Kaiser
    I say this with all the love I can muster because I got into this business because of DB2 Big Iron....

    I hate IBM
    Most people use 3rd party front-end administration tools for DB2 and Oracle databases. MS SQL Server probably has the best front-end, but even for that some DBA's use 3rd party tools.

    BTW, Microsoft is now the Evil Empire. Where have you been for the last 10 years?
    Last edited by Marcus_A; 06-01-06 at 03:58.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by grofaty
    To drop column in db2 v8.2 for LUW (don't know is this works for DB2 for zOS)
    No it does not work for z/OS.

    BTW, on LUW the SP does export the data, drop the table, recreate the table, and reload the data. It just does it without you seeing it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    The "SYSPROC.ALTOBJ() " sp for dropping a Column works only in UDB V8.X.


    On Z/OS it doesnot work. IBM material also speaks the same.

    Thanks,
    Jayanta Datta
    New Delhi

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes I realize that all these solutions don't work for z/os

    Thanks again big blue

    And dig this...I just found that the business requires a cloumn to be enlarged.

    I have 50 sprocs that have to be changed...or hopefully they are just in Cursors, but then that means the front end has to change
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Brett Kaiser
    Yes I realize that all these solutions don't work for z/os
    I share your feelings. I used to work on DB2 8.2 on Windows and AIX, and all the things I took for granted all of a sudden don't work anymore.

    Do you have an alternative DB2 infocenter site for mainframe DB2 ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Brett Kaiser
    Yes I realize that all these solutions don't work for z/os

    Thanks again big blue

    And dig this...I just found that the business requires a cloumn to be enlarged.

    I have 50 sprocs that have to be changed...or hopefully they are just in Cursors, but then that means the front end has to change
    Sounds to me like you are a lazy crybaby.

    There are tools from BMC and Platinum (CA) that will do what you want that IBM helped fund and promote when they were developed by these IBM Business Partners more than 15 years ago.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Wim
    Do you have an alternative DB2 infocenter site for mainframe DB2 ?
    Sure: infocenter DB2 v8 for z/OS
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  15. #15
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by grofaty
    CALL SYSPROC.ALTOBJ (...)
    Are you sure that related objects (VIEWs, PLANs, ...) are not affected by this surrogate ALTER TABLE?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •