Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Location
    Hyderabad, India
    Posts
    14

    Angry Unanswered: Dropping a Table

    Folks,
    I see a peculiar problem with the DB2 database..this is regarding deleting or droping of the table.
    I have around 2 lakh plus records in a table and i wanted to clear the table so since truncate command is not there i wanted to delete the data but when i issued that command i got a stupid error saying "The command was process as an sql statement because it was not a valid command line processor command. During SQL processing it returned: SQL0911N The current transaction has been rolled back because of a deadlock or time out. Reason Code "68". SQLSTATE = 40001.
    Im getting the same error when i tired to drop the table both from the command center as well as from the control center..
    Can any of you folks let me know the best way to handle this situation..
    This a ridiculous where u cannot drop the table if u have that bulk set of records..
    I would be happy if some one suggests a diff approach for ths..
    Regards,
    Krishna

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Krishna,
    Your problem is not the number of records in the table. You are having a lock timeout. That means another process has done something on the table to lock some portion of it. You probably need to prevent everyone else from accessing the table. You can put an exclusive lock on the table before deleting the data.

    HTH

    Andy

  3. #3
    Join Date
    Oct 2003
    Location
    Hyderabad, India
    Posts
    14
    Originally posted by ARWinner
    Krishna,
    Your problem is not the number of records in the table. You are having a lock timeout. That means another process has done something on the table to lock some portion of it. You probably need to prevent everyone else from accessing the table. You can put an exclusive lock on the table before deleting the data.

    HTH

    Andy
    Hi Andy,
    Can you please let me know how that exclusive lock could be applied before deleting the table?..or deleting the contents.. cause this is a temporary table which i created for my own and no application uses it. and does the delete happen if we lock the table exclusively.. Also please let me know whre the lock timeout could be increased?
    Regards,
    Krishna.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Krishna,
    You did not mention that it was a temporary table in your first post. Could you post the DDL to create the table?

    Andy

    Originally posted by vaddadik
    Hi Andy,
    Can you please let me know how that exclusive lock could be applied before deleting the table?..or deleting the contents.. cause this is a temporary table which i created for my own and no application uses it. and does the delete happen if we lock the table exclusively.. Also please let me know whre the lock timeout could be increased?
    Regards,
    Krishna.

  5. #5
    Join Date
    Oct 2003
    Location
    Hyderabad, India
    Posts
    14
    Originally posted by ARWinner
    Krishna,
    You did not mention that it was a temporary table in your first post. Could you post the DDL to create the table?

    Andy
    Andy,
    The temporary table that i was mentioning is not in the script but temporary physical table ex : create table temptable (field1 varchar(30));
    Thsi table is created on the userspace1 and it phsically exists in the database.
    Hope this gives better clarity
    Krishna

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What about a load replace with no data (or maybe just one row).

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Krishna,
    Then it can be accessed by another process. Check to see who has locks on the table.

    Andy

    Originally posted by vaddadik
    Andy,
    The temporary table that i was mentioning is not in the script but temporary physical table ex : create table temptable (field1 varchar(30));
    Thsi table is created on the userspace1 and it phsically exists in the database.
    Hope this gives better clarity
    Krishna

  8. #8
    Join Date
    Oct 2003
    Location
    Hyderabad, India
    Posts
    14
    Originally posted by ARWinner
    Krishna,
    Then it can be accessed by another process. Check to see who has locks on the table.

    Andy
    How to check the locks and remove them..if been locked from either control center or from any other..location?

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Krishna,
    You can try this tool to see who has the locks.

    http://chuzhoi_files.tripod.com/index.html

    Andy

    Originally posted by vaddadik
    How to check the locks and remove them..if been locked from either control center or from any other..location?

  10. #10
    Join Date
    Oct 2003
    Location
    Hyderabad, India
    Posts
    14

    Talking

    Originally posted by ARWinner
    Krishna,
    You can try this tool to see who has the locks.

    http://chuzhoi_files.tripod.com/index.html

    Andy
    Hi Andy,
    Thanks a lot for the tool.. its pretty good one..
    Regards,
    Krishna

Posting Permissions

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