/**************************************************************************** ** (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: tbmove.sqC ** ** SAMPLE: How to move table data ** ** DB2 APIs USED: ** db2Export -- Export ** db2Import -- Import ** sqluvqdp -- Quiesce Table Spaces for Table ** db2Load -- Load ** db2LoadQuery -- Load Query ** ** SQL STATEMENTS USED: ** PREPARE ** DECLARE CURSOR ** OPEN ** FETCH ** CLOSE ** CREATE TABLE ** DROP ** ** ***************************************************************************** ** ** For more information on the sample programs, see the README file. ** ** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book. ** ** 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, compiling, and running DB2 ** applications, visit the DB2 Information Center at ** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp ****************************************************************************/ #include <string.h> #include <stdlib.h> #include <sqlenv.h> #include <sqlutil.h> #include <db2ApiDf.h> #include "utilemb.h" #if ((__cplusplus >= 199711L) && !defined DB2HP && !defined DB2AIX) || \ (DB2LINUX && (__LP64__ || (__GNUC__ >= 3)) ) #include <iostream> #include <fstream> using namespace std; #else #include <iostream.h> #include <fstream.h> #endif EXEC SQL BEGIN DECLARE SECTION; char strStmt[256]; short deptnumb; char deptname[15]; EXEC SQL END DECLARE SECTION; class TbMove { public: int DataExport(char *); int TbImport(char *); int TbLoad(char *); int TbLoadQuery(); private: // supporting functions int ExportedDataDisplay(char *); int NewTableDisplay(); }; int TbMove::ExportedDataDisplay(char *dataFileName) { struct sqlca sqlca = {0}; char buffer[100]; ifstream infile(dataFileName, ios::in); if (!infile) return 1; // creating 'infile' fails cout << "\n The content of the file '" << dataFileName << "' is:" << endl; while (!infile.eof()) { infile.getline(buffer, sizeof(buffer), '\n'); if (!infile.eof()) { cout << " " << buffer << endl; } } infile.close(); return 0; } //TbMove::ExportedDataDisplay int TbMove::NewTableDisplay() { struct sqlca sqlca = {0}; cout << "\n SELECT * FROM newtable" << endl; cout << " DEPTNUMB DEPTNAME " << endl; cout << " -------- --------------" << endl; strcpy(strStmt, "SELECT * FROM newtable"); EXEC SQL PREPARE stmt FROM :strStmt; EMB_SQL_CHECK("statement -- prepare"); EXEC SQL DECLARE c0 CURSOR FOR stmt; EXEC SQL OPEN c0; EMB_SQL_CHECK("cursor -- open"); EXEC SQL FETCH c0 INTO :deptnumb, :deptname; EMB_SQL_CHECK("cursor -- fetch"); while (sqlca.sqlcode != 100) { cout.width(12); cout.setf(ios::right, ios::adjustfield); cout << deptnumb; cout.setf(ios::left, ios::adjustfield); cout << " " << deptname << endl; EXEC SQL FETCH c0 INTO :deptnumb, :deptname; EMB_SQL_CHECK("cursor -- fetch"); } EXEC SQL CLOSE c0; return 0; } //TbMove::NewTableDisplay int TbMove::DataExport(char *dataFileName) { int rc = 0; struct sqlca sqlca = {0}; struct sqldcol dataDescriptor = {0}; char actionString[256]; struct sqllob *pAction = {0}; char msgFileName[128]; struct db2ExportOut outputInfo = {0}; struct db2ExportStruct exportParmStruct = {0}; cout << "\n-----------------------------------------------------------"; cout << "\nUSE THE DB2 API:" << endl; cout << " db2Export -- Export" << endl; cout << "TO EXPORT DATA TO A FILE." << endl; cout << "\n Be sure to complete all table operations and release" << endl; cout << " all locks before starting an export operation. This" << endl; cout << " can be done by issuing a COMMIT after closing all" << endl; cout << " cursors opened WITH HOLD, or by issuing a ROLLBACK." << endl; cout << " Please refer to the 'Administrative API Reference'" << endl; cout << " for the details." << endl; /* export data */ dataDescriptor.dcolmeth = SQL_METH_D; strcpy(actionString, "SELECT deptnumb, deptname FROM org"); pAction = (struct sqllob *)new char[sizeof(sqluint32) + sizeof(actionString) + 1]; pAction->length = strlen(actionString); strcpy(pAction->data, actionString); strcpy(msgFileName, "tbexport.MSG"); exportParmStruct.piDataFileName = dataFileName; exportParmStruct.piLobPathList = NULL; exportParmStruct.piLobFileList = NULL; exportParmStruct.piDataDescriptor = &dataDescriptor; exportParmStruct.piActionString = pAction; exportParmStruct.piFileType = SQL_DEL; exportParmStruct.piFileTypeMod = NULL; exportParmStruct.piMsgFileName = msgFileName; exportParmStruct.iCallerAction = SQLU_INITIAL; exportParmStruct.poExportInfoOut = &outputInfo; /* From V9.0 onwards, the structure db2ExportStruct */ /* will have three new members. They are */ /* piExportInfoIn, piXmlPathList and piXmlFileList */ exportParmStruct.piExportInfoIn = NULL; exportParmStruct.piXmlPathList = NULL; exportParmStruct.piXmlFileList = NULL; cout << "\n Export data." << endl; cout << " client destination file name: " << dataFileName << endl; cout << " action : " << actionString << endl; cout << " client message file name : " << msgFileName << endl; // export data db2Export(db2Version970, &exportParmStruct, &sqlca); DB2_API_CHECK("data -- export"); // release memory allocated delete [] pAction; // display exported data rc = ExportedDataDisplay(dataFileName); return 0; } //TbMove::DataExport int TbMove::TbImport(char *dataFileName) { int rc = 0; struct sqlca sqlca = {0}; struct sqldcol dataDescriptor = {0}; char actionString[256]; struct sqlchar *pAction = {0}; char msgFileName[128]; struct db2ImportIn inputInfo = {0}; struct db2ImportOut outputInfo = {0}; struct db2ImportStruct importParmStruct = {0}; db2int32 commitcount = 10; cout << "\n-----------------------------------------------------------"; cout << "\nUSE THE DB2 API:" << endl; cout << " db2Import -- Import" << endl; cout << "TO IMPORT DATA TO A TABLE." << endl; // create new table cout << "\n CREATE TABLE newtable(deptnumb SMALLINT NOT NULL," << endl; cout << " deptname VARCHAR(14))" << endl; EXEC SQL CREATE TABLE newtable(deptnumb SMALLINT NOT NULL, deptname VARCHAR(14)); EMB_SQL_CHECK("new table -- create"); dataDescriptor.dcolmeth = SQL_METH_D; strcpy(actionString, "INSERT INTO newtable"); pAction = (struct sqlchar *)new char[sizeof(short) + sizeof(actionString) + 1]; pAction->length = strlen(actionString); strcpy(pAction->data, actionString); strcpy(msgFileName, "tbimport.MSG"); /* Setup db2ImportIn structure */ inputInfo.iRowcount = inputInfo.iRestartcount = 0; inputInfo.iSkipcount = inputInfo.iWarningcount = 0; inputInfo.iNoTimeout = 0; inputInfo.iAccessLevel = SQLU_ALLOW_NO_ACCESS; inputInfo.piCommitcount = &commitcount; cout << "\n Import table." << endl; cout << " client source file name : " << dataFileName << endl; cout << " action : " << actionString << endl; cout << " client message file name: " << msgFileName << endl; importParmStruct.piDataFileName = dataFileName; importParmStruct.piLobPathList = NULL; importParmStruct.piDataDescriptor = &dataDescriptor; importParmStruct.piActionString = pAction; importParmStruct.piFileType = SQL_DEL; importParmStruct.piFileTypeMod = NULL; importParmStruct.piMsgFileName = msgFileName; importParmStruct.piImportInfoIn = &inputInfo; importParmStruct.poImportInfoOut = &outputInfo; importParmStruct.piNullIndicators = NULL; importParmStruct.iCallerAction = SQLU_INITIAL; /* From V9.1 the structure db2ImportStruct will */ /* have a new member.This is piXmlPathList. */ importParmStruct.piXmlPathList = NULL; /* import table */ db2Import(db2Version970, &importParmStruct, &sqlca); DB2_API_CHECK("table -- import"); // release memory allocated delete [] pAction; // display import info cout << "\n Import info." << endl; cout << " rows read : " << (int)outputInfo.oRowsRead << endl; cout << " rows skipped : " << (int)outputInfo.oRowsSkipped << endl; cout << " rows inserted : " << (int)outputInfo.oRowsInserted << endl; cout << " rows updated : " << (int)outputInfo.oRowsUpdated << endl; cout << " rows rejected : " << (int)outputInfo.oRowsRejected << endl; cout << " rows committed: " << (int)outputInfo.oRowsCommitted << endl; // display content of the new table rc = NewTableDisplay(); // drop new table cout << "\n DROP TABLE newtable" << endl; EXEC SQL DROP TABLE newtable; EMB_SQL_CHECK("new table -- drop"); return 0; } //TbMove::TbImport int TbMove::TbLoad(char *dataFileName) { int rc = 0; struct sqlca sqlca = {0}; struct db2LoadStruct paramStruct = {0}; struct db2LoadIn inputInfoStruct = {0}; struct db2LoadOut outputInfoStruct = {0}; struct sqlu_media_list mediaList = {0}; struct sqldcol dataDescriptor = {0}; char actionString[256]; struct sqlchar *pAction = {0}; char localMsgFileName[128]; cout << "\n-----------------------------------------------------------"; cout << "\nUSE THE DB2 API:" << endl; cout << " sqluvqdp -- Quiesce Table Spaces for Table" << endl; cout << " db2Load -- Load" << endl; cout << "TO LOAD DATA INTO A TABLE." << endl; // create new table cout << "\n CREATE TABLE newtable(deptnumb SMALLINT NOT NULL," << endl; cout << " deptname VARCHAR(14))" << endl; EXEC SQL CREATE TABLE newtable(deptnumb SMALLINT NOT NULL, deptname VARCHAR(14)); EMB_SQL_CHECK("new table -- create"); cout << "\n Quiesce the table spaces for 'newtable'." << endl; EXEC SQL COMMIT; EMB_SQL_CHECK("transaction -- commit"); // quiesce table spaces for table sqluvqdp("newtable", SQLU_QUIESCEMODE_RESET_OWNED, NULL, &sqlca); DB2_API_CHECK("tablespaces for table -- quiesce"); mediaList.media_type = SQLU_CLIENT_LOCATION; mediaList.sessions = 1; mediaList.target.location = new sqlu_location_entry[mediaList.sessions]; strcpy(mediaList.target.location->location_entry, dataFileName); dataDescriptor.dcolmeth = SQL_METH_D; strcpy(actionString, "INSERT INTO newtable"); pAction = (struct sqlchar *)new char[sizeof(short) + sizeof(actionString) + 1]; pAction->length = strlen(actionString); strcpy(pAction->data, actionString); strcpy(localMsgFileName, "tbload.MSG"); /* Setup the input information structure */ inputInfoStruct.piUseTablespace = NULL; inputInfoStruct.iSavecount = 0; /* consistency points as infrequently as possible */ inputInfoStruct.iRestartcount = 0; /* start at row 1 */ inputInfoStruct.iRowcount = 0; /* load all rows */ inputInfoStruct.iWarningcount = 0; /* don't stop for warnings */ inputInfoStruct.iDataBufferSize = 0; /* default data buffer size */ inputInfoStruct.iSortBufferSize = 0; /* def. warning buffer size */ inputInfoStruct.iHoldQuiesce = 0; /* don't hold the quiesce */ inputInfoStruct.iRestartphase = ' '; /* ignored anyway */ inputInfoStruct.iStatsOpt = SQLU_STATS_NONE; /* don't bother with them */ inputInfoStruct.iIndexingMode = SQLU_INX_AUTOSELECT;/* let load choose */ /* indexing mode */ inputInfoStruct.iCpuParallelism = 0; inputInfoStruct.iNonrecoverable = SQLU_NON_RECOVERABLE_LOAD; inputInfoStruct.iAccessLevel = SQLU_ALLOW_NO_ACCESS; inputInfoStruct.iLockWithForce = SQLU_NO_FORCE; /* From V9.0 onwards, the structure member iCheckPending is */ /* deprecated and replaced with iSetIntegrityPending. Also the */ /* possible value to set this variable SQLU_CHECK_PENDING_CASCADE_DEFERRED */ /* has been replaced with SQLU_SI_PENDING_CASCADE_DEFERRED. */ inputInfoStruct.iSetIntegrityPending = SQLU_SI_PENDING_CASCADE_DEFERRED; /* Setup the parameter structure */ paramStruct.piSourceList = &mediaList; paramStruct.piLobPathList = NULL; paramStruct.piDataDescriptor = &dataDescriptor; paramStruct.piActionString = pAction; paramStruct.piFileType = SQL_DEL; paramStruct.piFileTypeMod = NULL; paramStruct.piLocalMsgFileName = localMsgFileName; paramStruct.piTempFilesPath = NULL; paramStruct.piVendorSortWorkPaths = NULL; paramStruct.piCopyTargetList = NULL; paramStruct.piNullIndicators = NULL; paramStruct.piLoadInfoIn = &inputInfoStruct; paramStruct.poLoadInfoOut = &outputInfoStruct; paramStruct.piPartLoadInfoIn = NULL; paramStruct.poPartLoadInfoOut = NULL; paramStruct.iCallerAction = SQLU_INITIAL; cout << "\n Load table." << endl; cout << " client source file name : " << dataFileName << endl; cout << " action : " << actionString << endl; cout << " client message file name: " << localMsgFileName << endl; /* load table */ db2Load (db2Version970, /* Database version number */ ¶mStruct, /* In/out parameters */ &sqlca); /* SQLCA */ DB2_API_CHECK("table -- load"); // release memory allocated delete [] pAction; // display load info cout << "\n Load info." << endl; cout << " rows read : " << (int) outputInfoStruct.oRowsRead << endl; cout << " rows skipped : " << (int) outputInfoStruct.oRowsSkipped << endl; cout << " rows loaded : " << (int) outputInfoStruct.oRowsLoaded << endl; cout << " rows deleted : " << (int) outputInfoStruct.oRowsDeleted << endl; cout << " rows rejected : " << (int) outputInfoStruct.oRowsRejected << endl; cout << " rows committed: " << (int) outputInfoStruct.oRowsCommitted << endl; // display content of the new table rc = NewTableDisplay(); // drop new table cout << "\n DROP TABLE newtable" << endl; EXEC SQL DROP TABLE newtable; EMB_SQL_CHECK("new table -- drop"); return 0; } //TbMove::TbLoad int TbMove::TbLoadQuery() { int rc = 0; struct sqlca sqlca = {0}; char tableName[128]; char loadMsgFileName[128]; db2LoadQueryStruct loadQueryParameters; db2LoadQueryOutputStruct loadQueryOutputStructure; cout << "\n-----------------------------------------------------------"; cout << "\nUSE THE DB2 API:" << endl; cout << " db2LoadQuery -- Load Query" << endl; cout << "TO CHECK THE STATUS OF A LOAD OPERATION." << endl; // Initialize structures memset(&loadQueryParameters, 0, sizeof(db2LoadQueryStruct)); memset(&loadQueryOutputStructure, 0, sizeof(db2LoadQueryOutputStruct)); // Set up the tablename to query. loadQueryParameters.iStringType = DB2LOADQUERY_TABLENAME; loadQueryParameters.piString = tableName; // Specify that we want all LOAD messages to be reported. loadQueryParameters.iShowLoadMessages = DB2LOADQUERY_SHOW_ALL_MSGS; // LOAD summary information goes here. loadQueryParameters.poOutputStruct = &loadQueryOutputStructure; // Set up the local message file. loadQueryParameters.piLocalMessageFile = loadMsgFileName; // call the DB2 API strcpy(tableName, "ORG"); strcpy(loadMsgFileName, "tbldqry.MSG"); // load query db2LoadQuery(db2Version970, &loadQueryParameters, &sqlca); cout << "\n Note: the table load for '" << tableName << "' is NOT in progress." << endl; cout << " So an empty message file '" << loadMsgFileName << "' will be created," << endl; cout << " and the following values will be zero." << endl; DB2_API_CHECK("status of load operation -- check"); cout << "\n Load status has been written to local file " << loadMsgFileName << endl; cout << " Number of rows read = " << loadQueryOutputStructure.oRowsRead << endl; cout << " Number of rows skipped = " << loadQueryOutputStructure.oRowsSkipped << endl; cout << " Number of rows loaded = " << loadQueryOutputStructure.oRowsLoaded << endl; cout << " Number of rows rejected = " << loadQueryOutputStructure.oRowsRejected << endl; cout << " Number of rows deleted = " << loadQueryOutputStructure.oRowsDeleted << endl; cout << " Number of rows committed = " << loadQueryOutputStructure.oRowsCommitted << endl; cout << " Number of warnings = " << loadQueryOutputStructure.oWarningCount << endl; return 0; } //TbMove::TbLoadQuery int main(int argc, char *argv[]) { int rc = 0; CmdLineArgs check; TbMove move; DbEmb db; char dataFileName[256]; // check the command line arguments rc = check.CmdLineArgsCheck1(argc, argv, db); if (rc != 0) { return rc; } cout << "\nTHIS SAMPLE SHOWS HOW TO MOVE TABLE DATA." << endl; // connect to database rc = db.Connect(); if (rc != 0) { return rc; } strcpy(dataFileName, ""); #if (defined(DB2NT)) strcat(dataFileName, getenv("DB2PATH")); #else // UNIX strcat(dataFileName, getenv("HOME")); #endif strcat(dataFileName, PATH_SEP); strcat(dataFileName, "tbmove.DEL"); rc = move.DataExport(dataFileName); rc = move.TbImport(dataFileName); rc = move.TbLoad(dataFileName); rc = move.TbLoadQuery(); // disconnect from the database rc = db.Disconnect(); if (rc != 0) { return rc; } return 0; } //main