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
 

 

Reformat data into a sensible format

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

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Tue Jan 19, 2016 6:00 pm    Post subject: Reformat data into a sensible format
Reply with quote

I've got a file with data (VB(218)), it has grown over the years to include various attributes, but rather than actually including these as new columns, they have been added as alternate "punctuation" in various time fields, i.e. the default hh.mm format has been changed into one of
  • hh:mm
  • hhxmm
to indicate special conditions, like a delay that was caused by specific reasons. We're now discussing adding two more variations,
  • hh-mm (moving west)
  • hh+mm (moving east)
For those occasions where a timezone has been crossed. The current programs use country data to detect these crossings, but we currently have a growing number of border crossings where the crossing times are such that we have to change auto-detections thresholds again and again, and at some stage changing them may no longer be possible...

So, given that the actual data in the file never exceeds column 192 (aka we have 26 spare columns), it would seem rather a lot more logical to actually expand an already existing flag field and use that, which would have the added advantage that we can use times in ISO8601 (hh:mm) format which would make it easier to exchange the data with spreadsheets on the PC.

I've already done a trial conversion using the ISPF editor, using a combination of excluding, hiding, cutting and pasting, but ideally we need something one-pass using DFSORT/ICETOOL, an edit macro in batch seems to be using an awful lot of CPU, so that we can run the old and new programs parallel for a while while testing. I've got no trouble doing it in multi-pass mode, by first expanding the existing flag field, to fill that on subsequent passes with translated current "alternate" punctuation characters, but can this be done in a single pass, even when some current records use two of these "alternate" punctuation characters?

As for positions, the current flag field occupies column 66, and
  • a ':' in column 61 should result in an 'F' in column 67
  • a ':' in column 71 should result in an 'Q' in column 68
  • an 'x' in column 71 should result in an 'x' in column 69
  • a '+' in column 78 should result in a '+' in column 70
  • a '-' in column 78 should result in a '-' in column 70
Only the ':' in column 71 and '+' (or '-') in column 78 can appear together, which may, and this has already been suggested, make it possible to actually combine columns 67-69 (so that we can have another conversion in a couple of years...)

The only other condition is that records starting with '{' in column 1 should be returned unchanged.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Tue Jan 19, 2016 6:58 pm    Post subject: Reply to: Reformat data into a sensible format
Reply with quote

Code:
  OPTION COPY
  INREC IFTHEN=(WHEN=(1,1,CH,EQ,C'{'),
                    OVERLAY=(1:1,1)),
        IFTHEN=(WHEN=(61,1,CH,EQ,C':'),
                    OVERLAY=(67:C'F')),
        IFTHEN=(WHEN=(71,1,CH,EQ,C':'),
                    OVERLAY=(68:C'Q'),HIT=NEXT),
        IFTHEN=(WHEN=(71,1,CH,EQ,C'x'),
                    OVERLAY=(69:71,1)),
        IFTHEN=(WHEN=(78,1,CH,EQ,C'+'),
                    OVERLAY=(70:78,1)),
        IFTHEN=(WHEN=(78,1,CH,EQ,C'-'),
                    OVERLAY=(70:78,1))


The first IFTHEN is to get rid of the test for { from within the other WHEN=(logicalexpressions). If you have lots of those lines, it may be more efficient to use AND and NE for it in the others.

The only issue is where you can have more than one test being true on the same record. This can happed with the 71 equal to ":", so that must have HIT=NEXT to continue the IFTHEN processing for that record. By default it stops when a WHEN=(logicalexpression) is true.

You only need the one HIT=NEXT, as the other conditions are mutually exclusive. You can rearrange the IFTHENs (into order of frequency of occurence in the data) as long as the first WHEN=(logicalexpression) that can also have a second truth on the same record has the HIT=NEXT. If you put the "signs" checks first, both would require HIT=NEXT.

The HIT=NEXT simply says "carry on the IFTHEN processing until true, or no more IFTHENs". If you want to carry on after the second hit, you'd need HIT=NEXT on that IFTHEN as well.
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Tue Jan 19, 2016 11:59 pm    Post subject:
Reply with quote

It's the HIT=NEXT that I completely missed.

Thank you!
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Wed Jan 20, 2016 3:05 am    Post subject:
Reply with quote

OK, I'm obviously doing something wrong, but given that every reformat is the same, I cannot for the life of me figure out what:

Code:
1ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED
 ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
 ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 23:16 ON TUE JAN 19, 2016 -
0            OPTION COPY
             OUTREC IFTHEN=(WHEN=(1,1,CH,EQ,C'{'),
                                BUILD=(1,218,C'     ')),
                    IFTHEN=(WHEN=(61,1,CH,EQ,C':'),
                                BUILD=(1,67,C'F   ,',68)),
                    IFTHEN=(WHEN=(71,1,CH,EQ,C':'),
                                BUILD=(1,67,C' Q  ,',68)),
                    IFTHEN=(WHEN=(71,1,CH,EQ,C'x'),
                                BUILD=(1,67,C'  x ,',68)),
                    IFTHEN=(WHEN=(78,1,CH,EQ,C'+'),
                                BUILD=(1,67,C'   +,',68)),
                    IFTHEN=(WHEN=(78,1,CH,EQ,C'-'),
                                BUILD=(1,67,C'   -,',68))
            END
 ICE201I F RECORD TYPE IS F - DATA STARTS IN POSITION 1
 ICE126A 2 INCONSISTENT REFORMATTING FOR *OUTREC : REASON CODE 02, IFTHEN 1
 ICE751I 0 C5-K90014 C6-K90014 C7-BASE   C8-K90014 E7-BASE
 ICE052I 3 END OF DFSORT

And is there a way to add the five blanks to the '{' records without actually having to code the LRECL? Output goes to (for now) to SYSOUT with an LRECL of 223.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Wed Jan 20, 2016 3:16 am    Post subject: Reply to: Reformat data into a sensible format
Reply with quote

Here's the ICE126A:


The Fine Manual wrote:
The following information is displayed in the message:

*INREC if the error is associated with an INREC statement. *OUTREC if the error is associated with an OUTREC statement. ddname if the error is associated with an OUTFIL statement.

rsn: The reason code associated with the error. The reason code (listed later in this section) helps you determine the specific cause of the error.

n: 0 if the error is not associated with an IFTHEN clause. Otherwise, n identifies the number of the IFTHEN clause associated with the error (starting
at 1 for the first IFTHEN clause).


If you use BUILD, you have to specify everything, and for fixed-length records all the output has to be the same length. OVERLAY is better for this, and can also be used to extend the records. You only need to specify the data that changes/is new.

You can consider IFOUTLEN for getting your records the same length, or ensure that in the OVERLAY, finishing with ,223:X), which says put a blank in column 223 (note, I may not have counted correctly) and fill any intervening positions (between the last byte of data and this column) with space.

No, for fixed-length records you have to know the length if you are extending (and in some other cases, like making a REFORMAT record).

For fixed-length records you can't say ,68)) to mean "from position 68 to the end of the record". That is only possible for variable-length records.
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Wed Jan 20, 2016 4:11 am    Post subject:
Reply with quote

OK, So I've changed the sort input to:

Code:
  OPTION COPY
  OUTREC IFOUTLEN=218,
* Comment record
         IFTHEN=(WHEN=(1,1,CH,EQ,C'{'),
                     BUILD=(1,218)),
* Blank record
         IFTHEN=(WHEN=(1,4,CH,EQ,C'    '),
                     BUILD=(1,218)),
* Specific wait
         IFTHEN=(WHEN=(61,1,CH,EQ,C':'),
                     BUILD=(1,67,C' F   ,',68,145),HIT=NEXT),
* Remove the now obsolete indicator
         IFTHEN=(WHEN=(69,1,CH,EQ,C'F'),
                     OVERLAY=(61:C'.'),HIT=NEXT),
* Specific departure
         IFTHEN=(WHEN=(71,1,CH,EQ,C':'),
                     BUILD=(1,67,C'  Q  ,',68,145),HIT=NEXT),
* Remove the now obsolete indicator
         IFTHEN=(WHEN=(70,1,CH,EQ,C'Q'),
                     OVERLAY=(77:C'.'),HIT=NEXT),
* Change of direction
         IFTHEN=(WHEN=(71,1,CH,EQ,C'x'),
                     BUILD=(1,67,C'   x ,',68,145),HIT=NEXT),
* Remove the now obsolete indicator
         IFTHEN=(WHEN=(71,1,CH,EQ,C'x'),
                     OVERLAY=(77:C'.'),HIT=NEXT),
* Move to the east(+) or west(-)
         IFTHEN=(WHEN=(84,1,CH,EQ,C'+',OR,
                       84,1,CH,EQ,C'-'),
                     OVERLAY=(72:84,1,84:C'.')),
* Everything else
         IFTHEN=(WHEN=NONE,
                     BUILD=(1,67,C'     ,',68,145))
 END

and although this should work, I still have '-' and '+''es left at position 84 of some records, with those records still containing blanks at position 72, despite adding a HIT=NEXT to everything. No error messages to help me this time...

And while posting this I realize why - no Q records, so no reformat yet...
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Wed Jan 20, 2016 4:58 am    Post subject: Reply to: Reformat data into a sensible format
Reply with quote

You identify the comment line to avoid false hits.


Code:
         IFTHEN=(WHEN=(1,1,CH,EQ,C'{'),
                     OVERLAY=(1:1,1)),


That's because there is no "no-op", so you make one up - "let column one be the value of position one for a length of one".

There is no possibility of a false hit for a blank line, so ditch that.


Use OVERLAY instead of BUILD. If all the columns and data in an OVERLAY are within the current copy of the record, the current copy of the record is simply updated. With BUILD, a new copy of the record is made.

You don't need to set a flag in one WHEN= and then test it and take some action in the next (if that flag has no value in it in the input record). Just clobber the position tested with OVERLAY (as you have done in one case).

Code:
* Specific wait, removing now obsolete indicator as well
         IFTHEN=(WHEN=(61,1,CH,EQ,C':'),
                     OVERLAY=(69:C' F   ,',61:C',')),


