Managing data source connections » Defining how tables and columns are retrieved and cached for a data source

Defining how tables and columns are retrieved and cached for a data source

For connections to your database data sources, you can define how tables and columns are retrieved and cached. This includes using either SQL or ODBC calls, customizing the SQL, selecting the applicable namespaces, and determining how table and column names for your database are cached.

Catalog mode: You can select how to retrieve data for data sources that support both SQL and ODBC calls to retrieve data. In most cases, using data source SQL is recommended as compared to using ODBC calls:
Data source SQL: This option allows you to customize the SQL to retrieve data for every platform (except Microsoft Access). If you select this option, the default SQL used to retrieve data is displayed. You can clear the Use default value check boxes to make manual modifications to this default SQL. You must ensure that the syntax you use is valid for your data source.

Be aware that if you select the Use default value check boxes or make any changes to the namespaces or caching options, any customizations made to the default SQL are overwritten. Prior to selecting this check box or changing the namespace or caching options, ensure that you copy any customizations to a separate backup file.

ODBC Calls: This option is available if you connect to a database type that supports ODBC calls to retrieve the database catalog tables. If you use a Microsoft Access database, ODBC calls are automatically used.

If you select this option, standard ODBC calls are used to retrieve the database catalog tables and no further customizations can be made. If you select this option and the results are not retrieving the database catalog tables correctly, you can select the Data source SQL option to customize how the database catalog tables are retrieved.

Namespaces: You can select which namespaces to retrieve data from for your data source. If your data source does not support namespaces, these options are not available for modification:
All: Data is retrieved for all namespaces. If you use SQL to retrieve data, the SQL statements are automatically updated to remove filtering based on namespaces.
Current only: Data is retrieve only for the selected namespace. If you use SQL to retrieve data, the SQL statements are automatically updated to add  filtering based on the current namespace. You can select the namespace to use from the drop-down list near the Preview area.
Caching: You can cache table and column names from your database to improve the performance of accessing that data.

These caches must be created and stored in memory. For large databases, these caches can be a considerable amount of data and this must factor in to what level of caching you select. Additionally, if you select some level of caching and the names of columns or tables are changed, which is typically a rare occurrence, the cache is not automatically updated with the new name. An update of the database or specific database table must be done to update the cache.

You can select from the following levels of caching:

Table and column names: The table names and column names are both included in the cache. This can improve performance when retrieving data from a database by retrieving the table and column names from a cache rather than from the database. This also has the largest memory requirements in terms of storing the table and column cache information.

If you select this option and you use SQL to retrieve data, a single SQL statement is used to retrieve tables and columns.

Table names only: The table names are included in the cache. This can improve performance when retrieving data from a database by retrieving the table names from a cache rather than from the database. Column names are not included in the cache and must always be retrieved from the database. This can be a reasonable alternative to the Table and column names option if the cache size is too large.

If you select this option and you use SQL to retrieve data, separate SQL statements are used to retrieve tables and columns respectively.

None: No data is cached for your database. This removes any memory requirements for creating or storing table or column cache information, but can affect performance as table and column names must always be retrieved from the database.

If you select this option and you use SQL to retrieve data, separate SQL statements are used to retrieve tables and columns respectively.

Previewing the data

Once you have defined how to retrieve tables and columns from your data source, you can click Apply to see a preview of what data is returned. You can then review the tables returned, and expand the tables  to view information on the columns retrieved, including the column name and data type.