IBM Support

CPYTOIMPF and CPYFRMIMPF examples

Troubleshooting


Problem

The Copy to Import File (CPYTOIMPF) command can create a comma-delimited stream file from an externally described file (SQL Table, Physical File)
The Copy from Import File (CPYFRMIMPF) command can take a comma-delimited stream file and populate an externally described file (SQL Table, Physical File)

Note these are just simple examples.
The commands have numerous parameters.
Review online documentation for specific questions.

Cause

CPYTOIMPF and CPYFRMIMPF commands are a good way to load / offload data from the IBM i.

Here are some simple examples.   This TechNote is set up to show you a series of examples using an IBM i provided set of tables.
Example 3 below is likely the example the one you will need for CPYTOIMPF.

 
First, some setup for these examples.  We will use these IBM i provided set of tables for the examples in this TechNote.

CALL QSYS.CREATE_SQL_SAMPLE ('DBSAMPLE') <- from STRSQL, Run SQL Scripts, etc.
mkdir '/tmp/N1017894' <- from IBM i command line

We use a table in schema (library) DBSAMPLE and the IFS directory '/tmp/N1017894'

Use a runqry () dbsample/employee (or something) to view the data in the EMPLOYEE table for reference.

Here are the first few columns of the first few rows:

EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT (more columns ->)
000010 CHRISTINE I HAAS A00
000020 MICHAEL L THOMPSON B01
000030 SALLY A KWAN C01
000050 JOHN B GEYER E01  
(more rows)


Second, examples of CPYTOIMPF - off load data from the IBM i to the IFS.  Here is a simple example:

Example 1:
CPYTOIMPF FROMFILE(DBSAMPLE/EMPLOYEE)
TOSTMF('/tmp/N1017894/employee')
RCDDLM(*CRLF)


In the resulting IFS stream file, you see something like this:
(again just the first few rows and first columns)

"000010","CHRISTINE","I","HAAS","A00", ...
"000020","MICHAEL","L","THOMPSON","B01", ...
"000030","SALLY","A","KWAN","C01", ...
"000050","JOHN","B","GEYER","E01", ...


Notice that the "columns" in the resulting IFS stream file are separated by commas and each value is double quotation marks. That can be adjusted.

Here is another example where we do NOT want the double quotation marks:

Example 2:
CPYTOIMPF FROMFILE(DBSAMPLE/EMPLOYEE)
TOSTMF('/tmp/N1017894/employee')
RCDDLM(*CRLF)
STRDLM(*NONE)


(added the STRDLM parameter)

000010,CHRISTINE,I,HAAS,A00, ...
000020,MICHAEL,L,THOMPSON,B01, ...
000030,SALLY,A,KWAN,C01, ...
000050,JOHN,B,GEYER,E01, ...


Similar to before but now without any double quotes around each column value.

Another set of parameters that frequently comes in play is the CCSID (Coded Character Set IDentifiers) parameters.

Frequently the data on the IBM i is in EBCDIC but you want the data on the IFS in ASCII.

While still within IFS, do an "8=Display attributes" on the 'employee' stream file.
Notice that the "Coded character set ID" is 37. That's EBCDIC. 
37 is the common CCSID for the United States.
The underlying table had CCSID 37 and by default that is what used on the stream file creation.

We can add the STMFCCSID parameter to get an ASCII CCSID on the output IFS stream file

Example 3:  (this is the most common starting point for CPYTOIMPF users)

This example takes data from an IBM i table (DBSAMPLE/EMPLOYEE in this example),
copies it to an IFS file (/tmp/N1017894/employee),
put a carriage return and line feed at the end of each row (RCDDLM(*CRLF)),
does not put any quotes around each field of that row (STRDLM(*NONE)),
and creates that IFS output file with CCSID / Code Page of 1252 (STMFCCSID(*PCASCII))


