Creating tables and data sources automatically to test CMP beans for WAS v6.0.x

The table and data source creator provides an automated way to create the persistent resources used for testing container-managed persistence (CMP) enterprise beans without worrying about the persistence mechanism or how to configure a data source. You can test CMP beans as easily as session beans by allowing the tools to create the data source or database tables, or both for you. The table and data source creator automatically captures the following: userid, password, host name, port, path to the JDBC drivers, whether to drop tables first or not, and whether to create data sources only and not tables or whether to create tables but not data sources.

The following database systems are supported, with restrictions as stated:

Prerequisite tasks:

  1. Create an enterprise application project.

  2. Create a EJB project in the enterprise application project you just created.

  3. Create CMP beans in the EJB projects that correspond to the enterprise application project you just created.

  4. Generate a top-down mapping, selecting one of the following as the target database:

    • Cloudscape 5.1

    • DB2 UDB V8.2

    • DB2 UDB V8.1

    • Oracle 10

    • Oracle 9

    • SQL Server 2000

  5. Verify the current back-end id you want to use is selected and the JNDI name of the connection factory binding is unique by completing the following:

    1. Expand your EJB project in the Project Explorer view of the Java™ EE perspective.

    2. Right-click the

      EJB JAR icon and select

      Open.

    3. The EJB Deployment Descriptor editor opens.

    4. In the Overview page of the EJB Deployment Descriptor editor, scroll down to the

      WebSphere Bindings section.

    5. In the Current list under the Backend ID section, specify the database vendor you want to create the table or data source against by selecting one of the following supported current back-end ids:

      • CLOUDSCAPE_V51

      • DB2UDBNT_V82

      • DB2UDBNT_V8_1

      • ORACLE_V10

      • ORACLE_V9

      • MSSQLSERVER_V2000

      Tip: Although the generating top-down mapping using the EJB to RDB mapping tools allow you to generate any current back-end ids, the table and data source creator only supports the above listed current back-ends. If you attempt to select an unsupported current back-end id, the table and data source creator fails to continue with the following error message: 

      No EJB projects contain schemata for vendors supported automatic table and data creation for this WebSphere version.  
Supported vendors are: Cloudscape v5.1, DB2 Universal Database v8.1, DB2 Universal Database v8.2,
Oracle v9i, Oracle v10g and Microsoft SQL Server 2000.

    6. In the JNDI name field, under the JNDI - CMP Connection Factory Binding section, specify an unique JNDI name. Otherwise, the table and data source creator fails to create a data source with the following error message: 

      A new data source was not added because it would duplicate an existing data source JNDI name.

    7. Save your changes and close the EJB deployment descriptor editor.

To create tables or data source or both for testing your CMP entity beans:

  1. Create a WAS v6.0 server.

  2. In the Servers view, right-click the server and select Add and Remove projects, add the EAR project containing the CMP to the server.

  3. In the Servers view, right-click the server and select

    Create tables and data sources. The table and data source creator wizard opens.

  4. Specify if you want to create tables:

    1. If you are doing a top-down mapping of your CMP entity beans and want to create tables, select the Create database tables for database_vendor check box.

    2. If you selected the Create database tables for database_vendor check box, use the Drop the previously generated database tables check box to specify whether to remove the previously generated database table.

      CAUTION:

      Enabling the "Drop the previously generated database tables" check box will remove any previously created tables and its data stored in these tables.

    3. If you want to use tables that already exists in your database or you are doing a bottom-up mapping and do not want the tables created, clear the

      Drop the previously generated database tables and Create database tables for database_vendor check boxes and continue with creating the data source for your CMP entity bean to connect to these existing tables.

  5. If you want to create data sources complete the following steps:

    1. Select the Create data source for database_vendor check box.

    2. If you are creating a data source for Cloudscape 5.1 , continue to the next step as the host name or port number is not required. For all other supported database vendors continue with this step.

    3. In the

      Database server host name or IP address field, specify the computer machine name that is hosting the database, for example localhost.

    4. In the

      Database server port number field, specify the port number for the database connection. By default, the following port numbers are specified: 50000 for DB2, 1521 for Oracle or 1433 for SQL Server.

    5. (Optional) If you cleared the Create database tables database_vendor check box and only want to create a data source, you have the option of specifying the data source connection information in this wizard by selecting the

      Specify all data source connection information here check box, specify the below details and click Finish:

      Database user ID

      Specify your user ID needed to access the database.

      Database user password

      Specify your password needed to access the database.

      Local path to JDBC drivers

      Specify a complete set of JDBC drivers path for this database on your local file system. If you specify multiple JAR files, the JAR file names must be fully qualified and separated by path separators. The path separator depends on the operating system you are running the workbench. On Windows®, use semi-colons (;) as the path separator. On Linux® platforms, use colons (:) as the path separator. For example:

      c:\Program Files\IBM\SQLLIB\java\db2jcc.jar;c:\Program Files\IBM\SQLLIB\java\db2jcc_license_xxxxx.jar

      c:\Program Files\IBM\SQLLIB\java\db2jcc.jar:c:\Program Files\IBM\SQLLIB\java\db2jcc_license_xxxxx.jar
      Alternatively, you could specify the connection parameters of the data source in the next page of this wizard.

  6. Click Next. The Select Connection wizard opens.

  7. Specify whether to use a new database connection or use an existing connection.

    1. To create a new database connection, select the New button. The new connection profile wizard opens. Use this wizard to specify the database connection parameters.

      Tip: Use the Test Connection button to test the database connection before clicking finish to verify all the settings are correct and a connection to the database is possible.

    2. To use the newly created database connection or an existing connection, under the Connections table select the connection profile. The Properties table displays the connection parameters and values. The existing database connection might be connected or disconnected. If the database connection is connected and you specified to create a table, the table and data source creator makes a connection to the database and creates the table. If the database connection is disconnected and you specified to create a table, a Database Authorization dialog box opens and you are prompted for your user id and password to initiate the database connection. Click Next.

  8. After completing the table and data source creator wizard, click Finish. The Table and Data Source Creations Results dialog box opens which displays a list of SQL operations and any errors when performing the table creation. To copy the text, highlight the text, right-click and select

    Copy. The Details button is available for additional status on the table and data source creation.

  9. (Optional) If you want to verify the creation of the data source, open the Deployment page of the application deployment descriptor editor. You will see the following elements created:

    • A new JDBC provider, if not previously created.

    • A new data source that uses the JDBC provider. The data source specifies the JNDI name defined in the EJB deployment editor, and the database name specified in the mapping.

    • A new Java Authentication and Authorization Service (JAAS) authentication entry specifying the user id and password used to authenticate the database connection. For Cloudscape, a JAAS authentication entry is not created as this is not required.

    • Variable mappings that associate the variables for the JDBC provider path to the actual file paths to the JDBC drivers. For Cloudscape, variable mappings are not created as this is not required.

  10. (Optional) Use the Universal Test Client to test the CMP bean.

If you are performing this task again: