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
- 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 menu option.
Getting started
- Define and connect to a Db2 for z/OS system. For instructions, see Connect to a Db2 location.
- 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.
- 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, block selection, and whitespace characters.
- To expand or collapse SQL statements, click and in the editor margin. To preview a collapsed statement, hover over the Expand icon.
- To comment out the line that has cursor focus, right-click and select .
- To show information about syntax warnings, hover over the warning icon in the margin:
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
Preference page.- 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.
- Press Ctrl+Spacebar. The content assistance window opens with suggestions.
- To see a preview of a function or template, click its name. A text box opens showing the content of the template.
- Double-click a function or template name to include it in the source file.
Formatting SQL
- To set SQL Formatter preferences, open the Preferences
window and navigate to .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.
- 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 +Shift+F (macOS).
Running SQL
You can run SQL from the COBOL, PL/I, SQL, or z Systems® LPEX Editor.
- Open the editor on a file that contains SQL statements.
- In a COBOL or PL/I file: Select the contents of an SQL statement, and then right-click and select (Ctrl+Alt+R or ⌥+⌘+R).
- 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 .
- Right-click in the editor and select .
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.
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.
- Open the editor on a file that contains SQL statements.
- Select an SQL statement, and then right-click and select
(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.
- 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.
- To see the output of each tuning action, select it and click Open Results.