Setting up required user IDs and permissions

Specific user IDs with sufficient permissions are required to install, configure, administer, and use SQL Tuning Services, Db2® for z/OS®, and other related components. You must allocate or create these user IDs before you start to install SQL Tuning Services.

Installing and configuring SQL Tuning Services and its related products requires several different user IDs that have specific privileges and permissions. These IDs are listed in the following table. The names in the user ID column are used throughout the installation and configuration documentation, but you can assign any names that you want to these IDs.

User ID	Description	Required privilege or permission
`tms_setup_userid`	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`
`db2_authid_R`	This user ID is used to access the Db2 for z/OS subsystem where the SQL Tuning Services repository database resides.	The CREATEDBA privilege and the CREATEIN privilege on SCHEMA IBMTMS are required on the Db2 for z/OS subsystem where the SQL Tuning Services repository database will reside: `CREATEDBA ON SYSTEM TO db2_authid_R CREATEIN ON SCHEMA IBMTMS TO db2_authid_R` Additionally, to use the extended tuning features, the `db2_authid_R` ID requires EXECUTE privilege on all of the packages that are listed in the DSN5RTRP sample job. Use the DSN5RTRP sample job to grant these privileges.
`tms_userid`	This user ID is used to log on to SQL Tuning Services.	All SQL Tuning Services users need the following permissions: Permission to connect to the SQL Tuning Services repository database through JDBC Permission to execute the CANVIEW authentication UDF (by default, IBMTMS.CANVIEW) as a valid SQL Tuning Services user `EXECUTE ON FUNCTION IBMTMS.CANVIEW TO tms_userid` Additionally, you need a `tms_userid` that has permission to execute the CANADMINISTER authentication UDF (by default, IBMTMS.CANADMINISTER). `EXECUTE ON FUNCTION IBMTMS.CANADMINISTER TO tms_userid` This user ID is considered to be an SQL Tuning Services administrator and is responsible for the following tasks: Setting up the repository database Managing all tuning jobs Managing all connection profiles A user who does not have permission to execute the CANADMINISTER UDF can see only their jobs.
`db2_authid_T`	This user ID is a Db2 for z/OS authorization ID that's used to connect to the target Db2 for z/OS subsystem to run various tuning APIs. To run certain tuning APIs, the `db2_authid_T` requires EXPLAIN privileges. For more information about these privileges, see the Authorization section of EXPLAIN statement.	Use the DSN5RTTG sample job to grant all of the following privileges. General privileges All `db2_authid_T` IDs require EXECUTE privilege on the packages that are listed in the DSN5RTTG sample job. DSN5OADM DSN5OEPN DSN5OFMM DSN5ONPT DSN5OPKN DSN5OSCM Privileges for SQL Capture, Statistics Advisor, and Query Environment Collector To use these three APIs, the `db2_authid_T` ID requires SQLADM authority. To use the Query Environment Collector, the `db2_authid_T` requires the privileges that are documented in the Authorization section of ADMIN_INFO_SQL stored procedure. 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 `db2_authid_T` 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 `db2_authid_T` ID has SELECT privilege on that view. Privileges to manage Explain tables To manage Explain tables, the `db2_authid_T` ID requires the privileges that are documented in the Authorization section of ADMIN_EXPLAIN_MAINT stored procedure. Privileges for extended tuning features To use the extended set of tuning features, the `db2_authid_T` ID requires SQLADM authority and EXECUTE privilege on the following packages: DSN5OIA2 DSN5OIA7 DSN5OIA8 DSN5OIAL DSN5OQRA DSN5OSCM DSN5OWCN

Example

Carol is an SQL Tuning Services administrator who is 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 adminstrative tasks, Carol needs a tms_userid ID with CANVIEW and CANADMINISTER privilege on the SQL Tuning Services repository database.

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

Figure 1. 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 tms_userid ID to log on to SQL Tuning Services. Because he is a user with no administrative responsibilities, Zhou's tms_userid ID must have UDF CANVIEW privilege on the repository database.
A db2_authid_T ID to connect to the target Db2 subsystem.
Access to the functional db2_authid_R 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