Create A New SQL Statement wizard

The Create A New SQL Statement wizard and the SQL Builder both provide a visual interface for creating and executing SQL statements. You can use the wizard to create a simple SQL statement, or you can use the SQL Builder, which supports a wider range of statements. Statements generated by these tools are saved in a file with the extension .sqx.

The following table lists the statements that you can create with the wizard and with the SQL Builder:

Statement	Create A New SQL Statement wizard	SQL Builder
INSERT - inserts a row into a table INSERT from value INSERT from subquery	X	X X
UPDATE - update a row from a table Simple UPDATE by value UPDATE by group	X	X X
DELETE - delete one or more rows from a table	X	X
SELECT - perform a query	X	X
WITH - multiple UDB common table expressions		X
FULLSELECT - create a composite of one or more queries		X

 

Working in the Create A New SQL Statement wizard

When you create a statement using the wizard, you are prompted to specify:

• The kind of statement that you want to create.

• How you want to create that statement (for example, typing it in manually or being guided by the wizard).

• Whether to use an existing database connection or create a new one.

• What database model you want to specify (you can work with one that is already in the workbench or specify a new one).

• The name of your statement.

Selecting how to create the statement

If you select to be guided through creating an SQL statement, the wizard prompts you for various kinds of information needed to create your statement, such as the tables you want to use in your statement and so on. Press F1 in each page (such as the Tables page) for information on how to work on that page.

If you select to create a new SQL resource and invoke the SQL Builder, the wizard closes after you click Finish and the SQL Builder opens, showing the new statement. You can use the SQL Builder to build up your SQL statement. The SQL Builder has more advanced features than the wizard and may be preferable to the wizard, depending on the statement that you want to create. Refer to the related concepts and tasks for more details.

If you select to manually create your statement, you are prompted to type it in, and then parse and execute it.

Select what kind of database model you want to specify

By default, you are prompted to create a new database connection. To use an existing database connection, clear the Create a new database connection check box.

If you select to use an existing database model for your statement, your SQL statement is saved in the same folder as the database model. You must select the database model in order to be able to use it, but no further information is required.

If you select to connect to a database and import a new database model, you are prompted to specify information required to connect to the database you want to work with. You are asked to specify a project or folder for the new database and the statement. For information about what data to specify in a field, position the pointer in that field, and then press F1.

 

What you can do with the SQL statement that you create

When you are finished with the Create A New SQL Statement wizard, you have created a simple SQL statement that you can run. You can also perform the following tasks with the statement:

• Generate an XML file that can be used with the XMLtoSQL class and run dynamically to update tables.

• Generate a DADX file that can be used by the DB2 XML Extender to compose or decompose data.

• Generate a Java bean from a statement that you can use to run the statement.

• Use it in a relational database-to-XML mapping. The RDB-to-XML mapping editor can be used to map the result set from an SQL statement to an XML file. This mapping can be used to generate a DAD file.

You can also open the statement in the SQL Builder and edit it. Refer to the related tasks for more details.

 

Database vendors supported by the wizard

For a list of supported database vendors, see Supported database vendors.

The SQL documentation was written based on the options available when used with the IBM DB2 Universal Database. If you are using a different database product, you may notice some differences in the behavior of the tool or the options that are available. The creation of a WITH statement, for example, is only available in DB2.

 

Parent topic

SELECT, UPDATE, INSERT and DELETE statements. The SQL Builder also supports WITH (DB2 only) and FULLSELECT statements. Any statement that you create is stored in a file with the extension .sqx.">Creating an SQL statement

 

Related concepts

SQL Builder

 

Related tasks

Editing an SQL statement
Opening the SQL Builder
Generating DADX files
Generating XML from SQL using a wizard