Setting up an SQL management repository
To set up an SQL management repository:
- Identify the repository characteristics during the planning stage.
- Create the required database objects using the setup script that you imported and customized for this tutorial.
- Grant manager access to the objects for the users that you identified during the planning activity.
- Enable your managing users to connect their Optim Development Studio to the repository before they can use it.
The repository characteristics implicitly define the steps that need to be completed to set up an operational SQL management repository that meets your needs. Answer the following common questions as part of this activity:
- What is the desired repository scope?
- In this tutorial,
you are creating a sandbox repository that you can use to
explore some of the management features that the example
script, the ManageRepository utility, and Optim Development
Studio provide. In a real-life scenario, the scope of a
repository might span one or more test environments, a
production environment, or any other logical grouping of
pureQuery-enabled applications that you might have
deployed in your enterprise.
For example, assume you want to use a single repository to manage all pureQuery-enabled applications that run in your development, test, and production environments. While this provides managers with a global view of your deployed applications, it also increases the risk, because all of your environments rely on a single repository. Repositories should ideally reflect the common life-cycle of your applications. Creating a repository instance for development systems, one for test, and one for production environments separates the information that is stored for each version of the application that might be used in the those environments. The amount of information stored in each environment might vary as well, depending on your exact needs.
- Where should the repository reside?
- A repository can reside on any supported database server. Generally speaking, you can choose to create the repository on one of your application nodes, in which case collocation with the application can reduce network traffic. You can then create the repository in the same database where your transactional data resides (application data and application metadata are managed as an entity) or on a separate computer.
- Who can manage the repository content?
- Anybody that you designate as an administrator for pureQuery-enabled applications must be granted access to the repository. By default, only the person who owns the repository database artifacts (typically the creator) is authorized to manipulate the repository content.
- Who can access, but not manage, the repository content?
- This is actually a question that you can answer only after you identify the pureQuery-enabled applications that will be managed using this repository.
Using the instructions in the following sections, you are going to complete these activities for a simple scenario. Upon successful completion, you'll have an operational repository that you can use to easily manage your pureQuery-enabled applications.
Note that it is beyond the scope of this tutorial to elaborate on how to actually manage a pureQuery-enabled Java application using the repository. Another part of this Managing pureQuery-enabled applications efficiently series provides more details.
The repository in this tutorial will manage a small set of pureQuery-enabled applications in a local Windows test environment where a DB2 database named SQLMGMT has already been created. One existing user ID, db2user, will be used to manage the locally running applications using an installation of Optim Development Studio 2.2.1.
An instance of the SQL management repository consists of a set of tables, views, and database packages in DB2 only. The number of database objects that make up a repository instance depends on the chosen repository type. For most environments, a lightweight repository is appropriate and is therefore created by default if you are using the provided Ant script. Note that the database objects must reside in the IBMPDQ schema.
Complete the following steps to create the repository:
- Open Optim Development Studio, and select the workspace into which you imported the repository setup scripts.
- In the Package Explorer view, double-click the setup_repository.xml file to open it in an editor.
- Locate the Outline view in your workspace. This
view shows a list of predefined repository management tasks, as
shown in Figure 5. If you can't find the
view, select Window > Show View > Outline from the main toolbar
menu. If this view does not show any information or shows
something other than what is shown in Figure 5, make sure you have selected the editor
view that contains
Figure 5. The Outline view displays a list of available SQL management repository tasks
By default this view shows a lot of information that is not relevant to you.
- Hide the irrelevant information by clicking each hide button, as shown in Figure 6.
Figure 6. Hiding unnecessary information in the Outline view
Use the setup_repository task to create the required tables and views, bind the database packages that support static SQL access, and validate that the database objects have been created properly.
- Right-click the setup_repository task entry in the Outline view, and select Run As > Ant Build to create the repository.
- Enter the password for the user ID that you specified earlier when you customized the repository.user configuration setting in the setup_repository.properties file, as shown in Figure 7. Note that the input field is not masked due to a limitation of the version of Ant being used.
Figure 7. Password prompt
The Console view displays the results of the create, bind, and validation operations, as shown in Figure 8.
Figure 8. An SQL management repository was successfully created
Any failures are likely caused by one of the following:
- invalid configuration settings in the setup_repository.properties file
- Mistyped input values
- Insufficient authority to execute the required DDL or DML statements
As an enterprise database administrator, you might be interested in reviewing and customizing the DDL that is used to create the repository related database objects. In the following section, you will explore another task that will store the DDL in a text file that you can customize.
The generated DDL statements create database objects based on database defaults. While these defaults may be sufficient in test installations, you might want to place objects in specific table spaces in production or stress test environments. You can take advantage of the create_DDL task to force the generation an SQL file, customize it, and execute it using your favorite utility. Note that the generated DDL can vary based on the target DB2 database and the type of repository you are creating.
Reviewing the DDL requires the same inputs as you used when you created the repository: the JDBC URL of the target database, the user ID of an administrator, and the corresponding password. The first two inputs you already defined in Section 1 of the setup_repository.properties configuration file. The third input is entered (like before) at a prompt. You are therefore ready to run the create_DDL task.
Complete the following steps to create the DDL:
- In the Package Explorer view, double click the setup_repository.xml file to open it in an editor if it is not already open.
- Locate the Outline view, and find the create_DDL task.
- Right-click create_DDL, and select Run As > Ant Build from the context menu.
- Enter your database password. Note again that the password you are entering will be displayed in clear text.
A success message is displayed, and an SQL script named DDLscript.sql is created, as shown in Figure 9.
Figure 9. The customizable DDL script to create an SQL management repository was successfully created
- Locate the Package Explorer view, and select the ManageRepository project.
- Press the F5 key to refresh the view. The generated SQL script is displayed, as shown in Figure 10.
Figure 10. The Package Explorer view displays the generated SQL file DDLscript.sql
- Double-click the generated DDLscript.sql file to open it in an editor.
- Explore the SQL file, keeping the following in mind:
- The table names and view names are unqualified. Because
these repository objects must reside in the IBMPDQ
schema in the current release, you need to insert the
in the beginning of the SQL script to set the current
set current schema 'IBMPDQ'. (The schema qualifier is set implicitly if you use the setup_repository task to create the database objects.)
- The generated create table statements do not specify an IN clause, leaving it up to you to assign table space mappings.
- You cannot change object names or data types.
- After you manually run the SQL script, you need to run the bind_and_verify_repository task to create the supporting database packages and to validate that the repository was created properly.
- The table names and view names are unqualified. Because these repository objects must reside in the IBMPDQ schema in the current release, you need to insert the following line in the beginning of the SQL script to set the current schema register:
- Because you already created the repository objects in this tutorial, close the generated SQL file without making any changes or running it. In a real-life scenario you would run only the task described in the previous section or the tasks described in this section.
Having created the repository database objects using either the default SQL or a customized version, you are now ready grant manager access to the users that you identified during the planning phase. Keep in mind that a manager can manipulate any data that is stored in the repository. Therefore grant this privilege sparingly and appropriately.
Access control to the repository is enforced using database security. While it is possible to grant privileges to the database objects explicitly, it is much easier to use the grant_privileges task to identify and execute the necessary database grants implicitly. Similarly, the revoke_privileges task can be used to revoke a previously granted privilege.
Complete the following steps to grant manager access appropriately:
- In the Package Explorer view, double-click the setup_repository.properties configuration file to open it in an editor, if it is not already open.
- Find Section 1
Repository connection information, and verify that configuration setting
repository.urlpoints to the repository to which you want to grant users access.
- Make sure that the user ID listed as
repository.userhas sufficient authority to grant SELECT, INSERT, UPDATE, and DELETE on the repository tables and views (if dynamic SQL access is required) or EXECUTE on the repository support packages (if static access is required).
- Locate Section 2
Repository access authorization. This section defines for the grant and revoke tasks the impacted users and the privileges and access levels that are to be granted or revoked.
To grant a user (or group) access to a repository, customize the following three configuration settings:
- Defines a single user, a group, or a comma-separated list of users or groups that need authority to access the repository.
- Defines the repository objects for which access is
tableAccessis specified, the users are authorized to access the repository tables (IBMPDQ.*_T) and views. If
executePackageis specified, the EXECUTE PACKAGE privilege is granted assuming the repository support packages (IBMPDQ.AMD*) have been created.
- Defines the type of access that is granted to specified users. Two access levels are currently supported: manager and application. Manager access is required by users who have administrative responsibility and by users who need to access pureQuery-enabled application metadata, such as for impact analysis. Application access, which is much more restrictive, is typically granted only to user IDs that are associated with a pureQuery Runtime deployment on an application node. To determine whether to grant manager or application access, ask yourself whether a given user ID will be used in Optim Development Studio or in the command-line utilities to work with the SQL management repository. If it will, manager access might be the appropriate choice.
- For this tutorial, assign the existing user db2user to repository.grant.users.
- Assign executePackage to repository.grant.privileges.
- Assign manager to repository.grant.accessLevel, as shown in Listing 3.
Listing 3. Granting manager repository access to user db2user
#------------------------------------------------ # Section 2: # Repository access authorization #------------------------------------------------ # grant access configuration # specify the user/group you would like to grant privileges to repository.grant.users=db2user #type of privilege being granted for specified entity repository.grant.privilege=executePackage #type of access level. Supported types are APPLICATION and MANAGER repository.grant.accessLevel=manager
- Save the changes in the setup_repository.properties configuration file.
- In the Package Explorer view, double-click the setup_repository.xml file to open it in an editor, if it is not already open.
- In the Outline view, click the grant_privileges task.
- Right-click and select Run As > Ant Build from the context menu.
- Enter your password at the prompt. A status message appears in the Console view, indicating whether the operation completed successfully, as shown in Figure 11.
Figure 11. Manager access to the repository was successfully granted
- Review the catalog tables using the Data Source Explorer in Optim Development Studio to verify the privileges that were granted to the underlying database objects, as shown in Figure 12.
Figure 12. Reviewing granted permissions using the Data Source Explorer and the Properties view
- To grant additional privileges or revoke privileges, repeat Steps 1-13, keeping in mind that the corresponding configuration settings for the revoke task are named in the configuration file as repository.revoke.users, repository.revoke.privileges, and repository.revoke.accessLevel.
- Close the files setup_repository.properties and setup_repository.xml. You will not need them again during this tutorial.
The basic SQL management repository setup is complete. The supporting database objects have been created, and a user has been authorized to manage them. Next, you need to connect Optim Development Studio to the repository, allowing you to work with pureQuery-enabled applications that are administered using this repository.