MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Append variable number of lines to the correct previous one
Goto page 1, 2, 3  Next
 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Wed Apr 26, 2017 10:04 am    Post subject: Append variable number of lines to the correct previous one Reply with quote

Hello,
maybe this one can be done with JOINKEYS processing the same file twice, but I still can't get any good result. I'm reading manual trying to understand how I can do it. Meanwhile if I can get a quick support, well I'll be grateful, no problem otherwise.

Often we have some spot DB2 queries to run, sometimes these queries might be too heavy in results for QMF, so we run them via Batch using this tool
Code:

//GO1        EXEC DB2GO                     
//STEPLIB    DD DISP=SHR,DSN=DB2.XXXX.RESLIB
//SYSTSIN    DD  *                         
 DSN SYSTEM(XXXX)                           
 RUN PROGRAM(PROG2) PLAN (PLAN2) -     
     LIB ('DB2LIB.RUNLIB.LOAD')           


The output from previout tool is something like this (if resulting line is too long, it is splitted in more lines, each line can be identified by it's number, so 1_, 2_ and so on)
Code:

          +------------------------------------------------------------------
          !       AAAAAA       !        BBBBBBB        !      CCCCCCC       !
          +------------------------------------------------------------------
        1_! XX                 !             1050405   ! 000000000000893994 !
        2_! XX                 !             1054016   ! 000000000000909742 !
        3_! XX                 !             1048799   ! 000000000000991566 !
        4_! XX                 !             1048355   ! 000000000000686623 !
        5_! XX                 !             1047049   ! 000000000000860967 !
          +------------------------------------------------------------------
1PAGE    3                                                                   
           ---------------------------------------------------------------------
           !                DDDDDDDDDDDDDDDD                 ! EEEEEEEEEEEEEEE !
           ---------------------------------------------------------------------
         1_! TTTTTTTTTT TTTTTTT TTTTTTT                      ! GG.MM.AAAA      !
         2_! TTTTTTTTTT TTTTTTT TTTTTTT                      ! GG.MM.AAAA      !
         3_! TTTTTTTTTTTTTT TTTTTTTT                         ! GG.MM.AAAA      !
         4_! TTTTTTTTTTTTTT TTTTTTTT                         ! GG.MM.AAAA      !
         5_! TTTTTTTTTT TTTTTTT TTTTTTT                      ! GG.MM.AAAA      !
           ---------------------------------------------------------------------
1PAGE    4                                                                     
          +------------------------------------------------------------------
          !       AAAAAA       !        BBBBBBB        !      CCCCCCC       !
          +------------------------------------------------------------------
        6_! XX                 !             1047096   ! 000000000000930578 !
        7_! XX                 !             1052489   ! 000000000000884280 !
...
       59_! XX                 !             1064278   ! 000000000000240283 !
          +------------------------------------------------------------------
1PAGE    5                                                                   
           ---------------------------------------------------------------------
           !                DDDDDDDDDDDDDDDD                 ! EEEEEEEEEEEEEEE !
           ---------------------------------------------------------------------
         6_! TTTTTTTTTT TTTTTTT TTTTTTT                      ! 22.09.2014      !
         7_! TTTTTTTTTT TTTTTTTTTT                           ! 22.09.2014      !
...
        59_! TTTTTTTTTT TTTTTTTTTT                           ! 07.11.2014      !
           ---------------------------------------------------------------------
1PAGE    6                                                                     
...

output is variable according to query complexity in terms of column selected, so fields are NOT always in the same position ( second 1_ might start at different column from the above example). Also usually a line is splitted in two chunks, but it could be splitted in three parts too or maybe more, we can't know in advance. Anyway we can be sure that the 2nd line 1_ need to attached to the end of the previous 1_, 2_ at the end of the previous 2_ and so on. I have also some difficult to understand how I can dinamically calculate in advance the width of the new fille I would like to create with sort, at the moment I allocate a 1000 bytes long, which I guess will be enough.

So in output we would need something like this:
Code:

+---------------------------------------------------------------------------------------------------------------------------------------
!       AAAAAA       !        BBBBBBB        !      CCCCCCC       !!                DDDDDDDDDDDDDDDD                 ! EEEEEEEEEEEEEEE !
+---------------------------------------------------------------------------------------------------------------------------------------
  XX                               1050405     000000000000893994    TTTTTTTTTT TTTTTTT TTTTTTT                        GG.MM.AAAA
  XX                               1054016     000000000000909742    TTTTTTTTTT TTTTTTT TTTTTTT                        GG.MM.AAAA
  XX                               1048799     000000000000991566    TTTTTTTTTTTTTT TTTTTTTT                           GG.MM.AAAA
  XX                               1048355     000000000000686623    TTTTTTTTTTTTTT TTTTTTTT                           GG.MM.AAAA
  XX                               1047049     000000000000860967    TTTTTTTTTT TTTTTTT TTTTTTT                        GG.MM.AAAA
  XX                               1047096     000000000000930578    TTTTTTTTTT TTTTTTT TTTTTTT                        22.09.2014
  XX                               1052489     000000000000884280    TTTTTTTTTT TTTTTTTTTT                             22.09.2014
  ...  other lines
  XX                               1064278     000000000000240283    TTTTTTTTTT TTTTTTTTTT                             07.11.2014


Actually I do this manually with ISPF command lines, and it's rather time consuming to me, I would like to process the output file from the tool to gain the previous output ready to be imported in an EXCEL spreadsheet. Hope in some quick help, which will be greatly appreciated.
Thanks in advance.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Wed Apr 26, 2017 10:23 am    Post subject: Reply with quote

Fab,

You can use DSNTIAUL to unload the records from the DB2 in raw format. You have couple of options to reformat the data to readable format.

1. Change the sql for the columns to a readable format (Decimal, Integer, float...)
2. Run the Unload file thru an utility to reformat.

or if your shop has High performance Unload (HPU) then you can use to unload the records from the DB2 table with excel delimiters.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Wed Apr 26, 2017 10:43 am    Post subject: Reply with quote

Kolusu,
thanks, but as far as I know we are only allowed to use that tool I told you in my post. But you can be sure that I will ask if we can use that tool too. These datas are directly from our production environment and here sysops are very paranoid about what we can or can't do with that data. I work in a big Bank, probably you can imagine. Thanks as usual for you continuous support.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Wed Apr 26, 2017 11:49 am    Post subject: Reply with quote

Fab wrote:
Kolusu,
thanks, but as far as I know we are only allowed to use that tool I told you in my post. But you can be sure that I will ask if we can use that tool too.


You are running DSNTEP2/QMF to generate the report. Check this link for an example of DSNTIAUL.

http://www.mvsforums.com/helpboards/viewtopic.php?p=61581#61581

Give the example a try and see if it works for you.


Fab wrote:

These datas are directly from our production environment and here sysops are very paranoid about what we can or can't do with that data. I work in a big Bank, probably you can imagine. Thanks as usual for you continuous support.


You are reading the data the same way. One in the raw format and other in formatted form which is readable.

Btw if you insist on merging the datasets then it can be done, but it would require a lot of processing. You need to use IFTHEN=(when=GROUP processing but you need to able to distinguish as how many records it being split into.

For example if your data ends up less than 266 bytes ,then it is only 2 groups, but if the data is 1500 bytes then you end up with more than 11 groups. so you need to adjust as to where each group goes, like group 2 starts at position 134 and group 3 starts at 267 and so on...
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Thu Apr 27, 2017 8:28 am    Post subject: Reply with quote

Hi Kolusu,
Quote:
You are reading the data the same way. One in the raw format and other in formatted form which is readable.

I don't think so. I can't create or execute any jcl by hand in production, so no unload is possible. I can just run queries and export results from QMF.

Let me explain the environment we have and what we do right now. As I told sometimes QMF can't manage our complex queries, insufficient resouces. Then we put that query in a catalogued dataset. Every 15 min a scheduled process execute those commands (we use it for spufi). I have no control on that process, and no control on jcl which is already there in that form. We have at least 2 different environment here. Production which is read only, and test which we can modify. Production and test DB2 database are NOT aligned nor can be. So I have to execute my query in production, transfer sysout to test environment and there process it creating an exportable EXCEL file.

I can process it by hand (ISPF), with a jcl or a program. Smile

Quote:

You are running DSNTEP2/QMF to generate the report. Check this link for an example of DSNTIAUL.
http://www.mvsforums.com/helpboards/viewtopic.php?p=61581#61581
Give the example a try and see if it works for you.


you mean I should try that sort (modified of course) and see if it fits my needs? Ok I will

Quote:
Btw if you insist on merging the datasets then it can be done, but it would require a lot of processing. You need to use IFTHEN=(when=GROUP processing but you need to able to distinguish as how many records it being split into.

I guess that 7 split are enough for our common needs, in particular cases I would need to adjust that hypothetical sort. but as I understand you maybe this time is a bit too complex to be done, at least by my limited knowledge.
Thank you very much as usual Kolusu.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Thu Apr 27, 2017 12:17 pm    Post subject: Reply with quote

Fab,

You sure do have a weird set up at your shop. Since you can run jcl's I would suggest trying out the sample JCL for DSNTIAUL as shown in my link earlier in your test environment.

Also check this link for converting the numerical fields to display fields and generating the SQL from it using sysibm.syscolumns

http://www.mvsforums.com/helpboards/viewtopic.php?t=11443

You can change the above sql to append any excel delimiter value and you use that generated SQL as input to DSNTIAUL which would give you the desired results.

I can show you how to do in DFSORT but I for one believe in using the right tool for the right job.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Fri Apr 28, 2017 3:28 am    Post subject: Reply with quote

Hi Kolusu,
weird set up yes, even frustrating, nonetheless that's it. I can't follow your suggestions because test DB2 database is absolutely NOT reliable. I need production data. And I can't request any database alignment for two good reasons, burocracy and other people using test environment for their own develpment purpose. Burocracy also make me reluctant to create a new jcl and run it in test, in production we are not allowed to create or transfer JCL, we can only ask another group to create that for us, giving specification. So it's pointless to prepare something new in test and ask for a new creation in production, because it will be created according our production JCL standard, and I doubt it will be the same as I want. I have to process production data, transfer them to test and then process it there. Where process might be to use ISPF, a sort or a program.

Have no other choice here. If you can help me with sort would be wonderful, but I understand you if you don't Smile absolutely no problem anyway. Thanks so much Kolusu.
Back to top
View user's profile Send private message
misi01
Advanced


Joined: 02 Dec 2002
Posts: 616
Topics: 171
Location: Stockholm, Sweden

PostPosted: Mon May 01, 2017 3:06 am    Post subject: Reply with quote

Not sure if it might be of interest, but I have a Rexx script that I specifically wrote to reformat" this sort of output.

The diea was that you can call it via DSNTSA4 or DSNTLA4.

In your example (I've simplified it), you have the following:-

Code:

         +------------------------------------------------------------------
          !       AAAAAA       !        BBBBBBB        !      CCCCCCC       !
          +------------------------------------------------------------------
        1_! XX                 !             1050405   ! 000000000000893994 !
        2_! XX                 !             1054016   ! 000000000000909742 !
        3_! XX                 !             1048799   ! 000000000000991566 !
        4_! XX                 !             1048355   ! 000000000000686623 !
        5_! XX                 !             1047049   ! 000000000000860967 !
          +------------------------------------------------------------------
1PAGE    3                                                                   
           ---------------------------------------------------------------------
           !                DDDDDDDDDDDDDDDD                 ! EEEEEEEEEEEEEEE !
           ---------------------------------------------------------------------
         1_! TTTTTTTTTT TTTTTTT TTTTTTT                      ! GG.MM.AAAA      !
         2_! TTTTTTTTTT TTTTTTT TTTTTTT                      ! GG.MM.AAAA      !
         3_! TTTTTTTTTTTTTT TTTTTTTT                         ! GG.MM.AAAA      !
         4_! TTTTTTTTTTTTTT TTTTTTTT                         ! GG.MM.AAAA      !
         5_! TTTTTTTTTT TTTTTTT TTTTTTT                      ! GG.MM.AAAA      !
           ---------------------------------------------------------------------


DSNTLA4 (in Swedish, Landscape is called Liggande) will format it as Landscape, thus
(this should be shown using Courier New, but the forum doesn't give me that luxury, but you get (?) the idea))
Code:

 +------------------------------------------------------------------------------------------------------------------------
 !       AAAAAA       !        BBBBBBB        !      CCCCCCC       !   DDDDDDDDDDDDDDDD                 ! EEEEEEEEEEEEEEE
 +------------------------------------------------------------------------------------------------------------------------
 ! XX                 !             1050405   ! 000000000000893994 ! TTTTTTTTTT TTTTTTT TTTTTTT         ! GG.MM.AAAA     
 ! XX                 !             1054016   ! 000000000000909742 ! TTTTTTTTTT TTTTTTT TTTTTTT         ! GG.MM.AAAA     
 ! XX                 !             1048799   ! 000000000000991566 ! TTTTTTTTTTTTTT TTTTTTTT            ! GG.MM.AAAA     
 ! XX                 !             1048355   ! 000000000000686623 ! TTTTTTTTTTTTTT TTTTTTTT            ! GG.MM.AAAA     
 ! XX                 !             1047049   ! 000000000000860967 ! TTTTTTTTTT TTTTTTT TTTTTTT         ! GG.MM.AAAA     

In the same way, DSNSA4 (S = Swedish for portait) would give you something along these lines
Code:

 AAAAAA(1)                        XX                   
 BBBBBBB(1)                      1050405   
 CCCCCCC (1)                     000000000000893994
 DDDDDDDDDDDDDDDD(1)    TTTTTTTTTT TTTTTTT TTTTTTT
 EEEEEEEEEEEEEEE(1)          GG.MM.AAAA
 AAAAAA(2)                        XX                   
 BBBBBBB(2)                      1054016   
 CCCCCCC (2)                     000000000000909742
 DDDDDDDDDDDDDDDD(2)    TTTTTTTTTT TTTTTTT TTTTTTT
 EEEEEEEEEEEEEEE(2)          GG.MM.AAAA

etc etc (you get the idea)


Let me know if this is what you're looking for and I can send it you.

(Updated to change from quote to code tags. Thanks Nic)
_________________
Michael


Last edited by misi01 on Mon May 01, 2017 5:55 am; edited 3 times in total
Back to top
View user's profile Send private message Send e-mail
Nic Clouston
Advanced


Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home

PostPosted: Mon May 01, 2017 4:07 am    Post subject: Reply with quote

The code tags give you a fixed pitch font - same as Courier New. OK, you get green on black but that is what is probably being seen anyway - before printing.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.
Back to top
View user's profile Send private message
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Tue May 02, 2017 10:04 am    Post subject: Reply with quote

Thanks much for your kind offer misi01,
unfortunately it is not that clear to me if your output will be like this:
Code:

+------------------------------------------------------------------------------------------------------------------------
 !       AAAAAA       !        BBBBBBB        !      CCCCCCC       !   DDDDDDDDDDDDDDDD                 ! EEEEEEEEEEEEEEE
 +------------------------------------------------------------------------------------------------------------------------
 ! XX                 !             1050405   ! 000000000000893994 ! TTTTTTTTTT TTTTTTT TTTTTTT         ! GG.MM.AAAA     
 ! XX                 !             1054016   ! 000000000000909742 ! TTTTTTTTTT TTTTTTT TTTTTTT         ! GG.MM.AAAA     
 ! XX                 !             1048799   ! 000000000000991566 ! TTTTTTTTTTTTTT TTTTTTTT            ! GG.MM.AAAA     
 ! XX                 !             1048355   ! 000000000000686623 ! TTTTTTTTTTTTTT TTTTTTTT            ! GG.MM.AAAA     
 ! XX                 !             1047049   ! 000000000000860967 ! TTTTTTTTTT TTTTTTT TTTTTTT         ! GG.MM.AAAA     


or like this:
Code:

AAAAAA(1)                        XX                   
 BBBBBBB(1)                      1050405   
 CCCCCCC (1)                     000000000000893994
 DDDDDDDDDDDDDDDD(1)    TTTTTTTTTT TTTTTTT TTTTTTT
 EEEEEEEEEEEEEEE(1)          GG.MM.AAAA
 AAAAAA(2)                        XX                   
 BBBBBBB(2)                      1054016   
 CCCCCCC (2)                     000000000000909742
 DDDDDDDDDDDDDDDD(2)    TTTTTTTTTT TTTTTTT TTTTTTT
 EEEEEEEEEEEEEEE(2)          GG.MM.AAAA


because in 1st case it will be perfect, I'm not sure I can execute REXX scripts in my test environment tho, I will verify tomorrow morning.

I tried a couple of sort but I couldn't get desired results yet, I see no answer from Kolusu so I think that maybe I could be misunderstood his answer above. I understand that formatting output straight from the Batch query is the way to go, but as I tried to explain I simply can't create/execute anything in production environment, and every JCL that we want to transfer in production must be approved by our sysops before (this is what I called burocracy). I can only get sysout from production tool execution and process it with easy in my test environment. I was in doubt I did not explain me good above, I'm sorry if I didn't.

Thanks again
Back to top
View user's profile Send private message
misi01
Advanced


Joined: 02 Dec 2002
Posts: 616
Topics: 171
Location: Stockholm, Sweden

PostPosted: Tue May 02, 2017 4:30 pm    Post subject: Reply with quote

Fab

The results you get are dependent on whether you choose DSNTLA4 (the first set of results) or DSNTSA4 (the second set )

You SHOULD be able to run a text script even if it means you having to do a manual ALTLIB on the library where the script is.
_________________
Michael
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Tue May 02, 2017 6:53 pm    Post subject: Reply with quote

misi01 wrote:

DSNTLA4 (in Swedish, Landscape is called Liggande) will format it as Landscape, thus
(this should be shown using Courier New, but the forum doesn't give me that luxury, but you get (?) the idea))
In the same way, DSNSA4 (S = Swedish for portait) would give you something along these lines

The results you get are dependent on whether you choose DSNTLA4 (the first set of results) or DSNTSA4 (the second set )

You SHOULD be able to run a text script even if it means you having to do a manual ALTLIB on the library where the script is


Misi01,

From your brief description (portrait vs landscape), I don't think it will work for what Fab is trying to do.

What is the maximum LRECL that your rexx exec can handle for landscape mode?

For example, SYSIBM.SYSTABLES have 59 columns and the formatted output from DSNTEP2 will be around 21 pages @ 133 byte Lrecl for just 1 row of data. Now these 21 pages need to be combined into single page which would result in a single record of 2793 bytes in Lrecl. I may be wrong but I don't think your exec can handle that LRECL. Also remember that DSNTEP2 has a weird way of formatting the data.

For example you can try this and look at where you would find 2nd page of your 1st column data ( Hint : look for .1 in position 11)

Code:

//STEP0100 EXEC PGM=IKJEFT01,DYNAMNBR=20               
//SYSTSIN  DD  *                                       
 DSN SYSTEM(DB2P)                                     
 RUN  PROGRAM(DSNTEP2)  -                             
      PLAN(DSNTEP2)     -                             
      LIB('DB2P.RUNLIB.LOAD')                         
 END                                                   
//SYSTSPRT DD SYSOUT=*                                 
//SYSPRINT DD DSN=Misi01.DSNTEP2.OUTPUT.FETCH20K,
//            DISP=(NEW,CATLG,DELETE),                 
//            SPACE=(CYL,(100,50),RLSE)               
//SYSIN    DD *
  SELECT *                         
    FROM SYSIBM.SYSTABLES         
   FETCH FIRST 20000 ROWS ONLY   
  ;                             


Now try to format that using DSNTLA4 or DSNTSA4 and see if you can ALL the columns into single record. (roughly about 24 pages with headers)

If your exec does indeed format the data, I would love to have that exec.

Thanks
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Tue May 02, 2017 7:07 pm    Post subject: Reply with quote

Fab wrote:
I tried a couple of sort but I couldn't get desired results yet, I see no answer from Kolusu so I think that maybe I could be misunderstood his answer above.


Fab,

As I mentioned earlier, you can format it using DFSORT however it would need about 3 passes of data which includes 2 sorts which I was trying to avoid. As I was I busy wrapping some of my work, I did not get a chance to work on it.

Here is a way to do it with DFSORT. It would give you just the data without the headers.

P.S: I may have missed something as I do not have a working unload data from DSNTEP2. I just used the SYSIBM.SYSTABLE as a sample with 18000 rows. I do not handle more than 99,999 pages of output from DSNTEP2. So you may have to change the sort solution a bit, if you have more than 100K pages of data which I will leave it to you.

Last but not least, I still stand by using DSNTIAUL and get the raw data and then format it. I am aware that you have restrictions but, I am sure if you are allowed to run DSNTEP2 you would be able to run DSNTIAUL.

Code:

//*************************************************************
//* TAG THE RECORDS TO GROUPS AND SORT BASED ON GROUP NUMBER **
//*************************************************************
//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD DISP=SHR,DSN=Your unload from DSNTEP2 file
//*
//SORTOUT  DD DSN=&&TMP,DISP=(,PASS),SPACE=(CYL,(100,25),RLSE)
//SYSIN    DD *                                               
  INCLUDE COND=(001,005,CH,EQ,C'1PAGE',OR,                     
                001,133,SS,EQ,C'_|')                           
                                                               
  INREC IFOUTLEN=142,                                         
        IFTHEN=(WHEN=GROUP,BEGIN=(001,5,CH,EQ,C'1PAGE'),       
          PUSH=(134:ID=8,143:SEQ=2)),                         
        IFTHEN=(WHEN=GROUP,BEGIN=(011,2,CH,EQ,C'.1'),         
          PUSH=(153:6,5)),                                     
        IFTHEN=(WHEN=(153,5,CH,GT,C' '),                       
       OVERLAY=(134:(134,8,ZD,SUB,153,5,UFF),ADD,+1,           
                     M11,LENGTH=8))                           
                                                               
  SORT FIELDS=(134,8,CH,A),EQUALS                             
                                                               
  OUTFIL OMIT=(1,5,CH,EQ,C'1PAGE'),                           
  OVERLAY=(144:SEQNUM,8,ZD,RESTART=(134,8))                   
//*
//*************************************************************
//* SORT THE RECORDS SO THE DATA IS ORDERED AS IN DB2 TABLE  **
//*************************************************************
//STEP0200 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD DSN=&&TMP,DISP=SHR                               
//SORTOUT  DD DSN=&&DAF,DISP=(,PASS),SPACE=(CYL,(100,25),RLSE)
//TLN      DD DSN=&&TLN,DISP=(,PASS),SPACE=(TRK,(1,0),RLSE)   
//SYSIN    DD *                                               
  SORT FIELDS=(144,8,CH,A,                                     
               134,8,CH,A),EQUALS                             
                                                               
  OUTREC OVERLAY=(155:134,8,ZD,MUL,+131,M11,LENGTH=5)         
                                                               
  OUTFIL IFTHEN=(WHEN=INIT,BUILD=(2,132)),                     
         IFTHEN=(WHEN=INIT,                                   
         PARSE=(%01=(STARTAFT=C'_',FIXLEN=132)),               
              BUILD=(%01,JFY=(SHIFT=LEFT))),                   
         IFTHEN=(WHEN=INIT,BUILD=(2,131))                     
                                                               
  OUTFIL FNAMES=TLN,REMOVECC,NODETAIL,BUILD=(80X),             
  TRAILER1=(3:'TOLEN(',                                       
            MAX=(155,5,ZD,EDIT=(TTTTT)),                       
            ')')                                               
//* 
//**************************************************************
//*   NOW RESIZE THE DATA INTO A SINGLE RECORD                **
//**************************************************************
//STEP0300 EXEC PGM=ICETOOL                                     
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//DAF      DD DISP=(OLD,PASS),DSN=&&DAF                         
//OUT      DD SYSOUT=*                                         
//TOOLIN   DD *                                                 
  RESIZE FROM(DAF) TO(OUT) -                                   
//         DD DISP=(OLD,PASS),DSN=&&TLN                         
//*                                                             

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
misi01
Advanced


Joined: 02 Dec 2002
Posts: 616
Topics: 171
Location: Stockholm, Sweden

PostPosted: Tue May 02, 2017 11:42 pm    Post subject: Reply with quote

Kolusu, you're quite right inasmuch as the file with the results HAS to have been (pre) allocated with a record length large enough to accommodate the landscape folded results (the script will issue an error message if it's not)

You're also right that DSNTEP2 produces some weird results (I'd be more inclined to call them bugs since the resulting layouts aren't consistent). My rexx script takes a number of these into account.

Be that as it may, fab has two possible solutions to his problem; it's up to him you choose which path he takes.
_________________
Michael
Back to top
View user's profile Send private message Send e-mail
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Wed May 03, 2017 10:41 am    Post subject: Reply with quote

Thanks heaps Kolusu & misi01,

@misi01
I have verified that I can run REXX scripts in my test env. I used the following JCL to test it:
Code:

//S0010    EXEC PGM=IKJEFT01                                     
//SYSTSPRT DD   SYSOUT=*                                         
//SYSEXEC  DD   UNIT=SYSDA,SPACE=(80,(100,50)),AVGREC=U,LRECL=80,
//             BLKSIZE=27920,RECFM=FB,DSN=MYUSERID.TEST.REXX,     
//             DISP=(,DELETE)                                   
//SYSTSIN  DD   *                                               
 REPRO INFILE(REXXCODE),OUTFILE(SYSEXEC)                         
EXEC 'MYUSERID.TEST.REXX' EXEC                                   
//REXXCODE DD *,DLM='?@'                                         
SAY 'Hello REXX '                                           
EXIT 0                                                           
?@                                                               

I'm sorry but it is still unclear to me what are DSNTLA4 and DSNTSA4.

@Kolusu
I apology if I gave you impression that I was insisting on my intention to use a sort. As you can easily guess english in not my native language and I could express things in a wrong way sometimes.

Code:

Last but not least, I still stand by using DSNTIAUL and get the raw data and then format it. I am aware that you have restrictions but, I am sure if you are allowed to run DSNTEP2 you would be able to run DSNTIAUL.

you are right, we can execute that, but we can't write JCL and transfer them in production as they are. We can write and run JCL in test, and no more than that. When we must create some new JCL in production we have a complex request to compile, in which we must describe what our JCL must accomplish. But JCL will be entirely written in production by our sysop team according to our JCL standard (which is rather basic). Just to (try) to be even more precise I can tell you that in my 1st post here you gave me a smart solution to make a formula calculation in a JCL I had. Well, when I requested to transfer that JCL that I had succesfully run in test env, they refused to write it as it was in production because they could not understand what that JCL did. So I was forced to find a "trasversal" (read "almost illegal")solution to execute that JCL in production. It would be difficult to explain even in my native language.

Code:

I may have missed something as I do not have a working unload data from DSNTEP2. I just used the SYSIBM.SYSTABLE as a sample with 18000 rows. I do not handle more than 99,999 pages of output from DSNTEP2. So you may have to change the sort solution a bit, if you have more than 100K pages of data which I will leave it to you.

Understood, maybe I have to modify something right now. because when I try your code I get this:
Code:

ICE600I 0 DFSORT ICETOOL UTILITY RUN STARTED                                 
                                                                             
ICE650I 0 VISIT http://www.ibm.com/storage/dfsort FOR ICETOOL PAPERS, EXAMPLES
                                                                             
ICE632I 0 SOURCE FOR ICETOOL STATEMENTS:  TOOLIN                             
                                                                             
                                                                             
ICE630I 0 MODE IN EFFECT:  STOP                                               
                                                                             
            RESIZE FROM(DAF) TO(OUT) -                                       
            TOLEN(00000)                                                     
                  $                                                           
ICE604A 0 ERROR IN KEYWORD, PARAMETER, OR DELIMITER                           
ICE602I 0 OPERATION RETURN CODE:  12                                         
                                                                             
                                                                             
ICE601I 0 DFSORT ICETOOL UTILITY RUN ENDED - RETURN CODE:  12                 


I left Icetool step unchanged (but probably I have to change it?)
Code:

//STEP0300 EXEC PGM=ICETOOL           
//TOOLMSG  DD SYSOUT=*                 
//DFSMSG   DD SYSOUT=*                 
//DAF      DD DISP=(OLD,PASS),DSN=&&DAF
//OUT      DD SYSOUT=*                 
//TOOLIN   DD *                       
  RESIZE FROM(DAF) TO(OUT) -           
//         DD DISP=(OLD,PASS),DSN=&&TLN


My test input is actually 083221 lines for 1415 pages so probably that is not the problem. Tomorrow I will see how it works Icetool/resize token to solve this error. Thanks for you help & support Kolusu.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Goto page 1, 2, 3  Next
Page 1 of 3

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group