Hi,

I am trying to read a text file with fixed length lines (1000 bytes) on Z/OS and I am trying to write the data to a DB2 table. I am using Data Migrator for this.

I can reproduce the behaviour in a simplefied example.

Text file:
123456789
234567890
345678901
456789012

Master-file:
(textView)
FILENAME=aa_test_flatfile, SUFFIX=FIX ,
CODEPAGE=37,
DATASET=GW1O.EYC.TESTREAD.TXT, $
SEGMENT=SEG1, SEGTYPE=S0, $
FIELDNAME=RECORD_TYPE, USAGE=A4, ACTUAL=A4, $
FIELDNAME=DATA_CONTENT, USAGE=A6, ACTUAL=A6, $
(Access file text view)
this is empty

I am using a Direct Load Flow to read the file and write it to a DB2 table.

Direct Load Flow:
(text view)
-*DM_JOB_TYPE=13
-*DM_USERID=OPSYS\MOERMAE
-*DM_CLIENT_RELEASE_GEN=M728100B_833
-*DM_SERVER_RELEASE_GEN=M728100B_858

-***************************************************

-:START_PRC
SET PANEL=9999
SET MORE=OFF
SET 2PARTNAME=ON
-RUN

-*[Variables to Control Request]
-SET &&CM__TARGET = 'gwtbs49';
-SET &&CM__AUTHOR = 'OPSYS\MOERMAE';
-SET &&CM__REQUEST = '&FOCFEXNAME.EVAL';
-SET &&CM__RETURN = 0;
-SET &&CM__FOCCPU = &FOCCPU.EVAL;
-DEFAULT &DBMSERROR = 1
-DEFAULT &STARTAT = 0
-DEFAULT &STOPAT = 1000000000

-TYPE (ICM18122) Request - &FOCFEXNAME (Owner: OPSYS\MOERMAE) submitted.
-GOTO EP_MAIN;

-EP_MAIN
-TYPE (ICM18742) syn_cgw/gwtbs49 type DB2 Existing target

SET CASESTAT=EXTENDED

SQL DELETE FROM syn_cgw.gwtbs49
END
-RUN
-SET &&CM__RETURN = &FOCERRNUM;
-IF (&&CM__RETURN NE 0) GOTO :ENDJOB;

SQL DB2
COMMIT WORK;
END
-RUN

-TYPE (ICM18743) Starting Load

MODIFY FILE syn_cgw/gwtbs49
FIXFORM FROM SYN_CGW/AA_TEST_FLATFILE ALIAS PROPAGATE
GOTO MATCHIT1
CASE MATCHIT1
COMPUTE
REC_TYPE/A4 MISSING ON=RECORD_TYPE;
REC_LINE/A996 MISSING ON=DATA_CONTENT;
MATCH REC_TYPE
ON MATCH INCLUDE
ON NOMATCH INCLUDE
GOTO TOP
ENDCASE

CASE AT START
START &STARTAT
STOP &STOPAT
STOP DBMSERRORS &DBMSERROR

LOG DBMSERR MSG OFF
LOG DUPL MSG OFF
LOG INVALID MSG OFF
LOG NOMATCH MSG OFF
LOG FORMAT MSG OFF
LOG ACCEPT MSG OFF
LOG TRANS MSG OFF
CHECK 1000
SQL SET ERRORTYPE FOCUS
ENDCASE
DATA ON SYN_CGW/AA_TEST_FLATFILE
END
-RUN

-TYPE (ICM18744) Ending Load
-SET &&CM__RETURN = &FOCERRNUM;
-SET &&CM__RETURN = IF (&&CM__RETURN EQ 1416) AND (&DBMSERR LT &DBMSERROR)
- THEN 0 ELSE &&CM__RETURN;

-:ENDJOB
-TYPE (ICM18040) Return Code = &&CM__RETURN

SET CASESTAT=OFF

SET EMGSRV=ON
SQL DB2
COMMIT WORK;
END
-RUN

-TYPE (ICM18076) Request: &FOCFEXNAME - finished processing

-SET &&CM__FOCCPU = &FOCCPU.EVAL - &&CM__FOCCPU;
-TYPE (ICM18007) CPU Time : &&CM__FOCCPU

