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.

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 All SQL.
    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. The query results are displayed in the Remote System Details view.

Animation: Running an SQL query

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: