Creating Multiple Format Flat File

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Creating Multiple Format Flat File

Post by rameshrr3 »

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 :P ]
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)													
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Try using Complex Flat File stage as your target.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Post by rameshrr3 »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Good question. I'll need to check that.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Post by rameshrr3 »

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.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

The 9.1 information center says the same thing for server jobs--CFF stage does not support reference lookup capability or input links.
Choose a job you love, and you will never have to work a day in your life. - Confucius
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Post by rameshrr3 »

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 .
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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]
-craig

"You can never have too many knives" -- Logan Nine Fingers
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Post by rameshrr3 »

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?
Post Reply