Terrible SQL performace

[TracyV]

First of all, we have NO embedded sql on any of our systems for me to refer to. I am experimenting and my program is probably full of don'ts. I'd appreciate anyone's suggestion/explaination as to why this is taking as long as 5-8 minutes to process 31 files. (Maybe that is normal? I don't know)

There are 31 keyed files residing in qtemp that contain the data that needs to be updated into production. The key to ASUTIL is:

UCEN
UYR
USEQNO
USPLTC
UUNITID
UUTILCD

Basically what is supposed to happen at this point is the data in QTEMP needs to replace the data in the production file, or the production record should be deleted altogether. There's some other code inserting or deleting the next year, bla bla, but it's just another delete/insert action.

Below is my sql for one of the 31 files. (the rest are essentially the same)

C*===========================
C* ASUTIL
C*===========================
C Eval FileName = 'File ASUTIL'
C If ParmOver = 'D'
C* Delete existing record from production:
C/Exec SQL
C+ Delete from MDNASDEV/ASUTIL
C+ Where UCEN = :Century and
C+ UYR = :NumYear and
C+ USEQNO = :Sequence and
C+ USPLTC = armSplit
C/End-Exec
C Exsr DeleteStatus
C If NumYear >= AssmYear
C/Exec SQL
C+ Delete from MDNASDEV/ASUTIL
C+ Where UCEN = :Century and
C+ UYR = :NumYear +1 and
C+ USEQNO = :Sequence and
C+ USPLTC = armSplit
C/End-Exec
C Exsr DeleteStatus
C Endif
C/Exec SQL
C+ Delete from QTEMP/ASUTIL
C/End-Exec
C Exsr DeleteStatus
C Else
C/Exec SQL
C+ Delete from MDNASDEV/ASUTIL
C+ Where UCEN = :Century and
C+ UYR = :NumYear and
C+ USEQNO = :Sequence and
C+ USPLTC = armSplit
C/End-Exec
C Exsr DeleteStatus
C/Exec SQL
C+ Insert into MDNASDEV/ASUTIL
C+ Select * from QTEMP/ASUTIL
C/End-Exec
C Exsr InsertStatus
C If Today <= CutOff
C/Exec SQL
C+ Delete from MDNASDEV/ASUTIL
C+ Where UCEN = :Century and
C+ UYR = :NumYear +1 and
C+ USEQNO = :Sequence and
C+ USPLTC = armSplit
C/End-Exec
C Exsr DeleteStatus
C/Exec SQL
C+ Update QTEMP/ASUTIL
C+ Set UYR = :NumYear +1
C/End-Exec
C Exsr UpdateStatus
C/Exec SQL
C+ Insert into MDNASDEV/ASUTIL
C+ Select * from QTEMP/ASUTIL
C/End-Exec
C Exsr InsertStatus
C Endif
C Endif
C/Exec SQL
C+ Delete from QTEMP/ASUTIL
C/End-Exec
C Exsr DeleteStatus
C
C

Below is my joblog. This is a keyed physical file with no logicals built over it.

**** Starting optimizer debug message for query .
The OS/400 Query access plan has been rebuilt.
Query options retrieved file QAQQINI in library QUSRSYS.
All access paths were considered for file ASUTIL.
All access paths were considered for file ASUTIL.
Arrival sequence access was used for file ASUTIL.
**** Ending debug message for query .

I pretty much get the same messages for each of the 31 files. Every file used in this program is a physical file with no logicals. Why does sql use arrival sequence rather than the key......is this what is taking so long??

 

[Mercury2]

- Turn on STRDBG in your job and check the joblog.
- Check Access Plan with PRTSQLINF.
- Remove or rename QAQQINI in QUSRSYS to make it not in use.

There are number of compile parameters that affect query optimizer's decision making. Try playing with those, especially ALWCPYDTA.