It's been a very long time, since i have blogged on SCN. In my current project, we are using PI JDBC sender adapter and we faced few issues in the course of configuring and testing this interface. I believe sharing it may help the community.
The JDBC sender adapter is configured to connect to Oracle 11g(version 11.2.0.1) hosted on Windows 2008. We are executing a select query(with a join on 4 tables) along with rownum(to restrict the number of rows that get picked up, since we expect high volume of data). PI JDBC adapter is polling these tables every 2 minutes and there is an external process that writes to these tables all through the day for PI to pick up. Initial testing of this interface went fine with no issues. Later we started noticing critical data integrity issues. I will try to list them and how we handled it. Now, don't expect any great stuff and start saying what's great about this :smile: . It may sound trivial to few, may have been discussed already here in the forums, but i am writing this assuming it could help a few. Given this disclaimer, let us see what we faced and how we went about resolving them.
Issue 1: Like i said earlier, we are using rownum to restrict the number of rows getting picked up by the adapter. So, we have identical rownum <= n in our SELECT & UPDATE statements. We expected that UPDATE statement will update the same exact number of rows what was picked up by the SELECT statement. This was not the case, UPDATE statement happened to update more rows than what the SELECT statement picked up. Now many of you know there is an SAP note that talks about this - SAP Note 831162. We did find out that the default isolation level for the Oracle database we were using was set to READ COMMITTED(defalt isolation level). So, we changed the transaction isolation level for the JDBC sender adapter to "SERIALIZABLE" and we could see that the issue was resolved.
Just to give more information on how the Serializable setting works, if you observe the NWA logs, you will see that whenever PI senses that it may update more number of rows than what was picked up by the SELECT statement(meaning the application that writes to the Oracle table(s) has added few records after the SELECT statement was executed by PI, but before the UPDATE statement was executed), it will fail with a ORA-08177- Can't serialize access for this transaction and will re-try processing in the next polling interval. The current processing will be rolled back.
Issue 2 - After a few days, we observed that the JDBC adapter was continously failing to pick up records from the Oracle tables. We could see the SQL exception ORA-08177- Can't serialize access for this transaction in the NWA logs & in the commn. channel logs starting to happen very very frequently.
NWA log screen shot
JDBC Commn. Channel screen shot
This was puzzling because, there was no other process writing data to these tables and even in that time period, PI channel logs showed this dreaded ORA-08177. We did check the Oracle server and could see that no open sessions or locks were held in these tables.
We checked Oracle documentation and could find some interesting facts - Oracle Database stores control information in each data block to manage access by concurrent transactions. To use the SERIALIZABLE isolation level, you must use the INITRANS clause of the CREATE TABLE or ALTER TABLE command to set aside storage for this control information. To use serializable mode, INITRANS must be set to at least 3. This must be done at the CREATE TABLE/CREATE INDEX time to ensure that it is set for all blocks of the object. Also rebuilding table/index should do the trick. I have just given an excerpt of what is mentioned in Oracle documentation. You could google and read more if you are interested and happen to be building PI interfaces using Oracle database.
In addition to above, Oracle has introduced a new feature called ROWDEPENDENCIES from Oracle 10g version, which makes every row independent. You could check this link - http://www.devx.com/dbzone/Article/41591/0/page/2 to get some additional details on how rowdependencies works with SERIALIZABLE setting in the JDBC commn. channel.
Solution: Based on the above facts, we dropped the tables we were using in the Oracle database and re-created them with INITRANS set to 3 and with ROWDEPENDENCIES. We could see a dramataic decrease in the occurence of ORA-08177 in the JDBC commn. channel logs. The interface seems to be working fine.
I hope the above information may come in handy and could make you pause and think when you are configuring few advanced settings in the JDBC commn. channel(s).
- SAP Managed Tags:
2 Comments
