How to review LXX (Log Area) settings and values using a query for SQL with program DBSQLPR.
search cancel

How to review LXX (Log Area) settings and values using a query for SQL with program DBSQLPR.

book

Article ID: 41437

calendar_today

Updated On:

Products

Datacom DATACOM - AD Datacom/AD Datacom/DB

Issue/Introduction

While theDatacom/DB or Datacom/AD Multi-User Facility (called MUF) is running, it is difficult to determine the status of the LXX (Log file) Area, and to determine how full the LXX might be. This article will provide an SQL query that provides some key information to help manage the LXX.

 

Environment

Database/DB SQL Option

Resolution

The query listed below can be run using the DBSQLPR utility program. To provide the best display of information in the SYSOUT spool file, we recommend the use of the NOTYPE and NOECHO options. This can be specified using the following z/OS EXEC parameter: PARM='INPUTWIDTH=72,AUTHID=SYSADM,NOTYPE,NOECHO'
or the individual options can be entered through the OPTIONS DD statement. Because this query accesses the Datacom System Tables, you will also need the SYSADM AuthID.

  

Here is the SYSIN:

//SYSIN    DD  *                                                       
--  -----------------------------------------------------------------7--
--  THIS INPUT IS BROKEN INTO MULTIPLE PIECES TO MAKE AN EASIER DISPLAY
--  AND TO SEPARATE RELATED ITEMS. ALL FIELDS CAN BE CREATED ON ONE    
--  LINE IF DESIRED (USE OPTION PRTWIDTH=250 AND SYSOUT=*,LRECL=250).  
--  -----------------------------------------------------------------7--
                                                                       
SELECT '- - - - - - - - - MUF DATE AND TIME INFO  - - - - - - - - - -' 
                               AS " " FROM MUF_AREA_STATS              
                                      WHERE AREA_NAME = 'LXX'          
UNION                                                                  
SELECT '--------------------------------------------------------------'
                                      FROM MUF_AREA_STATS;             
*                                                                      
SELECT                                                                 
   MFA.MUF_NAME                               AS  "MUF NAME"           
--  -----------------------------------------------------------------7--
--  EXTRACT DATE AND TIME FROM TIMESTAMP VALUES AS Y-M-D @ H.M.S       
--                                                                     
  ,CHAR(DATE(MFA.BEGIN_TIME))      ||' '|| CHAR(TIME(MFA.BEGIN_TIME))  
                                             AS "  MUF START TIME"     
  ,CHAR(DATE(MFA.CURRENT_DATETIME))||' '||                             
                                       CHAR(TIME(MFA.CURRENT_DATETIME))
                                             AS "   CURRENT TIME"      
--  -----------------------------------------------------------------7--
--  CALCULATE DIFFERENCE BETWEEN TIMESTAMPS, AS DAYS-HR.MN.SEC         
--                                                                      
  ,SUBSTR(DIGITS(DAYS(MFA.CURRENT_DATETIME) - DAYS(MFA.BEGIN_TIME) -   
    CASE WHEN TIME(MFA.CURRENT_DATETIME) < TIME(MFA.BEGIN_TIME)        
         THEN 1 ELSE 0 END),7) || '-' ||                               
    CHAR(TIME('00.00.00') +                                            
        (TIME(MFA.CURRENT_DATETIME) - TIME(MFA.BEGIN_TIME)))           
                                             AS "   ELAPSED"           
  FROM  MUF_AREA_STATS MFA                                              
  WHERE AREA_NAME = 'LXX';                                             
*                                                                      
SELECT '- - - - - - - - - LXX USAGE INFO  - - - - - - - - - - - - - -' 
                               AS " " FROM MUF_AREA_STATS              
                                      WHERE AREA_NAME = 'LXX'          
UNION                                                                  
SELECT '--------------------------------------------------------------'
                                      FROM MUF_AREA_STATS;             
*                                                                     
SELECT                                                                
   MFL.TOTAL_TRACKS                          AS  "LXX TRKS"           
  ,MFL.TOTAL_BLOCKS                          AS  "BLKS TOTAL"         
  ,MFL.IN_USE_BLOCKS                         AS  "BLKS IN USE"        
  ,CAST( 100 *                                                         
        (CAST(MFL.IN_USE_BLOCKS AS DECIMAL(11,2)) /                   
         CAST(MFL.TOTAL_BLOCKS  AS DECIMAL(11,2))                     
        ) AS NUMERIC(4,1))                   AS  "PCT FULL"           
  ,MFL.BLOCKS_SPILLABLE                      AS  "BLKS SPILLABLE"     
  FROM MUF_LOGGING MFL;                                               
*                                                                     
SELECT '- - - - - - - - - LXX OPERATIONAL CHARACTERISTICS - - - - - -'
                               AS " " FROM MUF_AREA_STATS             
                                      WHERE AREA_NAME = 'LXX'         
UNION                                                                 
SELECT '--------------------------------------------------------------'
                                      FROM MUF_AREA_STATS;            
*                                                                     
SELECT                                                                 
   MFL.LOG_RECORD_SEQ                        AS  "HIGHEST SEQ"        
  ,CASE WHEN SUBSTR(CHAR(MFL.LAST_SPILLED_TIME),1,1) >= '0'           
       THEN CHAR(DATE(MFL.LAST_SPILLED_TIME)) ||' '||                 
            CHAR(TIME(MFL.LAST_SPILLED_TIME))                         
       ELSE 'N/A'                                                     
   END                                       AS  "     LAST SPILL"    
  ,MFL.CYCLE_NUMBER                          AS  "SPILL CYCLE#"       
  ,MFL.RXX_NUMBER                            AS  "NEXT RXX #"         
  ,CASE WHEN MFL.SPILLING = 'R'  THEN 'PREPARED TO SPILL'             
        WHEN MFL.SPILLING = 'Y'  THEN 'SPILL IN PROCESS '             
        ELSE                          'NO SPILL ACTIVITY'             
   END                                       AS  "   SPILL STATUS"    
  FROM MUF_LOGGING MFL;                                               
*                                                                      
SELECT '- - - - - - - - - LXX I/O STATISTICS  - - - - - - - - - - - -'
                               AS " " FROM MUF_AREA_STATS             
                                      WHERE AREA_NAME = 'LXX'         
UNION                                                                  
SELECT '--------------------------------------------------------------'
                                      FROM MUF_AREA_STATS;            
*                                                                      
SELECT                                                                
   MFL.BLOCK_LENGTH                          AS  "LXX BLKSIZE"        
  ,MFA.LOGICAL_READS                         AS  "LOGICAL READS"      
  ,MFA.PHYSICAL_READS                        AS  "PHYSICAL READS"     
  ,MFA.LOGICAL_WRITES                        AS  "LOGICAL WRITES"     
  ,MFA.PHYSICAL_WRITES                       AS  "PHYSICAL WRITES"    
  FROM MUF_LOGGING MFL, MUF_AREA_STATS MFA                
    WHERE MUF_AREA_STATS.AREA_NAME = MUF_LOGGING.AREA_NAME;
/*                                            

 

Additional Information

Additional Information:

For more information about the MUF_LOGGING and MUF_AREA_STATS tables, please refer to the System Tables Reference area of the manual.