Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2012
    Posts
    5

    Unanswered: DROP TABLE does not drop a table?

    I'm new to DB2. I'm trying to drop a staging table using command

    Code:
    DROP TABLE <SCHEMA>.<TABLENAME>;
    COMMIT;
    but the table does not seem to drop. I takes very long time to process and nothing seems to happen. I have CONTROL rights to that table. I'm using Toad for DB2 freeware. Any ideas what might be the reason for this? Locks etc.?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    list application - get snapshot for application agentid...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2012
    Posts
    5
    This table is used solely by ETL tool (IBM DataStage), so there is no other application using this staging table. Could you be more precise what you mean? Thanks!

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I don't understand that people that do not understand db2 have to handle objects and trying to drop these without knowing what is the impact
    all info about any command can be found in :
    LIST APPLICATIONS - IBM DB2 9.7 for Linux, UNIX, and Windows
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Nov 2012
    Posts
    5
    Thanks for this info! And also for the warm welcome for this forum :-) As an ETL Developer, I don't have experience in all the database platforms and I'm new to DB2.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    If you create a new table and then try to drop it, does it work? Do you have access to DB2 command line, CLP? Can you try it from there?

  7. #7
    Join Date
    Nov 2012
    Posts
    5
    Thanks @db2girl! Yes, I can log into DB2 computer (on AIX Server). So I have access on DB2 command line. I was thinking that can I use e.g. system catalog views to see if there is some locks on the table which prevents this? I read LIST APPLICATIONS help page but I did not understand what is the essential I'm getting from that command?

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Execute "drop table ....". While it's waiting/doing nothing, execute "db2 list applications show detail" from DB2 on AIX and see if there is any application in "Waiting for locks" status. To see what locks applications hold/wait for, you can use snapshot for application and locks or db2pd -locks -applications -transactions.

  9. #9
    Join Date
    Nov 2012
    Posts
    5
    Thanks! There was a lock for this table which prevented the DROP statement to complete.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •