The following example shows how to export information from the STAFF table in the SAMPLE database (to which the user must be connected) to myfile.ixf, with the output in IXF format. If the database connection is not through DB2 Connect, the index definitions (if any) will be stored in the output file; otherwise, only the data will be stored:
db2 export to myfile.ixf of ixf messages msgs.txt select * from staff
The following example shows how to export the information about employees in Department 20 from the STAFF table in the SAMPLE database (to which the user must be connected) to awards.ixf, with the output in IXF format:
db2 export to awards.ixf of ixf messages msgs.txt select * from staff
where dept = 20
The following example shows how to export LOBs to an DEL file:
db2 export to myfile.del of del lobs to mylobs
lobfile lobs1, lobs2 modified by lobsinfile
select * from emp_photo
The following example shows how to export LOBs to a DEL file, specifying a second directory for files that may not fit into the first directory:
db2 export to myfile.del of del
lobs to /db2exp1, /db2exp2 modified by lobsinfile
select * from emp_photo
The following example shows how to export data to a DEL file, using a single quotation mark as the string delimiter, a semicolon as the column delimiter, and a comma as the decimal point. The same convention should be used when importing data back into the database:
db2 export to myfile.del of del
modified by chardel'' coldel; decpt,
select * from staff
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 (impexp.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 impexp from the source file impexp.sqc on OS/2:
impexp.bnd impexp.c util.obj impexp.obj impexp.exe
To run the sample program (executable file), enter impexp. You might find it useful to examine some of the generated files, such as the message file, and the delimited ASCII data file.
/******************************************************************************
**
** Source File Name = impexp.sqc 1.4
**
** PURPOSE :
** This program is an example of how APIs are implemented in order to
** export and import tables and table data. The order of the program
** is as follows:
** - export a table to a comma-delimited text file
** - import the comma-delimited text file to a DB2 table
** This program needs the embedded SQL calls in order to connect to
** an existing database, then to create a temporary table to work with.
**
** STRUCTURES USED :
** sqldcol
** sqlchar
** sqluexpt_out
** sqluimp_in
** sqluimp_out
** sqlca
**
** APIs USED :
** IMPORT TO sqluimpt_api
** EXPORT sqlgexpt
**
** FUNCTIONS DECLARED :
** 'C' COMPILER LIBRARY :
** stdio.h - printf
** string.h - fgets, strncpy
**
** DBMS LIBRARY :
** sqlenv.h - see "APIs USED" above
**
** OTHER :
** internal :
** list_dcs : Displays a directory of databases
**
** 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>
#ifndef DB2MAC
#include <malloc.h>
#endif
#include "util.h"
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;
EXEC SQL INCLUDE SQLCA;
int main (int argc, char *argv[]) {
short int callerAction = 0;
struct sqldcol columnData;
struct sqlchar *columnStringPointer;
struct sqluexpt_out outputInfo;
struct sqluimpt_in impInput;
struct sqluimpt_out impOutput;
char datafile[] = "EXPTABLE.DEL";
char statement[] = "select name, id from staff";
char impStatement[] = "insert into imptable (name, id)";
char msgfile_x[] = "EXPMSG.TXT";
char msgfile_m[] = "IMPMSG.TXT";
char fileFormat[] = "DEL";
EXEC SQL BEGIN DECLARE SECTION;
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
#ifdef DB268K
/* Before making any API calls for 68K environment,
need to initial the Library Manager */
InitLibraryManager(0,kCurrentZone,kNormalMemory);
atexit(CleanupLibraryManager);
#endif
/* need to preset the size of structure field and counts */
outputInfo.sizeOfStruct = SQLUEXPT_OUT_SIZE;
impInput.sizeOfStruct = SQLUIMPT_IN_SIZE;
impOutput.sizeOfStruct = SQLUIMPT_OUT_SIZE;
impInput.restartcnt = impInput.commitcnt = 0;
/******************************************************************\
* need to allocate the proper amount of space for the SQL statement *
\******************************************************************/
columnStringPointer = (struct sqlchar *)malloc(strlen(statement)
+ sizeof (struct sqlchar));
columnStringPointer->length = strlen(statement);
strncpy (columnStringPointer->data, statement, strlen(statement));
/* DELimited format can not have specified names, therefore the
column method is 'D'efault */
columnData.dcolmeth = '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: impexp [userid passwd]\n\n");
return 1;
} /* endif */
printf ("exporting NAME and ID from STAFF table into file '%s'\n", datafile);
/*******************\
* EXPORT API called *
\*******************/
sqluexpr (datafile, NULL, NULL, &columnData, columnStringPointer,
fileFormat, NULL, msgfile_x, 0, &outputInfo, NULL, &sqlca);
CHECKERR ("exporting table");
printf ("rows exported %d\n", outputInfo.rowsExported);
free (columnStringPointer);
/******************************************************************\
* need to allocate the proper amount of space for the SQL statement *
\******************************************************************/
columnStringPointer = (struct sqlchar *)malloc(strlen(impStatement)
+ sizeof (struct sqlchar));
columnStringPointer->length = strlen(impStatement);
strncpy (columnStringPointer->data, impStatement, strlen(impStatement));
printf ("creating a temporary table 'imptable' to import into\n");
/* create a temporary table to import into */
EXEC SQL CREATE TABLE imptable (name VARCHAR(15), id INT);
CHECKERR ("CREATE TABLE");
printf ("importing the file '%s' into the 'imptable'\n", datafile);
/*******************\
* IMPORT API called *
\*******************/
sqluimpr (datafile, NULL, &columnData, columnStringPointer, fileFormat,
NULL, msgfile_m, 0, &impInput, &impOutput, NULL, NULL, &sqlca);
CHECKERR ("importing table");
printf ("rows imported %d\nnumber of rows committed %d\n",
impOutput.rowsInserted, impOutput.rowsCommitted);
free (columnStringPointer);
/* drop the table */
EXEC SQL DROP TABLE imptable;
CHECKERR ("DROP TABLE");
EXEC SQL CONNECT RESET;
CHECKERR ("CONNECT RESET");
}
/* end of program : impexp.sqc */