|
DB2 UDB可以将数据通过EXPORT工具导出为有分隔符的ASCII文件（Delimited ASCII files），例如：EXPORT TO names.del OF DEL SELECT * FROM NAMES，它将表NAMES导入到names.del中，它的格式是Delimited ASCII，如下图所示：
在DB2/400中能不能将数据也导出成这种格式呢？
下面就介绍一种方法来实现将AS400的文件转换为DEL格式。
1． 创建一个目标物理文件DELPC, 用它来保存DEL格式的文件。
Create Physical File (CRTPF)
Type choices, press Enter.
File . . . . . . . . . . . . . . > DELPC Name
Library . . . . . . . . . . . > XQLIB Name, *CURLIB
Source file . . . . . . . . . . QDDSSRC Name
Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB
Source member . . . . . . . . . *FILE Name, *FILE
Record length, if no DDS . . . . > 200 Number
Generation severity level . . . 20 0-30
Flagging severity level . . . . 0 0-30
File type . . . . . . . . . . . *DATA *DATA, *SRC
Member, if desired . . . . . . . > DEL Name, *FILE, *NONE
Text 'description' . . . . . . . *SRCMBRTXT
按一下F10
Create Physical File (CRTPF)
Type choices, press Enter.
Additional Parameters
Source listing options . . . . . *SRC, *NOSRC, *SOURCE...
+ for more values
System . . . . . . . . . . . . . *LCL *LCL, *RMT, *FILETYPE
Expiration date for member . . . *NONE Date, *NONE
Maximum members . . . . . . . . 1 Number, *NOMAX
Access path size . . . . . . . . *MAX1TB *MAX1TB, *MAX4GB
Access path maintenance . . . . *IMMED *IMMED, *DLY, *REBLD
Access path recovery . . . . . . *NO, *AFTIPL, *IPL
Force keyed access path . . . . *NO *NO, *YES
Member size:
Initial number of records . . > *NOMAX 1-2147483646, *NOMAX
Increment number of records . Number
Maximum increments . . . . . . Number
请注意上面的红色字体：
Record length=200，规定每条记录长度为200bytes;
Member=DEL,规定纪录格式为DEL格式；
Initial number of records=*NOMAX,规定对于表中的每个member中的纪录数没有限制。
2． 用命令CPYTOIMPF将源文件复制到目标文件DELPC中。
Copy To Import File (CPYTOIMPF)
Type choices, press Enter.
From file:
File . . . . . . . . . . . . . > T1 Name
Library . . . . . . . . . . > XQLIB Name, *LIBL, *CURLIB
Member . . . . . . . . . . . . *FIRST Name, *FIRST, *ALL
To data base file:
File . . . . . . . . . . . . . > DELPC Name
Library . . . . . . . . . . > XQLIB Name, *LIBL, *CURLIB
Member . . . . . . . . . . . . *FIRST Name, *FIRST, *FROMMBR, *ALL
To stream file . . . . . . . . .
Replace or add records . . . . . *ADD *ADD, *REPLACE
To CCSID . . . . . . . . . . . . *FILE 1-65533, *FILE
Record delimiter . . . . . . . . *EOR Character value, *EOR...
Record format of import file . . *DLM *DLM, *FIXED
String delimiter . . . . . . . . '"' Character value, *NONE
Field delimiter . . . . . . . . ',' Character value
请注意上面的红色字体：
这里的XQLIB/T1是源文件名;
XQLIB/DELPC是目标文件名，用于保存DEL格式的文件;
String delimiter . . . . . . . . '"' :字符串分隔符是“””;
Field delimiter . . . . . . . . ',':字段分隔符是“,”;
3． 进入STRSQL查看：
STRSQL
DELPC
"R","04","LEC OFF-NET "
"C","05","NON-NUMERIC DATA IN NUMERIC "
"R","06","INVALID CREDIT LEC "
"R","08","LEC MASTER FILE ERROR "
"C","09","LEC NOT IN TPM FILE OR LEC MST "
"C","20","INVALID RECORD ID "
"C","21","ORIGINATING NPA INVALID PER BC "
"C","22","ORIGINATING NPA/NXX NOT ON TPM "
"C","23","ORIG NUMBER = TERMINATING NUM "
"R","24","SENT PAID CALL/ORIG OCN <> BIL "
"L","32","ORIGINATING NXX INVALID "
"R","33","ORIGINATING NPA INVALID "
"R","34","ORIGINATING NUMBER LENTH INVAL "
"L","35","ORIGINATING NUMBER INVALID "
"R","36","ORIGINATING STATE INVALID "
"R","04","LEC OFF-NET "
"C","05","NON-NUMERIC DATA IN NUMERIC "
"R","06","INVALID CREDIT LEC "
4． 同时也可以用通过FTP将文件直传入到PC机，用于导入到其他数据库。
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
C:\WINNT\system32>e:
E:\>ftp 9.181.159.180
Connected to 9.181.159.180.
220-QTCP at NEW570.CN.IBM.COM.
220 Connection will close if idle more than 5 minutes.
User (9.181.159.180:(none)): xiaoqing
331 Enter password.
Password:
230 XIAOQING logged on.
ftp> lcd
Local directory now E:\.
250-NAMEFMT set to 1.
250 "/QSYS.LIB/XQLIB.LIB/DELPC.FILE" is current directory.
ftp> dir
200 PORT subcommand request successful.
125 List started.
XIAOQING 32768 02/08/06 12:58:47 *FILE
XIAOQING *MEM DEL.MBR
250 List completed.
ftp: 113 bytes received in 0.00Seconds 113000.00Kbytes/sec.
ftp> get del.mbr delpc.txt
200 PORT subcommand request successful.
150 Retrieving member DEL in file DELPC in library XQLIB.
250 File transfer completed successfully.
ftp: 2190 bytes received in 0.00Seconds 2190000.00Kbytes/sec.
ftp>
用这种方法可以很方便地将AS/400的数据导出为DEL格式的文件。 |
