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

On 17-May-2017 15:14 -0600, Robert Rogerson wrote:

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>

Note: Best to add a predicate AND JOTYPE<>'IL'

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 "no check" for the Format Option (FMTOPT) implies the user will take all necessary precautions to ensure compatibility between the Record Format (RCDFMT) of the From File (FROMFILE) and and the To File (TOFILE). The *TYPE4, by default [specifications on a DSPJRN request], will store the JOESD as a VARCHAR [alluded to be, as I infer, when any VARCHAR columns are present in the file(s) for which entries are being logged, but AFaIK, always]. As such, there is a 2-byte length prefix in the JOESD column that must be accommodated.

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 '' ,

One possibility is to add a 2-byte integer column, e.g. named JOESD_Vlen, in this position of the WEEJRN3 file:

JOESD_Vlen SMALLINT NOT NULL DEFAULT 0 ,

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.

The 2-byte integer VLen values must be valid for every row of data copied; these values must align in the buffer of data read from the From-File. The above suggested modifications should allow the process to function without the error.

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.

Immaterial in this scenario; i.e. the VLen portion of the VarChar is always validated, irrespective of DDS vs SQL. The issue arose due to the journaled file having VarLen fields, and thus the Output File Record Format has a nuanced change from the files without any columns of a variable-length data-type.

Can anyone shed more light on my issue and hopefully suggest a
solution?

Possibly the R-IL entries may not have valid JOESD, as they are "illegitimate" as far as "record level operation" are concerned, with respect to the actual record-data. That is to suggest, I doubt a "default record" could be generated *and* still have the ability to store the "Increment Record Limit" data.

An alternate means to effect the same resolution [as with the added column, as alluded in my inline comments above] for the problem introduced by the existence of the VLen portion of the JOESD, would be to cast the JOESD as CHAR [i.e. CAST(JOESD as CHAR(the_length))] when creating the <tempJRN2> file.

Note: In the "Outfile format (OUTFILFMT) - Help" for the Display Journal (DSPJRN) command [and should be the "same" in the KnowledgeCenter docs], the "Entry-Specific Data" is denoted with a (3) as a special note, and the number three descriptive text says:

(3) The first two bytes are the length of the variable-
length field followed by the actual data if the fields
are variable-length fields.