What I am trying to achieve is to merge the records with same keys in a single line.

Keys technically would be 1 2 3 4 5 etc. They can continue to grow upto million. Across different pages, keys column be different column. Basically, this is DSNTEP2/DSNTEP4 output from querying 30-40 DB2 tables.

I would rather do simple unload (with any utility you have at site) and then build a SORT Card which does add the Header and the frame/border around the data values.

The issue with simple unload is that there is limitation on size of query - when using DSNTIAUL. Although the source code is available for DSNTIAUL, on talking with DBAs, it would be next to impossible to change the code. also tried to create a view and do view unload. That is impacting CPU time a lot. The query is very complex - queries 20-30 db2 tables in 1 query and uses TABLE , case when clause etc. with DSNTEP2 the query runs in less than 10 min and uses very less CPU. Hence the need for post processor module that does formatting of the file..

Talk to your site support to see if you can modify the source code "SDSNSAMP(DSNTEP2)" to fit to what you want.

What limitations are you talking about ? please elaborate further? Is it a onetime report or everyday run? are you dealing with BLOBF, CLOBF or DBCLOBF ? 32KB is huge as record length so what is that makes it occupy the whole limit of DSNTIAUL? how many columns in a query you have?

I would rather do simple unload (with any utility you have at site) and then build a SORT Card which does add the Header and the frame/border around the data values.

The issue with simple unload is that there is limitation on size of query - when using DSNTIAUL

Then use a real utility. Check to see if you have IBM's or BMC's utility program installed.

Not sure if above comment was for me, if yes we have dsntep2 and dsntiaul. As I mentioned earlier DSNTIAUL can handle only 32676 bytes, and have explored to see if we can change the assembler module.i would like to know if I can reformat spufi files using sort.

I would rather do simple unload (with any utility you have at site) and then build a SORT Card which does add the Header and the frame/border around the data values.

The issue with simple unload is that there is limitation on size of query - when using DSNTIAUL

Then use a real utility. Check to see if you have IBM's or BMC's utility program installed.

Not sure if above comment was for me, if yes we have dsntep2 and dsntiaul.

Those are not -- repeat, not -- actual utilities, although they are too often used as though they were.

Here is a link to the IBM documentation on the honest-to-חשּׁם DB2 utilities. Here is a link describing what BMC utilities
and documentation are available.

Your DBAs, if they are worth their salt -- which, I concede, is often not the case -- will which of these, or perhaps some product, you have installed on your system. RTFM, and work with them to produce your job.

If you have two files, with keys that match one-for-one, it is a simple JOINKEYS.

Use OMIT in the JNFnCNTL files for the join to get rid of superfluous lines (if there are some you can't get, you can OMIT them later in the main task). Then, in the same place, you'll have to "normalise" the position of the keys (the JOINKEYS itself needs fixed start/length for the join). Simple with PARSE and ENDBEFR="_" and a second field with the rest of the data, from your examples, then BUILD with the two PARSEd fields.

You mention SYNCSRT in your topic title but post in the DFSORT forum. ICETOOL is related to DFSORT but not to SYNCSORT (exept as an alias to SYNCTOOL). Which sort product do you mean? The solution [i]could[/b] vary depending on the product.

You mention SYNCSRT in your topic title but post in the DFSORT forum. ICETOOL is related to DFSORT but not to SYNCSORT (exept as an alias to SYNCTOOL). Which sort product do you mean? The solution [i]could[/b] vary depending on the product.

Sorry for the wrong title, we do have both. I wanted to put the query on both the forums. Syncsort Mfx is what i normally use.

DSNTEP2 always provide 40 rows per page, ( atleast in our shop )..
Since the keys are actually in different positions, how about building sequence numbers.

This would mean that page 1 would have sequence number 1 to 40, page 2 would have sequence number 41-80...or something like this..

Another factor we would know is that SYSPRINT record for DSNTEP2 would be fixed at 133 bytes. This would mean that based on columns selected, number of pages displaying data for first 40 records would be same as next 40 records.

Something like this: Assume after unloading all columns, it spans over 3 pages.

If you note above, every time sequence number crosses 120 * n, the actual record changes.

So, if i can merge the records with sequence numbers 1 41 81, 2 42 82, 3 43 83 etc, that may provide me in single line, and then i may have to reformat little bit. This has to be repeated everytime sequence number crosses multiple of 120.

If you already have the key, use the key. If your matching depends on what someone sepcifies for lines on a page, when it doesn't need to, then it's asking for trouble.

Thee are lots of JOINKEYS examples here. Your JOINKEYS itself is very simple. You have two ancialliary issues which are 1) dross you don't want (headings) 2) keys in different starting-position.

On your sample, the key is very simple - the stuff just before the "_", then the data is the stuff that follows. Very simple thing for PARSE to "normalise" that, so not only you get the key in one location, you get the data in one location, rather than it just being a lump of stuff for, presumably. something else to rarrange later.