如何将AS400的文件转换为DEL格式的文件

如何将AS400的文件转换为DEL格式的文件  2006-11-09 20:47:42

分类：

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格式的文件。