Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE OPENED] Fixed File: Loosing characters when reading lines with Data Migrator
Go
New
Search
Notify
Tools
Reply
  
[CASE OPENED] Fixed File: Loosing characters when reading lines with Data Migrator
 Login/Join
 
Member
posted
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 Big GrinEP_MAIN;

-Big GrinEP_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,


WF 8.2
DMC
Gen Number: 833
Release: 81M 32bit
 
Posts: 10 | Registered: September 21, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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,


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1571 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Member
posted Hide Post
@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.


WF 8.2
DMC
Gen Number: 833
Release: 81M 32bit
 
Posts: 10 | Registered: September 21, 2018Reply With QuoteReport This Post
Member
posted Hide Post
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?


WF 8.2
DMC
Gen Number: 833
Release: 81M 32bit
 
Posts: 10 | Registered: September 21, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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?


WebFOCUS 8206, Unix, Windows
 
Posts: 1660 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1571 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Member
posted Hide Post
Thanks Babak and Chuck. I will open a case with Tech Support later today.


WF 8.2
DMC
Gen Number: 833
Release: 81M 32bit
 
Posts: 10 | Registered: September 21, 2018Reply With QuoteReport This Post
Member
posted Hide Post
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.


WF 8.2
DMC
Gen Number: 833
Release: 81M 32bit
 
Posts: 10 | Registered: September 21, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE OPENED] Fixed File: Loosing characters when reading lines with Data Migrator

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.