DB2 Developer Workbench - IBM DB2

DB2 Developer Workbench (DWB) is a comprehensive development environment available for use with DB2 9. It is used to create, edit, debug, test, and deploy DB2 stored procedures and user defined functions, along with SQL, SQLJ, and XQuery APIs.

Available for Windows 32-bit and Linux 32-bit environments, it was formerly packaged with DB2 as the Development Center. This new DB2 Developer Workbench is based on Eclipse and features new XQuery Visual Builder.

Key features of DB2 Developer Workbench
DB2 Developer Workbench provides a universal toolset for DB2 solutions development based on the Eclipse project. In addition to the functionality of the previous Development Center, additional new features have been added. These include:

  • Addition of Developer Workbench information center and tutorials.
  • Ability to migrate existing Development Center projects.
  • Compare routines within a data development project.
  • Ability to deploy routines to different servers on various platforms.
  • Develop Java and SQL stored procedures.
  • Develop SQLJ applications.
  • Stored procedure debugger for SQL or Java stored procedures.
  • XML support:
    • XML functions
    • XML data type
    • XML schema registration
    • XQuery builde

Creating New Project in DB2 Developer Workbench
You can start a new development project in DB2 Developer Workbench using two methods.

  • Select File New, then select from a list of choices. Below shows the resulting window output.
  • Selecting new data development project

    Selecting new data development project

  • You can make these same choices by clicking New Project from the tool menu (first button on the left). Below shows window output from selecting New Project.
  • New Project drop-down menu

    New Project drop-down menu

Creating stored procedure
To create a stored procedure:

  1. Start a new stored procedure project by selecting File New Stored Procedure.
  2. This prompts you for the project name. If new project creation is specified, you can specify the current schema as shown Below

    Data development project for a stored procedure

    Data development project for a stored procedure

  3. In the following window , choose Create a new connection or Use an existing connection. Once your choice is made, click Finish to return to the Specify a Project window.
  4. Upon choosing the newly-created project name, the name of the stored procedure and the language of the procedure need to be specified as shown Below

    Specifying language type for stored procedure

    Specifying language type for stored procedure

  5. Once selections are made, you are prompted for the password to connect to the target database (if the connection does not already exist). The wizard then steps you through the creation of the procedure.

Creating SQLJ
Before invoking the wizard, you need to create the Java project prior to the SQLJ creation using the wizard :

  1. Click File New Other, or click New toolbar. Expand the Java folder and select Java Project. Specify the new project name. Here you can specify other options such as JDK compliance level. Click Finish to complete the creation of the new Java Project as shown Below
  2. Creating a Java project

    Creating a Java project

  3. Using the newly created or the existing Java project (highlight the project), click File New Other, or click New toolbar. In the New window, expand the Data folder, and then expand the SQLJ Applications folder to show the available SQLJ choices. Click SQLJ File, and then click Next.
  4. You can now specify Package name and SQLJ name . The SQLJ name should follow the Java type convention and should start with an uppercase character.
  5. Specifying package and SQLJ name

    Specifying package and SQLJ name

  6. Complete the wizard steps.

Creating a user defined function (UDF)
Before creating UDFs, you need to create the data development project to store the given database object:

  1. Start the new Data Development project by selecting File New Data development. This will prompt you for the project name. If new project creation is specified, you can specify the current schema as shown in below.
  2. In following window , you can choose to Create a new connection or Use an existing connection. Once the choice is made, clicking Finish returns you to the workspace window.

    The data development project now displays in the Data Project Explorer view. Now, you can use the New User Defined Function wizard to create DB2 user defined functions (UDFs) in SQL.

  3. Switch to the Data perspective using Window Open Perspective Other, then select Data. Output of the Select Perspective window is shown in below
  4. Switching to Data perspective

    Switching to Data perspective

  5. In the Data Project Explorer, right-click the User Defined Functions folder in a data development project, and click New User Defined Function as shown in below
  6. Selecting user defined function wizard

    Selecting user defined function wizard

  7. Complete the steps of the wizard.

    By default, creating the user defined function does not register the user defined function on the database. To register the user defined function on the database, you must deploy it.

Creating XQuery using Visual Builder
DB2 9 allows you to write XQuery expressions directly rather than requiring that you embed or wrap XQueries in SQL statements. This is possible because the DB2 9 query engine processes XQueries natively. We cover more details regarding XQuery in future chapters.

With native XML capabilities added to DB2 9, IBM has enhanced development tools for use with DB2 9. One of the key development tools is DB2 Developer Workbench, which utilizes XML Query Language (XQuery) to query the XML data type. You can use Visual Builder to piece together queries, then review the DB2 Developer Workbench generated syntax.

