iSeries Datasource Locking

[jeffgrace]

I have a data source set up on my AS400 (iSeries) and when Cognos accesses it through the client access ODBC driver, it locks the files on the AS400.  Even if the report closes the files remain locked for a length of time.  This causes issues with updating the datasource, reorganizing files, clearing records, etc.  There has to be a way of forcing the ODBC driver to remove the lock when the data has been retrieved...or at the very least, monitor the time that it keeps its hold.  Any direction would be greatly appreciated.

Thanks.

Cognos 10.1.0....
iSeries R7R1M0

[adam_mc]

In the ODBC Data Source Administrator:

Select your AS400 Connection (ours is under the System DSN Tab), then click the Configure button.
Then, on the Server Tab, what is your Connection Type set to?

Ours is set to "Use ODBC access mode, Call allowed" and currently we are not experiencing the issue you describe.
However, we do not link directly to many AS400 source files from Cognos FM (and especially not to those that may cause the problems to the business that you outline).

I would think, but have not tested, that the "Limit to Read-Only (SELECT statements only)" would prevent any locking.

From the Help within Server Tab:

Connection type

Allows you to specify the connection type to one of the following:

Use ODBC access mode
Use ODBC access mode, Call allowed
Limit to Read/Call (SELECT and CALL statements allowed)
Limit to Read-Only (SELECT statements only)
Notes:

The ODBC access mode setting is controlled by the application and corresponds with the SQL_ATTR_ACCESS_MODE connection attribute.
The default selection is Use ODBC access mode.

[Michal_Husaria]

Hi there,
silly question, but did you put "for read only with ur" in your queries? We're also using as400 and I have not seen any problems like yours...