Hello,
We are experiencing a strange problem with our application. We have a C++ application that opens a DB2 cursor using an embedded SQL statement (so the SQL is a static one). All select criteria values are hard coded (no host variables are being used). The number of records selected by the cursor is 3068. If we run the exact same select statement from the command line (so it is dynamic one now) using the exact same select criteria we have 3498 records selected. We cannot understand the difference of 430 records between the 2 select statements. We tried this test on 3 different environments (i.e. servers) with the same database image an the problem is consistent. We use DB2 V8 on AIX. First 2 tests were done on an environment with Fix Pack 10 the last one on an environment with Fix pack 13. The select statement is not complex as you can see below:

Cursor, i.e. static select statement:
EXEC SQL DECLARE c151 CURSOR WITH HOLD FOR
SELECT ....
FROM tableA t,
tableB m
WHERE EXISTS
(SELECT 1
FROM tableC pr,
tableD pl,
tableE dp,
tableF sp
WHERE pr.extract_id = '2007-05-30-16.16.41.265070'
AND pr.plan_coid = sp.account_coid
AND pr.plan_sp_id = sp.product_seq
AND dp.product_coid = sp.defining_prod_coid
AND pl.product_id = dp.product_id
AND pl.plan_holdings_type = 119210368
AND pr.plan_coid = t.account_coid)
AND m.Source_ = 125569856
AND t.txn_type = m.Txn_type
AND t.Post_Date >= x'002573C3000000000804011B'
AND t.Post_Date >= x'00256D20000000000804011B'
AND t.Post_Date <= x'0025741D0001517F0804011B'
AND NOT EXISTS
(SELECT 1
FROM tableG st
WHERE extract_id = '2007-05-30-16.16.41.385273'
AND st.plan_coid = t.account_coid
AND st.plan_sp_id = t.product_id
AND st.request_id = 0)
ORDER BY t.Account_Coid,
t.Effective_Date,
t.Top_Level_Txn_Id,
t.Reversal_,
t.Txn_Id
FOR READ ONLY;


Command line one, i.e. dynamic select statement:
SELECT ...
FROM tableA t,
tableB m
WHERE EXISTS
(SELECT 1
FROM tableC pr,
tableD pl,
tableE dp,
tableF sp
WHERE pr.extract_id = '2007-05-30-16.16.41.265070'
AND pr.plan_coid = sp.account_coid
AND pr.plan_sp_id = sp.product_seq
AND dp.product_coid = sp.defining_prod_coid
AND pl.product_id = dp.product_id
AND pl.plan_holdings_type = 119210368
AND pr.plan_coid = t.account_coid)
AND m.Source_ = 125569856
AND t.txn_type = m.Txn_type
AND t.Post_Date >= x'002573C3000000000804011B'
AND t.Post_Date >= x'00256D20000000000804011B'
AND t.Post_Date <= x'0025741D0001517F0804011B'
AND NOT EXISTS
(SELECT 1
FROM tableG st
WHERE extract_id = '2007-05-30-16.16.41.385273'
AND st.plan_coid = t.account_coid
AND st.plan_sp_id = t.product_id
AND st.request_id = 0)
ORDER BY t.Account_Coid,
t.Effective_Date,
t.Top_Level_Txn_Id,
t.Reversal_,
t.Txn_Id

Any suggestions would be more than welcome.

Thanks, Ioan.

this is not possible
take a record from the clp output that is not in the static result and check the
condition why this is or is not returned
the same nbr of records should be returned

I totally agree with you ... they should return the exact same results. I should have mentioned in the original message that I already tried to take on record that is only returned by the dynamic SQL statement and hardcode the static one so it will only return me that particular record. And it did. So my guess is that there is something to do with the volume of the returned records. Is there any database configuration parameter in particular that we should tune?

Thanks, Ioan.

you could create a copy of the static program and say
select count(*) from ... where ...
to see the resultset
at the same time run the dynamic sql.

Are you sure the same schema is being used in both cases? Try specifying the table schemas (schemata?) explicitly.

Yes I'm positive the schema is the same. I'm running the application and the command line SQL statement using the same user ID.
I just did what Guy suggested: I changed the static select statement only to count the number of records it should select and output the result. It was the exact same number I get when running the dynamic SQL from the command line. Once I change the static select statement and declare a cursor which is then opened I have less 430 records fetched. Not sure what's going on.
Is there a way to check the number of records in a cursor? We run event monitor and I can see the number of fetched records, but I'm wondering if I can see the number of records in the opened cursor. I know, normally what is in the cursor is then fetched.

are you fetching until sqlcode 100 or any other sqlcode different from 0
try : small program with no other logic
declare cursor
open cursor
counter=0
loop
fetch
if sqlcode=100 leave
counter=counter+1
end loop
print counter

Could you explain what this is about?

AND t.Post_Date >= x'002573C3000000000804011B'
AND t.Post_Date >= x'00256D20000000000804011B'
AND t.Post_Date <= x'0025741D0001517F0804011B'

What data type are these columns?

They are character for bit data and represent a date in Julian format.

I sort of found what the problem is, however I'm still not sure how to resolve it :-).

Here is a brief description of what the application does:
1 - opens the cursor
2 - fetches one record from the cursor
3 - processes the fetched data
4 - inserts the processed data into another table (we call it extract table)
5 - repeats steps 2 to 4 until no more records in the cursor
6 - closes the cursor

If you look at the cursor we have, at the end of it, a "NOT EXISTS" clause. The table in this "NOT EXISTS" clause is the extract table from above. This is the recovery mechanism of the application (i.e. do not extract records if they are already extracted). Now, what I realized is that this table makes the cursor to skip records. The only logical explanation I have is that the cursor's pointer is changed after few inserts into the extract table. I removed the "NOT EXISTS" clause and everything works fine. However, this mechanism works fine in production but in test area it gives us hard time. The cursor is opened WITH HOLD and FOR READ ONLY.
We would prefer not to change the application because this would be a huge thing.

Have you experienced something similar before when a cursor's pointer is changed and how did you fix the problem?