Working with Data in WebSphere

WebSphere V5.0 : Data

In this tutorial you will learn about WebSphere V5.0 : Data - Create connection and load catalog into a project, Manage database definitions and connections, Connecting to databases, To create a new connection to an existing database and Importing data definitions.

Create connection and load catalog into a project

Manage database definitions and connections

With the DB Servers view, the Data Definitions view, and the DB Output view, you can manage the database definitions and connections that you need for your application development. You can connect to databases and import database definitions, or you can define new databases, schemas, tables, and views.

DB Servers view - With the DB Servers view, you can connect to existing databases and view their designs and existing objects. You can browse and import the designs to the Data Definition view, where you can extend or modify the designs. You can also run stored procedures and UDFs and view the results in the DB Output view.

Data Definition view - With the Data Definition view, you can work locally and directly with relational data objects. The Data Definition view can hold local copies of existing data definitions imported from the DB Servers view, designs created by running DDL scripts, or new designs that you have created directly in the workbench. You can also export data definitions to another database that is installed either locally or remotely. One benefit of the Data Definition view is that you can work on database objects and definitions even when you are not connected to your database server.

DB Output view - With the DB Output view, you can see the messages, parameters, and results related to the database objects that you work with. The DB Output view includes an actions list on the left side of the view and several tabbed pages on the right side:

• A Messages page for viewing messages, including the progress of actions
  .
• A Parameters page for viewing the parameters involved with a certain routine or object
  .
• A Results page for viewing output such as the result sets of SQL statements that are executed by a routine
  
  

Connecting to databases

You can browse an existing data definition by connecting to it in the DB Servers view. Once you have connected to the database, you can also import it into a workbench project.

Database connections created using the New Database Connection wizard are only to be used with the WebSphere Application Server tools that are packaged with this WebSphere Studio product.

Limitation: The New Database Connection wizard does not create connections to be used at run time.

To create a new connection to an existing database:

1. Access the DB Servers view via the Data perspective by clicking Window > Open Perspective > Data.
   .
2. When the perspective opens you will see the DB Servers view. Anywhere in the empty area of the DB Server view, click New Connection to open the New Database Connection wizard.
   .
3. Depending on the database vendor type that you are connecting to, you may be required to provide details of the connection, such as the name of the JDBC connection, the name of the database, the database user ID and password, the database vendor type, the host name, the port number, the name of the JDBC driver, the JDBC driver class, the class location, and the URL to the connection.

4. At this time you may want to create your own JDBC connection filters to exclude one or more tables from relational view. Important: Connections to a remote server for all database vendor types may take a considerable amount of time. It is strongly recommended that you configure appropriate connection filters to decrease the load time. By default, the Exclude system schemas check box is selected thus excluding the system schemas that come with the database.

NOTE: Once you have created the connection, you cannot change these filters.

5. Click Finish. The database meta data is collected and used to create a transient model. This model can be viewed in the DB Servers view.

The design of the database that you selected now appears in the DB Servers view. You can expand the folders under the database to browse its design or from a database connection's pop-up menu you can select one of the following:

1. New Connection... - to create a new database connection
   .
2. Import to Folder... - to copy the data definition into a workbench project in the Data Definition view
   .
3. Refresh - to refresh the catalog metadata if the connection is defined and connected
   .
4. Reconnect - to reconnect to the database if the connection is defined but not connected
   .
5. Disconnect - to disconnect from the database if the connection is connected
   .
6. Delete - to delete an already defined database connection

Note that even though the Aliases, the Indexes, the Triggers, and the Structured Types folders are created under the Tables folder, these capabilities are not implemented.

NOTE: If you have a connection to a database in WebSphere Studio and that connection is cancelled in some way from outside the workbench (for example, in DB2 using the "force applications all" command), WebSphere Studio cannot detect the fact the connection has been dropped.

Importing data definitions

You can import existing data definitions into a project either from the DB Servers view to the Data Definition view by either using the Import to Folder wizard or by dragging and dropping the data definitions. After importing to another database, you can then view the design and run queries against the database. When you import a table that contains a foreign key, you must import the table with the primary key at the same time. You cannot import tables that contain foreign key references on their own.

To import the design of an existing database using the Import to Folder wizard:

1. Ensure that you are connected to the database.
   .
2. Expand the connection to show the data definitions in the DB Servers view.
   .
3. Select the data definitions that you want to import. Hold down the Shift key to select multiple data definitions.
   .
4. From the pop-up menu of the data definitions (database, schema or table), click Import to Folder... to display the Import window.

5. Select the target for the import. If the project or folder you specify does not exist it will be created for you. You must import into the appropriate level. Databases can be imported into folders, schemas can be imported into databases, and tables can be imported into schemas. You cannot import across database types. For example, you cannot import a table from an Oracle database into a local DB2 design.

6. Click Finish.

7. Switch to the Data Definition view to work with the imported design.

To import using drag and drop:

1. Ensure that you are connected to the database.
   .
2. Expand the connection to show the data definitions in the DB Servers view.
   .
3. Select the data definitions that you want to import. Hold down the Shift key to select multiple data definitions.
   .
4. Drag and then drop the data definitions to an appropriate node in the Data Definition view.

The following database systems are supported:

• Cloudscape
• DB2 Universal Database
• DB2 Universal Database for iSeries
• DB2 Universal Database for OS/390
• Informix Dynamic Server
• InstantDB
• Microsoft SQL Server
• MySQL
• Oracle8i and Oracle9i
• Sybase Adaptive Server Enterprise
• SQL-92 and SQL-99

_________________

Author: Mikalai Zaikin. Please Click Here to visit Authors site for any updates and changes to the study notes.