ManageRepository utility

The ManageRepository utility creates a repository in a database and manages the repository. The repository contains pureQuery data used by pureQuery-enabled applications such as configuration information and pureQueryXML data. The repository can store captured SQL data from applications enabled with pureQuery client optimization.

A database that supports installing a repository must be available, and the application must be configured to use pureQuery client optimization. See System requirements for InfoSphere™ Optim™ pureQuery Runtime for pureQuery system requirements.

The following sections describe the ManageRepository utility:

To understand the conventions that are used in the diagram, see How to read syntax diagrams.

Repository-level operations

The repository-level ManageRepository operations include creating, upgrading, and removing a repository in a database, checking the validity of the repository and displaying the version of the repository.

To view the SQL statements required to create a repository, remove a repository, or bind a repository, use the -generateScriptOnly option. The statements are generated without performing the operation. If you modify the SQL statements and issue the statements, you can use the -verify repository option to check the repository.

To create a repository that contains only the information from pureQuery files used for pureQuery client optimization, use the -repositoryType option with the pureQueryRuntimeOnly value.

Syntax of the ManageRepository command for repository-level operations

Read syntax diagramSkip visual syntax diagram
    (1)                                             
>>-------java--com.ibm.pdq.tools.ManageRepository--------------->

>--+- -create repository-- -repositoryType--+-pureQueryOnly--------+-+-->
   |                                        '-pureQueryRuntimeOnly-' |   
   |  (2)                                                            |   
   +------- -bind packages-------------------------------------------+   
   +- -verify repository---------------------------------------------+   
   +- -report--+-repositoryData-- -file--file-name-+-----------------+   
   |           '-repositoryVersion-----------------'                 |   
   +- -delete repository--+--------------------+---------------------+   
   |                      '- -force--+-FALSE-+-'                     |   
   |                                 '-TRUE--'                       |   
   '- -upgrade-------------------------------------------------------'   

>--| connection information |----------------------------------->

>--+-------------------------------------------------+---------->
   |  (3)                                            |   
   '------- -generateScriptOnly--path-to-script-file-'   

>--+--------+--------------------------------------------------><
   '- -help-'   

connection information

|-- -repositoryURL--URL-to-repository-database------------------>

>-- -repositoryUsername--user-ID-- -repositoryPassword--password-->

>-- -repositoryDriverClass--driverclass-------------------------|

Notes:
  1. You can specify the options in any order.
  2. The -bind packages option can be used only with a database that supports static SQL.
  3. The -generateScriptOnly option cannot be used with the -report option.

Description of options for repository-level operations

The following list describes the supported ManageRepository options and variables.

-create repository
The ManageRepository utility uses the connection information to determine the database platform type and issues the appropriate DDL statements to create the tables in the specified schema.

A message is displayed to indicate that data will be replaced by this command. The user has the option to cancel the action.

-repositoryType
Specify the type of repository to create. The supported types of repository are:
pureQueryOnly
This value is the default. The repository can contain information from pureQuery files used by applications enabled with pureQuery client optimization, pureQuery Runtime properties and pureQueryXML data. The repository can also contain bind options, and options to configure to pureQueryXML files.

When you upload the contents of a pureQueryXML file to a repository, the ManageRepository utility extracts the SQL statements from the pureQueryXML data and stores them separately in the repository.

The repository can contain SQL data that has been exported from IBM® Data Studio into the repository.

pureQueryRuntimeOnly
The repository can contain information from pureQuery files used by applications enabled with pureQuery client optimization, pureQuery Runtime properties and pureQueryXML data. The repository can also contain bind options, and options to configure to pureQueryXML files.

Use the -generateScriptOnly option to generate an SQL script with the SQL statements to create a repository without creating a repository. After reviewing the script, you can run the script to create the repository. If you modify the SQL statements and run the script, you can use the -verify repository option to check the repository.

-bind packages
On database platforms that support static SQL, specifies that database packages are created to govern access to the repository. Collection name and package names are chosen by the utility and are not modifiable by the user.

After the repository is created, the ManageRepository utility -grant and -revoke options are used to grant and revoke privileges to these packages. Information about granting access to the packages, see Runtime group privilege management operations

Use the -generateScriptOnly option to generate a script only without performing the bind operation.

