Before you start
In today's fast-paced world, information on demand plays a very critical role in business success. Hence, a stable database server that meets or exceeds expectations at the enterprise application level in the areas of performance, scalability, reliability, extensibility, maintainability has become a mandate. Informix Dynamic Server (IDS), a relational database server from the IBM Information Management products portfolio, meets these requirements with its copious feature set.
However, having the best database to support your application is not good enough considering the rampant growth of data coming from data-centric applications. This multi-fold growth in enterprise application data increases the load on the application database and also impacts performance. You may need to segregate older data and maintain it separately in such a way that it is easily accessible if needed for reporting or strategic decision making. IBM Optim solution provides functionalities such as archiving historical data from various database systems supporting the application, restoring data from these archives in the production environment, masking production data, and making it available for reliability and application quality testing
This tutorial gives you a quick overview of integrating Informix Dynamic Server with Optim Solutions and Optim Data Privacy Solution.
Brief introduction to IDS and Optim
Informix Dynamic Server is a relational database management system designed to provide client/server processing capabilities for enterprises, workgroups, midsize businesses, and even small businesses. With its dynamic multi-threading architecture, IDS scales well and can manage very large volumes of data, providing high performance in the OLTP environment. With the new warehouse capabilities available in IDS, it can also be used in the analytics area to make smarter business decisions.
The high-availability data replication (HDR), the multi-node active cluster for high availability (MACH11) features, and other advanced features in the area of backup and restore make it a very resilient and highly reliable database server. Informix Dynamic Server is also well-known for its ease of use and low maintenance. With the Open Admin Tool, a PHP-based open source tool, and administrative SQL APIs, administration of IDS is minimal. Last but not the least, IDS is easy to manage. IDS provides enough flexibility and freedom to application developers since it supports a variety of environments, including Java™ technology, PHP, Ruby on Rails, and more.
However good the database system is, if there is no provision for separating old and inactive data from current, active application data, application performance will eventually deteriorate. This is where Optim plays a key role in enterprise data management—by providing the means for safe removal of historical data to an archive that can be stored in a variety of environments. It provides access to this data through multiple methods, such as report writers, ODBC/JDBC applications, and other types of applications.
In the future, if additional business processing is required, this data can be retrieved and placed back in the application environment. This archived data can even be viewed directly from the archive. Due to various regulatory and compliance restrictions, sensitive application data such as social security numbers or credit card numbers need to be masked before it is made available in the testing environment. Optim provides application-aware and persistent data masking routines that substitute realistic but fictional data in place of confidential fields for testing. Using Optim Test Data Management, a subset of production data can be separated out that can be further used in testing by loading or inserting it into the target environment.
In this tutorial:
- Learn how to configure Informix Dynamic Server to work together with Optim Solutions.
- Verify connections and set up sample tables and stored procedures.
This tutorial was written using the following software levels and assumes that you have them already installed:
- Informix Dynamic Server 11.50xC5
- Informix CSDK 3.50xC5
- Optim products:
- IBM Optim Data Growth Solution 7.1.1
- IBM Optim Test Data Management Solution 7.1.1
- IBM Optim Data Privacy Solution 7.1.1
In all the scenarios mentioned in this tutorial, the Informix CSDK must reside on the same machine with the Optim server, irrespective of where the IDS Server resides.
Note: The Optim documentation referred to in the tutorial is bundled with the Optim products and is not available on the Web.
Configure the IDS server and IDS client for Optim on the Microsoft® Windows® platform
This section introduces you to the following:
- Making registry entries for an IDS instance using the Setnet32 utility available in IDS CSDK
- Verifying the connectivity to Informix Dynamic Server
- By creating a DSN
- By executing the ILogin Demo utility
Set up the registry for the IDS instance using the CSDK's Setnet32
- Start an instance of IDS—either the demo instance created during installation, or other instances you have created.
- Use the Setnet32 utility to update the Windows registry for future
connectivity from Optim to IDS.
You can initiate Setnet32 either by selecting Start > All Programs > IBM Informix Client -SDK <version> > Setnet32 or by directly executing the command
setnet32from a command prompt.
- The screen shown in Figure 1 is displayed. The
Setnet32 application has four tabs: Environment, Server
Information, Host Information, and About Setnet32. By default, the
Environment tab is active.
Set INFORMIXDIR to the location where the CSDK has been installed, and set INFORMIXSERVER to the name of the server instance that is being used.
Figure 1. Setting up environment variables in Setnet32
- Choose the Server Information tab (illustrated in
Figure 2). The name of the Informix server
instance will automatically appear (the value that you set for
INFORMIXSERVER). You can manually choose another server if
Figure 2. Setting up server information in Setnet32
- Fill in the remaining fields: HostName,
Protocolname, and Service
Name. You can also opt to have the INFORMIXSERVER as
your default server. To do so, click on the Make Default
Table 1 shows descriptions of the server information variables:
Table 1. Description of server information variables
|IBM Informix Server||The name of the IDS instance that will be used to create an active connection. By default, the setnet32 utility picks the value from the entry INFORMIXSERVER as set up in the Environment tab.|
|HostName||The name of the machine where the IDS instance is running.|
|Protocolname||The protocol that is used for communication with the IDS server instance.|
|Service Name||The Port number that is assigned to the IDS instance in use. You can also provide the Service Name that is mapped to the port number used by the IDS server instance.|
- Click on Apply to save your entries.
- Click on the Host Information tab. The screen
illustrated in Figure 3 will display.
Figure 3. Setting up Host Information in Setnet32
- Fill in the information for Current Host,
User Name, Password Option,
and Password, relevant to the setup available on
the user side.
Table 2 shows definitions for the host information variables:
Table 2. Description of host information variables
|Current Host||Name of the machine that hosts the IDS instance.|
|User Name||Name of the user who has an account on the specified Host machine and is the owner of the specified IDS instance, whose information is updated in the Host Information tab.|
|Password Option||Designation where you can specify if password security is needed.|
|Password||If you opt to have a password, this text box will be enabled and will allow you to enter the actual password against the specified user name and IDS instance. If you chooses not to have any password, this text box will be disabled.|
- Click on OK to save the entries.
This completes the configuration using Setnet32 utility for the active or current IDS instance.
Verify the connection
You can verify the connectivity to the IDS server using CSDK by following either of the two methods described in this section.
Prerequisite: In both scenarios, connectivity is verified by accessing the stores_demo demo database. You can create this demo database by executing the following command from the command line interface of the instance window:
The stores_demo database comes loaded with demo tables and data.
Method 1: Using ODBC DSN
This scenario creates a new Data Source Name (DSN) for the entry in Setnet32.
- Select Control Panel > Administrative
Tools > Data Sources (ODBC) to
bring up the ODBC Data Source Administrator window, as shown in
Figure 4. ODBC Data Source Administrator
- Under the User DSN tab of the ODBC Data Source Administrator window, click on the Add button to add a new Data Source.
- This action should open a new window, as shown in Figure 5, with a list of drivers to choose from.
Choose the entry with the name IBM INFORMIX ODBC DRIVER, and then click on Finish.
Figure 5. Create New Data Source: Choose IBM Informix ODBC Driver
- This will bring up a new window for setting up the IBM Informix
ODBC Driver. By default, the initial screen will have the General
tab active (as shown in Figure 6). Choose a
name for the DSN, and fill in the optional description field, if
Figure 6. IBM Informix ODBC Driver Setup: General tab
- Click on Apply and choose the next tab, Connection.
- Under this tab, you can enter the connection information against
the server instance you previously specified (INFORMIXSERVER) and
the database to connect to, as shown in Figure
Earlier, you entered the connection information in the entry during the setnet32 configuration. Here, you can just choose the setnet32 entry from the drop-down menu provided against the variable Server Name. By doing so, the fields against the variables Host Name, Service, and Protocol are updated according to the entries made in the setnet32 environment.
Figure 7. IBM Informix ODBC Driver Setup: Connection tab
- Choose an entry against the variable Database Name from the drop-down menu. (The list includes all the databases related to that specific IDS instance.)
- Enter the relevant User Id and Password for the given database and instance.
- Click on Apply & Test Connection.
Upon making a successful connection, you will see the following message on the screen:
Figure 8. IBM Informix ODBC Driver Setup: Test Connection Successful
Method 2: Using the ILogin Demo
The ILogin Demo is a sample demo application that is shipped with Informix Client SDK.
- To choose this option, select Start >
All Programs > IBM Informix Client
SDK > ILogin Demo.
The ILogin Demo application will open, and you will be presented with the File and Help options in the toolbar.
- Select Run from the File menu to
initiate the connection process:
Figure 9. Execution of ILogin Demo
- Enter the values for Server and Stores
Database, and then click on OK. The
ILogin Demo application will pull in the relevant values for the
parameters Hostname, Servicename, Protocolname, Username, and
Password from the registry. Alternatively, you can always fill in
the values for these parameters manually.
Figure 10. ILogin Demo: Login Parameters
- Click on OK.
The ILogin Demo will connect to the server through the client, execute a certain demo
SELECTstatement against a table within the demo database, and present the output, as shown in the Figure 11:
Figure 11. ILogin Demo: Successful test connection and execution
Configure the IDS server and IDS client on UNIX® and Linux® platforms for Optim
In this section, explore the possible configurations for setting up the IDS server and client, learn about how variables must be set correctly in order to connect client to server, and see how to execute demo programs to verify connectivity between the IDS server and client.
The IDS server and client can be set up in any of the following combinations:
- IDS server and CSDK residing in the same directory
- IDS server and CSDK residing in two different directories
- IDS server and CSDK residing on two different machines
To work with these three combinations, follow these guidelines:
- If both IDS server and Informix CSDK are installed in the same
directory, then the value of the variable
INFORMIXDIR(explained in Table 3) should point to the location of that directory structure.
- If the IDS server and Informix CSDK are installed in two different
directories, then you should consider following two scenarios:
- While working with IDS server, the value of the variable
INFORMIXDIRshould point to the directory structure where the IDS server is installed.
- While working with Informix client, the value of the
INFORMIXDIRshould point to the directory structure where the Informix Client is installed. Remember that Optim establishes connectivity to the IDS server through the Informix CSDK.
- While working with IDS server, the value of the variable
- If IDS server and Informix Client are installed on two different
machines, then the value of the variable
INFORMIXDIRshould point to the location of the Informix CSDK installation directory structure. Remember, for each IDS server on a different machine, the CSDK should maintain individual
INFORMIXSQLHOSTS(explained in Table 3) files with relevant entries.
When working on the UNIX or Linux platforms, you will need to have values set for the parameters outlined in Table 3.
Table 3. Informix variables considered by Optim for connectivity
|The parameter should point to the directory where the Client SDK is installed. Optim connects to IDS Server using Client SDK.|
| The value of the
|The value of the parameter should be the instance name, such as DBSERVERNAME.|
Verify the connectivity between the Client SDK and the IDS server
During the installation of IDS, the installer creates a directory demo under $INFORMIXDIR, which contains ready-to-use demo files that can be used to verify connectivity to the IDS instance. Navigate to the location $INFORMIXDIR/demo/esqlc. Follow the steps mentioned in the README to test the connectivity to the IDS instance.
Optim-Informix communication architecture
Figure 12 shows how Optim interacts with Informix Database Server. The database alias and Optim directory are the two variables that you must set up. In this section, you will work on creating both the database alias and the Optim directory.
Figure 12. Optim-Informix communication architecture
The Optim workstation connects to the Optim server by choosing an Optim
directory to work with. When a communication request to the Informix
database is raised by Optim, the database alias looks for the registry
entries based on the value of
INFORMIXSERVER and makes a connection using
the host name and port number associated with it.
The Optim directory is a set of tables where Optim stores the objects needed to perform the operations shown in Figure 12:
The objects of the Optim directory are as follows:
- Database aliases
- Access definitions
- Table maps
- Column maps
- Column map procedures
- Primary keys
- Processing, utility, and security definitions
- Archive file registration
A DB alias is a user-defined object associated with a database. When you create a DB alias, you provide values such as database type and database version. These values are used by Optim to make a connection with a database. You must be authorized in order to define a DB alias.
A DB alias name is used as a high-level qualifier that allows you to access the database and performs requested functions. For example:
Each database that you are connecting to can have only a single unique DB alias.
This section covers the procedures and steps required to create or update an Optim directory and a DB alias, along with a few screen shots that depict connection with Informix. Refer to the "IBM Optim Installation and Configuration guide" (Config.pdf), found under <Optim Dir>/RT/Docs, to get detailed information with respect to menus and the various available operations.
The Optim directory
To create an Optim directory, you must provide the directory name, the database instance in which the directory resides, and the information required to connect to the database. After creating the Optim directory tables, the Optim configuration program creates packages, plans, procedures, and a Windows registry entry on a Windows workstation that allows the workstation to access the Optim directory. Here are the steps to follow to complete this process:
- Select Optim under Start > All Programs > IBM Optim.
- Select Tasks > Create/Update Optim
Directory, as shown in Figure 13:
Figure 13. Optim Configuration: Tasks available for the user
Note : You can choose to create or update both the Optim directory and the DB alias either as two individual steps or as a single step by merging the procedure of creating the DB alias during the creation of Optim directory. This can be done by checking or un-checking the option Create/Update DB Alias for this Database as shown in Figure 19. In this tutorial, we have chose to create the DB alias as part of the create/update Optim directory procedure.
- A new window with description "Create/Update Optim Directory" will
open, where you can choose from one of the three options provided
under the section "Optim Directory Specification":
- Create new Optim directory and registry entry
- Create new Registry entry for existing Optim directory
- Use existing Optim directory and registry entry
To create a new Optim directory, choose the first option and specify a custom Optim Directory name. If no Optim Directory registry entry exists, then the default name "OPTIMDIR" will be displayed.
Figure 14. Create/Update Optim Directory: Specify Optim Directory
- Click on Proceed to move to the next screen.
- On the next screen, you can optionally provide a description of
the Optim directory that is being created. The next two fields,
Type and Version, allow you to choose the database and the version
of that database. For Type, select
Informix from the drop-down list; for
Version, select Dynamic Server v9.x,
10.0 and 11.0(Toleration).
Figure 15. Optim Directory DBMS specifications
- Click on Proceed to continue.
- In the next screen, provide the parameters for connecting to IDS.
The configuration program must connect to the database to create
the Optim directory tables, packages, plans, and procedures, To
enable this connection, provide values for User ID, Password, and
the Connection String. The value for Optim directory will be
Figure 16. Optim Directory - Database connection parameters
Table 4 lists the descriptions of the database connection parameters:
Table 4. Database connection parameters
|User ID||The login name of the user who is the creator of the Optim directory database.|
|Password||The login password assigned to the user ID.|
|Connection String||The value of the IDS instance.|
|DB Name||The name of the database you are connecting to.|
- During the creation of Optim directory, the Optim configuration
loads the Optim directory with object tables. These tables are
used to store Optim objects that contain information for every
database that connects to Optim. Each Optim Directory table can be
loaded into a different DBSpace, which can be separate from your
Figure 17. Create/Update Optim Directory -- Update Optim Directory Tables
Under the "Directory Table Specification" section, choose an owner ID, specify a default DBSpace, and, optionally, specify a DBSpace name to override the default for an Optim directory table.
The entries under the header "Directory Table" on the right-hand side of the screen list the tables that will be populated within the Optim directory. Optionally, you can choose to display SQL (select the check box next to Display SQL) to take a quick look at the SQL.
Note the following definitions:
- Database table
- The fully qualified name of a primary key that matches the name of the database table for which it is defined: dbalias.creatorid.tablename.
- Alias that identifies the database where the table resides (1 to 12 characters).
- Creator ID assigned to the table (1 to 64 characters).
- Base table name (1 to 64 characters).
- Click on Proceed to continue.
- On the next screen, enter settings related to creating and
dropping stored procedures relevant to the Optim directory. These
procedures are used for accessing Optim directory tables. Figure
18 shows the options on the Create/Drop Stored Procedure screen:
Figure 18. Create/Update Optim Directory -- Create/Drop Stored Procedures
Under the "Stored Procedure Specifications" section, you can choose between Create/Refresh, Use Existing, or Drop, as provided on the left-hand side of the section. On the right-hand side, you can edit the Grant Auth ID. This author ID is an identifier for authorized users. It can be a user name, a group name, or "PUBLIC," which means anyone can run Optim. Optionally, you can choose to display SQL before creating or dropping procedures.
- Click on Proceed to continue.
Optim DB alias
Optim can access several databases at the same time. However, each database must have a unique DB alias stored in the current Optim directory, which will be used as a high-level qualifier for database table name. Hence, after creating the Optim directory, the next step is to create a DB alias for each database and to create the packages, plans, and procedures to access the tables in those databases.
After creating the Optim directory, you will be prompted to create a DB alias for the database in which it resides.
- Select the check box option to Create/Update DB Alias for
this Database if the tables from the same database
(which was used in the connection information) are to be accessed.
If the DB alias is to be created for a different database, then
clear the check box.
Figure 19. Create/update DB alias for this database
- Click on Proceed to continue.
- The next screen allows you to select one of the following options
listed under the "Database Alias Specifications" section:
- Create New - For any Single DB Alias: To create a new DB alias.
- Create/Select Multiple - For a Single DB Server (Sybase, SQL Server, Informix): To create or select multiple DB aliases from a single database server.
- Use Existing - For any Single Existing DB Alias: To choose one of the existing DB aliases from the drop-down menu.
Figure 20. Create/update DB alias: Database alias specifications
In this instance, select Create New to create a new DB Alias, and enter a name in the Name field.
- Click on Proceed to continue.
- On the next screen, you can optionally provide a description for
the new DB alias. The other two fields, Type and Version, retain
the values you entered during the creation of the Optim directory.
Figure 21. Create/update DB alias: Specify DBMS specifications
- Click on Proceed to continue.
- The configuration program requires certain information to
configure the Informix catalog tables. That information is
provided on the next "Connect to Database" screen. Values related
to connection to the database are already populated from
previously entered values. Alter those if needed.
Figure 22. Create/update DB alias: Database connection parameters
- Click on Proceed to continue.
- Next you are prompted to create or drop stored procedures related
to catalog tables, as shown in Figure 23:
Figure 23. Create/update Optim directory: Create/drop stored procedures for the catalog tables
This is a similar screen to the stored procedure screen you saw during the Optim directory creation (see Figure 18).
- Proceed with the creation of procedures. Refer to the "IBM Optim
Installation and Configuration Guide" (Config.pdf, found under
<Optim Dir>/RT/Docs) for assistance with completing these
- Always require a password for this database.
- Provide connection information for this database.
- Create primary keys for the given DB alias.
These are all generic steps that are the same for any database system, with no specific considerations for IDS.
- Optim is bundled with sample and data privacy tables. You may
choose to load or refresh the sample tables as well as the data
privacy tables, as shown in the Figure 24. If
you do not intend to make use of the sample or data privacy
tables, then you can simply skip the steps and proceed to the next
option on the next screen.
Figure 24. Create/update Optim directory: Load/drop sample tables
To load the sample tables, select the radio button next to Load/Refresh Sample Tables. The name for Owner ID will vary, depending on the database vendor, and the DBSpace was chosen in the Table specifications section.
- Click on Proceed to create or refresh the sample tables. Otherwise, click on Skip to jump to the next screen without opting to create the sample tables.
- Follow the same process to load or drop data privacy tables, as
shown in the Figure 25:
Figure 25. Create/update Optim directory: Load/drop data privacy tables
- Continue the remaining steps as provided in the "IBM Optim
Installation and Configuration Guide" until you reach the screen
shown in Figure 26, which says configuration is complete.
Figure 26. Create/update Optim directory: Complete
The process of creating the new Optim directory and DB alias is now successfully concluded.
In Part 2 of this tutorial series, explore all the Optim functionalities you can use with Informix Dynamic Server as the backend database.
We wish to thank Sheshnarayan Agrawal, Optim architect at the India Software Lab, for reviewing this article .
- "Understand the packaging of Optim Solutions for database development, administration, and performance management" (developerWorks, February 2010): Get an overview of the functionality and packaging of the development, administration, and performance management offerings of the IBM Optim products.
- "Integrated Data Management: Managing data across its lifecycle" (developerWorks, April 2010): Understand both the vision and reality of Integrated Data management and how you can use IBM solutions to respond quickly to emerging opportunities, improve quality of service, mitigate risk, and reduce costs.
- IDS Information Center: Get more details about Informix Dynamic Server.
- developerWorks Optim family page: Learn more about Informix. Find technical documentation, how-to articles, education, downloads, product information, and more.
- developerWorks Informix page: Learn more about Optim solutions. Find technical documentation, how-to articles, education, downloads, product information, and more.
- developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
- IBM Data Studio V2.2: Download IBM Data Studio, which provides foundational database development and administration support for the DB2 and Informix family of products at no charge.
- Optim Development Studio and Optim pureQuery Runtime: Download the free, 30-day trial version of Optim Development Studio, which provides an integrated database development environment for Oracle, DB2, and Informix.
- Informix Dynamic Server Enterprise and Developer Edition: Download the free, 90-day trial version of Informix Dynamic Server Enterprise Edition, an exceptional online transaction processing (OLTP) database that offers outstanding performance, reliability, scalability and manageability for enterprise and workgroup computing. .
- Informix Dynamic Server Express Edition: Download a free, trial version of Informix Dynamic Server Express Edition to get started with IDS.
- Informix CSDK: Download one of the trial or demo versions of Informix CSDK.
- Download the Optim Database Administrator: Download the free, 30-day trial version of Optim Database Administrator V2.2.2 (previously Data Studio Administrator), which improves database administrators' (DBAs') productivity and reduces application outages by automating and simplifying complex DB2 structural changes.
- Data Studio Administration Console: Download the Data Studio Administration Console.
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Check out the Integrated Data Management Experts blog and get involved in the Integrated Data Management community space, which has a comprehensive list of resources and downloads.
- Participate in developerWorks blogs and get involved in the My developerWorks community; with your personal profile and custom home page, you can tailor developerWorks to your interests and interact with other developerWorks users.