15.10 - SelectPreparedCollects - Teradata Database

Teradata Database Application Programming Reference

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

Displays the contents of a collection commands list generated by PrepCollect or a specified object across all commands lists.

REPLACE PROCEDURE TDSTATS.SelectPreparedCollects
  (IN CmdListID      BIGINT,
   IN CmdListName    VARCHAR(128) CHARACTER SET UNICODE,
   IN DatabaseName   VARCHAR(128) CHARACTER SET UNICODE,
   IN TableName      VARCHAR(128) CHARACTER SET UNICODE,
   IN ObjectListName VARCHAR(128) CHARACTER SET UNICODE)
           .
           .
           .
;
 

Parameter

Description

CmdListID

System assigned commands list ID from a prior call to PrepCollect. For more information, see “PrepCollect” on page 546.

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

CmdListName

User assigned commands list name as specified during prior call to PrepCollect. For more information, see “PrepCollect” on page 546.

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

DatabaseName

Name of the database. DatabaseName limits the results to collections on the database you specify.

TableName

Name of the table. TableName limits the results to collections on the table you specify.

ObjectListName

Name of the object list. ObjectListName allows you to specify a previously defined list of objects (that is, one or more database names or fully qualified table names where each name entry may optionally contain wildcard characters) whose collections should be displayed.

For more information about this input parameter, see “Usage Notes” or “CreateObjectList” on page 603.

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.

Specifying Collection Statements to Display

 

To display ...

You must specify a value for ...

collections from a particular command list

either CmdListID or CmdListName.

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

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

If CmdListID and CmdListName are both NULL, the report will return collections from all command lists in the TDSTATS database.

collections on a particular object

both DatabaseName and TableName.

collections for a particular named object list

ObjectListName that was previously created by the CreateObjectList and AddObjectListEntry external stored procedures.

Note: ObjectListName usage is mutually exclusive with DatabaseName and TableName. When you specify a value other than NULL for ObjectListName, DatabaseName and TableName must be NULL.

 

Parameter

Description

CmdListID

Commands list ID.

CmdListName

Command list name, if assigned.

DataBaseName

Database in which the collection is defined.

TableName

Table on which the collection is defined.

PriorityRank

System assigned submission priority relative to other collections in the same commands list.

RequestID

System assigned ID for the COLLECT STATISTICS statement within the commands list.

CollectText

SQL text of the COLLECT STATISTICS statement.

The following example shows how to display the collection commands in the user assigned command list named MyCmdsList.

CALL TDSTATS.SelectPreparedCollects(NULL,'MyCmdslist',NULL,NULL,NULL);
 
 *** Query completed. 4 rows found. 7 columns returned. 
 *** Total elapsed time was 1 second.
 
CmdListID CmdListName DatbaseName TableName  Priority RequestID  CollectText
--------- ----------- ----------- ---------- -------- ---------  -----------
    5     MyCmdsList  Personnel   Employee       1    34560      COLLECT STATISTICS..
    5     MyCmdsList  Personnel   Department     2    34561      COLLECT STATISTICS..
    5     MyCmdsList  Personnel   Charges        3    34562      COLLECT STATISTICS..