/****************************************************************************
** (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