Creating scripts more efficiently in the SQL and XQuery editor

Taking advantage of the new features in the editor

The SQL and XQuery editor has been enhanced with new features that can help you develop your SQL scripts more efficiently. Features such as the Command pane and the editor toolbar put the controls, options, and buttons that you need just a mouse-click away. This article describes these new features and how to use them effectively.

Share:

Hardik Patel (hardikpa@us.ibm.com), Software Engineer, IBM

Hardik Patel photoHardik Patel is a Software Engineer at the IBM lab in Lenexa, Kansas. He has worked on the Optim Development Studio and the IBM Migration Toolkit (MTK) teams. Currently, he is responsible for the SQL and XQuery editor component of the Optim products.



Mark Kitanga (mkitang@us.ibm.com), Information Developer, IBM

Mark KitangaMark Kitanga is a supplemental co-op at the IBM Silicon Valley Laboratory in San Jose, California. Mark develops information for IBM Data Studio on the User Technology team. He is currently pursuing a Master's degree in Rhetoric and Professional Communication from New Mexico State University.



08 December 2011 (First published 04 November 2010)

Also available in Chinese Vietnamese

Introduction

The SQL and XQuery editor helps you create and run SQL scripts that contain SQL and XQuery statements. Editor features include syntax highlighting, SQL formatting, content assist, statement parsing and validation, and semantic validation.

You can validate the syntax in scripts with multiple database parsers, and run scripts serially against multiple database connections. In addition, you can export SQL scripts from and import SQL scripts to the editor. Through the editor, you can also schedule scripts for execution and access IBM tools that help you analyze and tune the performance of your SQL queries.

This article describes the effective use of the new features to help you develop your SQL scripts more efficiently. The features in the editor are available for all the data servers that are supported in the workbench, except for any that are specifically noted as not supported.

The SQL and XQuery editor is available in the following IBM Eclipse-based products:

The Resources section contains links to topics in the Integrated Data Management Information Center that describe each of these products.

Editor's note: This article is an update of one originally published in November, 2010 by Hardik Patel and Chat Chatterton.


Creating an SQL script

You can create an SQL script from different places in the workbench. For this article, they are created from the Data Source Explorer view. For other approaches to creating a script, see SQL and XQuery statement creation in the SQL and XQuery editor in the Resources section.

In this article, the script is created in the SAMPLE database on a DB2 for Linux, UNIX, and Windows data connection. If you want to walk through the processes that are described in this article, you can use your own database or the SAMPLE database. Your organization might provide the SAMPLE database on a server that you can connect to. If you want to generate a local copy of the SAMPLE database on your system, see the Creating the sample database link in the Resources section.

After connecting to a database in the Data Source Explorer view, you are ready to create an SQL script. Click the database connection, and then click the New SQL Script button on the toolbar, as shown in Figure 1.

Figure 1. New SQL script toolbar button in the Data Source Explorer view
A screen capture of the Data Source Explorer view that shows the New SQL Script button circled on the toolbar.

An empty script opens in the SQL and XQuery editor for the selected connection. The connection information is shown in the toolbar and on the Configuration page of the Command pane, as shown in Figure 2. The Command pane is a tabbed window that you can show or hide while working in the editor that controls the configuration, validation, special registers, and performance metrics for your scripts.

Figure 2. Database connection shown in the editor toolbar and Configuration page
A screen capture of the toolbar and Command pane in the SQL and XQuery editor.

You can show or hide the database connection information in the editor toolbar by clicking the control arrow next to the connection, as shown in Figure 3.

Figure 3. Database connection information shown in the editor toolbar
A screen capture of the database connection information in the SQL and XQuery editor toolbar.

Changing database connections

When you create an SQL script through a connection in the Data Source Explorer view, the script is connected to the associated database. The information in the editor toolbar shows information about the connection and the database.

When you are developing your SQL script, the database that the script is connected to determines the information that is available in content assist, a feature of the editor that can help you develop your SQL statements. The vendor and version of the database determines the syntax validation of your SQL statements. The database connection also might affect the results when you run the SQL script.

