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
 

 

Using DFSORT symbols on REFORMAT record

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Claes Norreen

Active User


Joined: 20 Dec 2005
Posts: 137
Location: Denmark

PostPosted: Fri Aug 24, 2012 5:17 pm    Post subject: Using DFSORT symbols on REFORMAT record
Reply with quote

An issue I have with JOINKEYS is the lack of (real) positions (icon_smile.gif in the REFORMAT statement. The reason being I would like to use symbols to place the data exactly the same place in the output file - no matter if the input file(s) changes. So, in addition to state where in F1/F2 the data comes from, I would like to be able to state where in the reformatted record the data should be placed. It's NOT good enough to be able to control the output record with OUTFIL, since if you are using symbols in the REFORMAT statement, you can't know if they were changed (Because, here we use dynamically generated symbols as masks for record layouts).
In addition, it would be great to be able to position the ? using the ':' as well.

I hope you'll consider these issues I have with JOINKEYS, because I really love JOINKEYS and use it a lot. I will be happy to explain further and/or supply with examples, if need be.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Aug 24, 2012 6:23 pm    Post subject: Reply to: Issues with JOINKEYS
Reply with quote

Hi Claes,

If possible, can you post the sysout for the sort prior to the joinkeys step and the sysout from the joinkeys step.

There might be an "old school" way to ascertain which record(s) are the problem:

If you do a JOINKEYS with F1 being the file you have a problem with, without the SORTED, use

Code:
  JOIN UNPAIRED,F1,ONLY


and a REFORMAT for the entire F1 record.

You will then have a file in sorted JOINKEYS order which you can use in a comparison product to match to your original "pre-sorted" file and thus identify the out-of-sequence records.
Back to top
View user's profile Send private message
Claes Norreen

Active User


Joined: 20 Dec 2005
Posts: 137
Location: Denmark

PostPosted: Fri Aug 24, 2012 8:01 pm    Post subject:
Reply with quote

Hi Bill,

Thanks for your reply. It's a good idea you present, and I already have a job to do the match-stuff (using SEQNUM). I will try this on Monday (weekend now icon_biggrin.gif) and also post the SYSOUT if nessesary.

In the mean time, I invite you all to comment on my 2nd issue. icon_smile.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Aug 24, 2012 9:18 pm    Post subject: Reply to: Issues with JOINKEYS
Reply with quote

I'm not sure I understand your second part well enough.

You can specify, byte-by-byte, exactly where you want to put data on the REFORMAT record, only not by specifying a column (with the colon) but by specifying all the data up to that point. The same with the ?. You can tell DFSORT exactly where to put it, by specifying what goes in front of it, if anything.

So, you mention symbols and their generation. Are you saying that, in circumstances where you want to include an entire record on the REFORMAT, you'd like to be able to use the same symbol names?

That could be done for one of the files, directly, but not for both, as the most relevant part of the symbols are the position and length. The first part of the REFORMAT record could always be identical to one of the input records if you want, but the second part of the REFORMAT record cannot be because of the increased displacement of the first part of the REFORMAT record.

I could be completely misunderstanding this, but...

What I do is define the record with symbols in such a way that only one actual start position is specified: all other symbols for the record are relative to that first one. Then I can easily "change the position" of a record whilst using symbols without having to re-code everything. Of course, I have to give the symbol a new name, so I have a "prefix" which changes as to the location being used.

If you are generating symbols, it should be possible to include a prefix and a starting point for the symbols, so that standard and equivalent symbols can be used in the REFORMAT statement where you have the whole record.

Code:
INPUT-RECORD,1,80,CH
INPUT-KEY,=,10,CH
  INPUT-DATA1,*,20,CH
  INPUT-DATA2,*,30,CH
  INPUT-DATA3,*,20,CH


If I want to use that, with matching record-types on both files, on a REFORMAT I can add this:

Code:
REFORMATF1-RECORD,1,80,CH
  REFORMATF1-KEY,=,10,CH
  REFORMATF1-DATA1,*,20,CH
  REFORMATF1-DATA2,*,30,CH
  REFORMATF1-DATA3,*,20,CH
REFORMATF2-RECORD,81,80,CH
  REFORMATF2-KEY,=,10,CH
  REFORMATF2-DATA1,*,20,CH
  REFORMATF2-DATA2,*,30,CH
  REFORMATF2-DATA3,*,20,CH
REFORMAT-MATCH-MARKER,*,1,CH

  REFORMAT FIELDS=(F1:INPUT-RECORD,
                  F2:INPUT-RECORD,
                  ?)


Then, for references to the reformat record, use those with the correct prefix (REFORMATF1, REFORMATF2), and REFORMAT-MATCH-MARKER for the ? value.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Sat Aug 25, 2012 2:33 am    Post subject:
Reply with quote

Claes Norreen,

1.) Unfortunately there is no direct way to know the out of sequence key. However you can use this sample to find the out of sequence key
Code:

//STEP0100 EXEC PGM=SORT                     
//SYSOUT   DD SYSOUT=*                       
//SORTIN   DD *                               
A                                             
B                                             
C                                             
A                                             
//SORTOUT  DD SYSOUT=*                       
//SYSIN    DD *                               
  INREC OVERLAY=(10:SEQNUM,8,ZD)             
  SORT FIELDS=(1,1,CH,A),EQUALS               
  OUTREC OVERLAY=(20:SEQNUM,8,ZD)             
  OUTFIL INCLUDE=(10,8,ZD,NE,20,8,ZD),ACCEPT=1
//*


2. I am with bill on this. Can you show us some examples of what you want? Remember that you can use INREC IFTHEN right after REFORMAT to the main task which you can format the way you want.
Back to top
View user's profile Send private message
Claes Norreen

Active User


Joined: 20 Dec 2005
Posts: 137
Location: Denmark

PostPosted: Mon Aug 27, 2012 1:59 pm    Post subject:
Reply with quote

Thanks both for your time icon_smile.gif First, I will try to illustrate my issue of 2):

At my installation, we make heavily use of full DB2 UNLOADs - thus, every day we unload every single DB2 table to a flat dataset. Using the output PUNCH cards of the DB2 UNLOAD, I automatically generate SYMNAMES for each unload dataset. Now, these data goes into a lot of processing jobs, which merge the data in all kinds of ways (ie, the business).

To avoid having to recode a lot of jobs in the event of a new column to one of the DB2 tables, I make use of the DFSORT symbols to position fields on exact positions. So, even if a DB2 column is added to a table (and the UNLOAD dataset extended), it will not affect the running jobs at all.

Example:
UNLOAD of Customers must be joined with UNLOAD of orders:

First, I need symbols for the output record. This is done by automatically generating them from what is equivalent to a COBOL copybook. So, when I need new data in the output, I just change the "copybook", and regenerate the symbols.

I would then like to code something like this:

Code:

SYMNAMES DD DSN=input1 (CUST)
         DD DSN=input2 (ORDER)
         DD DSN=output (Joined OUTPUT record)

...

JOINKEYS F1=IN1,FIELDS=(CUST_CUSTOMERNO)
JOINKEYS F2=IN2,FIELDS=(ORDER_CUSTOMERNO)
REFORMAT FIELDS=(OUTPUT_CUSTOMERNO:F1:CUST_CUSTOMERNO,
                 OUTPUT_CUSTOMERNAME:F1:CUST_CUSTOMERNAME,
                 OUTPUT_ORDERNO:F2:ORDER_ORDERNO)
 


Now, if anything changes in either SYMNAMES DD, it still works without any intervention. This is doable when using the “old fashion way” using COPY, COPY, SPLICE and OUTFIL.

I hope this makes it more clear? I’ll get back to 1) later today.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Mon Aug 27, 2012 2:49 pm    Post subject: Reply to: Issues with JOINKEYS
Reply with quote

