Re: Loging and No Log Data Base

Thanks Jim. I blush a little for not having given this enough thought before I posted. > jgordon@us.DHL.COM (Jim Gordon) writes: > > Nils, > > This falls into the nasty area of two phase commit requirements in the two > databases. If you have one logged and one unlogged database and you start a > transaction in the logged database when you start action on the remote database > it sends a begin work to that database. This begin work obviously fails as the > other database is unlogged. But if you didn't send it then you wouldn't be able > to rollback your transaction as the parts of the transaction active on the > remote system would have been committed to the database! Yes, there is no way to start a transaction on one database only. This is not a solution for us though. We want our select from the database with transactions to run without any kind of logging at all. Our main problem is selects from the database with transactions and inserts into the one without. > Selects have the possibility of being logged as they may involve building > temporary tables so these must also be excluded. Except if an error along the lines: Could not select due to need of temporary table in logging database where implemented. Or better yet, turn off loggin of temporary tables (those created by some select statements) for this connection. > I'm not suggesting that some of these problems can't be solved by Informix but > I suspect that, as usual, they see this as having reasonably simple work > arounds so it is not a priority for them. I don't see any simple workarounds. Do you have any. Also see below. Our current "solution" is that the problem hasn't yet become very big so we have been able to find ad hoc solutions. The problem will however become significant very soon! What do people with very large marketing databases do? > To make it work at all Informix would have to insist that all SQL statements in > a program that operated across these boundaries acted as singleton > transactions. This way no begin work and commit/rollback work traffic is > required. This effectively means that you are very limited in what a program > can do. This limit would be acceptable for us. However isn't a transaction on one database possible without one on the other, appart from lack of support in the SQL language? Or is it me not thinking enough again? Of course any rollback would happen only on the database with transactions while the other would be as faulty as any database without transactions would in such a case. Now another thing is that logging of our select statement would defeat the whold purpose of the solution I am seeking. It would still be a very large transaction. Whatever this select statement does we want it to run without a transaction even on the database with transactions enabled! That should not be to hard for Informix to implement? > The workaround is to build a table of changes to be made to the remote > databases, unload it and load it into the other database and then update the > other database. This isn't functionally any different from what you can achieve > with the above direct method. It's downside is that it costs the extra time > and extra disk space required for the temp tables and the O/S temp file. And is very hard to put into a program we didn't write ourselves. And is possibly more timeconsuming than a direct insert...select would be. And is hard to write into a client program. It would take to much resources to transfer the data to a PC and back. NewEra's application partitioning would be helpfull here, but isn't currently available on SCO servers that we use. And we have *no* indication from Informix when this will be available. And we can't easily write a stored procedure to do it (no dynamic SQL, no unload statement, no call to externaly written procedures - system call to 4GL or C program only option...). Nils.Myklebust@ccmail.telemax.no NM-data, Dalsbergstien 7, N-0170 Oslo, Norway My opinions are those of my company