You can change the database that the script is connected to by clicking the Select button on the Configuration page. If you have connections to two or more databases in the Data Source Explorer view, then select a different connection profile in the Select Connection Profile wizard, as shown in Figure 4.

Figure 4. Different connection profile selected in the Select Connection Profile wizard
A screen capture of the Select Connection Profile wizard with a different connectinon profile selected.

Alternatively, if you are connected to only one database, you can click New in the Select Connection Profile wizard, and then define a new connection in the New Connection Profile wizard.

For more information about changing database connections, see Changing database connections in the SQL and XQuery editor in the Resources section.

You also can disconnect the script from a database. This is useful, for example, when you want to work offline. To disconnect the script, select No Connection in the drop-down list, as shown in Figure 5.

Figure 5. Disconnecting the script from the database
A screen capture of the Connection page with 'No Connection' selected in the drop-down list.

After you select No Connection, the Command pane is hidden automatically, as shown in Figure 6, but you can restore the pane at any time.

Figure 6. No database connection and hidden command pane
A screen capture of no database connection and the hidden Command pane in the SQL and XQuery editor.

When you want to reconnect to a database, click the No Connection link on the editor toolbar and the Command pane is shown automatically. Select the connection in the drop-down list that you want to connect to.

For more information about disconnecting a script from a database, see Disconnecting scripts from databases in the SQL and XQuery editor in the Resources section.


Developing your script

The Validation page of the Command pane, as shown in Figure 7, contains controls that you can use while you are creating the statements in your SQL script. You can validate either the syntax, the semantics, or both in the statements that you are creating. You can also change the statement terminator for all the statements in the script.

Figure 7. Validation page in the Command pane
A screen capture of the Validation page in the Command pane of the SQL and XQuery editor.

Validating the syntax in SQL and XQuery statements

As you type SQL and XQuery statements in the editor, the syntax in the statements is validated. The statements are parsed to determine whether keywords and their location in the statements are valid.

By default, the Validate statement syntax for current configuration option is selected, which means the selected parser is based on the type of database that the script is connected to. For example, suppose you develop a script that creates a SALES table with an index in the DB2ADMIN schema of the SAMPLE database. In Figure 8, the script shows no syntax errors with the DB2 for Linux, UNIX, and Windows (V9.7) parser selected.

Figure 8. Statements validated with current connection associated with the script
A screen capture of an SQL script in the editor with no syntax errors for the DB2 for Linux, UNIX, and Windows parser.

You may want to validate a script against a different type of database, for example, if you are planning to export the script for use in another type of database. You can select a different parser without changing the database that the script is connected to. When you select the Validate statement syntax option and a different parser in the drop-down list, the statements in the script are validated with the parser that you select. Currently, parsers for the following types of databases are available in the SQL and XQuery editor:

  • DB2 for Linux, UNIX, and Windows (V9.7)
  • DB2 for Linux, UNIX, and Windows (V9.8)
  • DB2 for z/OS (V10)
  • DB2 for z/OS (V9)
  • DB2 for i5/OS
  • Informix Dynamic Server

Note: Version specific syntax checking is only available for DB2 for Linux, UNIX, and Windows and DB2 for z/OS, which means that any DB2 for Linux, UNIX, and Windows version prior to V9.7 will still use V9.7 parser and any version after V9.8 will use V9.8 parser. The same applies to DB2 for z/OS prior to V9 and after V10. DB2 for i5/OS and Informix Dynamic Server will use latest supported parser.

For example, suppose you want to use the script that creates the SALES table with its index in a database on a DB2 for z/OS server. To validate the script for the target database, you can simply change the parser to DB2 for z/OS (V10), which you can do while the script is still connected to the SAMPLE database.

In this case, the ALLOW REVERSE SCANS clause in the CREATE INDEX statement is invalid with the DB2 for z/OS (V10) parser. The editor flags the validation error with red markers in the left and right margins and underlines the invalid syntax with a red squiggly line. As shown in Figure 9, you can see an explanation of a syntax error in a pop-up window by moving your mouse pointer over an error marker in a margin.

Figure 9. Script statements validated with the DB2 for z/OS (V10) parser
A screen capture that shows a syntax error in the script with the DB2 for z/OS parser.

If you prefer, you can stop syntax validation by selecting No validation in the Validate statement syntax list.

If you are working offline (that is, with No Connection selected on the Configuration page), you can still validate the syntax in the SQL and XQuery statements that you are writing. On the Validation page, select the parser for the appropriate database type, as shown in Figure 10. After you validate for one database type, you can proceed to validate statements with the parser for a different database type.

Figure 10. Validating SQL statements offline with no database connection
Editor with 'No Connection' displayed on the toolbar and 'DB2 for LUW' selected as the parser for syntax validation.

For more information about validating SQL and XQuery statements, see Validating SQL and XQuery statements in the Resources section.

Validating the semantics in SQL statements

You can also validate the references to tables, columns, and stored procedures in the database that the script is connected to. Database object references are validated only in SQL DML statements. The state of the Validate database object references option determines whether semantic validation occurs as you type.

Semantic validation is associated only with the database that the script is currently connected to. The parser selected in the Validate statement syntax list has no effect on semantic validation. You can select the check box at any time during script development whether or not you select a parser for syntax validation.

Figure 11 shows a semantic error for a reference to the SAMPLE_SALES1 table, which does not exist in the DB2ADMIN schema of the SAMPLE database. The editor shows the same error indicators for semantic and syntax errors.

Figure 11. Validate database object references error in a DML statement
A screen capture that shows a semantic error in a DML statement in the SQL and XQuery editor.

For more information about validating database object references in SQL statements, see Validating SQL and XQuery statements in the Resources section.

Changing the statement terminator

When you have multiple statements in an SQL script, each one must be separated by a statement terminator. By default, the SQL and XQuery editor uses a semicolon ( ; ). You can change the default statement terminator for all scripts that you create in the editor in (Window > Preferences).

You can use the field on the Validation page of the Command pane to set the statement terminator for a specific script. The statement terminator that you set in an SQL script persists every time that you open the script in the SQL and XQuery editor.

In a given script, you can use only one statement terminator. That is, all the statements in an SQL script must use the same statement terminator. When you set the statement terminator in an SQL script that contains existing statements, the editor does not update the existing statement terminators automatically. Instead, you must manually update all existing statement terminators in the script.

Figure 12 shows an example of the syntax validation error that occurs after you set the statement terminator to an exclamation point ( ! ) and do not update an existing statement terminator. You will get an unexpected token error if you run the script after you stopped syntax validation.

Figure 12. Invalid statement terminator shown after changing the default terminator
A screen capture of the editor with the default semicolon statement terminator flagged with an error.

For more information about statement terminators, see SQL statement terminators in the Resources section.

Content assist in the SQL and XQuery editor

Like many other product features, the SQL and XQuery editor provides content assist to create SQL statements. Similar to the Java editor in Eclipse, content assist can be triggered by pressing the key combination Ctrl+Space.

To create your SQL statement with content assist, start by typing the expression select * from and press Ctrl+Space. This sequence of steps will display the content assist function for selecting database tables. When referencing fully qualified table names, you can take advantage of content assist for multiple steps, one for the schema name, as shown in Label 1 of Figure 13, and another for the table name, as shown in Label 2.

Figure 13. Content assist in the SQL and XQuery editor to reference schemas and tables
A screen capture that shows the content assist for referencing schemas and tables in SQL statements that is available to the editor.

After you add the required table to the FROM clause of the your SQL statement, the content assist functionality can also help you find columns from that table. You can use this capability to help you complete your SQL statement. Figure 14 shows the column COLOR being added to the SELECT clause of the SQL statement with content assist.

