![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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 2.2.0.3 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.
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.
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:
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular tutorial tags |
My tutorial tagsSkip to tags list
Popular tutorial tags |
My tutorial tags
