Creating and running SQL scripts

Use the SQL editor to create scripts, run and tune SQL statements, and to save scripts to a local or remote location.

Overview

The SQL editor is the default editor for files with the .sql file extension. It provides these functions:
  • Run all SQL statements in the editor or selected statements.
  • Tune an SQL statement that is selected in the editor.
  • Format the content of the editor using formatting options that are set in the Preferences.
  • Associate the SQL file with a Db2 connection. For more information about this function, see Associating a source file or script with a Db2 connection.
  • Save the SQL script to a local or remote location by using the File > Save As menu option.

Getting started

  1. Define and connect to a Db2 for z/OS system. For instructions, see Connect to a Db2 location.
  2. If you want to use the SQL tuning tools, connect to the SQL tuning Services server. For instructions, see Connect to an SQL Tuning Services server.
  3. Right-click the connection name and select New SQL Script. The SQL editor opens in the editor area.

Editing SQL

Use these tools for editing SQL:

  • Toggle buttons in the tool bar control Word wrapping toggle button word wrapping, Block selection toggle button block selection, and Show whitespace characters toggle button whitespace characters.
  • To expand or collapse SQL statements, click Expand and Collapse in the editor margin. To preview a collapsed statement, hover over the Expand Expand icon.
  • To comment out the line that has cursor focus, right-click and select Source > Toggle Line Comment.
  • To show information about syntax warnings, hover over the warning icon in the margin:SQL editor syntax warning

Inserting code with content assistance

The SQL editor provides content assistance for built-in functions, built-in stored procedures, and templates that are defined in the Db2 for z/OS > Db2 Templates Preference page.

  1. Click the location where you want to insert the code block. If you know the first few letters of the code block, you can type them.
  2. Press Ctrl+Spacebar. The content assistance window opens with suggestions.
  3. To see a preview of a function or template, click its name. A text box opens showing the content of the template.
  4. Double-click a function or template name to include it in the source file.
  5. To enable content assist proposals for schema, table and column names of the active Db2 for z/OS connection, see Defining Editor Preferences for more information.

Formatting SQL

The SQL editor includes a formatter that you can configure on the SQL Formatter Preferences page. It formats the entire contents of the SQL editor with the indentation and statement spacing options you set on the Preferences page.
  1. To set SQL Formatter preferences, open the Preferences window and navigate to z/OS Solutions > Db2 for z/OS > SQL Formatter.
    You can set these options:

    Indent using tabs instead of spaces: Select this option to indent SQL statement lines with a tab character. Clearing this option enables the Indent width option.

    Indent width: If you prefer to indent using spaces, specify the number of spaces to indent SQL statement lines.

    Lines between statements: Specify the number of blank lines to insert between SQL statements.

  2. Right-click in the SQL editor and select Format SQL. You can also select this menu option from the Db2 menu or press Ctrl+Shift+F (Windows) or macOS function button+Shift+F (macOS).

Running SQL

You can run SQL from the COBOL, PL/I, SQL, or z Systems® LPEX Editor.

  1. Open the editor on a file that contains SQL statements.
  2. In a COBOL or PL/I file: Select the contents of an SQL statement, and then right-click and select Db2 for z/OS > Run Selected SQL (Ctrl+Alt+R or ⌥+⌘+R).
  3. In an SQL file: You can run selected SQL or all SQL in the file:
    • Select one or more SQL statements and right-click and select Db2 for z/OS > Run Selected SQL.
    • Right-click in the editor and select Db2 for z/OS > Run Selected SQL or use the Run Selected SQL Toolbar button.
    Tip: For more information about preferences for SQL content, see Defining SQL content types. To set options for the Run SQL commands, select the Run SQL Options menu item. The context-sensitive helps describe the options available on the Run SQL Options page.

If any SQL statement takes parameters or contains host variables, a window prompting you to enter the parameter or variable value opens. If the SQL statement has been run against the active database recently, the window can be populated with recent values for the Data Type, Null, and Value fields. The query results are displayed in the Execution Status window and the Remote System Details view. For more information about viewing and editing table data in this view, see Viewing and editing table data.

Displaying SQL Results as Plain Text in Console View

The Display result sets as plain text in the Console view preference provides flexibility in how SQL result sets are displayed.
  1. This can be enabled on the Window > Preferences > Db2 for z/OS page by toggling the
    Display result sets as plain text in the Console view checkbox.
  2. When enabled, any future SQL execution displays the result set in a new Console page, instead of the Remote System Details view.
  3. Only one result set will be displayed at a time in the Console.
  4. The console title includes details such as execution date/time, SQL statement, and result set number.
  5. Standard console actions are supported, such as Clear Console, Toggle Word Wrap, and switching between console tabs.
  6. Right clicking the result set in the Console opens additional options:
    1. Show Execution Status: Opens a dialog with runtime status and SQL information.
    2. Result Sets submenu: If multiple result sets are returned (example, from routines), allows switching between them.
Note:
  • The setting affects only future actions; result sets already displayed will remain unchanged.
  • Cells are limited to 120 characters, and long values are truncated with ellipses (…).
  • Line breaks are removed; each cell is flattened into a single line.
Animation: Running an SQL query

Working with Remote System Details view

By default, the contents of result sets returned when running SQL are displayed in the Remote System Details view. This can be changed using the Display result sets as plain text in the Console view preference on the Db2 for z/OS preference page.

The Remote System Details view is used to display the children of remote resources in tabular form. The children of a remote resource can be displayed in this view by selecting their parent in either this view or the Remote Systems view, and using the context menu Show In > Remote System Details.

This view is used by default to show the results of Db2 for z/OS queries and is also useful for displaying the children of Db2 for z/OS Catalog Filters.

Some of the useful features of the Remote System Details view include:
  1. Click a table column header to sort by that column.
  2. Change the display order of columns using drag-and- drop or the Customize Table menu.
  3. Hide columns using the Customize Table menu.
  4. Filter displayed rows using pattern matching via the Subset… menu.
  5. Export the table contents to comma-separated values (.csv) files.
Note: When exporting Character Large Object (CLOB) data to CSV, we recommend using the Surround fields with Quotes option.

Tuning SQL

After you connect to a Db2® SQL Tuning Services server, you can select and tune SQL statements from the COBOL, PL/I, SQL, or z Systems LPEX Editor.

  1. Open the editor on a file that contains SQL statements.
  2. Select an SQL statement, and then right-click and select Db2 for z/OS > Tune Selected SQL (Ctrl+Alt+T or ⌥+⌘+T).
    Tip: To set options for the Tune SQL commands select the Tune SQL Options menu item. The context-sensitive helps contain descriptions of the options on the Tune SQL Options page.
  3. On the Tuning Actions window, select one or more tuning actions, and then click OK.

    The tuning data is displayed in the Remote System Details view.

  4. To see the output of each tuning action, select it and click Open Results.
For more information about the tuning actions, see these topics: