Example 1
TABLE1 has 5 columns:
ASCFILE1 has 6 elements:
Data Records:
1...5....10...15...20...25...30...35...40 Test data 1 XXN 123abcdN Test data 2 and 3 QQY wxyzN Test data 4,5 and 6 WWN6789 Y
The following command loads the table from the file:
db2 load from ascfile1 of asc modified by striptblanks reclen=40
method L (1 20, 21 22, 24 27, 28 31)
null indicators (0,0,23,32)
insert into table1 (col1, col5, col2, col3)
Notes:
Example 2 (Loading LOBs from Files)
TABLE1 has 3 columns:
ASCFILE1 has 3 elements:
The following files reside in either /u/user1 or /u/user1/bin:
Data Records in ASCFILE1:
1...5....10...15...20...25...30. REC1 ASCFILE2 ASCFILE3 REC2 ASCFILE4 ASCFILE5 REC3 ASCFILE6 ASCFILE7
The following command loads the table from the file:
db2 load from ascfile1 of asc
lobs from /u/user1, /u/user1/bin
modified by lobsinfile reclen=22
method L (1 4, 6 13, 15 22)
insert into table1
Notes:
Example 3 (Using Dump Files)
Table FRIENDS is defined as:
table friends "( c1 INT NOT NULL, c2 INT, c3 CHAR(8) )"
If an attempt is made to load the following data records into this table,
23, 24, bobby , 45, john 4,, mary
the second row is rejected because the first INT is NULL, and the column definition specifies NOT NULL. Columns which contain initial characters that are not consistent with the DEL format will generate an error, and the record will be rejected. Such records can be written to a dump file (see Table 8).
DEL data appearing in a column outside of character delimiters is ignored, but does generate a warning. For example:
22,34,"bob" 24,55,"sam" sdf
The utility will load "sam" in the third column of the table, and the characters "sdf" will be flagged in a warning. The record is not rejected. Another example:
22 3, 34,"bob"
The utility will load 22,34,"bob", and generate a warning that some data in column one following the 22 was ignored. The record is not rejected.
Example 4 (Loading DATALINK Data)
The following command loads the table MOVIETABLE from the input file delfile1, which has data in the DEL format:
db2 load from delfile1 of del
modified by dldel|
insert into movietable (actorname, description, url_making_of, url_movie)
datalink specification (dl_url_default_prefix "http://narang"),
(dl_url_replace_prefix "http://bomdel" dl_url_suffix ".mpeg")
for exception excptab
Notes:
actorname VARCHAR(n) description VARCHAR(m) url_making_of DATALINK (with LINKTYPE URL) url_movie DATALINK (with LINKTYPE URL)
The following sample program shows how to:
For detailed information about the SAMPLE database, see the Administration Guide.
The source file for this sample program (tload.sqc) can be found in the \sqllib\samples\c directory. It contains both DB2 APIs and embedded SQL calls. The script file bldvaemb.cmd, located in the same directory, contains the commands to build this and other sample programs. For general information about creating applications containing DB2 administrative APIs, and detailed information about compile and link options, see the Application Building Guide. To build the sample program tload from the source file tload.sqc on OS/2:
tload.bnd tload.c util.obj tload.obj tload.exe
To run the sample program (executable file), enter tload. You might find it useful to examine some of the generated files, such as the message files, and the delimited ASCII data file.
/******************************************************************************
**
** Source File Name = tload.sqc 1.4
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 1997
** 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
** 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.
**
*******************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include <malloc.h>
#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;
#ifdef DB2AIX
#define WORKDIR "/u/workdir"
#else
#define WORKDIR "."
#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;
struct sqlu_media_list *pCopyTargetList;
char LocalMsgFileName[] = "LOADMSG";
char RemoteMsgFileName[] = "RLOADMSG";
short CallerAction;
long *pNullIndicators;
void *pReserved;
EXEC SQL BEGIN DECLARE SECTION;
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
printf ("This is sample program 'tload.sqc'\n");
/* 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 statement *
\******************************************************************/
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;
CHECKERR ("CONNECT TO SAMPLE");
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
CHECKERR ("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);
CHECKERR ("exporting table");
printf ("Rows exported %d\n", outputInfo.rowsExported);
free (ActionString);
/* need to allocate the proper amount of space for the SQL statement */
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));
CHECKERR ("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);
CHECKERR ("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 = 1; /* consistency points as frequent */
/* 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 */
/* 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);
CHECKERR ("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;
CHECKERR ("CONNECT RESET");
}
/* end of program : tload.sqc */
The source file for a sample program called loadqry.sqc can be found in the \sqllib\samples\c directory. This sample program shows how to use an API to query the current status of a load operation against a database to which the program is connected. It contains both DB2 APIs and embedded SQL calls. The script file bldvaemb.cmd, located in the same directory, contains the commands to build this and other sample programs. For general information about creating applications containing DB2 administrative APIs, and detailed information about compile and link options, see the Application Building Guide. To build the sample program loadqry from the source file loadqry.sqc on OS/2:
loadqry.bnd loadqry.c util.obj loadqry.obj loadqry.exe
To run the sample program (executable file), enter loadqry. You might find it useful to examine the message file. This file will contain information only if the program is run when there is a load operation in progress.