View previous topic :: View next topic
|
Author |
Message |
nartcr
New User
Joined: 06 Jun 2007 Posts: 83 Location: anada
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
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 |
|
|
nartcr
New User
Joined: 06 Jun 2007 Posts: 83 Location: anada
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
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 |
|
|
nartcr
New User
Joined: 06 Jun 2007 Posts: 83 Location: anada
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
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 |
|
|
nartcr
New User
Joined: 06 Jun 2007 Posts: 83 Location: anada
|
|
|
|
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 |
|
|
nartcr
New User
Joined: 06 Jun 2007 Posts: 83 Location: anada
|
|
|
|
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 |
|
|
nartcr
New User
Joined: 06 Jun 2007 Posts: 83 Location: anada
|
|
|
|
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 |
|
|
nartcr
New User
Joined: 06 Jun 2007 Posts: 83 Location: anada
|
|
|
|
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 |
|
|
nartcr
New User
Joined: 06 Jun 2007 Posts: 83 Location: anada
|
|
|
|
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 |
|
|
nartcr
New User
Joined: 06 Jun 2007 Posts: 83 Location: anada
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
|