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.
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.
(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-------------------------|
The following list describes the supported ManageRepository options and variables.
A message is displayed to indicate that data will be replaced by this command. The user has the option to cancel the action.
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.
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.
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.
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.
Use -force TRUE to confirm the removal without displaying a message.
Use the -generateScriptOnly option to generate an SQL script without deleting the repository.
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.
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.
The following list describes the information required to connect to a repository database:
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.
(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-------------------------|
The following list describes the supported ManageRepository options and variables.
If the runtimeGroupId exists in the repository, an error is displayed.
To override the defaults and to specify other properties, specify the files that contain properties and their values.
Use the -force TRUE option to confirm the removal without displaying a message.
After merging the captured SQL data with pureQueryXML data for a runtime group version, the captured SQL data for the version can be deleted.
When pureQuery Runtime accesses a runtime group in the repository, it uses the pureQuery information in the active runtime group version.
When pureQuery Runtime accesses a runtime group in the repository, it ignores the pureQuery information in runtime groups that are not active.
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.
The following list describes the pureQuery data options:
The following list describes the information required to connect to a repository database:
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.
(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-------------------------|
The following list describes the supported ManageRepository options and variables:
Specifying a role is not necessary for the -revoke option.
"grantees(test1, test2, user1)"
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.
If an Optim Performance Manager repository is targeted rather than a repository, then only the access level MANAGER is available.
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.
The following list describes the information required to connect to a repository database:
(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--------------|
The following list describes the supported ManageRepository options and variables:
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.
The following list describes the pureQuery data options:
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.
The following list describes the information required to connect to a repository database:
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.
The ManageRepostiory -report pureQueryXMLChanges option creates an HTML report of the differences between two pureQueryXML files.
The pureQueryXML files that the ManageRepository utility compares must be on your file system. A connection to a database is required.
(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-------------------------|
The following list describes the ManageRepository options and variables that are used to generate the report:
The following list describes the information required to connect to a repository database:
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.
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.
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 |
create user IBMPDQ identified by password
When creating a repository, any user with administrative privileges on the database can create a repository.
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
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.
db2 connect to sample db2 -tvf filename
For information about the DB2 command line, see Command line processor (CLP) in the DB2 information center.
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.DB2DriverThe 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
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