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 

Information Information

Feedback


Timestamp icon Last updated: Friday, 8 February 2013


http://pic.dhe.ibm.com/infocenter/cicsts/v5r1/topic//cpaug325.htm