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.
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
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
A dialog will popup providing a list of perspectives available.
Figure 3. Open Perspective Dialog
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 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
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
Select the appropriate database connection from the list, and click Finish, as shown in Figure 7.
Figure 7. Select a repository connection
The configuration repository is displayed in the Data Source Explorer, as shown in Figure 8.
Figure 8. 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 9.
Figure 9. 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 10.
Figure 10. Modify configuration repository properties
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
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
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
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
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
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)
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)
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
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.
Figure 14. Create a connection configuration
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 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
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
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 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)
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
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 new database connection is displayed in the Data Source Explorer, as shown in Figure 19.
Figure 19. New database connection
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
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 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
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
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
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
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
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
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)
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 Problems View indicates the details of the failure, as shown in Figure 24.
Figure 24. Problems view
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
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
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)
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.
Thanks to Tony Leung, Lawrence Dunnell, and Gary Lazzotti for their help with the information presented in this article.
- 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.
- 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.