Header Trailer record creation for empty source

[dslearner]

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?

[ArndW]

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.

[ray.wurlod]

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.

[ray.wurlod]

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.

Warning!

CLOSE goes with OPEN and OPENPATH

CLOSESEQ goes with OPENSEQ (and releases the update lock)

[ArndW]

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.

[richdhan]

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

[ashwin2912]

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.

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