The Advanced SQL Engine contains system (Data Dictionary) views that show information about the different objects that have been created. Two versions of the system views are installed during the installation of the SQL Engine --an X and non-X version. These views are used by the provider when creating a schema collection.

For more information on the System Views refer to the Data Dictionary manual.

X Views

The X version of a system view limits the data returned to only those rows that contain information on objects that the requesting user

• owns
• created
• granted privilege on
• granted access through the current role
• granted access through nested roles

The X version of the system views reference tables and views that are used to determine ownership and privileges of objects assigned to the user. This data is then used to determine which rows will be returned.

Non-X Views

In contrast, the non-X version of the system views do not reference tables or views to determine ownership or type of privileges that have been granted to the user. These views will return all the data that satisfies the query regardless of ownership or privileges.

Connecting To Teradata

By default, the connection string attribute Use XViews is set to true in the TdConnection.ConnectionString. This enables the use of the X version of the system views by the provider.

Teradata Database 12.0

Beginning in Teradata Database release 12.0, a performance degradation may be experienced accessing the schema collections through the Data Provider, and the Data Provider's integration with Microsoft Visual Studio when the use of XViews has been enabled.

Recommendations

The following are recommendations when creating schema collections using the provider or through the integration with Visual Studio:

Optimum Performance

When creating schema collections, the provider is optimally retrieving schema information when Non-X versions of the system views are accessed and only information under the Default Database is retrieved. To set up the provider to optimally retrieve schema information, the connection string attributes are set to the following values:

• Use XViews = false

  Indicates that the Non-X version of the system views will be used by the provider. Refer to the section on X Views for more information.

• Restrict to Default Database = true

  Limits the schema information that will be returned to objects contained in the Default Database.

Other Performance Considerations

There will be situations where an application will need to access schema information from several different databases, or where X versions of the system views must be used.

In all situations it is recommended that the Teradata statistics be utilized on the SQL Engine. The collection of statistics enables the Teradata Optimizer to optimize table access and join plans. For more information on this topic, please see the SQL Engine documentation on COLLECT STATISTICS - SQL Reference: Data Definition Statements.

Following is an example of refreshing the statistics on several system tables:

    drop stats on dbc.tvm;
    drop stats on dbc.owners;
    drop stats on dbc.dbase;
    drop stats on dbc.accessrights;

    collect stats on dbc.tvm column ( tvmId );
    collect stats on dbc.tvm INDEX ( DatabaseId ,TVMNameI );
    collect stats on dbc.tvm column ( DatabaseId );
    collect stats on dbc.owners INDEX ( ownerId );
    collect stats on dbc.dbase INDEX ( DatabaseId );
    collect stats on dbc.dbase column( JournalId );
    collect stats on dbc.accessrights INDEX ( UserId ,DatabaseId );
    collect stats on dbc.accessrights INDEX ( TVMId );
    collect stats on dbc.accessrights column ( UserId ,TVMId );
    collect stats on dbc.accessrights column ( DatabaseId );

    help stats dbc.tvm;
    help stats dbc.owners;
    help stats dbc.dbase;
    help stats dbc.accessrights;

Note
These SQL commands may need to be performed by the System Administrator.