Using nqcmd to Test and Refine the Repository

When your repository is complete, you can run sample queries against it to test that it is created properly.

Correct any problems you find and test again, repeating this process until you are satisfied with the results.

You can use the Oracle BI Server utility nqcmd to run test queries against the repository. The utility connects using an Oracle BI Server ODBC DSN. The Oracle BI Server must be running to use nqcmd.

The nqcmd utility is available on both Windows and UNIX systems.

This utility is intended for sanity testing. For heavier load testing, use Answers or another client. Queries with many thousands of rows will not work with nqcmd.

Although you can use nqcmd to run queries against other ODBC data sources, this section only describes how to use this utility to query the Oracle BI Server.

For example, on Windows:

  1. Launch nqcmd from the following location:

    BI_DOMAIN/bitools/bin

  2. At nqcmd, type the desired options. For example: 
    nqcmd -dmy_dsn -umy_username [-pmy_password] -ssql_input_file -omy_result_file

You can pass a text file with SQL statements to the utility (script mode), or you can enter SQL at the command line (interactive mode). Queries are run against the default subject area, unless the object names used in the query are fully qualified.

The table lists the command-line arguments for nqcmd.

Argument Description

-?

Lists the available command-line arguments.

-ddata_source_name

The ODBC data source name for the Oracle BI Server to which you want to connect.

If you omit this parameter, you are prompted at the command line to enter the DSN.

Tip:

On Windows, you can see the available local ODBC data source names by going to Control Panel > Administrative Tools > Data Sources (ODBC). Click the System DSN tab to see a list of the available DSNs, for example, AnalyticsWeb_coreapplications.

-uuser_name

A valid Oracle Business Intelligence user name.

-ppassword

The corresponding Oracle Business Intelligence user password.

The password argument is optional. If you do not provide a password argument, you are prompted to enter a password when you run the command. To minimize the risk of security breaches, Oracle recommends that you do not provide a password argument either on the command line or in scripts. Note that the password argument is supported for backward compatibility only, and will be removed in a future release. For scripting purposes, you can pass the password through standard input.

-ssql_input_file_name

The name and path of a text file that includes your test SQL queries.

-ooutput_result_file_name

The name and path of a file to which the utility will write the query results. This option is only used with -s.

-Ddelimiter

The delimiter used in the SQL input file, for example, semicolon (;) or colon (:). This option is only used with -s.

-a

Enables asynchronous processing.

This option is typically used with -s, when you are passing a SQL input file with multiple SQL statements.

-z

Enables UTF8 output instead of ACP in the output result file.

You might need to include this option to display international characters in query results.

-utf16

Enables UTF16 instead of ACP for communication between nqcmd and the Oracle BI ODBC driver.

You might need to include this option to display international characters in query results.

-NotForwardCursor

Disables the ODBC forward only cursor.

Including this argument overrides the setting specified in the ODBC DSN.

-v

Displays the version of the nqcmd utility.

-SessionVar session_variable_name=session_variable_value

Includes the specified session variable and sets it to the specified value.

Although -C, -R, -f, -H, -q, and -NoFetch are listed by the utility as available arguments, these options are not typically used.

If you run nqcmd in interactive mode rather than script mode (or in other words, if you do not pass a SQL input file), nqcmd shows a menu of options after you provide the data source name and user credentials. Although many options are shown, you typically only use Q, T, and C against the Oracle BI Server.

Enter Q to type a query at the command line. You must enter the query on a single line, and you cannot use a semicolon as a delimiter. Pressing Enter sends the SQL to the Oracle BI Server.

Enter T to browse presentation tables, or C to browse presentation columns. The utility prompts you for catalog pattern, user pattern, table pattern, and table type pattern before returning results.

For catalog pattern, enter the subject area that contains the tables you want to see. For table pattern, enter the specific table. You can enter percent (%) to see all subject areas or all tables, use % with other characters to replace a set of characters, or use underscore (_) with other characters to replace a single character.

User pattern and table type pattern are not used in queries against the Oracle BI Server, so enter % for these options.

You can also enter D to view a static list of data types supported by the Oracle BI Server.