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