workbench query command

You can run published queries and your own queries from the command line by using the istool workbench query command. You can view the results in a file that is in a comma-separated value (CSV) or a text format.

Purpose

Use this command to run queries or schedule queries to run. In the query command, the query name is searched for first in the published queries and then in the user queries. You cannot create, edit, or delete queries from the command line. By default, queries are run on assets in the catalog, but you can also run queries on assets in the development glossary.

Prerequisites

You must have any Information Governance Catalog role except the Information Governance Catalog Basic User role.

The version of istool command framework must be the same version as IBM® InfoSphere® Information Governance Catalog.

You must have Write and Create access to the directory and file for the query results.

You must allocate more memory to the istool process. For details, see this technote.

Run the command from the client tier, or from the engine tier if InfoSphere Information Governance Catalog was installed there.

The preferred way to run this command is in the istool command-line window. If you run this command in your operating system command-line window or in scripts, you must add istool to the command name.

Command syntax

Optional parameters and optional values in the syntax are enclosed in brackets [ ].

workbench query
authentication parameters
[generic parameters] 
-queryname query_name
-filename query_results_file
[-devglossary]
[-format file_format]
[-includeAssetID]
[-pagesize]

Parameters

authentication parameters: Required. All asset interchange commands use authentication parameters to connect to a specific installation of InfoSphere Information Server.
generic parameters: The generic parameters are available to all asset interchange commands. Use the generic parameters to request help on command syntax, to specify silent or verbose operation, and to use a script to run commands.
-queryname | -q query_name: Specifies the name of the query to run.; The name is case-sensitive. If the name has an embedded space, enclose the name in double quotation marks ("). The matching query name is searched for in the list of published queries. If the query name is not found, the list of private user queries is searched.
-filename | -f query_results_file: Specifies the directory path and name of the file with the query results. A file extension is given, based on the value of the -format parameter.; If the directory does not exist, the command fails.; If the file does not exist, the file is created. If the file does exist, the file is overwritten with the current query results. If the file cannot be overwritten, the query command stops.
-devglossary | -devgl: Specifies that the query is run on assets in the development glossary.
-format | -fm file_format: Specifies whether file_format is in a comma-separated value (CSV) format, a CSV file in UTF8 format (CSV_UTF8), or a Microsoft Office Excel spreadsheet (XLS) format.; By default, the format is CSV.
-includeAssetID | -id: Includes the identification of the asset in the metadata repository to the query results.
-pagesize | -ps: When you execute a query, the query of the metadata repository can be performed in batches, with the query results stored in memory. Pagesize is the number of query result objects that are brought into memory in each batch. The larger the pagesize number, the quicker the query results are downloaded. However, the larger the pagesize number, the larger the amount of memory that is needed for the query process.; The default pagesize is 50 objects per page of memory. This amount ensures that the query does not run out of available Java VM memory, but downloading large query results might be slow. If you increase the pagesize amount to make the query faster, you increase the risk of running out of memory. This amount ensures that the download of query results does not run out of available Java VM memory, but downloading large query results might be slow. If you increase the pagesize amount to make the download of query results faster, you increase the risk of running out of memory.; For example, with 4 GB of memory for the CLI process, set the pagesize to 50000. If it runs out of memory, decrease the pagesize by a factor of 10, to 5000, and then retry the query.; In addition, you might increase the pagesize and the VM max heap setting (-Xmx), as described in the technote.

Output

A return value of 0 indicates successful completion. Any other value indicates failure. The reason for the failure is displayed in a screen message.

For more information, see the system log file in either of the following directories:

For Microsoft Windows operating system environment: C:\Documents and Settings\username\istool_workspace\.metadata\.log; where username is the name of the operating system account of the user who runs this command.
For UNIX or Linux® operating system environment: user_home/username/istool_workspace/.metadata/.log; where user_home is the root directory of all user accounts, and username is the name of the operating system account of the user who runs this command.

Example

This command runs the query My_DB_query and sends the query results to the file C:\IBM\InformationServer\queries\DB.csv:

workbench query 
-q "My_DB_query"
-dom mysys
-f C:\IBM\InformationServer\queries\DB.csv
-u myid -p mypassword

The output of the istool command might be:

Initializing query engine....
Executing query results 'My_DB_query' to file 
C:\IBM\InformationServer\queries\DB.csv
Execution of query 'My_DB_query' complete