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