Maurice van ’t Loo

Skip to content

Daily scripts

SESS - "query sessions" replacement; gives an overview of all active client sessions.

DELETE SCRIPT  SESS
Y
DEFINE SCRIPT  SESS DESC="Gives an overview of all active client sessions"
UPDATE SCRIPT  SESS "SELECT -"
UPDATE SCRIPT  SESS "  SESSION_ID, -"
UPDATE SCRIPT  SESS "  cast(varchar_format(START_TIME, 'YYYY-MM-DD HH24:MI:SS') as char(19)) as START, -"
UPDATE SCRIPT  SESS "  cast(STATE as char(7)) as STATE, -"
UPDATE SCRIPT  SESS "  cast(WAIT_SECONDS as dec(5)) as WAIT, -"
UPDATE SCRIPT  SESS "  cast(BYTES_SENT/1073741824 as decimal(8,2)) as GB_SENT, -"
UPDATE SCRIPT  SESS "  cast(BYTES_SENT/1048576/timestampdiff(2,current_timestamp-START_TIME+1) as decimal(6,2)) as MBS_SENT, -"
UPDATE SCRIPT  SESS "  cast(BYTES_RECEIVED/1073741824 as decimal(8,2)) as GB_RECV, -"
UPDATE SCRIPT  SESS "  cast(BYTES_RECEIVED/1048576/timestampdiff(2,current_timestamp-START_TIME+1) as decimal(6,2)) as MBS_RECV, -"
UPDATE SCRIPT  SESS "  substr(CLIENT_NAME,1,35) as NODE_NAME, -"
UPDATE SCRIPT  SESS "  substr(OUTPUT_VOL_ACCESS,locate(',',OUTPUT_VOL_ACCESS)+1,8) as OUTPUT, -"
UPDATE SCRIPT  SESS "  substr(INPUT_VOL_ACCESS,locate(',',INPUT_VOL_ACCESS)+1,8) as INPUT -"
UPDATE SCRIPT  SESS "FROM SESSIONS -"
UPDATE SCRIPT  SESS "WHERE upper(SESSION_TYPE)='NODE' -"
UPDATE SCRIPT  SESS "ORDER BY 1"

UPDATE SCRIPT  SESS "SELECT -"
UPDATE SCRIPT  SESS "  SESSION_TYPE, -"
UPDATE SCRIPT  SESS "  STATE, -"
UPDATE SCRIPT  SESS "  count(*) as NR -"
UPDATE SCRIPT  SESS "FROM SESSIONS -"
UPDATE SCRIPT  SESS "GROUP BY SESSION_TYPE,STATE -"
UPDATE SCRIPT  SESS "ORDER BY 1,2"

UPDATE SCRIPT  SESS "SELECT -"
UPDATE SCRIPT  SESS "  SESSION_TYPE, -"
UPDATE SCRIPT  SESS "  cast(sum(GB_SENT) as decimal(8,2)) as SUM_GB_SENT, -"
UPDATE SCRIPT  SESS "  cast(sum(MBS_SENT) as decimal(8,2)) as SUM_MBS_SENT, -"
UPDATE SCRIPT  SESS "  cast(sum(GB_RECV) as decimal(8,2)) as SUM_GB_RECV, -"
UPDATE SCRIPT  SESS "  cast(sum(MBS_RECV) as decimal(8,2)) as SUM_MBS_RECV -"
UPDATE SCRIPT  SESS "FROM ( -"
UPDATE SCRIPT  SESS "  SELECT -"
UPDATE SCRIPT  SESS "    case when CLIENT_NAME='$$_TSMDBMGR_$$' then 'DB_backup' else SESSION_TYPE end as SESSION_TYPE, -"
UPDATE SCRIPT  SESS "    BYTES_SENT/1048576/timestampdiff(2,current_timestamp-START_TIME+1) as MBS_SENT, -"
UPDATE SCRIPT  SESS "    BYTES_RECEIVED/1048576/timestampdiff(2,current_timestamp-START_TIME+1) as MBS_RECV, -"
UPDATE SCRIPT  SESS "    BYTES_SENT/1073741824 as GB_SENT, -"
UPDATE SCRIPT  SESS "    BYTES_RECEIVED/1073741824 as GB_RECV -"
UPDATE SCRIPT  SESS "  FROM SESSIONS -"
UPDATE SCRIPT  SESS ") -"
UPDATE SCRIPT  SESS "GROUP BY SESSION_TYPE -"
UPDATE SCRIPT  SESS "ORDER BY 1"

PR - "query process" replacement, gives an overview of running processes.

DELETE SCRIPT  PR
Y
DEFINE SCRIPT  PR DESC="Show overview of active processes"
UPDATE SCRIPT  PR "SELECT -"
UPDATE SCRIPT  PR "  PROCESS_NUM, -"
UPDATE SCRIPT  PR "  cast(varchar_format(START_TIME,'YYYY-MM-DD HH24:MI') as char(16)) as START_TIME, -"
UPDATE SCRIPT  PR "  cast(PROCESS as char(30)) as ACTIVITY, -"
UPDATE SCRIPT  PR "  cast(BYTES_PROCESSED/1073741824 as decimal(8,2)) as GB, -"
UPDATE SCRIPT  PR "  cast(BYTES_PROCESSED/1048576/(timestampdiff(2,current_timestamp-start_time)+1) as decimal(8,2)) as MBSEC, -"
UPDATE SCRIPT  PR "  cast(BYTES_TO_PROCESS/1073741824 as decimal(8,2)) as GB_TODO -"
UPDATE SCRIPT  PR "FROM PROCESSES -"
UPDATE SCRIPT  PR "ORDER BY 1"

UPDATE SCRIPT  PR "SELECT -"
UPDATE SCRIPT  PR "  cast(PROCESS as char(69)) as PROCESS, -"
UPDATE SCRIPT  PR "  cast(sum(BYTES_PROCESSED/1073741824) as decimal(8,2)) as GB, -"
UPDATE SCRIPT  PR "  cast(sum(BYTES_PROCESSED/1048576/timestampdiff(2,current_timestamp-START_TIME+1)) as decimal(8,2)) as MBSEC, -"
UPDATE SCRIPT  PR "  cast(sum(BYTES_TO_PROCESS/1073741824) as decimal(8,2)) as GB_TODO -"
UPDATE SCRIPT  PR "FROM PROCESSES -"
UPDATE SCRIPT  PR "GROUP BY PROCESS -"
UPDATE SCRIPT  PR "ORDER BY 1"

PH - Shows the results of some processes of the last number of days.
Give amount of days as attribute, example: RUN PH 3

DELETE SCRIPT  PH
Y
DEFINE SCRIPT  PH DESC="Show overview of some processes for last $1 days"
UPDATE SCRIPT  PH "SELECT -"
UPDATE SCRIPT  PH "  cast(varchar_format(START_TIME,'YYYY-MM-DD HH24:MI') as char(16)) as START, -"
UPDATE SCRIPT  PH "  cast(varchar_format(END_TIME,'YYYY-MM-DD HH24:MI') as char(16)) as END, -"
UPDATE SCRIPT  PH "  substr(ACTIVITY,1,16) as PROCESS, -"
UPDATE SCRIPT  PH "  substr(ENTITY,1,30)as ENTITY, -"
UPDATE SCRIPT  PH "  cast(cast(BYTES as decimal(20,2))/1073741824 as decimal(9,2)) as GB, -"
UPDATE SCRIPT  PH "  cast(cast(BYTES as decimal(20,2))/1048576/(timestampdiff(2,END_TIME-START_TIME)+1) as decimal(9,2)) as MBSEC, -"
UPDATE SCRIPT  PH "  cast(MEDIAW as decimal(6)) as MEDIAW, -"
UPDATE SCRIPT  PH "  SUCCESSFUL -"
UPDATE SCRIPT  PH "FROM SUMMARY -" 
UPDATE SCRIPT  PH "WHERE (ACTIVITY in ('STGPOOL BACKUP','MIGRATION','FULL_DBBACKUP','REPLICATION','STGPOOL PROTECTION') or (activity='EXPIRATION' and ENTITY is null)) and (days(current_date)-days(START_TIME))<$1 -"
UPDATE SCRIPT  PH "ORDER BY 1,2"

MO - Gives an overview of the tapedrives and their status.

DELETE SCRIPT  MO
Y
DEFINE SCRIPT  MO DESC="Display overview of all drives"
UPDATE SCRIPT  MO "SELECT -"
UPDATE SCRIPT  MO "  cast(d.LIBRARY_NAME as char(6)) as LIBR, -"
UPDATE SCRIPT  MO "  cast(d.DRIVE_NAME as char(12)) as DRIVE, -"
UPDATE SCRIPT  MO "  cast(d.ALLOCATED_TO as char (22)) as ALLOCATED_TO, -"
UPDATE SCRIPT  MO "  cast(d.DRIVE_STATE as char (12)) as DRIVE_STATE, -"
UPDATE SCRIPT  MO "  cast(d.ONLINE as char(8)) as ONLINE, -"
UPDATE SCRIPT  MO "  cast(p.DEVICE as char(20)) as DEVICE, -"
UPDATE SCRIPT  MO "  cast(d.VOLUME_NAME as char(11)) as VOLUME, -"
UPDATE SCRIPT  MO "  case when pc.NUM_PATHS is null then 0 else pc.NUM_PATHS end as NUM_PATHS -"
UPDATE SCRIPT  MO "FROM DRIVES d -"
UPDATE SCRIPT  MO "LEFT JOIN ( -"
UPDATE SCRIPT  MO "  SELECT -"
UPDATE SCRIPT  MO "    * -"
UPDATE SCRIPT  MO "  FROM PATHS -"
UPDATE SCRIPT  MO "  WHERE SOURCE_NAME=(SELECT SERVER_NAME FROM STATUS) -"
UPDATE SCRIPT  MO ") p -"
UPDATE SCRIPT  MO "ON d.DRIVE_NAME=p.DESTINATION_NAME -"
UPDATE SCRIPT  MO "LEFT JOIN ( -"
UPDATE SCRIPT  MO "  SELECT -"
UPDATE SCRIPT  MO "    DESTINATION_NAME, -"
UPDATE SCRIPT  MO "    count(1) as NUM_PATHS -"
UPDATE SCRIPT  MO "  FROM PATHS -"
UPDATE SCRIPT  MO "  GROUP BY DESTINATION_NAME -"
UPDATE SCRIPT  MO ") pc -"
UPDATE SCRIPT  MO "ON d.DRIVE_NAME=pc.DESTINATION_NAME -"
UPDATE SCRIPT  MO "ORDER BY 1,2"

S - Display an overview of scratch tapes per library

DELETE SCRIPT  S
Y
DEFINE SCRIPT  S DESC="Display overview of scratch tapes"
UPDATE SCRIPT  S "SELECT -"
UPDATE SCRIPT  S "  libraries.LIBRARY_NAME, -"
UPDATE SCRIPT  S "  case when libvol.SCRATCH is null then 0 else libvol.SCRATCH end as SCRATCH, -"
UPDATE SCRIPT  S "  case when libvol.TOTAL is null then 0 else libvol.TOTAL end as TOTAL, -"
UPDATE SCRIPT  S "  case when libvol.TOTAL is null then 0 else libvol.SCRATCH*100/libvol.TOTAL end||' %' as PCT_FREE -"
UPDATE SCRIPT  S "FROM LIBRARIES -"
UPDATE SCRIPT  S "LEFT OUTER JOIN ( -"
UPDATE SCRIPT  S "  SELECT -"
UPDATE SCRIPT  S "    LIBRARY_NAME, -"
UPDATE SCRIPT  S "    sum(case when libvolumes.STATUS like 'Scratch%' then 1 else 0 end) as SCRATCH, -"
UPDATE SCRIPT  S "    case when count(1)>0 then count(1) else 0 end as TOTAL -"
UPDATE SCRIPT  S "  FROM LIBVOLUMES -"
UPDATE SCRIPT  S "  GROUP BY LIBRARY_NAME -"
UPDATE SCRIPT  S ") as libvol -"
UPDATE SCRIPT  S "ON libraries.LIBRARY_NAME=libvol.LIBRARY_NAME -"
UPDATE SCRIPT  S "WHERE LIBRARY_TYPE='SCSI'"