/**********************************************************************/
/* */
/* Name: VELUVM */
/* */
/* Status: Performance Reporter 1.1.0 */
/* */
/* Function: */
/* Define Update of table VMPRF_CONFIG_T from record VELRVSYS */
/* Define Update of table VMPRF_PROCESSOR_H from record VELRVCPU */
/* Define Update of table VMPRF_SYSTEM_H from record VELRVCPU */
/* Define Update of table VMPRF_SYSTEM_H from record VELRVSYS */
/* Define Update of table VMPRF_USER_H from record VELRVUSR */
/* Define Update of VMPRF_DASD_H from record VELRVDEV */
/* */
/* Change activity: */
/* 00 1999-04-07 SB Created */
/* */
/**********************************************************************/
/**********************************************************************/
/* Define Update of table VMPRF_CONFIG_T */
/**********************************************************************/
DROP UPDATE VELVM_VSYS_CONF_T;
DEFINE UPDATE VELVM_VSYS_CONF_T
VERSION 'IBM.110'
FROM VELRVSYS
TO &PREFIX.VMPRF_CONFIG_T
LET
(SYSTEM_ID = VALUE(:SYSTEM_ID,'$UNKNOWN'),
YEAR = CASE WHEN SUBSTR(DATE,1,2) > '50'
THEN '19' ELSE '20' END,
W_DATE = CASE WHEN (SUBSTR(DATE,7,2) = ' '
OR LENGTH(DATE) = 6)
THEN
DATE(YEAR 33
SUBSTR(DATE,1,2) 33 '-' 33
SUBSTR(DATE,3,2) 33 '-' 33
SUBSTR(DATE,5,2))
ELSE DATE(
SUBSTR(DATE,1,4) 33 '-' 33
SUBSTR(DATE,5,2) 33 '-' 33
SUBSTR(DATE,7,2))
END,
W_TSSTART = TIMESTAMP(W_DATE, STARTIME),
W_TSSTOP = TIMESTAMP(W_DATE, STOPTIME),
W_ETIME = INTERVAL(W_TSSTART, W_TSSTOP))
GROUP BY
(DATE = W_DATE,
TIME = TIME(ROUND(STARTIME, 1 HOUR)),
VM_SYSTEM_ID = SYSTEM_ID) --FROM SET COMMAND
SET
(CPU_ID = FIRST(SERIAL),
VM_VERSION = FIRST(SUBSTR(HCPCPEID,3,2)),
VM_RELEASE = FIRST(SUBSTR(HCPCPEID,1,2)),
VM_SERVICE_LEVEL = FIRST(SUBSTR(HCPCPEID,5,4)),
LAST_ABEND_CODE = FIRST(SYSABNCD),
TIME_OF_LAST_IPL = FIRST(SYSTODST),
TIME_OF_LAST_TERM = FIRST(SYSTERM),
STORAGE_REAL = FIRST(RSASTORE),
STORAGE_SYSGEN = FIRST(SYSTORS),
VR_SIZE = FIRST(SYSVRSZ2),
VR_FREE = FIRST(SYSVRFRE),
FRAMES_PAGEABLE = FIRST(RSAPGAB2),
FRAMES_NONPAGEABLE = FIRST(RSANONP2),
FRAMES_OFFLINE = FIRST(RSAOFFLN),
ESTOR_INSTALLED = FIRST(SYSXTSIZ));
/**********************************************************************/
/* Define Update for table VMPRF_PROCESSOR_H from VELRVCPU */
/**********************************************************************/
DROP UPDATE VELVM_VCPU_PROC_H;
DEFINE UPDATE VELVM_VCPU_PROC_H
VERSION 'IBM.110'
FROM VELRVCPU
TO &PREFIX.VMPRF_PROCESSOR_H
LET
(SYSTEM_ID = VALUE(:SYSTEM_ID,'$UNKNOWN'),
YEAR = CASE WHEN SUBSTR(DATE,1,2) > '50'
THEN '19' ELSE '20' END,
W_DATE = CASE WHEN (SUBSTR(DATE,7,2) = ' '
OR LENGTH(DATE) = 6)
THEN
DATE(YEAR 33
SUBSTR(DATE,1,2) 33 '-' 33
SUBSTR(DATE,3,2) 33 '-' 33
SUBSTR(DATE,5,2))
ELSE DATE(
SUBSTR(DATE,1,4) 33 '-' 33
SUBSTR(DATE,5,2) 33 '-' 33
SUBSTR(DATE,7,2))
END,
W_CPUADDR = CASE WHEN CPU = 'TOTAL'
THEN 99
ELSE CPUADDR
END,
W_TSSTART = TIMESTAMP(W_DATE, STARTIME),
W_TSSTOP = TIMESTAMP(W_DATE, STOPTIME),
W_ETIME = INTERVAL(W_TSSTART, W_TSSTOP))
GROUP BY
(DATE = W_DATE,
PERIOD_NAME = VALUE(PERIOD(SYSTEM_ID, W_DATE, STARTIME),'?'),
TIME = TIME(ROUND(STARTIME, 1 HOUR)),
VM_SYSTEM_ID = SYSTEM_ID, --FROM SET COMMAND
CPU_ADDRESS = W_CPUADDR)
SET
(CPU_ID = FIRST(PFXIDSER),
CPU_MODEL_NO = FIRST(PFXIDMDL),
ELAPSED_SECONDS = SUM(W_ETIME),
CONNECT_SEC = SUM(CPUUTIL * SESYTPRP),
PROC_USER_SEC = SUM(PFXPRBTM * SESYTPRP),
PROC_SYSTEM_SEC = SUM(PFXTMSYS * SESYTPRP),
PROC_EMUL_SEC = SUM(PFXUTIME * SESYTPRP),
SYSTEM_WAIT_SEC = SUM(PFXTOTWT * SESYTPRP),
SYSTEM_ONLINE_SEC = SUM(SESYTPRP),
VECTOR_USER_SEC = SUM(PLSVFVTM * SESYTPRP),
VF_OVERHEAD_SEC = SUM(PLSVFOTM * SESYTPRP),
-- All samples are the same size.
BUSY_TOTAL_PCT = AVG(CPUUTIL,RECORDS_COLLECTED),
BUSY_USER_PCT = AVG(PFXUTIME,RECORDS_COLLECTED),
BUSY_SYSTEM_PCT = AVG(PFXTMSYS,RECORDS_COLLECTED),
BUSY_VECTOR_PCT = AVG(PLSVFVTM,RECORDS_COLLECTED),
WAIT_PCT = AVG(PFXTOTWT,RECORDS_COLLECTED),
ESTOR_PAGEIN_RATE = AVG(PLSPGIN,RECORDS_COLLECTED),
ESTOR_PGEOUT_RATE = AVG(PLSPGOUT,RECORDS_COLLECTED),
DASD_PAGEIN_RATE = AVG(PLSPIOPR,RECORDS_COLLECTED),
DASD_PAGEOUT_RATE = AVG(PLSPIOPW,RECORDS_COLLECTED),
FASTPATH_PERCENT = AVG(PFXPGIN*100/(PFXPGIN+PLSPGIN),
RECORDS_COLLECTED),
RECORDS_COLLECTED = SUM(1),
PAGE_READS = SUM(PLSPIOPR * SESYTPRP),
PAGE_WRITES = SUM(PLSPIOPW * SESYTPRP),
PAGES_READ_TO_MS = SUM((PFXPGIN + PLSPGIN) * SESYTPRP),
PAGES_WRIT_TO_ES = SUM(PLSPGOUT * SESYTPRP),
SPOOL_READS = SUM(PLSPIOSR * SESYTPRP),
SPOOL_WRITES = SUM(PLSPIOSW * SESYTPRP),
DIAG_INSTRUCTIONS = SUM(PLSDIAGT * SESYTPRP),
SIMUL_INSTRUCTIONS = SUM(PLSPRVIS * SESYTPRP),
SIGP_INTERRUPTS = SUM(PLSEXTNC * SESYTPRP),
EXTERN_INTERRUPTS = SUM(PLSEXTNX * SESYTPRP),
CSCH_COUNT = SUM(PLSCTCS * SESYTPRP),
RSCH_COUNT = SUM(PLSCTRS * SESYTPRP),
SSCH_COUNT = SUM(PLSCTSS * SESYTPRP),
HSCH_COUNT = SUM(PLSCTHS * SESYTPRP)
);
/**********************************************************************/
/* Define Update for table VMPRF_SYSTEM_H from VELRVCPU */
/**********************************************************************/
DROP UPDATE VELVM_VCPU_SYST_H;
DEFINE UPDATE VELVM_VCPU_SYST_H
VERSION 'IBM.110'
FROM VELRVCPU
TO &PREFIX.VMPRF_SYSTEM_H
LET
(
SYSTEM_ID = VALUE(:SYSTEM_ID,'$UNKNOWN'),
YEAR = CASE WHEN SUBSTR(DATE,1,2) > '50'
THEN '19' ELSE '20' END,
W_DATE = CASE WHEN (SUBSTR(DATE,7,2) = ' '
OR LENGTH(DATE) = 6)
THEN
DATE(YEAR 33
SUBSTR(DATE,1,2) 33 '-' 33
SUBSTR(DATE,3,2) 33 '-' 33
SUBSTR(DATE,5,2))
ELSE DATE(
SUBSTR(DATE,1,4) 33 '-' 33
SUBSTR(DATE,5,2) 33 '-' 33
SUBSTR(DATE,7,2))
END,
W_COUNT = CASE WHEN QUALIFIE = 'TOTAL'
THEN 0
ELSE 1
END,
W_TSSTART = TIMESTAMP(W_DATE, STARTIME),
W_TSSTOP = TIMESTAMP(W_DATE, STOPTIME),
W_ETIME = INTERVAL(W_TSSTART, W_TSSTOP)
)
GROUP BY
(
DATE = W_DATE,
PERIOD_NAME = VALUE(PERIOD(SYSTEM_ID, W_DATE, STARTIME),'?'),
TIME = TIME(ROUND(STARTIME, 1 HOUR)),
VM_SYSTEM_ID = SYSTEM_ID --FROM SET COMMAND
)
SET
(
CPU_ID = FIRST(PFXIDSER),
CPU_MODEL_NO = FIRST(PFXIDMDL),
ELAPSED_SECONDS = SUM(W_ETIME),
CONNECT_SEC = SUM(CPUUTIL * SESYTPRP),
PROC_USER_SEC = SUM(PFXPRBTM * SESYTPRP),
PROC_SYSTEM_SEC = SUM(PFXTMSYS * SESYTPRP),
PROC_EMUL_SEC = SUM(PFXUTIME * SESYTPRP),
SYSTEM_WAIT_SEC = SUM(PFXTOTWT * SESYTPRP),
SYSTEM_ONLINE_SEC = SUM(SESYTSYP),
VECTOR_USER_SEC = SUM(PLSVFVTM * SESYTPRP),
VF_OVERHEAD_SEC = SUM(PLSVFOTM * SESYTPRP),
-- All samples are the same size.
BUSY_TOTAL_PCT = AVG(CPUUTIL,RECORDS_COLLECTED),
BUSY_USER_PCT = AVG(PFXUTIME,RECORDS_COLLECTED),
BUSY_SYSTEM_PCT = AVG(PFXTMSYS,RECORDS_COLLECTED),
BUSY_VECTOR_PCT = AVG(PLSVFVTM,RECORDS_COLLECTED),
WAIT_PCT = AVG(PFXTOTWT,RECORDS_COLLECTED),
ESTOR_PAGEIN_RATE = AVG(PLSPGIN,RECORDS_COLLECTED),
ESTOR_PGEOUT_RATE = AVG(PLSPGOUT,RECORDS_COLLECTED),
DASD_PAGEIN_RATE = AVG(PLSPIOPR,RECORDS_COLLECTED),
DASD_PAGEOUT_RATE = AVG(PLSPIOPW,RECORDS_COLLECTED),
FASTPATH_PERCENT = AVG(PFXPGIN*100/(PFXPGIN+PLSPGIN),
RECORDS_COLLECTED),
RECORDS_COLLECTED = SUM(1),
PAGE_READS = SUM(PLSPIOPR * SESYTPRP),
PAGE_WRITES = SUM(PLSPIOPW * SESYTPRP),
PAGES_READ_TO_MS = SUM((PFXPGIN + PLSPGIN) * SESYTPRP),
PAGES_WRIT_TO_ES = SUM(PLSPGOUT * SESYTPRP),
SPOOL_READS = SUM(PLSPIOSR * SESYTPRP),
SPOOL_WRITES = SUM(PLSPIOSW * SESYTPRP),
DIAG_INSTRUCTIONS = SUM(PLSDIAGT * SESYTPRP),
SIMUL_INSTRUCTIONS = SUM(PLSPRVIS * SESYTPRP),
SIGP_INTERRUPTS = SUM(PLSEXTNC * SESYTPRP),
EXTERN_INTERRUPTS = SUM(PLSEXTNX * SESYTPRP),
CSCH_COUNT = SUM(PLSCTCS * SESYTPRP),
RSCH_COUNT = SUM(PLSCTRS * SESYTPRP),
SSCH_COUNT = SUM(PLSCTSS * SESYTPRP),
HSCH_COUNT = SUM(PLSCTHS * SESYTPRP)
);
/**********************************************************************/
/* Define Update for table VMPRF_SYSTEM_H from VELRVCPU */
/**********************************************************************/
DROP UPDATE VELVM_VSYS_SYST_H;
DEFINE UPDATE VELVM_VSYS_SYST_H
VERSION 'IBM.110'
FROM VELRVSYS
TO &PREFIX.VMPRF_SYSTEM_H
LET
(SYSTEM_ID = VALUE(:SYSTEM_ID,'$UNKNOWN'),
YEAR = CASE WHEN SUBSTR(DATE,1,2) > '50'
THEN '19' ELSE '20' END,
W_DATE = CASE WHEN (SUBSTR(DATE,7,2) = ' '
OR LENGTH(DATE) = 6)
THEN
DATE(YEAR 33
SUBSTR(DATE,1,2) 33 '-' 33
SUBSTR(DATE,3,2) 33 '-' 33
SUBSTR(DATE,5,2))
ELSE DATE(
SUBSTR(DATE,1,4) 33 '-' 33
SUBSTR(DATE,5,2) 33 '-' 33
SUBSTR(DATE,7,2))
END,
W_TSSTART = TIMESTAMP(W_DATE, STARTIME),
W_TSSTOP = TIMESTAMP(W_DATE, STOPTIME),
W_ETIME = INTERVAL(W_TSSTART, W_TSSTOP))
GROUP BY
(DATE = W_DATE,
PERIOD_NAME = VALUE(PERIOD(SYSTEM_ID, W_DATE, STARTIME),'?'),
TIME = TIME(ROUND(STARTIME, 1 HOUR)),
VM_SYSTEM_ID = SYSTEM_ID) --FROM SET COMMAND
SET
(USERS_LOGGED = SUM(SYSUSRS), --AVG
USERS_DORMANT = SUM(SRMCDORM)); --AVG
/**********************************************************************/
/* Define Update for table VMPRF_USER_H from VELRVUSR */
/**********************************************************************/
DROP UPDATE VELVM_VUSR_USER_H;
DEFINE UPDATE VELVM_VUSR_USER_H
VERSION 'IBM.110'
FROM VELRVUSR
WHERE (USRTYPE = 'USER' OR USRTYPE = 'SYST' OR USRTYPE = 'SRVR')
TO &PREFIX.VMPRF_USER_H
LET
(SYSTEM_ID = VALUE(:SYSTEM_ID,'$UNKNOWN'),
YEAR = CASE WHEN SUBSTR(DATE,1,2) > '50'
THEN '19' ELSE '20' END,
W_DATE = CASE WHEN (SUBSTR(DATE,7,2) = ' '
OR LENGTH(DATE) = 6)
THEN
DATE(YEAR 33
SUBSTR(DATE,1,2) 33 '-' 33
SUBSTR(DATE,3,2) 33 '-' 33
SUBSTR(DATE,5,2))
ELSE DATE(
SUBSTR(DATE,1,4) 33 '-' 33
SUBSTR(DATE,5,2) 33 '-' 33
SUBSTR(DATE,7,2))
END,
W_TSSTART = TIMESTAMP(W_DATE, STARTIME),
W_TSSTOP = TIMESTAMP(W_DATE, STOPTIME),
W_ETIME = INTERVAL(W_TSSTART, W_TSSTOP))
GROUP BY
(DATE = W_DATE,
PERIOD_NAME = VALUE(PERIOD(SYSTEM_ID, W_DATE, STARTIME),'?'),
TIME = TIME(ROUND(STARTIME, 1 HOUR)),
VM_SYSTEM_ID = SYSTEM_ID, --FROM SET COMMAND
USER_CLASS = VALUE(LOOKUP GROUP_NAME
IN &PREFIX.USER_GROUP
WHERE SYSTEM_ID LIKE SYSTEM_ID AND
USRID LIKE USER_ID,
CLASSID),
USER_ID = USRID)
SET
(ACTIVE_SEC = SUM(VACTIVE),
CONNECT_SEC = SUM(CONNECT),
LOGGED_SEC = SUM(LOGGEDTM),
CPU_TOTAL_SEC = SUM(VMDTTIME),
CPU_VIRTUAL_SEC = SUM(VMDVTIME),
VECTOR_USER_SEC = SUM(VMDVFVTM),
RECORDS_COLLECTED = SUM(1),
DASD_IO = SUM(VMDVDSCT),
CONSOLE_IO = SUM(VMDVCSCT),
UR_IO = SUM(VMDVUSCT),
CTC_IO = SUM(VMDVTSCT),
OTHER_IO = SUM(VMDVOSCT),
DASD_IO_RATE = AVG(VMDVDSCT/LOGGEDTM,RECORDS_COLLECTED),
PAGE_IO_RATE = AVG(((VMDCTPGR + VMDCTPGW)/LOGGEDTM),
RECORDS_COLLECTED),
PAGE_READS = SUM(VMDCTPGR),
PAGE_WRITES = SUM(VMDCTPGW),
PAGES_READ_TO_MS = SUM(VMDCTXRD),
PAGES_WRIT_TO_ES = SUM(VMDCTXWT),
SPOOL_READS = SUM(VMDCTSPR),
SPOOL_WRITES = SUM(VMDCTSPW),
IUCV_DATA_RECEIVED = SUM(VMDISTVM),
IUCV_DATA_FAILED = SUM(VMDISUVM),
IUCV_DATA_SENT = SUM(VMDISEVM),
IUCV_MSGQ_SEND = SUM(CALIUCVS),
VMCF_DATA_RECEIVED = SUM(VMDVSTVM),
VMCF_DATA_FAILED = SUM(VMDVSUVM),
VMCF_DATA_SENT = SUM(VMDVSEVM));
/**********************************************************************/
/* Define Update for VMPRF_DASD_H from VELRVDEV */
/**********************************************************************/
DROP UPDATE VELVM_VDEV_DASD_H;
DEFINE UPDATE VELVM_VDEV_DASD_H
VERSION 'IBM.110'
FROM VELRVDEV
TO &PREFIX.VMPRF_DASD_H
LET
(SYSTEM_ID = VALUE(:SYSTEM_ID,'$UNKNOWN'),
YEAR = CASE WHEN SUBSTR(DATE,1,2) > '50'
THEN '19' ELSE '20' END,
W_DATE = CASE WHEN (SUBSTR(DATE,7,2) = ' '
OR LENGTH(DATE) = 6)
THEN
DATE(YEAR 33
SUBSTR(DATE,1,2) 33 '-' 33
SUBSTR(DATE,3,2) 33 '-' 33
SUBSTR(DATE,5,2))
ELSE DATE(
SUBSTR(DATE,1,4) 33 '-' 33
SUBSTR(DATE,5,2) 33 '-' 33
SUBSTR(DATE,7,2))
END,
W_TSSTART = TIMESTAMP(W_DATE, STARTIME),
W_TSSTOP = TIMESTAMP(W_DATE, STOPTIME),
W_ETIME = INTERVAL(W_TSSTART, W_TSSTOP))
GROUP BY
(DATE = W_DATE,
PERIOD_NAME = VALUE(PERIOD(SYSTEM_ID, W_DATE, STARTIME),'?'),
TIME = TIME(ROUND(STARTIME, 1 HOUR)),
VM_SYSTEM_ID = SYSTEM_ID,
DEVICE_NUMBER = RDEVDEV,
VOLSER = RDEVSER)
SET
(CONNECT_SEC = SUM(SCMCNTIM * SCMSSCH),
DISCONN_SEC = SUM(SCMDDTIM * SCMSSCH),
PENDING_SEC = SUM(SCMFPTIM * SCMSSCH),
ELAPSED_SEC = SUM(W_ETIME),
HF_SAMPLES = SUM(HFRDEVCT),
ONLINE_SEC = SUM(SEIODDEV),
CONNECT_MSEC = AVG((SCMCNTIM * SCMSSCH),SSCH_AND_RSCH),
DISCONN_MSEC = AVG((SCMDDTIM * SCMSSCH),SSCH_AND_RSCH),
PENDING_MSEC = AVG((SCMFPTIM * SCMSSCH),SSCH_AND_RSCH),
SERVICE_MSEC = AVG(SERVTIME*SCMSSCH,SSCH_AND_RSCH),
BUSY_PCT = AVG(DEVBUSY,RECORDS_COLLECTED),
DEVICE_IO_RATE = AVG(RATE,RECORDS_COLLECTED),
DEVICE_QUEUE = AVG(QUEUELEN,RECORDS_COLLECTED),
SSCH_QUEUED = SUM(HFCTIO),
SSCH_AND_RSCH = SUM(SCMSSCH),
RECORDS_COLLECTED = COUNT(1));
If you can't measure it, I'm just not interested!™
© 2006 Velocity Software, Inc.
Webmaster