Figure 14. Content assist in the SQL and XQuery editor to reference table columns
A screen capture that shows the content assist for referencing table columns in SQL statements that is available to the editor.

Special Registers

With the Special Registers page of the Command pane, you can specify the current schema and path that you want to use to run the SQL or no XQuery statements against.

Note: You must be connected to a DB2 for z/OS or DB2 for Linux, UNIX, and Windows data server to specify these registers.

The Current schema register is used when you deploy and run database objects with your SQL scripts. It resolves unqualified database object references. By default, the database name from the current connection profile on the Configuration page will be displayed if your SQL does not specify a schema. To change it, click on the Select button, then select a different schema from the Select a schema window, as shown in Figure 15.

Figure 15. The Select a schema window opens to change the Current schema
A screen capture that shows how to change the current schema in the SQL and XQuery editor.

The Current path register is used when you deploy and run database objects with your SQL scripts. It resolves unqualified function names, procedure names, data type names, global variable names, and module object names in dynamically prepared SQL statements. You can add schemas to the Current path by clicking the Select button, as shown in Figure 16. Select one or more from the Select schemas window that opens.

Figure 16. The Select schemas window opens to change the Current path
A screen capture that shows how to change the current path in the SQL and XQuery editor.

Running your script

Setting run preferences

To determine if an SQL script you are developing returns the expected results, you need to run it. But first, you may want to modify how the SQL script is run. You can do this with the run preferences on the Configuration page of the Command pane, as shown in Figure 17.

Figure 17. Run preferences
A screen capture that shows how to change the run preferences in the SQL and XQuery editor.
  • Run method
    You can set the execution environment for the SQL and XQuery Editor with this preference. The available execution environments are JDBC and Command Line Processor.

    Note: The Command Line Processor option is only available if your data server is DB2 for Linux, UNIX, and Windows.

  • Refresh explorer view after this script is run
    Select this option to refresh the Data Source Explorer or Administrator Explorer view after you run the script.

  • Open new connection when script is run
    This option creates a new connection to the target database that is used to run your scripts. By default, it is selected.

    Note:The Run method must be set to a JDBC environment if you wish to deselect this option. If you do, the information from the Connection field is used, and the Commit on completion of script and Roll Back on completion of script options are disabled.

  • On success
    These options specify how SQL statements are handled when they run successfully. The availability of each option depends on the Run method you select. More information is available in the tables below.

  • On error
    These options specify how SQL statements are handled when an error occurs. The availability of each option depends on the Run method you select. More information is available in the tables below.

JDBC run method and preferences

The following table details how statements are handled when the statements are run successfully or encounter errors in a JDBC environment:

On SuccessOn ErrorResult
Commit after each statementContinueIf a statement is successful, it is committed to the specified database. If an error occurs, the next statement will run.
Commit after each statementStopIf a statement is successful, it is committed to the specified database. If an error occurs, the script will stop running.
Commit on completion of scriptContinueIf all of the statements in the script are successful, all statements are committed to the specified database. If an error occurs, the next statement will run, and all successful statements are committed to the specified Database.
Commit on completion of scriptStop and CommitIf all of the statements in the script are successful, all statements are committed to the specified database. If an error occurs, the script will stop running, and any statements that were successfully run are committed to the specified Database.
Commit on completion ofStop and Roll BackIf all of the statements in the script are successful, all statements are committed to the specified database. If an error occurs, the script will stop running, and all successful statements are rolled back.
Roll Back on completion of scriptContinueIf all of the statements in the script are successful, all statements will be rolled back. If an error occurs, the next statement in the script will run, and any successful statements are rolled back.
Roll Back on completion of scriptStop and Roll BackIf all of the statements in the script are successful, all statements will be rolled back. If an error occurs, the script will stop running, and all successful statements are rolled back.

Command Line Processing run method and preferences

The following table details how statements are handled when the statements are run successfully or encounter errors in a command line processor environment:

