IBM InfoSphere Federation Server, Version 10.5

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.

Authorization

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.

Syntax

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
                    )

Parameters

Server

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.

Schema

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.

Nickname

The name of the nickname. If you specify a nickname, you must also specify a schema.

Colnames

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.

Indexnames

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.

Method

The method for collecting statistics information from the data source.

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.

1

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.

2

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 nonrelational nicknames.

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.

Log_File_Path

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.

Out_Trace

The trace.

Update_Method

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.
1: 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.

Example 1: In this example, the federated server retrieves the statistics for the nickname STAFF in the ADMIN schema. Statistics are gathered for columns 1, 3, 4, 6, 7, and 10 and indexes 1 through 3. The data-based method is used to collect statistics. The federated server writes the log to the /home/iiuser/reportlogs/log1.txt file. Statistics are updated by nickname.

CALL SYSPROC.NNSTAT( 
    NULL, 'ADMIN','STAFF','COL1, COL3, COL4, COL6, COL7, COL10',
    'IND1, IND2, IND3',2,'/home/iiuser/reportlogs/log1.txt',?,1)

Example 2: In this example, the federated server retrieves the statistics for all the nicknames on the DB2SERV server in the ADMIN schema. The federated server writes the log to the c:\\reports\\log1.txt file.

CALL SYSPROC.NNSTAT( 
    'DB2SERV','ADMIN',NULL,NULL,NULL,0,'c:\\reports\\log1.txt',?)

Example 3: In this example, the federated server retrieves the statistics for all the nicknames on the DB2SERV server and does not create a log.

CALL SYSPROC.NNSTAT(
    'DB2SERV',NULL,NULL,NULL,NULL,0,NULL,?)

Feedback