/****************************************************************************
** (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, building, and running DB2
** applications, visit the DB2 Information Center:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include <db2ApiDf.h>
#include "utilemb.h"
int DataExport(char *);
int TbImport(char *);
int TbLoad(char *);
int TbLoadQuery(void);
/* support function */
int ExportedDataDisplay(char *);
int NewTableDisplay(void);
EXEC SQL BEGIN DECLARE SECTION;
char strStmt[256];
short deptnumb;
char deptname[15];
EXEC SQL END DECLARE SECTION;
int main(int argc, char *argv[])
{
int rc = 0;
char dbAlias[SQL_ALIAS_SZ + 1];
char user[USERID_SZ + 1];
char pswd[PSWD_SZ + 1];
char dataFileName[256];
/* check the command line arguments */
rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
printf("\nTHIS SAMPLE SHOWS HOW TO MOVE TABLE DATA.\n");
/* connect to database */
rc = DbConn(dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
#if(defined(DB2NT))
sprintf(dataFileName, "%s%stbmove.DEL", getenv("DB2PATH"), PATH_SEP);
#else /* UNIX */
sprintf(dataFileName, "%s%stbmove.DEL", getenv("HOME"), PATH_SEP);
#endif
rc = DataExport(dataFileName);
rc = TbImport(dataFileName);
rc = TbLoad(dataFileName);
rc = TbLoadQuery();
/* disconnect from the database */
rc = DbDisconn(dbAlias);
if (rc != 0)
{
return rc;
}
return 0;
} /* main */
int ExportedDataDisplay(char *dataFileName)
{
struct sqlca sqlca = {0};
FILE *fp;
char buffer[100];
int maxChars = 100;
int numChars;
int charNb;
fp = fopen(dataFileName, "r");
if (fp == NULL)
{
return 1;
}
printf("\n The content of the file '%s' is:\n", dataFileName);
printf(" ");
numChars = fread(buffer, 1, maxChars, fp);
while (numChars > 0)
{
for (charNb = 0; charNb < numChars; charNb++)
{
if (buffer[charNb] == '\n')
{
printf("\n");
if (charNb < numChars - 1)
{
printf(" ");
}
}
else
{
printf("%c", buffer[charNb]);
}
}
numChars = fread(buffer, 1, maxChars, fp);
}
if (ferror(fp))
{
fclose(fp);
return 1;
}
else
{
fclose(fp);
}
return 0;
} /* ExportedDataDisplay */
int NewTableDisplay(void)
{
struct sqlca sqlca = {0};
printf("\n SELECT * FROM newtable\n");
printf(" DEPTNUMB DEPTNAME \n");
printf(" -------- --------------\n");
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)
{
printf(" %8d %-s\n", deptnumb, deptname);
EXEC SQL FETCH c0 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
}
EXEC SQL CLOSE c0;
return 0;
} /* NewTableDisplay */
int 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};
printf("\n-----------------------------------------------------------");
printf("\nUSE THE DB2 API:\n");
printf(" db2Export -- Export\n");
printf("TO EXPORT DATA TO A FILE.\n");
printf("\n Be sure to complete all table operations and release\n");
printf(" all locks before starting an export operation. This\n");
printf(" can be done by issuing a COMMIT after closing all\n");
printf(" cursors opened WITH HOLD, or by issuing a ROLLBACK.\n");
printf(" Please refer to the 'Administrative API Reference'\n");
printf(" for the details.\n");
/* export data */
dataDescriptor.dcolmeth = SQL_METH_D;
strcpy(actionString, "SELECT deptnumb, deptname FROM org");
pAction = (struct sqllob *)malloc(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;
printf("\n Export data.\n");
printf(" client destination file name: %s\n", dataFileName);
printf(" action : %s\n", actionString);
printf(" client message file name : %s\n", msgFileName);
/* export data */
db2Export(db2Version970,
&exportParmStruct,
&sqlca);
DB2_API_CHECK("data -- export");
/* free memory allocated */
free(pAction);
/* display exported data */
rc = ExportedDataDisplay(dataFileName);
return 0;
} /* DataExport */
int 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};
int commitcount = 10;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE DB2 API:\n");
printf(" db2Import -- Import\n");
printf("TO IMPORT DATA TO A TABLE.\n");
/* create new table */
printf("\n CREATE TABLE newtable(deptnumb SMALLINT NOT NULL,");
printf("\n deptname VARCHAR(14))\n");
EXEC SQL CREATE TABLE newtable(deptnumb SMALLINT NOT NULL,
deptname VARCHAR(14));
EMB_SQL_CHECK("new table -- create");
/* import table */
dataDescriptor.dcolmeth = SQL_METH_D;
strcpy(actionString, "INSERT INTO newtable");
pAction = (struct sqlchar *)malloc(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;
printf("\n Import table.\n");
printf(" client source file name : %s\n", dataFileName);
printf(" action : %s\n", actionString);
printf(" client message file name: %s\n", msgFileName);
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");
/* free memory allocated */
free(pAction);
/* display import info */
printf("\n Import info.\n");
printf(" rows read : %ld\n", (int)outputInfo.oRowsRead);
printf(" rows skipped : %ld\n", (int)outputInfo.oRowsSkipped);
printf(" rows inserted : %ld\n", (int)outputInfo.oRowsInserted);
printf(" rows updated : %ld\n", (int)outputInfo.oRowsUpdated);
printf(" rows rejected : %ld\n", (int)outputInfo.oRowsRejected);
printf(" rows committed: %ld\n", (int)outputInfo.oRowsCommitted);
/* display content of the new table */
rc = NewTableDisplay();
/* drop new table */
printf("\n DROP TABLE newtable\n");
EXEC SQL DROP TABLE newtable;
EMB_SQL_CHECK("new table -- drop");
return 0;
} /* TbImport */
int 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];
printf("\n-----------------------------------------------------------");
printf("\nUSE THE DB2 API:\n");
printf(" sqluvqdp -- Quiesce Table Spaces for Table\n");
printf(" db2Load -- Load\n");
printf("TO LOAD DATA INTO A TABLE.\n");
/* create new table */
printf("\n CREATE TABLE newtable(deptnumb SMALLINT NOT NULL,");
printf("\n deptname VARCHAR(14))\n");
EXEC SQL CREATE TABLE newtable(deptnumb SMALLINT NOT NULL,
deptname VARCHAR(14));
EMB_SQL_CHECK("new table -- create");
/* quiesce table spaces for table */
printf("\n Quiesce the table spaces for 'newtable'.\n");
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");
/* load table */
mediaList.media_type = SQLU_CLIENT_LOCATION;
mediaList.sessions = 1;
mediaList.target.location =
(struct sqlu_location_entry *)malloc(sizeof(struct 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 *)malloc(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;
printf("\n Load table.\n");
printf(" client source file name : %s\n", dataFileName);
printf(" action : %s\n", actionString);
printf(" client message file name: %s\n", localMsgFileName);
/* load table */
db2Load (db2Version970, /* Database version number */
¶mStruct, /* In/out parameters */
&sqlca); /* SQLCA */
DB2_API_CHECK("table -- load");
/* free memory allocated */
free(pAction);
/* display load info */
printf("\n Load info.\n");
printf(" rows read : %d\n", (int)outputInfoStruct.oRowsRead);
printf(" rows skipped : %d\n", (int)outputInfoStruct.oRowsSkipped);
printf(" rows loaded : %d\n", (int)outputInfoStruct.oRowsLoaded);
printf(" rows deleted : %d\n", (int)outputInfoStruct.oRowsDeleted);
printf(" rows rejected : %d\n", (int)outputInfoStruct.oRowsRejected);
printf(" rows committed: %d\n", (int)outputInfoStruct.oRowsCommitted);
/* display content of the new table */
rc = NewTableDisplay();
/* drop new table */
printf("\n DROP TABLE newtable\n");
EXEC SQL DROP TABLE newtable;
EMB_SQL_CHECK("new table -- drop");
return 0;
} /* TbLoad */
int TbLoadQuery(void)
{
int rc = 0;
struct sqlca sqlca = {0};
char tableName[128];
char loadMsgFileName[128];
db2LoadQueryStruct loadQueryParameters;
db2LoadQueryOutputStruct loadQueryOutputStructure;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE DB2 API:\n");
printf(" db2LoadQuery -- Load Query\n");
printf("TO CHECK THE STATUS OF A LOAD OPERATION.\n");
/* 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);
printf("\n Note: the table load for '%s' is NOT in progress.\n", tableName);
printf(" So an empty message file '%s' will be created,\n", loadMsgFileName);
printf(" and the following values will be zero.\n");
DB2_API_CHECK("status of load operation -- check");
printf("\n Load status has been written to local file %s.\n",
loadMsgFileName);
printf(" Number of rows read = %d\n",
loadQueryOutputStructure.oRowsRead);
printf(" Number of rows skipped = %d\n",
loadQueryOutputStructure.oRowsSkipped);
printf(" Number of rows loaded = %d\n",
loadQueryOutputStructure.oRowsLoaded);
printf(" Number of rows rejected = %d\n",
loadQueryOutputStructure.oRowsRejected);
printf(" Number of rows deleted = %d\n",
loadQueryOutputStructure.oRowsDeleted);
printf(" Number of rows committed = %d\n",
loadQueryOutputStructure.oRowsCommitted);
printf(" Number of warnings = %d\n",
loadQueryOutputStructure.oWarningCount);
return 0;
} /* TbLoadQuery */