/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: dblogconn.sqC
**
** SAMPLE: How to read Asynchronous Log for Compressed and Uncompressed Tables
**
** Note:
** You must be disconnected from the sample database to run
** this program. To ensure you are, enter 'db2 connect reset'
** on the command line prior to read the logs.
**
** DB2 API USED:
** db2CfgSet -- Set Configuration
** db2ReadLog -- Asynchronous Read Log
** db2Reorg -- Reorganize a Table or Index
**
** SQL STATEMENTS USED:
** ALTER TABLE
** COMMIT
** INSERT
** UPDATE
** DELETE
** ROLLBACK
** CONNECT RESET
**
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing C++ applications, see the Application
** Development Guide.
**
** For information on using SQL statements, see the SQL Reference.
**
** For information on DB2 APIs, see the Administrative API Reference.
**
** For the latest information on programming, building, and running DB2
** applications, visit the DB2 Information Center:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#ifdef DB2NT
#include "utilrecov.cxx"
#else
#include "utilrecov.C"
#endif
class DbLogconn: public UtilRecov, public UtilLog
{
public:
int DbLogRecordsForCurrentConnectionRead(DbEmb *, char *);
int DbLogRecordsForCompressedTablesRead(DbEmb *, char *);
int ConfigParam(DbEmb *, char *); // support function
int db2ReadLogAPICall(DbEmb *, char *);
int ReorgTable(DbEmb *);
};
int main(int argc, char *argv[])
{
int rc = 0;
CmdLineArgs check;
char serverWorkingPath[SQL_PATH_SZ + 1] = { 0 };
sqluint16 savedLogRetainValue = 0;
Instance inst;
DbEmb db;
DbLogconn dblogconn;
// Check the command line arguments
rc = check.CmdLineArgsCheck3(argc, argv, db, inst);
CHECKRC(rc, "check.CmdLineArgsCheck3");
cout << "\nTHIS SAMPLE SHOWS HOW TO READ DATABASE LOGS "
"ASYNCHRONOUSLY WITH" << endl;
cout << " A DATABASE CONNECTION FOR BOTH COMPRESSED "
"AND UNCOMPRESSED TABLES" << endl;
// Attach to a local or remote instance
rc = inst.Attach();
CHECKRC(rc, "inst.Attach");
// Get a server working path
rc = dblogconn.ServerWorkingPathGet(&db, serverWorkingPath);
CHECKRC(rc, "dblogconn.ServerWorkingPathGet");
// Save log retain value
rc = dblogconn.DbLogRetainValueSave(&db, &savedLogRetainValue);
CHECKRC(rc, "dblogconn.DbLogRetainValueSave");
// Call the function to do asynchronous log read for uncompressed tables
rc = dblogconn.DbLogRecordsForCurrentConnectionRead(&db,
serverWorkingPath);
CHECKRC(rc, "dblogconn.DbLogRecordsForCurrentConnectionRead");
// Call the function to do asynchronous log read for compressed tables
rc = dblogconn.DbLogRecordsForCompressedTablesRead(&db,
serverWorkingPath);
CHECKRC(rc, "dblogconn.DbLogRecordsForCompressedTablesRead");
// Restore log retain value
rc = dblogconn.DbLogRetainValueRestore(&db, &savedLogRetainValue);
CHECKRC(rc, "dblogconn.DbLogRetainValueRestore");
// Detach from the local or remote instance
rc = inst.Detach();
CHECKRC(rc, "inst.Detach");
return 0;
} // main
// Function that reads log records for uncompressed tables
int DbLogconn::DbLogRecordsForCurrentConnectionRead(DbEmb *db,
char serverWorkingPath[])
{
int rc = 0;
struct sqlca sqlca = { 0 };
DbLogconn dblogconn;
cout << "\n*****************************************************\n";
cout << "*** ASYNCHRONOUS READ LOG FOR UNCOMPRESSED TABLES ***\n";
cout << "*****************************************************\n";
cout << "\nUSE THE DB2 APIs:" << endl;
cout << " db2CfgSet -- SET CONFIGURATION" << endl;
cout << " db2Backup -- BACKUP DATABASE" << endl;
cout << " db2ReadLog -- ASYNCHRONOUS READ LOG" << endl;
cout << "AND THE SQL STATEMENTS:" << endl;
cout << " CONNECT " << endl;
cout << " ALTER TABLE" << endl;
cout << " COMMIT" << endl;
cout << " INSERT" << endl;
cout << " UPDATE" << endl;
cout << " DELETE" << endl;
cout << " ROLLBACK" << endl;
cout << " CONNECT RESET" << endl;
cout << "TO READ LOG RECORDS FOR UNCOMPRESSED TABLES." << endl;
// Call the function to set the configuration parameters
rc = dblogconn.ConfigParam(db,
serverWorkingPath);
CHECKRC(rc, "dblogconn.ConfigParam");
// Connect to the database
rc = db->Connect();
CHECKRC(rc, "db->Conect");
// Invoke the SQL statements for filling database log
cout << "\n Invoke the following SQL statements:\n"
" ALTER TABLE emp_resume DATA CAPTURE CHANGES;\n"
" COMMIT;\n"
" INSERT INTO emp_resume\n"
" VALUES('000030', 'ascii', 'This is the first resume'),\n"
" ('000050', 'ascii', 'This is the second resume'),\n"
" ('000120', 'ascii', 'This is the third resume');\n"
" COMMIT;\n"
" UPDATE emp_resume \n"
" SET resume_format = 'html' \n"
" WHERE empno = '000050';\n"
" DELETE FROM emp_resume WHERE empno = '000030';\n"
" DELETE FROM emp_resume WHERE empno = '000050';\n"
" DELETE FROM emp_resume WHERE empno = '000120';\n"
" COMMIT;\n"
" DELETE FROM emp_resume WHERE empno = '000140';\n"
" ROLLBACK;\n"
" ALTER TABLE emp_resume DATA CAPTURE NONE;\n" " COMMIT;" << endl;
// The option 'DATA CAPTURE CHANGES' specifies that changes
// to the table 'emp_resume' be written to the log
EXEC SQL ALTER TABLE emp_resume DATA CAPTURE CHANGES;
EMB_SQL_CHECK("SQL statement 1 -- invoke");
EXEC SQL COMMIT;
EMB_SQL_CHECK("SQL statement 2 -- invoke");
EXEC SQL 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');
EMB_SQL_CHECK("SQL statement 3 -- invoke");
EXEC SQL COMMIT;
EMB_SQL_CHECK("SQL statement 4 -- invoke");
EXEC SQL UPDATE emp_resume
SET resume_format = 'html'
WHERE empno = '000050';
EMB_SQL_CHECK("SQL statement 5 -- invoke");
EXEC SQL DELETE FROM emp_resume WHERE empno = '000030';
EMB_SQL_CHECK("SQL statement 6 -- invoke");
EXEC SQL DELETE FROM emp_resume WHERE empno = '000050';
EMB_SQL_CHECK("SQL statement 7 -- invoke");
EXEC SQL DELETE FROM emp_resume WHERE empno = '000120';
EMB_SQL_CHECK("SQL statement 8 -- invoke");
EXEC SQL COMMIT;
EMB_SQL_CHECK("SQL statement 9 -- invoke");
EXEC SQL DELETE FROM emp_resume WHERE empno = '000140';
EMB_SQL_CHECK("SQL statement 10 -- invoke");
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("SQL statement 11 -- invoke");
EXEC SQL ALTER TABLE emp_resume DATA CAPTURE NONE;
EMB_SQL_CHECK("SQL statement 12 -- invoke");
EXEC SQL COMMIT;
EMB_SQL_CHECK("SQL statement 13 -- invoke");
// call the function to do asynchronous log read
rc = dblogconn.db2ReadLogAPICall(db,
serverWorkingPath);
CHECKRC(rc, "dblogconn.db2ReadLogAPICall");
// Disconnect from the database
rc = db->Disconnect();
CHECKRC(rc, "db->Disconnect");
return 0;
} // DbLogconn::DbLogRecordsForCurrentConnectionRead
// Function that reads log records for compressed tables
int DbLogconn::DbLogRecordsForCompressedTablesRead(DbEmb *db,
char serverWorkingPath[])
{
int rc = 0;
struct sqlca sqlca = { 0 };
DbLogconn dblogconn;
cout << "\n***************************************************\n";
cout << "*** ASYNCHRONOUS READ LOG FOR COMPRESSED TABLES ***\n";
cout << "***************************************************\n";
cout << "\nUSE THE DB2 APIs:" << endl;
cout << " db2CfgSet -- SET CONFIGURATION" << endl;
cout << " db2Backup -- BACKUP DATABASE" << endl;
cout << " db2ReadLog -- ASYNCHRONOUS READ LOG" << endl;
cout << "AND THE SQL STATEMENTS:" << endl;
cout << " CONNECT " << endl;
cout << " ALTER TABLE" << endl;
cout << " COMMIT" << endl;
cout << " INSERT" << endl;
cout << " UPDATE" << endl;
cout << " DELETE" << endl;
cout << " ROLLBACK" << endl;
cout << " CONNECT RESET" << endl;
cout << "TO READ LOG RECORDS FOR COMPRESSED TABLES." << endl;
// Call the function to set the configuration parameters
rc = dblogconn.ConfigParam(db,
serverWorkingPath);
CHECKRC(rc, "dblogconn.ConfigParam");
// Connect to the database
rc = db->Connect();
CHECKRC(rc, "db->Conect");
// Invoke the SQL statements for filling database log
cout << "\n Invoke the following SQL statements:\n"
" ALTER TABLE emp_resume COMPRESS YES;\n"
" COMMIT;\n";
// The 'COMPRESS YES' option specifies that data compression
// be applied to the rows of the table 'emp_resume'
EXEC SQL ALTER TABLE emp_resume COMPRESS YES;
EMB_SQL_CHECK("SQL statement 1 -- invoke");
EXEC SQL COMMIT;
EMB_SQL_CHECK("SQL statement 2 -- invoke");
// Call the function to perform a reorg on table 'emp_resume'
rc = dblogconn.ReorgTable(db);
CHECKRC(rc, "dblogconn.ReorgTable");
cout << "\n Invoke the following SQL statements:\n"
" ALTER TABLE emp_resume DATA CAPTURE CHANGES;\n"
" COMMIT;\n"
" INSERT INTO emp_resume\n"
" VALUES('000030', 'ascii', 'This is the first resume'),\n"
" ('000050', 'ascii', 'This is the second resume'),\n"
" ('000120', 'ascii', 'This is the third resume');\n"
" COMMIT;\n"
" UPDATE emp_resume \n"
" SET resume_format = 'html' \n"
" WHERE empno = '000050';\n"
" DELETE FROM emp_resume WHERE empno = '000030';\n"
" DELETE FROM emp_resume WHERE empno = '000050';\n"
" DELETE FROM emp_resume WHERE empno = '000120';\n"
" COMMIT;\n"
" DELETE FROM emp_resume WHERE empno = '000140';\n"
" ROLLBACK;\n"
" ALTER TABLE emp_resume COMPRESS NO;\n"
" ALTER TABLE emp_resume DATA CAPTURE NONE;\n"
" COMMIT;\n" << endl;
EXEC SQL ALTER TABLE emp_resume DATA CAPTURE CHANGES;
EMB_SQL_CHECK("SQL statement 3 -- invoke");
EXEC SQL COMMIT;
EMB_SQL_CHECK("SQL statement 4 -- invoke");
EXEC SQL 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');
EMB_SQL_CHECK("SQL statement 5 -- invoke");
EXEC SQL COMMIT;
EMB_SQL_CHECK("SQL statement 6 -- invoke");
EXEC SQL UPDATE emp_resume
SET resume_format = 'html'
WHERE empno = '000050';
EMB_SQL_CHECK("SQL statement 7 -- invoke");
EXEC SQL DELETE FROM emp_resume WHERE empno = '000030';
EMB_SQL_CHECK("SQL statement 8 -- invoke");
EXEC SQL DELETE FROM emp_resume WHERE empno = '000050';
EMB_SQL_CHECK("SQL statement 9 -- invoke");
EXEC SQL DELETE FROM emp_resume WHERE empno = '000120';
EMB_SQL_CHECK("SQL statement 10 -- invoke");
EXEC SQL COMMIT;
EMB_SQL_CHECK("SQL statement 11 -- invoke");
EXEC SQL DELETE FROM emp_resume WHERE empno = '000140';
EMB_SQL_CHECK("SQL statement 12 -- invoke");
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("SQL statement 13 -- invoke");
EXEC SQL ALTER TABLE emp_resume COMPRESS NO;
EMB_SQL_CHECK("SQL statement 14 -- invoke");
EXEC SQL ALTER TABLE emp_resume DATA CAPTURE NONE;
EMB_SQL_CHECK("SQL statement 15 -- invoke");
EXEC SQL COMMIT;
EMB_SQL_CHECK("SQL statement 16 -- invoke");
// Call the function to do asynchronous log read
rc = dblogconn.db2ReadLogAPICall(db,
serverWorkingPath);
CHECKRC(rc, "dblogconn.db2ReadLogAPICall");
// Call the function to perform a reorg on table 'emp_resume'
rc = dblogconn.ReorgTable(db);
CHECKRC(rc, "dblogconn.ReorgTable");
// Disconnect from the database
rc = db->Disconnect();
CHECKRC(rc, "db->Disconnect");
return 0;
} // DbLogconn::DbLogRecordsForCompressedTablesRead
// Function that sets the configuration parameters
int DbLogconn::ConfigParam(DbEmb *db,
char serverWorkingPath[])
{
int rc = 0;
struct sqlca sqlca = { 0 };
unsigned short logretain = 0;
db2CfgParam cfgParameters[1] = { 0 };
db2Cfg cfgStruct = { 0 };
db2BackupStruct backupStruct = { 0 };
db2TablespaceStruct tablespaceStruct = { 0 };
db2MediaListStruct mediaListStruct = { 0 };
db2Uint32 backupImageSize = 0;
db2RestoreStruct restoreStruct = { 0 };
db2TablespaceStruct rtablespaceStruct = { 0 };
db2MediaListStruct rmediaListStruct = { 0 };
// Update db config: set logretain = YES
cout << "\n Update \'" << db->getAlias() << "\' database configuration:\n";
cout << " - Enable the database configuration parameter LOGRETAIN \n";
cout << " i.e., set LOGRETAIN = RECOVERY/YES\n";
cfgParameters[0].flags = 0;
cfgParameters[0].token = SQLF_DBTN_LOG_RETAIN;
cfgParameters[0].ptrvalue = (char *)&logretain;
logretain = SQLF_LOGRETAIN_RECOVERY;
// Initialize cfgStruct
cfgStruct.numItems = 1;
cfgStruct.paramArray = cfgParameters;
cfgStruct.flags = db2CfgDatabase | db2CfgDelayed;
cfgStruct.dbname = db->getAlias();
// Set database configuration
db2CfgSet(db2Version970, (void *)&cfgStruct, &sqlca);
DB2_API_CHECK("Db Log Retain -- Enable");
// Start the backup process
tablespaceStruct.tablespaces = NULL;
tablespaceStruct.numTablespaces = 0;
mediaListStruct.locations = &serverWorkingPath;
mediaListStruct.numLocations = 1;
mediaListStruct.locationType = SQLU_LOCAL_MEDIA;
//****************************
// BACKUP THE DATABASE
//****************************
// Calling the routine for database backup
rc = DbBackup(db, serverWorkingPath, &backupStruct);
CHECKRC(rc, "DbBackup");
return 0;
} // DbLogconn::ConfigParam
// Function that makes a call to the db2ReadLog API
// to read the asynchronous log records
int DbLogconn::db2ReadLogAPICall(DbEmb *db,
char serverWorkingPath[])
{
int rc = 0;
struct sqlca sqlca = { 0 };
db2LSN startLSN;
db2LSN endLSN;
char *logBuffer = NULL;
sqluint32 logBufferSize = 0;
db2ReadLogInfoStruct readLogInfo = { 0 };
db2ReadLogStruct readLogInput = { 0 };
int i = 0;
cout << "\n Start reading database log." << endl;
// Get the database log info
logBuffer = NULL;
logBufferSize = 0;
// The API db2ReadLog (Asynchronous Read Log) is used to extract records
// from the database logs, and to query the log manager for current
// log state information.
// This API can only be used on recoverable databases.
// Query the log manager for current log state information
readLogInput.iCallerAction = DB2READLOG_QUERY;
readLogInput.piStartLSN = NULL;
readLogInput.piEndLSN = NULL;
readLogInput.poLogBuffer = NULL;
readLogInput.iLogBufferSize = 0;
// The 'iFilterOption' specifies the level of log record filtering
// to be used when reading the log records. With the iFilterOption ON,
// only log records in the given LSN range marked as propagatable
// are read.
// Log record contents will only be decompressed when reading logs
// through the db2ReadLog API with the iFilterOption ON.
// If the iFilterOption is OFF the log records queried may contain
// mixed compressed and uncompressed user data
readLogInput.iFilterOption = DB2READLOG_FILTER_ON;
readLogInput.poReadLogInfo = &readLogInfo;
rc = db2ReadLog(db2Version970, &readLogInput, &sqlca);
DB2_API_CHECK("database log info -- get");
// Read the database log
logBufferSize = 64 * 1024;
logBuffer = new char[logBufferSize];
memcpy(&startLSN, &(readLogInfo.initialLSN), sizeof(startLSN));
memcpy(&endLSN, &(readLogInfo.nextStartLSN), sizeof(endLSN));
// Extract a log record from the database logs, and
// read the first log sequence asynchronously.
readLogInput.iCallerAction = DB2READLOG_READ;
readLogInput.piStartLSN = &startLSN;
readLogInput.piEndLSN = &endLSN;
readLogInput.poLogBuffer = logBuffer;
readLogInput.iLogBufferSize = logBufferSize;
readLogInput.iFilterOption = DB2READLOG_FILTER_ON;
readLogInput.poReadLogInfo = &readLogInfo;
rc = db2ReadLog(db2Version970, &readLogInput, &sqlca);
if (sqlca.sqlcode != SQLU_RLOG_READ_TO_CURRENT)
{
DB2_API_CHECK("database logs -- read");
}
else
{
if (readLogInfo.logRecsWritten == 0)
{
cout << " Database log empty." << endl;
}
}
// Display the log buffer
rc = LogBufferDisplay(logBuffer, readLogInfo.logRecsWritten, 1);
CHECKRC(rc, "UtilLog.LogBufferDisplay");
while (sqlca.sqlcode != SQLU_RLOG_READ_TO_CURRENT)
{
// Read the next log sequence
memcpy(&startLSN, &(readLogInfo.nextStartLSN), sizeof(startLSN));
// Extract a log record from the database logs, and
// read the next log sequence asynchronously.
rc = db2ReadLog(db2Version970, &readLogInput, &sqlca);
if (sqlca.sqlcode != SQLU_RLOG_READ_TO_CURRENT)
{
DB2_API_CHECK("database logs -- read");
}
// Display the log buffer
rc = LogBufferDisplay(logBuffer, readLogInfo.logRecsWritten, 1);
CHECKRC(rc, "LogBufferDisplay");
}
// Release the log buffer
delete [] logBuffer;
return 0;
} // DbLogconn::db2ReadLogAPICall
// Function that performs a reorg on table 'emp_resume'
int DbLogconn::ReorgTable(DbEmb *db)
{
EXEC SQL BEGIN DECLARE SECTION;
char tableName[129];
char schemaName[129];
char fullTableName[258];
EXEC SQL END DECLARE SECTION;
int rc = 0;
struct sqlca sqlca = { 0 };
db2ReorgStruct paramStruct;
db2Uint32 versionNumber = db2Version970;
// Get fully qualified name of the table
strcpy(tableName, "EMP_RESUME");
// Get table schema name
EXEC SQL SELECT tabschema INTO :schemaName
FROM syscat.tables
WHERE tabname = :tableName;
EMB_SQL_CHECK("SQL statement 14 -- invoke");
// Get rid of spaces from the end of schemaName
strtok(schemaName, " ");
strcpy(fullTableName, schemaName);
strcat(fullTableName, ".");
strcat(fullTableName, tableName);
cout << "\n Reorganize the table: " << fullTableName << endl;
// Setup parameters
memset(¶mStruct, '\0', sizeof(paramStruct));
paramStruct.reorgObject.tableStruct.pTableName = fullTableName;
paramStruct.reorgObject.tableStruct.pOrderByIndex = NULL;
paramStruct.reorgObject.tableStruct.pSysTempSpace = NULL;
paramStruct.reorgType = DB2REORG_OBJ_TABLE_OFFLINE;
paramStruct.reorgFlags = DB2REORG_LONGLOB;
paramStruct.nodeListFlag = DB2_ALL_NODES;
paramStruct.numNodes = 0;
paramStruct.pNodeList = NULL;
// Reorganize table
rc = db2Reorg(versionNumber, ¶mStruct, &sqlca);
DB2_API_CHECK("table -- reorganize");
// Commit transaction
EXEC SQL COMMIT;
EMB_SQL_CHECK("Transaction -- Commit");
return 0;
} // DbLogconn::ReorgTable