|
View previous topic :: View next topic
|
| Author |
Message |
Anil Minumula
New User
Joined: 24 Feb 2009
Posts: 12
Location: Frankfurt
|
|
|
|
Hi,
Our organization has a Standard of "always perform Header & Trailer Validations before processing any file in an application program".
This was also required for DB2 Unloads. So we normally unload DB2 tables, and use a program to add a Header & Trailer to the unload and make it available for the application programs to use.
So Generally when our application programs process unload files the structure of the flat file would like below.
000 31.12.2008 Creator_Name // Header
111 111 222 333 // Data Record 1
111 222 333 333 // Data Record 2
111 222 444 555 // Data Record 3
999 777 999 5 // Trailer record
Basically we make a sortable file by appending the first 3 bytes including header/Trailer.
Header generally has the creation date and creator name
Trailer generally contains the count of recs, and some variables which contain various SUM of data rec fields.
Question: Is it possible in someway to generate this Header/Trailer by the DB2 Unload utility itself?
DB2 UNLOAD has to go thru the complete file, so is there someway it can create a trailer itslef and apend before completing the task?
Thanks a lot |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
you could use UNION.
select header
union
select your garbage
union
select trailer.
and use 'literals' to build your header trailer.
but remember, to use union, each union needs to deliver the same number and types of columns.
or to relieve the complexity of union
you could have three different unloads (3 steps).
one for the header
one for the data
one for the trailer
and then concatenate the three as a fourth step.
may require running the head and trailer thru a sort to extend the record to be the same a data record length.
or
have a utility to generate the header and trailer.
you can get the counts for the trailer from the sysout of the data unload step.
that way your routine to generate the header and trailer could be easily customizable for each table that you unload. |
|
| Back to top |
|
|
Anil Minumula
New User
Joined: 24 Feb 2009
Posts: 12
Location: Frankfurt
|
|
|
|
Hi Dick,
Thanks a lot for your fast reply!
| Quote: |
but remember, to use union, each union needs to deliver the same number and types of columns.
|
This would definetely be a problem as the record formats have to be identical.
| Quote: |
you could have three different unloads (3 steps).
one for the header
one for the data
one for the trailer
and then concatenate the three as a fourth step.
may require running the head and trailer thru a sort to extend the record to be the same a data record length
|
The reason we were luking for Unload Utility to do this was, it would avoid multiple data parsing. We dont have to go thru the complete data, to create the trailer.
I was hoping DB2 Unload, which already parses complete data in the table could inturn do the trailer creation also.
If DB2 Unload utility cannot do this currently, I would like to put it in my wish list for the next version of DB2 :-)
The third option is also not possible because, the sysout only gives the count and we would also need some SUMs. Like for a Banking transaction file, the sum of all credits/Debits. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
If DB2 Unload utility cannot do this currently, I would like to put it in my wish list for the next version of DB2 :-)
|
don't hold your breath waiting for this.
you can get sums by running a second query........................
unload is for generating qsam files that can then be 'loaded'. how would you load the sums record??
you could use sort to generate the header and trailers - to contain record counts.
if you don't have the whitherwithall to have 'unloads' handled differently,
thus 'staying within your little box',
the summing could be accomplished by a sort step reading in the unload data. which is really stupid......how many times are you going to 'pass the file?'.
you have a count of the records (contained in the unload step sysout),
unloads are only data.
probably the reason to use unloaded qsam data instead of just having your application perform the necessary db2 is because 'someone' is worried about resource consumption. Having extra steps (sort passing the file to generate sums) will only increase the resource consumption. |
|
| Back to top |
|
|
|
|
