MVSFORUMS.com

[Sqlcode]

Hi,


It has been said and followed that WE SHOULD USE LITERALS INSTEAD ON HOST VARIABLES in Embeded SQL Statements in Cobol.

Now, Could someone please give me idea on this or I should say Correct me If I am wrong.


1) If we use HOST VARIABLES in SQL statements, DB2 might choose DEFAULT FILTER FACTOR which may be poor and hence we should use LITERALS.

2) Filter Factor is nothing 1/(Cardinality).

3) At any point of time, Cardinality remains same. Atleast before any batch job run starts on any given particular day. It do understand that it also depends on RUNSTATS.

4) Now, Since Cardinality is Constant, Filter Factor should also be Constant.

My question here is if Cardinality is and hence Filter Factor is contact why It is said that WE SHOULD USE LITERALS IN STEAD OF HOST VARIALBES.

I do know that all these factor largely depends on how ofter we perform RUNSTATS but still these concept is not clear.

Could someone please throw light on this? I have gone thru many IBM Topics but couldn't find satisfactory answer to this.


Thanks,

[jim haire]

The reason why you would use a literal instead of a host variable is that the optimizer could make better choices about which access path to take.

Let's say you have a query which has a where clause using a BETWEEN statement and you are searching a range of dates. There is an index built on the DATE.

If you were looking for what the actual sales of Halloween candy were on the two days before Halloween, it would be much better to code:

BETWEEN '2008-10-29' AND '2008-10-30' than
BETWEEN :WS-LOW-DATE AND :WS-HIGH-DATE

because the optimizer would know at Bind time how it would be going after the data.

With host variables, it doesn't know what to expect. If while testing, someone decided to put in a wide range of dates, the optimizer might decide to do a tablespace scan if a majority of the rows were returned based on the dates provided.

That's why if you know the values to be used in a query, you should provide them.