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

dropping a column in DB2

P: n/a
sat
hi all!
I have a doubt regarding the dropping of a column.
As i've seen, we have an option like
"alter table <table namedrop column <column name>" in
oracle.
Do we have any method in db2 to drop a column?
Or is there any process for dropping a column in db2?
if so, please let me know.
Thanks in Advance,
sat.

Aug 17 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
sat wrote:
hi all!
I have a doubt regarding the dropping of a column.
As i've seen, we have an option like
"alter table <table namedrop column <column name>" in
oracle.
Do we have any method in db2 to drop a column?
Or is there any process for dropping a column in db2?
if so, please let me know.
Yes, DB2 V9 supports that.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 17 '06 #2

P: n/a
sat

Knut Stolze wrote:
sat wrote:
hi all!
I have a doubt regarding the dropping of a column.
As i've seen, we have an option like
"alter table <table namedrop column <column name>" in
oracle.
Do we have any method in db2 to drop a column?
Or is there any process for dropping a column in db2?
if so, please let me know.

Yes, DB2 V9 supports that.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Hi Knut Stolze!
As u said it works on db2 V9 but i am using db2 V8.2 so if
i need to drop column on that what i need to do?

Thanks in Advance,
sat.

Aug 17 '06 #3

P: n/a
In DB2 V8.2 the straight forward case is to:
RENAME the table
CREATE TABLE LIKE the renamed table
INSERT SELECT or LOAD from CURSOR
DROP the old table.

If you have complex dependencies (RI, functions, triggers), take a look
at my post on the ALTOBJ procedure in this forum:
http://www.gatago.com/comp/databases.../24027879.html

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 17 '06 #4

P: n/a

Serge Rielau wrote:
In DB2 V8.2 the straight forward case is to:
RENAME the table
CREATE TABLE LIKE the renamed table
INSERT SELECT or LOAD from CURSOR
DROP the old table.

If you have complex dependencies (RI, functions, triggers), take a look
at my post on the ALTOBJ procedure in this forum:
http://www.gatago.com/comp/databases.../24027879.html
Do we need a certain fixpak level to run the example? I tried it on
fixpak 9, but get errors because systools tables are missing (?). If
fixpak 9 is sufficient, where can I find the definition of systools
tables

