7.5 װDATALINKݵӣӾDELݸʽļdelfile1װر
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

ʹDB2 LOADװ֮ǰӵݿϡAPIͷļdb2ApiDf.h

/* C API﷨      */
/* ļ: db2ApiDf.h */
/* API:װ*/
/* ... */

SQL_API_RC SQL_API_FN
db2Load (
db2Uint32 versionNumber,
void * pParmStruct,
struct sqlca * pSqlca);

typedef SQL_STRUCTURE db2LoadStruct
{
struct sqlu_media_list *piSourceList;
struct sqlu_media_list *piLobPathList;
struct sqldcol *piDataDescriptor;
struct sqlchar *piActionString;
char *piFileType;
struct sqlchar *piFileTypeMod;
char *piLocalMsgFileName;
char *piTempFilesPath;
struct sqlu_media_list *piVendorSortWorkPaths;
struct sqlu_media_list *piCopyTargetList;
db2int32 *piNullIndicators;
struct db2LoadIn *piLoadInfoIn;
struct db2LoadOut *poLoadInfoOut;
struct db2PartLoadIn *piPartLoadInfoIn;
struct db2PartLoadOut *poPartLoadInfoOut;
db2int16 iCallerAction;
} db2LoadStruct;

typedef SQL_STRUCTURE db2LoadIn
{
db2Uint64 iRowcount;
db2Uint64 iRestartcount;
char *piUseTablespace;
db2Uint32 iSavecount;
db2Uint32 iDataBufferSize;
db2Uint32 iSortBufferSize;
db2Uint32 iWarningcount;
db2Uint16 iHoldQuiesce;
db2Uint16 iCpuParallelism;
db2Uint16 iDiskParallelism;
db2Uint16 iNonrecoverable;
db2Uint16 iIndexingMode;
db2Uint16 iAccessLevel;
db2Uint16 iLockWithForce;
db2Uint16 iCheckPending;
char iRestartphase;
char iStatsOpt;
} db2LoadIn;

typedef SQL_STRUCTURE db2LoadOut
{
db2Uint64 oRowsRead;
db2Uint64 oRowsSkipped;
db2Uint64 oRowsLoaded;
db2Uint64 oRowsRejected;
db2Uint64 oRowsDeleted;
db2Uint64 oRowsCommitted;
} db2LoadOut;

typedef SQL_STRUCTURE db2PartLoadIn
{
char *piHostname;
char *piFileTransferCmd;
char *piPartFileLocation;
struct db2LoadNodeList *piOutputNodes;
struct db2LoadNodeList *piPartitioningNodes;
db2Uint16 *piMode;
db2Uint16 *piMaxNumPartAgents;
db2Uint16 *piIsolatePartErrs;
db2Uint16 *piStatusInterval;
struct db2LoadPortRange *piPortRange;
db2Uint16 *piCheckTruncation;
char *piMapFileInput;
char *piMapFileOutput;
db2Uint16 *piTrace;
db2Uint16 *piNewline;
char *piDistfile;
db2Uint16 *piOmitHeader;
SQL_PDB_NODE_TYPE *piRunStatDBPartNum;
} db2PartLoadIn;

typedef SQL_STRUCTURE db2LoadNodeList
{
SQL_PDB_NODE_TYPE *piNodeList;
db2Uint16 iNumNodes;
} db2LoadNodeList;

typedef SQL_STRUCTURE db2LoadPortRange
{
db2Uint16 iPortMin;
db2Uint16 iPortMax;
} db2LoadPortRange;

typedef SQL_STRUCTURE db2PartLoadOut
{
db2Uint64 oRowsRdPartAgents;
db2Uint64 oRowsRejPartAgents;
db2Uint64 oRowsPartitioned;
struct db2LoadAgentInfo *poAgentInfoList;
db2Uint32 iMaxAgentInfoEntries;
db2Uint32 oNumAgentInfoEntries;
} db2PartLoadOut;

typedef SQL_STRUCTURE db2LoadAgentInfo
{
db2int32 oSqlcode;
db2Uint32 oTableState;
SQL_PDB_NODE_TYPE oNodeNum;
db2Uint16 oAgentType;
} db2LoadAgentInfo;

/* ... */

/* Generic API﷨ */
/* ļ: db2ApiDf.h */
/* API: װ */
/* ... */
SQL_API_RC SQL_API_FN
db2gLoad (
db2Uint32 versionNumber,
void * pParmStruct,
struct sqlca * pSqlca);

