FAQ about the Isolation Level when using a Progress ODBC driver

hjw100

FAQ about the Isolation Level when using a Progress ODBC driver
	Details:
	XA600 XA610 XA800 XA803 Q: How do I check or change the Isolation Level for an existing System DSN? A: To check the Isolation Level for an existing System DSN: - Go to Start > Control Panel > Administrative Tools > Data Source (ODBC) > System DSN - Select the DSN name - Go to Configure > Advance > Default Isolation Level This information is stored in the following registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\OpenEdge10B Q: What is the impact of the Isolation Level? A: Setting the correct isolation level is an important step in the driver configuration process. The ODBC isolation level in effect determines the record locking scheme: - READ UNCOMMITTED ensures that when a record is read, no record locks are acquired - READ COMMITTED ensures that when a record is read a share lock is acquired on that record; the duration of the lock varies. - REPEATABLE READ ensures that when a record is read, a share lock is acquired on that record and held until the end of the current transaction. - SERIALIZABLE ensures that when a table is accessed the entire table is locked with a lock of appropriate strength; the lock is held until the end of the transaction. Q: My System DSN ODBC Isolation Level is blank, what does it mean? Q: I haven't specified the DIL parameter in the ODBC connection string, what value will be used? A: If the value is blank (i.e. not defined) in the DSN Default Isolation Level tab then the default isolation level is REPEATABLE READ If the DIL parameter is not specified in the ODBC connection string then it will use REPEATABLE READ Q: What is the recommended Isolation Level to use? A: For reporting and view only purpose we recommend to set the Isolation Level to READ UNCOMMITTED For updating we cannot make any recommendation as in most cases ODBC should not be used to allow database updates because it would bypass the business logic. If you have such need then this will need to be discussed with an Epicor consultant. Q: What issues can the isolation level cause? A: If any other Level besides READ UNCOMMITTED is used here are the potential issues: - Record locking - "error 0:" e.g If a crystal report using ODBC with REPEATABLE READ crashes then some locks may never be released - Your system may run out of locks and generate an error "Lock table overflow, increase -L on server (915)" that will stop the database In addition if you have many users using ODBC (or Crystal Reports using ODBC) - Performance issues - Report taking longer to run Q: How to change the Isolation Level when using an ODBC connection string instead of a DSN? A: If you use an ODBC connection string instead of a DSN, e.g. when using Crystal Developer 10 or 11, then you need to add the DIL parameter to your connection string e.g. DRIVER=<ODBC Driver Name>;HOST=<Hostname>ORT=<Port>;DB=<Database Name>;UID=<Username>;PWD=<Password>;DIL=READ UNCOMMITTED Q: Where can I get more information on this topic? A: For more information please refer to the Progress SQL-92 Guide and Reference available on-line and the Progress Knowledge Base on the Progress Website OpenEdge 10.0B: Deploying Crystal ReportsTM http://www.psdn.com/library/servlet/KbServlet/download/1180-102-1007/rpcry.pdf OpenEdge 10.1B: Deploying Crystal ReportsTM http://www.psdn.com/library/servlet/KbServlet/download/1983-102-2572/rpcry.pdf OpenEdge DataServer for ODBC http://www.psdn.com/library/servlet/KbServlet/download/1138-102-949/dmodb.pdf