CPYF fails when trying to copy JOESD from journal extract...

Hi all,

We have an in house process which can extract journal data and write it to a file which makes the journal data easy to work with.

The basic flow is...

Get all journal entries for the journaled file

DSPJRN JRN(<journal>) FILE((<journaled file>)) OUTFMT(*HEX)
OUTPUT(*OUTFILE) OUTFILFMT(*TYPE4) OUTFILE(<tempJRN1>)
ENTDTALEN(*CALC)

A query is then run selecting JOSEQN, JOCODE, JOENTT, JOTSTP, JOJOB, JOUSER, JONBR , JOPGM, JOOBJ , JOESD from the <tempJRN1> where jocode = 'R' and writing it to <tempJRN2>

There are no errors at this point and <tempJRN2> has been populated.

As a final step <tempJRN2> is copied to <tempJRN3>

CPYF FROMFILE(<tempJRN2>) TOFILE(<tempJRN3>) MBROPT(*ADD) FMTOPT(*nochk)

When I run the copy it fails with CPF5035 and the error is ...

Cause . . . . . : A data mapping error occurred on field IMP_STATUS in
record number 0, record format WEITMR, member number 1, in member WEEJRN3
file WEEJRN3 in library HJC, because of error code 14. The error code
meanings follow:

The error code identifies the error as - 14 -- The length of a variable length field is not valid or the data in a
substring variable is not valid. The error a

A little background. <tempJRN3> is a DDL created table with the 9 fields (excepting JOESD) added to the front of the table. The DDL is

CREATE TABLE hjc/WEEJRN3 (
JOSEQN NUMERIC(10, 0) NOT NULL DEFAULT 0 ,
JOCODE CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
JOENTT CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
JOTSTP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
JOJOB CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
JOUSER CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
JONBR NUMERIC(6, 0) NOT NULL DEFAULT 0 ,
JOPGM CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
JOOBJ CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
UNIQUE_ID INTEGER NOT NULL DEFAULT 0 ,
IMPORT_ID VARCHAR(60) ALLOCATE(0) CCSID 37 NOT NULL DEFAULT '' ,
IMPORT_STATUS FOR COLUMN IMP_STATUS VARCHAR(10) ALLOCATE(0) CCSID 37 NOT NULL DEFAULT '' ,
INSERT_TIMESTAMP FOR COLUMN INSERT_DT TIMESTAMP NOT NULL ,
IMPORT_START_TIMESTAMP FOR COLUMN IMP_START TIMESTAMP NOT NULL ,
IMPORT_DONE_TIMESTAMP FOR COLUMN IMP_DONE TIMESTAMP NOT NULL ,
IMPORT_ERROR FOR COLUMN IMP_ERROR VARCHAR(256) ALLOCATE(0) CCSID 37 NOT NULL DEFAULT '' ,
WH_ID VARCHAR(10) ALLOCATE(0) CCSID 37 NOT NULL DEFAULT '' ,
ITEM_NUMBER FOR COLUMN ITEM_NBR VARCHAR(30) ALLOCATE(0) CCSID 37 NOT NULL DEFAULT '' ,
PALLET_TI INTEGER NOT NULL DEFAULT 0 ,
PALLET_HI INTEGER NOT NULL DEFAULT 0 ,
WEIGHT DOUBLE PRECISION NOT NULL DEFAULT 0 ,
LENGTH DOUBLE PRECISION NOT NULL DEFAULT 0 ,
WIDTH DOUBLE PRECISION NOT NULL DEFAULT 0 ,
HEIGHT DOUBLE PRECISION NOT NULL DEFAULT 0 ,
EMPLOYEE_ID FOR COLUMN EMP_ID VARCHAR(10) ALLOCATE(0) CCSID 37 NOT NULL DEFAULT '' ,
EMPLOYEE_NAME FOR COLUMN EMP_NAME VARCHAR(100) ALLOCATE(0) CCSID 37 NOT NULL DEFAULT '' ,
GO_WH_ID INTEGER NOT NULL DEFAULT 0 ,
GO_ITEM_NUMBER FOR COLUMN GO_ITEM INTEGER NOT NULL DEFAULT 0 ,
ERROR_FLAG CHAR(1) CCSID 37 NOT NULL DEFAULT '' )

RCDFMT WEITMR ;

We use this process all the time with no issues. The difference with this table is that it has VARCHAR fields and is created with DDL.

I've been trying to find a solution or what the problem is but I still haven't been able to find it. One thing that I read about was that SQL validates the data when the data is inserted as opposed to DDS created files which do not.

I think this may be the issue. Can anyone shed more light on my issue and hopefully suggest a solution?

Thanks,

Rob