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 

DB2 Query converted to DFSORT

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
OSCORP
Beginner


Joined: 15 Oct 2004
Posts: 29
Topics: 8
PostPosted: Tue Feb 20, 2007 4:04 pm    Post subject: DB2 Query converted to DFSORT Reply with quote
Hi,

Can anyone help me with this?

I have a join query of 3 tables which is abending with -904. Some issues with Temp Workspace. Each table has about 2 to 8 million rows. Also it takes 4-5 hrs to complete.

The task is to achieve this in a simpler way by removing table download step and replacing it with HPUNLOAD utility and filter records using DFSORT

Current Query:

Code:

SELECT OPCB.PROV_ID,         
       OVTN_PROV_CONTR_ID,   
       OVTN_CONTR_SEQ_NBR,   
       OPCB.ADR_ID,         
       OPCB.ADR_TYP_CD,     
       OPCB.TAX_ID_NBR,     
       OPCB.TAX_ID_TYP_CD,   
       OVTN_PRC_PKG_CD,     
       OPCB.EFF_DT,         
       OPCB.CREAT_DT         
FROM  OVTN_PRV_CONTR_BIL OPCB         
     ,PROV                 PRV       
     ,PROV_TIN_ADR         PTA       
WHERE OPCB.PROV_ID       = PRV.PROV_ID       
  AND PRV.CANC_DT        > CURRENT DATE       
  AND OPCB.ADR_ID        = PTA.ADR_ID         
  AND OPCB.ADR_TYP_CD    = PTA.ADR_TYP_CD     
  AND OPCB.TAX_ID_NBR    = PTA.TAX_ID_NBR     
  AND OPCB.TAX_ID_TYP_CD = PTA.TAX_ID_TYP_CD 
  AND PTA.CANC_DT        > CURRENT DATE       
  ORDER BY OPCB.PROV_ID;                     



I have taken a dump of 3 tables in 3 separate files as below:

OPCB:
Code:
SELECT PROV_ID,                 
       OVTN_PROV_CONTR_ID,       
       OVTN_CONTR_SEQ_NBR,       
       ADR_ID,                   
       ADR_TYP_CD,               
       TAX_ID_NBR,               
       TAX_ID_TYP_CD,           
       OVTN_PRC_PKG_CD,         
       EFF_DT,                   
       CREAT_DT                 
  FROM OVTN_PRV_CONTR_BIL


PROV:
Code:
SELECT PROV_ID                 
  FROM PROV             
 WHERE CANC_DT > CURRENT DATE   


PROV_TIN_ADR:
Code:

SELECT ADR_ID,                 
       ADR_TYP_CD,             
       TAX_ID_NBR,             
       TAX_ID_TYP_CD           
  FROM PROV_TIN_ADR   
 WHERE CANC_DT > CURRENT DATE 



What I want to achieve is...

Quote:
#1 Compare OPCB & PROV table extract and filter only those records which match criteria (in current query) mentioned above

#2 Compare Output of Step1 with PROV_TIN_ADR table with the criteria mentioned (in current query) above.


Can anyone help me achive this??

Regs
Oscorp
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 Feb 20, 2007 4:45 pm    Post subject: Reply with quote
Oscorp,

yes it can be done , can you provide us with the file layout for all the 3 files ?i.e field definitions and their length.

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


Joined: 15 Oct 2004
Posts: 29
Topics: 8
PostPosted: Wed Feb 21, 2007 9:34 am    Post subject: Reply with quote
Hi kolusu,

Thanks....here are the details:

OPCB File: LRECL = 51 Bytes

Code:
10 PROV-ID              PIC S9(9) USAGE COMP.   
10 OVTN-PROV-CONTR-ID   PIC S9(9) USAGE COMP.   
10 OVTN-CONTR-SEQ-NBR   PIC S9(4) USAGE COMP.   
10 ADR-ID               PIC S9(9) USAGE COMP.   
10 ADR-TYP-CD           PIC X(1).               
10 TAX-ID-NBR           PIC S9(9)V USAGE COMP-3.
10 TAX-ID-TYP-CD        PIC X(1).               
10 OVTN-PRC-PKG-CD      PIC X(10).             
10 EFF-DT               PIC X(10).             
10 CREAT-DT             PIC X(10).           



PROV File: LRECL = 4 Bytes

Code:
10 PROV-ID              PIC S9(9) USAGE COMP.   



PTA File: LRECL = 11 Bytes

Code:
10 ADR-ID               PIC S9(9) USAGE COMP.   
10 ADR-TYP-CD           PIC X(1).               
10 TAX-ID-NBR           PIC S9(9)V USAGE COMP-3.
10 TAX-ID-TYP-CD        PIC X(1).               



Regs
Oscorp
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: Wed Feb 21, 2007 11:05 am    Post subject: Reply with quote
OSCORP,

Change your sql's as follows

Code:

