Good day,
Recently, I became involved in OnDemand from more of an application end. Upon reviewing the DB2 z/OS calls, it turns out that these 4 five tables, based upon three queries consume 83.7% of all SQL ran and anywhere from 2/3rds to 3/4ths of all resource time. The question I have is two fold. First, is this something that can be reduced by OD-WEK or WEBI modification? Or, is this something the application team needs to change in their application, such as caching the app user id results? In either case, if so how?
Please advise. Thank you.
Below are the three queries in question.
SELECT ARSSERVR . ARSFOL . NAME ,
ARSSERVR . ARSFOL . DESCRIPTION ,
ARSSERVR . ARSFOL . FID ,
ARSSERVR . ARSFOL . ANN_SEARCH ,
ARSSERVR . ARSFOL . UPD_USERID ,
ARSSERVR . ARSFOL . UPD_DATE ,
ARSSERVR . ARSFOL . UPD_DT ,
ARSSERVR . ARSFOLPERMS . ID ,
ARSSERVR . ARSFOLPERMS . FID ,
ARSSERVR . ARSFOLPERMS . PERMS ,
ARSSERVR . ARSFOLPERMS . HITS
FROM ARSSERVR . ARSFOL ,
ARSSERVR . ARSFOLPERMS
WHERE ARSSERVR . ARSFOL . FID = ARSSERVR . ARSFOLPERMS . FID
AND ARSSERVR . ARSFOLPERMS . ID IN ( ? , ? )
ORDER BY ARSSERVR . ARSFOLPERMS . FID ,
ARSSERVR . ARSFOLPERMS . ID
FOR FETCH ONLY
SELECT ARSSERVR.ARSAPP.name, ARSSERVR.ARSAPP.description,
ARSSERVR.ARSAPP.agid, ARSSERVR.ARSAPP.aid, ARSSERVR.ARSAPP.doc_type,
ARSSERVR.ARSAPP.doc_comp_type, ARSSERVR.ARSAPP.res_comp_type,
ARSSERVR.ARSAPP.idx_type, ARSSERVR.ARSAPP.comp_obj_size,
ARSSERVR.ARSAPP.pid, ARSSERVR.ARSAPP.alias, ARSSERVR.ARSAPP.fixed_view,
ARSSERVR.ARSAPP.indexer, ARSSERVR.ARSAPP.preprocessor,
ARSSERVR.ARSAPP.upd_userid, ARSSERVR.ARSAPP.upd_date,
ARSSERVR.ARSAPP.db_exp_date, ARSSERVR.ARSAPP.appl_type,
ARSSERVR.ARSAPP.upd_dt, ARSSERVR.ARSAPPUSR.id,
LENGTH(ARSSERVR.ARSAPPUSR.user_view)
FROM ARSSERVR.ARSAPP, ARSSERVR.ARSAPPUSR
WHERE ARSSERVR.ARSAPP.agid = ? AND ARSSERVR.ARSAPP.aid
IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND
ARSSERVR.ARSAPP.agid = ARSSERVR.ARSAPPUSR.agid AND ARSSERVR.ARSAPP.aid =
ARSSERVR.ARSAPPUSR.aid AND ARSSERVR.ARSAPPUSR.id
IN (0, ?) ORDER BY ARSSERVR.ARSAPP.aid, ARSSERVR.ARSAPPUSR.id DESC FETCH
FIRST 182 ROWS ONLY OPTIMIZE FOR 182 ROWS FOR FETCH ONLY;
SELECT NID ,
NAME ,
DESCRIPTION ,
LOGON ,
PASSWD ,
SERVER ,
STATUS ,
SEC_NID ,
UPD_USERID ,
UPD_DATE ,
UPD_DT
FROM ARSSERVR . ARSNODE
WHERE SID = ?
ORDER BY NID
FOR FETCH ONLY