IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

ICETOOL/SYNCSORT - SYSPRINT output


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
nartcr

New User


Joined: 06 Jun 2007
Posts: 83
Location: anada

PostPosted: Wed Oct 05, 2016 10:46 pm
Reply with quote

I have an input file with length 133. Basically output from SPUFI. I have a specific requirement to run it through SORT program,

input

Code:

PAGE   17
       -------------------------------------------------------------------------
       | ABCDE_ABCD_WARNING_FLAG | ABCD_CATEGORY | ABCDEFGHIJ_TYPE | ABC123 | AU
       -------------------------------------------------------------------------
     1_| N                       | COLLABCDE     | ABCDE           | N      | Y
     2_| N                       | COLLABCDE     | ABCDE           | N      | Y
     3_| N                       | COLLABCDE     | ABCDE           | N      | Y
     4_| N                       | COMABCDEABCDE | ABCDE           | Y      | Y
     5_| N                       | COLLABCDE     | ABCDE           | N      | N



Code:

PAGE   20
                  --------------------------------------------------------------
                  | PAYMENT_DATE | BUSINESS_FACILITY_ID |            BUSINESS_FA
                  --------------------------------------------------------------
                1_| 2013-11-28   |         123123       | B.E.1. ABCDESION
                2_| 2013-11-28   |          12312       | SDDRCAN ABCDEDION CENT
                3_| 2013-12-12   |         123123       | TEST TEST TEST SALES L
                4_| 2013-12-12   |         123123       | TEST'S TEST1 & TESTTES
                5_| 2013-12-12   |         123123       | NEW TECHNOLOGY



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.

Sample output for 1 record would be:

Code:
   1_| N                       | COLLABCDE     | ABCDE           | N      | Y| 2013-11-28   |         123123       | B.E.1. ABCDESION


Have anyone tried to create output like this before?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Oct 05, 2016 10:58 pm
Reply with quote

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.
Back to top
View user's profile Send private message
nartcr

New User


Joined: 06 Jun 2007
Posts: 83
Location: anada

PostPosted: Wed Oct 05, 2016 11:11 pm
Reply with quote

Rohit Umarjikar wrote:
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..
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Thu Oct 06, 2016 12:08 am
Reply with quote

nartcr wrote:
Rohit Umarjikar wrote:
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.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Oct 06, 2016 12:20 am
Reply with quote

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?

Hint- ibmmainframes.com/about51950.html
Back to top
View user's profile Send private message
nartcr

New User


Joined: 06 Jun 2007
Posts: 83
Location: anada

PostPosted: Thu Oct 06, 2016 12:20 am
Reply with quote

Akatsukami wrote:
nartcr wrote:
Rohit Umarjikar wrote:
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.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Thu Oct 06, 2016 12:53 am
Reply with quote

nartcr wrote:
Akatsukami wrote:
nartcr wrote:
Rohit Umarjikar wrote:
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.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Oct 06, 2016 1:08 am
Reply with quote

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.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Oct 06, 2016 2:05 am
Reply with quote

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.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Oct 06, 2016 2:07 am
Reply with quote

Bill, Not to offense,
Quote:
If you have two files, with keys that match one-for-one, it is a simple JOINKEYS.
They are recursive up to millions(Cartesian). Just to be considered.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Oct 06, 2016 3:00 am
Reply with quote

Quote:
They are recursive up to millions(Cartesian). Just to be considered.


NOPE ...
the key is 1,2,3,4
so it a good plain joinkey one to one

the problem is the reformatting and splitting that has to be done before the joins ...

split the output to have one <report> in each file, and then run number_of_files - 1
sort step do do an incremental joinkey.

I wonder about the IQ of the PHA ( Pointed Head Analyst ) who wrote the requirement
Back to top
View user's profile Send private message
nartcr

New User


Joined: 06 Jun 2007
Posts: 83
Location: anada

PostPosted: Thu Oct 06, 2016 10:32 am
Reply with quote

No Blob Clob etc. I do have DSNTEP2/DSNTAIUL source code, however there is some restriction in modifying the source - assembler/PL1, that would prevent us from binding anything to prod DB2 schema.

More than 40 columns, however, these columns are derived from 20+ tables, depending on case when/ union/ and other cases.

I looked in IBM documentation, apparently modifying DSNTIAUL would be easier than modifying DSNTEP2.
Back to top
View user's profile Send private message
nartcr

New User


Joined: 06 Jun 2007
Posts: 83
Location: anada

PostPosted: Thu Oct 06, 2016 10:36 am
Reply with quote

I have used UNLOAD utilities, and unloaded tablespaces. However, the way the query is written in our shop, uses multiple tables, and adhoc table features. We do not have BMC in our shop.
Back to top
View user's profile Send private message
nartcr

New User


Joined: 06 Jun 2007
Posts: 83
Location: anada

PostPosted: Thu Oct 06, 2016 10:39 am
Reply with quote

Nic Clouston wrote:
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.
Back to top
View user's profile Send private message
nartcr

New User


Joined: 06 Jun 2007
Posts: 83
Location: anada

PostPosted: Thu Oct 06, 2016 10:41 am
Reply with quote

Would you provide me the high level approach, on how you would attack this with Sort? I would be willing to spend some time on long term to come with some solution.
Back to top
View user's profile Send private message
nartcr

New User


Joined: 06 Jun 2007
Posts: 83
Location: anada

PostPosted: Thu Oct 06, 2016 10:55 am
Reply with quote

The query is over 900 lines, and this works fine with SPUFI/DSNTEP2. But DSNTAIUL, will throw error, since its more than 32676 ( 450ish lines ) in SYSIN.
Back to top
View user's profile Send private message
nartcr

New User


Joined: 06 Jun 2007
Posts: 83
Location: anada

PostPosted: Thu Oct 06, 2016 11:14 am
Reply with quote

I was thinking an approach like this:

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.

Page 1

rec1 date1.. SEQ-000001
rec2 date2.. SEQ-000002
.
.
rec40 date40...SEQ-000040

Page 2
rec1 number1....SEQ-000041
rec2 number2....SEQ-000042
.
.
rec40 number40....SEQ-000080

Page 3
rec1 text1....SEQ-000081
rec2 text2....SEQ-000082
.
.
rec40 text40....SEQ-000120

Page 4

rec41 date41.. SEQ-000121
rec42 date42.. SEQ-000122
.
.
rec80 date80...SEQ-000160

Page 5
rec41 number41....SEQ-000161
rec42 number42....SEQ-000162
.
.
rec80 number80....SEQ-000200

Page 6
rec41 text41....SEQ-000201
rec42 text42....SEQ-000202
.
.
rec80 text80....SEQ-000240


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.

Is there some way i could achieve this, in sort?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Oct 06, 2016 12:38 pm
Reply with quote

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.

Lots of examples of PARSE here as well.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Shift left VB record without x00 endi... DFSORT/ICETOOL 11
No new posts how to calculate SUM value for VB fil... DFSORT/ICETOOL 1
No new posts how to calculate SUM for VB file usin... JCL & VSAM 1
Search our Forums:

Back to Top