CPYTOIMPF FROMFILE(DBSAMPLE/EMPLOYEE)
TOSTMF('/tmp/N1017894/employee')
RCDDLM(*CRLF)
STRDLM(*NONE)
STMFCCSID(*PCASCII)


Check the CCSID now within IFS with an "8=Display attributes" on the 'employee' stream file and now you have 1252 for "Coded character set ID".
Either *PCASCII or *STDASCII usually works for non-IBM i.

Third, example with CPYTOIMPF where we want the resulting IFS stream file to match what one sees with a DSPPFM on the IBM i.   Common request with a change from FTP to SFTP.

For this example, we need an IBM i physical file / SQL Table with a ZONED / NUMERIC column for a good example


a) CREATE TABLE TBLZONE (COL1 CHAR (1 ),   
ZONE1 NUMERIC (5 ))  

b) INSERT INTO TBLZONE 
VALUES('a',1)              

c) DSPPFM FILE(TBLZONE) 

Results in:

File . . . . . . :   TBLZONE
Member . . . . . :   TBLZONE

...+.   
a00001  
****** END OF DATA ******    

Notice that there are 4 zeros - that makes sense as it's a ZONED(5,0) / NUMERIC(5,0)

But this CPYTOIMPF adds a zero to the resulting IFS stream file.

CPYTOIMPF FROMFILE(TBLZONE) 
          TOSTMF('tblzone.txt')
          RCDDLM(*CRLF)             
          DTAFMT(*FIXED)            
          STRDLM(*NONE)             
          NUMFLDPAD(*ZERO)           

Resulting IFS stream file:   

 ************Beginning of data*************
a0000001                                  
************End of Data********************  

Here is one option to prevent that. 


We create a temporary file in QTEMP, without any column specifications,
CPYF from the original Physical File / SQL Table to that temporary flat file,
and do the CPYTOIMPF from that temporary flat file to the IFS

CRTPF FILE(QTEMP/O_TBLZONE)  
      RCDLEN(132)           

CPYF FROMFILE(TBLZONE)  
     TOFILE(QTEMP/O_TBLZONE)    
     MBROPT(*REPLACE)           
     CRTFILE(*NO)               
     FMTOPT(*NOCHK)             
                  
CPYTOIMPF FROMFILE(QTEMP/O_TBLZONE)      
          TOSTMF('tblzone.txt')
          RCDDLM(*CRLF)                  
          STRDLM(*NONE)                 

Now your IFS stream file matches your DSPPFM output:

....+....1....+....2....+....3....+....4....+
 ************Beginning of data**************
a00001                                       
 ************End of Data********************


Fourth, examples of CPYFRMIMPF - Data from IFS and insert into an IBM i physical file / SQL Table

For this example, we'll use the IFS file we just created.

Let's create a new, empty physical file to use as the 'to file'

CRTDUPOBJ OBJ(EMPLOYEE)
FROMLIB(DBSAMPLE)
OBJTYPE(*FILE)
TOLIB(DBSAMPLE)
NEWOBJ(EMPLOYEE2)
DATA(*NO)


So we made a copy of the existing EMPLOYEE physical file but named it EMPLOYEE2.

You can use "runqry () dbsample/employee2" on a command line to verify it is empty.

Here is a simple CPYFRMIMPF

CPYFRMIMPF FROMSTMF('/tmp/N1017894/employee')
TOFILE(DBSAMPLE/EMPLOYEE2)
MBROPT(*ADD)
RCDDLM(*CRLF)
STRDLM(*NONE)


This takes the data in IFS stream file 'employee' and insert it into IBM i physical file 'employee2'

Now run "runqry () dbsample/employee2" again and you see that it is populated with the data from the IFS stream file, employee.


Notice that both commands do have some similar or identical parameters.

Generally speaking, we see the RCDDLM(*CRLF) and the various CCSID parameters all the time.

[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"DB2 for IBM i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

N1017894

Document Information

Modified date:
03 March 2020

UID

nas8N1017894