Transaction not available

rdy · Post by **rdy** » Thu Mar 03, 2005 9:37 am

We've recently upgraded our Informix server to 9.40 FC5 64 bit. Now an extract job that previously worked does not work anymore. I haven't tried the job in several months, so I cannot be sure that the upgrade is causing the error.

Here's the story:

1 - I have an ODBC stage that selects from an Informix table. No big deal. But it started getting the following error "Specified driver could not be loaded" when we tried to query the database or even connect to it to import table definitions (always a handy test of a connection).

We were using the Informix DataDirect driver provided with the 6.0 install. We were told by Ascential that it didn't work with the 64 bit Informix.

2 - So we switched the driver to the Informix Wire Protocol driver. That allowed us to see a list of tables in the import table definition, but it times out when trying to actually import the defintions. And now when I try to view data using the ODBC connection I get this error:
"Transaction Not Available"

Ascential told us that since our Informix data warehouse database is non-logging (and has to stay that way) we should change the "isolation level" to auto-commit. But I still get this error.

This seems like one of those times we're barking up the wrong tree....anyone have experience with this?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Mar 03, 2005 11:15 am

I believe that you need to have your logging on to get rid of 'transaction not available'.

For more info, look into

ontape -s -L 0 -A or -B db_name

A - Ansi Logging
B - Buffered Logging

Your DBA will be able to advise better.

rdy · Post by **rdy** » Thu Mar 03, 2005 12:17 pm

Sainath.Srinivasan wrote:I believe that you need to have your logging on to get rid of 'transaction not available'.

Thanks for the response. Are you basically saying that DataStage is not able to read data from a non-logging Informix database?

That sounds a little crazy to me if that is the case. Can anyone confirm this?

ray.wurlod · Post by **ray.wurlod** » Thu Mar 03, 2005 2:44 pm

DataStage is perfectly happy to work with a non-logging Informix database. However, the warning that Informix generates to alert you to the fact is detected by DataStage and logged accordingly - the message coming out if Informix has "warning" severity, and there's unfortunately nothing you can do about it. DataStage's philosophy is to log everything, because you probably need to know if the database has taken the trouble to emit a message. This behaviour is non-negotiable.

A similar circumstance happens in Oracle; if you insert 0 rows, Oracle generates a warning that 0 rows were affected. DataStage logs this.

You can create a Routine to check whether warnings of any other type have occurred in the log, and invoke this in the controlling job sequence.
That is, the Routine ignores the particular warning.

chulett · Post by **chulett** » Thu Mar 03, 2005 2:58 pm

ray.wurlod wrote:A similar circumstance happens in Oracle; if you insert 0 rows, Oracle generates a warning that 0 rows were affected. DataStage logs this.

DB2. And you mean 'update' I do believe.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 04, 2005 12:12 am

Yes, UPDATE.

No, not DB2, Oracle, through Dynamic Relational Stage.

Interestingly, even though the access protocol was set to Oracle the error message came from the BCI.

Code: Select all

DetectClaimFileFormatErrors..ProcessError0040.Count0040: DSD.BCIPut call to SQLExecute failed.
SQL statement:UPDATE "hfFileFormatErrors" SET "ErrCount" = ? WHERE ("ErrorNo" = ?) 
0 Rows affected.

chulett · Post by **chulett** » Fri Mar 04, 2005 6:43 am

Interesting, indeed.

Not having had a chance to play with the DRS yet, I'm surprised it would log an error like that. Well, surprised in the fact that Oracle itself doesn't consider a query that updates zero records to be any kind of a problem. DB2 does, which is why I made my comment.

And what in the world are BCI calls and some very ODBC-looking parameter markers doing in a stage that supposedly used 'native' connectivety when it can?

ray.wurlod · Post by **ray.wurlod** » Sat Mar 05, 2005 10:08 pm

DSD.BCIxxxx function names are from the BASIC SQL Client Interface library. I was as surprised to you to see them from the DRS when the access protocol was specified as "Oracle"; my understanding was that it uses the OCI in such cases. Hmm...

You can find more about BCI by searching this forum, particularly those threads about the 30 day free period with the Data Direct drivers.

rdy · Post by **rdy** » Wed Mar 09, 2005 3:03 pm

I wanted to give everyone an update on this. Our DataStage administrators installed upgraded 64 bit ODBC drivers that Ascential provided. That resolved the issue.

By the way, the actual problem was an error message that caused the job to abort (not just a warning message in the log).

DSXchange

Transaction not available

Transaction not available

Can't ready from a non-logging database?

Update: fixed by updating ODBC driver to 64 bit