MSSQL Linked Db for Informix Call Centers

Last week our VOIP team upgraded our Cisco Call Center server for 7.x to 8.x. This move required the migration of the database from its previous MSSQL server to an IBM Informix database. For a while now, our business intelligence department has reported off of the call information recorded in this database to gather statistics about the call center’s performance. Rather than reporting directly against the active database, a data mart of sorts was creating by using a sql server job to move bits of data from one MSSQL server to another designated for reporting against. This allows the report writers to “thrash” the db without affecting the functionality of the call centers.
After the migration we needed to change the job so that it could insert as select from the new Informix linked database.

To do this, follow these steps:

1. Download the IBM Informix Client Drivers. You can find the right installer for your OS on ibm’s site: http://www14.software.ibm.com/webapp/download/search.jsp?rs=ifxdl
2. In the drivers on the database host. In our case we have a 4 node active cluster so it was necessary to install the drivers on each node in case a failover occurs.
3. Launch the SetNet tool and configure the Server Information and Host tabs.
4. Configure an ODBC connection using the server name that you setup in the setnet interface. Test your connecting here with a set of credentials to make sure its functioning properly.
5. Launch SQL Server Management Studio and drill down to Linked Servers. Create a new linked server (the following are the parameters we used):
a. Product name: ifxoledbc
b. Data source: db_cra@odbc_servername
c. User=username;password=password;service=port;host=hostname;protocol=onsoctcp;
d. Setup security under the security menu to suit your needs.
6. Test your linked Server

Now that the linked server is configured you can test out some basic queries and setup the job to perform the sync. One of the odd things about our sync was the requirement to convert the timezone from the source db to the destination db. The original timezone is in GMT and they wanted the data mart to store the values in EST. Here is an example syntax that accomplishes that query:

DECLARE @offset SMALLINT
SELECT @offset = DATEDIFF(MINUTE, GETUTCDATE(), CURRENT_TIMESTAMP)
SELECT agentID, DATEADD(MINUTE, @offset, eventDateTime), gmtOffset, eventType, reasonCode, profileID FROM OPENQUERY([servername], 'SELECT agentID, eventDateTime, gmtOffset, eventType, reasonCode, profileID FROM AgentStateDetail')