|
View previous topic :: View next topic
|
| Author |
Message |
saubhik
New User
Joined: 21 Sep 2007
Posts: 35
Location: kolkata
|
|
|
|
Hi All,
I am trying to delete a column of a DB2 table and reorganise the table structure without deleting the table.I am trying to to it in Dev region in my test table using DB2 admin command ALC.But when I am submitting the batch job with the DDL it is showing Maxcc 0 but it is not getting reflected in the table.
I have also tried with AL and ALM command giving the correct details of the table.
Can you please suggest how to do this without dropping the table.
Thanks again!!! |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| saubhik wrote: |
| I am submitting the batch job with the DDL |
What does the DDL look like ? |
|
| Back to top |
|
|
saubhik
New User
Joined: 21 Sep 2007
Posts: 35
Location: kolkata
|
|
|
|
DDL looks like the below:
| Code: |
CREATE TABLE xx.yy
(c1 CHAR(3) FOR SBCS DATA NOT NULL
WITH DEFAULT 'OLD' ,
c2 CHAR(35) FOR SBCS DATA NOT NULL
WITH DEFAULT ,
C3 CHAR(3) FOR SBCS DATA NOT NULL
WITH DEFAULT )
IN DATABASE DSNxxxx
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE ;
COMMIT ; |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| That's not ddl to drop a column, it is to create a table. |
|
| Back to top |
|
|
saubhik
New User
Joined: 21 Sep 2007
Posts: 35
Location: kolkata
|
|
|
|
| This DDL is organising the DB2 table structure. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
No, this ddl is creating a table, hence CREATE table.
Any DDL that changes a table structure would commence with ALTER table.
Although "standard" SQL allows :
ALTER TABLE table_name DROP COLUMN column_name
The DB2 SQLreference doens't mention it. Only following three are mentioned :
ALTER TABLE table_name ADD COLUMN column_name
ALTER TABLE table_name ALTER COLUMN column_name
ALTER TABLE table_name RENAME COLUMN column_name
Which would indicate that drop column isn't implemented in V9. |
|
| Back to top |
|
|
saubhik
New User
Joined: 21 Sep 2007
Posts: 35
Location: kolkata
|
|
|
|
| So it is not possible to drop a column in a db2 table without droping a table.I am using DB2 V9. |
|
| Back to top |
|
|
|
|