typedef SQL_STRUCTURE db2gLoadStruct
{
struct sqlu_media_list *piSourceList;
struct sqlu_media_list *piLobPathList;
struct sqldcol *piDataDescriptor;
struct sqlchar *piActionString;
char *piFileType;
struct sqlchar *piFileTypeMod;
char *piLocalMsgFileName;
char *piTempFilesPath;
struct sqlu_media_list *piVendorSortWorkPaths;
struct sqlu_media_list *piCopyTargetList;
db2int32 *piNullIndicators;
struct db2LoadIn *piLoadInfoIn;
struct db2LoadOut *poLoadInfoOut;
struct db2gPartLoadIn *piPartLoadInfoIn;
struct db2PartLoadOut *poPartLoadInfoOut;
db2int16 iCallerAction;
db2Uint16 iFileTypeLen;
db2Uint16 iLocalMsgFileLen;
db2Uint16 iTempFilesPathLen;
} db2gLoadStruct;

typedef SQL_STRUCTURE db2LoadIn
{
db2Uint64 iRowcount;
db2Uint64 iRestartcount;
char *piUseTablespace;
db2Uint32 iSavecount;
db2Uint32 iDataBufferSize;
db2Uint32 iSortBufferSize;
db2Uint32 iWarningcount;
db2Uint16 iHoldQuiesce;
db2Uint16 iCpuParallelism;
db2Uint16 iDiskParallelism;
db2Uint16 iNonrecoverable;
db2Uint16 iIndexingMode;
db2Uint16 iAccessLevel;
db2Uint16 iLockWithForce;
db2Uint16 iCheckPending;
char iRestartphase;
char iStatsOpt;
} db2LoadIn;

typedef SQL_STRUCTURE db2LoadOut
{
db2Uint64 oRowsRead;
db2Uint64 oRowsSkipped;
db2Uint64 oRowsLoaded;
db2Uint64 oRowsRejected;
db2Uint64 oRowsDeleted;
db2Uint64 oRowsCommitted;
} db2LoadOut;

typedef SQL_STRUCTURE db2gPartLoadIn
{
char *piHostname;
char *piFileTransferCmd;
char *piPartFileLocation;
struct db2LoadNodeList *piOutputNodes;
struct db2LoadNodeList *piPartitioningNodes;
db2Uint16 *piMode;
db2Uint16 *piMaxNumPartAgents;
db2Uint16 *piIsolatePartErrs;
db2Uint16 *piStatusInterval;
struct db2LoadPortRange *piPortRange;
db2Uint16 *piCheckTruncation;
char *piMapFileInput;
char *piMapFileOutput;
db2Uint16 *piTrace;
db2Uint16 *piNewline;
char *piDistfile;
db2Uint16 *piOmitHeader;
SQL_PDB_NODE_TYPE *piRunStatDBPartNum;
db2Uint16 iHostnameLen;
db2Uint16 iFileTransferLen;
db2Uint16 iPartFileLocLen;
db2Uint16 iMapFileInputLen;
db2Uint16 iMapFileOutputLen;
db2Uint16 iDistfileLen;
} db2gPartLoadIn;

typedef SQL_STRUCTURE db2LoadNodeList
{
SQL_PDB_NODE_TYPE *piNodeList;
db2Uint16 iNumNodes;
} db2LoadNodeList;

typedef SQL_STRUCTURE db2LoadPortRange
{
db2Uint16 iPortMin;
db2Uint16 iPortMax;
} db2LoadPortRange;

typedef SQL_STRUCTURE db2PartLoadOut
{
db2Uint64 oRowsRdPartAgents;
db2Uint64 oRowsRejPartAgents;
db2Uint64 oRowsPartitioned;
struct db2LoadAgentInfo *poAgentInfoList;
db2Uint32 iMaxAgentInfoEntries;
db2Uint32 oNumAgentInfoEntries;
} db2PartLoadOut;

typedef SQL_STRUCTURE db2LoadAgentInfo
{
db2int32 oSqlcode;
db2Uint32 oTableState;
SQL_PDB_NODE_TYPE oNodeNum;
db2Uint16 oAgentType;
} db2LoadAgentInfo;

/* ... */

db2LoadQueryװزѯAPIװز״̬ҪκȨӵݿϡAPIļdb2ApiDf.h

