RE: dirty read isolation in views
Ok, so the issue is that clients using PC open excel and want to get refreshed data from the database. How accurate does the data in the database have to be? Does it have to be real time? Does it have to be within 5 minutes of the last change? 10 minutes? The simplest solution would be to run a scheduled job that dumps updated data from the tables in the query in to a secondary read only table. The data may not match the OLTP transact data but you will be able to update your spread sheets. If you're trying to use views, then you'd have to use a materialized view (which again has to be updated.) Or if you put an update/insert/delete trigger such that when a row is changed or inserted, the trigger calls a stored procedure which will also insert the row in to your read only table. So that you will avoid the lock conflicts. Yes it duplicates some data, however it gets the job done. HTH -G From: jnebab@idealcut.com To: informix-list@iiug.org Subject: RE: dirty read isolation in views Date: Fri, 19 Dec 2008 10:48:58 -0500 The issue is we have a few client pc's that have pivot tables in spreadsheets that refreshes data thru an odbc connection to an IDS 9.4. The pc's run windows informix csdk v3.50. Whenever table they query on is locked during refresh, windows vb gives a generic error msg. Regards, June Nebab -------------------------------------------------------------- Eliezer A. Nebab, Jr. Database Administrator / Linux and Unix System Administrator Lazare Kaplan International Inc. 19 West 44th Street, 16th Flr. New York, NY 10036 Ph: 212-857-7660 Fax: 212-972-8561 The World's Most Beautiful Diamond® www.lazarediamonds.com From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Jarrod Teale Sent: Thursday, December 18, 2008 3:22 PM To: informix-list@iiug.org Subject: RE: dirty read isolation in views You might be able to establish a trigger on the view that sets the isolation level prior to the query being run and resets it after the query? It would have to be a SPL command, as I don't believe that a view can take an isolation level - it is dependant on the client session. Jarrod Teale Fonterra New Zealand From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of June Nebab Sent: Friday, 19 December 2008 9:14 a.m. To: informix-list@iiug.org Subject: dirty read isolation in views Is it possible to create views with dirty read default isolation -or- run views in dirty read isolation? IDS 9.4UC8 Redhat Enterprise Linux 3 AS DISCLAIMER This transmission and the information contained herein and/or in any attachments hereto and/or in any attachments thereto is privileged and/or confidential and is intended ONLY for the use of the individual(s) and/or entity(s) named above. If you are not the intended recipient, you are strictly prohibited from disclosing, printing, copying, using or disseminating this transmission and/or any such attachments and any information contained in this transmission and/or in any such attachments. ANY unauthorized interception of this transmission and/or any such attachments is a violation of federal criminal law. If you have received this transmission in error, please notify the sender immediately and delete the transmission and all such attachments. DISCLAIMER: This email contains confidential information and may be legally privileged. If you are not the intended recipient or have received this email in error, please notify the sender immediately and destroy this email. You may not use, disclose or copy this email or its attachments in any way. Any opinions expressed in this email are those of the author and are not necessarily those of the Fonterra Co-operative Group. http://www.fonterra.com/ DISCLAIMER This transmission and the information contained herein and/or in any attachments hereto and/or in any attachments thereto is privileged and/or confidential and is intended ONLY for the use of the individual(s) and/or entity(s) named above. If you are not the intended recipient, you are strictly prohibited from disclosing, printing, copying, using or disseminating this transmission and/or any such attachments and any information contained in this transmission and/or in any such attachments. ANY unauthorized interception of this transmission and/or any such attachments is a violation of federal criminal law. If you have received this transmission in error, please notify the sender immediately and delete the transmission and all such attachments. _________________________________________________________________ It’s the same Hotmail®. If by “same” you mean up to 70% faster. http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_broad1_122008