Oct 29

Tivoli Storage Manager DB2 ODBC user ID configuration

DB2, SQL, TSM Server 6 Comments Off on Tivoli Storage Manager DB2 ODBC user ID configuration

Problem(Abstract)

To access the Tivoli Storage Manager DB2 instance via an ODBC connection, a user ID must have the necessary permissions.

Resolving the problem

The user ID and password that is configured for ODBC access must be a valid UNIX user that has authority to the Tivoli Storage Manager database. For example, you can use the Tivoli Storage Manager server instance user ID and password. The user ID does not need to be part of a special UNIX group but it must have authority to the DB2 database. The user must also be allowed to log on.

You can grant authority to a separate user ID, other than the Tivoli Storage Manager server instance user ID, to access the database for ODBC query. This document provides two examples to grant authority to the user ID named db2odbc.
Example 1 :
To give the db2odbc user ID access to all tables for the Tivoli Storage Manager database, log on with the Tivoli Storage Manager server instance user ID and run the following DB2 commands:

  1. db2 connect to TSMDB1
  2. db2 grant DATAACCESS, CONNECT on database to db2odbc
  3. db2 grant usage on workload SYSDEFAULTUSERWORKLOAD to user db2odbc
  4. db2 grant execute on package nullid.syssh200 to public

This series of commands grants less authority to the db2odbc user ID than what the Tivoli Storage Manager server instance user ID has. To grant even less authority, use example 2, which gives the user ID only select authority.
Example 2 :
To give the db2odbc user ID select authority only to a specific table, log on with the Tivoli Storage Manager server instance user ID and run the following DB2 commands:

  1. db2 connect to TSMDB1
  2. db2 grant CONNECT on database to db2odbc
  3. db2 grant usage on workload SYSDEFAULTUSERWORKLOAD to user db2odbc
  4. db2 grant execute on package nullid.syssh200 to public
  5. db2 grant select on syscat.tables to user db2odbc
  6. db2 grant select on TSMDB1.BACKUP_OBJECTS to user db2odbc

In this example, the db2odbc user ID is granted authority to run select statements only against the BACKUP_OBJECTS table. For each table for which you want the db2odbc user ID to have select authority, repeat the grant select command with that table name.
To view the available Tivoli Storage Manager tables, run the following command: db2 connect to TSMDB1 db2 "select tabname from syscat.tables where tabschema='TSMDB1' and tabname not like '%(TEMP)'"
Example 3 :
To grant the db2odbc user ID select authority to all tables of a Tivoli Storage Manager running on AIX, log on with the Tivoli Storage Manager server instance user ID and run the following DB2 commands:

  1. db2 connect to TSMDB1
  2. db2 grant CONNECT on database to db2odbc
  3. db2 grant usage on workload SYSDEFAULTUSERWORKLOAD to user db2odbc
  4. db2 grant execute on package nullid.syssh200 to public
  5. db2 grant select on syscat.tables to user db2odbc
  6. for tablename in $(db2 -x "select tabname from syscat.tables where tabschema='TSMDB1' and tabname not like '%(TEMP)'") ; do db2 grant select on TSMDB1.${tablename} to user db2odbc ; done

Example 4 :
To grant the db2odbc user ID select authority to all tables of a Tivoli Storage Manager running on Windows, logon with the Windows account that runs the Tivoli Storage Manager server instance. Open a windows command prompt and enter “db2cmd”. This will open a new Windows command prompt. From the new command prompt, run the following db2 commands :

  1. db2 connect to TSMDB1
  2. db2 grant CONNECT on database to db2odbc
  3. db2 grant usage on workload SYSDEFAULTUSERWORKLOAD to user db2odbc
  4. db2 grant execute on package nullid.syssh200 to public
  5. db2 grant select on syscat.tables to user db2odbc
  6. db2 -x "select tabname from syscat.tables where tabschema='TSMDB1' and tabname not like '%(TEMP)'" > c:\temp\tsmtb.list
  7. for /F %N in (c:\temp\tsmtb.list) do db2 grant select on TSMDB1.%N to user db2odbc

In step 6, a file name, c:\temp\tsmtb.list, is used to write the output of the command. You can select any appropriate file to write this output into. This same file name must then be specified in step 7.

written by Bosse