Advanced Settings for a Relational Data Source

The collection of fields on the Advanced tab differs by type of data source. The following table describes the properties for common data sources. For descriptions of properties for advanced data sources, go to Help > Adapter Help from the Studio main menu.

Advanced Property

Data Source

Description

Collation Sensitive

All

TDV does not use the SORT MERGE join algorithm if any data source involved in the join is marked Collation Sensitive.

Concurrent Request Limit

All

Works with the Massively Parallel Processing engine configuration parameters to control the amount of parallelization for the queries for a particular data source.

Connection Check-out Procedure

 

All

A procedure that returns a valid SQL statement that can be used to initialize the connection. The signature of the initialization procedure should be:

(IN ds_name VARCHAR, OUT sqlText VARCHAR)

Give the full path to the procedure in the Connection Check-out Procedure box. For example, this is a procedure that sets session options on an Oracle database:

PROCEDURE Connection_Checkout_Procedure_Oracle(IN ds_path /lib/resource/ResourceDefs.ResourcePath, OUT sqlText VARCHAR(32768))
	BEGIN
    SET sqlText =  
      'BEGIN 
         EXECUTE IMMEDIATE ''alter session set optimizer_index_cost_adj=10'';
         EXECUTE IMMEDIATE ''alter session set optimizer_index_caching=90'';
         EXECUTE IMMEDIATE ''alter session set "_complex_view_merging"=true''; 
       END;';
  END

Connection Check-out Procedure (Cont’d)

 

Another example is for an Oracle Virtual Private Database (VPD) system. VPD is a method of doing row-level security. After the connection is made, often with a generic account, the client enables certain sets of access rights by setting a security context. Write the code in such a way that the initialization procedure revokes rights if it is not called within the appropriate context. This is executed on the connection, changing connection privileges from the default to those associated with the user.

PROCEDURE Connection_Checkout_Procedure_Oracle(IN ds_path /lib/resource/ResourceDefs.ResourcePath, OUT sqlText VARCHAR(32768))
	BEGIN
    SET sqlText =  
      'BEGIN 
        dbms_session.set_identifier(''username'');
       END;';
  END

Connection checkout timeout

Apache Drill, TIBCO ComputeDB

Time that a connection doing a checkout can remain idle without being dropped.

Connection Pool Idle Timeout

All

Number of seconds (default 30) that a connection can remain idle without being dropped from the pool when there are more than the minimum number of connections.

Connection Pool Maximum Size

All

Maximum number of connections (both active and idle) allowed for the data source. When the maximum is reached, new requests must wait until a connection is available.

If the maximum number of connections is in use when a request comes in (even with pass-through authentication), the new request is blocked and queued until a connection is available or the Connection Pool Idle Timeout is reached.

If no connection was made available within the specified timeout, a check is made for an available connection by the same user. If none is available, the least recently used connection for another user is dropped and a new connection is opened.

Studio reuses pooled connections if they continue to be valid after changes (such as connection name), but JDBC requests are forced to use new connections if any part of the data source connection configuration has changed.

Connection Pool Minimum Size

All

Minimum number of connections in the pool even when the pool is inactive.

When a connection has been idle, a validation query is used to verify whether an open connection is still valid just prior to submission of a request. If the connection is invalid, the connection is discarded and another is used.

Connection Properties—JDBC Connection Properties
(button)

All

Lets you specify property-value pairs to pass to the JDBC data source.

Click to add custom connection properties for any JDBC data source. Commonly used properties are populated with default values. Use the Add Argument button to specify other properties and values.

TDV does not validate property names. Some data source adapters ignore invalid property names or values; others return an error.

The driver properties specify connection timeout settings required by specific drivers. To avoid leaving connections open indefinitely, specify properties explicitly for your data source.

Connection Attributes

Apache Drill, TIBCO ComputeDB

Lets you specify property-value pairs to pass to the data source. For example:

bootPassword=key attribute
collation=collation attribute
dataEncryption=true attribute
drop=true attribute
encryptionKey=key attribute
encryptionProvider=providerName attribute
encryptionAlgorithm=algorithm attribute
failover=true attribute

Connection URL Pattern

All

A template for generating a URL to connect to the physical data source. TDV does not validate modifications. The data source adapter might not validate changes.

DataDirect

Keywords are:

APNA—Application name for SQL grouping.
CPFX—Catalog prefix for DB2 or non-DB2 resources.
DBTY—DBMS type.
HOST and PORT—Network name or IP address, and port number.
SUBSYS—DB2 database names.
TRLT—Turns off truncation for strings greater than 20 characters.
DTFM—Date format to standard ODBC/ISO format: yyyy-mm-dd

