December 15, 2008 at 8:42 am
Could someone please point me in the right direction? I have a linked server to an Informix database. There are a couple of tables on the Informix database that I need to pull some of the data over to my SQL server every so many minutes for reporting purposed. The Informix database is not on site so crunching numbers for reports on it is futile.
What I need to do is query the Informix data for new and updated records each time and of course update the ones that have changed and insert the ones that are new. Am I just going to have to write a stored procedure or SSIS package to do this or is there something built into 2005 that would simplify this process?
December 15, 2008 at 8:54 am
There is nothing built in for this. You will need to write some queries to figure out which records are new or changed and insert or update the tables in the SQL database.
December 15, 2008 at 9:16 am
You can do this on either side, but pulling the entire table over every few minutes and then comparing with what's in SQL Server will end up with a lot of data.
If you have more info on what's inInformix, how it's updated, and what you've tried, we can help. As mentioned above, there's nothing that will determine this for you, but you can write something to handle the MERGE-type operation.
December 15, 2008 at 9:38 am
Sure, let me elaborate. The Informix data is from a help desk application. The data itself are help desk tickets. But I am only concerned about the tickets for our group WHERE group IN ('our group names'). So throughout the day as new tickets come in and existing tickets get updated we must be able to report on those numbers in near real time. By the way I am only provided a view from the Informix data and I have no control over that end of the deal.
If there is nothing built into 2005 to merge from external sources then I guess for all practical purposes we can forget that the data is from a linked server or from Informix for that matter. It will only make the discussion more complicated.
So I guess it comes down to a strategy. By the way I have many similar situations with data from other in house SQL servers where I need to have the data local for reporting. In most cases an overnight feed has been sufficient and I usually truncate the destination table and insert the data from the source each night. But as the data gets larger this method becomes more costly. And now that I need this in near real time then it will not work. So I guess I need to focus on writing a procedure to INSERT if new, UPDATE if different, and DELETE if does not exist. Any good examples of a proficient method of this would be greatly appreciated.
December 15, 2008 at 9:41 am
There are lots of ways to do this and all of them have their up and down sides. Search around the web for "slowly changing dimension" updates. Data warehousing is constantly dealing with this issue and it will help you focus your search results.
March 2, 2009 at 11:53 am
Jay,
I'm going to be in a similar situation soon. Are you using an ODBC driver to the Informix machine, and if so, is the SQL Server 32-bit or 64-bit? Also, how does one acquire that driver? Any/all help appreciated. Thanks!
Steve
(aka smunson)
:):):)
Jay Gamblin (12/15/2008)
Could someone please point me in the right direction? I have a linked server to an Informix database. There are a couple of tables on the Informix database that I need to pull some of the data over to my SQL server every so many minutes for reporting purposed. The Informix database is not on site so crunching numbers for reports on it is futile.What I need to do is query the Informix data for new and updated records each time and of course update the ones that have changed and insert the ones that are new. Am I just going to have to write a stored procedure or SSIS package to do this or is there something built into 2005 that would simplify this process?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 2, 2009 at 12:19 pm
I have a 64 bit SQL server but could not get a 64 bit ODBC to connect to Informix to save my life. When I get time I'll have to call IBM and pay for support to figure that part out. So what I had to do was use a 32 bit SQL server and a 32 bit ODBC driver for linking to Informix.
As far as the driver, trying to find it on IBM's site is not straight forward. And if I remember correctly someone in my organization sent me a driver which had a license key so I won't be able to share it with you.
For keeping my data in sync, the Slowly Changing Dimension was the route that got me going. I am using SQL 2005 and wasn't aware of what the SCD transformation was for. After mentioned in this post I searched for "slowly changing dimension" and found this great video that taught me what I need to get this going. http://channel9.msdn.com/posts/ZachSkylesOwens/Business-Intelligence-04b-Implementing-Slowly-Changing-Dimensions-in-the-Data-Flow/[/url]
March 2, 2009 at 1:41 pm
Thanks for the site link to that SCD information for SSIS. It was rather interesting. While the presentation was easy to follow, there must be a LOT of other things that the simplicity of it all was based on, like the exstence of an existing DW that could be looked at using SSAS, and no doubt a bunch of other stuff, like an application that's smart enough to properly populate the base tables in a similar fashion. I may have to figure all that stuff out.
With regard to confguring an ODBC driver on a Windows 64-bit platform, I found the following on IBM's site, having Googled for info on 64-bit ODBC drivers for Informix:
Configuring a DSN on the Windows 64-bit Platform
To configure a DSN on the Windows 64-bit platform, you must use the 32-bit ODBC Data Source Administrator: C:\WINDOWS\SysWOW64\odbcad32.exe.
That text was at the bottom of the page at:
Not sure if that will help or not, but what the heck...
Steve
(aka smunson)
:):):)
Jay Gamblin (3/2/2009)
I have a 64 bit SQL server but could not get a 64 bit ODBC to connect to Informix to save my life. When I get time I'll have to call IBM and pay for support to figure that part out. So what I had to do was use a 32 bit SQL server and a 32 bit ODBC driver for linking to Informix.As far as the driver, trying to find it on IBM's site is not straight forward. And if I remember correctly someone in my organization sent me a driver which had a license key so I won't be able to share it with you.
For keeping my data in sync, the Slowly Changing Dimension was the route that got me going. I am using SQL 2005 and wasn't aware of what the SCD transformation was for. After mentioned in this post I searched for "slowly changing dimension" and found this great video that taught me what I need to get this going. http://channel9.msdn.com/posts/ZachSkylesOwens/Business-Intelligence-04b-Implementing-Slowly-Changing-Dimensions-in-the-Data-Flow/[/url]
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply