Is there a way using SQL to determine how long my CA Datacom Multi-User Facility (called MUF) has been running?

Document ID : KB000018992
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Is there a way using the SQL feature of CA Datacom to determine how long my MUF has been running?

I would like to programmatically determine this for availability and other reporting.

Solution:

The MUF start time and the current time are available from the CA Datacom/DB Datacom System Tables, and can be reported using a simple SQL query:


SELECT MUF_NAME,
       DATE(BEGIN_TIME)       AS BEGDATE,
       TIME(BEGIN_TIME)       AS BEGTIME,
       DATE(CURRENT_DATETIME) AS CURDATE,
       TIME(CURRENT_DATETIME) AS CURTIME,
  FROM MUF_SYSTEM_STATS;
/*

This will produce a simple report of the start date and time, and the current date and time. However, since BEGIN_TIME and CURRENT_DATETIME are both timestamp fields, there is currently no capability to subtract timestamps to produce an execution time.

In order to produce a comprehensive report showing the start time, current time and execution duration, a more complex query can be used:


-- .5...10....5...20....5...30....5...40....5...50....5...60....5...70..
SELECT                                                                
 MUF_NAME,                                                            
 CHAR(DATE(BEGIN_TIME))  || ' @ ' ||  CHAR(TIME(BEGIN_TIME))          
                                     AS "MUF START TIME",             
 CHAR(DATE(CURRENT_DATETIME)) || ' @ ' || CHAR(TIME(CURRENT_DATETIME))
                                     AS "CURRENT TIME",               
 SUBSTR(DIGITS(                                                       
    CASE WHEN TIME(CURRENT_DATETIME) < TIME(BEGIN_TIME)               
         THEN DATE(CURRENT_DATETIME) - DATE(BEGIN_TIME) - 1           
         ELSE DATE(CURRENT_DATETIME) - DATE(BEGIN_TIME)               
    END),7) || 'D - '                                                 
 ||                                                                   
 SUBSTR(DIGITS(                                                       
    CASE WHEN TIME(CURRENT_DATETIME) < TIME(BEGIN_TIME)               
         THEN (HOUR(TIME('24.00.00') -                                
                   (TIME(BEGIN_TIME) - TIME(CURRENT_DATETIME))))      
         ELSE (HOUR(TIME(CURRENT_DATETIME)   - TIME(BEGIN_TIME)))     
    END),9) || 'H '                                                   
 ||                                                                   
 SUBSTR(DIGITS(                                                       
    CASE WHEN TIME(CURRENT_DATETIME) < TIME(BEGIN_TIME)               
         THEN (MINUTE(TIME('24.00.00') -                              
                   (TIME(BEGIN_TIME) - TIME(CURRENT_DATETIME))))      
         ELSE (MINUTE(TIME(CURRENT_DATETIME) - TIME(BEGIN_TIME)))     
    END),9) || 'M '                                                   
 ||                                                                   
 SUBSTR(DIGITS(                                                       
    CASE WHEN TIME(CURRENT_DATETIME) < TIME(BEGIN_TIME)               
         THEN (SECOND(TIME('24.00.00') -                              
                   (TIME(BEGIN_TIME) - TIME(CURRENT_DATETIME))))      
         ELSE (SECOND(TIME(CURRENT_DATETIME) - TIME(BEGIN_TIME)))     
    END),9) || 'S'                   AS "MUF ELAPSED TIME"            
FROM SYSADM.MUF_SYSTEMS_STATS;

For more information about the Datacom System Tables, please refer to the CA Datacom/DB System Tables Reference Guide.

As always, please contact CA Technologies support for CA Datacom if you have further questions.