/******************************************************************************
**
** Source File Name = tload.sqc
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
** To show the use of the QUIESCE TABLESPACE and the LOAD APIs.
** - EXPORT the EMP_RESUME table into a comma delimited file.
** - create a temporary table ('loadtable').
** - QUIESCE the TABLESPACES, preparing the temporary table to be
** LOADable.
** - LOAD the comma delimited file into a temporary table ('loadtable').
**
** STRUCTURES USED :
** sqldcol
** sqlchar
** sqluexpt_out
** sqlu_media_list
** sqluload_in
** sqluload_out
** sqlca
**
** APIs USED :
** EXPORT sqluexpr
** QUIESCE TABLESPACE FOR TABLES sqluvqdp
** LOAD sqluload
**
** FUNCTIONS DECLARED :
** 'C' COMPILER LIBRARY :
** stdio.h - printf
** string.h - fgets, strncpy
**
** DBMS LIBRARY :
** sqlenv.h - see "APIs USED" above
**
** OTHER :
** external :
** check_error : Checks for SQLCODE error, and prints out any
** [in UTIL.C] related information available.
**
** EXTERNAL DEPENDANCIES :
** - Ensure existence of database (SAMPLE) for precompile purposes.
** - Precompile with the SQL precompiler (PREP in DB2)
** - Bind to a database (BIND in DB2)
** - Compile and link with the IBM Cset++ compiler (AIX and OS/2)
** or the Microsoft Visual C++ compiler (Windows)
** or the compiler supported on your platform.
**
** For more information about these samples see the README file.
**
** For more information on programming in C, see the:
** - "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, see the:
** - "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include <malloc.h>
#include "utilemb.h"
#ifdef DB2AIX
#define WORKDIR "/u/workdir"
#else
#define WORKDIR NULL
#endif
EXEC SQL INCLUDE SQLCA;
int main (int argc, char *argv[]) {
short int callerAction = 0;
struct sqldcol DataDescriptor;
struct sqlchar *ActionString;
struct sqlchar *FileTypeMod;
struct sqluexpt_out outputInfo;
char datafile[] = "EXPTABLE.DEL";
char statement[] = "SELECT empno, photo_format, picture FROM emp_photo";
char impStatement[] = "INSERT INTO loadtable (num, format, photo)";
/*
char statement[] = "SELECT empno, photo_format FROM emp_photo";
char impStatement[] = "INSERT INTO loadtable (num, format)";
*/
char msgfile_x[] = "EXPMSG.TXT";
char FileType[] = SQL_DEL;
char table_name[18];
/* Variables for the LOAD API */
struct sqlu_media_list DataFileList;
struct sqlu_media_list *pLobPathList;
struct sqluload_in InputInfo;
struct sqluload_out OutputInfo;
struct sqlu_media_list *pWorkDirectoryList = NULL;
struct sqlu_media_list *pCopyTargetList;
char LocalMsgFileName[] = "LOADMSG";
char RemoteMsgFileName[] = "RLOADMSG";
short CallerAction;
sqlint32 *pNullIndicators =NULL;
void *pReserved = NULL;
EXEC SQL BEGIN DECLARE SECTION;
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
printf ("This is sample program 'tload.sqc'\n");
/* Initialize structures. */
memset(&DataFileList, 0, sizeof(struct sqlu_media_list));
memset(&InputInfo, 0, sizeof(struct sqluload_in));
memset(&OutputInfo, 0, sizeof(struct sqluload_out));
/* need to preset the size of structure field and counts */
outputInfo.sizeOfStruct = SQLUEXPT_OUT_SIZE;
/******************************************************************\
* need to allocate the proper amount of space for the SQL statment *
\******************************************************************/
ActionString = (struct sqlchar *)malloc(strlen(statement)
+ sizeof (struct sqlchar));
ActionString->length = strlen(statement);
strncpy (ActionString->data, statement, strlen(statement));
FileTypeMod = (struct sqlchar *)malloc(strlen("lobsinfile")
+ sizeof (struct sqlchar));
FileTypeMod->length = strlen("lobsinfile");
strncpy (FileTypeMod->data, "lobsinfile", FileTypeMod->length);
/* DELimited format can not have specified names, therefore the
column method is 'D'efault */
DataDescriptor.dcolmeth = SQL_METH_D;
if (argc == 1) {
EXEC SQL CONNECT TO sample;
EMB_SQL_CHECK("CONNECT TO SAMPLE");
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
EMB_SQL_CHECK("CONNECT TO SAMPLE");
}
else {
printf ("\nUSAGE: tload [userid passwd]\n\n");
return 1;
} /* endif */
printf ("Exporting EMP_RESUME table into file '%s'\n", datafile);
/*******************\
* EXPORT API called *
\*******************/
sqluexpr (datafile, NULL, NULL, &DataDescriptor, ActionString,
FileType, FileTypeMod, msgfile_x, 0, &outputInfo, NULL, &sqlca);
EMB_SQL_CHECK("exporting table");
printf ("Rows exported %d\n", outputInfo.rowsExported);
free (ActionString);
/* need to allocate the proper amount of space for the SQL statment */
ActionString = (struct sqlchar *)malloc(strlen(impStatement)
+ sizeof (struct sqlchar));
ActionString->length = strlen(impStatement);
strncpy (ActionString->data, impStatement, strlen(impStatement));
printf ("Creating a temporary table 'loadtable' to load into\n");
/* create a temporary table to import into */
EXEC SQL CREATE TABLE loadtable (num CHARACTER(6), format VARCHAR(10),
photo BLOB(100K));
EMB_SQL_CHECK("CREATE TABLE");
/* end the transaction so the program can quiesce the tablespace */
EXEC SQL COMMIT;
printf ("Quiescing tablespaces for table 'loadtable'\n");
/******************************\
* QUIESCE TABLESPACE FOR TABLE *
\******************************/
sqluvqdp ("loadtable", SQLU_QUIESCEMODE_EXCLUSIVE, NULL, &sqlca);
EMB_SQL_CHECK("QUIESCE TABLESPACES FOR TABLE");
printf ("Loading the file '%s' into 'loadtable'\n", datafile);
/* initializing the variables for the LOAD API */
/* the DataFileList structure */
DataFileList.media_type = SQLU_SERVER_LOCATION;
DataFileList.sessions = 1;
DataFileList.target.location = (sqlu_location_entry *) malloc
(sizeof(sqlu_location_entry) * DataFileList.sessions);
strcpy (DataFileList.target.location->location_entry, datafile);
pLobPathList = NULL;
CallerAction = SQLU_INITIAL;
/* the sqluload input structure */
InputInfo.sizeOfStruct = SQLULOAD_IN_SIZE; /* this should never change */
InputInfo.savecnt = 0; /* consistency points as
infrequently as possible */
InputInfo.restartcnt = 0; /* start at row 1 */
InputInfo.rowcnt = 0; /* load all rows */
InputInfo.warningcnt = 0; /* don't stop for warnings */
InputInfo.data_buffer_size = 0; /* default data buffer size */
InputInfo.sort_buffer_size = 0; /* default warning buffer size */
InputInfo.hold_quiesce = 0; /* don't hold the quiesce */
InputInfo.restartphase = ' '; /* ignored anyway, but must be ' ',L,B,D */
InputInfo.statsopt = SQLU_STATS_NONE; /* don't bother collecting them */
InputInfo.indexing_mode = SQLU_INX_AUTOSELECT; /* let load choose indexing mode */
/* the sqluload output structure */
OutputInfo.sizeOfStruct = SQLULOAD_OUT_SIZE;
/* the CopyTargetList structure */
pCopyTargetList = NULL;
OutputInfo.sizeOfStruct = SQLULOAD_OUT_SIZE;
/******\
* LOAD *
\******/
sqluload (&DataFileList,
pLobPathList,
&DataDescriptor,
ActionString,
FileType,
FileTypeMod,
LocalMsgFileName,
RemoteMsgFileName,
CallerAction,
&InputInfo,
&OutputInfo,
pWorkDirectoryList,
pCopyTargetList,
pNullIndicators,
pReserved,
&sqlca);
EMB_SQL_CHECK("LOADing table");
printf ("Rows loaded %d\nrows committed %d\n", OutputInfo.rowsLoaded,
OutputInfo.rowsCommitted);
free (ActionString);
/* drop the table */
EXEC SQL DROP TABLE loadtable;
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("CONNECT RESET");
return 0;
}
/* end of program : tload.sqc */