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
 

 

Sort Query to fetch particular records

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

New User


Joined: 04 Feb 2016
Posts: 15
Location: Lithuania

PostPosted: Fri Mar 04, 2016 8:17 pm    Post subject: Sort Query to fetch particular records
Reply with quote

Hi All,

Is it possible to achieve the below:

Below is the sample input record and the output expected
Is this is possible using a sort jcl?
I just want the records at 1,5,10 etc. Since the file is in production I dont have access to view it directly.
INPUT: ABCXYZ.UTIL.TEST
AB
AB
DC
DC
DC
PQ
PQ
SR
RQ
CD
AB
CD
CD
AB


Output expected:

1 AB 4
5 DC 3
10 CD 3

Where 4, 3, 3 are the number of occurrences
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Fri Mar 04, 2016 8:34 pm    Post subject: Reply to: Sort Query
Reply with quote

is the 1,5,10 sequence fixed
and the counting until the end of file ?
Back to top
View user's profile Send private message
SanaR

New User


Joined: 04 Feb 2016
Posts: 15
Location: Lithuania

PostPosted: Fri Mar 04, 2016 8:37 pm    Post subject:
Reply with quote

Yes the sequence number is fixed but it not multiple. For example it can also be 1,6,8,11 etc until end of file. I also want the last record of the file.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Fri Mar 04, 2016 8:50 pm    Post subject: Reply to: Sort Query to fetch particular records
Reply with quote

Quote:
For example it can also be 1,6,8,11


tell how the sequence is determined!
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Mar 04, 2016 9:25 pm    Post subject: Reply to: Sort Query to fetch particular records
Reply with quote

Make use of SECTIONS and SEQNUM and filter whatever records you like.

Something on the lines of:
Code:
 INREC IFTHEN=(WHEN=INIT,OVERLAY=(3:SEQNUM,4,ZD))
 OUTFIL SECTIONS=(1,2,
      TRAILER3=(1,2,X,3,4,X,'COUNT = ',COUNT))
 OPTION COPY   

.
Back to top
View user's profile Send private message
SanaR

New User


Joined: 04 Feb 2016
Posts: 15
Location: Lithuania

PostPosted: Sun Mar 06, 2016 2:04 am    Post subject:
Reply with quote

Hi Erico,

the sequence is assumed say 1, 6 10 and last records. There is no particular format.
Back to top
View user's profile Send private message
SanaR

New User


Joined: 04 Feb 2016
Posts: 15
Location: Lithuania

PostPosted: Sun Mar 06, 2016 2:06 am    Post subject:
Reply with quote

Hi Rahul,

Thanks for your reply but this doesn't give me the record on 1,6,10th or the last positions.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sun Mar 06, 2016 3:53 am    Post subject: Reply to: Sort Query to fetch particular records
Reply with quote

SanaR,

It should strike you, forcefully, that you know what you want, and no-one else does.

If you cannot describe exactly what it is is that you want, with sample input and expected output which matches your exact description, the topic will be locked, as it can go nowhere. It is up to you.
Back to top
View user's profile Send private message
SanaR

New User


Joined: 04 Feb 2016
Posts: 15
Location: Lithuania

PostPosted: Sun Mar 06, 2016 12:04 pm    Post subject: Reply to: Sort Query to fetch particular records
Reply with quote

Hi Bill,

I have clearly mentioned the sample and expected output. And I have also mentioned that the file is in prod so I cannot view it and the only thing that can be assumed is record at line number say 1,6,10 and last record
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Sun Mar 06, 2016 5:01 pm    Post subject:
Reply with quote

You will have to get a copy of the dataset (not 'file') cut and paste some of the data (after concealing sensitive data) and describe what you want more fully. You will need a copy anyway so that you can test the solution.

Do you want the 1st, 6th, 10th and last records no matter what their content? If not, what is the criteria? What are the dataset characteristics - RECFM and LRECL? What if there are 10 records? Do you want the 10th record twice? What if there are less than 10 records?
Back to top
View user's profile Send private message
SanaR

New User


Joined: 04 Feb 2016
Posts: 15
Location: Lithuania

PostPosted: Sun Mar 06, 2016 5:58 pm    Post subject:
Reply with quote

Thanks Nic for your reply.

The situation is I will be unloading the data from image copy of a table which has nearly 30 million records. Data set in which I will be unloading will be PS, FB, 20 bytes(20 bytes can change) . Then I would like to fetch records at line 1, 15000000, 29923210 and last records. The only thing I will know is these line numbers and that the records are 30M. The records key is from 1-19. Please let me know if I am still unclear.
Sorry not able to paste the sample file image. so pasting this:


Code:
Command ===>,
=COLS>,----+----1----+----
******,********************
000001,   9999920002155556,
000002,   9999920002155556,
000003,   9999920002155556,
000004,   9999920002155556,
000005,   9999920002155556,
000006,   9999920002155556,
000007,   9999920002155556,
000008,   9999920002155556,
000009,   9999920002155556,
000010,   9999920002155556,
000011,   9999920002155556,
000012,   9999920002155556,
000013,   9999920002155556,
000014,   9999920002155556,
000015,   9999920002155556,
000016,   9999920002155556,
000017,   9999920002155556,
000018,   9999920002155556,


thanks &deity ... image, jpeg, png just waste space and do not convey any additional info
post edited to use the code tags
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Sun Mar 06, 2016 9:37 pm    Post subject: Reply to: Sort Query to fetch particular records
Reply with quote

Quote:
I have clearly mentioned the sample and expected output.


You did not mention anything useful ...

Quote:
I just want the records at 1,5,10 etc.

and then
Quote:
For example it can also be 1,6,8,11

and then
Quote:
1, 15000000, 29923210 and last records


so You have no reason to complain ...

people replying on forums do it on their OWN time and FREE OF CHARGE
so if You do not want spend time helping the people who try to help You
by CLEARLY defining and explaining the requirements You will find that people will not be keen on helping YOU

so the real question is ...
are the record numbers FIXED in value and number or they can change ?
the solution will be different for each case
Back to top
View user's profile Send private message
SanaR

New User


Joined: 04 Feb 2016
Posts: 15
Location: Lithuania

PostPosted: Sun Mar 06, 2016 10:03 pm    Post subject:
Reply with quote

Apologies as this my first query and I appreciate everyone's time.

Yes the record no. is fixed in value and number. In the above response I have provided the input file to the sort and the expected output should be something like below:

Code:
LINE NUMBER          RECORD                COUNT OF RECORD
1                    9999920002155556      18
15000000             9999930002155555      25
29923210             9999960008888899      10
LAST RECORD          9999978888888899      16
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Mon Mar 07, 2016 12:42 am    Post subject: Reply to: Sort Query to fetch particular records
Reply with quote

It is difficult to tell (in your latest sample input you only show one key) but is your data in key sequence already? Your earlier example showed not.
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


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