/****************************************************************************
** (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   */
  	   &paramStruct,             /* 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