create an extract

[patrickmn10]

Hi,

I need a help in creating extract from the database. the problem is for each value i need to create trailer and header in the same file. for eg :

EMP EMPNAME SAL
111 PAT 100
111 ABC 100
222 JON 200
222 JON1 200
333 JJJJ 300
333 JJJJ 300

I need to create an extract with the following from the above table.
HDR 111
DTL 111 PAT 100
TRL 111 2 200 (HERE 2 is record count (111,111) and (200 is sal sum).
HDR 222
DTL 222 PAT 200
TRL 222 2 400

Please let me know how to create hdr and trailer for ever empno change in a loop. Appreciate any help.

Thanks
Patrick

[deployDS]

Do you need help with the sql for extraction
or with a job to do this after extraction?

[chulett]

Well... I've done this in the past, but not recently so bear with me.

An aggregator will get you the pieces that you need for the header and trailer records per grouping. Your example of the extract output seems a little odd to me, shouldn't it more properly be:

Code: Select all

HDR 111 
DTL 111 PAT 100 
DTL 111 ABC 100
TRL 111 2 200
HDR 222 
DTL 222 JON 200 
DTL 222 JON1 200
TRL 222 2 400 
HDR 333
DTL 333 JJJ 300
DTL 333 JJJ 300
TRL 333 2 600

Or perhaps the last DTL record should be a sum, that's something you can handle if need be.

I wrote out each 'set' of records to three flat files - one for all headers, one for all details, etc. They were then concatenated together and a Sort stage using a custom collating map put them into the correct order.

How? Each record was 'keyed' by type as the first field and then each complete record was just there as a second string field. In this case, if you did something like H<emp> for all headers, D<emp> for all detail and T<emp> for all trailers, then tell the sort stage to sort H then D then T. Or make it a little more obtuse but easier to sort by using A,B,C. After sorting, the key is simply pitched before writing out the final file.

That should get you on your way, unless someone has a better way. Be happy to hear it! :D

[patrickmn10]

Hi,

thanks for replying, this is what i was looking for. Can you give little more details , my requirement is same as you mentioned below. Instead of creating 3 seq files, Is there any way we can create in one file. Give more details please.

HDR 111
DTL 111 PAT 100
DTL 111 ABC 100
TRL 111 2 200
HDR 222
DTL 222 JON 200
DTL 222 JON1 200
TRL 222 2 400
HDR 333
DTL 333 JJJ 300
DTL 333 JJJ 300
TRL 333 2 600


Thanks
Patrick

[chulett]

What 'little more details'? If you have specific questions, ask.

And why the concern about the three files? You create the three to create the one, I don't see any way to create that kind of output directly. However, if you are worried about 'the three files' ( why does that remind me of the 'Demolition Man' movie? ) replace them with a properly keyed hashed file. Write to it and then use a Universe stage over the top of it so you can use SQL to pull the results out. Or use a database.

Easier to deal with the three 'work' files, in my opinion.

[patrickmn10]

Thanks for your suggestion

What 'little more details'? If you have specific questions, ask.

And why the concern about the three files? You create the three to create the one, I don't see any way to create that kind of output directly. However, if you are worried about 'the three files' ( why does that remind me of the 'Demolition Man' movie? ) replace them with a properly keyed hashed file. Write to it and then use a Universe stage over the top of it so you can use SQL to pull the results out. Or use a database.

Easier to deal with the three 'work' files, in my opinion.

[kumar_s]

If you dont wish to land three files, you need to compromise with number of stages. User transformer to split the recoreds in 3 links, user another transformer for each link to restructure, user link collector to merge all the three. Then user the sort with the sort order you per-specified.

[patrickmn10]

Kumar.

thanks kumar for sharing the idea. It gives me an error when use the Link collector . It says "Link collector stage does not support in-process active-to-active inputs or outputs". I did use like what u suggested, like from Transfer created 3 links (hdr,trailer,detail) sort on key aggregated and used 3 transfers and then link collector(from 3 transfmers to link collector it is not accepting to merge, it gives above mentioned error message). Any help appreicated.

Thanks
Patrick

[kris007]

GoTo JobProperties>>Performance and then select Enable row buffer and select Interprocess. Your compilation error should dissappear.

[DSguru2B]

You can also stick an IPC stage in between the transformer and the link collector stage to get rid of that error.
This error occurs because both transformer and link collector are active stages and DataStage will not allow a direct link between them unless you have an IPC stage in between or have Interprocess buffering enabled.

[patrickmn10]

Hi,

Thanks everybody, it worked. Thanks again.

Patrick

You can also stick an IPC stage in between the transformer and the link collector stage to get rid of that error.
This error occurs because both transformer and link collector are active stages and DataStage will not allow a direct link between them unless you have an IPC stage in between or have Interprocess buffering enabled.