The following walkthrough demonstrates the use of Visual Builder for the XQuery in DB2 Developer Workbench, which queries the name and city of the customer who has the city element defined with the data Toronto:

  1. First launch DB2 Developer Workbench.
  2. Establish a database connection.
  3. In the Workspace panel, enter the workspace directory. The workspace is the Eclipse platform component that holds the development environment. Select the Database Explorer tab in the workspace view . Expand the Connections folder. This should show SAMPLE[DB Alias]. Right-click SAMPLE[DB Alias] then select Reconnect. The User ID and password prompt window appears. Upon successful connect, the status in the Properties should change from <Disconnected Connection>SAMPLE to <Live Connection> SAMPLE [DB2 UDB V9.1].

    XQuery Visual Builder: connecting to database

    XQuery Visual Builder: connecting to database

  4. Start a new data development project:
  5. Use File New Data Development Project. You can make these same choices by clicking the New Project drop down from the tool menu. Name the project. In our example, XQuery1 was used as the project name. Click Next.

    DWB: creating new project

    DWB: creating new project

  6. In the Select Connection panel, you can choose either Create a new connection or Use an existing connection. Since connection has already been established , we select Use an existing connection for our walkthrough. Click Finish.
  7. XQuery Visual Builder: specifying the connection

    XQuery Visual Builder: specifying the connection

  8. Create XML query.
  9. Workbench can discover and generate the XML schema, but you can also add XSDs, DTD, and schema to the XML Schema Documents folder (This folder is located under the recently created project in the Data Project Explorer pane).

    Right-click XML Queries and choose New XML Query. Name the query. Here we used Sample_XML_Query. Once you click Next, the Add representative XML documents window appears. Click the Add button on the right. On the Specify document location panel, choose Database. Select Next.

    XQuery Visual Builder: specifying the document location

    XQuery Visual Builder: specifying the document location

  10. The XML column or Schema window displays . Select the schema, which contains the sample database, in this example, the schema is CHUNJ. We use the CUSTOMER table, which contains two XML data type columns (INFO and HISTORY). Choose the INFO column, and click Next.
  11. XQuery Visual Builder: specifying the document location

    XQuery Visual Builder: specifying the document location

  12. The Choose a document window shows you a list of XML documents found in that column as well as their respective sizes. The Schema navigation window can be seen on the left side. Here you can specify the resulting XML file name (Document Name). In our walkthrough scenario, we accept the default. Click Finish.
  13. XQuery Visual Builder: specifying XML file name

    XQuery Visual Builder: specifying XML file name

  14. The Add representative XML documents window displays. Now, we have successfully added data that we will query. Click Next.
  15. XQuery Visual Builder: adding representative XML documents

    XQuery Visual Builder: adding representative XML documents

  16. The Associate documents with XML columns window displays. This shows the association between XML Schema and actual data (shown by the green check mark in the Associated? column. This association is there by default because the XML document was generated from the data in the specified XML column. The association between the XML document and the XML column is required for the query. Click Finish.
  17. XQuery Visual Builder: associating documents with XML columns

    XQuery Visual Builder: associating documents with XML columns

  18. This brings you to the workspace view with the Sample_XML_Query.xqm view.
  19. XQuery Visual Builder: XQM tab

    XQuery Visual Builder: XQM tab

  20. The Sample_XML_Query.xqm view has a navigation node tree with Constructors, Expressions, Functions, and Variables syntax. Now drag the name node from the navigation tree over to the XQuery designer grid (right side of the XQM section). This node now serves as a representative node, which means that the query is performed for all XML data that has the name element.
  21. XQuery Visual Builder: adding element to XQuery

    XQuery Visual Builder: adding element to XQuery

    The Step Into icon appears when name is highlighted. Use this to specify conditions and result sets for that node. Click the Step Into icon to step into the For Logic (FLWOR) window. FLWOR stands for FOR, LET, WHERE, ORDER BY, and RETURN, which represent basic XQuery syntax. We limit the query to cities with the name Toronto. So we expand theFunctions, String functions, choose fn:matches and drag it to the Operand1 column of the Where section. This populates the Operand 1 column in Where with matches(…) and the Step Into icon appears next to matches(…).Click the Step into icon.

    XQuery Visual Builder: For Logic (FLWOR) window

    XQuery Visual Builder: For Logic (FLWOR) window

  22. The matches window appears with arguments: source-string, pattern, and flags. Drag the city node under addr from the explorer window on the left to the source-string window. Enter Toronto for the pattern argument and i for case insensitivity for the flags argument. Now, click the Step out icon on the top right corner of the matches window and return to the For Logic (FLWOR) window.
  23. The fn:matches function does not take a second operand (Operand 2), so we leave that column empty (as well as Operator).

    XQuery Visual Builder: matches window

    XQuery Visual Builder: For Logic (FLWOR) window

  24. In addition to the customer name of the customer who resides in Toronto, we want to display the city in the resulting output to verify that resulting names are those from Toronto.
  25. To do this, drag the city node from the navigation pane on the left to the Return section of the For Logic (FLWOR) window.

    XQuery Visual Builder: For Logic (FLWOR) window with operand 1

    XQuery Visual Builder: For Logic (FLWOR) window with operand 1

  26. There are two tabs, Design and source, representing two views in XQM window. We have been exposed to the Design view up to now. You can view the source generated by DB2 Developer Workbench by choosing the Source tab.

    XQuery Visual Builder: source tab

    XQuery Visual Builder: source tab

    You can execute the following source code that DB2 Developer Workbench generated in Command Editor with minor modification.

Generated source code

You can execute the same query from the script file.

Query script

It is the syntax for the XMLQuery. We can modify the source for XQuery

Modified source

Save the source , then issue the following command:

db2 -td@ -vf xquery.db2
  • Now, execute XQuery using XQuery Run. Below shows the successful execution of XQuery (see Data Output tab).

XQuery Visual Builder: Data Output tab

XQuery Visual Builder: Data Output tab

  • You can click the button in the Results tab to see the results output.

XQuery Visual Builder: XQuery results

XQuery Visual Builder: XQuery results

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM DB2 Topics