Exercise 1.2: Creating a connection to the DB2 SAMPLE database and copying it to your project

Before you begin, you must complete Exercise 1.1: Setting up the environment for stored procedure development.

Creating a connection to the SAMPLE database

The workbench provides wizards that make it easy for you to connect to both DB2(R) and non-DB2 databases and to display the status of your connections. The New Database Connection wizard creates a connection to a database that is displayed in the Database Explorer view. Using the wizard, you have the option to connect to a DB2(R) database that has a defined alias. Using the alias option, you do not need to know the platform and version of the DB2 alias. For the purposes of this tutorial you will connect to the SAMPLE database using this alias option.

If you wanted to connect to a DB2 database that was not catalogued, or connect to a database management system other than DB2, you could also use the connection wizard to specify the database manager, platform, and version of your database along with a supported JDBC driver.

The database connection wizard allows you to filter objects that will be retrieved from the database. Filtering database objects improves connection speed for large databases and can also simplify your display. In this exercise you will use the filtering capability to retrieve and display only the EMPLOYEE table in the SAMPLE database.

To create a connection to the SAMPLE database:

  1. In the Database Explorer view, right-click anywhere, and select New Connection. The New Database Connection wizard opens.
  2. In the Connection name field, type DB2SP.
  3. Select the Choose a DB2 alias radio button, and click Next.
  4. In the Alias field, select SAMPLE.
  5. Specify the user ID and password that you want to connect with, then click Next.

    You can specify your operating system user ID or another user ID. In either case, the ID and password must be valid for the SAMPLE database.

  6. In the schema filtering area of the filter page, select Meet all conditions. This option includes objects only if they meet all of the filtering conditions that are enabled (AND logic). The Meet any condition option includes objects if they meet any of the filtering conditions (OR logic).
  7. Keep the default filter (Schema NOT LIKE SYS%). This string filters out all of the system schemas from your connection display. It is usually desirable to keep this filter enabled.
  8. Click Add next to the schema filter area to specify another schema filter.

    The Add Schema Filter window opens. In this window you will create a string that filters out (does not retrieve or display) all objects in the NULLID schema.

  9. In the Predicate field, select NOT LIKE.
  10. In the Filter field, type NULLID, then click OK.
  11. Click Add next to the object filter area to specify a filter for tables in qualifying schemas. Make sure that the Table tab is enabled. You could also specify filters for routines by clicking the Routine tab.
  12. In the Add Object Filter window, select LIKE in the Predicate field.

    This time you will create a string that filters out, all tables in qualifying schemas except the EMPLOYEE table.

  13. In the Filter field, type EMPLOYEE.
  14. Click OK in the Add Object Filter window, then click Finish.

    When prompted to copy the database metadata to a project folder, answer No. You will complete this step in the next exercise.

The connection that you created appears in the Database Explorer view. You can expand the connection object to see the database objects that match your filtering criteria.

Copying the database model to your project

After you create a database connection, you can copy the database model to a project in the Data Definition view and then use the metadata to create supported database objects. In this tutorial, you will copy the database connection that you just created to the simple project that you created in Exercise 1.1.

To copy the database connection to your project:

  1. In the Database Explorer, expand the DB2SP database connection tree.
  2. Right-click the SAMPLE connection, and select Copy to Project. The Copy to Project window opens.

    In this case, you are copying the whole database, but you can also copy from the schema or table level.

  3. Click Browse, and navigate to the SPSimple folder.
  4. Click OK, then click Finish.

The SAMPLE database is displayed in the SPSimple folder in the Data Definition view.

Now you are ready to begin Exercise 1.3: Creating and building an SQL stored procedure.

Terms of use | Feedback
(C) Copyright IBM Corporation 2000, 2005. All Rights Reserved.