By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,658 Members | 1,501 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,658 IT Pros & Developers. It's quick & easy.

Can i drop a cloumn from a table in DB2 for Z/OS

P: n/a
Can i drop a cloumn from a table in DB2 for Z/OS?
Jun 27 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On 4 Cze, 09:58, Joseph <josephrathin...@gmail.comwrote:
Can i drop a cloumn from a table in DB2 for Z/OS?
If you mean 'alter table so that it has one column less' AFAIK you
cannot :(, this is not Informix, i'm afraid

regards
Z
Jun 27 '08 #2

P: n/a
Zbikow wrote:
On 4 Cze, 09:58, Joseph <josephrathin...@gmail.comwrote:
Can i drop a cloumn from a table in DB2 for Z/OS?

If you mean 'alter table so that it has one column less' AFAIK you
cannot :(, this is not Informix, i'm afraid

regards
Z
Yup, that's correct - neither DB2 8 nor DB2 9 for z/OS support ALTER
TABLE DROP COLUMN (IIRC, DB2 9 for LUW introduced it, although it was
also possible(ish) from within the control center on DB2 8 for LUW).

One possibility for doing this is to export+drop+recreate+import, e.g.
assuming a table called TABLE1 with columns COL1, COL2, and COL3, you
could do something like this to drop COL3:

EXPORT TO data.ixf OF IXF SELECT COL1, COL2 FROM TABLE1;
DROP TABLE TABLE1;
CREATE TABLE TABLE1 (COL1 ..., COL2 ...);
IMPORT FROM data.ixf OF IXF INSERT INTO TABLE1;

(can't use IMPORT ... CREATE as it's not supported with DB2 for z/OS
IIRC)

Alternatively, you could try using a "NO DATA" MQT to accomplish this:

CREATE TABLE TABLE2 AS (SELECT COL1, COL2 FROM TABLE1) WITH NO DATA;
INSERT INTO TABLE2 SELECT COL1, COL2 FROM TABLE1;
DROP TABLE1;
RENAME TABLE2 TO TABLE1;

Either way, you'll need to be careful to recreate any indexes /
constraints on the new table that existed on the old one.
Cheers,

Dave.
Jun 27 '08 #3

P: n/a
Either way, you'll need to be careful to recreate any indexes /
constraints on the new table that existed on the old one.

Cheers,

Dave.
....as well as grants on the table, they're lost too. This is a thing
that one can easily forget. You need to extract grants from DB2
catalog beforehand.

regards
Zbikow

Jun 27 '08 #4

P: n/a
On Jun 4, 8:14 pm, Zbikow <zbik...@wp.plwrote:
Either way, you'll need to be careful to recreate any indexes /
constraints on the new table that existed on the old one.
Cheers,
Dave.

...as well as grants on the table, they're lost too. This is a thing
that one can easily forget. You need to extract grants from DB2
catalog beforehand.

regards
Zbikow
Thanks a lot guys.
Jun 27 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.