Thanx
/Lennart
>>>
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2 "CALL
SYSPROC.ALTOBJ('VALIDATE','CREATE TABLE DB2INST1.T(C1 BIGINT, C3 DOUBLE
NOT NULL)',-1,?)"
SQL0443N Routine "SYSPROC.ALTOBJ" (specific name "ALTOBJ") has
returned an
error SQLSTATE with diagnostic text "SQL0204 Reason code or token:
DB2INST1.T(C1". SQLSTATE=38553
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2 "select tabname from
syscat.tables where tabschema = 'SYSTOOLS'"

TABNAME

--------------------------------------------------------------------------------------------------------------------------------
HMON_ATM_INFO

HMON_COLLECTION

POLICY
3 record(s) selected.

Aug 18 '06 #5

P: n/a
This function was introduced in DB2 V8.2 (FP7).
Try running db2updv8

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 18 '06 #6

P: n/a

Serge Rielau wrote:
This function was introduced in DB2 V8.2 (FP7).
Try running db2updv8
Thanks. Unfortenate that doesnt help. I still get the error:

[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2 "CALL
SYSPROC.ALTOBJ('VALIDATE','CREATE TABLE DB2INST1.T(C1 BIGINT, C3 DOUBLE
NOT NULL)',-1,?)"
SQL0443N Routine "SYSPROC.ALTOBJ" (specific name "ALTOBJ") has
returned an
error SQLSTATE with diagnostic text "SQL0204 Reason code or token:
DB2INST1.T(C1". SQLSTATE=38553

Anything else that needs to be done?
Below are steps taken to reproduce error:

[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ uname -a
Linux wb-03 2.4.21-27.ELsmp #1 SMP Wed Dec 1 21:59:02 EST 2004 i686
i686 i386 GNU/Linux

[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08022"
with level identifier "03030106".
Informational tokens are "DB2 v8.1.0.89", "OD_14086", "MI00105_14086",
and
FixPak "9".
Product is installed at "/opt/IBM/db2/V8.1".

[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2updv8 -d tmp
DB2UPDV8 complete successfully for database 'tmp'.

[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2stop
2006-08-18 22.19.40 0 0 SQL1064N DB2STOP processing was
successful.
SQL1064N DB2STOP processing was successful.

[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2start
2006-08-18 22.19.45 0 0 SQL1063N DB2START processing was
successful.
SQL1063N DB2START processing was successful.
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2 connect to tmp

Database Connection Information

Database server = DB2/LINUX 8.2.2
SQL authorization ID = DB2INST1
Local database alias = TMP

[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ cat def.ddl
DROP FUNCTION FOO;
DROP TABLE T;
CREATE TABLE T (
c1 INT NOT NULL GENERATED ALWAYS AS IDENTITY,
c2 FLOAT
);
INSERT INTO T (c2) VALUES 10, 20, 30, 40, 50, 60, 70;

DROP VIEW V;
CREATE VIEW V AS SELECT c1, c2 FROM T;

DROP TRIGGER Trg1;
CREATE TRIGGER Trg1
BEFORE INSERT ON T
REFERENCING NEW AS N
FOR EACH ROW
SET n.c2 = COALESCE(n.c2, 7);

CREATE FUNCTION FOO ()
RETURNS FLOAT
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
NOT DETERMINISTIC
RETURN
select c2 from (
select c2, rownumber() over () as x from T
) Y where x = 1 ;

[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2 -tf def.ddl
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.

[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2 "CALL
SYSPROC.ALTOBJ('VALIDATE','CREATE TABLE DB2INST1.T(C1 BIGINT, C3 DOUBLE
NOT NULL)',-1,?)"
SQL0443N Routine "SYSPROC.ALTOBJ" (specific name "ALTOBJ") has
returned an
error SQLSTATE with diagnostic text "SQL0204 Reason code or token:
DB2INST1.T(C1". SQLSTATE=38553
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 18 '06 #7

P: n/a
le*****@kommunicera.umea.se wrote:
Serge Rielau wrote:
>This function was introduced in DB2 V8.2 (FP7).
Try running db2updv8

Thanks. Unfortenate that doesnt help. I still get the error:

[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2 "CALL
SYSPROC.ALTOBJ('VALIDATE','CREATE TABLE DB2INST1.T(C1 BIGINT, C3 DOUBLE
NOT NULL)',-1,?)"
SQL0443N Routine "SYSPROC.ALTOBJ" (specific name "ALTOBJ") has
returned an
error SQLSTATE with diagnostic text "SQL0204 Reason code or token:
DB2INST1.T(C1". SQLSTATE=38553
Actually this has nothing to do with SYSTOOLS.
It says it can't find the usertable. What is a bit odd is that it
includes the "(C1". Just out of curiosity: Add a space after T before (.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 18 '06 #8

P: n/a

Serge Rielau wrote:
le*****@kommunicera.umea.se wrote:
Serge Rielau wrote:
This function was introduced in DB2 V8.2 (FP7).
Try running db2updv8
Thanks. Unfortenate that doesnt help. I still get the error:

[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2 "CALL
SYSPROC.ALTOBJ('VALIDATE','CREATE TABLE DB2INST1.T(C1 BIGINT, C3 DOUBLE
NOT NULL)',-1,?)"
SQL0443N Routine "SYSPROC.ALTOBJ" (specific name "ALTOBJ") has
returned an
error SQLSTATE with diagnostic text "SQL0204 Reason code or token:
DB2INST1.T(C1". SQLSTATE=38553
Actually this has nothing to do with SYSTOOLS.
It says it can't find the usertable. What is a bit odd is that it
includes the "(C1". Just out of curiosity: Add a space after T before (.
Thanks again Serge. That did the trick
/Lennart

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 19 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.