Hi,
I've a requirement wherein the output is as follows:
i)
Header Record
Detail Records
Trailer Record (contains record count of Detail Records)
Source is DB2 and target is sequential file
However, when no records are read from the DB2 table, requirement is to output:
ii)
Header Record
Trailer Record (to output record count = 0 in this case)
I'm trying to figue out how (ii) can be achieved, i.e. creation of a Header and Trailer from PX only when no records are read from DB2. Anyone faced similar scenarios?
Header Trailer record creation for empty source
Moderators: chulett, rschirm, roy
I think that you might best solve your issue by using a before and an after job subroutine.
The before-job subroutine can use the OPENSEQ, WRITESEQ and CLOSE commands to write one or more header record(s) to the file while truncating it. The job itself writes 0 or {n} data records and then the after-job routine can use the DSGetLinkInfo() to retrieve the number of records that went down the main link to put together the footer record that can be written with OPENSEQ, WRITESEQ and CLOSE.
The before-job subroutine can use the OPENSEQ, WRITESEQ and CLOSE commands to write one or more header record(s) to the file while truncating it. The job itself writes 0 or {n} data records and then the after-job routine can use the DSGetLinkInfo() to retrieve the number of records that went down the main link to put together the footer record that can be written with OPENSEQ, WRITESEQ and CLOSE.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Indeed Arnd's suggestion is the only sensible one, because only the job itself exists once. If you tried to create a header/trailer in a section leader or player process, you'd get one header per partition.
However, you don't need OpenSeq and its companions. It may be enough to invoke an operating system command such as echo header > #filename# and echo trailer >> #filename# using ExecSH as the before/after subroutine.
However, you don't need OpenSeq and its companions. It may be enough to invoke an operating system command such as echo header > #filename# and echo trailer >> #filename# using ExecSH as the before/after subroutine.
Last edited by ray.wurlod on Tue Aug 23, 2005 4:17 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Warning!ArndW wrote:I think that you might best solve your issue by using a before and an after job subroutine.
The before-job subroutine can use the OPENSEQ, WRITESEQ and CLOSE commands to write one or more header record(s) to the file while truncating it. The job itself writes 0 or {n} data records and then the after-job routine can use the DSGetLinkInfo() to retrieve the number of records that went down the main link to put together the footer record that can be written with OPENSEQ, WRITESEQ and CLOSE.
CLOSE goes with OPEN and OPENPATH
CLOSESEQ goes with OPENSEQ (and releases the update lock)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Oopsie; it is "CLOSESEQ". Fortunately jobs close all open file units when they finish, but it is always good programming practice to explicitly close open files instead of letting an implicit close do it for you. Thanks.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Hi DSLearner,
1. Create a dummy row with columns for Header and Trailer using a row generator.
2. Use the DB stage to get the detail records.
3. Use a Transformer with 2 output links, one to pass the records and other to an aggregator to find the count.
4. Use a lookup stage to pass the record count to the dummy row from row generator
5. Pass the dummy row through a transformer to get Header and Trailer (with record count)
6. Use the column export stages(3) to get the same structure for Header, Detail and Trailer
7. Pass the outputs from the column export stages to a Funnel to get the desired output.
HTH
--Rich
1. Create a dummy row with columns for Header and Trailer using a row generator.
2. Use the DB stage to get the detail records.
3. Use a Transformer with 2 output links, one to pass the records and other to an aggregator to find the count.
4. Use a lookup stage to pass the record count to the dummy row from row generator
5. Pass the dummy row through a transformer to get Header and Trailer (with record count)
6. Use the column export stages(3) to get the same structure for Header, Detail and Trailer
7. Pass the outputs from the column export stages to a Funnel to get the desired output.
HTH
--Rich
-
- Participant
- Posts: 57
- Joined: Wed Jan 26, 2005 10:22 pm
- Location: India
Good idea Rich...
Just to simplify...
1. Add a dummy row (you can use a row gen & funnel stage) with a column COUNT=0
2. The detail recs should have an extra column COUNT=1
3. In an aggregator SUM(COUNT) and get the count to be put in your trailer.
Just to simplify...
1. Add a dummy row (you can use a row gen & funnel stage) with a column COUNT=0
2. The detail recs should have an extra column COUNT=1
3. In an aggregator SUM(COUNT) and get the count to be put in your trailer.
richdhan wrote:Hi DSLearner,
1. Create a dummy row with columns for Header and Trailer using a row generator.
2. Use the DB stage to get the detail records.
3. Use a Transformer with 2 output links, one to pass the records and other to an aggregator to find the count.
4. Use a lookup stage to pass the record count to the dummy row from row generator
5. Pass the dummy row through a transformer to get Header and Trailer (with record count)
6. Use the column export stages(3) to get the same structure for Header, Detail and Trailer
7. Pass the outputs from the column export stages to a Funnel to get the desired output.
HTH
--Rich
Ashwin