Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2007
    Posts
    5

    Unanswered: different results for the same SQL statement run static or dynamic

    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.

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    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
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Feb 2007
    Posts
    5
    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.

  4. #4
    Join Date
    Jun 2006
    Posts
    471
    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.
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Are you sure the same schema is being used in both cases? Try specifying the table schemas (schemata?) explicitly.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Feb 2007
    Posts
    5
    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.

  7. #7
    Join Date
    Jun 2006
    Posts
    471
    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
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Feb 2007
    Posts
    5
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •