Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

Error in calculatio

Help
Peter Prib
2008-12-05
2013-04-05
  • Peter Prib
    Peter Prib
    2008-12-05

    You have made the assumption that the TOTAL_SYS_CPU_TIME_MS contains the total time.  Wrong.  In some IBM developer's infinite wisdom, rather than use a decimal with precision, they decided to have two separate columns for a time.  One for the number before the point and one for the number past the decimal point.  I complained about this back in version 7 (or was it 6) and raised an APAR.  I suspect it was lazy programming at the time.  There are several time measurements that have the same issue.

    To get the actual total time you need the following formula "TOTAL_SYS_CPU_TIME+TOTAL_SYS_CPU_TIME_MS/1000000" in seconds

    SELECT NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_MS, STMT_SORTS, SORTS_PER_EXECUTION, ROWS_READ, ROWS_WRITTEN, TOTAL_EXEC_TIME_MS, TOTAL_USR_CPU_TIME_MS, TOTAL_SYS_CPU_TIME_MS, DBPARTITIONNUM, STMT_TEXT, SNAPSHOT_TIMESTAMP FROM TABLE ( SELECT NUM_EXECUTIONS, (TOTAL_EXEC_TIME_MS / NUM_EXECUTIONS) AS AVERAGE_EXECUTION_TIME_MS, STMT_SORTS, (STMT_SORTS / NUM_EXECUTIONS) AS SORTS_PER_EXECUTION, ROWS_READ, ROWS_WRITTEN, TOTAL_EXEC_TIME_MS, TOTAL_USR_CPU_TIME_MS, TOTAL_SYS_CPU_TIME_MS, DBPARTITIONNUM, STMT_TEXT, SNAPSHOT_TIMESTAMP FROM SYSIBMADM.SNAPDYN_SQL ) AS SNAPDYNSQL  ORDER BY  NUM_EXECUTIONS DESC

     
    • Thank you,

      I have opened a ticket for this and will fix the queries for the next cycle (about 1 week).

      Regards,
      Matthew

       
    • Peter Prib
      Peter Prib
      2008-12-13

      Hi

      I have fixed this issue and another issue.  It is possible or the execution count to be zero and thus have a division error.  Which highlights another error.  If it fails you don't see an error message.

      Corrected SQL for "Dynamic"

      SELECT
              NUM_EXECUTIONS,
             ((decimal(TOTAL_EXEC_TIME*1000000,31,0) + TOTAL_EXEC_TIME_MS) / nullif(NUM_EXECUTIONS,0)) AS AVERAGE_EXECUTION_TIME_MS, 
              STMT_SORTS,
              (STMT_SORTS / nullif(NUM_EXECUTIONS,0)) AS SORTS_PER_EXECUTION,
          ROWS_READ,
          ROWS_WRITTEN,
          decimal(TOTAL_EXEC_TIME*1000000,31,0) + TOTAL_EXEC_TIME_MS as TOTAL_EXEC_TIME_MS,
          decimal(TOTAL_USR_CPU_TIME*1000000,31,0) + TOTAL_USR_CPU_TIME_MS as TOTAL_USR_CPU_TIME_MS,
          decimal(TOTAL_SYS_CPU_TIME*1000000,31,0) + TOTAL_SYS_CPU_TIME_MS as TOTAL_SYS_CPU_TIME_MS,
              DBPARTITIONNUM,
          STMT_TEXT,
              SNAPSHOT_TIMESTAMP
            FROM
              SYSIBMADM.SNAPDYN_SQL