Using common connections with Optim solutions

Creating and using connection configurations with Optim Development Studio, Optim Database Administrator, Optim Query Tuner, and InfoSphere Data Architect

Creating database connections requires users to provide specific information, including the database vendor, version, and URL information. In version 2.1, Data Studio Developer and Administrator introduced a simple and efficient way to manage database connections, allowing users to easily store and retrieve connection parameters for multiple databases. Since then, these products have been renamed to Optim and there have been additional usability improvements. Support for common connections has also been added to InfoSphere Data Architect and Optim Query Tuner. This article describes how to use common connections for greater efficiency in managing database connection information.

[7 Oct 2010: This article has been updated from its original December 2008 publication to include the addition of Optim Query Tuner and other product name changes, as well as additions to product capabilities. --Ed.]

Karen Devlin (kdevlin@us.ibm.com), Software Engineer, IBM

author photoKaren Devlin is a software engineer with IM Tooling Quality Assurance. During her IBM career she has published articles in the IBM Technical Disclosure Bulletin and developerWorks. She also received a patent award for her work on creating xpath expressions in an XQuery Builder.



Salvador Ledezma, Staff Software Engineer, IBM

Salvador Ledezma photoSalvador has been working at IBM since 2002 at the Silicon Valley Lab, in San Jose, CA, initially developing Java-based workloads and applications for DB2 z/OS. Salvador currently spends his time working on runtime and tooling technologies for IBM's data server products using the Eclipse platform.



07 October 2010 (First published 18 December 2008)

Also available in Chinese

Introduction

The Data Source Explorer gives you the capability to create and manage database connections by using the New Connection wizard and property browser. After database connections are configured appropriately, those connections can be shared with others by exporting to connection files that can be imported into the workspaces of other users.

While this feature does provide the capability to share database connections, the files need to be stored in a shared file system. Access control is restricted by file system security limitations. People you want to share with are required to know where to find the connection files and to know which files contain the connection information they need.

This process is improved significantly by using configuration repositories. A configuration repository is a set of database tables containing shared connection information or properties. Using the repository, you can organize connection information into logical groups to meet the needs of different users. A developer or database administrator (DBA) who needs access to shared database connections simply needs to know how to connect to the configuration repository. The underlying database provides the access and security.

This article describes how to set up, connect to, and use a configuration repository to manage database connections in the supported products. As an example, this article will demostrate how to set up a configuration repository organized by platform to support connections used by a quality assurance (QA) team for testing purposes.

Prerequisites

This article assumes that you have a working knowledge of creating, managing, and using database connections in an Eclipse-based product.

All features and actions described below are invoked from the Data Source Explorer (DSE) view. The DSE view is shown by default in the Data perspective. In the latest releases of Optim Development Studio 2.2.1 and Optim Query Tuner 2.2.1, each introduces a new perspective called the IBM SQL and Routine Development perspective and the IBM Query Tuning perspective, respectively. These perspectives also contain the DSE view by default. To switch to any perspective in the Eclipse workspace, go to the top right corner of the workspace and select the icon with the plus (+) sign as shown in Figure 1 Open Perspective

Figure 1. Open Perspective
Screen shot of Optim Development Studio with multiple windows opened and the Open Perspective icon circled in the upper right-hand corner

Alternatively, a perspective can also be opened from the main menu. Go to Window -> Open Perspective -> Other…

Figure 2. Open Perspective from the Window menu
Screen shot of Optim Development Studio with Window menu expanded and the Open Perspective action selected

A dialog will popup providing a list of perspectives available.

Figure 3. Open Perspective Dialog
Screen shot of the Open Perspective dialog box that contains a list of perspective such as Data, Database Development, Java, etc.

System requirements

Key features of common connections

The following capabilities are available since Version 2.2:

  • Common connections are added to InfoSphere Data Architect and Optim Query Tuner (and you can store a connection configuration for any database supported by these products).
  • You can rename connection groups and connection configurations, or you can use copy and paste to modify their locations.
  • You can create multiple database connections from a connection group using copy and paste.
  • You can create connections and connection configurations using copy and paste.
  • You can validate and refresh database connections.
  • A distinct icon enables users to easily identify database connections that were created from connection configurations.

The Common Connection features described in this article were introduced in Data Studio Developer 2.1 and Data Studio Administrator 2.1. and are available in the current Optim solutions portfolio: Optim Development Studio 2.2.1, Optim Database Administrator 2.2.3, Optim Query Tuner 2.2.1, and InfoSphere Data Architect 7.5.3. To try any of these features out, see the Resources section of this article to download a trial copy of any of these products.

