469,361 Members | 2,298 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,361 developers. It's quick & easy.

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

Can i drop a cloumn from a table in DB2 for Z/OS?
Jun 27 '08 #1
4 12418
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
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
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
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.

Similar topics

2 posts views Thread by James Knowlton | last post: by
1 post views Thread by adolph | last post: by
5 posts views Thread by Markus | last post: by
4 posts views Thread by TycoonUK | last post: by
2 posts views Thread by =?Utf-8?B?aUhhdkFRdWVzdGlvbg==?= | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.