-report repositoryData | repositoryVersion
Displays information about the repository.
repositoryData -file file-name
Generates an HTML report describing runtime group information in the repository. The file-name value is the path and file name to the file that will contain the report.
repositoryVersion
Returns the version of the repository.
-verify repository
Checks the tables in the repository to ensure that the tables have been created correctly with the column and column types as expected. Also, tests are performed for the correct read and write access to the repository, ensuring that the executing userid has correct authorizations.

Output that is returned includes information for the different components in the repository. The output also includes messages indicating problems with the repository such as incorrect authorizations or tables with missing columns.

Use the -generateScriptOnly option to generate an SQL script without verifying the repository.

-delete repository
Drops all the tables used by the repository. All data in the repository is deleted.
A message is shown and prompts the user to confirm to remove the database tables.
-force TRUE|FALSE
A Boolean value to confirm the removal of repository.

Use -force TRUE to confirm the removal without displaying a message.

Use the -generateScriptOnly option to generate an SQL script without deleting the repository.

-generateScriptOnly path-to-script-file
Generates an SQL script with the SQL statements to perform the ManageRepository operation without performing the operation. You must specify the connection information to the repository database.

In the script to create a repository, you can modify the script to change some object names. However, you cannot modify the schema name, table names, or column names. For information about the working with the ManageRepository scripts, see Usage notes.

After reviewing and modifying the script, a database administrator can create the repository by running the script. For example, the database administrator might add table space to the SQL statements in the script.

After creating the repository, the -verify repository option can be used to check the repository.

For the -bind packages option, the collection name and package names are chosen by the utility and are not modifiable by the user.

-upgrade
Upgrade a repository to support storing pureQuery log events in the repository. If the repository is already at the correct level, no action is taken.

If you used to the ManageRepository -bind packages option to create database packages to govern access to the repository, you must use the option -bind packages to rebind the packages.

For a repository that supports storing log events, you can specify the pureQuery logging property com.ibm.pdq.database to write pureQuery events to the repository.

Use the -generateScriptOnly option to generate an SQL script without upgrading the repository.

-help
Displays summary usage information.

Description of connection information options

The following list describes the information required to connect to a repository database:

-repositoryURL URL-to-repository-database
URL to the repository database
-repositoryUsername user-ID -repositoryPassword password
ID and password used to connect to the database
-repositoryDriverClass driverclass
Fully-qualified class name of JDBC driver class to use to connect to the repository database.

Runtime group management operations

The ManageRepository runtime group operations include creating, managing, and removing runtime groups and versions of groups in a repository.

The pureQuery data stored in a runtime group version includes pureQuery configuration information, pureQueryXML data, and optionally captured SQL data from applications enabled with pureQuery client optimization.

Runtime group versions also contain other information used by pureQuery Runtme to identify the runtime and the active version of the group.

Syntax for runtime group management operations

Read syntax diagramSkip visual syntax diagram
    (1)                                             
>>-------java--com.ibm.pdq.tools.ManageRepository--------------->

>--+- -create runtimeGroup--+--------------------+------------------------------------------------+-->
   |                        '-| pureQuery data |-'                                                |   
   +- -update runtimeGroup--+--------------------+------------------------------------------------+   
   |                        '-| pureQuery data |-'                                                |   
   +- -delete runtimeGroup--+--------------------+------------------------------------------------+   
   |                        |          .-FALSE-. |                                                |   
   |                        '- -force--+-TRUE--+-'                                                |   
   +- -delete incremental-- -inputDirectory--path-to-directory-- -incrementalCapture--file-prefix-+   
   +- -list runtimeGroupVersions------------------------------------------------------------------+   
   +- -activate runtimeGroup----------------------------------------------------------------------+   
   +- -deactivate runtimeGroup--------------------------------------------------------------------+   
   '- -copy runtimeGroup--+---------------------------------+-- -targetVersion--target-version----'   
                          '- -sourceVersion--source-version-'                                         

>-- -runtimeGroupId--GroupID------------------------------------>

    (2)                                   
>-------- -runtimeGroupVersion--version------------------------->

>--+--------------------------------+--+--------+--------------->
   '- -contact--contact-information-'  '- -help-'   

>--| connection information |----------------------------------><

pureQuery data

    (3)                                        
|-------- -inputDirectory--path-to-directory-------------------->

>--+----------------------------------------+------------------->
   '- -pureQueryXml--pureQueryXML-file-name-'   

>--+------------------------------------------+----------------->
   '- -pureQueryProperties--runtime-prop-file-'   

