Results 1 to 10 of 10
  1. #1
    Join Date
    May 2003
    Posts
    4

    Angry Unanswered: SQL Select returns inconsistent resultset

    Help! My select statement performs consistently correctly when running interactively on the iseries or through ops navigator. However, in my web application, embedded in a java class on websphere using jdbc and connection pooling, it sometimes returns the correct results and sometimes a few rows of the resultset are missing. There are no SQL warnings or errors... no messages in the log indicating anything went wrong. These rows would not be updated while I am retrieving them but other rows in the table could be updated at this time. Let me know if you need any more info about this if you think you can help me... below is the big huge sql statement (written by a consultant, not me).

    select distinct ulcv.applicationid appid,
    ulcv.coveragecode ccde,
    ulcv.premiumamount UnitPrem,
    acov.Limit,
    acov.Value,
    acov.Deductible,
    clmt.OCCURENCELIMIT,
    clmt.perpersonlimit,
    spdf.coveragelineno lnno,
    spdf.limitheading1,
    spdf.limitheading2,
    spdf.text1 CoverageDesc,
    spdf.sequence,
    ulcv.unitid,
    btext.heading
    from uploaddvp.aeappmst app,
    uploaddvp.aeuntlcv ulcv,
    uploaddvp.aeappcov acov,
    uploaddvp.aesumpdf spdf,
    uploaddvp.aecovdef cdef,
    uploaddvp.aecovlmt clmt,
    uploaddvp.aepdfview btext
    where app.applicationid = 11030
    and ulcv.applicationid = app.applicationid
    and acov.applicationid = ulcv.applicationid
    and acov.coveragecode = ulcv.coveragecode
    and acov.coveragestatus <> 'DELETE'
    and spdf.coveragecode = acov.coveragecode
    and spdf.riskstate =app.riskstate
    and substr(char(btext.ptrseq),1,3) = substr(char(spdf.sequence),1,3)
    and cdef.coveragecode = spdf.coveragecode
    and cdef.companynumber = spdf.companynumber
    and cdef.policytype = spdf.policytype
    and cdef.riskstate = spdf.riskstate
    and cdef.unittype = spdf.unittype
    and cdef.unitgroup = spdf.unitgroup
    and cdef.effectivecentury = spdf.effectivecentury
    and cdef.effectiveyear = spdf.effectiveyear
    and cdef.effectivemonth = spdf.effectivemonth
    and cdef.effectiveday = spdf.effectiveday
    and clmt.limit = acov.limit
    and clmt.coveragecode = spdf.coveragecode
    and clmt.companynumber = spdf.companynumber
    and clmt.policytype = spdf.policytype
    and clmt.riskstate = spdf.riskstate
    and clmt.unittype = spdf.unittype
    and clmt.unitgroup = spdf.unitgroup
    and clmt.effectivecentury = spdf.effectivecentury
    and clmt.effectiveyear = spdf.effectiveyear
    and clmt.effectivemonth = spdf.effectivemonth
    and clmt.effectiveday = spdf.effectiveday
    and (DATE( CASE when cdef.EFFECTIVECENTURY = 0 then '99' ELSE rtrim(char(cdef.EFFECTIVECENTURY)) END CONCAT CASE WHEN cdef.EFFECTIVEYEAR = 0 then '00' WHEN cdef.EFFECTIVEYEAR > 0 and cdef.EFFECTIVEYEAR < 10 then '0' concat rtrim(char(cdef.EFFECTIVEYEAR)) ELSE rtrim(char(cdef.EFFECTIVEYEAR)) END CONCAT '-' CONCAT CASE WHEN cdef.EFFECTIVEMONTH = 0 then '12' WHEN cdef.EFFECTIVEMONTH > 0 and cdef.EFFECTIVEMONTH < 10 then '0' concat rtrim(char(cdef.EFFECTIVEMONTH)) ELSE rtrim(char(cdef.EFFECTIVEMONTH)) END CONCAT '-' CONCAT CASE WHEN cdef.EFFECTIVEDAY = 0 then '31' WHEN cdef.EFFECTIVEDAY > 0 and cdef.EFFECTIVEDAY < 10 then '0' concat rtrim(char(cdef.EFFECTIVEDAY)) ELSE rtrim(char(cdef.EFFECTIVEDAY)) END ) <= app.effectivedate and DATE( CASE when cdef.EXPIRATIONCENTURY = 0 then '99' ELSE rtrim(char(cdef.EXPIRATIONCENTURY)) END CONCAT CASE WHEN cdef.EXPIRATIONYEAR = 0 then '00' WHEN cdef.EXPIRATIONYEAR > 0 and cdef.EXPIRATIONYEAR < 10 then '0' concat rtrim(char(cdef.EXPIRATIONYEAR)) ELSE rtrim(char(cdef.EXPIRATIONYEAR)) END CONCAT '-' CONCAT CASE WHEN cdef.EXPIRATIONMONTH = 0 then '12' WHEN cdef.EXPIRATIONMONTH > 0 and cdef.EXPIRATIONMONTH < 10 then '0' concat rtrim(char(cdef.EXPIRATIONMONTH)) ELSE rtrim(char(cdef.EXPIRATIONMONTH)) END CONCAT '-' CONCAT CASE WHEN cdef.EXPIRATIONDAY = 0 then '31' WHEN cdef.EXPIRATIONDAY > 0 and cdef.EXPIRATIONDAY < 10 then '0' concat rtrim(char(cdef.EXPIRATIONDAY)) ELSE rtrim(char(cdef.EXPIRATIONDAY)) END ) >= app.effectivedate)
    union all
    select distinct
    ucov.applicationid appid,
    ucov.coveragecode ccde,
    ucov.premiumamount UnitPrem,
    ucov.Limit,
    ucov.value,
    ucov.deductible,
    clmt.OCCURENCELIMIT,
    clmt.perpersonlimit,
    spdf.coveragelineno lnno,
    spdf.limitheading1,
    spdf.limitheading2,
    spdf.text1 CoverageDesc,
    spdf.sequence,
    ucov.unitid,
    btext.heading

    from uploaddvp.aeappmst app join
    uploaddvp.aeuntcov ucov on
    ucov.applicationid = app.applicationid
    join
    uploaddvp.aesumpdf spdf on
    spdf.coveragecode = ucov.coveragecode
    and spdf.riskstate =app.riskstate
    left outer join
    uploaddvp.aecovlmt clmt on
    ucov.limit = clmt.limit
    and spdf.coveragecode = clmt.coveragecode
    and spdf.companynumber = clmt.companynumber
    and spdf.policytype = clmt.policytype
    and spdf.riskstate = clmt.riskstate
    and spdf.unittype = clmt.unittype
    and spdf.unitgroup = clmt.unitgroup
    and spdf.effectivecentury = clmt.effectivecentury
    and spdf.effectiveyear = clmt.effectiveyear
    and spdf.effectivemonth = clmt.effectivemonth
    and spdf.effectiveday = clmt.effectiveday
    join
    uploaddvp.aecovdef cdef on
    cdef.coveragecode = spdf.coveragecode
    and cdef.companynumber = spdf.companynumber
    and cdef.policytype = spdf.policytype
    and cdef.riskstate = spdf.riskstate
    and cdef.unittype = spdf.unittype
    and cdef.unitgroup = spdf.unitgroup
    and cdef.effectivecentury = spdf.effectivecentury
    and cdef.effectiveyear = spdf.effectiveyear
    and cdef.effectivemonth = spdf.effectivemonth
    and cdef.effectiveday = spdf.effectiveday
    join
    uploaddvp.aepdfview btext on
    substr(char(btext.ptrseq),1,3) = substr(char(spdf.sequence),1,3)

    where app.applicationid = 370


    and ucov.coveragestatus <> 'DELETE'


    and (DATE( CASE when cdef.EFFECTIVECENTURY = 0 then '99' ELSE rtrim(char(cdef.EFFECTIVECENTURY)) END CONCAT CASE WHEN cdef.EFFECTIVEYEAR = 0 then '00' WHEN cdef.EFFECTIVEYEAR > 0 and cdef.EFFECTIVEYEAR < 10 then '0' concat rtrim(char(cdef.EFFECTIVEYEAR)) ELSE rtrim(char(cdef.EFFECTIVEYEAR)) END CONCAT '-' CONCAT CASE WHEN cdef.EFFECTIVEMONTH = 0 then '12' WHEN cdef.EFFECTIVEMONTH > 0 and cdef.EFFECTIVEMONTH < 10 then '0' concat rtrim(char(cdef.EFFECTIVEMONTH)) ELSE rtrim(char(cdef.EFFECTIVEMONTH)) END CONCAT '-' CONCAT CASE WHEN cdef.EFFECTIVEDAY = 0 then '31' WHEN cdef.EFFECTIVEDAY > 0 and cdef.EFFECTIVEDAY < 10 then '0' concat rtrim(char(cdef.EFFECTIVEDAY)) ELSE rtrim(char(cdef.EFFECTIVEDAY)) END ) <= app.effectivedate and DATE( CASE when cdef.EXPIRATIONCENTURY = 0 then '99' ELSE rtrim(char(cdef.EXPIRATIONCENTURY)) END CONCAT CASE WHEN cdef.EXPIRATIONYEAR = 0 then '00' WHEN cdef.EXPIRATIONYEAR > 0 and cdef.EXPIRATIONYEAR < 10 then '0' concat rtrim(char(cdef.EXPIRATIONYEAR)) ELSE rtrim(char(cdef.EXPIRATIONYEAR)) END CONCAT '-' CONCAT CASE WHEN cdef.EXPIRATIONMONTH = 0 then '12' WHEN cdef.EXPIRATIONMONTH > 0 and cdef.EXPIRATIONMONTH < 10 then '0' concat rtrim(char(cdef.EXPIRATIONMONTH)) ELSE rtrim(char(cdef.EXPIRATIONMONTH)) END CONCAT '-' CONCAT CASE WHEN cdef.EXPIRATIONDAY = 0 then '31' WHEN cdef.EXPIRATIONDAY > 0 and cdef.EXPIRATIONDAY < 10 then '0' concat rtrim(char(cdef.EXPIRATIONDAY)) ELSE rtrim(char(cdef.EXPIRATIONDAY)) END ) >= app.effectivedate) order by sequence, unitid, lnno;

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: SQL Select returns inconsistent resultset

    I assume it might be because of isolation level ....

    Can you give more details of your webapplication environment ...



    Cheers

    Sathyaram

  3. #3
    Join Date
    May 2003
    Posts
    4
    How do I display and change the isolation level on the iseries? I've asked the operations people but they don't know how, so currently I am searching the ibm website. I'm sure it's currently set at the default. We don't have a DBA here, and no one here really knows this kind of thing since the consultants setup alot of this stuff.

  4. #4
    Join Date
    May 2003
    Posts
    4
    I figured out how to get and set isolation level when we get a connection. So we put in a debug statement to display the current isolation level and it is at Uncommitted Read. I assume that means we shouldn't have any problem with concurrency?

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Try to escalate tu isolation level to CS.

    Hope this helps,
    Grofaty

  6. #6
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37

    Re: SQL Select returns inconsistent resultset

    Has the issue been resolved?

    There was nothing wrong with the statement from consistency's point of view. So I concur with the other repliers about their isolation level concern.

    I am curious, though. If you do not mind elaborating on what you experience regarding the performance of this statement in your environment. As my focus in my current job is DB2 query performance, every bit of information is welcome. If you don't mind, could you please share size, indexing, version, CPU data as well.

    Cheers,

    Julius

  7. #7
    Join Date
    May 2003
    Location
    Toronto
    Posts
    29
    Originally posted by tanyavogel
    I figured out how to get and set isolation level when we get a connection. So we put in a debug statement to display the current isolation level and it is at Uncommitted Read. I assume that means we shouldn't have any problem with concurrency?
    Would you please tell me how to get the Isolation Level? thanks

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    After the full select statement write WITH UR, WITH CS, WITH RS or WITH RR.

    Sample:
    select col1 from tab1 WITH UR;
    select col1 from tab1 WITH CS;
    select col1 from tab1 WITH RS;
    select col1 from tab1 WITH RR;

    Hope this helps,
    Grofaty

  9. #9
    Join Date
    May 2003
    Location
    Toronto
    Posts
    29
    Thank you so much.

  10. #10
    Join Date
    Jul 2003
    Location
    Switzerland, Basle
    Posts
    10
    maybe it's related to the jdbc-driver.
    checkout if DB2-APAR IY32981 "Communication overflow, last row missing with JDBC-application, when using the NET-driver"
    first fixed in DB2 V7 Fixpack 9, applies to your environment.

Posting Permissions

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