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 (think SQL Table, Physical File)
The Copy from Import File (CPYFRMIMPF) command can take a comma delimited stream file and populate an externally described file (think SQL Table, Physical File)

Note that these are just simple examples.
The commands have numerous parameters.
You should 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.


First some setup for these examples.

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

We will 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 is 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:

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

In the resulting IFS stream file, you will 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 quoted. That can be adjusted.

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

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

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 work good for non IBM i folks.
 

 

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 zeroes - 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 will 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 just 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 will take the data in IFS stream file 'employee' and insert it into IBM i physical file 'employee2'

Now run "runqry () dbsample/employee2" again and you will 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.

Historical Number

18304041

Document information

More support for: IBM i

Component: DB2 for IBM i

Software version: Version Independent

Operating system(s): IBM i

Reference #: N1017894

Modified date: 29 October 2018