Re: Need help in finding a creative solution.

In article <01bc7e68$67297500$547faac2@msdusr> "Nabil Courdy" <moab@emirates.net.ae> writes: > >I have an application on hand that processes >files coming from a telephone exchange. The >approximate file size is about 500,000 rows. >I load the file into a table, I then generate a >temporary table that sums up the calls per unique >telephone number. I then have to update the up-to-date >table which indicates how much each telephone number >owes up to this processed file. I process about 15 files >a day. The update is simple: > > update amount = amount + new_amount. > >The challenge is that I cannot chunk the transaction. It >all has to be done as one single transaction. If I chunk >the logic, one telephone number can be updated twice. >The logic works in such a way that if there is any error, >it rollsback and continues with the next file. It comes >back to the failed file after there are no more files to process. >I do not want to run into filling up the log file. This is why I'm >looking for a way to chunk. > >I am using an RDBMS other than Informix. However, I am >hoping to find an answer here. > >Nabil Courdy >Senior DBA Two ways come to mind. The first is to use a procedural language (4GL, esql/c or equivalent) and do a COMMIT after every X rows. The secons is, when creating your temp table, add an extra SERIAL column. Then you can do the update in chunks by using a BETWEEN clause in the WHERE part of your SQL statement. Figure out the worst case and write a series of SQL statements, or get clever and generate them dynamically. FWIW. Peter Wiley