Setting up the repository database

Use the instructions in this section to create the SQL Tuning Services repository database, which is a Db2® for z/OS® database that SQL Tuning Services uses to store tuning metadata.

Step 1: Create the repository database ID

The SQL Tuning Services repository database ID is a functional Db2 for z/OS ID that is used to create the repository database and to connect to the Db2 for z/OS subsystem where the repository database resides. This ID is referred to as the sqlts_repodb_id.

Procedure

Consult with your security administrator to create this ID.

There are two common approaches to creating the repository ID:

  • Instead of using an existing functional Db2 for z/OS ID, create a new functional ID for the sqlts_repodb_id and assign it a password that does not expire, which eliminates the extra maintenance that is involved with regularly resetting the password. The password is encrypted and is stored in the repodb_override.properties file. This approach is easier to implement but provides a lower level of security.

    If you are not able to assign a password that doesn’t expire, see Changing the repository database user ID and password for instructions for updating the password after you change it in RACF.

  • Use PassTickets for the repository database ID. This approach provides a higher level of security. See Enabling Db2 to receive RACF PassTickets for more information about using PassTickets in Db2.

    To implement PassTickets for the repository database ID, see the example tmsservice.config settings for setting up PassTickets for the repository database ID.

Step 2: Grant CREATE privileges

The SQL Tuning Services repository database ID (sqlts_repodb_id) needs the privileges to create the repository database.

Procedure

Grant the following privileges to the sqlts_repodb_id: 
GRANT CREATEDBA ON SYSTEM TO sqlts_repodb_id  

GRANT CREATEIN ON SCHEMA IBMTMS TO sqlts_repodb_id
Note: The JCL for these two statements is available as step TMSGRT2 of the DSN5RTRP sample job. You can copy this sample JCL into a new job to GRANT the required privileges for this step. However, do not run the GRANT EXECUTE statements that are also in DSN5RTRP. You will run these statements in a future step.

Step 3: Create the repository database

To create the SQL Tuning Services repository database, which is a Db2 for z/OS database that SQL Tuning Services uses to store tuning metadata.

Procedure

To use sample JCL to create the repository database, customize and run the DSN5REPO sample job:
  • The name of the repository database is IBMTMSDB. It is recommended that you accept the default name.
  • Set the SQLID variable to the SQL Tuning Services repository database ID (sqlts_repodb_id).
  • Set the remaining variables to values that are appropriate for your environment.
After you have customized DSN5REPO, run it and ensure that it completes successfully.

Step 4: Bind required packages

Bind the packages that SQL Tuning Services needs to the Db2 subsystem or subsystems where you will be tuning.

About this task

Sample JCL is provided to complete this task. The sample jobs that you run depend on whether the repository database and the tuning target databases reside on the same or different Db2 subsystems.
  • If the repository database and the tuning target databases reside on the same Db2 subsystem, customize and run the DSN5BND sample job to bind all the required packages.
  • If the repository database and the tuning target databases reside on different Db2subsystems, customize and run the following sample jobs:
    • Run DSN5NDRP to bind the required packages to the repository database subsystem.
    • Run DSN5NDTG to bind the packages to the tuning target database subsystem.

Step 5: Grant EXECUTE privileges

The SQL Tuning Services repository database ID (sqlts_repodb_id) needs the privileges to use the packages.

Procedure

Customize and run the DSN5RTRP sample job to grant the sqlts_repodb_id EXECUTE privileges to the packages that you bound in the previous step. For example: 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OPKN TO sqlts_repodb_id; 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OWCN TO sqlts_repodb_id; 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OWCC TO sqlts_repodb_id; 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OINS TO sqlts_repodb_id; 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OIAA TO sqlts_repodb_id; 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OIAK TO sqlts_repodb_id; 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OIA1 TO sqlts_repodb_id; 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OIA3 TO sqlts_repodb_id; 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OIA4 TO sqlts_repodb_id; 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OIA5 TO sqlts_repodb_id; 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OIA6 TO sqlts_repodb_id; 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OIA7 TO sqlts_repodb_id; 
GRANT EXECUTE ON PACKAGE IBMTMS.DSN5OIA8 TO sqlts_repodb_id;
Note: The DSN5RTRP sample job contains two statements for granting privileges that the sqlts_repodb_id needs to create the repository database. You ran these statements in a previous step. Do not run them again.