[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 836
  • Last Modified:

Rename table

Hi all,

I have a database with just 4 tables, and 4-5 applications writing dozens of records a seconds to each of them. To prevent the tables getting too big and also to solve the problem of archiving the data, I've written a C++ application to:
1. send a message to every application that writes to the tables to stop for 30 seconds
2. rename the tables every hour and create them again with the same names.

It works, but sometimes I get this error:
RENAME TABLE DBO.TR to TR2011100803
[IBM][CLI Driver][DB2/NT64] SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".  SQLSTATE=40001

My logs tell me that all of the applications received the message and stopped writing to the table.

The database is DB2 Express-C, LOCKTIMEOUT is set to 1.

What is wrong?
Is there a command I can send to DB2 to forcefully disconnect all clients and then rename the table?
Please help, this is urgent for me.

Thank you,

Gene

0
Mi-Jack
Asked:
Mi-Jack
  • 7
  • 6
  • 3
2 Solutions
 
Bill NolanCommented:
Just a couple of quick notes, off the top of my head:

1) Do you create any kind of pause between stopping/pausing the applications and doing the DB tasks?  Maybe the app's DB connection hasn't had time to close.
2) How are you "stopping" the applications?  Are the processes actually terminated, or are they being paused/sleeping somehow?  If they are paused, you may want to actually end them, and then restart them when finished.
3) Are you explicitly closing the DB connection?  If you are just cold-stopping the executable, consider something like the following sequence::
   TerminateDBConnections();
   Wait(1500);
   TerminateApplication();  // or PauseApplication();
4) Might there be general "lingering" open DB issues?  Could you have an open connection from ANY operation?
0
 
Bill NolanCommented:
5) Might the table lock be occurring elsewhere (e.g., from an external UI to the DB)?
0
 
Mi-JackAuthor Commented:
Hi Slimfinger

1. I wait 2 seconds
2. The process is just sleeping,
3. I do not close the connection, just stop doing anything (as in 2.)
4 and 5 - there are no other clients

How can actually closing the connection help? I thought that open connection in itself does not really lock any object...
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Mi-JackAuthor Commented:
Will closing the connection help?
0
 
Bill NolanCommented:
I won't suggest the "best" method; you will have to decide that.  But if you cannot conclusively find the problem via logic or documentation, etc., you might narrow down what is going on by experimenting with the above items.

1. wait longer
2. wait & terminate them & wait
3. close connection
4. by general lingering connections here, i meant in your app's

And to answer your question:  these were just off the top of my head.  I wouldn't be so sure about that open connection when you are renaming/creating tables.  You should test that explicitly.
0
 
Bill NolanCommented:
Also, it might be better to export the data somehow and then clear the tables.  Not sure if that would help with this issue, but it's probably more proper.
0
 
Mi-JackAuthor Commented:
I've found this impossible to do:

-- each table adds about 1.2 million records an hour
-- data needs to be kept for at least 6 months

Trying to read from this table and copy data some place else is just impossible.
0
 
Bill NolanCommented:
I am no DB expert, but I can tell you that those are precisely the type of operations that DB's are made to support.
0
 
Bill NolanCommented:
Also (as another aside) for that kind of traffic, there may be certain tunings that would greatly help, or even a different model altogether.
0
 
Mi-JackAuthor Commented:
I agree.
the management doesn't want to pay for the full version, nor hire a consultant, so I left my own devices (slim pickings)
The only other option I thought will work was table partitioning, but it's only available in the enterprise version.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Mi-Jack,

You can forcibly cause all connected applications to exit by issuing the "FORCE APPLICATION" command.

A more "scientific" approach would be to partition the table with the partition key being a function of time or date/time.  DB2 will automatically quit writing to one partition and start writing to the next.  You can then archive/initialize the "closed" partition.

And when you query the table with standard SQL, you still see the data in all of the partitions.


Good Luck,
Kent
0
 
Mi-JackAuthor Commented:
Hi Kdo,

In my comment above, this is only available in the enterprise version, or am I mistaken?
0
 
Mi-JackAuthor Commented:
"FORCE APPLICATION" is not a sql command, is it?
How can I execute it from an application?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
That's correct.  Partitioning requires the Enterprise version.

Do you have the DB2 command line processor available to you?  FORCE APPLICATION will kill the other tasks, and QUIESCE DATABASE can be used to quieten the database.

You can also use LOCK TABLE (you may have to issue repeatedly after the FORCE or QUIESCE command) to acquire exclusive access to the table.


BTW, management often has some really strange ideas....  ;)

Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:

If you're running a C++ application, the DB2 CLI is available to you.  :)  Just run the program from an environment that has run the DB2 profile.

  system ("DB2 connect to db1");
  system ("DB2 FORCE APPLICATION ALL");
  system ("DB2 set schema myschema");
  system ("DB2 RENAME TABLE A to B");
  system ("DB2 CREATE TABLE A as (SELECT * FROM TABLE B) DEFINITION ONLY");



Kent
0
 
Mi-JackAuthor Commented:
I am exploring some of the possibilities mentioned, but I'm keeping this open in hopes that somebody has other ideas.
Thank you.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now