Alter Table Drop Column

Brett Kaiser · #1 (**permalink**) 05-25-06, 09:51

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?

Marcus_A · #2 (**permalink**) 05-25-06, 11:32

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.

Brett Kaiser · #3 (**permalink**) 05-25-06, 11:48

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 Kaiser · #4 (**permalink**) 05-25-06, 11:49

Not that I'm a big fan of the evil empire either....

What does BMC stand for btw

Peter.Vanroose · #5 (**permalink**) 05-28-06, 14:40

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...

grofaty · #6 (**permalink**) 05-31-06, 03:45

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

Marcus_A · #7 (**permalink**) 05-31-06, 03:58

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.

Marcus_A · #8 (**permalink**) 05-31-06, 04:03

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?

Marcus_A · #9 (**permalink**) 05-31-06, 04:06

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.

JAYANTA_DATTA · #10 (**permalink**) 05-31-06, 04:15

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

Brett Kaiser · #11 (**permalink**) 05-31-06, 09:23

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

Wim · #12 (**permalink**) 06-01-06, 02:50

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 ?

Marcus_A · #13 (**permalink**) 06-01-06, 03:06

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.