How to copy large data in del format to IBM i using Cpyfrmimpf

This paper discusses the use of Cpyfrmimpf to Del in the case of http://www.aliyun.com/zixun/aggregation/20522.html "> Test data Copy" based on the author's work demand. The method of large data copy to IBM, and the processing of LOB data and the improvement of copy efficiency are discussed.

IBM I (formerly known as Os400,i5 OS, etc.) is an integrated operating system, the most characteristic of which is that the system itself has built-in database DB2. The operating system itself uses some of the DB2 features and features, and DB2 also uses the underlying functionality of the system itself. Because of the close coupling relationship between the operating system and the database, IBM I has a good performance in the retail and financial fields.

In the actual work process, the author needs to build a customer's simulated data environment and shopping scene, that is, the test environment needs to contain a lot of products, promotions and other information, and then simulate a large number of users concurrent access to stores and shopping, and then perform performance testing work. The loading of simulated data is the precondition of performance test. Test data is generated by specialized tools, and the data itself can be exported as a SQL file or DEL file format. SQL files can be imported directly into the database through DB2-TF, but the processing efficiency is low, it takes more time to import test data, DEL file format data has better processing efficiency, in DB2 LUW version, you can use similar to Listing 1 command to copy data to the database:

Listing 1. Importing data using the LOAD command

load from Px_policy.del of del flushes by Chardel ' insert INTO Px_policy ("px_policy_id", "storeent_id", "STATUS", " NAME "," Implcls "," Xmlparam "," Optcounter ");

However, DB2 for I does not support the LOAD command, so it is not possible to copy data in this way. But IBM I provides a similar function for a CL command called Cpyfrmimpf.

Cpyfrmimpf

Introduction

Cpyfrmimpf (copy from Import file) can copy all or part of the records in the source file to the target file, with FROMSTMF, ToFile, errlvl parameters, please refer to IBM I Information Center. In the author's test data processing process, mainly uses the following several key parameters:

Table 1 Cpyfrmimpf Key parameters

Parameter Action Example FROMSTMF specify the source DEL file, you can use an absolute path or a relative path FROMSTMF ('. /load/acplgpsubs_0.del ') tofile Specify the destination file, a table in the database schema, in the form of a <schema name >/< table name > ToFile (demo120/acplgpsubs ERRLVL the number of error Records ERRLVL (*nomax) rmvblank remove the trailing space character in the source file field Rmvblank (*both) rplnullval (*FLDDFT) Replace the value of the field NULL in the source DEL file Rplnullval (*FLDDFT) RCDDLM Record separator rcddlm (*LF) STRDLM string delimiter strdlm (' \ ') flddlm the separator between records FLDDLM (', ')

Use

The use of the Cpyfrmimpf command can be run directly on the green screen with the CL Format command, or under Qsh command. Since the author has multiple DEL files in the test process that need to be copied to the corresponding datasheet, the method used under Qsh, the command is shown in Listing 2:

Listing 2 Qsh Execution Cpyfrmimpf

System "Cpyfrmimpf FROMSTMF (' ... /load/acplgpsubs_0.del ') tofile (demo120/acplgpsubs) errlvl (*nomax) Rmvblank (*both) rplnullval (*FLDDFT) RCDDLM (*LF) STRDLM (' \ ') "

One thing to note here is the relative path for the DEL file in Listing 2, which is relative to the current Qsh path, not the root path.

Best practices for using CPYFRMIMPF copy data

Get the short name of the table

The name of the DB2 for I data table cannot exceed 10 characters, names of more than 10 characters are represented on IBM I in short names (the first 5 characters are the first 5 characters of the full name of the table, and the 5 characters are 00001 starting numbers, such as a table in the database that the author processes) ATTRDICTGRP short name is ATTRD00001). If the first 5 characters of multiple tables are the same, you can only differentiate them by the numbers that follow, so you must obtain a short name for each long table before copying the data. The name can be obtained in ISeries Navigator, as shown in Figure 1:

Figure 1 The short name of the table

Remove constraint

The format of the command using CPYFRMIMPF to copy data is described above, but in actual use, because of the need to copy a large number of data to the same schema in different tables, and between tables often have foreign key constraints, so we need not only to ensure the accuracy of the test data itself, That is, the data does not violate the constraint conditions, and in the process of inserting in a certain sequence. As a result, the efficiency of copying data is reduced. An alternative approach is to remove all constraints before copying the data. After the copy is complete, plus the removed constraint, the accuracy of the data itself is guaranteed by the data source, and you can use EDTCPCST after adding the constraint to help check which of the tables the field violates the foreign key constraint.