| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-17-03, 11:44
|
|
Registered User
|
|
Join Date: Oct 2003
Location: Hyderabad, India
Posts: 14
|
|
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
|
|
10-17-03, 12:01
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
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
|
|
10-17-03, 12:04
|
|
Registered User
|
|
Join Date: Oct 2003
Location: Hyderabad, India
Posts: 14
|
|
|
|
Quote:
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.
|
|
10-17-03, 12:14
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
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
Quote:
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.
|
|
|
10-17-03, 14:32
|
|
Registered User
|
|
Join Date: Oct 2003
Location: Hyderabad, India
Posts: 14
|
|
Quote:
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
|
|
10-17-03, 14:33
|
|
Registered User
|
|
Join Date: May 2003
Location: USA
Posts: 5,198
|
|
What about a load replace with no data (or maybe just one row).
|
|
10-17-03, 14:40
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
Krishna,
Then it can be accessed by another process. Check to see who has locks on the table.
Andy
Quote:
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
|
|
|
10-17-03, 14:47
|
|
Registered User
|
|
Join Date: Oct 2003
Location: Hyderabad, India
Posts: 14
|
|
Quote:
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?
|
|
10-17-03, 14:51
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
Krishna,
You can try this tool to see who has the locks.
http://chuzhoi_files.tripod.com/index.html
Andy
Quote:
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-17-03, 15:27
|
|
Registered User
|
|
Join Date: Oct 2003
Location: Hyderabad, India
Posts: 14
|
|
Hi Andy,
Thanks a lot for the tool.. its pretty good one..
Regards,
Krishna
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Dropping a Table