Teradata

Sends debug log messages to system.out rather than to a file. The pattern is jdbc:teradata://<HOST>/DBS_PORT=<PORT>/DATABASE=<DATABASE_NAME>/CHARSET=UTF8,COMPAT_DBS=true. To view debug messages, append ,LOG=DEBUG (including the initial comma) to the pattern.

Connection URL String

All

The URL string generated from the connection URL pattern with the connection information you provide. This string is used by the JDBC adapter to connect to the physical data source. This field cannot be edited. For details, see the section “Connecting through JDBC Adapters” in the TDV Administration Guide.

Connection Validation Query

All

A data-source-specific query that the TDV query engine sends to see if the data source connection is valid. This query is executed every time a connection is checked out from the pool. Enter a query that returns quickly.

If this query returns a non-error result, the data source connection is considered valid. If this query fails, the connection is discarded and a new connection is checked out from the available pool.

No one SELECT statement works with all data sources. To verify that TDV is running and that it can connect to the data source, devise a query against a published table from that data source.

Data source driver doesn’t support query timeout

Apache Drill, TIBCO ComputeDB

Select or clear the check box. If cleared, specify an Execution timeout value in seconds

Data source is enabled

Apache Drill, TIBCO ComputeDB

 

Enable Bulk Load

Various

Related to the data ship feature capability.

Several fields are available only if others are checked. For details, see Data Ship Performance Optimization.

Enable Bulk Import/Export

SQL Server

Related to the data ship feature capability.

Several fields are available only if others are checked. For details, see Data Ship Performance Optimization.

Enable Export To Another Vertica Database

Vertica

Several of these fields are available only if others are checked. For details, see Data Ship Performance Optimization.

For setting up caching using bulk load features, see TDV Caching.

Note: All Netezza data sources should be configured to act as data ship targets.

Data Ship

Enable Sybase IQ SQL Location
Sql Anywhere Data Source
SQL Location List
Sybase iAnywhere JDBC Driver

Sybase IQ only

Locations to improve performance if you plan to use this data source for data ship optimization.

You can add one or more Sybase locations by specifying the location name and path of the data source for each link.

For further information, refer to Configuring Data Ship for Sybase IQ Targets with Location.

Select mode

Microsoft SQL Server

Direct or Cursor

Direct—Sends all results to the adapter in one request. Each statement establishes its own connection to the database using the same connection properties as the original connection, with auto-commit enabled. Java Transaction API (JTA) is not supported. Direct mode does not support operations where the adapter creates a second statement internally. A typical exception message is “Cannot start a cloned connection while in manual transaction mode.”

Cursor—Allows you to work with a smaller set of rows that are returned by the SQL statements.

Enable FastLoad/FastExport for large tables

Teradata

Use the FastLoad or FastExport utility to speed up queries. Cardinality information determines whether to use Fastpath or JDBC default loading for a given query.

Enable Native Data Loading

All

Let the data source use its proprietary functionality to optimize performance. See About Data Source Native Load Performance Options.

Enable Oracle Database Link

Oracle

Check to improve performance if you plan to use this data source for data caching or data ship optimization. Also add one or more Oracle database links. See Configuring Native Caching for Oracle, and Data Ship Performance Optimization.

Enable Pass-Through Prepared Statements

Oracle

For pass-through to work, the prepared statement must call data from only one Oracle database instance. Prepared statements can use data from multiple tables within a single Oracle database instance.

Enable PostgreSql dblink

PostegreSQL

Check if you plan to use this data source for data caching or data ship optimization. Also add one or more PostegreSQL database links.

Execute SELECTs Independently

All

Lets a SELECT statement be executed using a new connection from the connection pool, and committed immediately after completion. INSERT, UPDATE, and DELETE statements are executed using the same connection as part of the transaction.

Execute SELECTs in separate transactions from INSERTs and UPDATEs

Apache Drill, TIBCO ComputeDB

Lets a SELECT statement be executed using a new connection from the connection pool, and committed immediately after completion. INSERT and UPDATE, statements are executed using the same connection as part of the transaction.

Execution Timeout

All

The number of seconds an execution query on the data source can run before being canceled. Zero seconds (the default value) disables execution timeout, allowing processes to run to completion—for example, resource-intensive cache updates scheduled for non-peak processing hours.

FastExport Session Count

Teradata

The number of FastExport sessions to use.

FastLoad Session Count

Teradata

The number of FastLoad sessions to use.

Ignore Procedure Return Parameter

Sybase

Check to suppress the return parameter for stored procedures. By default (unchecked), return parameters are inserted into procedure definitions by the JDBC adapter.

Case sensitivity mismatch between TDV and data source can be ignored

