Query doing table scan when added simple predicates

Hi
I was using a query previously, that was efficient
select * from table where pred1 and pred2 and pred3;
>
Later I was asked to introduce new ones, but they were not based on
table columns but variables declared in SP.
>
select * from table where pred1 and pred2 and pred3 and variable1
='number1 and variable2 =number2;
>
so in turn this translates to pure mathematical comparison, sometimes
simple like 2=2,
I;ve added only 2 of them in the ex above, but actually there are 13
(does this influence the path, duh!!). Anyways, what happened was,
after they were added, it started doing a table scan, and was very
very inefficient. Later, I converted the additional predicates into
simple if loops, and executed the original query, and SP was very
fast.
Is there any database logic connected to doing a table scan? or any
registry/variable setting I can do so db2 doesnt do the table scan?
Please advise, since my team has done this mistake in many places,
and rolling back is going to be a pain in backside.

I am really surprised at the following explains.
>
1. The correct one, with just one constant predicate check.
>
select ri_indv_id
>
* from elg.artelgf
>
* where client_cd ='300' and segment_type_cd ='003' and carrier_cd =
>
* * * * * '60054' and ri_indv_id =11233 and covpf_rf ='MED'and
--- below is the only constant predicate check
* * * * * value(
>
* * * * * '01-01-2009' , CURRENT DATE)CURRENT DATE - 30 days
>
* fetch first 1 rows only
>
Section Code Page = 1252
>
Estimated Cost = 38.574055
>
Estimated Cardinality = 0.047454
>
( * *3) Table Constructor
>
* * * * | *1-Row(s)
>
( * *3) Residual Predicate(s)
>
* * * * | *#Predicates = 1
>
( * *2) Nested Loop Join
>
( * *2) | *Access Table Name = ELG.ARTELGF *ID = 47,4
>
* * * * | *| *Index Scan: *Name = ELG.PK_ARTELGF2 *ID = 6
>
* * * * | *| *| *Regular Index (Not Clustered)
>
* * * * | *| *| *Index Columns:
>
* * * * | *| *| *| *1: CLIENT_CD (Ascending)
>
* * * * | *| *| *| *2: SEGMENT_TYPE_CD (Ascending)
>
* * * * | *| *| *| *3: CARRIER_CD (Ascending)
>
* * * * | *| *| *| *4: RI_INDV_ID (Ascending)
>
* * * * | *| *| *| *5: COVPF_RF (Ascending)
>
* * * * | *| *| *| *6: GROUP_NUM (Ascending)
>
* * * * | *| *| *| *7: POLICY_NUM (Ascending)
>
* * * * | *| *| *| *8: POLICY_START_DT (Ascending)
>
* * * * | *| *| *| *9: CARRIER_OFFICE_CD (Ascending)
>
* * * * | *| *| *| *10: SGSRC_RF (Ascending)
>
* * * * | *| *| *| *11: QHIT_IND (Include Column)
>
* * * * | *| *#Columns = 0
>
* * * * | *| *Compressed Table
>
* * * * | *| *Skip Inserted Rows
>
* * * * | *| *Skip Deleted Keys
>
* * * * | *| *Skip Deleted Rows
>
* * * * | *| *#Key Columns = 5
>
* * * * | *| *| *Start Key: Inclusive Value
>
* * * * | *| *| *| *| *1: '300 *'
>
* * * * | *| *| *| *| *2: '003'
>
* * * * | *| *| *| *| *3: '60054'
>
* * * * | *| *| *| *| *4: 000000011233
>
* * * * | *| *| *| *| *5: 'MED'
>
* * * * | *| *| *Stop Key: Inclusive Value
>
* * * * | *| *| *| *| *1: '300 *'
>
* * * * | *| *| *| *| *2: '003'
>
* * * * | *| *| *| *| *3: '60054'
>
* * * * | *| *| *| *| *4: 000000011233
>
* * * * | *| *| *| *| *5: 'MED'
>
* * * * | *| *Index-Only Access
>
* * * * | *| *Index Prefetch: None
>
* * * * | *| *Lock Intents
>
* * * * | *| *| *Table: Intent Share
>
* * * * | *| *| *Row *: Next Key Share
>
( * *1) Return Data to Application
>
* * * * | *#Columns = 1
>
End of section
>
Optimizer Plan:
>
* * * * *RETURN
>
* * * * *( * 1)
>
* * * * * *|
>
* * * * *NLJOIN
>
* * * * *( * 2)
>
* * * * / * * *\
>
*TBSCAN * * * * IXSCAN
>
*( * 3) * * * * ( * 2)
>
* *| * * * * * / * * *\
>
*TFunc: *Index: * * * Table:
>
*SYSIBM *ELG * * * * *ELG
>
*GENROW *PK_ARTELGF2 *ARTELGF
>
2. Here we have the same query but havin 2 const predicate checks
instead of one and see what our db2 engine comes up with.
*select ri_indv_id
>
* from elg.artelgf
>
* where client_cd ='300' and segment_type_cd ='003' and carrier_cd =
>
* * * * * '60054' and ri_indv_id =11233 and covpf_rf ='MED'and
--below are two const predicate checks instead of 1
*value(
>
* * * * * '01-01-2009' , CURRENT DATE)CURRENT DATE - 30 days and ( (
>
* * * * * '01-01-1999' between '01-01-2000' and '01-01-2009' AND days(
>
* * * * * value('01-01-2009' , current date))- days('01-01-1999' )>=
>
* * * * * 30 ))
>
* fetch first 1 rows only
>
Section Code Page = 1252
>
Estimated Cost = 0.000101
>
Estimated Cardinality = 0.000000
>
( * *2) Table Constructor
>
* * * * | *N-Rows
>
( * *2) Residual Predicate(s)
>
* * * * | *#Predicates = 1
>
( * *1) Return Data to Application
>
* * * * | *#Columns = 1
>
End of section
>
Optimizer Plan:
>
*RETURN
>
*( * 1)
>
* *|
>
*TBSCAN
>
*( * 2)
>
* *|
>
*TFunc:
>
*SYSIBM
>
*GENROW

>
I got it, actually db2 is far intelligent than what I thought :-p the
reason for this behaviour being, since one of the
constant predicates check was false, it didnt even go to the real
query.
Hail DB2,