|SQL Error Messages||DB2 External Compiler Module|
The SQL Query tool enables you to do a quick test or EXPLAIN of a SQL query.
The SQL Query tool (see Figure 15-1) appears when you select Tools > HCO > SQL Query from the IDE.
Figure 15-1: SQL Query Tool
The following elements make up the SQL Query window:
The following list shows the buttons in the toolbar and the functions that they invoke:
|Connect to database selected from drop-down list.|
|Open a saved query.|
|Save the query to a text file.|
|Display the on-line HCO User's Guide. The User's Guide is a hypertext file that describes how the SQL Query tool works.|
The menu bar is an alternate way of invoking SQL Query functions. The menu bar is composed of four menus: Database, Query, Options, and Help.
The Database menu is used to connect to a database, disconnect, or to exit the tool. You can also display this menu by right-clicking on the SQL Query window (see Figure 15-1).
|Connect||Connect to database selected from drop-down list.|
|Disconnect||Disconnect from the database you are currently connected to.|
|Exit||Exit SQL Query tool.|
You use the Query menu to copy, paste, explain, open a saved query, run a SQL query, or save a SQL query to a text file.
|Copy||Copy query in MLE to Windows clipboard.|
|Paste||Paste contents of Windows clipboard into MLE.|
|Open||Open a saved query|
|Save as||Save the current SQL query to a text file|
You can override options set in the configuration file by selecting the Options menu. Some of these options are pre-defined. See the chapter Setting INI Options for more details. The settings depend on your current environment and what you have set as defaults.
|Clear output||Clear the SQL Query output window every time a query is run.|
|Display results||Display results after run or explain completes.|
|Limit max rows||If this item is checked, only a maximum of 50 rows will be returned when the query is run. Uncheck this option if you want all rows to be returned when the query is run.|
|Set current SQLID||This option can only be set if you have DB2 Universal Database V5.2 or later installed. Setting this option will allow a different qualifier to be used when running unqualified SQL queries. This option should not be used if you are using the EXPLAIN function as the EXPLAIN tables need to be created with your logon id.|
You can specify the name of the log file that query results will be written to. The default name for the log file is set in the configuration file. See the chapter Setting INI Options for more details. The log file is overwritten if you use the same file name.
You must connect to a database before you can run or explain a query. The SQL Query tool automatically connects to the database specified in the Mainframe Express project unless you have disabled this feature in the configuration file. You can still connect to other databases by doing the following:
To run a query, do the following:
The results are displayed in the HCO Query tab (see Figure 15-2) if you have Display results turned on.
Figure 15-2: HCO Query results
You can also have host variables in the query. The SQL Query tool prompts you for values to substitute for each host variable in the query (see Figure 15-3).
Figure 15-3: Set Value for Host Variable
For character data, you can omit entering quotes around the host variable value unless you want to use a space as the search value
If you are using DB2 Universal Database V5.2 or later, you can use the special register CURRENT SQLID to change the schema name used for unqualified queries. To do this, do the following:
Figure 15-4: Set CURRENT SQLID
You should not use this function if you want to use the EXPLAIN query function since the explain tables are associated with your logon id .
Run Query has the following limitations:
The SQL Query tool also allows you to explain a query to determine if the query is optimized properly. To explain a query, do the following:
The results are displayed in the HCO Log tab (see Figure 15-5) if you have Display results turned on.
Figure 15-5: HCO Explain results
You can also use the Visual Explain tool provided with DB2 Universal Database to look at a graphical representation of the results of the query explained.
Before you can explain a query, you must have explain tables defined. The SQL Query tool determines if these tables have been created. If not, the SQL Query tool creates them for you if desired. Explain table definitions for DB2 V2.1 and DB2 Universal Database V5.2 are included with the Host Compatibility Option software. If you have DB2 V2.1 Explain tables and migrate to DB2 Universal Database V5.2, you will need to update the Explain table definitions. IBM provides a file, EXPLMIG.DDL, to add the new columns used by DB2 Universal Database.
To save a SQL query that you are working on to a text file, do the following:
Figure 15-6: File to Save Query to
The default drive/folder the SQL Query tool uses to save the SQL query to is the Mainframe Express \data subfolder specified in the Mainframe Express project settings. The file extension is set in the configuration file. See the chapter Setting INI Options for more details. If the text file already exists, it is overwritten.
Besides manually typing in a SQL query, you can open a text file that has a SQL query saved in it. To open a saved query, do the following:
Figure 15-7: Select Query
The SQL Query tool copies the contents of the text file into MLE.
The SQL Query tool looks for files first in the Mainframe Express \data subfolder specified in the Mainframe Express project settings. The file extension it uses is set in the configuration file. See the chapter Setting INI Options for more details.
Copyright © 1999 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.
|SQL Error Messages||DB2 External Compiler Module|