Lost RPC connection to remote Agent error with Oracle connected to Informix

Question

I am having a problem with a simple data importer / updater that takes data from Informix, inserts it into a table in Oracle, and updates a flag in the original table. The set up is this

Pro*C program calls a PL/SQL procedure
The procedure 
  opens a cursor on Informix, 
  loops through each row
    insert the data into an oracle table
    commit
    update the "new data" flag in Informix (i.e. stop a re-import)
    commit

The two commits are necessary to stop a particular Oracle error (I forget which one, but it is because the tables being updated are in two databases and a single commit can't handle it). This code works fine for a small number of records (5-10) in the source table, but falls over when there are > 1000. The alert log in the Oracle log directory shows an error "Lost RPC connection to remote Agent". There is no corresponding error in the Informix directory, so I suspect the driver is at fault. We are using Oracle 10.2 with the unixODBC driver on Red hat.

Does anyone have any ideas for a workaround or indeed how to fix it? There does not seem to be any useful information about this error message - most pages just say something along the lines of "It's not an Oracle problem".

Answer 1

Normally, a COMMIT closes open cursors. You might need to make the cursor on Informix a cursor WITH HOLD (ESQL/C jargon - you'd have to translate to ODBC). You might get a few rows working because of buffering; a single fetch operation might collect several (maybe more than 10) records from Informix, and you only notice the problem when it comes time to go back to the database for more - when the cursor is closed.

I'm not completely comfortable with this as an explanation; the UPDATE plus COMMIT should mark the cursor as closed.

Assuming you are using an Informix ODBC driver (as opposed to some other vendor's ODBC driver for Informix), then you might be able to debug this by setting SQLIDEBUG=2:xyz in the environment before the program starts. This should produce a file whose name starts 'xyz_###' where '###' is some series of digits, possibly hex digits. This file can then be interpreted by sqliprint. It will show you the information conveyed between your application and the Informix database server - and may well show you the grouping of data sent over the wire, etc.