>--+------------------------------------------+----------------->
   '- -optionsFileForConfigure -genProps-file-'   

>--+--------------------------------------+---------------------|
   '- -optionsFileForBind -bindProps-file-'   

connection information

|-- -repositoryURL--URL-to-repository-database------------------>

>-- -repositoryUsername--user-ID-- -repositoryPassword--password-->

>-- -repositoryDriverClass--driverclass-------------------------|

Notes:
  1. You can specify the options in any order.
  2. The -runtimeGroupVersion option cannot be specified with the -copy runtimeGroup option.
  3. When updating pureQuery data, at least one file must be specified.

Description of options for runtime group management

The following list describes the supported ManageRepository options and variables.

-create runtimeGroup
Creates a runtime group with the GroupID and version specified.

If the runtimeGroupId exists in the repository, an error is displayed.

When creating a runtime group version, the following are default pureQuery settings:
  • The executionMode property is set to DYNAMIC (run SQL statements dynamically).
  • The captureMode property is set to ON (capture mode is set to incremental).

To override the defaults and to specify other properties, specify the files that contain properties and their values.

-update runtimeGroup
Update the data in runtime group version with the pureQuery data from the specified files.
-delete runtimeGroup
Deletes the runtime group GroupID from the repository.
A message is shown and prompts the user to confirm the removal of runtime group. User has the option to cancel the action.
-force TRUE|FALSE
Specify TRUE or FALSE to confirm the removal of the runtime group. TRUE deletes the runtime group without confirmation. The default is FALSE.

Use the -force TRUE option to confirm the removal without displaying a message.

-delete incremental
Deletes captured SQL data from a runtime group version. The data in the specified captured SQL files is compared with the repository data and the repository captured SQL data is removed if it matches.

After merging the captured SQL data with pureQueryXML data for a runtime group version, the captured SQL data for the version can be deleted.

-inputDirectory path-to-directory
The full path to directory containing the captured SQL files.
-incrementalCapture file-prefix
Prefix used by the captured SQL files.
-list runtimeGroupVersions
Displays information about the runtime group versions.
-activate runtimeGroup
Activate a runtime group version.

When pureQuery Runtime accesses a runtime group in the repository, it uses the pureQuery information in the active runtime group version.

-deactivate runtimeGroup
Deactivate a runtime group.

When pureQuery Runtime accesses a runtime group in the repository, it ignores the pureQuery information in runtime groups that are not active.

-copy runtimeGroup
Copy the runtime group data from the version specified by the -sourceVersion option to the same group with the version name specified by the -targetVersion option.
-sourceVersion source-version
Specifies an existing version in the runtime group that is used to create a runtime group version.

If a source-version value is not specified, the active runtime group version is used. If a source-version value is not specified and there is no active runtime group version, an error is displayed.

-targetVersion target-version
Specifies the runtime group version that is created when the source-version runtime group version is copied.
-runtimeGroup GroupID
Specifies the repository runtime group identifier
-runtimeGroupVersion version
Specifies a runtime group version name. The -runtimeGroupVersion option cannot be specified with the -copy runtimeGroup option.
-contact contact-information
A string specifying optional contact information stored with runtime group version.
-help
Displays summary usage information.

Description of PureQuery data options

The following list describes the pureQuery data options:

-inputDirectory path-to-directory
The full path to directory for the pureQuery files.
-pureQueryXml pureQueryXML-file-name
The name of the pureQueryXML file.
-pureQueryProperties runtime-prop-file-name
The name of the pureQuery runtime properties file.
-optionsFileForConfigure genProps-file
The file containing the options used by the Configure utility.
-optionsFileForBind bindProps-file
The file containing the options used by the StaticBinder utility to bind SQL statements in the pureQueryXML file.

Description of connection information options

The following list describes the information required to connect to a repository database:

-repositoryURL URL-to-repository-database
URL to the repository database
-repositoryUsername user-ID -repositoryPassword password
ID and password used to connect to the database
-repositoryDriverClass driverclass
Fully-qualified class name of JDBC driver class to use to connect to the repository database.

Runtime group privilege management operations

The privilege management operation are performed after SQL capture data are stored in the in the repository database. An administrator must grant permissions for applications to read and write to the repository database tables.

Syntax for privilege management operations

Read syntax diagramSkip visual syntax diagram
    (1)                                             
