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.
