Setting database options from report

[beardenb]

I am using CR9 and CE9 connecting to an Informix 9.4 database using the Crystal-supplied Informix driver. Outside of CR, when we are issuing queries from the query environment of Informix (using the server side utility dbaccess), we set two options to improve performance of our queries:

set isolation dirty read;
set pdqpriority XX; (where XX is a priority #)

I would like to be able to set these options in the ODBC connection for each and every report I run against the database. We have tried various strategies but have thus far not been able to figure out how to do from within Crystal Reports.

I wrapped the two statements into a stored procedure and tried calling the stored procedure from a subreport in the report header. Doesn't work. I have tried creating a SQL command such as:

set isolation dirty read;
set pdqpriority 20;
select * from mytable;

and this results in the error message "Cannot use a select or any of the database statements in a multi-query prepare".

I have spoken to Crystal Decisions support and they had no solution.

Not being able to set these options severely hampers performance of our reports. Surely there's got to be a way to do it. I'm hoping someone out there is doing something similar, whether on Informix or another engine.

What it boils down to is we need these 2 statements sent to the db engine before the query is issued to the database.

Thanks,
--Bearden

 

[beardenb]

For the record, we figured out how to set PDQPRIORITY on the client PC where we're running Crystal Reports and/or Crystal Enterprise. It is just a matter of setting an environment variable in Windows.

To edit environment variables in XP, it's a matter of going to Control Panel | System | Advanced | Environment Variables. Then under User Variables for (username), click New and then specify Variable Name of PDQPRIORITY and Variable Value of whatever priority is desired (i.e. 25). Then OK, OK, OK to get out. I believe a reboot is required to make Crystal Reports/Enterprise aware of the new variable.

If you want all users on the machine to have the same PDQPRIORITY, instead of clicking New under User Variables, click New under System Variables and add the variable as described above.

Another option is to set a default PDQPRIORITY by setting it in the profile of the user that starts the database engine. In our case, we're running Informix on HP/UX and the user that starts the engine is informix. So in our case we edited /home/informix/.profile and added the line export PDQPRIORITY=20. The engine must then be stopped and restarted for the variable to take effect.

Still trying to figure out how to set isolation dirty read on the client PC.
--Bearden

 

[beardenb]

Reading through the readme for Crystal Reports 9 service pack 3, patch ID 35586459 contains instructions on how to change the default isolation level. It requires a change to the registry key value IsolationLevel as described below.

The subkey is located at HKEY_CURRENT_USER (or HKEY_LOCAL_MACHINE for global change)\Software\Crystal Decisions\9.0\Crystal Reports\Database\ODBC\IsolationLevel.

Set the subkey to one of the valid values:
1 (Read Uncommitted)
2 (Read Committed) - default
4 (Repeatable Read)
8 (Serializable)

I tested this by setting IsolationLevel to 1 for CURRENT_USER and LOCAL_MACHINE. When I run a report and check the session on the database, it shows isolation level DR for dirty read (a.k.a. Read Uncommitted). I'm connecting to an Informix 9.4 database.
--Bearden

 

[beardenb]

P.S. to my previous post - I have not applied service pack 3, so apparently this capability to change isolation levels was introduced in an earlier fix pack or service pack. I'm currently running SP 2.