Using the Interval Monitor › SQL Information

SQL Information

 PM-Rnn.n SYSTEM71         CA, Inc.                  V71      08.348 12:05:56.67
 CMD-->                                                             Window : 02

   02 08:25 SQL  SQL Information                                              >
      Start    Rows     Rows    Rows    Rows   Total  Hi-Row  Lo-Row    Rows
       Time Fetched Inserted Updated Deleted   Sorts   Sorts   Sorts  Sorted
    _ 08:25      75       30      11       6       1      26      26      26
    _ 08:30     204       56      15      10       2      50      13      63

 PM-Rnn.n SYSTEM71         CA, Inc.                  V71      08.348 12:05:56.67
 CMD-->                                                             Window : 02

   02 08:25 SQL  SQL Information                                           < >
   Start    Rows       ACM        SQL  Select  Update   Pages   Pages     Pages
    Time  Sorted Recompile Statements   Locks   Locks    Read Written Requested
 _ 08:25      26        2          3        0       11     48      46        52
 _ 08:30      63        2          4       54       15    154      32       167

 PM-Rnn.n SYSTEM71         CA, Inc.                  V71      08.348 12:05:56.67
 CMD-->                                                             Window : 02

   02 08:25 SQL  SQL Information                                           < >
      Start     Pages      CALC    CALC       VIA     VIA       Rows       Rows
       Time Requested With OVFL No OVFL With OVFL No OVFL  Requested  Curr_Tran
    _ 08:25       46         5       42        3        2         52          3
    _ 08:30       32        20      100       17       63        200         27

 PM-Rnn.n SYSTEM71         CA, Inc.                  V71      08.348 12:05:56.67
 CMD-->                                                             Window : 02

   02 08:25 SQL  SQL Information                                           <
       Start    CALC       VIA     VIA      Rows       Rows   DBMS  Fragments
        Time No OVFL With OVFL No OVFL Requested  Curr_Tran  Calls     Stored
     _ 08:25      42        3        2        52          3    210         10
     _ 08:30     100       17       63       200         27    382         31

Screen Description

The SQL Information screen displays an overview of SQL activity by interval. Use this screen to quickly compare activity across intervals.

Using this Screen

To access the Specific SQL Information screen for a particular interval, type any nonblank character to the left of the interval for which the detail is required and press [Enter].

What To Look For

• Look for a high number in any of the following fields:
  • CALC With OVFL
  • VIA With OVFL
  • Fragments Stored

  These fields indicate that target pages for rows stored by the task were full, forcing CA IDMS to store the rows on other pages. If necessary, use the IDMSDBAN utility to analyze space availability for database pages. If a large number of pages in the database are full, consider increasing the database page size or the number of pages in the database.

• Look for a high number in the Rows Requested field compared to the number in the Rows Current field. This ratio should be as close to 1:1 as possible.
• Look at the ratio of pages requested to pages read. The ratio, which indicates the effectiveness of the buffer size and database design, should be about two. Ratios less than two indicate that either the buffer is too small or the database should be tuned.
• Look for large values under Access Module Recompiles. Three reasons for recompiles:
  • Changes in the physical database definition

    Note: Use discretion in planning changes to components of the physical database definition.

  • Program recompiling; the recompile changes the date/time stamp, necessitating an AM recompile

    Note: Try to limit program compiles on a production system.

  • An SQL statement referencing a temporary table before the table is defined

    Note: Define temporary tables before referencing them.