Set up search_path for the ODBC Source

[JimKnicely]

You can add a database schema to your search_path variable so that when you log in you won't have to prefix table names with that schema.

For instance, if I log into Vertica as the user dbadmin and I want to query the employee_zip_dim table in the cognos schema, normally I would have to write the following query in the Toad SQL editor window:

Code: Select all

SELECT * FROM cognos.employee_zip_dim;

But if I add the cognos schema to my schema search path then I won't have to specify the schema in my query. To do that in Toad, I would edit the ODBC connection and add the following text to the "ConnectionString" field:

• ConnSettings=SET SEARCH_PATH TO "$user", cognos, public, v_catalog, v_monitor, v_internal

(See the attachment below for screen shot of the ODBC Connection Properties window)

If I reconnect I'll only have to type:

Code: Select all

SELECT * FROM employee_zip_dim;

[bkozorra]

What if you have 2 or more tables with the same name in different schemas?

[JimKnicely]

The Vertica search_path works just like the the PATH variable in Linux or DOS.

If a table with the same name exists in more than one schema, Vertica will simply use the first of those schemas listed in your search_path variable.

Example:

Code: Select all

dbadmin=> create schema test1;
CREATE SCHEMA

dbadmin=> create schema test2;
CREATE SCHEMA

dbadmin=> create table test1.weather_dim (key1 int);
CREATE TABLE

dbadmin=> create table test2.weather_dim (key1 int);
CREATE TABLE

dbadmin=> insert into test1.weather_dim values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test2.weather_dim values (0);
 OUTPUT
--------
      1
(1 row)

dbadmin=> show search_path;
    name     |                             setting
-------------+-----------------------------------------------------------------
 search_path | "$user", public, v_catalog, v_monitor, v_internal
(1 row)

dbadmin=> set search_path="$user", test2, test1, public, v_catalog, v_monitor, v_internal;
SET

dbadmin=> select * from weather_dim;
 key1
------
    0
(1 row)

dbadmin=> set search_path="$user", test1, test2, public, v_catalog, v_monitor, v_internal;
SET
dbadmin=> select * from weather_dim;
 key1
------
    1
(1 row)

The example was demonstrated using vsql, however the concept applies across any connection type.