/* C API﷨      */
/* ļ: db2ApiDf.h */
/* API: db2LoadQuery */
/* ... */
SQL_API_RC SQL_API_FN
db2LoadQuery (
db2Uint32 versionNumber,
void *pParmStruct,
struct sqlca *pSqlca);

typedef struct
{
db2Uint32 iStringType;
char *piString;
db2Uint32 iShowLoadMessages;
db2LoadQueryOutputStruct *poOutputStruct;
char *piLocalMessageFile;
} db2LoadQueryStruct;

typedef struct
{
db2Uint32 oRowsRead;
db2Uint32 oRowsSkipped;
db2Uint32 oRowsCommitted;
db2Uint32 oRowsLoaded;
db2Uint32 oRowsRejected;
db2Uint32 oRowsDeleted;
db2Uint32 oCurrentIndex;
db2Uint32 oNumTotalIndexes;
db2Uint32 oCurrentMPPNode;
db2Uint32 oLoadRestarted;
db2Uint32 oWhichPhase;
db2Uint32 oWarningCount;
db2Uint32 oTableState;
} db2LoadQueryOutputStruct;

һAPI﷨£

/* ļ: db2ApiDf.h */
/* API: db2gLoadQuery */
SQL_API_RC SQL_API_FN
db2gLoadQuery (
db2Uint32 versionNumber,
void *pParmStruct,
struct sqlca *pSqlca);

typedef struct
{
db2Uint32 iStringType;
db2Uint32 iStringLen;
char *piString;
db2Uint32 iShowLoadMessages;
db2LoadQueryOutputStruct *poOutputStruct;
db2Uint32 iLocalMessageFileLen;
char *piLocalMessageFile
} db2gLoadQueryStruct;

typedef struct
{
db2Uint32 oRowsRead;
db2Uint32 oRowsSkipped;
db2Uint32 oRowsCommitted;
db2Uint32 oRowsLoaded;
db2Uint32 oRowsRejected;
db2Uint32 oRowsDeleted;
db2Uint32 oCurrentIndex;
db2Uint32 oNumTotalIndexes;
db2Uint32 oCurrentMPPNode;
db2Uint32 oLoadRestarted;
db2Uint32 oWhichPhase;
db2Uint32 oWarningCount;
db2Uint32 oTableState;
} db2LoadQueryOutputStruct;

7.17 װصʵӵ£TABLE1ļload.delλĵǰĿ¼Сִload.delװݵTABLE1зС

LOAD FROM LOAD.DEL of DEL
REPLACE INTO TABLE1

7.18 ִʹýڵ34Ϊڵִװز

LOAD FROM LOAD.DEL of DEL REPLACE INTO TABLE1
PARTITIONED DB CONFIG PARTITIONING_DBPARTNUMS (3,4)

7.19 ֻʵӵTABLE1ķУļload.delλĵǰĿ¼Сִʹ÷34Ϊڵ㣬װload.delTABLE1ݿϡ

LOAD FROM LOAD.DEL of DEL REPLACE INTO TABLE1
PARTITIONED DB CONFIG MODE PARTITION_ONLY
PART_FILE_LOCATION /udb/data
PARTITIONING_DBPARTNUMS (3,4)

7.20ֻʹڷ0ϵ1ĬֵΪPARTITIONING_DBPARTNUMSİ壬ִװload.delTABLE1ݿ13ϡ

LOAD FROM LOAD.DEL OF DEL REPLACE INTO TABLE1
PARTITIONED DB CONFIG MODE PARTITION_ONLY
PART_FILE_LOCATION /udb/data
OUTPUT_DBPARTNUMS (1, 3)

7.21 PARTITION_ONLYģʽִװزҪװÿװط/udb/dataĿ¼еķļTABLE1зϣִ
LOAD FROM LOAD.DEL OF DEL REPLACE INTO TABLE1
PARTITIONED DB CONFIG MODE LOAD_ONLY
PART_FILE_LOCATION /udb/data

7.22 ִֻװط4

LOAD FROM LOAD.DEL OF DEL REPLACE INTO TABLE1
PARTITIONED DB CONFIG MODE LOAD_ONLY
PART_FILE_LOCATION /udb/data
OUTPUT_DBPARTNUMS (4)

7.23 LOADװļͷֱд뼸ݿСļλڱTABLE1ÿϣΪload.del.xxxxxxʾ롣ִװļ

