Topic
  • 7 replies
  • Latest Post - ‏2013-09-10T14:07:57Z by 9RYQ_Kiran_mk
DF5V_krishna_Bissa
DF5V_krishna_Bissa
42 Posts

Pinned topic Not able to deleting the table

‏2013-07-31T08:24:15Z |

Hi all,

By mistake i have created a table on "DB2 v9.5.0.8" on AIX witht the naming covention as  "testCD.CATENTATTR"

Requirement: i want to drop the table testCD.CATENTATTR now.- table is a empty 

 

Details:

db2 "select tabname from syscat.tables where tabschema='testCD'"

TABNAME
--------------------------------------------------------------------------------------------------------------------------------
CATENTATTR

table drop commands that i tried.

1. db2 "drop table testCD.CATENTATTR"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "TESTCD.CATENTATTR" is an undefined name.  SQLSTATE=42704

 

2.db2 drop table testCD.CATENTATTR
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "TESTCD.CATENTATTR" is an undefined name.  SQLSTATE=42704
 

3.db2 "drop table "testCD"."CATENTATTR""
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "TESTCD.CATENTATTR" is an undefined name.  SQLSTATE=42704

I was not able to delete the table, as the DB2 is trying to delete TESTCD.CATENTATTR which is different from the name that i have mentioned in the command line "testCD"."CATENTATTR"".

I know, we should not have the schemaname with the combination of upper and lowercase letters.but i have created the table by mistake and now i want to delete it.Please let me know on the right approch to do that.

Other actions:tried renaming the table name-noluck

                         :tried dropping the schema testCD-noluck

 

  • DF5V_krishna_Bissa
    DF5V_krishna_Bissa
    42 Posts
    ACCEPTED ANSWER

    Re: Not able to deleting the table

    ‏2013-09-08T16:00:06Z  

    Hi Krishna,

    I do understand this kind of issue happens some times and even I faced it.  Can you please try dropping the table with GUI's like Control Center or Data Studio or  DBVisualizer.  Try with the same and let me know.
     

    Best Regards,

    Kiran.M.K.

    Hi kiran,

    unfortunelty  we dont have GUI in our environment.

     

  • jdokos
    jdokos
    2 Posts

    Re: Not able to deleting the table

    ‏2013-08-05T18:29:15Z  

    Try this:

    db2 'drop table "testCD".catentattr'

  • 9RYQ_Kiran_mk
    9RYQ_Kiran_mk
    78 Posts

    Re: Not able to deleting the table

    ‏2013-08-07T08:03:06Z  

    You have used the schema and table names as the combination of uppercase and lowercase.  So better use the same without changing the cases with double quotes as below.

     

    db2 "drop table "testCD"."CATENTATTR""

    Let me know in case if you are facing any issue.

     

    Thanks in advance.

    Best Regards,

    Kiran.M.K.

  • DF5V_krishna_Bissa
    DF5V_krishna_Bissa
    42 Posts

    Re: Not able to deleting the table

    ‏2013-08-07T18:53:16Z  

    You have used the schema and table names as the combination of uppercase and lowercase.  So better use the same without changing the cases with double quotes as below.

     

    db2 "drop table "testCD"."CATENTATTR""

    Let me know in case if you are facing any issue.

     

    Thanks in advance.

    Best Regards,

    Kiran.M.K.

    Hi Kiran,

    thanks for your reply.I have tried the solution that you provided .But no luck.Please find the set of the error messages the i have pasted in the initial post.

  • 9RYQ_Kiran_mk
    9RYQ_Kiran_mk
    78 Posts

    Re: Not able to deleting the table

    ‏2013-08-08T05:06:53Z  

    Hi Kiran,

    thanks for your reply.I have tried the solution that you provided .But no luck.Please find the set of the error messages the i have pasted in the initial post.

    Hi Krishna,

    I do understand this kind of issue happens some times and even I faced it.  Can you please try dropping the table with GUI's like Control Center or Data Studio or  DBVisualizer.  Try with the same and let me know.
     

    Best Regards,

    Kiran.M.K.

  • DF5V_krishna_Bissa
    DF5V_krishna_Bissa
    42 Posts

    Re: Not able to deleting the table

    ‏2013-09-08T16:00:06Z  

    Hi Krishna,

    I do understand this kind of issue happens some times and even I faced it.  Can you please try dropping the table with GUI's like Control Center or Data Studio or  DBVisualizer.  Try with the same and let me know.
     

    Best Regards,

    Kiran.M.K.

    Hi kiran,

    unfortunelty  we dont have GUI in our environment.

     

  • Jotac
    Jotac
    82 Posts

    Re: Not able to deleting the table

    ‏2013-09-09T10:01:13Z  

    TABSCHEMA field is padded with space up to 8 chars,

    thry this db2 "drop table "testCD  "."CATENTATTR""

    note the 2 spaces on schema name

    otherwhyse I guess there some spurious unprintable character?

    ciao

    GIovanni

  • 9RYQ_Kiran_mk
    9RYQ_Kiran_mk
    78 Posts

    Re: Not able to deleting the table

    ‏2013-09-10T14:07:57Z  

    Hi kiran,

    unfortunelty  we dont have GUI in our environment.

     

    You can install the DB2 Data Studio which is freely available in your local machine (if  allowed) and try accessing your remote DB to drop that.  Otherwise try whether you are able to rename the table name ?

     

    Best Regards,

    Kiran.M.K.