2

I want to transfer tables data from SQL server to Informix and vice versa.

The transferring should be run scheduled and sometimes when the user make a specific action.

I do this operation through delete and insert transactions and it takes along long time through the web between 15 minute to 30 minute.

How to do this operation in easy way taking the performance in consideration?

Say I have

Vacation table in SQL Server and want to transfer all the updated data to the Vacation table in Informix.

and

Permission table in Informix and want to transfer all the updated data to the Permission table in SQL Server.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Anyname Donotcare
  • 11,113
  • 66
  • 219
  • 392
  • 1
    I've looked, as you requested. I've not touched SQL Server in my life; I don't know anything about transferring data between it and Informix. Sorry, I can't help further. Please flag this comment 'obsolete' when you've read it. – Jonathan Leffler Sep 15 '15 at 18:26
  • @JonathanLeffler : thanks a lot but i have succeeded in writing the data from `SQL Server` table to `csv file` now Could u help me in loading the data in this file to the equivalent table in `informix` through stored procedure beacuse i get syntax error when i use `LOAD` command in my stored procedure – Anyname Donotcare Sep 16 '15 at 06:44
  • @JonathanLeffler :here's exactly the part i need your help in http://stackoverflow.com/q/32586186/418343 ,and it concerns `informix` . – Anyname Donotcare Sep 16 '15 at 06:47

2 Answers2

2

DISCLAIMER: I am not an SQL Server DBA. However, I have been an Informix DBA for over ten years and can make some recommendations as to its performance.

Disclaimer aside, it sounds like you already have a functional application, but the performance is a show-stopper and that is where you are mainly looking for advice.

There are some technical pieces of information that would be helpful to know, but in their absence, I'm going to make the following assumptions about your environment and application. Please comment or edit your question if I am wrong on any of these.

  1. Database server versions. From the tags, it appears you are using SQL server 2012. However, I cannot determine the Informix server and version. I will assume you are running at least IDS 11.50 or greater.
  2. How the data is being exchanged currently. Are you connecting directly from your .NET application to Informix? I would assume that is the case with SQL Server and will make the same assumption for your Informix connection as well.
  3. Table structures. I assume you have proper indexing on the tables. On the Informix side, dbschema -d *dbname* -t *tablename* will give the basic schema.

If you haven't tried exporting data to CSV and as long as you don't have any compliance concerns doing this, I would suggest loading the data from a comma-delimited file. (Informix normally deals with pipe-delimited files, so you'll either need to adjust the delimiter on the SQL Server side to a pipe | or on the Informix import side). On the Informix end, this would be a

LOAD FROM 'source_file_from_sql_server' DELIMITER '|' INSERT INTO vacation (field1, field2, ..)

For reusability, I would recommend putting this in a stored procedure. Just wrap that load statement inside a BEGIN WORK; and COMMIT WORK; to keep your transactional integrity. Michał Niklas suggested some ways to track changes. If there is any correlation between the transfer of data to the vacation table in Informix and the permission table back in SQL Server, I would propose another option, which is adding a trigger to the vacation table so that you write all new values to a staging table.

With the import logic in a stored procedure, you can fire the import on demand:

EXECUTE PROCEDURE vacation_import();

You also mentioned the need to schedule the import, which can be accomplished with Informix's "dbcron". Using this feature, you'll create a scheduled task that executes vacation_import() periodically as well. If you haven't used this feature before, using OAT will be helpful. You will also want to do some housekeeping with the CSV files. This can be addressed with the system() call, which you can make from stored procedures in Informix.

Community
  • 1
  • 1
Nigel Tufnel
  • 418
  • 3
  • 14
  • The informix server version is: `IBM Informix Dynamic Server Version 12.10.FC3` Could u explain how to put `'source_file_from_sql_server'`,How to generate file like this and how to read it .? – Anyname Donotcare Sep 01 '15 at 15:05
  • 1
    The `source_file_from_sql_server` is just a CSV from the SQL Server. Here is an example: http://stackoverflow.com/a/425463/5260945. Getting the CSV from one DB server to the other really depends on your network - could be via a shared file system. The "reading" it part of it would be handled entirely by the Informix DB, using the stored procedure, which I dubbed `vacation_import`. – Nigel Tufnel Sep 01 '15 at 16:28
  • thanks , but still little confused , Do u mean `'source_file_from_sql_server'` is the path of CSV generated from SQL Server ? What about the opposite from informix to sql server ? – Anyname Donotcare Sep 01 '15 at 18:30
  • 1
    Sorry for the confusion and you are correct. The `source_file_from_sql_server` is the full path of the CSV generated from SQL Server. You can follow the same pattern for the data that needs to be fed back to SQL Server. You may want to dump the outgoing data (Informix -> SQL Server) into a separate directory from the incoming data (SQL Server -> Informix). I never addressed how you could trigger the import on SQL Server, which would largely depend on how the two database systems are connected. – Nigel Tufnel Sep 01 '15 at 19:58
  • everything goes okay except the part of informix !! I can't use `Load` in stored procedure in informix ,it gives me syntax error – Anyname Donotcare Sep 15 '15 at 12:21
  • 1
    You are right - I didn't realize that limitation. However, since you are using a recent version of Informix, an alternative way to load the external data is by mapping the CSV file to an `EXTERNAL TABLE`, then using the `MERGE` command to handle the update/insert. See Jonathan Leffler's answer to a similar question on SO: http://stackoverflow.com/a/20665293/5260945. He is an absolute expert in Informix and his solution seems very applicable to your use case too. – Nigel Tufnel Sep 15 '15 at 16:53
1

Some ideas:

  1. Add was_transferred column to source tables setting its default value to 0 (you can use 0/1 instead of false/true).

  2. From source table select data with was_transferred=0.

  3. After transferring data update selected source row, set its was_transferred to 1.

  4. Make table syncro_info with fields like date_start and date_stop. If you discover that there is record with date_stop IS NULL it will mean that you are tranferring data. This will protect you against synchronizing data twice.

Michał Niklas
  • 53,067
  • 18
  • 70
  • 114