CODEWITHSUNDEEP
javajdbcdb2connection-pooling
DevGuru
DevGuru

Reputation: 23

Db2 transaction logs are locked and not able to archive it

The scenario is in my java code where I have created a temp table and filled it with records which i need to delete and then I am deleting the records from various tables by getting the data from temp table consecutively. At a time only 1000 records are deleted from all tables and then statements are committed and again next 1000 records deleted and loop continues until all the records are deleted .

The sql statements are simple delete queries. Since the data is in millions and table is indexed so my transaction logs started to fill and didn't allow the db to archive it while the code is running .Since I am committing all the statements but still something is holding the transaction logs and preventing it from archiving.

I need to know what can lock the transaction logs from archiving since all statements are committed at regular interval. There is connection pooling in java. Connection is closed after executing the whole code. Does connection or prepared statement can keep the transaction active so disabling it from archiving, or any other process?

Db is db2 and logging is archival logging. Can't use circular logging, and can't use NLI command.

Upvotes: 1

Views: 624

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12299

Use the following query to find the application ID of the application that has the oldest transaction.

select appl_id_oldest_xact from sysibmadm.snapdb

If you want to know what statements / transactions this (or other) application issued, use an event monitor for activities. Configuring data collection for an activity event monitor.

-- Temporarily turn the collection on with the following query
ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS
-- Run you workload
-- Turn the collection off
ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT ACTIVITY DATA NONE
-- Find the corresponding information in the corresponding event monitor tables

You can collect the information to tables of this monitor about all applications or for individual application with a known application handle as well. Collecting data for individual activities.

Upvotes: 2

Related Questions