-*[Main Condition]
-*[Main End]

-*[Dependence]
-:ENDDEP
SET PANEL=0
SET MORE=ON
SET 2PARTNAME=OFF
-RUN


The script to create the DB2 table is:
CREATE TABLE "GWSDDBA5"."GWTBS49" (
"REC_TYPE" CHAR(4) FOR SBCS DATA WITH DEFAULT NULL,
"REC_LINE" VARCHAR(996) FOR SBCS DATA WITH DEFAULT NULL
)
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
WITH RESTRICT ON DROP;


When I run the flow I don't get any errors. The result is the following:

REC_TYPE REC_LINE
1234 789
...
11 empty lines
...
2345 890
...
11 empty lines
...
3456 901
...
11 empty lines
...
4567 012
...
11 empty lines
...


The empty lines are not really relevant for my problem because they don't occur in my actual problem. However as you can see in each line I am missing the first 2 characters that should be in the 2nd field. I don't understand what's happening here.

Can anybody help me out here?

Etienne

This message has been edited. Last edited by: FP Mod Chuck,

Etienne

First of all welcome to Focal Point, it is a great place to get answers to your development questions....

I created your input file on my windows environment and the CODEPAGE=37 causes a bunch of weird characters. I am wondering why you are using that codepage.

I also noticed in the MODIFY code the COMPUTE REC_LINE/A996 MISSING ON=DATA_CONTENT; and wondered why it is A996 and not A6 like the input file. Does the target DB2 master file have it defined as A996?

This message has been edited. Last edited by: FP Mod Chuck,

@Chuck I am afraid I cannot easily change the DB2 table to further simplify the situation. It can take days to do that. However when you want to investigate the situation you can use VARCHAR(6) instead of VARCHAR(996). I expect all the empty lines will be gone in that case.

The actual situation is an input file with 1000 character line length and the master file for the DB2 table has as mentioned a CHAR(4) REC_TYPE field and a VARCHAR(996) REC_LINE field. The field lengths in both master files (flat file + DB2) and the actual DB2 table do match in the actual situation.

Here is the master file for the DB2 target (actual situation):
(text view)
FILENAME=GWTBS49, SUFFIX=DB2 , $
SEGMENT=GWTBS49, SEGTYPE=S0, $
FIELDNAME=REC_TYPE, ALIAS=REC_TYPE, USAGE=A4, ACTUAL=A4,
MISSING=ON, $
FIELDNAME=REC_LINE, ALIAS=REC_LINE, USAGE=A996, ACTUAL=A996,
MISSING=ON, $

(access file tekst view)
SEGNAME=GWTBS49,
TABLENAME=GWSDDBA5.GWTBS49,
CONNECTION=---I MASKED THE CONNECTION---, $


Like I said, replace all A(996) definition by A(6) and you should be able to reproduce my problem perfectly.

I just did the following experiment:

Map REC_TYPE from the flat file to the REC_LINE in the DB2 table and Substr(REC_LINE, 1, 4) from the flat file to REC_TYPE in the DB2 table.

Result:
REC_LINE from the flat file does not loose any leading characters.
REC_TYPE from the flat file does loose the leading to characters.

Weird.

I think I read somewhere something about VARCHAR needing a length before the string. Could that have something to do with this behaviour? If so how?

I doubt you'll be able to get a solution to this issue here. There are too many moving parts. One test I'd run is to see what happens if you created a test flow that loads the Fixed file into a brand new table to see how it'll treat the records and compare the target master file's field formats with the existing DB2 table field formats.

Have you opened a case with IB Tech support?

Etienne

I agree with Babak that you will need to open a case with techsupport. Neither your source or target master files are defining varchar fields otherwise you would see a V at the end of the actual and usage (A996V) so I don't think it is a varchar issue.

Thanks Babak and Chuck. I will open a case with Tech Support later today.

For those who are interested. The cause is the mismatch between the masterfile and the DB2 table.

If the DB2 table defines a VARCHAR(996) for a field then the masterfile must define A996V for that same field. Choosing A996 in the masterfile causes the loss of the 2 characters.