DB2 and Rational Introduction

 

+ Search Tips   |   Advanced Search

1. Open RAD...

   Start | All Programs | IBM Rational | IBM Rational Application Developer V6.0 Trial | Rational Application Developer

2. Select...

   Window | Reset Perspective

   ...to reset the default perspective to "J2EE".

3. Set the default vendor type to DB2...

   Window | Preferences | Data | DB2 Universal Database V8.1 | OK

   

4. Set the perspective to Data...

   Window | Open Perspective | Other | Data | OK

   The "Data" icon will be indented on the icon bar.

5. Right-click on the white space anywhere inside the Database Explorer view and select "New Connection" from the pop-up menu.
   
   
   

6. Set the "Connection name:" to "JobAppCon". Select "Choose a DB2 alias", then click "Next".

7. Select "SAMPLE" from the "Alias:" drop-down menu.
   
   
   

8. Select the "Test Connection" button to test the new connection.

9. Select the "Finish" button

10. For...

    "Do you want to copy the database metadata to a project folder?"

    ...click the "Yes" button.

11. In the "Copy to Project" window enter...

    JobApp

    ...in the Folder text box.

12. Select the "Finish" button.

13. For...

    'JobApp' does not exist. Create?

    ...select "Yes"

14. In the Data Definition view, click...

    JobApp (right-click) | New | Database definition

15. In the Database Definition wizard, type "JOBAPPDB" in the "Database name" field.

16. In the "Database vendor type" drop-down menu, select...

    DB2 Universal Database V8.1

    

17. Click "Finish".

18. Expand the "JobApp" project folder. The new database "JOBAPPDB" is displayed under the "JobApp" project folder.
    

19. In the Data Definition view, click...

    JOBAPPDB (right-click) | New | Schema definition

    ...and create a schema called DRIVERSCHEMA

20. Click "Finish".
    

21. In the Data Definition view, click...

    DRIVERSCHEMA | Tables (right-click) | New | Table Definition

22. In the New Table Definition wizard, type "DRIVERS "in the "Table name" text box then click "Next".
    

23. Select...

    Columns (right-click) | Add

    Enter "DRIVERID" in the "Column name" text box. Select "Integer" from the "Column type" drop-down menu. Make DRIVERID a primary key. Select the "Add Another" push button.

24. Using the process in the previous step, add the following columns using the information below:

    

25. Click "Next".

26. In the Primary Key window enter "DRIVERIDKEY" in the "Primary key name" text box.

27. Click "Finish".

28. On the Microsoft Windows desktop menu bar, select...

    Start | All Programs | IBM DB2 | General Administration Tools | Control Center | Databases | Create | Database using Wizard

    

29. In the Control Center Create Database Wizard enter "JOBAPPDB" in the "Database name" text box.

30. Enter "JOBAPPDB" in the "Alias" text box.

31. Click "Next".
    

32. Choose "Next", taking all the defaults in the wizard until you get to the summary page of the "Create Database Wizard". On the summary page click "Finish".

33. On the Control Center menu bar select "Control Center | Exit".

34. In the Data Definition view, select the "JOBAPPDB" database folder.
    

    Right-click on the white space in the Data Definition view, then click "Deploy" on the pop-up menu.

35. In the "Deploy" wizard select "JOBAPPDB, DRIVERSCHEMA, Tables and DRIVERSCHEMA.DRIVERS" as the data objects to deploy. Click "Next".
    

36. On the "Data Export Options" window, select the options "Commit changes only upon success" and "Generate fully qualified names". Click "Next".

    

37. On the "Database Connections" window select "JOBAPPDB" from the "Database" drop-down menu. Enter your database "User ID" and "Password" in the "User ID" and "Password" text boxes. Click "Finish".

    

38. In the Database Explorer view, "JOBAPPDB" appears as a new connection to the database server for the JOBAPPDB database. In the Data Definition view, the "JOBAPPDB" database and objects are represented.

39. In the Data Definition view, expand the "JobApp" project folder, then the "JobApp"database folder until you see the "Statements" folder.

40. Right click the "Statements" folder then click "New -> Insert Statement" on the pop-up menu.

    

41. Type the name "InsertRow" in the "Statement name" text box, then click "OK". The "SQL Builder" view opens on the right side of the workbench.

42. Add the table to execute the INSERT DML statement. Right-click on the white space in the "Tables" pane of the SQL Builder. Click "Add Table" on the pop-up menu.

    

43. Select the table "DRIVERSCHEMA.DRIVERS" from the drop-down menu and then click "OK".

44. In the SQL Builder's "Tables" pane, select the boxes for all the columns.

45. In the middle pane of the SQL Builder, click the "Values" radio button for the "Insert rows from" option.

46. Add values for each of the columns selected.

47. In the Data Definition view, select the "InsertRow" statement listed in the "STATEMENT" folder in the "JOBAPPDB" folder. Right-click on the white space in the view pane. Select "EXECUTE" from the pop-up menu to execute the statement.

48. Double-click on the DB Output view to enlarge the view. For a successful execution of the SQL statement, you should see "Status = Success, Action = Execute and Object Name = InsertRow".

49. In the DB Output view, click on the "Messages" tab. The message "Statement execution successful. No results to display." will follow the InsertRow SQL statement. Double-click on the DB Output view to reduce the view size.

    

50. Turn on SQL statement validation. From the workbench menu bar, click "Windows -> Preferences" to open the Preferences window.

    

51. Expand the "Data" node, then click "SQL Query". Select the "Validate query statements by executing before saving" check box. Click "OK".

    

52. Insert additional rows into the JobApp table by editing the InsertRow statement. Double-click on the "InsertRow" statement in the Data Definition view. Change the column values as shown below:

"Table 2. Table column values for mulitple execution"

ARTIST	TITLE	CATEGORY	ID
Bruce Springsteen	Born to Run	Rock	02
U2	Where the Streets Have No Name	Rock	03
Johnny Cash	Ring of Fire	Country	04
Glen Campbell	Wichita Lineman	Country	05
Etta James	Cry Me a River	Jazz	06
Miles Davis	All Blue	Jazz	07
George Gershwin	Rhapsody in Blue	Classical	08
Ferde Grofe	Grand Canyon Suite	Classical	09

4. Execute each statement and check the DB Output view for successful execution.

5. In the Database Explorer view, expand the JobAppCon connection to show the data definitions for the "JOBAPPDB" database. Select the "JobApp.DRIVERS" table. Right-click the white space in the view, then select "Sample Contents" from the pop-up menu.

   

6. Double-click the DB Output view to expand the view. Click the "Results" tab to display the results.

   

7. Click...

   workbench menu | Window | Preferences

   

8. In the Preferences window, click "Data -> DB2 Stored Procedures -> Build Options -> SQL" from the main menu. The settings should match the figure below.
   

9. Click "Workbench -> Capabilities" in the Preferences window. Expand the "Database Developer" capabilities. Enable all the "Database Development" capabilities. Click "OK".
   

10. In the Data Definition view, select the "Stored Procedures" folder under the JOBAPPDB database folder. Right-click on the white space in the view, then select "NEW -> SQL Stored Procedure".

    

11. In the "New SQL Stored Procedure" wizard, specify "SelectRows" in the "Name" text box and "SelectRowsJobApp" in the "Specific Name (Optional)" text box. Enable "Build". Click "Next".

    

12. Start the "Create a New SQL Statement" wizard by selecting the "SQL Assist... "button below the "Statement details" pane on the right center.

    

13. Choose "SELECT" from the "SQL Statement" drop down menu. Enable ""Be guided through creating an SQL statement". Click "Next".

    

14. On the "Tables" tab in the "Available Tables" pane, expand the JobApp folder to display the Tables. Select "JobApp.DRIVERS" table. Select the "push" button. The "JobApp.DRIVERS" appears in the "Selected Tables" pane.

    

15. On the "Columns" tab in the "Available Columns" pane, expand the JobApp.DRIVERS folder to display the columns. Select "JobApp.DRIVERS" table. Select the "Double Right Arrow" button. All the "JobApp.DRIVERS" columns appear in the "Selected Columns" pane.

    

16. On the "Conditions" tab, double-click in the first blank space in the "Column" column. From the drop-down menu, select the "DRIVERS.CATEGORY" column. Double-click in the first blank space in the "Operator" column. From the drop-down menu, select the "=" operator. Double-click in the first blank space in the "Value" column. Manually enter the value "'Jazz'". Click "Next".

    

17. Click the "Execute" push button to test the SQL statment.

    

18. Click the "Execute" button on the "Execute SQL Statement" wizard. A two record result set should be returned. Click "Close". Click "Finish ". We are now complete with the SQL Assist Wizard.

    

19. We continue with the SQL Stored Procedure wizard. Edit the SQL statement in the "Statement Details" pane. Replace the condition value "'Jazz'" with the variable "parm1". Click "Next".

    

20. Click "OK" on the message box stating "The column: 'parm1' cannot be found". Shortly we will define an input variable for parm1. Click "Next".

    

21. Click the "Add" button on the "Parameters" section.

    

22. Select the Parameter Mode "In" radio button. Enter "parm1" in the Name text box. Select the SQL type "VARCHAR" from the drop-down menu. Set the Length text box to "8". Click "OK".

    

23. The new parameter is listed. Click "Next". Click "Next" .

    

24. The stored procedure settings we specified are summarized. Click "Finish".

    

25. The procedure "SelectRows" is built. "SelectRows" appears under the Stored Procedures folder in the Data Defintion view. The "CREATE PROCEDURE" statement appears in the editor. The DBOutput view shows "Status = Success, Action = Build and Object Name = SelectRows". The Messages view shows "Build successful.".

    

26. In the Data Definition view, right-click the "SelectRows" stored procedure. Click "Run". The stored procedure runs on the database server.

    

27. In the Run Settings window, double-click the blank column under "Value". Enter "'Jazz'". Click "OK".

    

28. View the results that correspond to the run action in the DB Output view. You can view messages, parameters, and result sets.

    

29. Click "Window -> Preferences" from the main menu.

    

30. Click "Run/Debug -> SQL Stored Procedure Debug" to access the preferences that you can set for debugging procedures. We will not change the default settings. Click "OK". Note: If you change the stored procedure debug settings specified in this dialog box, the changes do not take effect until the start of the next session.

    

31. On the workbench menu bar, select "Window -> Open Perspective -> Other".

    

32. Select "Debug" then click the "OK" button. You will now be in the Debug perspective. Note: The Debug perspective push button appears in the upper-right corner next to the Data perspective push button.

    

33. We need to create a launch configuration that will load a procedure for debugging. In the Debug perspective, select the down arrow next to the "Debug" icon to open the "Debug" drop-down menu. Select "Debug". This will open the "Debug" launch configurations dialog box.

    

34. In the "Debug" launch configurations dialog box, select the "Stored Procedure Debugger" node. Right-click the "Stored Procedure Debugger" node, then select "New" from the pop-up menu.

    

35. In the "Debug - Create, manage and run configurations" window make the following selections.

    In the "Name" field, enter "JOBAPPDB.SelectRows".

    Select the "Main" tab to bring it to the foreground.

    Specify the URL "jdbc:db2:JOBAPPDB" of the JOBAPPDB database in the "Location URL" field.

    Use your DB2 userid and password.

    To specify the procedure that you wish to debug, click "Browse" to invoke the procedure selection dialog box. In the procedure selection dialog box, choose the "JobApp.SELECTROWS" procedure that you want to debug and click "OK" to return to the launch configuration page.

    After you select the procedure, the "Procedure" field fills in with the procedure name. Completion of this field is mandatory. Arguments that you want to pass to the procedure for the debug session are specified in the argument list. Initally the argument list defaults to values appropriate for the argument type. We wish to pass arguments.

    Click the "Argument list" field, then the "Edit" button to invoke the argument selection dialog box. The argument selection box dialog box lists, in a table, the arguments that are available for the select procedure.

    Edit the valule in in the input parameter field "parm1" with the value "'Jazz'".

    Click "OK" to return to the launch configuration page.

    Click "Apply "to save the "JOBAPPDB.SelectRows" launch configuration.

    Click "Close" to exit the the debug configuration window.

    

36. Switch to the "Data" perspective by selecting the "Data Definition" push button in the upper-right corner. In the Data Definition view, select the "SelectRows" stored procedure in the "JOBAPPDB" stored procedures folder. Right-click and then select "Build for Debug".

    

37. In the Data Definition view, select the "SelectRows" stored procedure in the "JOBAPPDB" stored procedures folder. Right-click and then select "Debug ". In the Debug window, select the "Debug" button. Select "Yes" in the message window.

    

38. In the Debug view, the Variables view overlays the Breakpoint view. We want to see both when we step through through the program. Single-click the "Variables" tab on the Variables view, then drag the view over the Debug view. When you get a grey outline of a square with a right black arrow, release the cursor. The Variables view will now sit between the Debug view and the Breakpoint view. Adjust the view sizes so you can see the contents in all views.

    

39. In the "SelectRows" source code view, double-click the "SelectRows" tab to expand the view. Double-click on the grey column next to the "DECLARE cursor1" code line. A grey dot representing a breakpoint appears in the grey column. Create a breakpoint on the "OPEN cursor1" line. Double-click the "SelectRows" view tab to reset the views.

    

40. Reset the "Breakpoint" view to see the contents of each breakpoints, including the line number. Also note the variables listed in the "Variables" view.

    

41. In the Debug view, click the yellow down arrow representing the "Step Into" button. You are taken to your first breakpoint set in the code at the "DECLARE cursor1" code line. Notice the changes in the Debug, Variable, and Breakpoint views.

    
    

    g

    

42. Again in the Debug view, click the yellow down arrow representing the "Step Into" button. You are taken to the second breakpoint set in the code at the "OPEN cursor1" code line. Notice the changes in the Debug, Variable, and Breakpoint views.

    
    g

    

43. In the Debug view, click the green right arrow representing the "Resume" button. The procedure runs to completion and is terminated. Check the DB Output view for Success status. Check the Results view for the result set.

    
    g

    

 

Resources

"Learn"

• The Rational training site and DB2 training sites list several learning delivery methods for the use of DB2 and Rational.
  
  

• Visit the developerWorks Rational zone and the developerWorks DB2 zone to expand your skills.

  
  

• Stay current with Rational events and DB2 events via developerWorks technical events and Webcasts.

 

 

Get products and technologies

• Download a free trial version of DB2 Universal Database Enterprise Server Edition V8.1. Download a free trial version of Rational Application Developer for Websphere Software V6.0.

• Build your next development project with Rational trial software and DB2 trial software, available for download directly from developerWorks.