Code:
JOINKEYS F1=IN1,FIELDS=(CUST_CUSTOMERNO)
JOINKEYS F2=IN2,FIELDS=(ORDER_CUSTOMERNO)
REFORMAT FIELDS=(F1:CUST_CUSTOMERNO,
                    CUST_CUSTOMERNAME,
                 F2:ORDER_ORDERNO)


This would give you the data as you want it, so I'm assuming by what you suggest you'd like a method to be able to "name" the fields in the REFORMAT record, or, to put it another, to define positions for that data to occupy on the REFORMAT record using columns (the colon)).

However, what if CUST_CUSTOMERNAME were at position 5000 and ORDER_ORDERNO were at 4999? It's lots of extra space, plus the fields overlap.

What you want to get out of it, is the subsequent code works even when the positions of the symbols for the input fields change due to changes in the data.

Is that about it?
Back to top
View user's profile Send private message
Claes Norreen

Active User


Joined: 20 Dec 2005
Posts: 137
Location: Denmark

PostPosted: Mon Aug 27, 2012 3:57 pm    Post subject:
Reply with quote

Correct, this is what I want - IF we assume the output record is Customerno, Customername and Orderno. Now, suppose I added a field to the output record in between Customerno and Customername. Now, I would need to alter the REFORMAT code to adjust for this. But if I was able to use names (positions, really) for the REFORMAT statement, then I wouldn't have to worry about it at all, as the output would still comply with the specs given for it without any changes to the code.

In short, I would like to be able to use positions ( : ) in the REFORMAT statement, just as I do in the OUTFIL keyword.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Mon Aug 27, 2012 10:48 pm    Post subject:
Reply with quote

Claes Norreen,

We will add your suggestion to the long list of enhancements. However did you know that you can use INREC right after REFORMAT which lets you format the input the way you wanted?

You just need to generate the symbols considering the reformat record.
something like this

Code:

  REFORMAT FIELDS=(F1:1,80,F2:1,80)
  INREC BUILD=(OUTPUT_CUSTOMERNO:F1:CUST_CUSTOMERNO,
               OUTPUT_CUSTOMERNAME:F1:CUST_CUSTOMERNAME,
               OUTPUT_ORDERNO:F1+80:ORDER_ORDERNO)


So whenever you have F2 fields, you need to add the F1 length to get the position of the F2 field.
Back to top
View user's profile Send private message
Claes Norreen

Active User


Joined: 20 Dec 2005
Posts: 137
Location: Denmark

PostPosted: Tue Aug 28, 2012 9:03 am    Post subject:
Reply with quote

Hi Skolusu,

Thanks for adding this to your enhancement list. icon_smile.gif

No, I wasn't aware about the INREC, and I'm not sure if it helps. But I will certainly try it out. icon_cool.gif

Are you investigating my other issue? If you need anything from me, then I'll be happy to forward it to you.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Aug 30, 2012 10:30 pm    Post subject:
Reply with quote

Claes Norreen,

Can we discuss this offline? Please send me email at skolusu at us dot ibm dot com.

Thanks
Back to top
View user's profile Send private message
Claes Norreen

Active User


Joined: 20 Dec 2005
Posts: 137
Location: Denmark

PostPosted: Mon Sep 03, 2012 3:55 pm    Post subject:
Reply with quote

Here you hide.. icon_wink.gif

I've mailed you now.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Mon Sep 03, 2012 7:51 pm    Post subject: Reply to: Using DFSORT symbols on REFORMAT record
Reply with quote

Yes, sorry about that. A touch of "I wish I'd done that in a different order" when I split the topic.
Back to top
View user's profile Send private message
Claes Norreen

Active User


Joined: 20 Dec 2005
Posts: 137
Location: Denmark

PostPosted: Mon Sep 03, 2012 7:53 pm    Post subject:
Reply with quote

Hehe, no problem at all, Bill.
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 -> DFSORT/ICETOOL 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 How to get the remainder and quotient... vnktrrd DFSORT/ICETOOL 2 Mon Oct 31, 2016 10:59 am
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm


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