Edit Catalog Options Dialog

Use the Edit Catalog Options dialog to define how namespaces, tables, and columns are retrieved and cached for connections to your database data sources. 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.

Access

To access the Edit Catalog Options dialog:

1. Choose Add Data > New Data.
2. Select the database to use to connect.
3. Select an option and click Next.
4. In the Data Sources/Projects pane, select a data source/project.
5. In the Available Tables/Datasets pane, select Edit Catalog Options .

Fields

Retrieval mode: 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 appears. You can clear the Use default value checkboxes 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 checkboxes or make any changes to the namespaces or caching options, any customizations made to the default SQL are overwritten. Ensure that you copy any customizations to a separate backup file before selecting this option or changing the namespace or caching options.
• 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: Select which namespaces to retrieve data from for your data source.

• 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.
• When this option is selected, namespaces are not loaded; tables load directly into the GUI. If your data source does not support namespaces, All is selected by default and cannot be changed. All is also useful when an empty namespace exists in the data source. Since an empty namespace can't be shown from the GUI, this option allows you to skip the step of choosing a namespace and loading tables in the empty namespace directly.
• Current only: Data is retrieved only for the selected namespace. If you use SQL to retrieve data, the SQL statements automatically update to add filtering based on the current namespace. You can select the namespace to use from the drop-down 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. If you are using large databases, these caches may contain a considerable amount of data. This must factor into what level of caching you select. Additionally, if you select some level of caching and the names of columns or tables change, which is typically a rare occurrence, the cache does not automatically update 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 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 a 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.

Retrieval and Caching of Namespaces

Retrieving or caching namespaces is not affected by Retrieval Mode or Caching options. These settings only apply only to tables and columns.

Retrieve namespaces available in the data source: This allows you to customize the SQL to retrieve namespaces if they exist. This option is only available when you select Current only for Namespaces. The default SQL (which can be empty) for retrieving namespaces appears. You can clear the Use default value checkbox to make manual modifications the this default SQL. The retrieved namespaces are always cached.

How to retrieve namespaces is defined by the SQL statement:

• Empty: An empty SQL statement uses ODBC calls to retrieve namespaces. You must ensure ODBC call is supported for the data source.
• Non-Empty: A SQL statement retrieves namespaces. You must ensure that the syntax used is valid for your data source.

Note: If you select the Use default value checkbox, any customizations made to the default SQL are overwritten. Before selecting this checkbox or changing the namespace or caching options, copy any customizations to a separate back up file.

Preview Available Tables

Once you have defined how to retrieve namespaces, tables, and columns from your data source, you can view the available tables. Select a namespace in the Preview Available Tables pane. You can then view the available tables . Expand the tables to view the available columns. Columns appear with the column name and data type.