Re: iSeries DB2 SQL Updating via Linux ODBC Connection

I finally figured this out this morning. The problem was in the odbc.ini on the Linux Server. There is a setting called "CommitMode". I had to set this to 2; previously set to 0.

Because I wanted to know what this attribute did, here is a better explanation"

The*/CMT/*keyword, (can also be specified as*/CommitMode/*), identifies the isolation level that should be used when connecting to the iSeries. The isolation level refers to the level of locking and commitment control that you will have when executing SQL statements against the iSeries. A value of*/0/*specifies the isolation level of *NONE on the iSeries. This isolation level means that you will attach read locks when reading data, but these locks will disappear as soon as your cursor moves off the current record. This isolation level is appropriate for reporting programs or programs that will only read data. Any inserts, updates and deletes can not be rolled back in this isolation level and will be committed immediately. Note that this is the only isolation level that can be used when working with non-journaled tables. A value of*/1/*specifies read committed, (also referred to as cursor stability or *CS), isolation level. A value of*/2/*, (the default value), specifies read uncommitted, (also referred to as *CHG), isolation level. A value of*/3/*specifies repeatable read, (also referred to as *ALL), isolation level. A value of*/4/*specifies serializable, (also referred to as *RR), isolation level. If you do not specify this keyword, the driver will default to *CHG isolation.

Source: http://www.sqlthing.com/howardsodbciseriesfaq.htm

Hope this can help someone in the future!

-JA-




Jason Aleski / IT Specialist

On 4/8/2016 9:39 AM, Alejandro Olvera wrote:

Check your odbc.ini connections settings (CommitMode) add it if it's not
there.

https://www-304.ibm.com/support/docview.wss?uid=nas8N1017566



On Fri, Apr 8, 2016 at 9:12 AM, Bryan Dietz <bdietz400@xxxxxxxxx> wrote:

One thought, is the file on the IBM i journalled?

Bryan



Jason Aleski wrote on 4/7/2016 5:29 PM:

I have a Linux Server (PHP/NGINX) which has an unixODBC connection to
our iSeries. I can run SELECTs all day long, but when I attempt to run
an UPDATE, it tells me the connection is set to read only.

*Warning*: odbc_exec(): SQL error: [unixODBC][IBM][System i Access ODBC
Driver]Statement violates access rule: Connection is set to read only.,
SQL state 37000 in SQLExecDirect

in*/usr/share/nginx/html/mass_address_change/includes/adodb5/drivers/adodb-odbc.inc.php*on
line*540*
37000: [unixODBC][IBM][System i Access ODBC Driver]Statement violates
access rule: Connection is set to read only.


I've looked in my odbc.ini and odbcinst.ini, but do not see anything
that references the readonly mode. Has anyone ran across this?

-JA-



Jason Aleski / IT Specialist


--

This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing
list
To post a message email: WEB400@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/web400.