Understanding the terminology

This article introduces some terminology to describe the Common Connection features.

Configuration repository—A set of database tables containing connection groups and connection configurations.

Connection configuration—A set of required property values used to create database connections, including the database version, database vendor, port number, and host name.

Connection group—A virtual folder in a configuration repository containing related connection configurations. Connection groups are not required, but they are useful for organizing related connection configurations. This article uses connection groups to demonstrate how a QA team might organize connections by platform in their test environment. A sales team might organize connection groups by geography. A retail team might use connection groups to identify which database servers contain sales information by quarter or by year.


Using a configuration repository

This section describes how to set up, modify, and remove a configuration repository.

Set up a repository

The database administrator sets up configuration repositories. The repository can contain connectivity information for all database platforms or connections that the Data Source Explorer supports. Use any of the following databases to set up your configuration repository:

  • DB2® for Linux®, UNIX®, and Windows®
  • DB2 for z/OS®
  • DB2 for i
  • Informix® Dynamic Server

A database connection to the server where the repository will be created is required. Use the New Connection wizard to create the database connection. From the Data Source Explorer, right-click on the connection, and select the Set up Configuration Repository option, as shown in Figure 4.

Figure 4. Configuration repository setup
The screen capture shows the SAMPLE database selected in the Data Source Explorer with the context menu expanded (right-click action) and Set Up Configuration Repository action selected

The SQL Editor displays the generated DDL for the appropriate platform in an .sql script. You can customize the setup script to specify different tablespaces for the configuration tables based on the need to backup and share the connection configurations. Right-click in the SQL editor, and select Run SQL to set up the configuration repository, as shown in Figure 5.

Figure 5. Run SQL
This screen capture shows the generated DDL in the SQL Editor with the context menu opened and Run SQL action selected

Listing 1 contains the setup.sql script for DB2 for Linux, UNIX, and Windows for creating a configuration repository.

Listing 1. setup.sql for DB2 for Linux, UNIX, and Windows
CREATE SCHEMA IBMPDQ;

CREATE TABLE IBMPDQ.CONNECTION (
DATABASE VARCHAR(255),
NAME VARCHAR(255) NOT NULL,
DB_VENDOR VARCHAR(255),
DB_VERSION VARCHAR(255),
OS VARCHAR(255),
OWNER VARCHAR(255),
VERSION INTEGER
);

CREATE TABLE IBMPDQ.CONNECTION_PROPS (
NAME VARCHAR(255) NOT NULL,
PROFILE_TYPE VARCHAR(255),
PROPERTY_KEY VARCHAR(255),
PROPERTY_VALUE VARCHAR(255),
FLAG VARCHAR(255)
);

CREATE TABLE IBMPDQ.GROUP (
GROUP_PATH VARCHAR(1000) NOT NULL,
NAME VARCHAR(255),
DESCRIPTION VARCHAR(255),
VERSION INTEGER,
OWNER VARCHAR(255)
);

CREATE TABLE IBMPDQ.GROUP_CONNECTION (
GROUP_PATH VARCHAR(1000) NOT NULL,
CONNECTION_NAME VARCHAR(255) NOT NULL
);

ALTER TABLE IBMPDQ.CONNECTION ADD CONSTRAINT CONNECTION_PK PRIMARY KEY (NAME);

ALTER TABLE IBMPDQ.GROUP ADD CONSTRAINT GROUP_PK PRIMARY KEY (GROUP_PATH);


ALTER TABLE IBMPDQ.CONNECTION_PROPS ADD CONSTRAINT CONN_PROPS_FK FOREIGN KEY (NAME)
REFERENCES IBMPDQ.CONNECTION (NAME)
ON DELETE CASCADE;

ALTER TABLE IBMPDQ.GROUP_CONNECTION ADD CONSTRAINT GP_CONNG_FK FOREIGN KEY (GROUP_PATH)
REFERENCES IBMPDQ.GROUP (GROUP_PATH)
ON DELETE CASCADE;

ALTER TABLE IBMPDQ.GROUP_CONNECTION ADD CONSTRAINT 
  GP_CONNC_FK FOREIGN KEY (CONNECTION_NAME)
REFERENCES IBMPDQ.CONNECTION (NAME)
ON DELETE CASCADE;

Connect to the configuration repository

After you set up the configuration repository database, you can populate it with connection groups and connection configurations. Any user that connects to the repository has access to the connection groups and to the connection configurations for creating database connections. A local database connection to the server where the repository is located is required to connect to a configuration repository. Use the New Connection wizard to create the database connection.

In the Data Source Explorer, right-click the Configuration Repository folder, and select Add Repository…, as shown in Figure 6.

Figure 6. New configuration repository
This image shows the configuration repository folder selected in the Data Source Explorer with the Add Repository action selected from the context menu.

Select the appropriate database connection from the list, and click Finish, as shown in Figure 7.

Figure 7. Select a repository connection
The image shows the New Configuration Repository dialog box open displaying connections with the SAMPLE database selected.

The configuration repository is displayed in the Data Source Explorer, as shown in Figure 8.

Figure 8. SAMPLE configuration repository
The image shows the Data Source Explorer after the new repository is added with Configuration Repository SAMPLE underneath the Configuration Repositories folder

Like local database connections, you can connect and disconnect from a configuration repository.

View and modify configuration repository properties

Right-click on the repository in the Data Source Explorer, and select Properties, as shown in Figure 9.

Figure 9. Repository properties
The image shows the Configuration Repository SAMPLE selected in the Data Source Explorer with its context menu and the Properties action selected

For this example, change the default configuration repository name to QAConnections, and add a description. To view all of the properties, click on the tabs on the left side of the property browser, as shown in Figure 10.

Figure 10. Modify configuration repository properties
The image shows the Property browser populated for Configuration Repository SAMPLE with the Name property changed to QAConnections.

You can also rename a configuration repository using the context menu. Select the repository in the Data Source Explorer, and select Rename from the context menu, as shown in Figure 10b.

Figure 10b. Rename the configuration repository
The image shows the Configuration Repository SAMPLE selected in the Data Source Explorer with its context menu and the Renames action selected.

Remove a configuration repository

For convenience, .sql scripts are also provided to enable you to remove the tables that were created when a configuration repository is set up. To access the .sql, right-click on the database connection for the repository in the Data Source Explorer, and select Remove Configuration Repository, as shown in Figure 11.

Figure 11. Remove configuration repository
The image shows the SAMPLE database selected in the Data Source Explorer with its context menu and the Remove Configuration Repository action selected

Optionally using connection groups

You can organize connection information into groups within a configuration repository. Connection groups are optional. In order to create a connection group, the user must have the appropriate database authority to update the repository tables. Connection group names support any valid value of type VARCHAR in the underlying repository database.

Create a connection group

To create a connection group, right-click on the configuration repository, and select New Group, as shown in Figure 12.

Figure 12. Create connection group
The image shows the QAConnections repository selected in the Data Source Explorer with its context menu and New Group selected

Connection groups can contain subgroups to further organize the connection configurations. Connection group names must be unique within a configuration repository. However, subgroup names can be the same, as long as they are in separate groups, as shown in Figure 13.

Figure 13. Connection groups and subgroups
The image shows the QAConnections repository expanded with connection groups expanded for DB2LUW, DB2ZOS, and other. Under both DB2LUW and DB2ZOS we see connection subgroups for Version 8 and Version 9.

Delete connection groups

You can remove a connection group by selecting the group in the Data Source Explorer and selecting Delete Group from the context menu. When a connection group is deleted, all connection configurations within the group are also deleted. However, any database connections that were created using the affected connection configurations are not affected by the delete.

Modify connection groups

You can change the name or location of a connection group. To change the name of a connection group, select the group in the Data Source Explorer, and select Rename from the context menu, as shown in Figure 13b.

Figure 13b. Rename connection group
The image shows the IDS11.x subgroup selected in the Data Source Explorer with its context menu and the Rename action selected

Use the Cut and Paste menu options to modify the location of a connection group. Connection groups and subgroups can be moved within a repository or into a different repository. All subgroups and connection configurations within the selected connection group will also be moved. To move a connection group, select the group in the Data Source Explorer, and select Cut from the context menu, as shown in Figure 13c.

Figure 13c. Moving a connection group (cut)
The image shows the DB2ZOS group selected in the Data Source Explorer with its context menu and the Cut action selected

Select the new location, and select Paste from the context menu. The new location can be another configuration repository or another connection group or subgroup within the same repository. The connection group will be removed from the previous location and added to the selected group or repository, as shown in Figure 13d.

Figure 13d. Moving a connection group (paste)
The image shows the Integration configuration repository selected in the Data Source Explorer with its context menu and the Paste action selected

Connection groups can also be copied into new locations. Use the Copy menu option to copy a connection group, as shown in Figure 13e. This option will copy the connection group and its subgroups and configurations to the specified location. The original connection group is preserved.

Figure 13e. Copy a connection group
The image shows the DB2ZOS group selected in the Data Source Explorer with its context menu and the Copy action selected