>>-------java--com.ibm.pdq.tools.ManageRepository--------------->

>--+- -revoke-+------------------------------------------------->
   '- -grant--'   

                     .-,----------------.           
                     V                  |           
>----"--grantees--(----authorization-ID-+--) -"----------------->

>-- -privilege--+-tableAccess----------+------------------------>
                |  (2)                 |   
                '-------executePackage-'   

>-- -accessLevel -+-APPLICATION-+--| connection information |--->
                  '-MANAGER-----'                               

>--+--------+--+-------------------------------------------+---><
   '- -help-'  '- -generateScriptOnly--path-to-output-file-'   

connection information

|-- -repositoryURL--URL- to-repository-database----------------->

>-- -repositoryUsername--user-ID-- -repositoryPassword--password-->

>-- -repositoryDriverClass--driverclass-------------------------|

Notes:
  1. You can specify the options in any order.
  2. On platforms that support static SQL, executePackage is the only option supported after the -bind packages option is issued.

Description of repository privilege management options

The following list describes the supported ManageRepository options and variables:

-grant
Grant the access specified by the -privilege option to the users specified in the grantees list.
-revoke
Revokes all privileges against the metadata repository tables for the users specified in the grantees list.

Specifying a role is not necessary for the -revoke option.

"grantees(firstID, secondID, ...)"
Keyword specifying the list of affected database users. The grantees clause, the keyword and list of users, must start and end with the double quotation marks ("). The user names must be separated by commas. The following example lists three authorization IDs as grantees.
"grantees(test1, test2, user1)" 
authorization-ID
The database user ID who is being granted or revoked privileges.

PUBLIC can be specified as an ID.

The authorization-ID must be in the same case as the database user ID. For example, if the DB2® user ID is in uppercase characters, the authorization-ID must be entered in uppercase characters.

-privilege tableAccess|executePackage
Specify the type of privilege being granted. The following are the supported privilege values:
tableAccess
Privilege for platforms that support only dynamic SQL, ensures that dynamic applications have access to the underlying metadata repository tables. tableAccess is for dynamic SQL environments where privileges over the repository tables themselves is required.
executePackage
Privilege for platforms the support static SQL where the ManageRepository -bind packages option was issued. Ensures that applications can access the metadata repository with the statically bound SQL statements created by the ManageRepository utility.
-accessLevel MANAGER|APPLICATION
The following are supported values for the -accessLevel option:
APPLICATION
A user ID given the APPLICATION access can read the pureQueryXML data and write to the tables used to store captured SQL data.
MANAGER
The MANAGER access provides full access to all the metadata repository tables. Privileges are granted to the grantees list of user id's.

If an Optim Performance Manager repository is targeted rather than a repository, then only the access level MANAGER is available.

-generateScriptOnly path-to-script-file
Generates an SQL script with the SQL statements to perform the ManageRepository operation without performing the operation.

After reviewing and modifying the script, a database administrator can create the repository by running the script. After creating the repository, the -verify repository option can be used to check the repository.

For the -bind packages option, the collection name and package names are chosen by the utility and are not modifiable by the user.

-help
Displays summary usage information.

Description of connection information options

The following list describes the information required to connect to a repository database:

-repositoryURL URL-to-repository-database
URL to the repository database
-repositoryUsername user-ID -repositoryPassword password
ID and password used to connect to the database
-repositoryDriverClass driverclass
Fully-qualified class name of JDBC driver class to use to connect to the repository database.

Extracting, exporting, and importing operations

With incremental capture set, the pureQuery client optimizer continues to capture new SQL statements and record SQL activities so obsolete SQL statements can be removed. Depending on the setting in the pureQuery runtime properties file, the captured SQL data can be stored in the same repository where the pureQueryXML is stored or on a different repository. Once captured, the captured SQL data can be merged with the pureQueryXML so a new pureQueryXML can be uploaded. The steps include:
  1. Extract the incremental data.
  2. Use the Merge utility to combine the captured SQL files with the pureQueryXML file.
  3. Use the Configure utility to configure the updated pureQueryXML file.
  4. Optionally, use the StaticBind utility to create packages of SQL statements in a database from the statement sets in the pureQueryXML file, and bind the packages in the database.
  5. Update the runtime group with new data.

Syntax for exporting, extracting, and importing data operations

Read syntax diagramSkip visual syntax diagram
    (1)                                             
>>-------java--com.ibm.pdq.tools.ManageRepository--------------->

>--+- -export data-- -file--path-to-zip-file-name-+------------->
   +- -extract runtimeGroup--| pureQuery data |---+   
   '- -import data-- -file--path-to-zip-file-name-'   

>-- -runtimeGroupId--group-ID----------------------------------->

>-- -runtimeGroupVersion--version-ID---------------------------->

>--| connection information |----------------------------------->

>--+-----------------------------------------------+------------>
   |  (2)                                          |   
   '-------| captured SQL connection information |-'   

>--+--------+--------------------------------------------------><
   '- -help-'   

pureQuery data

    (3)                                         
|-------- -outputDirectory--path-to-directory------------------->

>--+----------------------------------------+------------------->
   '- -pureQueryXml--pureQueryXML-file-name-'   

>--+------------------------------------------+----------------->
   '- -pureQueryProperties--runtime-prop-file-'   

>--+------------------------------------------+----------------->
   '- -optionsFileForConfigure--genProps-file-'   

>--+--------------------------------------+--------------------->
   '- -optionsFileForBind -bindProps-file-'   

>--+------------------------------+-----------------------------|
   '- -incrementalCapture--prefix-'   

connection information

|-- -repositoryURL--repository-database-URL--------------------->

>-- -repositoryUsername--user-ID-- -repositoryPassword--password-->

>-- -repositoryDriverClass--driverclass-------------------------|

captured SQL connection information

|-- -incrementalRepositoryURL--repository-database-URL---------->

>-- -incrementalRepositoryUsername--user-ID-- -incrementalRepositoryPassword--password-->

>-- -incrementalRepositoryDriverClass--driverclass--------------|

Notes:
  1. You can specify the options in any order.
  2. Used only with the -extract runtimeGroup option. Specify the information for extracting the captured SQL data if it resides in a repository separate from the other pureQuery data.
  3. When updating pureQuery data, at least one file must be specified.

Description of options for exporting and extracting data

The following list describes the supported ManageRepository options and variables:

-export data
Export pureQuery data from all versions of a runtime group to a zip file. The zip file can be used as the source when importing data.
-file path-to-zip-file-name
The full path and file name to the zip file that will contain the exported runtime group pureQuery data.
-extract runtimeGroup
Extract all or some of the pureQuery data from the specified runtime group version into one or more files. The type of pureQuery data is extracted from the runtime group version to the specified file.
-import data
Import pureQuery data from zip file created by exporting pureQuery data from a repository.

If the data was exported with the Data Studio export feature, and more than one pureQuery project was selected for export, then only the last projects pureQuery data is imported to the runtime group specified by the -runtimeGroupId option.

-file path-to-zip-file-name
Specify the path and file name to a zip file containing the export data. The file is used to export pureQuery data from a repository runtime group version, or import pureQuery to a repository runtime group version.
-runtimeGroupId GroupID
Specifies the repository runtime group identifier
-runtimeGroupVersion version
Specifies runtime group version identifier
-help
Displays summary usage information.

Description of PureQuery data options

The following list describes the pureQuery data options:

-outputDirectory path-to-directory
Specify the full path to directory for the pureQuery files.
-pureQueryXml pureQueryXML-file-name
Specify the name of the pureQueryXML file.
-pureQueryProperties runtime-prop-file-name
Specify the name of the pureQuery runtime properties file.
-optionsFileForConfigure genProps-file
Specify the file containing the pureQuery properties to generate SQL capture files.
-optionsFileForBind bindProps-file
Specify the file containing the pureQuery properties used to bind SQL statements in the pureQueryXML file.
-incrementalCapture prefix
Specify the file prefix used when exporting sets of captured SQL data to files. The files names containing the extracted captured SQL data have the format prefix_N.pdqxml, where the value N is an integer starting with 1 and incriminated for each file created during the export.
For example, for the option -incrementalCapture "testApp", the following files might be generated
testApp_1.pdqxml
testApp_2.pdqxml

The captured SQL data can be retrieved from a repository that is different than the repository containing the pureQueryXML data. Specify the connection information about the command line for the repository containing the captured SQL database.

Description of connection information options

The following list describes the information required to connect to a repository database:

-repositoryURL repository-database-URL
Specify the URL to the repository database
-repositoryUsername user-ID -repositoryPassword password
Specify the ID and password used to connect to the database
-repositoryDriverClass driverclass
Specify the fully-qualified class name of JDBC driver class to use to connect to the repository database.

Description of connection information options for captured SQL

Used only with the extract runtimeGroup option. When exacting pureQuery data, the repository containing the captured SQL might be in a repository database separate from the other pureQuery data. The following list describes the connection information required to connect to a repository database that contains the captured SQL.

-incrementalRepositoryURL repository-database-URL
Specify the URL to the repository database
-incrementalRepositoryUsername user-ID -incrementalRepositoryPassword password
Specify the ID and password used to connect to the repository database.
-incrementalRepositoryDriverClass driverclass
Specify the fully-qualified class name of JDBC driver class to use to connect to the repository database.

Report listing the differences between two pureQueryXML files

The ManageRepostiory -report pureQueryXMLChanges option creates an HTML report of the differences between two pureQueryXML files.

You can create a report that compares two versions a pureQueryXML file. The report contains summary and detail information of the changes between the two files. The report includes:
  • Information about added, removed, and changed packages including version ID and collection ID, bind status, and the number of SQL statements added, removed, and changed.
  • Information about added, removed, and changed SQL statements in the packages including section number, when the statement was last executed, and the number of times the statement was executed.

The pureQueryXML files that the ManageRepository utility compares must be on your file system. A connection to a database is required.

Syntax for generating a pureQueryXML difference report

Read syntax diagramSkip visual syntax diagram
    (1)                                             
>>-------java--com.ibm.pdq.tools.ManageRepository--------------->

>-- -report pureQueryXMLChanges--------------------------------->

>-- -oldPureQueryXml--pureQueryXML-file------------------------->

>-- -newPureQueryXml--pureQueryXML-file-- -file--report-file---->

>--| connection information |----------------------------------><

connection information

|-- -repositoryURL--URL-to-repository-database------------------>

>-- -repositoryUsername--user-ID-- -repositoryPassword--password-->

>-- -repositoryDriverClass--driverclass-------------------------|

Notes:
  1. You can specify the options in any order.

Description of options to generate a report of differences between two pureQueryXML files

The following list describes the ManageRepository options and variables that are used to generate the report:

-report pureQueryXMLChanges
Generate an HTML report of differences between two pureQueryXML files.
-oldPureQueryXml original-pureQueryXML-file
Specify the name of the original pureQueryXML file.
-newPureQueryXml updated-pureQueryXML-file
Specify the name of the updated pureQueryXML file.
-file file-name
Specify the name of the file that will contain the report.
-help
Displays summary usage information.

Description of connection information options

The following list describes the information required to connect to a repository database:

-repositoryURL repository-database-URL
Specify the URL to the repository database
-repositoryUsername user-ID -repositoryPassword password
Specify the ID and password used to connect to the database
-repositoryDriverClass driverclass
Specify the fully-qualified class name of JDBC driver class to use to connect to the repository database.

Usage notes

Repository support for dynamic and static SQL
SQL statements are used to access and manipulate a repository. These statements can be statically bound to the database for platforms which support static SQL. The statements are issued dynamically for other platforms.

For platforms that support static SQL, specifying the -grant privilege option and -privilege EXECUTEPACKAGE options ensures that applications can access the metadata repository with statically bound statements. For platforms that support static SQL, the ManageRepository utility can use either the bound packages or issue dynamic SQL statements after the -bind packages is issued. If the bound packages do not exist or the user has no execute privilege on the bound packages, ManageRepository uses dynamic SQL to access the repository tables.

For dynamic platforms, specifying -grant privilege and -privilege TABLEACCESS options ensures that dynamic applications have access to the underlying metadata repository tables. This is the only option available for these platforms that do not support static SQL.

ManageRepository support forOptim Performance Manager repository

Optim Performance Manager and Optim Performance Manager Extended Insight can use the pureQueryXML data. The ManageRepository utility can upload pureQueryXML data to an Optim Performance Manager repository.

You can use the ManageRepository utility to upload only pureQueryXML data to an Optim Performance Manager repository. Other data such as pureQuery runtime properties cannot be uploaded.

You can use the ManageRepository utility to create, update, delete, and list runtime groups in an Optim Performance Manager repository. Other ManageRepository commands are not supported.

Managing a repository with an external DB2 security mechanism
When creating a repository, the ManageRepository utility creates packages to manage the repository. The utility creates different packages depending on the type of repository created. If you use an external DB2 security mechanism to manage a repository, you must grant privileges to the packages in their security tool. The following table lists the package name and privileges required for the type of repository:
Table 1. Required privileges for repository packages when managing a repository with an external DB2 security mechanism
Repository type Repository package name Manager level privileges (full) Application level privileges (restricted)
pureQueryRuntimeOnly AMDL X  
pureQueryRuntimeOnly or pureQueryOnly AMDA X X
pureQueryOnly AMD X  
Oracle databases
On Oracle databases, you must create a schema for a repository before creating the repository with the ManageRepository -create repositoryoption. The default schema IBMPDQ. For example, the running following Oracle SQL*Plus command creates a user and schema named IBMPDQ on an Oracle database:
create user IBMPDQ identified by password

When creating a repository, any user with administrative privileges on the database can create a repository.

The following example ManageRepository command assumes that the user tiger has administrative privileges on the Oracle database TEST and the schema IBMPDQ is created on the database:
java com.ibm.pdq.tools.ManageRepository 
  -create repository -repositoryType pureQueryOnly
  -repositoryURL jdbc:db2://test.ora.com:17593/test 
  -repositoryUsername tiger 
  -repositoryPassword testpwd 
  -repositoryDriverClass com.ibm.db2.jcc.DB2Driver
ManageRepository script to create a repository
You can generate a script to create a repository by specifying the -generateScriptOnly as part of the command to create a repository. You can customize some object names while other object names cannot be changed.

If you modify the SQL statements and run the script, you can use the -verify repository option to check the repository.

If you use a customized script to create a repository, you must delete the repository with a customized script. You create a script to delete a repository by specifying the -generateScriptOnly with the command to delete a repository. Before running the script, you change the object names that you changed in the script that you used to create a repository.

You can change names of the following items in the script to create a repository:
  • The database name. The database name is specified in the IN clause of the CREATE TABLESPACE statements.
  • The table space names. The table space names are specified in the IN clause of the CREATE TABLE statements.
  • The storage group ID. The storage group ID is specified in the USING STOGROUP clause of CREATE statements.
  • The buffer pool ID. The buffer pool ID is specified in the BUFFERPOOL clause and INDEXBP clause.
You cannot change the following items in the script to create a repository:
  • The schema name IBMPDQ.
  • The table names, view names, and their schema definitions.
  • The collection name and package names cannot be changed if you specify the -bind packages option.
The following are two methods for running the script:
  • Run the script from an SQL script file with the SQL and XQuery editor in IBM Data Studio. For information about the SQL and XQuery editor see, Working with SQL statements.
  • Run the script from a file from the DB2 command line. From the DB2 command line, you connect to the repository database and run the script. The following example connects to the SAMPLE database and runs the script in the file testcreate.sql.
    db2 connect to sample
    db2 -tvf filename

    For information about the DB2 command line, see Command line processor (CLP) in the DB2 information center.

Sample ManageRepository scripts
Sample command line scripts that demonstrate working with the ManageRepository commands are included in the installation directory of pureQuery Runtime for Linux, UNIX, and Windows.

Examples

The following command creates a connection to the database using the connection information and creates a repository.
java com.ibm.pdq.tools.ManageRepository 
  -create repository -repositoryType pureQueryOnly
  -repositoryURL jdbc:db2://test.user.com:17593/SAMPLE 
  -repositoryUsername mytestid 
  -repositoryPassword testpwd 
  -repositoryDriverClass com.ibm.db2.jcc.DB2Driver
The following command creates a connection to the database and generates an SQL script that can create a repository in the database:
java com.ibm.pdq.tools.ManageRepository 
  -create repository -repositoryType pureQueryOnly
  -repositoryURL jdbc:db2://test.user.com:17593/SAMPLE 
  -repositoryUsername mytestid 
  -repositoryPassword testpwd 
  -repositoryDriverClass com.ibm.db2.jcc.DB2Driver
  -generateScriptOnly D:\test\scripts\createtest.sql
The following command removes a repository:
java com.ibm.pdq.tools.ManageRepository –delete repository
  -repositoryURL jdbc:db2://test.user.com:17593/SAMPLE 
  -repositoryUsername mytestid 
  -repositoryPassword testpwd 
  -repositoryDriverClass com.ibm.db2.jcc.DB2Driver

Feedback