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

Thanks Chuck and Arco,

I'm taking baby steps trying to figure this out. I but the JRN3 file down
to only the first couple fields.

First I tried Chuck's suggestion add added JOESD_Vlen.

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 '' ,
JOESD_Vlen SMALLINT NOT NULL DEFAULT 0,
UNIQUE_ID INTEGER NOT NULL DEFAULT 0,
HJ_UNQID INTEGER NOT NULL DEFAULT 0 );

The copy from JRN2 to JRN3 completed normally with only a truncation
information message. But when I query the data it doesn't look correct.
The unique_id in the file is 1 and the HJ_UNQID = 3

JOSEQN JOCODE JOENTT JOESD_VLEN UNIQUE_ID HJ_UNQID
36050 R PT 0 65536 196668
36065 R UB 0 65536 196668
36066 R UP 0 65536 196668
36089 R UB 0 65536 196668
36090 R UP 0 65536 196668

But after looking closely at the results it looked like the JOESD_Vlen was
the issue. So I changed the JRN3 file to...

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,
HJ_UNQID INTEGER NOT NULL DEFAULT 0 ,
IMPORT_ID_Vlen SMALLINT NOT NULL DEFAULT 0) ;
-- IMPORT_ID VARCHAR(60)CCSID 37 NOT NULL DEFAULT '' )

Now querying the JRN3 file resulted in

JOSEQN JOCODE JOENTT UNIQUE_ID HJ_UNQID IMPORT_ID_VLEN
36050 R PT 1 3 60
36065 R UB 1 3 60
36066 R UP 1 3 60
36089 R UB 1 3 60
36090 R UP 1 3 60

Concidently this is the full length of the first VARCHAR field
(IMPORT_ID).

So I created the WEEJRN3 file once more but this time I uncommented the
IMPORT_ID column which I thought would now be populated correctly. No such
luck, back to the same CPF5035 error on IMPORT_ID.

Chuck, Arco, does this make sense to you?

Thanks,

Rob

On 5/17/2017 7:23 PM, CRPence wrote:

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.