Using the DBA Command Manager for DB2 Module › Issue SQL Statements

Issue SQL Statements

Use the DBA Command Manager for DB2 module to enter an SQL statement. You can import SQL statements from a .txt file or .sql file, and export the result set to a .csv file. For reference, you can display the last 100 previously saved command entries.

Note: Enter the SQL statement syntax using capital letters, and enter only one SQL statement at a time.

Input statements are limited to 1,000,000 characters. Multi-line comments are not supported.

Note: Binary data is displayed in hexadecimal form only for single SELECT statements, not for multiple SELECT statements.

Follow these steps:

1. Add the DBA Command Manager for DB2 module to a dashboard.
2. Use the connection toolbar to select the following:
   • DB2 subsystem
   • LPAR
   • SQLID
   • Explain Schema
   • Maximum number of rows to display in the Results tab for single or multiple SELECT statements. The limit is 1000.

   Note: If the selected subsystems do not appear, close and reopen the module.

3. Enter the SQL statement in the text box and click Submit.

   The Messages tab contains the status of the SQL execution. If a single SELECT query executes successfully, the Messages tab is disabled. The Results tab contains the output from running an SQL SELECT statement. The data that is retrieved from a column of type XML, CLOB, BLOB, or DBCLOB is limited to 32 KB per row.

   Note: To export the complete result set to a .csv file, click the Export icon.

Run Stored Procedures

Use the DBA Command Manager for DB2 module to enter a CALL statement to run a stored procedure. You can import a CALL statement from a .txt file and export the result set to a .csv file. When you click the export icon, the stored procedure runs again before the data is exported.

The following data types are supported:

Input parameters can contain the following DB2 functions:

Follow these steps:

1. Add the DBA Command Manager for DB2 module to a dashboard.
2. Use the connection toolbar to select the following:
   • DB2 subsystem
   • LPAR
   • SQLID
   • Explain Schema
   • Maximum number of rows to display in the Results tab for single or multiple SELECT statements. The limit is 1000.

   Note: If the selected subsystems do not appear, close and reopen the module.

3. Enter the CALL statement in the text box to run the stored procedure, and click Submit.

Important! If an OUT parameter of type XML in the stored procedure is set to a value greater than 32704 bytes, the execution ends abruptly and the page keeps on loading indefinitely.

Note: To pass an integer literal to an INOUT parameter of type CHAR, embed the integer literal in quotes.

The Messages tab contains the status of the execution. If the stored procedure runs successfully:

• The INOUT/OUT tab displays the INOUT/OUT parameters returned by the stored procedure.

  Specify only the INPUT and INOUT parameters in the CALL statement. Do not specify the OUT parameters. The length of the input data for XML and CLOB data types is limited to 32 KB.

• The Results tab displays the output in the form of a table or key value pairs. For multiple result sets, only the first result set is displayed in the Results tab.

  The data that is retrieved from a column of type XML, CLOB, BLOB, or DBCLOB is limited to 32 KB.

Execute DB2 Commands

Use the DBA Command Manager for DB2 module to execute DB2 commands. You can import DB2 commands from a .txt file or .sql file, and export the result set to a .csv file. For reference, you can display the last 100 previously saved command entries.

Follow these steps:

1. Add the DBA Command Manager for DB2 module to a dashboard.
2. Use the connection toolbar to select the following:
   • DB2 subsystem
   • LPAR
   • SQLID
   • Explain Schema
   • Maximum number of rows to display in the Results tab for single or multiple SELECT statements. The limit is 1000.

   Note: If the selected subsystems do not appear, close and reopen the module.

3. Enter the DB2 command in the text box, and click Submit.

The DB2 command is executed and the results are displayed under the Messages tab. If the size or the sum of the column length is larger than 4096 bytes, the data is truncated.

Import SQL Statements or DB2 Commands

You can import SQL statements, DB2 commands, or a CALL statement from a .txt file or .sql file for processing.

Follow these steps:

1. Add the DBA Command Manager for DB2 module to a dashboard.
2. Specify system information using the connection toolbar.
3. Click the Import icon.
4. Click Browse to select a file, and click Import.

   The SQL statements, DB2 commands, or the CALL statement from the selected file appear in the input text area.

   Note: The selected file is validated. The file must be a .txt file (UTF-8 or ASCII) or .sql file, less than 100 KB. Binary files are not supported.

5. Click Submit to process.

Note: To export the result set to a .csv file, click the Export icon.

View Command History

You can optionally display the last 100 previously saved command entries in the DBA Command Manager for DB2.

Follow these steps:

1. Add the DBA Command Manager for DB2 module to a dashboard.
2. Specify system information using the connection toolbar.
3. Click the Recent Commands History icon.

   The last 100 previously saved command entries are displayed by date and time with a description.

4. Select the active description link to populate the Input field with the selected history item.

   Initially, the first 50 characters of the command are displayed.

5. Click the active link to display the entire command in the input area.

Note: You can remove commands from the history using the check boxes next to the commands and clicking the Delete icon. To delete all the commands, select the check box next to Commands History and click the Delete icon. This feature is useful when incomplete or wrong commands entered earlier are saved and displayed in the history.