Updating BioRS _ID_ column cardinality

To update BioRS column cardinality statistics for the column that maps to the BioRS _ID_ element, you must modify the SYSSTAT.COLUMNS catalog view.

Before you begin

You must determine the cardinality number of the BioRS databank that corresponds to the nickname in which the column is referenced. The cardinality number of the column that maps to the BioRS _ID_ element must match the cardinality of the nickname in which the column is referenced.

You should ensure that the cardinality statistics for the column that maps to the BioRS _ID_ element are current. The optimizer and the BioRS wrapper use these statistics to choose the best data access plan to process your queries.

About this task

To update the BioRS _ID_ column cardinality you must select entries in the SYSCAT.COLOPTIONS view that contain the ELEMENT_NAME option. This BioRS wrapper uses this option to map between nickname column names in the federated database and element names in the BioRS server.

Procedure

Issue the UPDATE statement to modify the catalog view.

For example:

UPDATE SYSSTAT.COLUMNS SET COLCARD=cardinality_number
    WHERE
    TABSCHEMA=nickname_schema
    AND TABNAME=nickname_name
    AND COLNAME=column_name 
        IN (SELECT column_name FROM SYSCAT.COLOPTIONS
        WHERE
        TABSCHEMA=nickname_schema
        AND TABNAME=nickname_name
        AND OPTION='ELEMENT_NAME'
        AND SETTING='_ID_')

SYSSTAT.COLUMNS: The system catalog view in the federated database where column statistics are stored.
SET COLCARD=cardinality_number: The BioRS databank cardinality number that corresponds to the nickname of the column that you are updating the statistics for.
TABSCHEMA= nickname_schema: The name of the schema for the nickname that you want to update.
TABNAME=nickname_name: The name of the nickname that you want to update.
COLNAME=column_name: The name of the column whose cardinality statistics you want to update.
IN (SELECT column_name FROM SYSCAT.COLOPTIONS: This SELECT statement determines the name of the column that maps to the BioRS _ID_ element. SYSCAT.COLOPTIONS is the system catalog view in the federated database where column options are stored.
OPTION='ELEMENT_NAME': The value in the rows in the SYSCAT.COLOPTIONS view which indicates that a nickname column name is mapped to BioRS element name.
SETTING='_ID_': Specifies that the column for the ELEMENT_NAME option is '_ID_'.