hi all!
I have a doubt regarding the dropping of a column.
As i've seen, we have an option like
"alter table <table name> drop 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.

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 name> drop 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

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 name> drop 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.

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/

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.

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/

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/

lennart@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/

Serge Rielau wrote:
> lennart@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/