DSXchange

Posted: **Fri Apr 18, 2014 4:34 pm**

Im working on a project for an insurance firm where they want to use Datastage to generate a Multi Format Flat File . Currently this is being done programatically on a Mainframe platform [ using COBOL of course

]
but they want to now source the data from an Open systems Oracle DB and create the same file using Datastage [ Server Edition ] .

The File has 7 records types in the data payload itself , and has 3 types of header records [ 1 per file] and one trailer record. so totally 11 record types. As if this was not enough there are dependencies between the 7 data record types between policy and agentr ecord for example and party and party address record etc - based on policy number and other link key columsn . I knwo with workarounds - we can do it in Datastage Server edition - although it would be quite a pain to maintain, but is there a good justification of pushing this requirement out of Datastage to some other programmatic language for this requirement ?

Im not quite convinced that using Datastage is the best way to satisfy this requirement. Here is the looping diagram that describes the file & record structure itself

Code: Select all

	10 - Submitting Header - Mandatory - 1 - occurrence required for each file.																						
			12 - Contra Header - Mandatory - 1 - occurrence for each contra party sent.																				
				[+]	13/Seq01 - Contract Record - Mandatory - (99,999,999  occurrences per Contra Header, at least one is required.)																		
							13/Seq02 - Contract Valuation Record - Optional - (20 occurrences per Contract Record)																
						[+]	13/Seq03 - Contract Underlying Assets Record - Optional - (999 occurrences per Contract Record)																
									13/Seq04 - Contract Band/Guaranteed Loop with Underlying Assets Record - Optional -														
									(999 occurrences per Contract Underlying Assets Record)  (If 13/04 is present, then 13/03 is required)														
						[+]	13/Seq05 - Contract Agent Record - Optional - (25 occurrences per Contract Record)																
							13/Seq06 - Contract Dates Record - Optional - (1 occurrence per Contract Record)																
						[+]	13/Seq07 - Contract Events Record - Optional - (2 occurrences per Contract Record)																
						[+]	13/Seq09 - Contract Party Record - Optional - (99 occurrences per Contract Record)																
									13/Seq10 - Contract Party Address Record - Optional - (1 occurrence per Contract Party Record)														
								[+]	13/Seq11 - Contract Annuitization Payout Record - Optional - (10 occurrence per Contract Party Record)														
						[+]	13/Seq15- Service Feature Record - Optional - (99 occurrences per Contract Record)																
																							
																							
																							
																							
																							
																							
																							
																							
																							
																							
																							
(1)		(2)		(3)		(4)		(5)		(6)

Posted: **Fri Apr 18, 2014 6:10 pm**

Try using Complex Flat File stage as your target.

Posted: **Mon Apr 21, 2014 1:04 pm**

I could have tried that , but the group for which im doing this activity does not have Parallel jobs licensed . Does the Server CFF stage now support writes ? I mean we are with DS 8.7 Server edition.

Regards
Ramesh

Posted: **Mon Apr 21, 2014 2:22 pm**

Good question. I'll need to check that.

Posted: **Wed Apr 23, 2014 3:27 pm**

Is there any justification to keep this requirement out of datastage ? Does datastage Server edition handle itself well to generating such complex files ?

I would think using JAVA or PERL etc makes more sense. In fact I would prefer they continue this using the COBOL program on the mainframe.

Posted: **Wed Apr 23, 2014 7:51 pm**

I have checked (in version 8.7) and verified that, indeed, it is NOT possible to use a Complex Flat File stage as a target in a server job.

Posted: **Wed Apr 23, 2014 8:33 pm**

The 9.1 information center says the same thing for server jobs--CFF stage does not support reference lookup capability or input links.

Posted: **Wed Apr 23, 2014 8:40 pm**

Thanks . Although we implemented a simpler version of this format in Datastage by combining record types and sorting by policy number, recordType+internalLoopKey[ artificially created ina transformer stage var] and some stable sorts, it seems way too much of a PITA to do in DS Server edition . We have another , slightly more complex requirement that we will ask our Solution architect to look elsewhere
, Me thinks Java can write COBOL style files . Maybe we will have a hybrid solution for datastage to generate the base record types and put them all in one file - using catty 'cat ', and let Java do the sequencing/sorting based on the cobol file descriptor .

Posted: **Thu Apr 24, 2014 6:32 am**

I've done something very much like this in Server many moons ago. It also involved an internally generated sequencing value to help group things (perhaps even two? inner and outer?) and then a custom Sort collating sequence to get the internal records per group in the correct order.

Perhaps that last bit might help rather than worrying about a stable sort. [shrug]

Posted: **Tue Apr 29, 2014 5:45 pm**

Is this possible using Websphere TX ? There is another group that has license for WTX ? Do Type trees allow defining such aa structure. The WTX pack for acord would have ready type trees for these files.

I understand that WTX can write to xml , but what about such hierarchical flat files?

DSXchange

Creating Multiple Format Flat File

Creating Multiple Format Flat File