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

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

A dialog will popup providing a list of perspectives available.

System requirements

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.

Back to top

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.

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.

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

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.

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

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

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.

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.

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.

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.

Back to top

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.

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.

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.

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.

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.

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.

Back to top

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.

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.

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.

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.

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.

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.

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.

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.

Back to top

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.

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.

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

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.

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

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.

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.

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

Back to top

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

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.

Back to top

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.

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.

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.

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

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

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.

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

Back to top

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

• Check out "Integrated Data Management: Managing data across its lifecycle " (developerWorks, December 2008, updated June 2009) to understand the big picture of Integrated Data Management and how Optim solutions can help achieve this vision.
  
  
• Read "What’s new and cool in Optim Development Studio 2.2" (developerWorks, June 2009) to learn about the new features that can help you with your heterogeneous database application development.
  
  
• Want to use multiple Optim solution products together? See "Shell sharing with InfoSphere Data Architect, Optim Development Studio, and Optim Database Administrator" (developerWorks, July 2010).
  
  

Get products and technologies

• Download a trial version of Optim Development Studio
  
  
• Get the trial version of Optim Database Administrator
  
  
• Get the InfoSphere Data Architect trial code.
  
  

Discuss

• Explore the Optim Development Studio and pureQuery Runtime discussion forum.
  
  
• Check out the Integrated Data Management community space.
  
  
• Participate in the Integrated Data Management Experts Blog.
  
  

About the authors

Karen 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 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.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Understanding the terminology
• Using a configuration repository
• Optionally using connection groups
• Creating connection configurations
• Creating connections from connection configurations
• Create multiple database connections from connection groups
• Validating and refreshing connections
• Conclusion
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: Data Studio
• Demo: Use Ajax with DB2
• Community: DB2
• Buy: Data Studio

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers