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