SELECT PROV_ID                 
      ,OVTN_PROV_CONTR_ID       
      ,OVTN_CONTR_SEQ_NBR       
      ,ADR_ID                   
      ,ADR_TYP_CD               
      ,TAX_ID_NBR               
      ,TAX_ID_TYP_CD           
      ,OVTN_PRC_PKG_CD         
      ,EFF_DT                   
      ,CREAT_DT
      ,CHAR(' ',4)                 
  FROM OVTN_PRV_CONTR_BIL


Code:

SELECT PROV_ID
      ,CHAR(' ',47)
      ,PROV_ID                 
  FROM PROV             
 WHERE CANC_DT > CURRENT DATE   


Code:

SELECT CHAR(' ',10)
      ,ADR_ID
      ,ADR_TYP_CD
      ,TAX_ID_NBR             
      ,TAX_ID_TYP_CD
      ,CHAR(' ',34)
      ,ADR_ID
      ,ADR_TYP_CD
      ,TAX_ID_NBR             
      ,TAX_ID_TYP_CD
  FROM PROV_TIN_ADR   
 WHERE CANC_DT > CURRENT DATE   



and run these 2 DFSORT/ICETOOL steps and you will get the desired results.

Code:

//STEP0100 EXEC PGM=ICETOOL                             
//DFSMSG   DD SYSOUT=*                                   
//TOOLMSG  DD SYSOUT=*                                   
//IN       DD DSN=unload file of table PROV,
//            DISP=SHR                                   
//         DD DSN=unload file of table OPCB,
//            DISP=SHR                                   
//OUT      DD DSN=output combo of OPCB.PROV,
//            DISP=(NEW,CATLG,DELETE),                   
//            UNIT=SYSDA,
//            SPACE=(CYL,(X,Y),RLSE)
//TOOLIN   DD *                                         
  SPLICE FROM(IN) TO(OUT) ON(1,4,BI) -                   
    WITH(1,51) WITHALL USING(CTL1)                       
//CTL1CNTL DD *                                         
  OUTFIL FNAMES=OUT,                                     
  OUTREC=(01,55,11X)                                     
