MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Joinkeys to remove records from VSAM
Goto page 1, 2  Next
 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Mon Nov 24, 2014 11:25 am    Post subject: Joinkeys to remove records from VSAM Reply with quote

I need to delete a vsam file records post (1,1) record A,B,C
Sequential read vsam1 match with VSAM2, If Unmatched remove the record from vsam file and move it to another file. Exclude these records from deletion AA0035,BB0882,CC0225,DD0089 Position(2,6).

Tried following Sort.
Couple of issue.

1) I have duplicates in output, hence incldue SORTFIELDS. Please
2) I have Empty record a first in VSAM1CPY .

Please advice

Code:

//S2   EXEC  PGM=SORT                                             
//SYSOUT DD SYSOUT=*
//TEMP1 DD SYSOUT=*                                               
//SORTJNF1 DD DSN=VSAM1,DISP=SHR(VSAM/AVG 450 MAX 450)           
//SORTJNF2 DD DSN=VSAM2,DISP=SHR(VSAM/AVG 3568 MAX 3568)             
//SORTOUT DD DSN=VSAM1CPY,DISP=OLD               
//OUT2    DD DSN=VSAM1RJT,DISP=OLD           
//SYSIN    DD *                                           
  OPTION DYNALLOC=(,16)                                   
  JOINKEYS FILE=F1,FIELDS=(2,11,A),TYPE=F                 
  JOINKEYS FILE=F2,FIELDS=(1,11,A),TYPE=F                 
  REFORMAT FIELDS=(F1:1,450,?)                           
  JOIN UNPAIRED F1                                       
  SUM FIELDS=NONE                                         
  SORT FIELDS=(1,12,CH,A)                                 
  OUTFIL  BUILD(1,450),                                   
          OMIT(1,1,SS,EQ,C'A,B,C',AND,                   
               451,1,CH,EQ,C'1',AND,                     
               2,6,SS,NE,C'AA0035,BB0882,CC0225,DD0089') 
  OUTFIL  FNAMES=OUT2,BUILD(1,450),                       
          INCLUDE(1,1,SS,EQ,C'A,B,C',AND,               
               451,1,CH,EQ,C'1',AND,                     
               2,6,SS,NE,C'AA0035,BB0882,CC0225,DD0089')
  OUTFIL  INCLUDE(1,1,SS,EQ,C'A,B,C',AND,                   
                  451,1,CH,EQ,C'1',AND,                         
                  2,6,SS,NE,C'AA0035,BB0882,CC0225,DD0089'),   
       FNAMES=TEMP1,HEADER1=('MF11 FILE CLEANUP'),             
      SECTIONS=(1,1,                                           
      TRAILER3=(5:C'RECORD TYPE ',1,1,C' DELETED ',             
      COUNT=(EDIT=(III,III,III)))),                             
      TRAILER1=(/,                                             
                  03:'TOTAL NUMBER OF RECORDS  DELETED :   ',   
                  COUNT=(EDIT=(III,III,III))),NODETAIL         

/*                                                       
//JNF1CNTL DD *                                           
  OPTION DYNALLOC=(,16)                                   
//JNF2CNTL DD *                                           
  OPTION DYNALLOC=(,16)       
  INREC BUILD=(1,11)


Thanks
Magesh
Back to top
View user's profile Send private message
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Mon Nov 24, 2014 11:28 am    Post subject: Reply with quote

Typo in Issues.

1. I am getting duplicate records in output. Please advice, why I am getting it, My first file is key'ed file having no duplicates and i am just moving the same file to output.
2. Getting an empty record @ first in the output. But the vsam file 1 dosent have empty record.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose

PostPosted: Mon Nov 24, 2014 11:54 am    Post subject: Reply with quote

Magesh_J wrote:
Typo in Issues.
1. I am getting duplicate records in output. Please advice, why I am getting it, My first file is key'ed file having no duplicates and i am just moving the same file to output.


Magesh_J,

In Joinkeys , the keys will be treated as binary, so they must be "normalized". For example, if the keys are actually zoned decimal, they must have all C and D signs, or all F and D signs. You can use an INREC statement in JNF1CNTL and/or JNF2CNTL to normalize the keys for the F1 file and/or F2 file, respectively, if appropriate.

I am guessing that is the reason for your duplicates. What is the COBOL definition of the keys you are matching? X(11)? or they numeric?


Magesh_J wrote:

2. Getting an empty record @ first in the output. But the vsam file 1 dosent have empty record.


Magesh_J,

Look at the JOIN UNPAIRED statement, You missed a COMMA in between them and DFSORT treated F1 as a comment and you are getting unmatched records from File 2. Since your reformat statement doesn't write anything from file 2 you are looking at a record with all spaces. Look at the indicator value in position 451 and you will notice that it has 2.


You also need to have the VSAM cluster defined as REUSE in order to write it to Output as well as VSAMIO parm in DFSORT.
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Mon Nov 24, 2014 12:03 pm    Post subject: Reply with quote

Kolusu,

Thanks for looking into it.

Please advice how to normalize
VSAM1
Code:

10 LAYOUT-TYPE                X         
10 LAYOUT-FIELD1              XX         
10 LAYOUT-FIELD2              X(4)       
10 LAYOUT-FIELD3              X(5)       

VSAM2
Code:

10 LAYOUT-FIELD1              XX         
10 LAYOUT-FIELD2              X(4)       
10 LAYOUT-FIELD3              9(5)

Regards,
Magesh
Back to top
View user's profile Send private message
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Mon Nov 24, 2014 12:13 pm    Post subject: Reply with quote

Kolusu,

Worked great.

The comma i missed is the issue, After changing it, No duplicates and No space record now.

Thanks very much.

Regards
Magesh
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose

PostPosted: Mon Nov 24, 2014 12:34 pm    Post subject: Reply with quote

Magesh_J,

Glad you got it working. Did you remove the SORT FIELDS and SUM FIELDS statements on the main task?

As for normalizing your LAYOUT-FIELD3 might cause a problem in future if there is a sign overpunch for 9(5). You can simply change JNF2CNTL to

Code:

//JNF2CNTL DD *                                           
  OPTION DYNALLOC=(,16)       
  INREC BUILD=(1,6,7,5,ZD,EDIT=(TTTTT))
//*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Mon Nov 24, 2014 12:55 pm    Post subject: Reply with quote

Hi Kolusu,

Yes, I removed, We will not store any postive or negative value in the variable. it is unsigned 9(5).

Any way for safety purpose i will include it.


Thanks
Magesh
Back to top
View user's profile Send private message
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Tue Nov 25, 2014 11:09 am    Post subject: Reply with quote

Hi,

Please advise, where i am going wrong.

Code:

//S2   EXEC  PGM=SORT                                             
//SYSOUT DD SYSOUT=*                                             
//SORTJNF1 DD DSN=VSAM1,DISP=SHR(KEY 0 27) (AVG 80 MAX 612)       
//SORTJNF2 DD DSN=VSAM2,DISP=SHR(3568 3568) (AVG 0 MAX 16)             
//TEMP1 DD SYSOUT=*                                               
//REST  DD DSN=RESTFILE,DISP=(NEW,CATLG,DELETE),     
//         SPACE=(CYL,(10,10),RLSE)                               
//SORTOUT DD DSN=VSAMOUT,DISP=OLD             
//OUT2    DD DSN=VSAMOUT.NOT,DISP=OLD         
//SYSIN    DD *                                                   
  OPTION DYNALLOC=(,16)                                           
  JOINKEYS FILE=F1,FIELDS=(6,11,A),TYPE=V                         
  JOINKEYS FILE=F2,FIELDS=(1,11,A),TYPE=F                         
  REFORMAT FIELDS=(F1:1,4,?,F1:5)                                 
  JOIN UNPAIRED,F1                                               
  SORT FIELDS=(6,27,CH,A)                                         
  OUTFIL  BUILD(1,4,6),                                           
          OMIT(6,1,SS,EQ,C'E,F,G,H,I,J,0,3,5',AND,               
               5,1,CH,EQ,C'1')                                   
  OUTFIL  FNAMES=OUT2,                                           
          BUILD(1,4,6),                                           
          INCLUDE((5,1,CH,EQ,C'1',AND,                             
                   35,5,CH,NE,C'TUMMY',AND,                       
                   6,1,CH,EQ,C'0'),                               
                   OR,                                             
                  (6,1,SS,EQ,C'E,F,G,H,I,J,0,3,5',AND,             
                   5,1,CH,EQ,C'1'))                               
  OUTFIL  INCLUDE((5,1,CH,EQ,C'1',AND,                             
                   35,5,CH,NE,C'TUMMY',AND,                       
                   6,1,CH,EQ,C'0'),                               
                   OR,                                             
                  (6,1,SS,EQ,C'E,F,G,H,I,J,0,3,5',AND,             
                   5,1,CH,EQ,C'1')),                               
          FNAMES=TEMP1,HEADER1=('TEST FILE CLEANUP'),             
          SECTIONS=(6,1,                                           
                    TRAILER3=(5:C'RECORD TYPE ',                   
                              6,1,                                 
                              C' DELETED ',                       
                              COUNT=(EDIT=(III,III,III)))), 
          TRAILER1=(/,                                                 
                    03:'TOTAL NUMBER OF RECORDS  DELETED :   ',         
                    COUNT=(EDIT=(III,III,III))),NODETAIL               
  OUTFIL  FNAMES=REST,SAVE,BUILD=(1,4,6)                               
/*                                                                     
//JNF1CNTL DD *                                                         
  OPTION DYNALLOC=(,16)                                                 
  INREC OVERLAY=(11:11,5,ZD,EDIT=(TTTTT))                               
/*                                                                     
//JNF2CNTL DD *                                                         
  OPTION DYNALLOC=(,16)                                                 
  INREC BUILD=(1,6,7,5,ZD,EDIT=(TTTTT))                                 
/* 


Error message

Code:

ICE750I 0 DC 0 TC 0 CS DSVXX KSZ 31 VSZ 31                                     
ICE752I 0 FSZ=74372614 RE  IGN=0 C  AVG=311 311  WSP=30041743 E  DYN=542910 5666
ICE231I 0 STORAGE USED FOR OUTFIL : BELOW 16M = 47104, ABOVE 16M = 2408448     
ICE210I 0 SORTOUT  : VSAM USED, LRECL = 616, BLKSIZE = 22528, TYPE = V         
ICE210I 0 OUT2     : VSAM USED, LRECL = 616, BLKSIZE = 22528, TYPE = V         
ICE210I 0 TEMP1    : BSAM USED, LRECL = 618, BLKSIZE = 622, TYPE = VA           
ICE210I 0 REST     : EXCP USED, LRECL = 616, BLKSIZE = 27998, TYPE = VB   (SDB)
ICE077A 9 VSAM OUTPUT ERROR L(12) SORTOUT                                       
ICE751I 1 D8-BASE   D4-K59451 EA-K57947 CB-K64631 F1-K38900 E8-K61438           
ICE052I 0 END OF DFSORT                                                         


Thanks
Magesh
Back to top
View user's profile Send private message
Terry_Heinze
Supermod


Joined: 31 May 2004
Posts: 391
Topics: 4
Location: Richfield, MN, USA

PostPosted: Tue Nov 25, 2014 12:10 pm    Post subject: Reply with quote

This might help:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CM20/2.2.76?SHELF=&DT=20060721170811&CASE=
_________________
....Terry
Back to top
View user's profile Send private message Send e-mail
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Tue Nov 25, 2014 12:46 pm    Post subject: Reply with quote

ThanksTerry for the kind response



tested 10k records it ran fine.. testing with full file it is failing...

is it possible to identify which record creating problem ?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose

PostPosted: Tue Nov 25, 2014 12:46 pm    Post subject: Reply with quote

Magesh_J,

Apart from the obvious error ICE077A which terry pointed to, are you sure you had JNF1CNTL coded correctly? Based on your key definitions earlier, If your key in SORTJNF1 starts in position 6 shouldn't the numeric key which you are normalizing be at position 12 instead of 11?
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose

PostPosted: Tue Nov 25, 2014 12:57 pm    Post subject: Reply with quote

Magesh_J wrote:
ThanksTerry for the kind response

tested 10k records it ran fine.. testing with full file it is failing...

is it possible to identify which record creating problem ?


Magesh_J,


Is the output VSAM file EMPTY before you overlay the contents of it? If not is the VSAM defined as REUSE?
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Tue Nov 25, 2014 1:52 pm    Post subject: Reply with quote

My First step is a idcams step, delete define the file.

Code:

//S1  EXEC PGM=IDCAMS                                         
//SYSPRINT DD SYSOUT=*                                         
//SYSIN    DD  *               
 DELETE    VSAMOUT CL PRG                 
  DELETE    VSAMOUT.NOT CL PRG             
  IF MAXCC LE 8 THEN SET MAXCC = 0                             
  DEFINE    CLUSTER -                                         
            (  NAME ('VSAMOUT') -           
               MODEL('VSAM1'))           
  DEFINE    CLUSTER -                                         
            (  NAME ('VSAMOUT.NOT') -       
               MODEL('VSAM1'))   


I think 12th position might be an issue. Thanks for checking this, Changed it and running it, it is consuming 1132 CPU Seconds, it will take a while for result, i.e it is failing only after 1132 CU seconds . Is they any way we can optimize it ?

Regards,
Magesh
Back to top
View user's profile Send private message
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Tue Nov 25, 2014 2:01 pm    Post subject: Reply with quote

Hi,

Operator requested to cancelled my job, thinking i am coding it wrong, i could test it after 5 PM CST.

Regards.
Magesh
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose

PostPosted: Tue Nov 25, 2014 2:12 pm    Post subject: Reply with quote

Magesh_J,

Can you re-run your Job adding the following
Code:

//DFSPARM DD *
  OPTION RESET,MANSIZE=6M
/*   

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group