Converting a QMF SQL query to Cobol Pgm !!

"Jagster" wrote in message
news:ecdUa.7290$9f7.839903@news02.tsnz.net...
> Hi there,
>
> I am having a problem converting this monstrous query into cobol code as
it
> takes 14 hrs to run this QMF Proc. Could any one help me out pls ?? It
would
> be greatly appreciated. Thanx in advance for this help.
>
> Here is the query !!
>
> SELECT FULL_TERM, A.REM_MONTHS, COUNT(*), SUM(A.BILL_BALANCE)
> , END_LMTH_CHAR
> FROM CSTMT A, LCUST B, ACCPLN C
> , CSTMT D
> , MDATE
> WHERE D.CUST_NBR = A.CUST_NBR
> AND D.ACCT_NBR = A.ACCT_NBR
> AND A.STMT_DATE >= (SELECT STRT_2MTH
> FROM MDATE )
> AND A.STMT_DATE < (SELECT STRT_LMTH
> FROM MDATE )
> AND A.BILL_BALANCE > 0
> AND A.REM_MONTHS > 0
> AND D.STMT_DATE >= (SELECT STRT_LMTH
> FROM MDATE )
> AND D.STMT_DATE <= (SELECT END_LMTH
> FROM MDATE )
> AND D.BILL_BALANCE < 0.01
> AND D.CREDITS < 0
>
> AND A.CUST_NBR = B.CUST_NBR
> AND A.ACCT_NBR = B.ACCT
> AND B.ACCT_TYPE = C.ACCT_TYPE
> AND B.REVL_PLAN_NBR = C.PAY_PLAN
> AND B.ORG_ID = '600555'
> AND B.ACCT_TYPE = '30'
> GROUP BY FULL_TERM, A.REM_MONTHS
> , END_LMTH_CHAR
> ORDER BY 1,2
>
>
> Jagster
>
First, break this into separate queries to get the start and end months.
Select the STRT_LMTH, STRT_2MTH, and END_LMTH, etc. separately and store
them in host variables. Then use the host variable in the main query.

It's usually better to use BETWEEN than >= and <=, although DB2 may
automatically convert these to a BETWEEN. Probably the most important factor
is the indexing on the table and the proper use of clustering of the table
(although other queries/programs must be considered in these decisions).

"Jagster" wrote in message
news:ecdUa.7290$9f7.839903@news02.tsnz.net...
> Hi there,
>
> I am having a problem converting this monstrous query into cobol code as
it
> takes 14 hrs to run this QMF Proc. Could any one help me out pls ?? It
would
> be greatly appreciated. Thanx in advance for this help.
>
> Here is the query !!
>
> SELECT FULL_TERM, A.REM_MONTHS, COUNT(*), SUM(A.BILL_BALANCE)
> , END_LMTH_CHAR
> FROM CSTMT A, LCUST B, ACCPLN C
> , CSTMT D
> , MDATE
> WHERE D.CUST_NBR = A.CUST_NBR
> AND D.ACCT_NBR = A.ACCT_NBR
> AND A.STMT_DATE >= (SELECT STRT_2MTH
> FROM MDATE )
> AND A.STMT_DATE < (SELECT STRT_LMTH
> FROM MDATE )
> AND A.BILL_BALANCE > 0
> AND A.REM_MONTHS > 0
> AND D.STMT_DATE >= (SELECT STRT_LMTH
> FROM MDATE )
> AND D.STMT_DATE <= (SELECT END_LMTH
> FROM MDATE )
> AND D.BILL_BALANCE < 0.01
> AND D.CREDITS < 0
>
> AND A.CUST_NBR = B.CUST_NBR
> AND A.ACCT_NBR = B.ACCT
> AND B.ACCT_TYPE = C.ACCT_TYPE
> AND B.REVL_PLAN_NBR = C.PAY_PLAN
> AND B.ORG_ID = '600555'
> AND B.ACCT_TYPE = '30'
> GROUP BY FULL_TERM, A.REM_MONTHS
> , END_LMTH_CHAR
> ORDER BY 1,2
>
>
> Jagster
>
First, break this into separate queries to get the start and end months.
Select the STRT_LMTH, STRT_2MTH, and END_LMTH, etc. separately and store
them in host variables. Then use the host variable in the main query.

It's usually better to use BETWEEN than >= and <=, although DB2 may
automatically convert these to a BETWEEN. Probably the most important factor
is the indexing on the table and the proper use of clustering of the table
(although other queries/programs must be considered in these decisions).