/*                                                       
//STEP0200 EXEC PGM=ICETOOL                             
//DFSMSG   DD SYSOUT=*                                   
//TOOLMSG  DD SYSOUT=*                                   
//IN       DD DSN=unload file of table PTA,
//            DISP=SHR                                   
//         DD DSN=output combo of OPCB.PROV,
//            DISP=SHR                                   
//OUT      DD DSN=final output of OPCB.PROV.PTA,
//            DISP=(NEW,CATLG,DELETE),                   
//            UNIT=SYSDA,
//            SPACE=(CYL,(X,Y),RLSE)
//TOOLIN   DD *                                         
  SPLICE FROM(IN) TO(OUT) -                             
      ON(11,4,BI) ON(15,1,CH) ON(16,5,PD) ON(21,1,CH) - 
    WITH(1,55) WITHALL USING(CTL1)                       
//CTL1CNTL DD *                                         
  OUTFIL FNAMES=OUT,                                     
  OUTREC=(01,55)                                         
/*


Hope this helps...

Cheers

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


Joined: 15 Oct 2004
Posts: 29
Topics: 8
PostPosted: Thu Feb 22, 2007 12:04 pm    Post subject: Reply with quote
Hi Kolusu,

I am getting some different result set this time.

The first comparision did worked fine...but for the last comparision I am not able to get the desired output. It seems that the output file contains records from both the files (Here, I want records only from OPCB file and not from PTA or PROV file) ...also I wanted to have output file LRECL = 51 and same order as of OPCB file.

Please let me know....

Regs
Oscorp
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: Thu Feb 22, 2007 12:17 pm    Post subject: Reply with quote
oscorp,

if you want only 51 bytes then just change the outrec on last step

Code:

//CTL1CNTL DD *                                         
  OUTFIL FNAMES=OUT,                                     
  OUTREC=(01,51)                                         
/*


The above job I showed should only bring those records which are matched both OPCB & PROV file

If you are getting different output then show us sample input and output data

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


Joined: 15 Oct 2004
Posts: 29
Topics: 8
PostPosted: Thu Feb 22, 2007 3:14 pm    Post subject: Reply with quote
Hi Kolusu,

PTA Extract

[code:1:eb4d7f9e8d]
..."D.
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: Thu Feb 22, 2007 3:42 pm    Post subject: Reply with quote
OSCORP,

I cannot Inerpret binary and packed decimal fields on the website.

Do you have any duplicates in any of the files?

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


Joined: 15 Oct 2004
Posts: 29
Topics: 8
PostPosted: Fri Feb 23, 2007 5:47 pm    Post subject: Reply with quote
Very Happy Very Happy Very Happy

Sorry...I knew this would happen.

In OPCB File there will be duplicates
In PROV & PTA file there will not be any duplicates

Regs
Oscorp
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: Fri Feb 23, 2007 7:19 pm    Post subject: Reply with quote
OSCORP,

I think the solution posted does handle Duplicates on the OPCB file.

I guess the problem is the order how you concatenated the files in the second pass.

Any way here is the sample I ran. Run this sample JCL as is and compare the results.


Code:

//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN   DD *
10  01
10  01
10  01
10  01
20  02
30  03
40  04
50  05
//SORTOUT  DD DSN=Tid.OPCB,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,
//            SPACE=(TRK(1,1),RLSE)
//SYSIN    DD *
 SORT FIELDS=COPY
 OUTREC FIELDS=(1,2,ZD,BI,LENGTH=4,             $ PROV-ID
                +10,MUL,1,2,ZD,BI,LENGTH=4,     $ OVTN-PROV-CONTR-ID
                5,2,ZD,BI,LENGTH=2,             $ OVTN-CONTR-SEQ-NBR
                +20,MUL,1,2,ZD,BI,LENGTH=4,     $ ADR-ID
                C'A',                           $ ADR-TYP-CD
                +500,MUL,1,2,ZD,PD,LENGTH=5,    $ TAX-ID-NBR
                C'B',                           $ TAX-ID-TYP-CD
                C'1234567890',                  $ OVTN-PRC-PKG-CD
                C'2007-02-01',                  $ EFF-DT
                C'2007-02-21',                  $ CREAT-DT
                4X)
/*
//STEP0200 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN   DD *
10
40
50
//SORTOUT  DD DSN=TID.PROV,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,
//            SPACE=(TRK(1,1),RLSE)
//SYSIN    DD *
  SORT FIELDS=COPY
  OUTREC FIELDS=(1,2,ZD,BI,LENGTH=4,51:X,1,2,ZD,BI,LENGTH=4)
/*
//STEP0300 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN   DD *
10
20
60
70
40
50
//SORTOUT  DD DSN=TID.PTA,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,
//            SPACE=(TRK(1,1),RLSE)
//SYSIN    DD *
 SORT FIELDS=COPY
 OUTREC FIELDS=(10X,                            $ FILLER
                +20,MUL,1,2,ZD,BI,LENGTH=4,     $ ADR-ID
                C'A',                           $ ADR-TYP-CD
                +500,MUL,1,2,ZD,PD,LENGTH=5,    $ TAX-ID-NBR
                C'B',                           $ TAX-ID-TYP-CD
                34X,                            $ FILLER
                +20,MUL,1,2,ZD,BI,LENGTH=4,     $ ADR-ID
                C'A',                           $ ADR-TYP-CD
                +500,MUL,1,2,ZD,PD,LENGTH=5,    $ TAX-ID-NBR
                C'B')                           $ TAX-ID-TYP-CD
/*
//STEP0400 EXEC PGM=ICETOOL
//DFSMSG   DD SYSOUT=*
//TOOLMSG  DD SYSOUT=*
//IN       DD DSN=TID.PROV,
//            DISP=SHR
//         DD DSN=TID.OPCB,
//            DISP=SHR
//OUT      DD DSN=TID.OPCB.PROV,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,
//            SPACE=(TRK(1,1),RLSE)
//TOOLIN   DD *
  SPLICE FROM(IN) TO(OUT) ON(1,4,BI) -
    WITH(1,51) WITHALL USING(CTL1)
//CTL1CNTL DD *
  OUTFIL FNAMES=OUT,
  OUTREC=(01,55,11X)
/*
//STEP0500 EXEC PGM=ICETOOL
//DFSMSG   DD SYSOUT=*
//TOOLMSG  DD SYSOUT=*
//IN       DD DSN=TID.PTA,
//            DISP=SHR
//         DD DSN=TID.OPCB.PROV,
//            DISP=SHR
//OUT      DD DSN=TID.OPCB.PROV.PTA,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,
//            SPACE=(TRK(1,1),RLSE)
//TOOLIN   DD *
  SPLICE FROM(IN) TO(OUT) -
      ON(11,4,BI) ON(15,1,CH) ON(16,5,PD) ON(21,1,CH) -
    WITH(1,55) WITHALL USING(CTL1)
//CTL1CNTL DD *
  OUTFIL FNAMES=OUT,
  OUTREC=(01,55)
/*


Hope this helps...

Cheers

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


Joined: 15 Oct 2004
Posts: 29
Topics: 8
PostPosted: Mon Mar 26, 2007 6:12 pm    Post subject: Reply with quote
Hi kolusu,

sorry for being so late in posting thank you reply.


Regs
Oscorp
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 Mar 27, 2007 7:51 am    Post subject: Reply with quote
OSCORP wrote:
Hi kolusu,

sorry for being so late in posting thank you reply.


Regs
Oscorp


hmm did the job work ?

Kolusu
_________________
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
Page 1 of 1

 
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