SYSPROC.NNSTAT stored procedure
Retrieve currently available statistics on one or more nicknames. The statistics are saved in the system catalog on the federated database.
SYSPROC.NNSTAT is a fenced procedure. The privileges for the fenced user ID of the federated instance must include the privilege to create the log file in the specified location.
CALL SYSPROC.NNSTAT( SERVER VARCHAR(128) SCHEMA VARCHAR(128) NICKNAME VARCHAR(128) COLNAMES CLOB(2M) INDEXNAMES CLOB(2M) METHOD SMALLINT LOG_FILE_PATH VARCHAR(1000) OUT_TRACE VARCHAR(2000) UPDATE_METHOD SMALLINT )
- The server on which the federated server gathers the nickname statistics. This server is what the user registers to define a data source in the federated database. If you specify one nickname, you can specify NULL for this parameter.
- If NULL is specified, the federated server retrieves all the nicknames under the given server. If the Server parameter is NULL, the federated server retrieves the statistics of the nickname under the given schema. If the Schema parameter and Nickname parameter are NULL and you specify a server, the federated server retrieves statistics on the given server.
- The name of the nickname. If you specify a nickname, you must also specify a schema.
- The names of the columns that are specified as column-name identifiers.
You can specify this parameter for a single nickname only. If you specify column names, you must also specify a schema and a nickname.
If NULL is specified, statistics are collected for all columns. NULL is the default.
Only the specified columns are processed. If an empty string (") is specified, columns are not processed.
- The names of the indexes that are specified as index-name identifiers.
You can specify this parameter for a single nickname only. If you specify index names, you must also specify a schema and a nickname. Only the specified indexes are processed.
If NULL is specified , statistics are collected for all indexes. NULL is the default.
If an empty string (") is specified, indexes are not processed.
- The method for collecting statistics information from the data
- 0 or NULL
- The catalog-based method is used first. If this method fails, then the data-based method is used. This is the default.
- Catalog-based statistics collection. The catalog-based method maps information from remote catalogs to local statistics for the nickname. This method is valid for relational nicknames only.
- Data-based statistics collection. The data-based method queries data from the remote table to
calculate the values for local statistics. This method is valid for both relational and
This method is the default for relational nicknames if the catalog-based method fails for a given nickname. Typically, the reason that statistics cannot be collected is because nicknames are defined for remote views. In this case, statistics are not available at the remote source.
Collection of nickname statistics is not supported against graphic columns in non-Unicode databases for columns statistics HIGH2KEY and LOW2KEY. Db2® does not support conversion from graphic to character data types in a non-Unicode database. Therefore, you cannot use method 2 to store graphic values into HIGH2KEY and LOW2KEY VARCHAR2 catalog columns.
- The path name and file name for the log file. The federated server creates the log file on the server. The directories that you list in the path must exist. On Windows, use two backslashes to specify the log path. For example: c:\\temp\\nnstat.log. If you specify NULL, the federated server does not create a log.
- The trace.
- The method for updating catalog statistics.
- 0 or NULL
- All statistics are updated together. This is the faster method. However, if the statistics for one nickname are invalid, all nickname statistics are affected and catalog statistics will not be updated successfully.
- Statistics are updated by nickname. If the statistics for one nickname are invalid, the statistics for that nickname remain in the catalog as default statistics. Statistics for all other nicknames are updated independently. This is the slower method.
CALL SYSPROC.NNSTAT( NULL, 'ADMIN','STAFF','COL1, COL3, COL4, COL6, COL7, COL10', 'IND1, IND2, IND3',2,'/home/iiuser/reportlogs/log1.txt',?,1)
CALL SYSPROC.NNSTAT( 'DB2SERV','ADMIN',NULL,NULL,NULL,0,'c:\\reports\\log1.txt',?)
CALL SYSPROC.NNSTAT( 'DB2SERV',NULL,NULL,NULL,NULL,0,NULL,?)