Determining the oldest log file that the Q Capture program requires

Answer

For V9.1 and later versions, use the asnqccmd command with the following parameters while the Q Capture program is running:

asnqccmd capture_server=server_name capture_schema=schema status show details

The command returns a report on Q Capture program status, including the following details:

• Path to DB2 log files
• Oldest DB2 log file needed for Q Capture restart
• Current DB2 log file captured

Here is sample output for these details:

Path to DB2 log files (DB2LOG_PATH) = /home2/szp/szp/
NODE0000/SQL00002/SQLOGDIR/
Oldest DB2 log file needed for Q Capture restart (OLDEST_DB2LOG) = S0000043.LOG
Current DB2 log file captured (CURRENT_DB2LOG) = S0000046.LOG

Recommendation: Run the Q Capture program whenever DB2 is up. This should keep the Q Capture program reading as close as possible to the end of the DB2 log, where the most recent log records are. Reading near the end of the log minimizes the number of older log files that the Q Capture program needs.

However, if you are using V8.2 or if the asnqccmd command is not working, then use this method instead:

1. Run the following SQL statement to obtain the log record sequence number for the most recent transaction that the Q Capture program has seen, processed, and recorded a history for in its control tables:

SELECT max(RESTART_SEQ)
FROM capschema.IBMQREP_CAPMON
WITH UR;

The RESTART_SEQ value is a CHAR(10) FOR BIT DATA column, which looks like 20 hexadecimal characters. For example:

00000000123456123456

Make note of the last 12 characters of the RESTART_SEQ value. This number is a log sequence number. The Q Capture program does not need log records that are older than this one. In the above example, the log sequence number is:

123456123456

2. From a DB2 command line, obtain the path for the active log files from the database configuration. For example:

db2 get db cfg for yourdbname

where yourdbname is the database name. From the output displayed on the screen, note the path for the active log files. For example:

Path to log files =C:\DB2\NODE0000\SQL00001\SQLLOGDIR\

3. Change the current working directory to be one above the path to the active log files. For example:

C:\>cd c:\DB2\NODE0000\SQL00001

4. From a DB2 command line, use the db2flsn command and the log sequence number from the first step to get the name of the oldest log file that the Q Capture program requires. For example:

C:\DB2\NODE0000\SQL00001\>db2flsn 123456123456

To run the db2flsn command, you must have read access to the SQLLOGCTL.LFH file, which is located one directory above the path to active log files (C:\DB2\NODE0000\SQL00001\).
The system retrieves and displays the name of the file that contains the log record identified by the log sequence number. For example:

Given LSN is contained in the log file S000123.LOG

5. Note the age of this retrieved log file. The Q Capture program needs this log file and more recent log files to perform a restart at any particular time. You must retain this log file and more recent log files to ensure continuous operation of the Q Capture programs. You can delete any older logs.