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

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.

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