How to write sort card?

In this Mainframe Forum - You can post your queries on JCL, OS/390 JCL, MVS JCL, z/OS JCL, JES2 & JES3

Moderators: Frank Yaeger, DikDude, Moderator Group

Post Reply
Veena gowda
Member
Posts: 4
Joined: Wed Oct 08, 2014 1:58 pm
Location: Mangalore

How to write sort card?

Post by Veena gowda » Wed Oct 08, 2014 2:24 pm

Hi all,

I am working on sort card for the first time, so I need help. I have to unload data from db2 and arrange it in format. I have written unload query but output is not in expected format. now I have to write sort step for that. Please help me in this.

Expected Output should be:
col1(length3) col2(length40)col3(length3)col4(padding char)col5(space) = total length of the file 50
001aaaaa 102byr
002abdddd 106byr
003dssdaaaaa 109byr


Current output:
col1(length3) col2(length40) col3(length5)col4(padding) = total length of the file is 51
1 aaaaa 102 byr
2 abdddd 106 byr
3 dssdaaaaa 109 byr

Can you please let me know how to arrange the current output file in expected form?

Thanks in advance
Regards,
Veena

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am

Re: How to write sort card?

Post by William Collins » Wed Oct 08, 2014 3:14 pm

You need to use the Code tags when posting something which requires the preservation of spacing. Use the Preview button to see what the post will look like.

It would seem likely that you can get the output you want from the Unload. Can you show the control cards you are using for that. and someone may be able to assist.

It is of course possible to reformat the data with SORT. You will need to use BUILD, it looks like you want leading zeros on your first field. It is not clear, due to the formatting problems, what you need for any other reformatting.

Veena gowda
Member
Posts: 4
Joined: Wed Oct 08, 2014 1:58 pm
Location: Mangalore

Post by Veena gowda » Thu Oct 09, 2014 3:48 pm

I was able to resolve it and now am not getting how to add leading zero's.
Below is the query and sort card which I have written.

Code: Select all

Code:
SELECT CAST(ID AS CHAR(3))          
       ,NAME                          
       ,CAST(MGR_ID AS CHAR(3))  
FROM EMP                    
WHERE ID > 0                        
ORDER BY ID                         
WITH UR;                                


SORT FIELDS=COPY                        
OUTREC FIELDS=(1,3,4,40,44,3,47:C'BYR') 

Output:
1  aaaaaa                  101BYR
2  BBBBB                   102BYR
3  CCCCC                   103BYR
4  DDDDD                  105BYR
ID should be 3 digits only and in output it should be like '001, 002 ... 099'. So until '99' rows I have to do padding of zeroes. Can you please tell me how to do it?
Regards,
Veena

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am

Post by William Collins » Thu Oct 09, 2014 5:25 pm

If you have lots of records, it is pointless making the change after you get to three digits. However, if the numbers are unique, you don't really have lots anyway, but....

Code: Select all

  OPTION COPY 
                                      
  INREC IFTHEN=(WHEN=INIT, 
                 OVERLAY=(47:C'BYR')),
        IFTHEN=(WHEN=(3,1,CH,EQ,C' '),
                 OVERLAY=(1,3,UFF, 
                           TO=ZD, 
                           LENGTH=3, 
                          51:C'X')) 
The BYR will be done to each record, the conversion only when necessary. The 51:C'X' is just to show you the conversion working only when needed.

NicC
Active Member
Posts: 650
Joined: Sun Jul 24, 2011 5:27 pm
Location: Down on the pig farm

Post by NicC » Sat Oct 11, 2014 12:48 pm

If you cast to CHAR it will produce '3' instead of 003 - standard rules across most languages. You need to cast to a numeric field type although I do ot know if any will retain leading zeros.

By the way, this is either a sort query or a DB2 query so why post in the JCL section of the forum when there are sections for both sort and DB2?
Regards
Nic

Veena gowda
Member
Posts: 4
Joined: Wed Oct 08, 2014 1:58 pm
Location: Mangalore

Post by Veena gowda » Mon Oct 13, 2014 11:56 am

Thanks, Collins :) Its working.

But I have one more question.


Code: Select all

input file:

0  aaaaa                   106
1  bbbbb                   103
2  cccccc                   0
3  ddddd                   102
102sfsfsf                   209 
103fbbfbf                  0
105ggggg                 0


SORT FIELDS=COPY                     
INREC IFTHEN=(WHEN=INIT,             
               OVERLAY=(47:C'BYR')), 
      IFTHEN=(WHEN=(3,1,CH,EQ,C' '), 
               OVERLAY=(1,3,UFF,     
                         TO=ZD,      
                         LENGTH=3,   
                         51:C'X')),
      IFTHEN=(WHEN=(44,1,CH,EQ,C'0'),   
               OVERLAY=(44:C'000')) 

output file:
000aaaaa                   106byr
001bbbbb                   103byr
002cccccc                   0   byr ---> for this the second if condition is not working
003ddddd                   0   byr ---> for this the second if condition is not working
102sfsfsf                    209byr 
103fbbfbf                   000byr
105ggggg                  000byr
   
but I want zero padding in the 3rd column when MGR_ID is having 0. Can you please suggest me how to do it?
Regards,
Veena

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am

Post by William Collins » Mon Oct 13, 2014 12:31 pm

IFTHEN processing stops when a condition is met. If you MGR is on a three-digit-id record, it will work (if you need to prove it to yourself, it is documented).

On your first contional IFHTEN, instead of )), to close it, use ),HIT=NEXT), which will cause it to continue looking for an IFTHEN with a condition.

Veena gowda
Member
Posts: 4
Joined: Wed Oct 08, 2014 1:58 pm
Location: Mangalore

Post by Veena gowda » Mon Oct 13, 2014 2:15 pm

Collins,

As you suggested, )HIT=NEXT), option is not working. Yes, MGR_ID is a 3 digit record field. Can you please suggest me how to solve this.

Code: Select all

INREC IFTHEN=(WHEN=INIT,                 
               OVERLAY=(47:C'BYR')),     
      IFTHEN=(WHEN=(3,1,CH,EQ,C' '),     
               OVERLAY=(1,3,UFF,         
                         TO=ZD,          
                         LENGTH=3),HIT=NEXT),     
      IFTHEN=(WHEN=(3,44,CH,EQ,C' '),      
               OVERLAY=(44,3,UFF,        
                         TO=ZD,          
                         LENGTH=3,       
                         51:C'X'))  
Can you please correct?
Regards,
Veena

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am

Post by William Collins » Mon Oct 13, 2014 4:55 pm

You have said that the field starts at position 3 and is 44 bytes long. For the condition to be true, that all needs to be space.

HIT=NEXT works.

Remember that I only put the C'X' in there so you could see what is happening. It is not part of the solution.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others