Downloading data from our Informix server

Our entire sales database sits on an Informix server and users run their reports directly to this server.

Because of the volume of data - upwards of 7 million rows in some tables we are finding performance is becoming an issue. We realise that Informix is not the best for running queries and so are looking to replicate to a SQL server and use that as a reporting server.

My chosen method is DTS using ODBC which works fine. However due to the sheer volume of data to be downloaded each night (over a 512K wan link) we only want to download changed data.

Is there any way we can get SQL2000 to download only change data - i.e a differential download or something? We don't have a unique field in the table (for whatever reason) such as sales date or transaction ID (shops might upload old / back-dated transactions for example due to a systems failure).

If I do have to download all the data every night is my method the most efficient or is there some other approach I could use?

Ultimatly the aim here is to relieve the Informix server of the reporting overhead so any alternative ideas are welcome!

Many thanksHi
The normal way of doing this sort of thing would be replication - this would give you your "differential downlaod".
I am not sure what one can do repl wise between informix & mssql but would be worth checking it out...
Des
(otherwise can use tracking tables which record max id last copied & copy only from there each day - use vars in DTS package, seeded by values in the tracking tables)|||Which version of informix are you running - believe it or not informix is very good at running queries, so I would first check to see if either the queries are not optimized correctly, check for poor database design and also look at the onconfig file to make sure the server itself has the appropriate settings.

To solve the transfer issue - probably you best method is to dump the data and use sql server to bulk copy (bcp) or bulk insert the data. You can use some identifier within Informix such as a date/time stamp to find the changed records - however, I go back to there is probably something either designed poorly or misconfigured on your informix box that is causing this poor performance.

Doubts regarding Sales Forecast model

I have few more clarification regarding time series.

Firstly

In my model the month level product sales value represented across 1^st day of every month.So that the key time column is of datetime datatype containing a sequence of dates representing the 1^st day of every month of the year.

Eg: 2006-01-01, 2006-02-01………. etc. all in (yy-mm-dd) format

But

when I make prediction for next five months, though it makes monthly

predictions the date part for the months are random whereas I expect

the date part to be 1^st of every month.What is the reason for this and how can I overcome it.

Secondly

Predicted

sales values for some time period are negative though I do not have any

negative value in the training data. What is the reason for this and

how can I rectify it?

Thirdly

In

one of your earlier posts you had said that the time series algorithm

does not have any built in time intelligence but uses the key time

column as a time sequence stamp. So If have to make predictions for a

particular time period where the time slice for each time period is 25

days or 50 days etc, then I understand that the input data used to

train the model should be in the same time sequence.

Or

Can I specify the span of the time period according to which the prediction needs to be made?

Basically

how can I use the same time series model to make monthly, yearly,

quarterly, daily or predictions or for custom time period like I have

mentioned above.

1: There really isn't a workaround - you should use your own logic for dates

2: There is a parameter MINIMUM_PREDICTION_VALUE (or something like that) that you can set to 0, and you won't get negative predictions.

3: You should make unique models for each period you want to analyze - the patterns and periodicities are different, and need different analyses.

|||Thanks Jamie