< Previous | Next >

Exploring data application development tasks

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.

  1. In the Data Project Explorer, expand the data development project that you created earlier, right-click the SQL Scripts folder, and select New > SQL or XQuery Script. The New SQL Statement wizard opens.
  2. 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.
  3. In the SQL Query builder, right-click in the center panel and select Add Table.
    Screen capture showing the Add Table menu, as described.
  4. In the window that opens, browse to the EMPLOYEE table and click OK.
  5. 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
  6. Click File > Save 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.
    Screen capture showing the SQL query displayed in the Data Project Explorer, as described.

    Now create a stored procedure:

  7. Right-click the Stored Procedures folder in your project, and select New > Stored Procedure. The New Stored Procedure wizard opens.
  8. 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®.
  9. 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 Routines > Templates Preferences page.
  10. 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.

Tip: For detailed information about creating, deploying, and debugging SQL stored procedures, refer to the tutorial in this product called Create, test, and deploy a DB2 SQL stored procedure.
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.
Tip: For detailed information about XML features, refer to the tutorial called Work with XML data in a DB2 database.

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.

< Previous | Next >

Feedback