On SuccessOn ErrorResult
Commit after each statementContinueIf a statement is successful, it is committed to the specified database. If an error occurs, the next statement will run.
Commit after each statementStopIf a statement is successful, it is committed to the specified database. If an error occurs, the script will stop running.
User managed commitContinueIf a COMMIT statement is included in the script, the statement is committed at that point. If an error occurs, the next statement will run.
User managed commitStop and CommitIf a COMMIT statement is included in the script, the statement is committed at that point. If an error occurs, the script will stop running, and any statements that have run are committed to the specified database.

SQL Results view

Now that you have explored the Run preferences, you are ready to run the SQL script. Click the Run SQL button ( A screen capture of the 'Run SQL' button on the editor toolbar. ) on the editor toolbar to execute the statements in the SQL script.

The script is run against the database that it is currently connected to. The progress and results of running the script are then displayed in the SQL Results view, as shown in Figures 18 and 19.

Figure 18. SQL Results view after running a script with JDBC run method and preferences
A screen capture of the SQL Results view showing the status and results of running a script.
Figure 19. SQL Results view after running a script with Command Line Processing run method and preferences
A screen capture of the SQL Results view showing the status and results of running a script.

For more information about running SQL statements, see Running SQL statements in the Resources section.


SQL and XQuery editor toolbar buttons

Importing SQL statements from a script

You may want to include SQL statements that are stored in one or more files as part of the script that you are developing. Click the Import button ( A screen capture of the 'Import' button on the editor toolbar. ) on the editor toolbar to import the SQL statements in a text-based file.

The SQL statements in the selected file are copied and pasted at the location of the insertion point in the editor. If you import statements to a script that already contains SQL statements, the imported statements are bracketed with comment lines, as shown in Figure 20. A Script comment line identifies the file that the statements were imported from.

Figure 20. SQL statements imported into an existing script
A screen capture that shows SQL statements imported into an existing script in the editor.

For more information about importing SQL statements to scripts, see Importing SQL statements to scripts in the SQL and XQuery editor in the Resources section.

Exporting SQL statements from a script

As you develop your script, you may want to write SQL statements that you, or your team members, can include in other SQL scripts. Use the Export button ( A screen capture of the 'Export' button on the editor toolbar. ) on the editor toolbar to export all of the SQL statements in the script, or only the statements that you select.

The exported SQL statements are saved in a text-based file that, by default, has the same name as the SQL script, including the (.sql) extension.

For more information about exporting SQL statements from scripts, see Exporting SQL statements from scripts in the SQL and XQuery editor in the Resources section.

Analyzing and tuning your queries

The Visual Explain tool provides a graphic view of your SQL statements so that you can analyze your queries and tune them for better performance.

Click the Open Visual Explain button ( A screen capture of the 'Open Visual Explain' button on the editor toolbar. ) on the editor toolbar to open it. If Visual Explain is not supported for the type of database that you are connected to, a message tells you that it is not available for your data server.

For information on using Visual Explain, see Using Visual Explain for SQL statements in the Resources section.

You also can use InfoSphere Optim Query Tuner to analyze and tune the performance of your queries.

Click the Start Tuning button ( A screen capture of the 'Start Tuning' button on the editor toolbar. ) on the editor toolbar to start InfoSphere Optim Query Tuner. You will not see the Start Tuning button if query tuning functionality is not included in the product that you are using.

For information about query tuning, see Tuning single SQL statements in the Resources section.

Creating and managing jobs

Job Manager is a tool in IBM Data Studio that is used to create and manage schedules for the jobs that you create for your databases.

Click the Job Manager button ( A screen capture of the 'Job Manager' button on the editor toolbar. ) on the editor toolbar to start the Job Manager Editor.

The SQL and XQuery Editor helps to pass certain information to Job Manager, such as statements from the SQL script, the statement terminator, database connection information, and execution type. This information is used by the Job Manager Editor to schedule a job.

For information on using Job Manager, see Creating and managing jobs in the Resources section.

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=563134
ArticleTitle=Creating scripts more efficiently in the SQL and XQuery editor
publish-date=12082011