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!!