DB2 Version 9.7 for Linux, UNIX, and Windows

CATALOG ODBC DATA SOURCE command

Catalogs a user or system ODBC data source.

A data source, in ODBC (Open Database Connectivity) terminology, is a user-defined name for a specific database or file system. That name is used to access the database or file system through ODBC APIs. Either user or system data sources can be cataloged. A user data source is only visible to the user who cataloged it, whereas a system data source is visible to and can be used by all other users.

This command is available on Windows platforms only.

Authorization

None

Required connection

None

Command syntax


            .-USER---.         
>>-CATALOG--+--------+--ODBC------------------------------------>
            '-SYSTEM-'         

>--+-DATA SOURCE--data-source-name-+---------------------------><
   '-ALL DATA SOURCES -------------'

Command parameters

USER: Catalog a user data source. This is the default if no keyword is specified.
SYSTEM: Catalog a system data source.
DATA SOURCE data-source-name: Specifies the name of the data source to be cataloged. The name of the data source and the name of the database must be the same. Therefore, the name of the data source is limited to the maximum length for a database name.
ALL DATA SOURCES: Specifies to catalog all local database aliases as ODBC data sources (DSNs).

Usage notes

On Microsoft Windows Vista or later versions, you must execute the CATALOG SYSTEM ODBC DATA SOURCE command from a DB2® command window running with full administrator privileges.

Specifying the ALL DATA SOURCES parameter will not update an existing ODBC DSN that has set its dbalias parameter to a value that matches the alias of a database in the local database directory

In Windows environments, the ODBC DSN must be restored after upgrading DB2. To import and export the ODBC DSN settings, use the db2cfimp command or the db2cfexp command, or add DSNs manually.

To CATALOG ODBC DATA SOURCE for 32bit CLI/ODBC Driver of 64bit DB2 installation on 64bit Windows environments, use db2cli32.exe executable with registerdsn option, instead of DB2 Command Line Tool.

To catalog a system data source, use the command:

db2cli32 registerdsn -add -dsn mydsn1 -system

To catalog user data source, use the command:

db2cli32 registerdsn -add -dsn mydsn1 -user

Example 1

Assume there is an existing ODBC DSN named "MyProdDatabase". The dbalias parameter is set to "PRODDB". Assume there is also a database in the local directory with the alias "PRODDB". Executing the CATALOG ODBC DATA SOURCE myproddatabase command or the CATALOG ODBC ALL DATA SOURCES command will not alter the "MyProdDatabase" DSN because the DSN does not match the database alias. Instead, an ODBC DSN entry is created for "PRODDB" with the dbalias set to "PRODDB". If there is an existing ODBC DSN with the same name as the database alias, the existing ODBC DSN's dbalias parameter will be updated with the database alias. All associated CLI parameters and values will remain unchanged.

Example 2

Assume there is an existing DSN called "MYDB" that has the dbalias parameter set to "salesdb". If there is a database in the local directory named "MYDB" then executing the CATALOG ODBC DATA SOURCE mydb command or the CATALOG ODBC ALL DATA SOURCES command will change the DSN's dbalias parameter to "MYDB".