DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
bcarlson



Group memberships:
Premium Members

Joined: 01 Oct 2004
Posts: 772
Location: Minnesota
Points: 7799

Post Posted: Fri Aug 10, 2007 4:10 pm Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Parallel
OS: Unix
I have used DataStage for several years now, but this is actually my first foray into the Complex File Format stage - up until now, we have simply used sequential file stages and work arounds. I am trying to see if there is a more efficient way to handle multiple file formats in a single file.


So, with that said, I am doing a simple test. I have the following file layout with a header record and detail records (the CFD below is actually generated from the CFF stage):

Code:
******************************************************************
      * COBOL DECLARATION FOR TABLE COMPLEX-FLAT-FILE-36
      ******************************************************************
       01  COMPLEX-FLAT-FILE-36.
        02  MCRE-HEADER.
         05  TD-RECORD-ID PIC X(3).
         05  TD-APPL-CODE PIC X(2).
         05  TD-BANK-NUMBER PIC X(6).
         05  TD-FILLER1 PIC X(5).
         05  TD-DATA-TYPE PIC X(8).
         05  TD-FILLER2 PIC X(5).
         05  TD-ENTRY-DATE PIC X(6).
         05  TD-ACTION-CODE PIC X(1).
         05  TD-EXTRACT-DATE PIC X(6).
         05  TD-SOURCE PIC X(4).
         05  TD-PACKET-ID PIC X(8).
         05  TD-FILLER3 PIC X(18).
         05  TD-CREATE-APPL PIC X(2).
         05  TD-FILLER4 PIC X(4).
         05  TD-FORMAT PIC X(1).
         05  TD-FILLER5 PIC X(26).
        02  MCRE-DATA
          REDEFINES MCRE-HEADER.
         05  MCRE-SITE-CODE PIC X(1).
         05  MCRE-BANK-GROUP PIC X(3).
         05  MCRE-CYCLE PIC X(1).
         05  MCRE-CYCLE-DATE PIC X(8).
         05  MCRE-ENDPOINT PIC X(8).
         05  MCRE-STRING-NAME PIC X(24).
         05  MCRE-CASH-LETTER-TIMESTAMP PIC X(8).
         05  MCRE-ROUTING-TRANSIT PIC X(8).
         05  MCRE-AMT-X PIC X(10).
         05  MCRE-SEQ-FILL PIC X(2).
         05  MCRE-BANK-NUMBER PIC X(6).
         05  MCRE-SEQ12 PIC X(12).
         05  MCRE-RECORD-SEQ PIC X(9).
         05  MCRE-CRD-DBT-FLAG PIC X(1).
         05  MCRE-ONUS-TRNST-FLAG PIC X(1).
         05  MCRE-SORT-PATTERN PIC X(3).
      ******************************************************************
      * THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 34
      ******************************************************************


I have heard rumors that the CFF stage can do redefines. In fact, I have defined this layout already. I would like the header record to go to one dataset and the detail records to go to another. How do I do this?


Again, I am on DataStage PX 7.5.2.

Brad.
bcarlson



Group memberships:
Premium Members

Joined: 01 Oct 2004
Posts: 772
Location: Minnesota
Points: 7799

Post Posted: Fri Mar 07, 2008 11:41 am Reply with quote    Back to top    

Okay, I never got this example working back then and let it sit until now. Still can't get it working... at least not the way I want it to.

The job is simple. The CFF uses the file layout from above and has 3 outputs: reject, header and data (write directly to datasets).

The file imports and outputs to the datasets just fine. I get 0 records in the rejects (expected), but get all records in BOTH the header and data datasets.

How does the CFF know which record to send to which link? I would have expected all but 1 record to go the data link, and 1 record to go to the header link.

Any suggestions?

Brad.
Rate this response:  
Not yet rated
bcarlson



Group memberships:
Premium Members

Joined: 01 Oct 2004
Posts: 772
Location: Minnesota
Points: 7799

Post Posted: Fri Mar 07, 2008 11:42 am Reply with quote    Back to top    

One other note. When I look at the schemas for the data and header datasets, they do have the correct layouts. The header just has all records in it...

Brad.
Rate this response:  
Not yet rated
ArndW

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16318
Location: Germany
Points: 92566

Post Posted: Fri Mar 07, 2008 12:00 pm Reply with quote    Back to top    

I'm not a site where I can test this, but recall that you need a record-id column that is used, in a manner similar to a switch stage, to identify a column position that differentiates the different r ...

_________________

Image
Rate this response:  
Not yet rated
bcarlson



Group memberships:
Premium Members

Joined: 01 Oct 2004
Posts: 772
Location: Minnesota
Points: 7799

Post Posted: Fri Mar 07, 2008 12:05 pm Reply with quote    Back to top    

Thanks. I'll look into it. Man, I bet that means I have to look at documentation, doesn't it? Are men allowed to read directions? Smile

Brad.
Rate this response:  
Not yet rated
ArndW

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16318
Location: Germany
Points: 92566

Post Posted: Fri Mar 07, 2008 12:25 pm Reply with quote    Back to top    

Sure, many of us occasionally read the manuals but make sure we are not watched or disturbed in the process. I will usually only admit under duress that I even own a manual, and only the utmost coerci ...

_________________

Image
Rate this response:  
Not yet rated
Aruna Gutti



Group memberships:
Premium Members

Joined: 21 Sep 2007
Posts: 145
Location: Boston
Points: 989

Post Posted: Fri Mar 07, 2008 1:12 pm Reply with quote    Back to top    

If I recall correctly, we used @INROWNUM=0 constraint in the Header Output link. But not able to recall whether it is on a Server Job or parallel. I don't have access to 7.5.2 right now.

Also on the Header file input link selected 'first row column names' option.
Rate this response:  
Not yet rated
bcarlson



Group memberships:
Premium Members

Joined: 01 Oct 2004
Posts: 772
Location: Minnesota
Points: 7799

Post Posted: Fri Mar 07, 2008 2:48 pm Reply with quote    Back to top    

Will the column layout in CFF allow us to define default values for a column or a null field value? If not, how have others handled this situation?

Brad.
Rate this response:  
Not yet rated
flashgordon



Group memberships:
Premium Members

Joined: 17 Aug 2004
Posts: 99
Location: Boulder, Colorado
Points: 1188

Post Posted: Fri Mar 07, 2008 7:24 pm Reply with quote    Back to top    

Hi,

arndt is a stud so I'll defer to him. But I will say in our organization that if you have completely different record formats (cff stage can definitely handle small redefines) in the input (an a in column 1 means header record - format x, a b in column 1 means detail - format y) we split them out and process each separately. cff stage is a technical marvel, but one transformer the other side of cff stage handling two completely different input record format seems very confusing. Think about how you would do this with ascii rows, each row would have a completely different format.

... Flash

_________________
Flash Gordon
Hyperborean Software Solution
Rate this response:  
flashgordon



Group memberships:
Premium Members

Joined: 17 Aug 2004
Posts: 99
Location: Boulder, Colorado
Points: 1188

Post Posted: Fri Mar 07, 2008 7:25 pm Reply with quote    Back to top    

Hi,

arndt is a stud so I'll defer to him. But I will say in our organization that if you have completely different record formats (cff stage can definitely handle small redefines) in the input (an a in column 1 means header record - format x, a b in column 1 means detail - format y) we split them out and process each separately. cff stage is a technical marvel, but one transformer the other side of cff stage handling two completely different input record format seems very confusing. Think about how you would do this with ascii rows, each row would have a completely different format.

... Flash

_________________
Flash Gordon
Hyperborean Software Solution
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours