Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006
    Posts
    8

    Unanswered: Access path problems

    We have a problem in choosing the Access path like if we execute from spufi, it takes one Access path that is actually good one which takes few seconds to retrieve the datas , but if we execute from Application program it takes some other Access path and it's relatively very small.

    This was happened after REORG and also the version change..

    the below is the SQL Statements,


    EXEC SQL DECLARE A CURSOR FOR
    SELECT A.LOCAL_REF_NUMBER
    , A.FORM_NR
    , A.BRIEF_DESCRIPTION
    , A.ISSUE
    , A.PRIORITY
    , A.AUTHOR
    , A.AUTHOR_EXT
    , A.AUTHOR_DEPT
    , F.NEXT_NODE
    , B.DATE
    , B.NODE_NAME
    , F.DUE_DATE
    , C.ACTION_NAME
    , C.ROUTING_STATUS
    , A.AUTHOR_LOC
    , A.APPLICATION_ID
    , A.FORM_TYPE
    , A.COMPANY_CODE
    , A.FORM_NUMBER
    , A.SUBFORM_MODE_FLAG
    , F.SUBFORM_NUMBER
    , F.SUBFORM_ITEM
    , F.FORM_CONDITION
    FROM C#FORM_TABLE_09 A
    , C#HISTORY_07 B
    , C#ACTIONS_07 C
    , C#FORM_TYPES_07 D
    , C#FORM_STATS_02 F

    WHERE D.FORM_TYPE_NAME BETWEEN T1
    AND T2
    AND D.APPLICATION_ID BETWEEN :LOW_APPL
    AND :HIGH_APPL
    AND F.APPLICATION_ID = D.APPLICATION_ID
    AND F.FORM_TYPE = D.FORM_TYPE
    AND F.INACTIVE_CTR = 0
    AND F.NEXT_NODE = :NODE#
    AND A.FORM_TYPE = F.FORM_TYPE
    AND A.COMPANY_CODE = F.COMPANY_CODE
    AND A.FORM_NUMBER = F.FORM_NUMBER
    AND A.APPLICATION_ID = F.APPLICATION_ID
    AND B.FORM_TYPE = A.FORM_TYPE
    AND B.COMPANY_CODE = A.COMPANY_CODE
    AND B.FORM_NUMBER = A.FORM_NUMBER
    AND B.SUBFORM_NUMBER = F.SUBFORM_NUMBER
    AND B.APPLICATION_ID = A.APPLICATION_ID
    AND B.SEQUENCE_NUMBER = F.STATUS_SEQ_NR
    AND C.FORM_TYPE = B.FORM_TYPE
    AND C.ACTION_NUMBER = B.ACTION_NUMBER
    AND C.APPLICATION_ID = B.APPLICATION_ID
    FOR FETCH ONLY;


    Could you please check the above query which table should Access first whether it's bigger ones or smaller ones?

    Please give me your valuable suggestions in this regard.

    Regards,
    karthik

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    I had a similar problem with SPUFI giving me an access path with good performance and the stored procedure choosing poorly. I ended up using an optimization hint with the access path from the SPUFI.

  3. #3
    Join Date
    Jun 2006
    Posts
    8

    Dynamic SQL

    Like you suggested i have used opt hint, but not worked. Can i use the dyanamic SQL inside the PL/I programme to get rid of the Access path problems.

    PLease assist me.

    Regards,
    karthik

Posting Permissions

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