Creating connection configurations

This section describes how to create the connection configuration itself, which is then used by others as needed to connect to that particular database. Each user needs to provide an appropriate user ID and password when he tries to connect.

Connection configurations are typically created by the DBA. The DBA, following company rules and standards, will make the appropriate connections available. For the QA example used in this article, the DBA will create test connection configurations for the various vendors and platforms used. Another typical scenario is for the DBA to create development, integration, and production connection configurations for developers, modelers, or QA personnel.

Create connection configuration

Connection configurations contain the property values required for creating database connections. A connection configuration is created from a local database connection in the Data Source Explorer. In order to create a connection configuration, the user must have the appropriate database authority to update the repository tables.

Note: Connection configurations should be created from database connections that are generated by the New Connection wizard to make sure the appropriate property values are used. Undetermined results can occur when creating database connections from connection configurations that were created using database aliases.

To create a connection configuration, select the local database connection in the Data Source Explorer. Right-click and select Create Connection Configuration, as shown in Figure 14.

Figure 14. Create a connection configuration
ciguration action selected

Select the appropriate group, and assign a meaningful name for the connection configuration. For the current example, use the version number for the server, as shown in Figure 15.

Figure 15. Specify configuration location
The image shows the Create Connection Configuration dialog box with the Version 9 subgroups selected within the DB2LUW connection group withing the QAConnections configuration repository.

The connection configuration is displayed under the specified connection group in the Data Source Explorer. Unlike subgroup names, connection configuration names must be unique within the configuration repository, as shown in Figure 16.

Figure 16. New connection configuration
The image shows the new DB2V97Server connection configuration within the Version9 subgroup.

You can also create connection configurations using the Copy and Paste menu options. Select the database connection in the Data Source Explorer. Right-click and select Copy from the context menu, as shown in Figure 16b.

Figure 16b. Copy database connection
The image shows the DB2ZOS connection selected in the Data Source Explorer with its context menu and the Copy action selected

Select the configuration repository or connection group where the connection configuration should be created. Right-click on the folder, and select Paste from the context menu, as shown in Figure 16c.

Figure 16c. Paste database connection
The image shows the Version 9 connection subgroup selected in the Data Source Explorer with its context menu and the Paste action selected

The connection configuration is created in the specified location. The connection configuration has the same name as the database connection, as shown in Figure 16d.

Figure 16d. New connection configuration (copy/paste)
The image shows that the new connection configuration, DB2Z_TEST, has the same name as the database connection from which it was copied.

Once the connection configuration is created in a repository, any user who can connect to the repository can create a database connection using the connection configuration.

Modify connection configurations

You can modify the name and location of connection configurations. These features are provided using the Rename, Cut, Copy, and Paste context menu options on selected connection configurations. Refer to the Modify connection groups section of this article for more details on using these menu options.


Creating connections from connection configurations

Now that you know about the repository setup and connection configurations, see how to actually use these connections. Any user that is able to connect to a configuration repository can create local database connections from the connection configurations. In the example based on connections used by a QA team, the DBA has completed the creation of various connection configurations. They are now available to the extended team for use in their day-to-day work. The only requirement is that each person must create a connection to the appropriate configuration repository as described above in the Section entitled “Connect to the configuration repository”.

To create a database connection from a connection configuration, right-click on the connection configuration in the Data Source Explorer, and select Create Connection from the context menu, as shown in Figure 17.

Figure 17. Create database connection
he image shows the DB2ZOS_TEST connection configuration selected in the Data Source Explorer with its context menu and the Create Database Connection action selected

The connection configuration is pre-selected in the New Connection dialog. Notice that the connection properties are automatically filled in from the connection configuration with the exception of the database user name and password. Enter the database user name and password. A new connection name, if desired, can also be entered. Finally, select Finish, as shown in Figure 18.

Figure 18. Specify connection information
The image shows the New Connection dialog box with the populated values higlighted where the User name, Password, and Connection name properties are specified.

The new database connection is displayed in the Data Source Explorer, as shown in Figure 19.

Figure 19. New database connection
The image shows the new connection, MY_ZOS_V9,selected in the Data Source Explorer

You can also create database connections from connection configurations using the Copy and Paste context menu options. Select the connection configuration in the Data Source Explorer. Right-click and select the Copy menu option, as shown in Figure 19b.

Figure 19b. Copy connection configuration
The image shows the DB2V97Server connfection configuration selected in the Data Source Explorer with the Copy action selected in its context menu

Select the Database Connections folder in the Data Source Explorer. Right-click and select Paste from the context menu, as shown in Figure 19c.

