Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to drop a column in a db2 table without droping a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
saubhik

New User


Joined: 21 Sep 2007
Posts: 35
Location: kolkata

PostPosted: Mon Dec 13, 2010 1:47 pm    Post subject: How to drop a column in a db2 table without droping a table
Reply with quote

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
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Dec 13, 2010 2:07 pm    Post subject: Re: How to drop a column in a db2 table without droping a ta
Reply with quote

saubhik wrote:
I am submitting the batch job with the DDL

What does the DDL look like ?
Back to top
View user's profile Send private message
saubhik

New User


Joined: 21 Sep 2007
Posts: 35
Location: kolkata

PostPosted: Mon Dec 13, 2010 2:16 pm    Post subject: Reply to: How to drop a column in a db2 table without dropin
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Dec 13, 2010 2:42 pm    Post subject:
Reply with quote

That's not ddl to drop a column, it is to create a table.
Back to top
View user's profile Send private message
saubhik

New User


Joined: 21 Sep 2007
Posts: 35
Location: kolkata

PostPosted: Mon Dec 13, 2010 3:16 pm    Post subject: Reply to: How to drop a column in a db2 table without dropin
Reply with quote

This DDL is organising the DB2 table structure.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Dec 13, 2010 4:20 pm    Post subject:
Reply with quote

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
View user's profile Send private message
saubhik

New User


Joined: 21 Sep 2007
Posts: 35
Location: kolkata

PostPosted: Mon Dec 13, 2010 5:16 pm    Post subject: Reply to: How to drop a column in a db2 table without dropin
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Extracting DDL for all list of intere... Ashishpanpaliya DB2 5 Mon May 21, 2018 4:08 pm
No new posts Searching a table for the field name? socker_dad COBOL Programming 8 Sat Mar 31, 2018 2:57 am
No new posts Need inputs on DB2 tablespace/table r... ashek15 DB2 3 Sat Mar 24, 2018 11:49 pm
No new posts Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am
No new posts COBOL declaration for table Poha Eater DB2 3 Mon Feb 26, 2018 6:58 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us