I am looking out for the help to calculate Total Duration and Time in state (@ View Point )through dbql tables.
While monitoring we can point out certain sql with these parameters.But for batch queries for monthly audit these figures are very important. Through these reports it will be easy to find sqls continuously taking long time to complete
Is there any way I can get these from DBQL?
Difference between StartTime and FirstStepTime is certainly not these. It does not cover the last packet recive time.
Cant use LastRespTime as it is NULL for my box (The timestamp of the last response. This field is NULL if you are not using DBQL CPU/IO Collection algorithm 3.)
Can it be difference between LastStateChange and StartTime ( After completion, session will change the state from RESPONDING to COMPLETE)
You probably want to start with:
Select UserId, QuerId, FirstStepTime, FirstRespTime, cast(FirstRespTime-FirstStepTime as interval hour to second) as ELT from QryLogV
The FirstStepTime is when the work begins; FirstRespTime is when the final result set is queued in spool and ready to send to the user. These two fields tell you how long it took to run the query, not counting the time it took to transmit results to the user. You might also want to join this query to QryLogSQLV, QryLogStepsV and/or QryLogExplainV to learn what parts of long-running queries took the longest.
Total Duration and Time In State are essentially "delta" figures between snapshots. LastStateChange is something entirely different (and if you look, it's generally well before StartTime - not of use here.)
StartTime = when the parser sees the request
FirstStepTime = when the AMP(s) start processing the first query step for the request
FirstRespTime = when the last AMP says the last query step is done
FirstStepTime - StartTime includes throttle DelayTime as well as time waiting for MLK steps ("First, we lock...") and internal delays such as acquiring a WORKNEW AWT.
FirstRespTime - FirstStepTime is the elapsed time during which the request executes query steps (potentially consuming CPU)
If you need total end-to-end request duration or specifically time in "Responding" state, then you can set DBQLLogLastResp to True in dbscontrol to log a dummy "RESP" step in DBQLStepTbl when Response mode ends, or (as you mention) use collection algorithm 3 so LastRespTime is populated in DBQLogTbl.
BTW - there is no "Complete" state, the session just goes Idle or logs off.
LastStateChange will definitely be abort or session completion (IDEL or LOGOFF)
I will go with difference in LastStateChange and StartTime. This seems logical as this will the time through which resources are in use by the session.
LastStateChange refers to a TASM / TDWM system state, which isn't something directly related to the query itself.