Figure 19c. Copy connection configuration
The image shows the Database Connections folder selected and the Paste action selection within its context menu.

The new database connection is displayed in the Data Source Explorer, as shown in Figure 19d. Notice the connection icon. The green check mark on the connection icon indicates that the database connection was created from a connection configuration.

Figure 19d. Icon identifying connection created from a connection configuration
The image shows the Data Source Explorer with the icon circled indicating the connection was created using the connection repository.

Once the database connection has been created from the connection configuration, use the Connect option from the context menu to connect to the database, as shown in Figure 19e.

Figure 19e. Connect to a database
The image shows DB2V97Server connection selected in the Data Source Explorer with the Connect action selected in its context menu.

Input the database user name and password, and select the OK button to connect to the database, as shown in Figure 19f.

Figure 19f. Complete database connection
The image shows the properties dialog box for DB2V97Server connection with the User name and password entry location circled and filled in.

Create multiple database connections from connection groups

You can use the Copy and Paste context menu options to create multiple database connections at once from a connection group. Using the QAConnections example, assume it is necessary to perform some testing using different versions of an Informix database. Select the Informix connection group in the Data Source Explorer, and select the Copy option from the context menu. Select the Database Connections folder in the Data Source Explorer, and select Paste from the context menu, as shown in Figure 20

Figure 20. Create connections from group
The image shows the Database Connections folder selected in the Data Source Explorer with the Paste action selected in the context menu.

A new database connection is created for each configuration in the connection group. Notice in Figure 20b that the names of the new database connections are identical to the connection configurations used to create the connection.

Figure 20b. Group connections
The image shows the Database Connections folder selected in the Data Source Explorer with new connections, IDS11.1 and ODS11.5 matching the names in the QAConnections configuration repository.

Validating and refreshing connections

You can validate and refresh database connections that were created from connection configurations. This feature ensures that database connection properties are kept consistent with any changes made to the connection configuration in the configuration repository.

When the DBA needs to update the property values for a connection configuration, he or she must delete the original connection configuration and then replace it by creating a new connection configuration using the same name as the original connection configuration. If a different name is used, any database connections that were created using this connection configuration will not validate correctly.

Developers or QA personnel use the green check mark icon to help them identify database connections that were created from connection configurations. See Figure 19d for more information on the icon.

To validate database connection properties against the original connection configuration, select the database connection in the Data Source Explorer. Right-click on the connection, and select the Connection Configurations > Validate context menu option, as shown in Figure 21.

Figure 21. Validate connection properties
shwos Connection Configurations selected

The database connection properties are compared with the property values in the connection configuration that was used to create the connection. If the property values are identical, a success message is displayed, as shown in Figure 22.

Figure 22. Validate connection properties (success)
The success messages says the connection properties are consistent with the repository

If the property values for a connection configuration have been replaced and do not match the properties for the database connection, a failure message is displayed.

Figure 23. Validate connection properties (failure)
The failure message says the connection properties are not consistent with the repository

The Problems View indicates the details of the failure, as shown in Figure 24.

Figure 24. Problems view
The image shows the warning and says unable to locate the common connection data source specified in the database connection.

Note that the database connection icon changes to a warning icon when the connection fails to validate, as shown in Figure 25.

Figure 25. Refresh connection icon
The image shows the MY_DB2ZOS_V9 database connection in the Data Source Explorer with the yellow warning icon next to the connection.

To update the database connection properties, select the database connection in the Data Source Explorer. Right-click on the connection, and select the Connection Configurations > Refresh context menu option, as shown in Figure 26.

Figure 26. Refresh database connection
The image shows MY_DB2ZOS_V9 database connection in the Data Source Explorer with the Connection Configurations Refresh action selected in the context menu

After the refresh, the database connection properties are updated to the current property values stored in the configuration repository, as shown in Figure 27.

Figure 27. Refresh database connection (success)
The refresh messages says the connection properties are consistent with the repository

Conclusion

This article described how the DB2 common connection features in Optim Development Studio, Optim Database Administrator, Optim Query Tuner, and InfoSphere Data Architect can be used to set up a configuration repository for enabling multiple users to share database connection properties. You also learned how a DBA create connection configurations and connection groups and subgroups to organize and define the connection configurations. Any user with a database connection to the configuration repository server can use the connection configurations to create local database connections without having to know the connection properties for each database.

Acknowledgments

Thanks to Tony Leung, Lawrence Dunnell, and Gary Lazzotti for their help with the information presented in this article.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=360078
ArticleTitle=Using common connections with Optim solutions
publish-date=10072010