About ODBC Registry

Tables stored in the ODBC registry are connected to a catalog and schema, which are ways to organize and categorize tables in a database. The default catalog is master. Queries to tables that are registered in the master catalog do not need to specify the catalog. There are three configurations to a query that use the catalog, schema, and table format:

FROM <catalog>.<schema>.<table>—Returns the <table> contained in the <schema> contained in the <catalog>.

FROM <schema>.<table>—Returns the <table> contained in the <schema> contained in the default catalog.

FROM <table>— Returns the first table in the registry that is contained in the default catalog and that matches <table>. This ignores the schema value.

For example, If the tables with the name mass_sales and vermont_sales are contained within a catalog named state_sales and a schema named northeast_sales, then you can access them with statements such as:

SELECT ... FROM state_sales.northeast_sales.mass_sales;

SELECT ... FROM state_sales.northeast_sales.vermont_sales;

When the data source is a cPlan, you can also access individual named inputs in the cPlan by adding @ followed by the input name.

For example:

FROM <catalog>.<schema>.<cplan_table>@<input_name>

When the data source is a measure factory, you can access individual scopes by adding @ followed by the scope name.

For example:

FROM <catalog>.<schema>.<factory_table>@<scope_name>

Workbench features an ODBC GUI. To access the GUI:

1. Click Tools > Server Settings.

   The Server Settings tab opens.

2. Click ODBC Registry.

Each row in the ODBC registry table corresponds to a row in the odbc-registry.txt file. Adding a row in the ODBC registry table also adds a row in the text file.

NOTE: If the odbc-registry.txt file does not exist, adding a row in the ODBC registry GUI creates a copy of the odbc-registry.txt file in the DiveLine dataroot\config folder.

All table, catalog, and schema information for the ODBC registry is stored in the odbc-registry.txt file in the DiveLine dataroot\config folder. This file is automatically created when using dicfg commands to add a table. The file can also be created manually and saved in the dataroot\config folder.

The odbc-registry.txt file is a tab-delimited text file. It has five column headers (case insensitive): Catalog, Schema, Table, Project, and Path. Additional columns are preserved but ignored by DiveLine. For example:

Catalog <tab> Schema <tab> Table <tab> Project <tab> Path

master <tab> public <tab> my_sales <tab> MyData <tab> /cbases/sales.cbase

master <tab> public <tab> factory <tab> FactoryProject <tab> /factory-output

You can download the following formatted sample registry file:

Sample ODBC Registry File