Required user IDs and permissions

Installing, configuring, starting, and using SQL Tuning Services involves several different user IDs. For some tasks, you can likely use an existing ID with some additional privileges. For other tasks, you will need to create new IDs.

The following table identifies all these user IDs. The placeholder names in the ID column are used throughout the installation and configuration documentation.

Note: The information in the following table is intended merely to help you familiarize yourself with these IDs before you start installing SQL Tuning Services. Instructions for creating or setting up all of these IDs are provided in the relevant installation or configuration tasks.
User ID Description Required privilege or permission
SQL Tuning Services repository database ID ( sqlts_repodb_id)

This ID is a functional Db2® for z/OS® ID that is used to connect to the Db2 for z/OS subsystem where the SQL Tuning Services repository database will be created and to create the repository database.

It is recommended that you create a new functional ID for this ID and assign it a password that does not expire, which eliminates the extra maintenance that is required to regularly reset the password. This ID must be able to use passwords or passphrases.

 The sqlts_repodb_id requires CREATEDBA and CREATEIN privileges on the Db2 for z/OS subsystem where the SQL Tuning Services repository database will be created. Example GRANT statements for assigning these privileges are provided in the DSN5RTRP sample JCL job.

Additionally, to use the extended tuning features, the sqlts_repodb_id requires EXECUTE privilege on all the packages that are listed in the DSN5RTRP sample job.

Note: If you use a user ID other than the sqlts_repodb_id to create the repository database, you must explicitly grant that ID privileges to access the repository database tables:
GRANT DBADM ON DATABASE IBMTMSDB TO user_id
SQL Tuning Services started task ID (SQLTSID) This ID is a functional RACF ID that's associated with the SQL Tuning Services started task. Sample RACF commands for creating this ID and associating it with the SQL Tuning Services started task are provided in Setting up the started task ID and creating a key ring for it.
sqlts_install_id

This ID is typically used by a system programmer to install, configure, and start SQL Tuning Services in UNIX System Services.
  • $JAVA_HOME/bin defined in the $PATH environment variable in the user's profile
  • The $_BPXK_AUTOCVT environment variable set to ON in the user's profile
  • Permission to read and execute to the install_dir_zos directory or a directory similar to $TMS_HOME that's used by the SMP/E installation process.
  • The $IBM_JAVA_OPTIONS environment variable set to the following value in the user's profile:
    -Dfile.encoding=UTF-8
sqlts_targetsys_id

This user ID is a Db2 for z/OS authorization ID that's used to connect to the tuning target Db2 for z/OS subsystem.

Within these instructions, there are two types of this ID:

  • An sqlts_targetsys_id that has been configured only with the privileges that are required to tune SQL statements is referred to as an sqlts_user_id.
  • An sqlts_targetsys_id that has been configured to tune SQL statements and to administer SQL Tuning Services is referred to as an sqlts_dba_id.

An sqlts_user_id requires the following privileges:

  • Permission to connect through JDBC to the Db2 for z/OS subsystem where the SQL Tuning Services repository database resides.
  • Permission to execute the IBMTMS.CANVIEW authentication UDF. Sample JCL for this GRANT statement is provided in the DSN5RUDF sample job.
  • Additional privileges to run the SQL Capture, Statistics Advisor, and Query Environment Collector APIs and to create EXPLAIN tables. Sample JCL for granting these privileges is provided in the DSN5RGRT and DSN5RTTG sample jobs.
    Note: The sample jobs that you run depend on the structure of your environment:
    • If the repository database is on the same Db2 for z/OS subsystem as the SQL that you intend to tune, use DSN5RGRT.
    • If the repository database and the SQL that you intend to tune reside in different Db2 for z/OS subsystems, use DSN5RTTG to grant privileges on the tuning target subsystem, and use DSN5RTRP to grant privileges on the repository database.
  • SQLADM authority and additional privileges are required to use the extended set of tuning features.
An sqlts_dba_id requires all the same privileges that a sqlts_user_id requires plus these additional privileges:
  • Permission to execute the IBMTMS.CANADMINISTER authentication UDF. Sample JCL for this GRANT statement is provided in the DSN5RUDF sample job.
  • Optionally, privileges to capture data from a user-defined SQL repository. Applications that can gather runtime metrics about the performance of SQL statements within specified intervals of time can offload the runtime metrics and the SQL statements into Db2 for z/OS tables. These applications also gather and offload information about the database objects that were referenced by the SQL statements that ran during an interval.

    To access this data, ensure that the sqlts_targetsys_id has SELECT privileges on the tables that contain the text of SQL statements, runtime metrics, and information about objects that the SQL statements reference. If you use a view to join tables that contain the runtime metrics and the SQL text, ensure that the sqlts_targetsys_id has SELECT privilege on that view.

Example

Carol is an SQL Tuning Services administrator who is responsible for creating the repository database, creating repository tables, granting privileges to SQL Tuning Services users, managing all tuning connections, and so on. To perform these administrative tasks, Carol needs a sqlts_user_id ID with CANVIEW and CANADMINISTER privilege on the SQL Tuning Services repository database.

The following figure illustrates the function of Carol's sqlts_user_id ID.

Figure 1. IDs for setting up and administering SQL Tuning Services
IDs for setting up and administering SQL Tuning Services
Zhou is an SQL Tuning Services user who needs to tune SQL on Db2 for z/OS. As an SQL Tuning Services user, Zhou needs the following IDs:
  • A sqlts_user_id ID to log on to SQL Tuning Services. Because he is a user with no administrative responsibilities, Zhou's sqlts_user_id ID only needs UDF CANVIEW privilege on the repository database.
  • A sqlts_targetsys_id ID to connect to the target Db2 subsystem.
  • Access to the functional sqlts_repodb_id ID.

The following figure illustrates the function of the IDs that Zhou needs to use. In this figure, the repository database resides on its own subsystem, but it can also reside on the same subsystem as a target Db2 database.

Figure 2. IDs for using SQL Tuning Services
IDs for using SQL Tuning Services