/**********************************************************************/ /* */ /* 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
