Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Location
    washington dc
    Posts
    12

    Unanswered: Access Plan - Show Optimized SQL text

    I ran a sql in Command Center, and used 'Access Plan' utility to generate a Optimizd sql. Then I ran the optimized sql, but I got error of

    'DBA2191E SQL execution error.

    com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2/NT] SQL0206N "Q9.RET_UNQ_KEY" is not valid in the context where it is used. SQLSTATE=42703'

    I expected this utility will generate a better performance sql for me, but how come this sql has error?

    The DB2 version I used is 7, fix pak 9. Does anyone run into this problem?

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

    Please post the SQL message.

    Grofaty

  3. #3
    Join Date
    Jun 2003
    Location
    washington dc
    Posts
    12
    Originally posted by grofaty
    Hi,

    Please post the SQL message.

    Grofaty
    Grofaty,

    The following Optimized SQL that generate from access Plan.
    When I run it with above error message in command window.

    SELECT Q1.DESC_TXT AS "DESC_TXT", Q1.UPDATE_VALUE_TXT
    AS "UPDATE_VALUE_TXT", Q5.STATUS_DESC AS "STATUS_DESC",
    Q4.STATE_TAPE AS "STATE_TAPE", Q4.STATE_ST AS "STATE_ST"
    FROM GOLD.TXT_RTES AS Q1, GOLD.DMT_CTRL
    AS Q2, GOLD.TXT_CALL AS Q3, GOLD.PROC_STATE
    AS Q4, GOLD.STATUS AS Q5,
    (SELECT MAX(Q7.$C1), Q7.$C0
    FROM
    (SELECT Q6.RET_UNQ_KEY, Q6.DMT_SEQ_NUM
    FROM GOLD.DMT_CTRL AS Q6
    WHERE (Q6.RET_UNQ_KEY = Q9.RET_UNQ_KEY) AND (Q2.RET_UNQ_KEY
    = Q6.RET_UNQ_KEY)) AS Q7
    GROUP BY Q7.$C0) AS Q8, GOLD.DMT_CTRL AS Q9,
    (SELECT MAX(Q14.$C0)
    FROM
    (SELECT Q10.STATE_TAPE
    FROM GOLD.PROC_STATE AS Q10,
    (SELECT MAX(Q12.$C1), Q12.$C0
    FROM
    (SELECT Q11.RET_UNQ_KEY, Q11.STATE_ST
    FROM GOLD.PROC_STATE AS Q11) AS Q12
    GROUP BY Q12.$C0) AS Q13
    WHERE (Q10.RET_UNQ_KEY = Q13.$C1) AND (Q10.STATE_ST
    = Q13.$C0)) AS Q14) AS Q15
    WHERE (Q2.DMT_SEQ_NUM = Q8.$C0) AND (Q9.DLN = '1 ')
    AND (Q4.STATE_TAPE = Q5.STATE_TAPE) AND (Q5.STATE_TAPE
    = Q15.$C0) AND (Q8.$C1 = Q4.RET_UNQ_KEY) AND (Q8.$C1
    = Q1.RET_UNQ_KEY)


    Thanks for your help.

Posting Permissions

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