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 

Load a table after removing Dups
Goto page 1, 2  Next
 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Mon Jun 11, 2012 2:05 pm    Post subject: Load a table after removing Dups Reply with quote
I have a load file say 'TAFE' which I have to use to load a prod table NVDLINA. The TAFE file has Employee numbers which are unique.
I cannot load it directly since the TAFE file has employee numbers which were already there in the prod table NVDLINA. so I have to remove the Emp numbers from the load file which were already there in the NVDLINA table.
Below are the steps I will be following to resolve it.

Step1: Unload the production table NVDLINA.
Step2: Concatenate the unload file and TAFE dropping the duplicates that are in the TAFE file (note: I don't want to delete the rows from the unload file)
Step3: load replace the table NVDXTDN with the new concatenate file.

Tafe File:(first 9 digit is the emp number)
Code:

********************************* Top of Data *********************************
000000000Y0001-01-01-00.00.00.000001
000000001Y0001-01-01-00.00.00.000001
000000007Y0001-01-01-00.00.00.000001
000000015Y0001-01-01-00.00.00.000001


Question: Is there any better way to do this?

For step 2: pls provide the sortcards\JCL.

I appreciate all the help.
Thanks for your time
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Mon Jun 11, 2012 2:57 pm    Post subject: Re: Load a table after removing Dups Reply with quote
vak255 wrote:
any better way to do this?


Vak255,

You don't need 3 steps. You can use "ENFORCE CONSTRAINTS" keyword on your LOAD card so that the duplicates will be rejected and go to the SYSDISC dataset.

ex:
Code:

LOAD DATA INDDN(SYSREC) RESUME YES
ENFORCE CONSTRAINTS
INTO TABLE your_table_name


Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Mon Jun 11, 2012 3:02 pm    Post subject: Reply with quote
Thanks so much, Kolusu.
I will check on it.
Back to top
View user's profile Send private message
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Tue Jun 12, 2012 11:13 am    Post subject: Reply with quote
Below is the LOAD step. Please let me know where i should specify the 'ENFORCE CONSTRAINTS'.
Code:

//*********************************************************************
//*    LOAD RESUME NVDLINA TABLE
//*********************************************************************
//*
//LOADDN EXEC DS51PTLT,
//            RESTART=,
//            CTLPFX=DS51,
//            APPLID=NVDB,
//            LOADMBR=NVDBDNPR,
//            LOADCND=4,
//            INPTDSN='NVDB.NVDLINA.TORESUME',
//            DISCDSP=KEEP,
//            BKUPDSN='NULLFILE',
//            CPY2DSN='NULLFILE',
//            ACRWMBR=NVDBDNFC
/*
//*********************************************************************
//*   CO51TEP2 - RUNS THE SPUFI:  'DS51.SQL.CONTROL(NVDBDNFX)'        *
//*********************************************************************
//CO51TEP2 EXEC CO51TEP2,
//             INPTDSN='DS51.SQL.CONTROL(NVDBDNFX)'   -- Update the NVDLINA table
//*
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Jun 12, 2012 11:35 am    Post subject: Reply with quote
vak255,

You need to see the expanded version of the PROC DS51PTLT. In the proc it should be executing PGM=DSNUTILB in there. You need to check the SYSIN DD for that statement which contains the LOAD card.

Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Tue Jun 12, 2012 12:00 pm    Post subject: Reply with quote
Thanks for the quick response.
I checked with my SME. He want to do this by the steps I mentioned, as we need to generate some reports as well. so I am going to use the below JCL. Below is the JCL. Like I said I want to concatenate the unload file and TAFE file. If there are any duplicates then it should choose the record from the unload file and ignore the record in TAFE file. please let me know of any corrections in the below JCL.

Code:

//STEP  EXEC  PGM=SORT 
//SORTOUT  DD SYSOUT=*
//SORTJNF1 DD unload file of the table
//SORTJNF2 DD  TAFE
//SORTOUT  DD  SYSOUT=*
//SYSIN  DD  *
 JOINKEYS FILE=F1,FIELDS=(1,09,A)
 JOINKEYS FILE=F2,FIELDS=(1,09,A)
 REFORMAT FIELDS=(F1:01,09,F1:10,36)   (need to check the LRECL)
SORT FIELDS=COPY
SUM FIELDS=None
//
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Jun 12, 2012 12:20 pm    Post subject: Reply with quote
vak255 wrote:
Thanks for the quick response.
I checked with my SME. He want to do this by the steps I mentioned, as we need to generate some reports as well. so I am going to use the below JCL. Below is the JCL. Like I said I want to concatenate the unload file and TAFE file. If there are any duplicates then it should choose the record from the unload file and ignore the record in TAFE file. please let me know of any corrections in the below JCL.


Vak255,

Use CODE tags instead of Quote tags. I can't be editing every post of yours.

Hmm My way or the Highway SME ? Why do you need to concatenate? if you insist the 3 step procedure.

1. Unload just the key from the table with an ORDER by on the key so that it is sorted.
2. Match it against TAFE file using Joinkeys (if your tafe file is also pre-sorted on the key , then add SORTED,NOSEQCK to INA also)

INA = your tafe file
INB = your db2 key unload file.


Code:

//STEP0100 EXEC PGM=SORT                             
//SYSOUT   DD SYSOUT=*                               
//INA      DD *                                     
----+----1----+----2----+----3----+----4----+----5---
000000000Y0001-01-01-00.00.00.000001                 
000000001Y0001-01-01-00.00.00.000001                 
000000007Y0001-01-01-00.00.00.000001                 
000000015Y0001-01-01-00.00.00.000001                 
//INB      DD *                                     
000000001                                           
000000020                                           
//SORTOUT  DD SYSOUT=*                               
//SYSIN    DD *                                     
  OPTION COPY                                       
  JOINKEYS F1=INA,FIELDS=(1,9,A)                     
  JOINKEYS F2=INB,FIELDS=(1,9,A),SORTED,NOSEQCK     
  JOIN UNPAIRED,F1,ONLY                             
//*


This will produce the output file as
Code:

000000000Y0001-01-01-00.00.00.000001
000000007Y0001-01-01-00.00.00.000001
000000015Y0001-01-01-00.00.00.000001


3. Now use this file to load the table with RESUME YES.


Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Tue Jun 12, 2012 1:33 pm    Post subject: Reply with quote
Thanks Kolusu. I will follow the above steps.

I will use CODE tag for all future response.
While responding I see Quote. It will be good to have the CODE as well.
Back to top
View user's profile Send private message
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Tue Jun 12, 2012 2:22 pm    Post subject: Reply with quote
I also need to remove keys with 000000000. I believe I could add OMIT



Code:

//SYSIN    DD *                                     
  OPTION COPY                                       
  JOINKEYS F1=INA,FIELDS=(1,9,A)                     
  JOINKEYS F2=INB,FIELDS=(1,9,A),SORTED,NOSEQCK     
  JOIN UNPAIRED,F1,ONLY                         
  OMIT=(1,9,CH,EQ,C'000000000')
//*
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Jun 12, 2012 2:40 pm    Post subject: Reply with quote
vak255 wrote:
I also need to remove keys with 000000000. I believe I could add OMIT


You can Use OMIT in 2 different places.

1. You can use JNF1CNTL to eliminate the keys with 000000000 even before the JOIN

Code:

//JNF1CNTL DD *               
  OMIT COND=(1,9,ZD,EQ,0)
//*

2. You can use OMIT COND after the join and eliminate the keys with 000000000.

Code:

//SYSIN    DD *                                     
  OPTION COPY                                       
  JOINKEYS F1=INA,FIELDS=(1,9,A)                     
  JOINKEYS F2=INB,FIELDS=(1,9,A),SORTED,NOSEQCK     
  JOIN UNPAIRED,F1,ONLY                         
  OMIT COND=(1,9,ZD,EQ,0)
//*
Back to top
View user's profile Send private message Send e-mail Visit poster's website
papadi
Supermod


Joined: 20 Oct 2009
Posts: 594
Topics: 1
PostPosted: Tue Jun 12, 2012 2:54 pm    Post subject: Reply with quote
Quote:
It will be good to have the CODE as well
.
Before the info you want "Coded" enter left-bracket code right-bracket and after the data you want coded enter left-bracket /code right-bracket
(like: [codx] the info to be coded [/codx] - spelling code correctly) .

Here is what this would produce
Code:
 the info to be coded


Or click the Code tag. . . in the full editor (if you access to this), not the Quick Reply
_________________
All the best,

di
Back to top
View user's profile Send private message
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Tue Jun 12, 2012 4:28 pm    Post subject: Reply with quote
Thanks Kolusu and di for all the help.
Back to top
View user's profile Send private message
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79
PostPosted: Tue Jun 12, 2012 5:01 pm    Post subject: Reply with quote
I am getting the below error messages.
Code:

//SYSIN    DD *
----+----1----+----2----+----3----+----4----+----5----+----6-
 OPTION COPY
 JOINKEYS F1=INA,FIELDS=(1,9,A)
 JOINKEYS F2=INB,FIELDS=(1,9,A),SORTED,NOSEQCK
 JOIN UNPAIRED,F1,ONLY

------- START OF UTILITY PROGRAM OUTPUT -------
----+----1----+----2----+----3----+----4----+----5----+----6-

-------- PROCESSING SYSIN --------
 OPTION COPY
 JOINKEYS F1=INA,FIELDS=(1,9,A)
E1 - DSS10065E - PARAMETER 'F1' IS UNIDENTIFIED.
 JOINKEYS F2=INB,FIELDS=(1,9,A),SORTED,NOSEQCK
E2 - DSS10065E - PARAMETER 'F2' IS UNIDENTIFIED.
E3 - DSS10066E - POSITIONAL PARAMETER 'NOSEQCK' IS NOT VALID.
Back to top
View user's profile Send private message
William Collins
Supermod


Joined: 03 Jun 2012
Posts: 437
Topics: 0
PostPosted: Tue Jun 12, 2012 5:13 pm    Post subject: Reply with quote
That's from your JCL checker nosing about without knowing enough about control cards. Just toss the job in and see if there are any real problems. I can't see any Smile
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Jun 12, 2012 5:14 pm    Post subject: Reply with quote
vak255 wrote:
I am getting the below error messages.


Vak255,

The JSCAN messages you show are NOT DFSORT messages and it is the JCL checker software which is way beyond keeping up with the latest features of DFSORT.
_________________
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 -> Database 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