Technical Blog Post
Abstract
50 DB2 Nuggets #1 : Tech Tip - Demystifying the db2ReadLog API
Body
The db2ReadLog API is used to read log records from the DB2® database logs, or queries the Log Manager for current log state information.
Before a Embedded SQLC program can be used it has to be precompiled, bound, compiled and linked. Here is an example on how to use the db2ReadLog API using the dblogconn.sqc sample SQLC program that is provide with the DB2 sample scripts. This program reads the database log files asynchronously with a database connection using the db2ReadLog API.
Copy the sample c scripts from ~/sqllib/samples/c to local dir:
---
$ cp -R ~/sqllib/samples/c .
Change permission on the dir to allow for creating new files:
---
$ chmod -R 777 /home/db2v97/readlog/c
CREATE a sample datbase using the db2sampl executable:
---
$ db2sampl
Starting the DB2 instance...
Creating database "SAMPLE"...
Connecting to database "SAMPLE"...
Creating tables and data in schema "TEST97"...
Creating tables with XML columns and XML data in schema "TEST97"...
Stopping the DB2 instance...
'db2sampl' processing complete.
CONNECT to the database:
---
$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.7.8
SQL authorization ID = DB2V97
Local database alias = SAMPLE
PRECOMPILE:
---
The precompiler converts SQL statements contained in the source file to comments, and generates the DB2 run-time API calls for those statements. The precompiler also creates the information the database manager needs to process the SQL statements against a database. This information is stored in a package, in a bind file, or in both, depending on the precompiler options selected.
We are going to use a bindfile in this example:
$ db2 precompile dblogconn.sqc bindfile
LINE MESSAGES FOR dblogconn.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.
$ db2 precompile utilemb.sqc bindfile
LINE MESSAGES FOR utilemb.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.
$ls -ltr (will show the .bnd files)
-rw-r--r-- 1 db2v97 db2v97 29848 Mar 31 21:22 dblogconn.c
-rw-r--r-- 1 db2v97 db2v97 2258 Mar 31 21:22 dblogconn.bnd
-rw-r--r-- 1 db2v97 db2v97 7397 Mar 31 21:22 utilemb.c
-rw-r--r-- 1 db2v97 db2v97 589 Mar 31 21:22 utilemb.bnd
BIND:
---
Binding is the process of creating a package from a bind file and storing it in a database.
$ db2 bind dblogconn.bnd
LINE MESSAGES FOR dblogconn.bnd
------ --------------------------------------------------------------------
SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.
$ db2 bind utilemb.bnd
LINE MESSAGES FOR utilemb.bnd
------ --------------------------------------------------------------------
SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.
$ db2 list packages
Bound Total Isolation
Package Schema Version by sections Valid Format level Blocking
----------- --------- ----------- --------- ------------- ------- -------- --------- --------
DBLOGCON DB2V97 DB2V97 19 Y 0 CS U
P1587713> DB2V97 DB2V97 10 N 0 CS U
UTILEMB DB2V97 DB2V97 1 Y 0 CS U
3 record(s) selected.
COMPILE:
---
COMPILE is the process of generating an object file from the source code file.
Switch to cshell:
[db2v97@ts-jkgera ~/sqllib]$ csh
Source db2cshrc:
[db2v97@ts-jkgera ~/sqllib]$ source ~/sqllib/db2cshrc
Set the DB2PATH environment variable:
[db2v97@ts-jkgera ~/sqllib]$ setenv DB2PATH ~/sqllib
$ cc -I$DB2PATH/include -c dblogconn.c
$ cc -I$DB2PATH/include -c utilemb.c
$ls -ltr
-rw-r--r-- 1 db2v97 db2v97 7920 Mar 31 21:24 utilapi.o
-rw-r--r-- 1 db2v97 db2v97 51160 Mar 31 21:24 dblogconn.o
LINKING:
---
LINKING is the process of combining object files with the appropriate shared libraries to perform an executable program.
$ cc -o dblogconn dblogconn.o utilemb.o -L$DB2PATH/lib -ldb2
$ ls -ltr (Lists the executable)
-rwxr-xr-x 1 db2v97 db2v97 42465 Mar 31 21:25 dblogconn
OUTPUT:
---
The program insert records into the emp_resume table and uses the db2ReadLog API to read the log records.
$ ./dblogconn
THIS SAMPLE SHOWS HOW TO READ DATABASE LOGS ASYNCHRONOUSLY WITH
A DATABASE CONNECTION FOR BOTH COMPRESSED AND UNCOMPRESSED TABLES
USE THE DB2 API:
db2CfgGet -- Get Configuration
TO GET THE DATABASE CONFIGURATION AND DETERMINE
THE SERVER WORKING PATH.
-----------------------------------------------------------
USE THE DB2 APIs:
db2CfgGet -- GET CONFIGURATION
TO GET THE CONFIGURATION OF A DATABASE.
******* Save LOGRETAIN for 'sample' database. *******
*****************************************************
*** ASYNCHRONOUS READ LOG FOR UNCOMPRESSED TABLES ***
*****************************************************
USE THE DB2 APIs:
db2CfgSet -- Set Configuration
db2Backup -- Backup Database
db2ReadLog -- Asynchronous Read Log
AND THE SQL STATEMENTS:
CONNECT
ALTER TABLE
COMMIT
INSERT
UPDATE
DELETE
ROLLBACK
CONNECT RESET
TO READ LOG RECORDS FOR UNCOMPRESSED TABLES.
Update 'sample' database configuration:
- Enable the database configuration parameter LOGRETAIN
i.e., set LOGRETAIN = RECOVERY/YES
Backing up the 'sample' database...
Backup finished.
- backup image size : 131 MB
- backup image path : /home/db2v97/db2v97
- backup image time stamp: 20140124074136
Connecting to 'sample' database...
Connected to 'sample' database.
Invoke the following SQL statements:
ALTER TABLE emp_resume DATA CAPTURE CHANGES;
COMMIT;
INSERT INTO emp_resume
VALUES('000030', 'ascii', 'This is the first resume'),
('000050', 'ascii', 'This is the second resume'),
('000120', 'ascii', 'This is the third resume');
COMMIT;
UPDATE emp_resume
SET resume_format = 'html'
WHERE empno = '000050';
DELETE FROM emp_resume WHERE empno = '000030';
DELETE FROM emp_resume WHERE empno = '000050';
DELETE FROM emp_resume WHERE empno = '000120';
COMMIT;
DELETE FROM emp_resume WHERE empno = '000140';
ROLLBACK;
ALTER TABLE emp_resume DATA CAPTURE NONE;
COMMIT;
Start reading database log.
RLOG_FILTERDATA:
recordLSN: 40967171
realLogRecLen: 46
sqlcode: 0
recordSize: 46
Record type: Normal
component ID: DMS log record
function ID: Alter Table Attribute
Propagation attribute is changed to: ON
RLOG_FILTERDATA:
recordLSN: 40967217
realLogRecLen: 216
sqlcode: 0
recordSize: 216
Record type: Normal
component ID: DMS log record
function ID: Update Record
oldRID:x020000000000
old subrecord length: 76
old subrecord offset: 0
subrecord type: Updatable, Internal control
newRID: x000000000000
new subrecord length: 76
new subrecord offset: 16
subrecord type: Updatable, Internal control
RLOG_FILTERDATA:
recordLSN: 40967433
realLogRecLen: 50
sqlcode: 0
recordSize: 50
Record type: Local pending list
UTC transaction committed (in seconds since 70-01-01): 1390570908
authorization ID of the application: DB2V97
RLOG_FILTERDATA:
recordLSN: 40967483
realLogRecLen: 100
sqlcode: 0
recordSize: 100
Record type: Normal
component ID: DMS log record
function ID: Insert Record
RID: x0C0000000000
subrecord length: 56
subrecord offset: 5992
subrecord type: Updatable, Formatted user data
user data fixed length: 15
user data:
30 30 30 30 33 30 0F 00 05 00 *000030....*
14 00 1C 00 00 61 73 63 69 69 *.....ascii*
69 00 00 00 54 68 69 73 20 69 *i...This.i*
73 20 74 68 65 20 66 69 72 73 *s.the.firs*
74 20 72 65 73 75 6D 65 *t.resume *
RLOG_FILTERDATA:
recordLSN: 40967668
realLogRecLen: 101
sqlcode: 0
recordSize: 101
Record type: Normal
component ID: DMS log record
function ID: Insert Record
RID: x0D0000000000
subrecord length: 57
subrecord offset: 5935
subrecord type: Updatable, Formatted user data
user data fixed length: 15
user data:
30 30 30 30 35 30 0F 00 05 00 *000050....*
14 00 1D 00 00 61 73 63 69 69 *.....ascii*
69 00 00 00 54 68 69 73 20 69 *i...This.i*
73 20 74 68 65 20 73 65 63 6F *s.the.seco*
6E 64 20 72 65 73 75 6D 65 *nd.resume *
>><<
Disconnecting from 'sample' database...
Disconnected from 'sample' database.
***** Restore LOGRETAIN for 'sample' database ******
Bada Bing Bada Boom!!
UID
ibm11141510