What’s New With CPYFRMIMPF And CPYTOIMPF?

January 9, 2013 Michael Sansoterra

As my years as a developer whiz by, I often find myself ignorant of the newest features available. For example, I’ve used the Copy From Import File (CPYFRMIMPF) and Copy To Import File (CPYTOIMPF) commands so often that I just became (as my old boss used to say) “fat, dumb, and happy” when using them, not realizing IBM has added new features since the time I first studied these commands. I’ll briefly share a few of the relatively recent enhancements to these commands.

First of all, in i7.1 Technology Refresh 5 (TR5), CPYTOIMPF has been enhanced with a new parameter that controls whether it will include the table’s column names as column headings in the first row of the export.

Consider the following simple example of an ITEMS table:

CREATE TABLE DEV/ITEMS
(ITEM_NO INT NOT NULL PRIMARY KEY,
ITEM_DESCRIPTION VARCHAR(50) CCSID 37 NOT NULL,
ITEM_PRICE DEC(19,4) NOT NULL DEFAULT 0 
)

INSERT INTO DEV/ITEMS
VALUES(1,'Whirlpool Washing Machine',750),
      (2,'Whirlpool Dryer (Gas)',775),
      (3,'Maytag Dishwasher',600)

To export this data to a text file on the IFS, simply include the new Add Column Names (ADDCOLNAM) parameter to the CPYTOIMPF command:

CPYTOIMPF FROMFILE(DEV/ITEMS)
          TOSTMF('/tmp/Items.csv')
          RCDDLM(*CRLF)
          ADDCOLNAM(*SYS)

The allowed values for this parameter are *NONE (default), *SYS, and *SQL. Specifying *SYS will include the system (short) field names in the export file’s heading row. The combination of defaults and options on the above CPYTOIMPF example will create a comma delimited text file, and the raw output looks like this:

ITEM_NO,ITEM_00001,ITEM_PRICE
1          ,"Whirlpool Washing Machine",750.0000
2          ,"Whirlpool Dryer (Gas)",775.0000
3          ,"Maytag Dishwasher",600.0000

Specifying *SQL will include the long column names as the headings:

CPYTOIMPF FROMFILE(DEV/ITEMS)
          TOSTMF('/tmp/Items.csv')
          RCDDLM(*CRLF)
          ORDERBY(ITEM_DESCRIPTION)
          ADDCOLNAM(*SQL)

This time, the raw output has the long column names in the first row:

ITEM_NO,ITEM_DESCRIPTION,ITEM_PRICE
3          ,"Maytag Dishwasher",600.0000
2          ,"Whirlpool Dryer (Gas)",775.0000
1          ,"Whirlpool Washing Machine",750.0000

Since column names are often “ugly” and not aesthetically appealing for users, you can first copy your query results to a temp table with friendly column names:

CREATE TABLE QTEMP/ITEM_EXP AS (
SELECT
ITEM_NO AS "Item No",
ITEM_DESCRIPTION "Description",
ITEM_PRICE       "Unit Price"
  FROM DEV/ITEMS) WITH DATA

And then run your export from the temp table to get this export file:

"Item No","Description","Unit Price"
1          ,"Whirlpool Washing Machine",750.0000
2          ,"Whirlpool Dryer (Gas)",775.0000
3          ,"Maytag Dishwasher",600.0000

Those column names will look much nicer when the export file is viewed with Excel.

In the future, I hope IBM will add additional options for the heading export. It would be grand to export the column text or column heading information instead of column names. DDS file fields and DB2 for i table columns can have text descriptions and column headings defined, so why not give an option to export them? Incidentally, you can use SQL’s LABEL ON statement to define column text and/or headings.

You may have noticed I also included the ORDERBY parameter in an earlier example of CPYTOIMPF. This parameter will accept any column list that is valid in an SQL ORDER BY clause. A special option of *ARRIVAL is allowed as well. This little gem will allow you to sort your results without having to create an intermediate sorted temporary table. This option is available by PTF for i6.1 and i7.1.

CPYFRMIMPF also has a new option to ignore the heading row on import (in i7.1 TR5). Specifying RMVCOLNAM(*YES) will cause the import to skip the first row (although this has been possible for a long time by just specifying FROMRCD(2) on the command).

CPYFRMIMPF FROMSTMF('/tmp/Items.csv')
           TOFILE(QTEMP/ITEM_EXP)
           MBROPT(*ADD)
           RCDDLM(*CRLF)
           RMVCOLNAM(*YES)

One more noteworthy option is the MBROPT(*UPDADD), which will cause the CPYFRMIMPF to do an “upsert.” If the imported row is already in the table, it will be updated. Otherwise, it will be appended as a new row. The only requirement for this functionality is the table must have a unique index or primary key. For example, if we want to copy data back into our temporary ITEM_EXP table using the *UPDADD option, we would simply need to make sure that a unique index is present:

CREATE UNIQUE INDEX QTEMP/UDX_ITEM_EXP
ON QTEMP/ITEM_EXP ("Item No")

Now, *UPDADD will update existing data already present in the table using “Item No” as the unique key:

CPYFRMIMPF FROMSTMF('/tmp/Items.csv')
           TOFILE(QTEMP/ITEM_EXP)
           MBROPT(*UPDADD)
           RCDDLM(*CRLF)
           RMVCOLNAM(*YES)

The *UPDADD option has been available since at least V5R3.

While not an exhaustive list of recent changes, this simply shows there may be more functionality present then we realize. When using those familiar i/OS commands, make sure that you’re always looking over your shoulder. You never know when IBM is going to enhance a command to make the developer’s life easier.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot