473,377 Members | 1,153 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,377 software developers and data experts.

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 12881
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: James Knowlton | last post by:
Hello, I'm just returning to MS SQL Server after two years of dealing with Sybase ASE. I need to drop a column, using the alter table command. I keep getting an error indicating that a...
10
by: BuddhaBuddy | last post by:
Platform is DB2/NT 7.2.9 The table was created like this: CREATE TABLE MYTEST ( MYTESTOID bigint not null primary key, FK_OTHEROID bigint not null references other, FK_ANOTHEROID bigint not...
1
by: adolph | last post by:
I I've added a column to a table using a sql statement IE "ALTER TABLE sometable ADD COLUMN Direction" Now I would like to limit the valid entries for the field to North;East;South;West; How do I...
5
by: Markus | last post by:
I tried this: ALTER TABLE Dokumente DROP COLUMN docPrioID but I get this errormessage: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: COLUMN;ABLE Dokumente DROP;CONSTRAINT
4
by: TycoonUK | last post by:
Hi, As I do not have IE7 on my computer, I was wondering if there is a fault in my CSS Menu when using IE7. Please can someone look at my site - http://www.worldofmonopoly.co.uk and tell me...
1
by: sparksol | last post by:
I have a form with a drop down box. If you select an option in the drop down box (depending which option is selected) one or two textbox(es) and a submit button display. I would like to keep the...
5
by: Romulo NF | last post by:
Greetings, Im back here to show the new version of the drag & drop table columns (original script ). Ive found some issues with the old script, specially when trying to use 2 tables with...
2
by: =?Utf-8?B?aUhhdkFRdWVzdGlvbg==?= | last post by:
I have to retrieve the cloums from the table, but there are few columns in a table of datatype date and int and the default values are 9999-12-31 23:59:59.997 and -2147483648. My questions...
10
by: Dean | last post by:
My client has a db I am working that uses temp tables. During an update procedure, I had the code If fTableExists(tempTblName) = True Then DoCmd.DeleteObject acTable, tempTblName Then I thought...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.