Re: Sorting CSV data that begins with an IP address
I bet APL's solution to this would be like 2 weird symbols. I was just introduced to it by my senior colleague today, and it's ... dense. - Vignesh Mainframe Admin -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Shane Ginnane Sent: 17 April 2014 05:48 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address This serves as another tool for the job example. The sort in *nux is as simple as: sort -n -t . -k 1,1 -k 2,2 -k 3,3 -k 4,4 ipaddress.file How easy is that ?. Even OMVS has sort that should handle that. For the real output required, I'd pipe the output of that to gawk and use an associative array indexed on the first three octets of the IP address. Looks like the OMVS awk is missing the GNU extension of match() to use an array for splitting the IP address, but that can be worked around. REXX ? phooey. Shane ... -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
On 17 April 2014 08:18, Sankaranarayanan, Vignesh vignesh.v.sankaranaraya...@marks-and-spencer.com wrote: I bet APL's solution to this would be like 2 weird symbols. I was just introduced to it by my senior colleague today, and it's ... dense. Like Perl, it's been called a write-only language. Tony H. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
APL has perhaps attracted more fanatic enthusiasts than any other statement-level language. Over the years I have used it and still use it for throwaway routines for which matrix algebra is the obvious, appropriate formalism. (It does execution-time bound matrix algebra superbly, but not much else.) It is also a useful filter. In the days when it was more widely known and used I often asked job applicants what they thought of it, not because I cared much about their answers per se but because those who proved to know something about it usually also proved to be alert and intelligent, hackers in the good, old sense of that word. Jokes aside, its gnomic, read-only character is mostly in the minds of people who do not know much about it and/or matrix algebra. John Gilmore, Ashland, MA 01721 - USA -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
On Thu, 17 Apr 2014 15:16:30 -0400, Farley, Peter x23353 wrote: TSO Rexx does not support multiple tasks executing simultaneously. The ATTACH* functions do attach a new task, but your attaching Rexx waits synchronously for the attached task to complete. I know! Dammit; that sucks! The tedious alternative is to use SYSCALL spawn, which does support concurrency. I've pondered coding my own for-real ATTACH, but stumbled on not understanding Rexx's storage management or what else it might do behind my back. -- gil -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
On Thu, 17 Apr 2014 17:41:12 -0500, Norbert Friemel wrote: On Thu, 17 Apr 2014 19:26:17 +, Sankaranarayanan, Vignesh wrote: Ok.. Is it possible to fire off other REXXes repetitively (let's say 2 or 3, each doing one function) that will not RETURN to the main, but write their output to a dataset once done? Submit batch jobs (PGM=IKJEFT01,PARM='%rxsub?? p1 p2 p3) from the main? Neither DDLIST nor Google gives me relevant hits for RXSUB. (But it really shouldn't be that hard. For each job: RC = BPXWDYN( 'alloc rtddn(RDR) sysout writer(INTRDR) msg(WTP)' ) /* write JCL to RDR with EXECIO. */ RC = BPXWDYN( 'free dd('RDR') msg(WTP)' ) The hard part is detecting completion and getting status back.) -- gil -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
General info - The base program I'm putting together is a REXX that runs under NetView. It must be that way as I'm accessing global variables in there. For job step completion info, I remember seeing that in one of BMC's REXX'es that belongs to the ICE/cloning package. One of the REXXes was named JOBINFO. The idea was, they'd insert an ISPF batch step in each of the install jobs. That would collect the job statistics of the previous steps. - Vignesh Mainframe Admin -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Paul Gilmartin Sent: 18 April 2014 00:02 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address On Thu, 17 Apr 2014 17:41:12 -0500, Norbert Friemel wrote: On Thu, 17 Apr 2014 19:26:17 +, Sankaranarayanan, Vignesh wrote: Ok.. Is it possible to fire off other REXXes repetitively (let's say 2 or 3, each doing one function) that will not RETURN to the main, but write their output to a dataset once done? Submit batch jobs (PGM=IKJEFT01,PARM='%rxsub?? p1 p2 p3) from the main? Neither DDLIST nor Google gives me relevant hits for RXSUB. (But it really shouldn't be that hard. For each job: RC = BPXWDYN( 'alloc rtddn(RDR) sysout writer(INTRDR) msg(WTP)' ) /* write JCL to RDR with EXECIO. */ RC = BPXWDYN( 'free dd('RDR') msg(WTP)' ) The hard part is detecting completion and getting status back.) -- gil -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
While not a MF solution, there is a add-in available for Excel called IP Tools for Excel' that has a function that will allow you to sort IP addresses correctly. You can get it at http://checkthenetwork.com/ip-tools-for-excel There is a free trial period and then you need to pay $34.95 for a license. Besides the sort feature, there are many other things you can do with the product if you are tracking/managing your IP addresses using a spreadsheet. Thanks, Mark Regan From: Sankaranarayanan, Vignesh vignesh.v.sankaranaraya...@marks-and-spencer.com To: IBM-MAIN@LISTSERV.UA.EDU Sent: Wednesday, April 16, 2014 5:01 AM Subject: Sorting CSV data that begins with an IP address Hello, I need help sorting some 5000 lines based on the first entry in a CSV - IP address. When sorting, I need the whole record to be involved in the sort, not just the IP's. Sample data: IP,MAC,Make-Model,SEPINFO,Type,Ping,Status,Printer,GRPNAME Warehouse #,Warehouse Name,Warehouse Terminal,Warehouse Code, Warehouse CICS xx.yyy.zz.aaa,Unknown,Unknown,OKI,Report,NOT Ok,Unknown,abcd,Not Defined xx.yyy.zzz.a,Unknown,Unknown,Not Defined,Report,NOT Ok,Unknown,efgh,Not Defined xx.yy.zzz.aaa,bb:cc:dd:ee:ff:gg,ZTC GK420d,Not Defined,Report,Ok,Online,qwer,WHW Xx doesn't mean the same digit; just clearing out data. I require the sort to work for any IP address, be it 1 or 2 or 3 characters per octet. I'm working with this, and I haven't a clue on each of these keywords. If you guys were so kind as to help me with the SYSIN, I could save an hour or two of reading syntax rules. INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) Thanks in advance. - Vignesh Mainframe Admin MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
First thing to do is check what the result of INREC looks like. Second is to consider ICETOOL DATASORT to avoid sorting the header / trailer (or else use OUTFIL to recreate them. Cheers, Martin From: Sankaranarayanan, Vignesh vignesh.v.sankaranaraya...@marks-and-spencer.com To: IBM-MAIN@listserv.ua.edu Date: 16/04/2014 10:11 Subject:Sorting CSV data that begins with an IP address Sent by:IBM Mainframe Discussion List IBM-MAIN@listserv.ua.edu Hello, I need help sorting some 5000 lines based on the first entry in a CSV - IP address. When sorting, I need the whole record to be involved in the sort, not just the IP's. Sample data: IP,MAC,Make-Model,SEPINFO,Type,Ping,Status,Printer,GRPNAME Warehouse #,Warehouse Name,Warehouse Terminal,Warehouse Code, Warehouse CICS xx.yyy.zz.aaa,Unknown,Unknown,OKI,Report,NOT Ok,Unknown,abcd,Not Defined xx.yyy.zzz.a,Unknown,Unknown,Not Defined,Report,NOT Ok,Unknown,efgh,Not Defined xx.yy.zzz.aaa,bb:cc:dd:ee:ff:gg,ZTC GK420d,Not Defined,Report,Ok,Online,qwer,WHW Xx doesn't mean the same digit; just clearing out data. I require the sort to work for any IP address, be it 1 or 2 or 3 characters per octet. I'm working with this, and I haven't a clue on each of these keywords. If you guys were so kind as to help me with the SYSIN, I could save an hour or two of reading syntax rules. INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) Thanks in advance. - Vignesh Mainframe Admin MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN Unless stated otherwise above: IBM United Kingdom Limited - Registered in England and Wales with number 741598. Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6 3AU -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
On Wed, 16 Apr 2014 09:01:28 +, Sankaranarayanan, Vignesh wrote: I need help sorting some 5000 lines based on the first entry in a CSV - IP address. When sorting, I need the whole record to be involved in the sort, not just the IP's. I long ago gave up trying to decipher the runes to make sort/icetool work properly. Even the exits didn't help sometimes. Flick it to a zLinux instance and use GNU sort - you can use the dot as a key separator, and sort in one command. As Martin points out, any header/trailers may (will ) need particular attention. Not too surprisingly the perl monks have also had had a say on this - several times - but I'd forgo perl in this instance. Shane ... -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
Thanks for your inputs, Shane and Martin. Shane, We don't have a zLinux instance to pop over to. Plus, when the sorting is done in Excel, it takes 5 seconds. But I want the data to go out right from the mainframe. Besides, I will be looking for the next available IP in the last octet based on the sorted list of IP's. That is, If I see the below, I need to use 192.168.5.223. . . 192.168.5.222,. 192.168.6.200,. . . - Vignesh Mainframe Admin -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Shane Ginnane Sent: 16 April 2014 12:30 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address On Wed, 16 Apr 2014 09:01:28 +, Sankaranarayanan, Vignesh wrote: I need help sorting some 5000 lines based on the first entry in a CSV - IP address. When sorting, I need the whole record to be involved in the sort, not just the IP's. I long ago gave up trying to decipher the runes to make sort/icetool work properly. Even the exits didn't help sometimes. Flick it to a zLinux instance and use GNU sort - you can use the dot as a key separator, and sort in one command. As Martin points out, any header/trailers may (will ) need particular attention. Not too surprisingly the perl monks have also had had a say on this - several times - but I'd forgo perl in this instance. Shane ... -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
You ought to be able to do the +1 in DFSORT, by the way. In case that changes the problem into a better one. :-) Cheers, Martin Martin Packer, zChampion, Principal Systems Investigator, Worldwide Banking Center of Excellence, IBM +44-7802-245-584 email: martin_pac...@uk.ibm.com Twitter / Facebook IDs: MartinPacker Blog: https://www.ibm.com/developerworks/mydeveloperworks/blogs/MartinPacker From: Sankaranarayanan, Vignesh vignesh.v.sankaranaraya...@marks-and-spencer.com To: IBM-MAIN@listserv.ua.edu Date: 16/04/2014 12:35 Subject:Re: Sorting CSV data that begins with an IP address Sent by:IBM Mainframe Discussion List IBM-MAIN@listserv.ua.edu Thanks for your inputs, Shane and Martin. Shane, We don't have a zLinux instance to pop over to. Plus, when the sorting is done in Excel, it takes 5 seconds. But I want the data to go out right from the mainframe. Besides, I will be looking for the next available IP in the last octet based on the sorted list of IP's. That is, If I see the below, I need to use 192.168.5.223. . . 192.168.5.222,. 192.168.6.200,. . . - Vignesh Mainframe Admin -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Shane Ginnane Sent: 16 April 2014 12:30 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address On Wed, 16 Apr 2014 09:01:28 +, Sankaranarayanan, Vignesh wrote: I need help sorting some 5000 lines based on the first entry in a CSV - IP address. When sorting, I need the whole record to be involved in the sort, not just the IP's. I long ago gave up trying to decipher the runes to make sort/icetool work properly. Even the exits didn't help sometimes. Flick it to a zLinux instance and use GNU sort - you can use the dot as a key separator, and sort in one command. As Martin points out, any header/trailers may (will ) need particular attention. Not too surprisingly the perl monks have also had had a say on this - several times - but I'd forgo perl in this instance. Shane ... -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN Unless stated otherwise above: IBM United Kingdom Limited - Registered in England and Wales with number 741598. Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6 3AU -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
I plan to implement that in a separate REXX program. For now, it would be swell if I could get this -sorted-. Hah. - Vignesh Mainframe Admin -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Martin Packer Sent: 16 April 2014 13:23 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address You ought to be able to do the +1 in DFSORT, by the way. In case that changes the problem into a better one. :-) Cheers, Martin Martin Packer, zChampion, Principal Systems Investigator, Worldwide Banking Center of Excellence, IBM +44-7802-245-584 email: martin_pac...@uk.ibm.com Twitter / Facebook IDs: MartinPacker Blog: https://www.ibm.com/developerworks/mydeveloperworks/blogs/MartinPacker From: Sankaranarayanan, Vignesh vignesh.v.sankaranaraya...@marks-and-spencer.com To: IBM-MAIN@listserv.ua.edu Date: 16/04/2014 12:35 Subject:Re: Sorting CSV data that begins with an IP address Sent by:IBM Mainframe Discussion List IBM-MAIN@listserv.ua.edu Thanks for your inputs, Shane and Martin. Shane, We don't have a zLinux instance to pop over to. Plus, when the sorting is done in Excel, it takes 5 seconds. But I want the data to go out right from the mainframe. Besides, I will be looking for the next available IP in the last octet based on the sorted list of IP's. That is, If I see the below, I need to use 192.168.5.223. . . 192.168.5.222,. 192.168.6.200,. . . - Vignesh Mainframe Admin -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Shane Ginnane Sent: 16 April 2014 12:30 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address On Wed, 16 Apr 2014 09:01:28 +, Sankaranarayanan, Vignesh wrote: I need help sorting some 5000 lines based on the first entry in a CSV - IP address. When sorting, I need the whole record to be involved in the sort, not just the IP's. I long ago gave up trying to decipher the runes to make sort/icetool work properly. Even the exits didn't help sometimes. Flick it to a zLinux instance and use GNU sort - you can use the dot as a key separator, and sort in one command. As Martin points out, any header/trailers may (will ) need particular attention. Not too surprisingly the perl monks have also had had a say on this - several times - but I'd forgo perl in this instance. Shane ... -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN Unless stated otherwise above: IBM United Kingdom Limited - Registered in England and Wales with number 741598. Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6 3AU -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
Possibly the DFSORT INREC PARSE= feature might be usable to create your static-length, zone-decimal value field for the sort of a 'typical' IP address data-string in your CSV-format file. Scott Barry SBBWorks, Inc. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
Yup. I used this but the file gets all messed up. I'm sure it's not the right SYSIN for what I need to do. INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) - Vignesh Mainframe Admin -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Scott Barry Sent: 16 April 2014 14:27 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address Possibly the DFSORT INREC PARSE= feature might be usable to create your static-length, zone-decimal value field for the sort of a 'typical' IP address data-string in your CSV-format file. Scott Barry SBBWorks, Inc. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
Can you show us a sample of input data and of data before it's sorted i.e after the INREC? Thanks, Martin Martin Packer, zChampion, Principal Systems Investigator, Worldwide Banking Center of Excellence, IBM +44-7802-245-584 email: martin_pac...@uk.ibm.com Twitter / Facebook IDs: MartinPacker Blog: https://www.ibm.com/developerworks/mydeveloperworks/blogs/MartinPacker From: Sankaranarayanan, Vignesh vignesh.v.sankaranaraya...@marks-and-spencer.com To: IBM-MAIN@listserv.ua.edu Date: 16/04/2014 14:56 Subject:Re: Sorting CSV data that begins with an IP address Sent by:IBM Mainframe Discussion List IBM-MAIN@listserv.ua.edu Yup. I used this but the file gets all messed up. I'm sure it's not the right SYSIN for what I need to do. INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) - Vignesh Mainframe Admin -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Scott Barry Sent: 16 April 2014 14:27 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address Possibly the DFSORT INREC PARSE= feature might be usable to create your static-length, zone-decimal value field for the sort of a 'typical' IP address data-string in your CSV-format file. Scott Barry SBBWorks, Inc. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN Unless stated otherwise above: IBM United Kingdom Limited - Registered in England and Wales with number 741598. Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6 3AU -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
Just a few of questions 1) is the file being shipped from MF to PC or PC to MF as CSV? 2) Does the sort need to be done on the MF? 3) If the data will be in Excel - why not do the sort there? 4) What version of Excel? You have been given a lot of good suggestions. However, a better understanding of where the data actually resides will be helpful. Lizette -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Sankaranarayanan, Vignesh Sent: Wednesday, April 16, 2014 2:01 AM To: IBM-MAIN@LISTSERV.UA.EDU Subject: Sorting CSV data that begins with an IP address Hello, I need help sorting some 5000 lines based on the first entry in a CSV - IP address. When sorting, I need the whole record to be involved in the sort, not just the IP's. Sample data: IP,MAC,Make-Model,SEPINFO,Type,Ping,Status,Printer,GRPNAME Warehouse #,Warehouse Name,Warehouse Terminal,Warehouse Code, Warehouse CICS xx.yyy.zz.aaa,Unknown,Unknown,OKI,Report,NOT Ok,Unknown,abcd,Not Defined xx.yyy.zzz.a,Unknown,Unknown,Not Defined,Report,NOT Ok,Unknown,efgh,Not Defined xx.yy.zzz.aaa,bb:cc:dd:ee:ff:gg,ZTC GK420d,Not Defined,Report,Ok,Online,qwer,WHW Xx doesn't mean the same digit; just clearing out data. I require the sort to work for any IP address, be it 1 or 2 or 3 characters per octet. I'm working with this, and I haven't a clue on each of these keywords. If you guys were so kind as to help me with the SYSIN, I could save an hour or two of reading syntax rules. INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) Thanks in advance. - Vignesh Mainframe Admin -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
Hi Lizette, Here are the answers - 1) is the file being shipped from MF to PC or PC to MF as CSV? From MF to PC 2) Does the sort need to be done on the MF? Yes please 3) If the data will be in Excel - why not do the sort there? I need to do another unrelated process (REXX) on the sorted data on the mainframe. 4) What version of Excel? 2013 - Vignesh Mainframe Admin -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Lizette Koehler Sent: 16 April 2014 15:11 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address Just a few of questions 1) is the file being shipped from MF to PC or PC to MF as CSV? 2) Does the sort need to be done on the MF? 3) If the data will be in Excel - why not do the sort there? 4) What version of Excel? You have been given a lot of good suggestions. However, a better understanding of where the data actually resides will be helpful. Lizette -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Sankaranarayanan, Vignesh Sent: Wednesday, April 16, 2014 2:01 AM To: IBM-MAIN@LISTSERV.UA.EDU Subject: Sorting CSV data that begins with an IP address Hello, I need help sorting some 5000 lines based on the first entry in a CSV - IP address. When sorting, I need the whole record to be involved in the sort, not just the IP's. Sample data: IP,MAC,Make-Model,SEPINFO,Type,Ping,Status,Printer,GRPNAME Warehouse #,Warehouse Name,Warehouse Terminal,Warehouse Code, Warehouse CICS xx.yyy.zz.aaa,Unknown,Unknown,OKI,Report,NOT Ok,Unknown,abcd,Not Defined xx.yyy.zzz.a,Unknown,Unknown,Not Defined,Report,NOT Ok,Unknown,efgh,Not Defined xx.yy.zzz.aaa,bb:cc:dd:ee:ff:gg,ZTC GK420d,Not Defined,Report,Ok,Online,qwer,WHW Xx doesn't mean the same digit; just clearing out data. I require the sort to work for any IP address, be it 1 or 2 or 3 characters per octet. I'm working with this, and I haven't a clue on each of these keywords. If you guys were so kind as to help me with the SYSIN, I could save an hour or two of reading syntax rules. INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) Thanks in advance. - Vignesh Mainframe Admin -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
I have a question for you, I believe you had stated before that there is additional information on each record following the IP address, so with your INREC statement, should you not have also coded for %04 a ENDBEFR= option to end that field? So wouldn't you code: INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(ENDBEFR=C',',FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) Al Nims Systems Admin/Programmer 3 Information Technology University of Florida (352) 273-1298 -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Sankaranarayanan, Vignesh Sent: Wednesday, April 16, 2014 9:57 AM To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address Yup. I used this but the file gets all messed up. I'm sure it's not the right SYSIN for what I need to do. INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) - Vignesh Mainframe Admin -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Scott Barry Sent: 16 April 2014 14:27 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address Possibly the DFSORT INREC PARSE= feature might be usable to create your static-length, zone-decimal value field for the sort of a 'typical' IP address data-string in your CSV-format file. Scott Barry SBBWorks, Inc. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
This thread is giving me a bit of trouble. An IPA is internally a four-byte unsigned binary integer. By convention it is formatted externally as a sequence of four unsigned decimal integers separated by dots and with 'insignificant' leading, leftmost zeros suppressed. Each byte can of course have a decimal value d in the open interval 0 = d = 255. The IPA from which I am sending this is, for example, 24.62.42.175 InN order to make it sortable lexicographically just one operation is required: any and all short, less that three-digit, byte values need to be padded out on the left to three digits with zeros. For my IPA doing so yields 024.062.042.175 This operation is doable, if need be, in RPG; and it is of course doable in any adult programming language, readily for example in COBOL. Why all the pother? John Gilmore, Ashland, MA 01721 - USA -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
I should have. Problem is, I haven't coded for DFSORT much. :) - Vignesh Mainframe Admin -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Nims,Alva John (Al) Sent: 16 April 2014 15:24 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address I have a question for you, I believe you had stated before that there is additional information on each record following the IP address, so with your INREC statement, should you not have also coded for %04 a ENDBEFR= option to end that field? So wouldn't you code: INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(ENDBEFR=C',',FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) Al Nims Systems Admin/Programmer 3 Information Technology University of Florida (352) 273-1298 -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Sankaranarayanan, Vignesh Sent: Wednesday, April 16, 2014 9:57 AM To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address Yup. I used this but the file gets all messed up. I'm sure it's not the right SYSIN for what I need to do. INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) - Vignesh Mainframe Admin -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Scott Barry Sent: 16 April 2014 14:27 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Sorting CSV data that begins with an IP address Possibly the DFSORT INREC PARSE= feature might be usable to create your static-length, zone-decimal value field for the sort of a 'typical' IP address data-string in your CSV-format file. Scott Barry SBBWorks, Inc. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
On Wed, 16 Apr 2014 10:26:17 -0400, John Gilmore wrote: InN order to make it sortable lexicographically just one operation is required: any and all short, less that three-digit, byte values need to be padded out on the left to three digits with zeros. Be quite careful doing that. I once did that for legibility, and discovered that some system interfaces treat numbers with leading zeros as octal. Ugh! -- gil -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
On Wed, 16 Apr 2014 09:01:28 +, Sankaranarayanan, Vignesh wrote: Hello, I need help sorting some 5000 lines based on the first entry in a CSV - IP address. When sorting, I need the whole record to be involved in the sort, not just the IP's. Sample data: IP,MAC,Make-Model,SEPINFO,Type,Ping,Status,Printer,GRPNAME Warehouse #,Warehouse Name,Warehouse Terminal,Warehouse Code, Warehouse CICS xx.yyy.zz.aaa,Unknown,Unknown,OKI,Report,NOT Ok,Unknown,abcd,Not Defined xx.yyy.zzz.a,Unknown,Unknown,Not Defined,Report,NOT Ok,Unknown,efgh,Not Defined xx.yy.zzz.aaa,bb:cc:dd:ee:ff:gg,ZTC GK420d,Not Defined,Report,Ok,Online,qwer,WHW Xx doesn't mean the same digit; just clearing out data. I require the sort to work for any IP address, be it 1 or 2 or 3 characters per octet. I'm working with this, and I haven't a clue on each of these keywords. If you guys were so kind as to help me with the SYSIN, I could save an hour or two of reading syntax rules. INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) Thanks in advance. - Vignesh If the file's RECFM is FB, with a record size of 150, this should work: INREC IFTHEN=(WHEN=(1,1,CH,EQ,C'I'), OVERLAY=(151:C'0 0 0 0 ')), IFTHEN=(WHEN=NONE, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(ENDBEFR=C',',FIXLEN=3)), OVERLAY=(151:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(151,3,A,154,3,A,157,3,A,160,3,A) OUTREC BUILD=(1,150) You would need to make adjustments in OVERLAY, FIELDS, and BUILD for a different record length. Bill -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
On Wed, 16 Apr 2014 14:17:42 +, Sankaranarayanan, Vignesh wrote: It's FB. - Vignesh Mainframe Admin With LRECL=133 try: INREC PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(ENDBEFR=C',',FIXLEN=3)), BUILD=(%01,UFF,M11,LENGTH=3, %02,UFF,M11,LENGTH=3, %03,UFF,M11,LENGTH=3, %04,UFF,M11,LENGTH=3, 1,133) SORT FIELDS=(1,12,ZD,A) OUTREC BUILD=(13,133) Norbert Friemel -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
Sankaranarayanan, Vignesh wrote: Hello, I need help sorting some 5000 lines based on the first entry in a CSV - IP address. When sorting, I need the whole record to be involved in the sort, not just the IP's. Sample data: IP,MAC,Make-Model,SEPINFO,Type,Ping,Status,Printer,GRPNAME Warehouse #,Warehouse Name,Warehouse Terminal,Warehouse Code, Warehouse CICS xx.yyy.zz.aaa,Unknown,Unknown,OKI,Report,NOT Ok,Unknown,abcd,Not Defined xx.yyy.zzz.a,Unknown,Unknown,Not Defined,Report,NOT Ok,Unknown,efgh,Not Defined xx.yy.zzz.aaa,bb:cc:dd:ee:ff:gg,ZTC GK420d,Not Defined,Report,Ok,Online,qwer,WHW Xx doesn't mean the same digit; just clearing out data. I require the sort to work for any IP address, be it 1 or 2 or 3 characters per octet. I'm working with this, and I haven't a clue on each of these keywords. If you guys were so kind as to help me with the SYSIN, I could save an hour or two of reading syntax rules. INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) Thanks in advance. - Vignesh Mainframe Admin Hello, I think this might do what you need. It was run with Syncsort 1.4.10R. I think there is nothing special that should prevent it from running under a recent DFSORT, but I don't have it available to test with. INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(ENDBEFR=C',',FIXLEN=3)), OVERLAY=(81:%01,SQZ=(SHIFT=RIGHT,LENGTH=3), %02,SQZ=(SHIFT=RIGHT,LENGTH=3), %03,SQZ=(SHIFT=RIGHT,LENGTH=3), %04,SQZ=(SHIFT=RIGHT,LENGTH=3))), IFTHEN=(WHEN=INIT, FINDREP=(IN=C' ',OUT=C'0',STARTPOS=81,ENDPOS=93)) * SORT FORMAT=CH,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) * * You may be able to change the sort to the following * * SORT FORMAT=CH,FIELDS=(81,12,A) Alan -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
Vignesh, The following DFSORT/ICETOOL JCL will give you the desired results. I assumed that you have 1 header record which you want to retain as is //STEP0100 EXEC PGM=ICETOOL //TOOLMSG DD SYSOUT=* //DFSMSG DD SYSOUT=* //IN DD * IP,MAC,MAKE-MODEL,SEPINFO,TYPE,PING,STATUS,PRINTER,GRPNAME WA 192.23.55.223,UNKNOWN,UNKNOWN,OKI,REPORT,NOT OK,UNKNOWN,ABCD, 192.1.3.28,UNKNOWN,UNKNOWN,NOT DEFINED,REPORT,NOT OK,UNKNOWN, 23.192.53.1,AAA,BB:CC:DD:EE:FF:GG,ZTC GK420D,NOT //OUT DD SYSOUT=* //TOOLIN DD * DATASORT FROM(IN) TO(OUT) HEADER USING(CTL1) //CTL1CNTL DD * INREC PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(ENDBEFR=C',',FIXLEN=3)), OVERLAY=(134:%01,UFF,EDIT=(TTT), %02,UFF,EDIT=(TTT), %03,UFF,EDIT=(TTT), %04,UFF,EDIT=(TTT)) SORT FIELDS=(134,12,CH,A) OUTFIL BUILD=(1,133) //* The output from this job is IP,MAC,MAKE-MODEL,SEPINFO,TYPE,PING,STATUS,PRINTER,GRPNAME WAREHOUSE 23.192.53.1,AAA,BB:CC:DD:EE:FF:GG,ZTC GK420D,NOT 192.1.3.28,UNKNOWN,UNKNOWN,NOT DEFINED,REPORT,NOT OK,UNKNOWN,EFGH,NOT 192.23.55.223,UNKNOWN,UNKNOWN,OKI,REPORT,NOT OK,UNKNOWN,ABCD,NOT DEFINED If you don't have the header then, you can use the following //STEP0100 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //SORTIN DD * 192.23.55.223,UNKNOWN,UNKNOWN,OKI,REPORT,NOT OK,UNKNOWN,ABCD, 192.1.3.28,UNKNOWN,UNKNOWN,NOT DEFINED,REPORT,NOT OK,UNKNOWN, 23.192.53.1,AAA,BB:CC:DD:EE:FF:GG,ZTC GK420D,NOT //SORTOUT DD SYSOUT=* //SYSINDD * INREC PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(ENDBEFR=C',',FIXLEN=3)), OVERLAY=(134:%01,UFF,EDIT=(TTT), %02,UFF,EDIT=(TTT), %03,UFF,EDIT=(TTT), %04,UFF,EDIT=(TTT)) SORT FIELDS=(134,12,CH,A) OUTREC BUILD=(1,133) //* The output will be the same except you don't have the header. Further if you have any questions please let me know. Thanks, Kolusu DFSORT Development IBM Corporation IBM Mainframe Discussion List IBM-MAIN@listserv.ua.edu wrote on 04/16/2014 02:01:28 AM: From: Sankaranarayanan, Vignesh Vignesh.V.Sankaranarayanan@MARKS- AND-SPENCER.COM To: IBM-MAIN@listserv.ua.edu, Date: 04/16/2014 02:12 AM Subject: Sorting CSV data that begins with an IP address Sent by: IBM Mainframe Discussion List IBM-MAIN@listserv.ua.edu Hello, I need help sorting some 5000 lines based on the first entry in a CSV - IP address. When sorting, I need the whole record to be involved in the sort, not just the IP's. Sample data: IP,MAC,Make-Model,SEPINFO,Type,Ping,Status,Printer,GRPNAME Warehouse #,Warehouse Name,Warehouse Terminal,Warehouse Code, Warehouse CICS xx.yyy.zz.aaa,Unknown,Unknown,OKI,Report,NOT Ok,Unknown,abcd,Not Defined xx.yyy.zzz.a,Unknown,Unknown,Not Defined,Report,NOT Ok,Unknown,efgh,Not Defined xx.yy.zzz.aaa,bb:cc:dd:ee:ff:gg,ZTC GK420d,Not Defined,Report,Ok,Online,qwer,WHW Xx doesn't mean the same digit; just clearing out data. I require the sort to work for any IP address, be it 1 or 2 or 3 characters per octet. I'm working with this, and I haven't a clue on each of these keywords. If you guys were so kind as to help me with the SYSIN, I could save an hour or two of reading syntax rules. INREC IFTHEN=(WHEN=INIT, PARSE=(%01=(ENDBEFR=C'.',FIXLEN=3), %02=(ENDBEFR=C'.',FIXLEN=3), %03=(ENDBEFR=C'.',FIXLEN=3), %04=(FIXLEN=3)), OVERLAY=(81:%01,%02,%03,%04)) SORT FORMAT=UFF,FIELDS=(81,3,A,84,3,A,87,3,A,90,3,A) OUTREC BUILD=(1,133) Thanks in advance. - Vignesh Mainframe Admin MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
If the data is processed by a REXX routine after the sort anyway, why not do this transformation before the sort with another REXX routine? Should be a piece of cake ... Then the sort ... and if the leading zeroes indeed need to be removed again, you can do this in the REXX routine which runs after the sort. If the file is not very large, even the sorting can be done by clever usage of a REXX stem variable ... put the whole thing in a REXX stem, do all the modifications and sorting there and put it out in the desired format and order. I've done this very often even on Windows, to get readable reports out of unstructured test data ... BTW: 35th birthday of REXX some days ago ... many thanks to Mike Cowlishaw ! Kind regards Bernd Am 16.04.2014 16:26, schrieb John Gilmore: This thread is giving me a bit of trouble. An IPA is internally a four-byte unsigned binary integer. By convention it is formatted externally as a sequence of four unsigned decimal integers separated by dots and with 'insignificant' leading, leftmost zeros suppressed. Each byte can of course have a decimal value d in the open interval 0 = d = 255. The IPA from which I am sending this is, for example, 24.62.42.175 InN order to make it sortable lexicographically just one operation is required: any and all short, less that three-digit, byte values need to be padded out on the left to three digits with zeros. For my IPA doing so yields 024.062.042.175 This operation is doable, if need be, in RPG; and it is of course doable in any adult programming language, readily for example in COBOL. Why all the pother? John Gilmore, Ashland, MA 01721 - USA -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
Bernd, The REXX processing after sort is not as much a transformation as it is a search for the next available value from an IP block. - Vignesh Mainframe admin On Apr 16, 2014 6:24:08 PM, Bernd Oppolzer bernd.oppol...@t-online.de wrote: If the data is processed by a REXX routine after the sort anyway, why not do this transformation before the sort with another REXX routine? Should be a piece of cake ... Then the sort ... and if the leading zeroes indeed need to be removed again, you can do this in the REXX routine which runs after the sort. If the file is not very large, even the sorting can be done by clever usage of a REXX stem variable ... put the whole thing in a REXX stem, do all the modifications and sorting there and put it out in the desired format and order. I've done this very often even on Windows, to get readable reports out of unstructured test data ... BTW: 35th birthday of REXX some days ago ... many thanks to Mike Cowlishaw ! Kind regards Bernd Am 16.04.2014 16:26, schrieb John Gilmore: This thread is giving me a bit of trouble. An IPA is internally a four-byte unsigned binary integer. By convention it is formatted externally as a sequence of four unsigned decimal integers separated by dots and with 'insignificant' leading, leftmost zeros suppressed. Each byte can of course have a decimal value d in the open interval 0 = d = 255. The IPA from which I am sending this is, for example, 24.62.42.175 InN order to make it sortable lexicographically just one operation is required: any and all short, less that three-digit, byte values need to be padded out on the left to three digits with zeros. For my IPA doing so yields 024.062.042.175 This operation is doable, if need be, in RPG; and it is of course doable in any adult programming language, readily for example in COBOL. Why all the pother? John Gilmore, Ashland, MA 01721 - USA -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
Vignesh, Did you try the JCL which I showed? It should give the desired results. If you are NOT getting the results, then please show me a sample of input and I will show you how it can be done. Bernd, DFSORT has rich parsing and editing capability that you can parse the IP address and pad with leading zeroes and then perform a sort without altering the original record. I already showed an example of that. Thanks, Kolusu DFSORT Development IBM Corporation IBM Mainframe Discussion List IBM-MAIN@listserv.ua.edu wrote on 04/16/2014 10:29:25 AM: From: Sankaranarayanan, Vignesh Vignesh.V.Sankaranarayanan@MARKS- AND-SPENCER.COM To: IBM-MAIN@listserv.ua.edu, Date: 04/16/2014 10:29 AM Subject: Re: Sorting CSV data that begins with an IP address Sent by: IBM Mainframe Discussion List IBM-MAIN@listserv.ua.edu Bernd, The REXX processing after sort is not as much a transformation as it is a search for the next available value from an IP block. - Vignesh Mainframe admin On Apr 16, 2014 6:24:08 PM, Bernd Oppolzer bernd.oppol...@t-online.de wrote: If the data is processed by a REXX routine after the sort anyway, why not do this transformation before the sort with another REXX routine? Should be a piece of cake ... Then the sort ... and if the leading zeroes indeed need to be removed again, you can do this in the REXX routine which runs after the sort. If the file is not very large, even the sorting can be done by clever usage of a REXX stem variable ... put the whole thing in a REXX stem, do all the modifications and sorting there and put it out in the desired format and order. I've done this very often even on Windows, to get readable reports out of unstructured test data ... BTW: 35th birthday of REXX some days ago ... many thanks to Mike Cowlishaw ! Kind regards Bernd Am 16.04.2014 16:26, schrieb John Gilmore: This thread is giving me a bit of trouble. An IPA is internally a four-byte unsigned binary integer. By convention it is formatted externally as a sequence of four unsigned decimal integers separated by dots and with 'insignificant' leading, leftmost zeros suppressed. Each byte can of course have a decimal value d in the open interval 0 = d = 255. The IPA from which I am sending this is, for example, 24.62.42.175 InN order to make it sortable lexicographically just one operation is required: any and all short, less that three-digit, byte values need to be padded out on the left to three digits with zeros. For my IPA doing so yields 024.062.042.175 This operation is doable, if need be, in RPG; and it is of course doable in any adult programming language, readily for example in COBOL. Why all the pother? John Gilmore, Ashland, MA 01721 - USA -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
It's probable DFSORT could do that too. Consider the value of IFTHEN and ADD,+1 in OUTREC to increment and check the bounds of the block haven't been reached. But I accept that might be a fetishistic approach: Turning everything into something to be solved by DFSORT. Cheers, Martin Martin Packer, zChampion, Principal Systems Investigator, Worldwide Banking Center of Excellence, IBM +44-7802-245-584 email: martin_pac...@uk.ibm.com Twitter / Facebook IDs: MartinPacker Blog: https://www.ibm.com/developerworks/mydeveloperworks/blogs/MartinPacker From: Sankaranarayanan, Vignesh vignesh.v.sankaranaraya...@marks-and-spencer.com To: IBM-MAIN@listserv.ua.edu Date: 16/04/2014 18:29 Subject:Re: Sorting CSV data that begins with an IP address Sent by:IBM Mainframe Discussion List IBM-MAIN@listserv.ua.edu Bernd, The REXX processing after sort is not as much a transformation as it is a search for the next available value from an IP block. - Vignesh Mainframe admin On Apr 16, 2014 6:24:08 PM, Bernd Oppolzer bernd.oppol...@t-online.de wrote: If the data is processed by a REXX routine after the sort anyway, why not do this transformation before the sort with another REXX routine? Should be a piece of cake ... Then the sort ... and if the leading zeroes indeed need to be removed again, you can do this in the REXX routine which runs after the sort. If the file is not very large, even the sorting can be done by clever usage of a REXX stem variable ... put the whole thing in a REXX stem, do all the modifications and sorting there and put it out in the desired format and order. I've done this very often even on Windows, to get readable reports out of unstructured test data ... BTW: 35th birthday of REXX some days ago ... many thanks to Mike Cowlishaw ! Kind regards Bernd Am 16.04.2014 16:26, schrieb John Gilmore: This thread is giving me a bit of trouble. An IPA is internally a four-byte unsigned binary integer. By convention it is formatted externally as a sequence of four unsigned decimal integers separated by dots and with 'insignificant' leading, leftmost zeros suppressed. Each byte can of course have a decimal value d in the open interval 0 = d = 255. The IPA from which I am sending this is, for example, 24.62.42.175 InN order to make it sortable lexicographically just one operation is required: any and all short, less that three-digit, byte values need to be padded out on the left to three digits with zeros. For my IPA doing so yields 024.062.042.175 This operation is doable, if need be, in RPG; and it is of course doable in any adult programming language, readily for example in COBOL. Why all the pother? John Gilmore, Ashland, MA 01721 - USA -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN MARKSANDSPENCER.COM Unless otherwise stated above: Marks and Spencer plc Registered Office: Waterside House 35 North Wharf Road London W2 1NW Registered No. 214436 in England and Wales. Telephone (020) 7935 4422 Facsimile (020) 7487 2670 www.marksandspencer.com Please note that electronic mail may be monitored. This e-mail is confidential. If you received it by mistake, please let us know and then delete it from your system; you should not copy, disclose, or distribute its contents to anyone nor act in reliance on this e-mail, as this is prohibited and may be unlawful. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN Unless stated otherwise above: IBM United Kingdom Limited - Registered in England and Wales with number 741598. Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6 3AU -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
The solution that someone finds to a given problem depends on - the problem, of course - the tools or skills he or she has available For me, doing this task with DFSORT would involve long time of manual reading, and maybe in the end I would find the solution. With my given skills and tools, other solutions come to mind: - REXX programming (I have tons of REXX functions reading a file, doing some reformatting or parsing and writing it out again; I would search one which is similar, copy and change it and be happy - should be minutes, not hours) - programming in C or PL/1 or even ASSEMBLER - loading the CSV to a DB2 table (I have a tool to do this) and do the manipulation of the IP address and the sorting there (strange idea, but it would work, too) For me, given my actual skill situation, all those approaches are easier (faster) than the DFSORT approach. This is my very personal situation; others may have other situations. You should in the end FIND a solution, and in a minimum of time. Regarding DFSORT: Of course, I use DFSORT sometimes, if I have to sort VERY LARGE files or if I am building job control for production work, but then there are normally fixed sort field positions in the file; I never had the need so far to dig deeper into the more sophisticated features of DFSORT (I know that there are many). Kind regards Bernd Am 16.04.2014 22:48, schrieb Martin Packer: It's probable DFSORT could do that too. Consider the value of IFTHEN and ADD,+1 in OUTREC to increment and check the bounds of the block haven't been reached. But I accept that might be a fetishistic approach: Turning everything into something to be solved by DFSORT. Cheers, Martin Martin Packer, zChampion, Principal Systems Investigator, Worldwide Banking Center of Excellence, IBM +44-7802-245-584 email: martin_pac...@uk.ibm.com Twitter / Facebook IDs: MartinPacker Blog: https://www.ibm.com/developerworks/mydeveloperworks/blogs/MartinPacker -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Sorting CSV data that begins with an IP address
This serves as another tool for the job example. The sort in *nux is as simple as: sort -n -t . -k 1,1 -k 2,2 -k 3,3 -k 4,4 ipaddress.file How easy is that ?. Even OMVS has sort that should handle that. For the real output required, I'd pipe the output of that to gawk and use an associative array indexed on the first three octets of the IP address. Looks like the OMVS awk is missing the GNU extension of match() to use an array for splitting the IP address, but that can be worked around. REXX ? phooey. Shane ... -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN