Chapter 16: SQL Query

The SQL Query tool enables you to do a quick test or EXPLAIN of a SQL query.

16.1 SQL Query Window

The SQL Query tool (see Figure 16-1) appears when you click HCO > SQL Query on the Tools menu.



Figure 16-1: SQL Query Tool

The following elements make up the SQL Query window:

16.2 The Toolbar

The following list shows the buttons in the toolbar and the functions that they invoke:

Button
 Description
Connect to database selected from drop-down list.
Open a saved query.
Save the query to a text file.
Run query.
Explain query
Display the on-line HCO User's Guide. The User's Guide is a hypertext file that describes how the SQL Query tool works.

16.3 The Menu Bar

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.

16.3.1 The Database Menu

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 16-1).

Menu Option
 Function
Connect Connect to database selected from drop-down list.
Disconnect Disconnect from the database you are currently connected to.
Exit Exit SQL Query tool.

16.3.2 The Query Menu

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.

Menu Option
 Function
Copy Copy query in MLE to Windows clipboard.
Paste Paste contents of Windows clipboard into MLE.
Explain Explain query.
Open Open a saved query
Run Run query.
Save as Save the current SQL query to a text file

16.3.3 The Options Menu

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.

Menu Option
 Function
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.

16.4 Log File Entry Field

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.

16.5 Connecting to Database

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:

  1. Select the database that you want to connect to from the drop­down list
  2. Click Connect or select Connect from the Database menu

16.6 Running a Query

To run a query, do the following:

  1. Change any SQL Query defaults via Options menu
  2. Connect to a database
  3. Enter or paste a SQL query that you want to run in the MLE (see Figure 16-1)
  4. Enter the filename to write results to
  5. Click Run or select Run from Query menu (see Figure 16-1)

The results are displayed in the HCO Query tab (see Figure 16-2) if you have Display results turned on.



Figure 16-2: HCO Query results

16.6.1 Host Variables in Query

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 16-3).



Figure 16-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

16.6.2 Setting the Current SQLID

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:

  1. Select Set Current SQLID from Options menu
  2. Enter the schema name with which to qualify queries in the SQLID entry field (see Figure 16-4)
  3. Click Set. A message is displayed if the command is accepted. You can now run the query.



Figure 16-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 .

16.6.3 Limitations

Run Query has the following limitations:

16.7 Explaining a Query

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:

  1. Change any SQL Query defaults via Options menu
  2. Connect to a database
  3. Enter or paste a SQL query that you want to explain in the MLE (see Figure 16-1)
  4. Enter the filename to write results to
  5. Click Explain or select Explain from Query menu (see Figure 16-1)

The results are displayed in the HCO Log tab (see Figure 16-5) if you have Display results turned on.



Figure 16-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.

16.7.1 Explain Tables

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.

16.8 Saving a Query

To save a SQL query that you are working on to a text file, do the following:

  1. Change any SQL Query defaults via Options menu
  2. Connect to a database
  3. Click Save or select Save as from the Query menu (see Figure 16-1)
  4. The File to Save Query To window is displayed (see Figure 16-6)
  5. Enter the filename you want query saved to and then click Save.



Figure 16-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.

16.9 Opening a Query

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:

  1. Change any SQL Query defaults via Options menu
  2. Connect to a database
  3. Click Open or select Open from the Query menu (see Figure 16-1)
  4. The Select Query window is displayed (see Figure 16-7)
  5. Select the filename you want to use and click Open.



Figure 16-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.

16.10 Running a Query from the IDE

If you add SQL query files to your Mainframe Express project, you can select and execute the query directly from the IDE via the Tools, HCO menu.



Figure 16-8: Run Query from IDE

Output from query is displayed in the HCO Query tab (see Figure 16-8).

Copyright © 2001 Micro Focus International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.