Displays the contents of a collection commands list generated by PrepCollect or collections for a specified object across all commands lists.
Definition
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) . . . ;
Input Parameters
Parameter | Description |
---|---|
CmdListID | System assigned commands list ID from a prior call to PrepCollect. For more information about this input parameter, see “Usage Notes.” |
CmdListName | User assigned commands list name as specified during a prior call to PrepCollect. For more information about this input parameter, see “Usage Notes.” |
DatabaseName | Name of the database. DatabaseName limits the results to collections for the database you specify. |
TableName | Name of the table. TableName limits the results to collections on the table you specify. |
ObjectListName | Name of 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. |
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 Teradata Vantage™ - SQL External Routine Programming , B035-1147 .
Displaying the Collection Results
To display the collection results for a ... | You must specify a ... |
---|---|
particular commands list | non-value for CmdListID or CmdListName. You can specify both CmdListID and CmdListName as long as they identify the same command list. If CmdListID and CmdListName are both NULL, the report will consider collections from all command lists in the TDSTATS database.
|
particular object | value for both DatabaseName or TableName. |
particular named object list that cannot be specified by DatabaseName or TableName | value for ObjectListName that was previously created by the CreateObjectList and AddObjectListEntry external stored procedures. 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. |
Output Parameters
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 SQL COLLECT STATISTICS statement within the commands list. |
CollectText | SQL text of the COLLECT STATISTICS statement. |
Example: Using SelectPreparedCollects
The following example shows how to display the collection results for the commands 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..
Related Topics
For more information on ... | See ... |
---|---|
the PrepCollect external stored procedure | PrepCollect. |
SQL COLLECT STATISTICS statement | Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. |