Re: Loging and No Log Data Base

On Oct 29, 7:31pm, Nils.Myklebust@ccmail.telemax.no wrote: } Subject: Re: Loging and No Log Data Base } > CRAIG@CHEMISTRY.CHEM.UTAH.EDU writes: } > } } > } No either way will not work! } > } } > } When I go from the No logging database to the logging data base I get: } > } 569: Cannot reference an external database with logging. } > } } > } When I go from the logging database to the No logging database I get: } > } 568: Cannot reference an external database without logging. } > } } > } Informix I can understand not allowing this for update,insert, delete etc... } > } yet for a general select under ISQL or DBACCESS. What up? } > } Does any know if this will also occur in the 7 online engine? } > } } > } > The situation behaves the same under OnLine 7.11.UC1. (-569/-568 errors) } > } > --Craig (craig@chemistry.utah.edu) } } Could someone perhaps shed some light on this one. Is there some generic } problem with this, or is it Informix that has problems with the way something } is designed. } I can't understand that a simple select from one database to the other could } possibly be a problem. But also updates, inserts, deletes should be possible? } Each database has it's own transaction logs, so why would any of these statements } be more of a problem comeing in over some connection from another database, than } when they arrive from some application? } } We need this kind of access because of problems with very large insert and } update statements in a desition support like situation. We want end users to } be able to select customers from our OLTP database (with transactions) via } selects on a series of tables with different data. They may find up to several } hundred thousand customers, and we need to insert some data (codes/dates) for } each of these to keep track of exactly which customers where selected (and } got some offer or whatever). } With transactions turned on we get all kinds of problems with log size, locking++ } that would easily be avoided if we could simply turn transactions off in the } database we insert the data into. (The transactions aren't needed because any } of the selects could so easily be redone in case of a database crash - and beeing } Informix it hardly ever crashes.) } } There must be a lot of other users in similar situations? } If I could at least get an explanation of the nature of the problem it would } be nice. If it isn't a problem, simply a decision on Informix's part we could } nag them much harder on this. } } Nils.Myklebust@ccmail.telemax.no } NM-data, Dalsbergstien 7, N-0170 Oslo, Norway } My opinions are those of my company 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! Selects have the possibility of being logged as they may involve building temporary tables so these must also be excluded. 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. 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. 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. Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!