Re: Help with Oracle -- DB2 gateway!

"Sreeni Karpurapu" <karpurapus_at_detroitedison.com> wrote:
> We are having problems with Oracle DB2 gateway! Our Oracle Forms
>application is interfacing with a DB2 database through a Oracle-DB2
>gateway. We are having lot of locking problems(table level locks). Even
>if we do a select though SQL*Plus locks get established and they don't go
>untill we do a COMMIT or ROLLBACK. Some *THREADS* are still persistant on
>the DB2 machine and they have to be removed manually.

As pointed out by another poster, DB2 locking and thread management are not directly under the gateway's control. The gateway is a DB2 CAF application and is subject to the same considerations as other DB2 applications. If you write a simple DB2 COBOL program and bind its plan with CS (see below), you will find that the program's SELECTs take read locks on the DB2 tables and hold them until commit or rollback...just like the gateway.

One thing you can try is to rebind the gateway's DB2 plan with a different isolation level. Chances are you bound it with CS (cursor stability) when you installed the gateway. If your Oracle applications can tolerate reading uncommitted updates to the DB2 tables involved, you can rebind the plan with UR, which avoids most read locks altogether. I think you have to be at V4 or higher of DB2 to use UR binding. Check the gateway documentation for discussion of plan binding options and their implications.

> Further we have some not null columns in some of the DB2 tables. And
>when we try to insert data with spaces ' ' into those columns, it would not
>accept spaces! It has to be some other character!

Hmm. I don't know what to suggest here. It sounds like something to do with differences in treatment of character data. I don't have a copy of the gateway doc on hand at the moment but you might want to browse it since issues of this sort generally are covered. If the behavior appears to be a defect, you should report it to Oracle Support.

/b

--
Bill Manry - IBM Products Division - Oracle Corp. USA
The above statements and opinions are my own and do not
necessarily represent those of Oracle Corporation.
Remove the "." from "B.Manry" to email me.