Before you start
The pureQuery technology has relied since its introduction on a file-based approach to store its configuration and relevant application metadata information. While this approach works well in small deployments, there is a need for a more centralized approach that does the following:
- Simplifies administration
- Improves collaboration among DBAs and developers
- Supports broader analysis
- Enables tighter cross-product integration
- Scales well
- Leverages the strength of database systems with respect to availability, access control, and reliability in enterprise environments
The SQL management repository in Optim Development Studio 126.96.36.199 can meet those requirements. This tutorial introduces this repository and guides you through the process of setting one up using an example Ant script.
Introduction to the SQL management repository
The SQL management repository stores information about pureQuery-enabled applications in containers called runtime groups. Each runtime group contains application-specific information and pureQuery configuration information, as shown in Figure 1.
Figure 1. The central database repository leverages the advantages of DBMSes and improves collaboration
Application-specific information includes a list of known SQL statements that an application processes, SQL-to-source code correlation information, execution-related metrics, and other metadata that is useful in the context of common application maintenance, tuning, and problem determination tasks. The pureQuery Runtime typically collects this information and stores it in the repository as part of the pureQuery client optimization process.
pureQuery configuration information comprises runtime settings for the application and configuration files that are used by pureQuery utilities to create the artifacts that support static SQL execution (for DB2® databases).
You can use Optim Development Studio (ODS) to manage the repository interactively or the ManageRepository command-line utility to automate common administration tasks, as shown in Figure 2. Other applications, such as the pureQuery Runtime, can retrieve and, to some extent, update information that is stored in the repository.
Figure 2. The SQL management repository is accessed using various Optim tools
In Optim Development Studio 2.2.1, an SQL management repository can reside in any of the following DB2 versions:
- DB2 9.1, 9.5, and 9.7 for Linux®, UNIX®, and Windows®
- DB2 for System i® V5R3 or V5R4
- DB2 Universal Database for z/OS® 8.1
- DB2 9.1 for z/OS
SQL management repository-enabled applications (such as ODS and pureQuery Runtime) can access the repository using dynamic or static SQL. Enabling static SQL access during the repository setup process is recommended to lock down access to the underlying database objects as much as possible. Access to the repository objects is restricted to users that have been granted manager or application access. Manager access needs to be granted to users (or groups) that have a need to create, read, modify, or delete pureQuery application information. Application access is used only by the pureQuery Runtime to update application metadata.
In an enterprise environment, multiple SQL management repositories can be used to accommodate the deployment life cycle stages of a pureQuery application in multiple test and production environments.
For in-depth information about the SQL management repository, refer to the Integrated Data Management Information Center in Resources.
About this series
This tutorial is Part 1 of a loosely grouped three-part "Managing pureQuery-enabled applications efficiently" series that covers the client optimization process that extends the pureQuery benefits to existing Java™ applications.
About this tutorial
In this tutorial, you will learn how to use IBM Optim Development Studio to set up an SQL management repository using a simple Ant script example that exposes the ManageRepository command-line utility that is shipped as part of the pureQuery Runtime. This Ant script is provided in the Download section of this tutorial. It simplifies common tasks and provides an ideal approach to get up and running quickly without the need to learn about the more complex ManageRepository command-line utility.
The objective of this tutorial is to walk you through the basic SQL management repository setup process. While completing this tutorial, you will complete the following steps:
- Create a database that stores the SQL management repository.
- Prepare Optim Development Studio to run the example Ant repository management script.
- Customize the repository management script.
- Create the SQL management repository database objects, enable static SQL access, and validate the repository's integrity.
- Learn how to customize the SQL management repository deployment options.
- Grant users access to the repository.
- Create a repository connection in Optim Development Studio, allowing you to manage pureQuery-enabled applications.
The following software must be installed to complete this tutorial:
- Optim Development Studio 188.8.131.52 or later. See Resources for how to download a no-charge trial version.
- DB2 Database Server. See Resources for how to download a no-charge trial version of DB2 Express-C.
Creating a database and preparing ODS
Before you can set up an SQL management repository, you need to create a database or identify an existing one that will be used to store the repository objects. There are no real restrictions on which database you can use, as long as it resides on a supported DB2 database server. To keep this tutorial simple, you will create a new database on your local system. If you prefer to use an existing (remote) database, skip to Preparing ODS to run the repository management script.
Creating the repository database
To create a new local database, complete the following steps:
- Ensure that DB2 Express-C 9 (or your supported DB2 database server) is running.
- From the Start menu, open a DB2 command window by selecting Programs > IBM DB2 > DB2COPY1 > Command Line Tools > Command Window.
- Create a database using a name of your choice, such as SQLMGMT by typing DB2 CREATE DATABASE SQLMGMT at the prompt.
In the next section, you will prepare the Ant script that is provided as part of this tutorial.
Preparing ODS to run the repository management script
Optim Development Studio and the pureQuery Runtime include the ManageRepository command-line utility, which provides administrators with the ability to manage a repository using low-level operations. To simplify the setup process, this tutorial provides an easy-to-use utility script using Apache Ant that hides some of the complexities and enables you to get a repository up and running more quickly.
Obtaining the example repository management script
- Download the
from the Downloads section of this tutorial and extract it into a
temporary directory of your choice. The archive contains two files
that you will import into Optim Development Studio. They
are provided as-is. You can modify or re-purpose them as you wish.
They will help you get started. The files are:
- setup_repository.xml, which is the utility script
- setup_repository.properties, which is the configuration file for the utility script
If you are familiar with Apache Ant and have it already set up in your environment, there is no need for you to use Optim Development Studio for the basic repository set up. You can complete most of the steps using the environment that you are already have set up.
Importing the repository management script into ODS
Optim Development Studio (like other Eclipse-based tools) provides out-of-the-box support for Ant scripts, making it easy to configure and run the scripts. Complete the following steps to import the script into ODS:
- Open Optim Development Studio.
- Enter (or select) a path of your choice for the workspace directory.
- Click File > New > Project from the main menu.
- Click the Java Project wizard and click Next. The New Java Project wizard opens.
- Enter ManageRepository as the project name.
- Click Finish.
- If prompted, click Yes to switch to the Java perspective.
Complete the following steps to import the repository management script:
- Go to the Package Explorer view and click the project you just created.
- Right-click the project and click Import from the context menu.
- Expand the General node, select File System, and click Next.
- Click the Browse button and navigate to the temporary directory where you unzipped setup_repository_using_ANT.zip.
- Click OK.
- Select the checkboxes next to the two files listed: setup_repository.properties and setup_repository.xml.
- Click Finish to import the files.
- Locate the Package Explorer view and verify that the two files are now in the ManageRepository project, as shown in Figure 3.
Figure 3. The SQL management repository script simplifies common setup tasks
You are now ready to customize the script for your environment.
Configuring the repository setup script
The example repository setup script provides you with the capability to do the following:
- Create or delete a repository
- Create the DDL for the repository objects, which can be customized
- Grant users or revoke access to a repository
The behavior of this script is governed by configuration settings that are defined in the setup_repository.properties file and by interactive inputs that you provide while the script is executed. Complete the following steps to review the default configuration settings that apply to all repository operations and modify them as needed:
- In the Package Explorer view, double-click the file setup_repository.properties to open it in an editor, as shown in Listing 1.
Listing 1. Customizing database connectivity information
#------------------------------------------------ # Section 1: # Repository connection information #------------------------------------------------ #repository URL repository.url=jdbc:db2://localhost:50000/SQLMGMT #username to connect to the database repository.user=db2admin
The first section, named Repository connection information, defines database connectivity information, such as JDBC URL and the user ID that will be used to create, modify, and drop the database objects (tables, views, and optionally database packages) that make up the repository artifacts.
- Replace the default value for the repository.url configuration setting with the appropriate values for the database that you created or identified earlier. For example, if your repository database resides on your local machine and it is accessible via port 50,000 and it is named SQLMGMT, enter jdbc:db2://localhost:50000/SQLMGMT.
- Replace the default value for the repository.user configuration setting with a valid DB2 administrator ID, such as db2admin. If the specified user does not have sufficient authority on the database, the repository management script will fail execution. The password for this user is not stored in the configuration file. Instead, a prompt is shown whenever you run the script to complete a setup operation.
- Do not make any changes to Sections 2 and 3 of the setup_repository.properties file, which contain customization options that are relevant to access control and repository removal tasks, as shown in Listing 2.
Listing 2. Each configuration section maps to one setup task
#------------------------------------------------ # Section 2: # Repository access authorization #------------------------------------------------ ... #------------------------------------------------ # Section 3: # Repository removal #------------------------------------------------ ...
- Validate the existence of
the runtimejars.dir configuration setting in Section 4 of the
setup_repository.properties file by navigating to your
Optim Development Studio installation directory and locating the
dsdev\jar directory. It should contain four JAR files:
db2jcc.jar, db2jcc_license_cisuz.jar, pdq.jar, and pdqmgmt.jar.
Figure 4 shows Section 4 named
housekeeping, which defines general settings.
Figure 4. Validating that the required Java libraries are present
Typically only the locations of the pureQuery Runtime and JDBC libraries need to be customized.
- Modify the configuration file as needed, and press Ctrl+S to save the changes.
Housekeeping is done! Now that you configured the default configuration settings, use the Ant script to set up a simple SQL management repository.
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.
Identifying repository characteristics
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.
Creating an SQL management repository
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.
Reviewing and customizing the DDL that creates an SQL management repository
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.
Granting manager access to the SQL management repository
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.
Connecting ODS to an SQL management repository
Optim Development Studio (ODS) provides you with a set of tools that simplify common repository management tasks. These tools are available through the IBM Common Repositories perspective.
Complete the following steps to connect ODS to a repository:
- Open Optim Development Studio.
- Open the IBM Common Repositories perspective by selecting Window > Open Perspective > Other > IBM Common Repositories from the main menu. The Common Repositories Explorer view provides access to repositories that can be managed, as shown in Figure 13.
Figure 13. The IBM Common Repositories perspective provides management access for connected repositories
- Notice that no repository connections exist, even though you created a repository in the SQLMGMT database. Before you can manage that repository using Optim Development Studio, you need to create a repository connection.
- In the Common Repositories Explorer view, select Common Repositories Connections.
- Right-click and select New from the context menu. The New Common Repository wizard is displayed.
- Enter a repository name in the Repository Name field. In a typical deployment, you manage multiple repositories, so use descriptive names, such as QA_LUW, PROD_ZOS, to help you easily identify the environment for which you are managing pureQuery-enabled applications.
- Pick an existing database connection for the repository database that you created earlier, or create a new one if none exists. Make sure this connection uses the credentials of a user that has been granted manager access to the repository. Figure 14 shows an example of a valid connection configuration for the repository that was created as part of this tutorial.
Figure 14. Add a repository connection to start managing a repository
After the connection has been created, the new repository connection is displayed in the Common Repository Explorer. You are now ready to manage your pureQuery-enabled applications in this SQL management repository. Figure 15 shows the Common Repository Explorer view after the repository was connected successfully.
Figure 15. The newly created repository can now be managed using the IBM Common Repositories perspective
Following are a few tips and tricks for a more successful project:
- An SQL management repository is not dropped if its connection is deleted in the Common Repository Explorer.
- The repository connections (and connection names) are not shared between different Optim Development Studio installations. In other words, a connection that you are creating on one computer is not visible on another computer. The connection information you provided is only stored locally in ODS and not in the repository itself.
- If an error is returned indicating that the repository does not exist, make sure the repository database objects exist in the target database. Also make sure the user ID that you are using to connect to the database has been granted manager access.
For information on how to leverage the IBM Common Repositories Perspective, see Resources.
You successfully set up a basic SQL management repository by completing the following tasks:
- You created the database objects that implement the repository and learned how to generate the DDL that enables you to customize some of the deployment options.
- You designated a user as a manager for this repository, enabling this ID to access and manipulate the repository content.
- You connected Optim Development Studio to the repository using the manager ID, enabling you to use the Optim Common Repositories perspective to start managing pureQuery-enabled applications.
Figure 16, which is a slightly modified version of Figure 2, shows what you accomplished with this tutorial: you provided the foundation that enables you to manage pureQuery-enabled applications more efficiently.
Figure 16. The repository infrastructure has been successfully created
Part 2 of this Managing pureQuery-enabled applications efficiently series walk you through the process of managing a pureQuery-enabled application using the SQL management repository. Part 3 describes an approach to automate the application management process using another Ant script.
The setup_repository.xml script illustrates how easy it is to encapsulate the low-level functionality that the ManageRepository utility provides and compensate for some of the management features that are not included in Optim Development Studio. The information center (see Resources) provides lots of examples on how to use this utility. Once you have a better understanding of repository management tasks, you can start creating your own tasks to build an automation framework to free you up for other activities—like reading tutorials.
The authors wish to thank our reviewers for their thoughtful insights. You know who you are, and we appreciate your support over the years.
|Sample tutorial Ant script||setup_repository_using_ANT.zip||3KB|
- Review "Optimize your existing JDBC applications using pureQuery" (developerWorks, August 2008) to improve security and performance for existing JDBC applications.
- Refer to the Integrated Data Management Information Center.
- Get background information about the ManageRepository command-line utility.
- Go to Managing SQL Management repositories with the workbench in the information center to leverage the IBM Common Repositories Perspective.
- Check out the Optim Development Studio and pureQuery Runtime area on developerWorks to get the resources you need to advance your database application development skills.
- Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Follow developerWorks on Twitter.
Get products and technologies
- Download a trial version of IBM Optim Development Studio and pureQuery Runtime.
- Download a free trial version of DB2 9.7 for Linux, UNIX, and Windows.
- Download DB2 Express-C, which is a free edition of DB2 database for Linux, Windows, and Mac platforms that is easy to use and provides a solid base to build and deploy applications.
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Check out the developerWorks blogs and get involved in the developerWorks community.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.