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

Hi,

The problem lies in the ALLOCATE. The DSPJRN will output the full defined
length of your VARCHAR but the VARCHAR fields in the destination tempJRN3
file are defined with ALLOCATE so there is no physical room for the
fieldcontent to be mapped with *NOCHK.

Best regards,
-Arco


Op woensdag 17 mei 2017 heeft Robert Rogerson <rrogerson@xxxxxxxxxxx> het
volgende geschreven:

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

--