Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1278
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: 1278
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: 1278
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 DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us