Been using CF7 for years, but was asked by manager if I could get CF to do this. Coworkers using Crystal Reports haven't been able to do this. I've tried with CF7, but am getting either time-outs or non-specific error messages. So need some advice please.
First, my CF7 is running on a Windows 2000/SQL Server 2000 server. But the data is in 2 other servers. Have one set of customer data in an Oracle DB on Oracle Server (about 50,000 records per month). But Oracle DB is missing 2 fields that are needed to create the reports we need.
So there is another server, an IBM DB2/UDB server, that contains 1 million customer records per day. This DB has the 2 fields that are needed. I'd also like the query to run for a month at a time. So it seems for each of the 50,000 customer records in Oracle, I need to sort and match against 1 million DB2 records. In the end, I need the 50,000 Oracle records with the 2 needed fields from DB2, added to them, so I can run counts and reports based on the 2 needed fields. No data is needed from the Windows/SQL Server that CF7 is running on.
So I need to match the customers from Oracle server with the customers from DB2 server, based on 4 items: customer name, city, date, and flight number. As far as I know, with any single <CFQUERY>, you can only query one data source (or one server).
So I wrote one CFQUERY to pull these 4 fields, plus a few more, from the Oracle server. Sorted it by date, city, flight and customer name.
Then wrote second <CFQUERY> to pull these 4 fields, plus the 2 needed fields, from the DB2 server. Sorted it same as first query, by date, city, flight and customer name.
A few goofy things...Oracle stores the flight number as integer, 1 to 4 digit. DB2 stores as 4 char string with leading zeros. Oracle stores customer name in 2 fields, last and first. DB2 stores in single field, in format: "LastName/FirstName". So wrote the convert and concat SQL so these Oracle variables could match the DB2 variables.
Then wrote a Q of Q to include the needed variables. In WHERE statement, did this
WHERE Oracle.date = DB2.date AND
Oracle.city = DB2.city AND
Oracle.flight = DB2.flight AND
Oracle.name = DB2.name
I'm not using any GROUP BY statements. I either got time-outs, or non-specific error messages. First, is this do-able? Is my approach correct? I realize sorting and matching on this many records, from 2 totally different servers, will not be fast. I just need to find out how fast (or slow) the process will take.
Should this work? Am I asking QofQ to do too much? I'm not even sure where the processing (sorting/matching) occurs. Is it all being done in memory on the master SQL Server? Maybe I'm running out of memory to sort this many records. I've even tried a "maxrows = 1" on the first (Oracle) query, just to see if the job would match 1 record, and how long it took. Then slowly increase the "maxrows" to see how much longer it takes. But still can't get a match.
Appreciate any advice from anyone who had tried to pull data from 2 different servers, and match them up based on pure sorting and matching (no relationships, no customer IDs, no links between the data other than what's in the QofQ, and the sort/matching).
I don't want to spend any more time on this, unless you all feel this should work, even with this many records. Thanks for help/advice.
Gary
1
Reply
AdChoices