You only need HIT=NEXT when testing the 71 for :, at least if it is as you stated earlier and that is the only time there can be multiple codes of interest on the same record (multiple here is two, the : and either + or - (of significance).

You don't need a single BUILD in this code. You just OVERLAY the literal values, at the correct positions. You have the IFOUTLEN ensuring everything is 218, but if the input is 218 (implied by your BUILDs) then you don't even need that, as none of your OVERLAYs go outside the current record.

If you change the BUILDs to OVERLAYs, and remove the IFOUTLEN, then there is no dependency on knowing the record-length. If you need to extend the records, then yes, the IFOUTLEN is the way to go.

A comma has emerged that wasn't mentioned before.

For the +/- which remain in 84 with no other markers, I'd say you have blanks in the first four bytes, and you have no HIT=NEXT for that. I'm not suggesting you add HIT=NEXT, I'm saying that line is redundant, just not as redundant as I thought earlier, only more so. To put it another way, it is the source of your problem.

Your are clobbering 71 with your long literals. If you want to ensure that everything is space to start with (if that is necessary) you can use an IFTHEN=(WHEN=INIT to OVERLAY an appropriate amount of space (and a comma if now needed) into your flags.

I just had a go at updating my sample with what you seem to want, but from your current code I no longer know enough about your data to do that, eg:

Code:
* Change of direction
         IFTHEN=(WHEN=(71,1,CH,EQ,C'x'),
                     BUILD=(1,67,C'   x ,',68,145),HIT=NEXT),
* Remove the now obsolete indicator
         IFTHEN=(WHEN=(71,1,CH,EQ,C'x'),
                     OVERLAY=(77:C'.'),HIT=NEXT),


That tests 71 for x, then places an x in 71, along with some blanks and a comma around about. Then tests 71 for x again, and despite the comment drops a dot in 77.

I'd have updated mine to do the clearing the indicators back to what they were originally (before reused) in the OVERLAYs that were there. If a comma is needed on those records as well, that is easy to also include in the OVERLAYs.

With repeated use of the same position and values indicating things I'd also code the whole thing using SYMNAMES, which basically can obviate your comments (which can potentially be wrong and misleading) with code which tells its own story (and can still be wrong, but refelect 100% what is documented).

Originally I used this in my 80-byte test-data:

Code:
                                                                       
{                   
                                                            :
                                                                      :
                                                                      x
                                                                             +
                                                                             -
                                                                      :      +
                                                                      :      -


You could annotate those lines, say starting at column 10, with what should happen, extending as necessary. Explain the new output, and if you can't get completely there yourself, I'll have something to test on.
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Wed Jan 20, 2016 12:55 pm    Post subject:
Reply with quote

Bill,

Using this, which works:

Code:
  OPTION COPY
  OUTREC IFOUTLEN=218,
* Comment record
         IFTHEN=(WHEN=(1,1,CH,EQ,C'{'),
                     BUILD=(1,218)),
* Blank record
         IFTHEN=(WHEN=(1,4,CH,EQ,C'    '),
                     BUILD=(1,218)),
* Insert required space in all other records
         IFTHEN=(WHEN=(4,1,CH,EQ,C','),
                     BUILD=(1,67,C'     ,',68,145),HIT=NEXT),
* Specific wait
         IFTHEN=(WHEN=(61,1,CH,EQ,C':'),
                     OVERLAY=(69:C'F',61:C'.'),HIT=NEXT),
* Specific departure
         IFTHEN=(WHEN=(77,1,CH,EQ,C':'),
                     OVERLAY=(70:C'Q',77:C'.'),HIT=NEXT),
* Change of direction
         IFTHEN=(WHEN=(77,1,CH,EQ,C'x'),
                     OVERLAY=(71:C'x',77:C'.'),HIT=NEXT),
* Move to the east(1) or west(-)
         IFTHEN=(WHEN=(84,1,CH,EQ,C'+',OR,
                       84,1,CH,EQ,C'-'),
                     OVERLAY=(72:84,1,84:C'.'),HIT=NEXT),
* Everything else
         IFTHEN=(WHEN=NONE,
                     BUILD=(1,218))
 END

Probably one or two too many HIT=NEXT, but when I don't think that will affect processing time one iota.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Wed Jan 20, 2016 1:36 pm    Post subject: Reply to: Reformat data into a sensible format
Reply with quote

Looks pretty good.

If you have superfluous HIT=NEXT it will affect processing time. Whether you would notice on not depends on the amount of data. As important, it affects the understanding of the code/data. If you're happy, I'm happy.

Definitely affecting resources more are the redundant BUILDs, and the BUILDs which can still be replaced by OVERLAY.

Code:
  OPTION COPY
  OUTREC IFOUTLEN=218,
* Comment record
         IFTHEN=(WHEN=(1,1,CH,EQ,C'{'),
                     OVERLAY=(1:1,1)),
* Insert required space in all other records
         IFTHEN=(WHEN=(4,1,CH,EQ,C','),
                     OVERLAY=(68:C'     ,'),HIT=NEXT),
* Specific wait
         IFTHEN=(WHEN=(61,1,CH,EQ,C':'),
                     OVERLAY=(69:C'F',61:C'.'),HIT=NEXT),
* Specific departure
         IFTHEN=(WHEN=(77,1,CH,EQ,C':'),
                     OVERLAY=(70:C'Q',77:C'.'),HIT=NEXT),
* Change of direction
         IFTHEN=(WHEN=(77,1,CH,EQ,C'x'),
                     OVERLAY=(71:C'x',77:C'.'),HIT=NEXT),
* Move to the east(1) or west(-)
         IFTHEN=(WHEN=(84,1,CH,EQ,C'+',OR,
                       84,1,CH,EQ,C'-'),
                     OVERLAY=(72:84,1,84:C'.'))


That's functionally equivalent, assuming that all the lines which can change have that comma at position four.

I can't do anything with the HIT=NEXTs because I am unsure of the data. If you need them, you need them, it's only if you don't need them that they can be removed.
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Wed Jan 20, 2016 6:18 pm    Post subject: Re: Reply to: Reformat data into a sensible format
Reply with quote

Bill Woodger wrote:
Looks pretty good.

If you have superfluous HIT=NEXT it will affect processing time. Whether you would notice on not depends on the amount of data. As important, it affects the understanding of the code/data. If you're happy, I'm happy.

Given that this is only needed for a short while, in order to run the old and new versions of the program next to each other, simplicity is preferred over anything else. Still no decision on keeping old/introducing new format, usual "It's been working like this for ages, why risk breaking it..."

For what it's worth, haven't tried it, but
Code:
* Insert required space in all other records
         IFTHEN=(WHEN=(4,1,CH,EQ,C','),
                     OVERLAY=(68:C'     ,'),HIT=NEXT),

does this OVERLAY actually insert spaces and a comma?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Wed Jan 20, 2016 6:53 pm    Post subject: Reply to: Reformat data into a sensible format
Reply with quote

Sorry, no. Losing track...

You'd best use BUILD for that.

If you are selectively inserting data, the later columns are not going to be aligned across the whole file. I had thought you were reusing an area which was already only blank, and didn't check back to anything from earlier.
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Fri Jan 22, 2016 2:44 am    Post subject: Re: Reply to: Reformat data into a sensible format
Reply with quote

prino wrote:
Still no decision on keeping old/introducing new format, usual "It's been working like this for ages, why risk breaking it..."

Latest: "If we're going to change things, maybe we can create two extra columns, one for the flags (at least 90 different ones possible) and one specific for timezones." Sigh...
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Mon Feb 01, 2016 4:06 pm    Post subject:
Reply with quote

Followup to this, is it possible, in a single step to convert the file from FB to VB? I can add a second step with an
Code:
OUTFIL,FTOV,VLTRIM=x'40'

but it would be nice if this could be done in the same step. According to the manual it's not possible to use this in combination with IFTHEN, but maybe there is another way using ICETOOL?

If not, I'll just settle for a two-step solution.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Mon Feb 01, 2016 5:04 pm    Post subject:
Reply with quote

The documentation is saying that you can't use the FTOV in IFTHEN. You can use the FTOV in the same step with no problem, you can have IFTHEN on the OUTFIL with the FTOV, just not a conditional FTOV.

It's been suggested to me that CHANGE could be useful to you:
Code:

  INREC OVERLAY=(67:61,1,CHANGE=(1,C':',C'F'),NOMATCH=(67,1),
                 68:71,1,CHANGE=(1,C':',C'Q'),NOMATCH=(68,1),
                 69:71,1,CHANGE=(1,C'X',C'X'),NOMATCH=(69,1),
                 70:78,1,CHANGE=(1,C'+',C'+',                 
                                   C'-',C'-'),NOMATCH=(70,1))


Exactly how it fits depends on how your code has developed with the recent extensions...
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Tue Feb 02, 2016 4:53 pm    Post subject:
Reply with quote

I'll dive into the manuals to see what "CHANGE" is doing. In the meantime it's come to the attention that end-users have all kinds of scrips to work on the current file with all its idiosyncrasies, and needlessly to say plenty of these scripts no longer have an owner, "it just works...".

I still want to go for explicit flags, and have created another set of sort control cards to convert the data back to the old format, sigh...
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 Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am


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