Reducing ARSAPP, ARSAPPUSR, ARSFOL, ARSFOLPERMS accesses

[jfunk]

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         

[Alessandro Perucchi]

Hello jfunk,

good question, maybe that would be a good question for a PMR

Sincerely yours,
Alessandro

[Justin Derrick]

It's not likely that these queries are going away anytime soon.  They are the basic blocks of information that clients need when they log in.

Maybe try extending long in time-outs, or ensure you're doing connection pooling if your users are web based.

-JD.