Calculating standard deviation
Standard Deviation is a statistical estimate of the amount of variation in numerical values. The higher the standard deviation the more variation in the values. CICS PA requires the Sum of Squares to be loaded into the DB2® table to calculate standard deviation.
The following example calculates the standard deviation of response
time. The CASE statement shows the function required to calculate
standard deviation.
SELECT TRAN,
INT(SUM(TASKCNT)) AS TASKCNT,
DEC(SUM(RESPONSE_TIME),8,6) AS RESPONSE_TIME_TOT,
DEC(SUM(RESPONSE_TIME)/SUM(TASKCNT),8,6) AS RESPONSE_TIME_AVG,
CASE WHEN (SUM(TASKCNT) > 1) THEN
DEC(SQRT(((SUM(TASKCNT)*SUM(RESPONSE_TIME_SSQ))
-POWER(SUM(RESPONSE_TIME),2))
/(SUM(TASKCNT)*(SUM(TASKCNT)-1))),10,4)
ELSE 0
END AS RESPONSE_TIME_DEV
FROM CICSPA.CICSP1H
GROUP BY TRAN
This query produces output like that shown in Figure 1
Figure 1. SQL query calculating standard deviation of response
time
RESPONSE RESPONSE RESPONSE
TIME TIME TIME
TRAN TASKCNT TOT AVG DEV
---- ----------- ----------- ----------- ------------------ $
SGM 1 0.418736 0.418736 0.0000
ABAL 3 0.002592 0.000864 0.0000
ATRN 7 0.007104 0.001014 0.0001
AUTS 1 0.000752 0.000752 0.0000
BALA 4 0.004016 0.001004 0.0004
CATA 2 0.006336 0.003168 0.0000
CRSR 5 0.001696 0.000339 0.0000
CSGM 1 0.000528 0.000528 0.0000
CSMI 11 0.009120 0.000829 0.0004
CSSN 2 0.001232 0.000616 0.0000
DESC 2 0.001280 0.000640 0.0000