In this lesson, you will create and store some application
development artifacts in the data development project that you created
in an earlier lesson. You will create an SQL query and an SQL stored
procedure, and you will briefly explore other capabilities.
You use a data development project in Optim™ Development Studio to create and store
database application development artifacts, such as routines, queries,
and XML files.
First, create an SQL query.
- In the Data Project Explorer, expand the data development
project that you created earlier, right-click the SQL Scripts folder,
and select . The New
SQL Statement wizard opens.
- Type a name for the SQL statement (for example, testSelect),
select to edit the statement using the SQL Query builder, and click Finish. In this tutorial, you use the graphical SQL Query builder to
create the SQL statement, but you can also use the SQL and XQuery
editor to create SQL statements. The SQL and XQuery editor includes
many useful features including syntax highlighting, content assist,
and template support.
- In the SQL Query builder, right-click in the center panel
and select Add Table.
- In the window that opens, browse to the EMPLOYEE table
and click OK.
- Use the tabbed area at the bottom of the SQL Query builder
to add columns and conditions to the query, or type the query in the
editor at the top of the SQL Query builder. The final
query should look like this:
SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE SALARY > :SALARY
- Click to save your query to the project, and close the SQL
Query builder. The SQL query is saved and displayed
in the project in the Data Project Explorer.
Now create a stored procedure:
- Right-click the Stored Procedures folder
in your project, and select . The
New Stored Procedure wizard opens.
- In the wizard, specify a name for the stored procedure
(for example, mySQLPROCEDURE) and in the Language field,
select SQL.
Note: You can also create Java stored procedures, or native
and external SQL stored procedures if you are targeting DB2® for z/OS®.
- Select a template for the stored procedure. More
information about each template, including the DDL, is shown in the Preview area.
You can create and store additional templates to be displayed in this
wizard in the Preferences page.
- Click Finish.
The stored procedure is created and displayed in your project
in the Data Project Explorer. You can now deploy, run, and debug the
stored procedure. You can also follow similar steps to create user-defined
functions.
For more information about using deployment groups
to deploy stored procedures and SQL scripts, refer to the online help.
In addition to routines and
SQL queries, you can also stored XML artifacts in a data development
project. You can use wizards and editors to create and modify XML
files. You can register an XML schema on a DB2 database so that it can be used for decomposition
and validation of XML data inserts. You can use a wizard or the table
data editor to insert XML data into an XML column.
You
can also use a data development project to create and deploy Web services
that expose database operations (SQL SELECT and DML statements, XQuery
expressions, or calls to stored procedures) to client applications.
For detailed information about Web services features, refer to the Web services online help.