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: 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 unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm


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