Apache Drill, TIBCO ComputeDB

Check to ignore case mismatches.

Ignore trailing space mismatches between TDV and the data source

Apache Drill, TIBCO ComputeDB

Check to ignore trailing spaces.

Honor trailing spaces for string comparison

Apache Drill, TIBCO ComputeDB

Check to honor trailing spaces.

Case insensitive string comparison

Apache Drill, TIBCO ComputeDB

Ignore case.

Include Invalid Introspection Objects

Oracle

Check to return all objects during introspection, including invalid objects.

Introspect Procedures

Oracle

Checked by default. Ignoring procedures speeds up the initial introspection when only tables are wanted.

Introspect comments

Oracle

During the introspection process, TDV can retrieve table and column level comments and add them to the annotations field for each resource. Introspecting Data Source Table and Column Comment Metadata.

Introspect Should Use Column Alias

Sybase

Check to return column aliases when introspecting data sources. By default (unchecked), introspection returns column names.

Introspect Using DBA_* Views

Oracle

Oracle maintains multiple metadata views. By default, TDV introspection uses ALL_* views, which list resources for which the user has access to both data and metadata. DBA_* views show all resources in the database regardless of data access permissions. Refer to Oracle documentation for differences and privileges.

Max Source Side Cardinality for Semi Join

All

See the documentation for semijoins and the TDV Administration Guide for more information.

Max Source Side of Semi Join to Use OR Syntax

All

See the documentation for semijoins and the TDV Administration Guide for more information.

Maximum Connection Lifetime

All

The number of minutes that a connection that was returned to the pool persists if there are more open connections than the minimum pool size.

The duration is calculated from connection creation. Default value is 60 minutes. Set a smaller value if the pool is likely to run out of connections. Be sure to add a validation query. Set a larger value if you want the connections to be held for a longer period. Set a value of 0 to keep connections alive indefinitely.

Min Target to Source Ratio for Semi Join

All

Sets the minimum target-to-source ratio of cardinality for semijoins. Refer to the TDV Administration Guide for more information.

Query Banding

Teradata

Turns the query banding feature on (At Session Level) or off (Off). Stores query context information in the Teradata session table so it can be recovered after a system reset. Query banding takes effect when the data source is next used.

Connection pooling has no effect on query band data.

QueryBand Properties

Teradata

Click QueryBand Properties on the right to open a dialog box in which to specify property-value pairs to store in the session table.

Four properties are available by default. For the first three, if the default value (including brackets) appears alone in the value field, it is replaced with the actual value at run time.

TDV_USER. ID of the TDV user, application or report that originated the request from a middle-tiered application. The default value is <TDV_USER>.
DOMAIN. The default value is <DOMAIN>.
SESSION_NAME. The default value is <SESSION_NAME>.
SYS. The default value is TDV.

TDV administrator can click Add property to add custom name-value pairs.

Select Mode

Microsoft SQL Server

Choose one of two values: Cursor or Direct. These two values affect how result sets are created and retrieved when a query is executed against the data source.

Cursor—Generates a server-side cursor. Rows are fetched from the server in blocks. Use JDBC statement method setFetchSize to control the number of rows fetched per request. Useful for queries that return more data than what can be cached on the client.

Show All Databases

Sybase

Microsoft SQL Server

Check to list all databases accessible using these credentials during introspection. Inaccessible databases will be skipped.

If a SQL Server database is off-line for the instance you are attempting to introspect, you might see NPE exceptions in the log file.

Streaming Results Mode

MySQL

If selected (default), streams the result set row by row from MySQL to TDV. If not selected, MySQL does not send results to TDV until all results are gathered. For details, see the README.txt file in MySQL JDBC adapter’s docs directory.

Supports Star Schema

All

Check only if this data source supports very large predicates and very large cardinalities for star schema semijoins. Refer to the section Star Schema Semijoin, for more information.

Transaction Lock Wait Timeout

SAP HANA

The length of time a transaction is to wait on the lock before quitting.

Transaction isolation

Apache Drill, TIBCO ComputeDB

Valid values: none, Read committed, Read uncommitted, Repeatable read, Serializable.

Use global temp space for temp tables

DB2

Option that can be used to improve performance when using this data source with the TDV data ship feature. This option would allow you to manage the temp tables created for the data source like any other temp table that you have defined in your source database.

Use pass-through user’s certificate for encryption

Oracle

When pass-through login is configured for use with an Oracle data source, check this box to include the user’s certificate in the SSL negotiation (handshake).

Use X Views

Teradata

Returns data only for rows containing information on objects that the requesting user owns, created, has privileges on, or has been granted access through a current or nested role.