LOAD FROM LOAD.DEL OF DEL modified by dumpfile=rejected.rows
REPLACE INTO TABLE1
PARTITIONED DB CONFIG MODE LOAD_ONLY_VERIFY_PART
PART_FILE_LOCATION /udb/data

7.24 ִװݵ1С

LOAD FROM LOAD.DEL OF DEL modified by dumpfile=rejected.rows
REPLACE INTO TABLE1
PARTITIONED DB CONFIG MODE LOAD_ONLY_VERIFY_PART
PART_FILE_LOCATION /udb/data
OUTPUT_DBPARTNUMS (1)

7.25 PARTITION_ONLYLOAD_ONLYģʽPART_FILE_LOCATIONѡָȫϸļÿϴװصļȫϸĻļĿдУôʹضĻļDB2дִSELECT * FROM TABLE1Ļش𼯺езÿ/udb/data/select.out.xxxϵļУxxxʾ룬ԱڽһװصTABLE2С

DECLARE C1 CURSOR FOR
SELECT * FROM TABLE1
LOAD FROM C1 OF CURSOR
REPLACE INTO TABLE2
PARTITIONED DB CONFIG MODE PARTITION_ONLY
PART_FILE_LOCATION /udb/data/select.out

Ȼ󣬿ͨLOADװϲļ

LOAD FROM C1 OF CURSOR REPLACE INTO TABLE2
PARTITIONED CB CONFIG MODE LOAD_ONLY
PART_FILE_LOCATION /udb/data/select.out

7.26 ʵУݿWSDB4ֱΪ04з϶ݿ⣬TABLE1λз϶ĬϽڵСӵ0ִλڷ0ϵûļload.delװݵTABLE1С
load from load.del of del
replace into table1

7.27 µʵУPARTITION_ONLYģʽװݵTABLE1Сļ洢Ŀ¼/udb/dataÿϡ

load from load.del of del
replace into table1 partitioned db config mode
partition_only part_file_location /udb/data

7.28 ִװϵPARTITION_ONLYװزɵļ

load from load.del of del
replace into table1 partitioned db config mode
load_only part_file_location /udb/data

7.29 ִװ

load from load.del of del
replace into table1

7.30 ִ䣬յ1ַ쳣t1exc

create table t1exc like t1
alter table t1exc add column ts timestamp 
add column msg clob(32K)

7.31 ִ䣬յ2ַ쳣t1exc

create table t1exc as 
(
select t1.*, current timestamp
as ts, clob('',32767)
as msg
from t1
)
definition only

7.32 db2moveߵһӦӡ
db2 move sample export

УSAMPLEݿебĬֵеѡá
7.33 ӵuserid1ߡus%rid2֮ûIDбΪtbname1硰%tbname2

db2 move sample
export -tc userid1,us*rid2 -tn tbname1,*tbname2

7.34 ֻʺWindowsϵͳͨdb2moveSAMPLEݿебLOB·D:\LOBPATH1͡C:\LOBPATH2LOBļ

db2 move sample
import -l D:\LOBPATH1,C:\LOBPATH2

7.35 ʵڻUNIXϵͳִװSAMPLEݿеб/home/userid/lobpathĿ¼tmpĿ¼LOBļ
db2 move sample load -l /home/userid/lobpath,/tmp

7.36 ʵREPLACEģʽSAMPLEݿебҽʹضûID롣

db2 move sample
import -io replace -u userid -p password

7.37 ִĲνṹȻͨٴδ

DB2 CONNECT TO Source_db
DB2 EXPORT TO entire_hierarchy.ixf OF IXF HIERARCHY STARTING Person
DB2 CONNECT TO Target_db
DB2 IMPORT FROM entire_hierarchy.ixf OF IXF CREATE INTO
HIERARCHY STARTING Person AS ROOT TABLE

7.38 ӵʵУɵԴݿνṹ뵽ĿݿСܵ20˵ݣֻᵼѡݵĿݿС

DB2 CONNECT TO Source_db
DB2 EXPORT TO entire_hierarchy.del OF DEL HIERARCHY
(
Person,
Employee,
Manager,
Architect,
Student
)
WHERE Age>=20
DB2 CONNECT TO Target_db
DB2 IMPORT FROM entire_hierarchy.del OF DEL
INSERT INTO
(
Person,
Employee (Salary),
Architect
)
IN HIERARCHY
(
Person,
Employee,
Manager,
Architect,
Student
)

