15.10 - RunCollectReport - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

Displays all pending, in-progress, and completed collections for the most recent execution run operation of a given commands list or the results for a specified completed execution run operation as identified by its RunID output parameter.

REPLACE PROCEDURE TDSTATS.RunCollectReport
  (IN CmdListID    BIGINT,
   IN CmdListName  VARCHAR(128) CHARACTER SET UNICODE,
   IN RunID       BIGINT)
           .
           .
           .
;
 

Parameter

Description

CmdListID

System assigned commands list ID.

For more information about this input parameter, see “Usage Notes.”

CmdListName

User assigned commands list name.

For more information about this input parameter, see “Usage Notes.”

RunID

System assigned ID for the results from a given execution of RunCollect.

To show the results from a specific execution run, you must specify a value for RunID.

Note: If RunID is NULL, the results from all run operations in TDSTATS.CommandsHistoryTbl will be shown.

To display results from one or more completed run operations, you must call SelectStatsExecutionHistory. For more information, see “SelectStatsExecutionHistory” on page 562.

Returning Result Set

The output of this external stored procedure is in the form of a stored procedure dynamic result. That is, the external stored procedure can return result sets to the client application or to the caller of the external stored procedure (in addition to consuming the result sets itself) upon completion of the external stored procedure.

For more information about stored procedure dynamic result sets, see SQL External Routine Programming.

Displaying Execution Results

 

To display ...

You must specify ...

in-progress execution results or the most recent completed results for a specified commands list

a value other than NULL for either CmdListID or CmdListName.

To identify a list by name, you must have assigned it during a prior call to PrepCollect.

Note: You can specify CmdListID and CmdListName as long as they identify the same commands list.

a particular execution run operation that may not be the most recent

a value for RunID.

Note: When you specify a value other than NULL for RunID, the values for CmdListID and CmdListName are not relevant and can remain NULL.

 

Parameter

Description

Status

Possible values:

  • P (Pending)
  • I (In progress)
  • C (Completed successfully)
  • E (Error)
  • A (Aborted by the user)
  • PriorityRank

    System assigned submission priority relative to other collection commands in the same run operation.

    DatabaseName

    Database on which the collection is performed.

    TableName

    Table on which the collection is performed.

    StartTime

    Start time for an in-progress or completed collections.

    EndTime

    End time for completed collections.

    Skipped

    Possible values:

  • Y means the statistics are submitted but not collected according to the defined THRESHOLD setting.
  • N means the statistics were collected and updated in the dictionary.
  • RequestID

    System assigned ID for the COLLECT STATISTICS statement.

    CollectText

    SQL text of the COLLECT STATISTICS statement.

    ErrorText

    Error message text if the Status output parameter returns an E. For details, see Status.

    RunID

    System assigned ID of the RunCollect results reported.

    The following example shows how to display all pending statistics that are submitted but not collected according to the THRESHOLD setting.

    CALL TDSTATS.RunCollectReport(null,'mycmds',1);
     *** Procedure has been executed. 
     *** Warning: 3212 The stored procedure returned one or more result sets.
     *** Total elapsed time was 1 second.
     
     
     *** ResultSet# 1 : 6 rows returned by "TDSTATS.RUNCOLLECTREPORT".
     
    Status PriorityRank DataBaseName TableName StartTime                          EndTime                         Skip  Req. ID  Collect Text
    C   1               personnel    charges    2013-02-14 18:25:08.790000+00:00  2013-02-14 18:25:08.850000+00:00 N    00007    COLLECT STATISTICS INDEX("Proj_Id") ON "personnel"."charges";
    C   2               personnel    charges    2013-02-14 18:25:09.180000+00:00  2013-02-14 18:25:09.250000+00:00 N    100008   COLLECT STATISTICS INDEX("EmpNo","Proj_Id") ON "personnel"."charges";
    C   3               personnel    department 2013-02-14 18:25:09.600000+00:00  2013-02-14 18:25:09.660000+00:00 N    100009   COLLECT STATISTICS INDEX("DeptNo") ON "personnel"."department";
    C   4               personnel    employee   2013-02-14 18:25:09.910000+00:00  2013-02-14 18:25:09.970000+00:00 N    100010   COLLECT STATISTICS INDEX("Name") ON "personnel"."employee";
    C   5               personnel    employee   2013-02-14 18:25:10.310000+00:00  2013-02-14 18:25:10.380000+00:00 N    100011   COLLECT STATISTICS INDEX("EmpNo") ON "personnel"."employee";
    C   6               personnel    project    2013-02-14 18:25:10.720000+00:00  2013-02-14 18:25:10.810000+00:00 N    100012   COLLECT STATISTICS INDEX("Proj_Id") ON "personnel"."project";
     

    For more information on ...

    See ...

    displaying results from one or more past executions with the option of specifying a variety of criteria

    “SelectStatsExecutionHistory” on page 562.

    stored procedure dynamic result sets

    SQL External Routine Programming.