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 can be used to load or offload data from the IBM i.
Here are some simple examples.
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.
Run the following SQL statement from STRSQL or Run SQL Script
CALL QSYS.CREATE_SQL_SAMPLE ('DBSAMPLE')
Create an IFS directory by running the following CL command:
mkdir '/tmp/N1017894'
We use a table in schema (library) DBSAMPLE and the IFS directory '/tmp/N1017894'
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_example1)
TOSTMF('/tmp/N1017894/employee')
RCDDLM(*CRLF)
In the resulting IFS stream file, you see something like this:
"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 character value has double quotation marks around it.
Use STRDLM(*NONE) if you do not want to see double quotes around character fields as shown in Example 2.
Example 2:
CPYTOIMPF FROMFILE(DBSAMPLE/EMPLOYEE_example2)
TOSTMF('/tmp/N1017894/employee')
RCDDLM(*CRLF)
STRDLM(*NONE)
000010,CHRISTINE,I,HAAS,A00, ...
000020,MICHAEL,L,THOMPSON,B01, ...
000030,SALLY,A,KWAN,C01, ...
000050,JOHN,B,GEYER,E01, ...
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 you just created.
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 example takes data from an IBM i table (DBSAMPLE/EMPLOYEE in this example),
copies it to an IFS file (/tmp/N1017894/employee_example3),
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.
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.
Historical Number
N1017894
Was this topic helpful?
Document Information
Modified date:
18 November 2024
UID
nas8N1017894