SQL AND QUERY400 OPTIMINIZATION

•STRDBMON OUTFILE(MAMASON/DBMONSQL)   JOB(*ALL)

TYPE(*DETAIL) FTRUSER(MAMASON)

 

Turns on Database Monitoring and directs output to a source file.

File DBMONSQL will be created if not exists.

•While Monitor is running all Query Optimization details are captured in source.
•Best results for system optimization suggest a few weeks of monitoring data.
•Output MAMASON/DBMONSQL can be queried
•Access Path Creation is suggested when field QQIDXA = Y in DBMONSQL file
•Logicals Used is in field is shown in QQIFNM
•Fields to add to logical keys are shown in QVC3003
•SELECT QQPTLN, QQPTFN, SUM(QQTOTR), QQIDXD FROM DBQRYLOG/DBMON  WHERE QQIDXA=’Y’ GROUP BY QQPTLN, QQPTFN, QQIDXD  ORDER BY QQPTLN,QQPTFN,QQIDXD
•Shows which access paths are used most in the system, see if a new logical makes sense
•Sometimes adding additional keys to existing logicals makes sense.
•Monitor will stay in force till End Request issued.
•    ENDDBMON JOB(*ALL)