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. This article demonstrates 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.

System requirements

The Common Connection features described in this article were introduced in Data Studio Developer 2.1 and Data Studio Administrator 2.1. In Version 2.2, this capability is extended to InfoSphere Data Architect (IDA) 7.5.2, along with several usability enhancements. These enhancements are identified as Version 2.2 features in this article, and they indicate when a particular capability is only available beginning in the Version 2.2 releases of these products. 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 1.

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

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 New, as shown in Figure 3.

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

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

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

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

Version 2.2 feature: You can 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 7b.

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

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

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

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

Version 2.2 feature: 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 10b.

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 10c.

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 10d.

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

Create database connections from connection groups

Version 2.2 feature: 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 a DB2 for i database. Select the ISeries 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 10f.

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

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.

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

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

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

Version 2.2 feature: You can 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 13b.

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 13c.

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

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

Version 2.2 feature: 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. 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 14.

Version 2.2 feature: 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 new connection name, database user, and password, and select Finish, as shown in Figure 15.

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

Version 2.2 feature: You can easily 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 16b.

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

The new database connection is displayed in the Data Source Explorer, as shown in Figure 16d. Notice the new 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 16e.

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

Back to top

Validating and refreshing connections

Version 2.2 feature: 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 you need to update the property values for a connection configuration, 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 you use a different name, any database connections that were created using this connection configuration will not validate correctly.

New icons have been introduced to help users easily identify database connections that were created from connection configurations. See Figure 16d for more information on the new 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 17.

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

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

Note that the database connection icon changes to a red X when the connection fails to validate, as shown in Figure 21.

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

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

Back to top

Conclusion

This article described how the DB2 common connection features in Optim Development Studio, Optim Database Administrator, 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 to create 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.
  
  
• See Optim solutions in action in this "Day in the life of a DBA" video.
  
  

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 author

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.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

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

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers