SQL Server Performance Forum – Threads Archive

Calling all SQL and Informix experts!

Our Informix server is struggling with all the reports we run and so we are thinking of making a dedicated server for reporting. SQL is an obvious choice because we have it already for our retail system. However, the challenge is how to download the data we need each night. DTS works a treat but it is the volume of data that is the problem. We are a retail operation and we need to download the transactions from our Informix server into SQL. This data gets into Informix from the EPOS system in our stores. What we don’t want to do is download everynight the entire back history of transactions. We could do this by using the date of the transactions but we discovered it wont work. The problem is that if a store doesn’t post their transactions e.g. because of a system failure then these will get missed. What we need to do is record which transactions are downloaded into SQL and then compare this against what is on the Informix server and then download the difference each night. We thought of adding a flag onto the Informix server but we are not able to make any modifications to it. I think we could log the downloaded transactions in a SQL table and then use this as a record of what has been downloaded. We could then run a query that compares this to what is on the Informix server. With the right indexes I think this could work really well. Any thoughts? Incidently the two servers are separated by a 512Kbps wan link……

---

I have no experience on Informix so nothing to avail on that part. Coming to SQL Server, yes you can schedule a DTS job to import data and now you can take advantage of SQL Server 2000 Reporting Services which is a comprehensive reporting platform that can author, manage, and deliver both paper-oriented and interactive, Web–based reports. FOr more information on RS refer to thishttp://www.microsoft.com/sql/reporting/ link and review the contents. If the data is maintained in the Informix and reporting is done using SQL then the best option to take help of Crystal reports or Reporting services as mentioned above by linking this SQL server database where data will be imported on a scheduled pattern. BTW what is the volume of data you’re talking here? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

About 7 million rows…. The question is how to keep track of what has and hasn’t been downloaded each night. One idea I had was to make an assumption such as: All stores (which have a unique id) must have transactions for every day. Therefore, download all transactions with todays dates and all missing transactions for previous dates. Missing being defined as a date when a store doesn’t have any entries.

---

Loud Thoughts: -Your plan seems OK.
-Transactional REPLICATION would have been a good idea. I don’t know much about replication of Informix databases to SQL Server.
-Keep track of what you have downloaded in SQL Server (Transaction IDs or any other uniquely identifying characteristic of your data)
-Pull everything from the Informix server later/higher than the last recorded Identifier (or a combination of ID’s). This assumes that transactions already downloaded to SQL are not modified again on the Informix Server.
Nathan H.O.
Moderator
SQL-Server-Performance.com

---

We also need to deal with past transactions i.e. a shop might post transactions late so we can’t really use the transaction id. I thought of setting up a trigger on the Informix table and using that to populate a SQL table with transaction ids – that would give us a list of added transactions that we need to download….

---

As you mentioned, you will probably need to have triggers on the Informix database that can record the id and date that the row was added (are updates and removals also possible?) Then on the SQL Side, remember the most recent create date (note not related to the transaction date). Have a DTS job on the SQL Side, which imports the table. If you need to support updates and deletes, then your DTS job may need to import into a work-table and then process the result of that (plus on the Informix side you’d need to record the sql action to perform for this record) Cheers
Twan

---

Problem – just found out we can’t put a trigger on the Informix database as it is 3rd party…. But, we could use an open query and create an Inner join on the two databases and do a table compare – the results being what needs to be downloaded or not as the case may be….

---

Hi ya, If you need to do a comparison, and you have no way of holding a ‘pointer’ in the SQL database to the most recent data accepted from Informix, then you have no choice but to move the whole table across. Whether you do this by DTS, or by a multi-server join is not a big difference… I’d opt for the DTS solution… There are no replication products for Informix, which allow transactional replication to an ODBC database? Cheers
Twan

---

]]>