How to combine data from several DBs? lazarus / 16-Databases ---------------------------------------------------------------------- Vodnik · 2025-01-08 12:45 My application works with Informix DB, creating different reports from it. DB is contolled by Call Center and it is not allowed to make changes in it from outside. Now I want to improve reports with additional info that is not contained in the main DB, but in huge telephony log files. For that purpose I've created a tool that get data from log files and put them into SQLite DB. Tables in both DBs have an ID field that can be used for data linking. I'm using DBgrid control for reporting aim in my application. Can you recommend an approach how to deal with two different DBs? (Well, I have a couple of straightforward ideas, like: - get data from Informix, put it into TEMP table in SQLite, then select with join from 2 tables by SQLite, - or built DBGrid from Informix, providing spare column, then fill that column with data from SQLite) I just don't want to invite a bicycle, missing some good solution that may be already available. ---------------------------------------------------------------------- Zvoni · 2025-01-08 13:12 AFAIK, no other way you already described. I'd prefer Solution 1 (Report based on temp-table in SQLite) ---------------------------------------------------------------------- TRon · 2025-01-08 13:22 For local databases I use attach database but I do not know if that works icw with informix server. ---------------------------------------------------------------------- Zvoni · 2025-01-08 13:34 Quote from: TRon on January 08, 2025, 02:22:34 pmFor local databases I use attach database but I do not know if that works icw with informix server. AFAIK, that only works with "other" sqlite-DB's Don't know if Informix itself offers something like "federated tables" as known from MySQL (which is basically the same with "attach database" in SQLite). Probably would need a (ODBC) driver ---------------------------------------------------------------------- Thaddy · 2025-01-11 12:58 You can use an informix client locally and use any of the dml sql to create reports over multiple informix databases. That looks like the best solution. See the informix documentation: https://www.ibm.com/docs/en/informix-servers/12.10?topic=rrda-sql- statements-that-access-more-than-one-database That way you do not need to rely on logs or imports and your architecture is not affected. If that is not an option, then, indeed, your option one is the next best solution, but in a professional setting (which informix always is, it is prohibitatively expensive although there is a "free" developer edition) I would do what I described and keep it all informix. Both informix and oracle have very powerful options to query over multiple databases, even domains and clusters, much more so than other database engines. I am more familiar with the Oracle family and there it is not much of a question and relatively easy. I suppose it is the same with Informix. ---------------------------------------------------------------------- Zvoni · 2025-01-13 07:13 Quote from: Thaddy on January 11, 2025, 01:58:04 pmYou can use an informix client locally and use any of the dml sql to create reports over multiple informix databases. That looks like the best solution. See the informix documentation: This only applies if he's allowed to do "changes" on the Informix-DB. My guess he's not allowed or he isn't a full dba for the Informix-DB QuoteThat way you do not need to rely on logs or imports and your architecture is not affected.How do you figure that? He stated clearly, that he needs Data from some Logs, which apparently is not available anywhere else (guessing here) QuoteIf that is not an option, then, indeed, your option one is the next best solution, but in a professional setting (which informix always is, it is prohibitatively expensive although there is a "free" developer edition) I would do what I described and keep it all informix. Both informix and oracle have very powerful options to query over multiple databases, even domains and clusters, much more so than other database engines. I am more familiar with the Oracle family and there it is not much of a question and relatively easy. I suppose it is the same with Informix. Correct. If he can go "all Informix" that's the way to go. It definitely is NOT a problem to query across multiple Databases/schema, as long as they are on the same server/connection. I'm doing this every day on our IBM DB2. That said: If my guess is right (he's not full dba), maybe their IT can give him his own schema with corresponding access-rights (full CRUD). Then he could import his logs there, and then it would be "all Informix" ---------------------------------------------------------------------- Thaddy · 2025-01-13 10:44 I was guessing too. It is definitely open for interpretation. To create a report, he only needs read access of course. @vodnik Can you report back? ---------------------------------------------------------------------- Vodnik · 2025-01-13 13:18 Thank you for ideas. Well, I have full admin rights over Informix DB. I can create additional tables or even DBs for my own aims, but that breaks the rules of Call Center vendor. They have their own ideas for DB management, replication, backup, etc. They also are very strict with DB load; if I start a SELECT statement that will run for 10 minutes, Call Center operation may degrade. This makes even read-only access dangerous. So, though pure Informix solution is the best from any point of view, I prefer to stay on a safe position. I'm 99% sure that everything will work fine even if I will add some tables (system is not under heavy load), but, in case of problem, vendor will not provide any support. Lazarus provide no direct connection to Informix DB, so I work via ODBC. There is one more idea from Informix guru: set up DB replication (maybe partial, only the tables I need), then work with replica copy. Even if replica becomes broken, this should not affect the Call Center operation. Replication is not prohibited by vendor. But setting up Informix replication is a challenge for me... ---------------------------------------------------------------------- Thaddy · 2025-01-13 13:30 Quote from: Vodnik on January 13, 2025, 02:18:50 pmThere is one more idea from Informix guru: set up DB replication (maybe partial, only the tables I need), then work with replica copy. Even if replica becomes broken, this should not affect the Call Center operation. Replication is not prohibited by vendor. But setting up Informix replication is a challenge for me... That sounds like the way to go, but note that replication also affects the speed of the main database a bit, not much, but can be noticable. ---------------------------------------------------------------------- Zvoni · 2025-01-14 09:17 Quote from: Thaddy on January 13, 2025, 02:30:11 pmQuote from: Vodnik on January 13, 2025, 02:18:50 pmThere is one more idea from Informix guru: set up DB replication (maybe partial, only the tables I need), then work with replica copy. Even if replica becomes broken, this should not affect the Call Center operation. Replication is not prohibited by vendor. But setting up Informix replication is a challenge for me... That sounds like the way to go, but note that replication also affects the speed of the main database a bit, not much, but can be noticable. Not if the payload is negligable or in the "usual" User-Range, which is how i understand TS. If a User-Action takes 2 seconds, it will also take around 2 seconds to replicate to a slave, but that should actually happen at the same time (more or less), so the end result is NOT 4 seconds, but something like 2.1 seconds I remember doing a re-import from a big SQL-Dump on a replicated MySQL-Server (Re-Import on MySQL-master, with immediate replication to MySQL-slave). The time-difference between Re-Import MySQL-master done and Replication to Slave done was maybe 2 seconds (The re-import on the Master taking some 10 minutes). If those logs he has to import are huge, i'd even go so far and place that table on its own harddisk on the Slave-Server to avoid hardware- concurrency