Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Db2 Unload with Header/Trailer

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Anil Minumula

New User


Joined: 24 Feb 2009
Posts: 12
Location: Frankfurt

PostPosted: Wed Jul 01, 2009 9:04 pm    Post subject: Db2 Unload with Header/Trailer
Reply with quote

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
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jul 01, 2009 9:33 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Anil Minumula

New User


Joined: 24 Feb 2009
Posts: 12
Location: Frankfurt

PostPosted: Wed Jul 01, 2009 10:14 pm    Post subject: Reply to: Db2 Unload with Header/Trailer
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jul 01, 2009 10:54 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts DB2 Unload format options Susanta DB2 2 Fri Aug 12, 2016 5:42 pm
No new posts DXT unload utility - DVRE0000 mistah kurtz DB2 1 Thu Jul 07, 2016 4:56 pm
No new posts How to suppress retun code of partil ... mkk157 DB2 9 Fri May 27, 2016 6:59 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us