DB2 where clause disappearing

[Paul Hoyte <phoyte@SHARIDIONNE.COM>]

Hello all:

DB2 V7 with SQR 6.1.3

I was just wondering if anyone in the DB2 environment had run into this
problem.  The entire where clause disappears during the sql compilation
check before the SQR is executed causing a DB2 error as follows:

The Error message:

(SQR 5528) ODBC SQL dbsql: SQLPrepare error -495 in cursor 2:
   [IBM][CLI Driver][DB2] SQL0495N  Estimated processor cost of
"000000000626" processor seconds ("000096885952" service units) in cost
category "A" exceeds a resource limit error threshold of "000001000000"
service units.  SQLSTATE=57051

SQL: SELECT ALGN_XREF_CD, CNTR_LGCY_ID,
DVLP.BSC_RPT_CONTROL.BSC_MEAS_SECT_ID,
     FIS_YP, SECT_PERCENT_WT, WT_PERCENT, LINE_SEQ_NUM, LINE_TYPE,
     LINE_SUPPRESS, DVLP.BSC_RPT_CONTROL.BSC_MEAS_ID, ON_TARGET, PRD_ACTUAL,
     PRD_RATING, QTD_ACTUAL, QTD_RATING, YTD_ACTUAL, YTD_RATING, LINE_DESC
     From  DVLP.BSC_RPT_CONTROL, DVLP.BSC_SUMMARY_RPT

Error on line 297:
   (SQR 3716) Error in SQL statement.

Errors were found in the program file.

SQR: Program Aborting.


The following is the where clause that disappears:

>From  DVLP.BSC_RPT_CONTROL, DVLP.BSC_SUMMARY_RPT
Where DVLP.BSC_RPT_CONTROL.BRAND_SHRT_NM =
DVLP.BSC_SUMMARY_RPT.BRAND_SHRT_NM
 And DVLP.BSC_RPT_CONTROL.REPORT_ID = DVLP.BSC_SUMMARY_RPT.REPORT_ID
            And DVLP.BSC_RPT_CONTROL.BSC_MEAS_SECT_ID =
DVLP.BSC_SUMMARY_RPT.BSC_MEAS_SECT_ID
            And DVLP.BSC_RPT_CONTROL.BSC_MEAS_ID =
DVLP.BSC_SUMMARY_RPT.BSC_MEAS_ID
 And FIS_YP = 200202
 And DVLP.BSC_RPT_CONTROL.BRAND_SHRT_NM = 'TBC'
 And DVLP.BSC_RPT_CONTROL.REPORT_ID = '080'
    [$where1]
    [$where2]
 !and dvlp.bsc_summary_rpt.algn_xref_cd     = $entity_code   !$where1
 !and dvlp.bsc_summary_rpt.cntr_lgcy_id     = $legacy_code  !$where2

order by dvlp.bsc_summary_rpt.algn_xref_cd ,
         dvlp.bsc_summary_rpt.cntr_lgcy_id ,
         dvlp.bsc_rpt_control.bsc_meas_sect_id ,
         dvlp.bsc_rpt_control.line_seq_num

End-Select


Has anyone run into this problem, and does anyone have a sample of DB2 code
that used dynamic where clauses that I can look at?  Any solutios to this?

Regards to all,


Paul Hoyte
Sharidionne, Inc.
(248) 559-6868
www.sharidionne.com
phoyte@sharidionne.com