Crystal Reports XI, DB2 Access with Uncommitted Reads

[Former Member]

We are using Crystal Reports XI Developer for creating reports. We need to configure the SQL to access the DB2 database with Uncommitted Reads. Searching the forum we found references to END_SQL but we cannot find where to set this. We found other references to setting the Windows Registry key HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports\ but we cannot find this key in the registry. Does anyone know how to set up Crystal Reports XI Developer for generating SQL to access a DB2 database with Uncommitted Reads?

[former_member11696 · Accepted solution]

Hello,

Use the registry key, if the key doesn't exist then create it.

We made it harder because setting that option means you are also reading rows of data marked for deletion and could throw your results off marking the info useless. It's better to commit the data.

Thank you

Don

[Former Member]

Hi Don and Marty,

I hope both of you guys see this response. I found a solution to my problem and the seeds to the answer were in both of your responses to me. I just wasn't smart enough at the time to realize it. I will briefly restate my problem and give the solution in the hopes that this thread will help someone else in the future.

Problem:

Our Crystal Reports are written against a DB2 database using a DB2 Unicode connection. Each report must be written against a database view (no direct table accesses) and must access the database with "Uncommitted Reads" (dirty reads).

Solution:

Initially, I wrote the report and specified the data source as DB2 Unicode and selected the required database view. After the report was completely designed, I selected "Database" from the CR Developer menu and then selected "Show SQL". I copied the SQL to the clipboard. Next, I created a new data source connection; DB2 Unicode as before but this time a "Command" connection. In the "Command" window, I pasted the copied SQL and then added "WITH UR" at the end of the SQL. The Command connection provided an identical list of available fields as the original database view. Finally, I replaced all the database view fields in the report with the fields provided by the Command connection. Once the database view fields were replaced from the report, I deleted the database view connection. So now the report is still using the database view, although indirectly through the Command connection. Since the Command connection was specified "WITH UR", all accesses to the database are via uncommitted reads. We verified this with our database administrator.

Thanks again guys for taking the time to answer my desperate pleas for help. I'm sorry I didn't recognize the solution quicker.

Sieg

[Former Member]

If you can use SQL, add "for read only with ur" to the end of the select statement.

[Former Member]

Hi Marty,

Thanks for taking the time to respond to my posting. We have an internal standard that all of our Crystal Reports will use a database view as a datasource. My database administrator has told me that we can't use "for read only with ur" when creating a database view.

Sieg

[Former Member]

Hi Don,

Thank you for your quick response. I still have a couple of questions:

1. How do I know if I implemented the registry key correctly? I expected to see a change when I select "Show SQL" and since I don't, I'm suspecting that I've done something wrong.

2. Is the solution that you gave for an ODBC connection? We're connecting to the database through a database view using a DB2 Unicode connection.

Thanks,

Sieg Dugas

[former_member11696]

Hi Sieg,

You won't see it in our SQL but you will/should see it if you turn on ODBC tracing or if DB2 has a tracing utility it may show it also. Typically it would be better to call a Stored Procedure that would set the Dirty reads up but that is your option.

I don't believe it matters what you are using. But check with DB2 Administrator or help file to confirm this can be done. Or if they have a preferred method use it rather than CR's work around.

thanks again

Don

[Former Member]

Hi Don,

I've run a DB2 trace and see no difference between using and not using a registry key to set IsolationLevel. I placed the registry key in HKEY_CURRENT_USER/Software/Business Objects/Suite 11.0/Crystal Reports/Database Options/ . Is this the correct location? After setting the key, I reboot Windows, start Crystal Developer, start the DB2 trace, run a report, end the DB2 trace.

Here's my situation:

1.) I've been tasked with implementing "Uncommitted Reads".

2.) I'm not allowed to use "Stored Procedures".

3.) Oh yeah... and I'm getting desperate!

Any further assistance you could give me would be most greatly appreciated.

Thanks again,

Sieg

[former_member11696]

Hi Sieg,

Are you using CR 11.0? In Help, About... what version are you using? If not 11.0 then change the registry key, same path just the version is different.

If that doesn't work then the Client may not support dirty reads and your only option is to configure the client/server. If the Client/Server requires "special" configurations then it's out of CR's control and we can't by bass it due to security reasons.

You may want to post or search IBM's site and other sites for a solution also.

If you know the SQL Command to send to the server you could try/verify it works by using a Command connection in CR Designer. It at least may work or confirm if it works at all.

Thank you

Don