Using common connections with Optim solutions

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

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

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.

Summary: 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. With the 2.2 release (in which these products have been renamed to Optim™), there have been additional usability improvements. Support for common connections has also been added to InfoSphere™ Data Architect 7.5.2. This article describes how to use common connections for greater efficiency in managing database connection information.

Tag this!

Date: 16 Jul 2009 (Published 18 Dec 2008)
Level: Intermediate
PDF: A4 and Letter (1164KB | 43 pages)Get Adobe® Reader®
Activity: 2623 views

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.

Accompanying demo

Watch a demonstration on channelDB2 that shows how to use the common connections (based on 2.1).

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

What's new in Version 2.2?

The following capabilities are available in Version 2.2:

Common connections are added to InfoSphere Data Architect 7.5.2 (and you can store a connection configuration for any database supported by IDA).
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 new 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. 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.

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.

Figure 1. Configuration repository setup

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.

Figure 2. Run SQL

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

Figure 3. New configuration repository

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

Figure 4. Select a repository connection

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

Figure 5. SAMPLE configuration repository

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.

Figure 6. Repository properties

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.

Figure 7. Modify configuration repository properties

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.

Figure 7b. Rename the configuration repository

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.

Figure 8. Remove configuration repository

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.

Figure 9. Create connection group

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.

Figure 10. Connection groups and subgroups

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.

Figure 10b. Rename connection group

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.

Figure 10c. Moving a connection group (cut)

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.

Figure 10d. Moving a connection group (paste)

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.

Figure 10e. Copy a connection group (paste)

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.

Figure 10f. Create connections from group

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.

Figure 10g. Group connections

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.

Figure 11. Create a connection configuration

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.

Figure 12. Specify configuration location

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.

Figure 13. New connection configuration

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.

Figure 13b. Copy database connection

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.

Figure 13c. Paste database connection

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.

Figure 13d. New connection configuration (copy/paste)

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.

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.

Figure 14. Create database connection

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.

Figure 15. Specify connection information

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

Figure 16. New database connection

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.

Figure 16b. Copy connection configuration

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

Figure 16c. Copy connection configuration

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.

Figure 16d. New database connection icon

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.

Figure 16e. Connect to a database

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

Figure 16f. Complete database connection

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.

Figure 17. Validate connection properties

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.

Figure 18. Validate connection properties (success)

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 19. Validate connection properties (failure)

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

Figure 20. Problems view

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

Figure 21. Refresh connection icon

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.

Figure 22. Refresh database connection

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

Figure 23. Refresh database connection (success)

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

Comments (Undergoing maintenance)

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

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=07162009

author1-email=kdevlin@us.ibm.com

author1-email-cc=