6.1 ͨдCLPִIMPORTӡ
db2 import from stafftab.ixf of ixf insert into userid.staff

6.2 ͨдCLPִEXPORTӡ
db2 export to staff.ixf of ixf select * from userid.staff

6.3 ʵʾδļmyfile.ixfϢSAMPLEݿSTAFFСļΪIXFʽִ֮ǰӵSAMPLEݿϡͨDB2ݿӵ֮⣬Ķ嶼ļС
db2 import from myfile.ixf of ixf messages msg.txt insert into staff

6.4 ʵʾε벿20еĹԱϢSAMPLEݿSTAFFС뽫ΪIXFʽawards.ixfļСִ֮ǰӵSAMPLEݿ⡣ңеʵΪdeptdepartment
db2 export from awards.ixf of ixf messages msgs.txt insert to staff

dept = 20
6.5 ʵʾδļdelfile1LOBsmovietableУļΪDELļʽġ
db2 import 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"
)

6.6 پһӣݵͬһеıС
ɵ1к͵2еֵͬΪЩ˵DATAFILE1ļûṩֵͬ100101ֱ˵3к͵4С
db2 import from datafile1.del of del replace into table1

ִDATAFILE1TABLE1жֵͬ
db2 import from datafile1.del of del method P(1, 3, 4)
replace into table1 (c1, c3, c4)
db2 import from datafile1.del of del modified by identityignore
replace into table1

ִDATAFILE2TABLE1ÿһжֵͬ
db2 import from datafile2.del of del
replace into table1 (c1, c3, c4)
db2 import from datafile2.del of del modified
by identitymissing replace into table1

6.7 managerdocļӦݵ뵽managerУڵ֮ǰڣϵͳmanagerdocļĸʽñȻݵ롣
db2 connect to employeedb
db2 import from managerdoc of ixf message managermsg
create into manager in <tablespace>
index in <indextablespace>
long in <longtablespace>

6.8 ʵʾδSAMPLEݿSTAFFеϢļmyfile.ixfУļΪIXFʽġִ֮ǰӵSAMPLEݿϡ彫ļУͨDB2Ӵݿӡ
db2 export to myfile.ixf of ixf messages msgs.txt select * from staff

6.9 ʵʾδSAMPLEݿSTAFFе20еĹԱϢΪIXFʽawards.ixfļСִ֮ǰӵSAMPLEݿ⡣ңеʵΪdeptǡdepartment
db2 export to awards.ixf of ixf messages msgs.txt select * from staff

dept = 20
6.10 ʵʾεLOBsDELļС
db2 export to myfile.del of del lobs to mylobs
lobfile lobs1, lobs2modified by lobsinfile
select * from emp_photo

6.11 ʵʾָͨһĿ¼ʱĵڶĿ¼LOBsDELļС
db2 export to myfile.del of del
lobs to /db2exp1, /db2exp2 modified by lobsinfile
select * from emp_photo

6.12 ʵʾʹõűΪַֺΪмΪʮƵ㣬ݻصݿʱʹͬĹߡ
db2 export to myfile.del of del
modified by chardel coldel; decpt,
select * from staff

6.13 stafforgеӦݵmyfileļУļΪPV/IXFʽ
db2 connect to sample
db2 export to myfile of ixf message msg
select staff.name, staff.dept, org.location from org, staff
where org.deptname=staff.dept

6.14 ʵʾʹIXFʽҪӵSAMPLEݿSTAFFеϢmyfile.ixfݿûͨDB2 Connectô彫洢ļУֻ洢ݡ
db2 export to myfile.ixf of ixf messages msgs.txt
select * from staff

6.15 ʵʾʹIXFʽҪϵSAMPLEݿSTAFFейز20ĹԱϢawards.ixf
db2 export to awards.ixf of ixf messages msgs.txt
select * from staff

Уdept = 20
6.16 ʵʾεLOBsDELļС
db2 export to myfile.del of del
lobs to mylobs/lobfile lobs1, lobs2modified by lobsinfile
select * from emp_photo

6.17 ʵʾεLOBsDELļָ״ڶԵһĿ¼ʵļ
db2 export to myfile.del of del
lobs to /db2exp1/, /db2exp2/ modified by lobsinfile
select * from emp_photo

6.18 ʵʾʹõΪַָർݵDELļС
db2export to myfile.del of del
modified by chardel coldel; decpt,
select * from staff

6.19 ʵʾν¼ֲ:
(1)ݵļ
(2)ݵһ
(3)װݵ
(4)װز״̬
ʵԴtbmove.sqcDB2 APIsǶʽSQLãűļbldapp.cmdʵʵtbmoveʵȻ󣬿ɷԼһЩļãϢļͻASCIIļ
/**************************************************************************
** Դļtbmove.sqc
** ʵƶݡ
** DB2APIs USED:
** sqluexpr C 
** sqluimpr C 
** sqluvqdp -- Quiesce Table Spaces for Table
** db2Load C װ
** db2LoadQuery C ѯ
** SQL STATEMENTS USED:
** PREPARE
** DECLARE CURSOR
** OPEN
** FETCH
** CLOSE
** CREATE TABLE
** DROP
** OUTPUT FILE: tbmove.out
**************************************************************************/
#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);

/* ֺ֧ */
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];
/* в */
rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
printf("\nTHIS SAMPLE SHOWS HOW TO MOVE TABLE DATA.\n");
/* ݿ */
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();
/* Ͽݿ */
rc = DbDisconn(dbAlias);
if (rc != 0)
{
return rc;
}
return 0;
}
/*  */

int ExportedDataDisplay(char *dataFileName)
{
struct sqlca sqlca;
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;
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;
struct sqldcol dataDescriptor;
char actionString[256];
struct sqlchar *pAction;
char msgFileName[128];
struct sqluexpt_out outputInfo;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE DB2API:\n");
printf(" sqluexpr -- 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");
/* ģ */
dataDescriptor.dcolmeth = SQL_METH_D;
strcpy(actionString, "SELECT deptnumb, deptname FROM org");
pAction = (struct sqlchar *)malloc(sizeof(short) +
sizeof(actionString) + 1);
pAction->length = strlen(actionString);
strcpy(pAction->data, actionString);
strcpy(msgFileName, "tbexport.MSG");
outputInfo.sizeOfStruct = SQLUEXPT_OUT_SIZE;
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);
/*  */
sqluexpr(dataFileName,
NULL,
NULL,
&dataDescriptor,
pAction,
SQL_DEL,
NULL,
msgFileName,
SQLU_INITIAL,
&outputInfo,
NULL,
&sqlca);
DB2_API_CHECK("data -- export");
/* ڴ */
free(pAction);
/* ʾ */
rc = ExportedDataDisplay(dataFileName);
return 0;
} /* DataExport */

int TbImport(char *dataFileName)
{
int rc = 0;
struct sqlca sqlca;
struct sqldcol dataDescriptor;
char actionString[256];
struct sqlchar *pAction;
char msgFileName[128];
struct sqluimpt_in inputInfo;
struct sqluimpt_out outputInfo;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE DB2API:\n");
printf(" sqluimpr -- Import\n");
printf("TO IMPORT DATA TO A TABLE.\n");
/* ± */
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");
/* ģ */
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");
inputInfo.sizeOfStruct = SQLUIMPT_IN_SIZE;
inputInfo.commitcnt = 10;
inputInfo.restartcnt = 0;
outputInfo.sizeOfStruct = SQLUIMPT_OUT_SIZE;
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);
/*  */
sqluimpr(dataFileName,
NULL,
&dataDescriptor,
pAction,
SQL_DEL,
NULL,
msgFileName,
SQLU_INITIAL,
&inputInfo,
&outputInfo,
NULL,
NULL,
&sqlca);
DB2_API_CHECK("table -- import");
/* пռ */
free(pAction);
/* ʾϢ */
printf("\n Import info.\n");
printf(" rows read : %ld\n", outputInfo.rowsRead);
printf(" rows skipped : %ld\n", outputInfo.rowsSkipped);
printf(" rows inserted : %ld\n", outputInfo.rowsInserted);
printf(" rows updated : %ld\n", outputInfo.rowsUpdated);
printf(" rows rejected : %ld\n", outputInfo.rowsRejected);
printf(" rows committed: %ld\n", outputInfo.rowsCommitted);
/* ʾ± */
rc = NewTableDisplay();
/* ɾ± */
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;
struct db2LoadStruct paramStruct;
struct db2LoadIn inputInfoStruct;
struct db2LoadOut outputInfoStruct;
struct sqlu_media_list mediaList;
struct sqldcol dataDescriptor;
char actionString[256];
struct sqlchar *pAction;
char localMsgFileName[128];
printf("\n-----------------------------------------------------------");
printf("\nUSE THE DB2API:\n");
printf(" sqluvqdp -- Quiesce Table Spaces for Table\n");
printf(" db2Load -- Load\n");
printf("TO LOAD DATA INTO A TABLE.\n");
/* ± */
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ռ */
printf("\n Quiesce the table spaces for newtable.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* quiesceռ */
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 =
(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");
/* Ϣṹ */
inputInfoStruct.piUseTablespace = NULL;
inputInfoStruct.iSavecount = 0; /* consistency points as
inputInfoStruct.iRestartcount = 0; /* start at row 1 */
inputInfoStruct.iRowcount = 0; /* load all rows */
inputInfoStruct.iWarningcount = 0; /* dont stop for warnings */
inputInfoStruct.iDataBufferSize = 0; /* default data buffer size */
inputInfoStruct.iSortBufferSize = 0; /* def. warning buffer size */
inputInfoStruct.iHoldQuiesce = 0; /* dont hold the quiesce */
inputInfoStruct.iRestartphase =  ; /* ignored anyway */
inputInfoStruct.iStatsOpt = SQLU_STATS_NONE; /*dont bother with them*/
inputInfoStruct.iIndexingMode = SQLU_INX_AUTOSELECT;/*let load choose*/
inputInfoStruct.iCpuParallelism = 0;
inputInfoStruct.iNonrecoverable = SQLU_NON_RECOVERABLE_LOAD;
inputInfoStruct.iAccessLevel = SQLU_ALLOW_NO_ACCESS;
inputInfoStruct.iLockWithForce = SQLU_NO_FORCE;
inputInfoStruct.iCheckPending = SQLU_CHECK_PENDING_CASCADE_DEFERRED;
/* ṹ */
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);
/* װر */
db2Load (db2Version810, /* Database version number */
&paramStruct, /* In/out parameters */
&sqlca); /* SQLCA */
DB2_API_CHECK("table -- load");
/* пռ */
free(pAction);
/* ʾװϢ */
printf("\n Load info.\n");
printf(" rows read : %ld\n", outputInfoStruct.oRowsRead);
printf(" rows skipped : %ld\n", outputInfoStruct.oRowsSkipped);
printf(" rows loaded : %ld\n", outputInfoStruct.oRowsLoaded);
printf(" rows deleted : %ld\n", outputInfoStruct.oRowsDeleted);
printf(" rows rejected : %ld\n", outputInfoStruct.oRowsRejected);
printf(" rows committed: %ld\n", outputInfoStruct.oRowsCommitted);
/* ʾ± */
rc = NewTableDisplay();
/* ɾ± */
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;
char tableName[128];
char loadMsgFileName[128];
db2LoadQueryStruct loadQueryParameters;
db2LoadQueryOutputStruct loadQueryOutputStructure;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE DB2API:\n");
printf(" db2LoadQuery -- Load Query\n");
printf("TO CHECK THE STATUS OF A LOAD OPERATION.\n");
/* ʼṹ */
memset(&loadQueryParameters, 0, sizeof(db2LoadQueryStruct));
memset(&loadQueryOutputStructure, 0, sizeof(db2LoadQueryOutputStruct));
/* tablenameѯ */
loadQueryParameters.iStringType = DB2LOADQUERY_TABLENAME;
loadQueryParameters.piString = tableName;
/* ָҪLOADϢ */
loadQueryParameters.iShowLoadMessages = DB2LOADQUERY_SHOW_ALL_MSGS;
/* LOADܽϢ */
loadQueryParameters.poOutputStruct = &loadQueryOutputStructure;
/* Ϣļ */
loadQueryParameters.piLocalMessageFile = loadMsgFileName;
/* DB2API */
strcpy(tableName, "ORG");
strcpy(loadMsgFileName, "tbldqry.MSG");
/* װزѯ */
db2LoadQuery(db2Version810, &loadQueryParameters, &sqlca);
if (sqlca.sqlcode == 3523)
{
printf("\n No load of the table %s is in progress.\n", tableName);
printf(" Empty message file %s created.\n", loadMsgFileName);
}
else
{
DB2_API_CHECK("status of load operation -- check");
printf("\n Load status has been written to local file %.\n",
loadMsgFileName);
printf(" Number of rows read = %ld\n",
loadQueryOutputStructure.oRowsRead);
printf(" Number of rows skipped = %ld\n",
loadQueryOutputStructure.oRowsSkipped);
printf(" Number of rows loaded = %ld\n",
loadQueryOutputStructure.oRowsLoaded);
printf(" Number of rows rejected = %ld\n",
loadQueryOutputStructure.oRowsRejected);
printf(" Number of rows deleted = %ld\n",
loadQueryOutputStructure.oRowsDeleted);
printf(" Number of rows committed = %ld\n",
loadQueryOutputStructure.oRowsCommitted);
printf(" Number of warnings = %ld\n",
loadQueryOutputStructure.oWarningCount);
}
return 0;
}
/* TbLoadQuery */

