Hi,
In our jobs we are using DB2 as a source. It looks like an issue with a BODI process locking DB2 tables after run SELECT statement/
Please suggest me why BODI is locking DB2 tables.
If anyone knows a clue then please let me know.
Thanks & regards
Ritesh
ritesh.kumar07 (BOB member since 2008-11-05)
you should check that no other process already has the table locked before DI starts to run - it seems that any open window or query or user session can cause the table to be locked before DI attempts to extract.
speed12 (BOB member since 2004-09-27)
User which is exclusively used by BODI process is locking the table. I checked with DBA, DI user is culprit for locking.
In DI job DB2 table is used as a source. I am unable to understand why these sources are locked after first run. After completion of DI job mainframe process insert delta into DB2 tables. Mainframe process gives error because DI doesnt release lock on table.
Sources are not containing much data only few thousand data are there
DI will fire select on source. How select statement can lock tables? We are using data services 3.1.
Any thought or suggestion
ritesh.kumar07 (BOB member since 2008-11-05)
what is Driver that you are using ?
see id there are any options in Driver Advanced option related to transaction ioslation level or commit mode, try changing that
manoj_d (BOB member since 2009-01-02)
Thanks a lot Manoj for your prompt reply. This time also your suggestion will help me out.
We are using IBM DB2 client. Through this gateway we create data source and connect DB2.
I couldnt find out any option over there to change transaction isolation level or commit mode.
ritesh.kumar07 (BOB member since 2008-11-05)
IBM DB2 Client ? is this different than IBM DB2 ODBC Driver ?
where did you define your DSN ? using windows Driver Manager or some other tool ?
what is your Datastore Database type DB2 or ODBC ?
is your DB2 on AS400 ?
if you are on 12.1, then try using the Additional Session Parameter option in datastore, type the following command
SET CURRENT ISOLATION UR;
manoj_d (BOB member since 2009-01-02)
For DB2 connection we are using server client architecture.
DB2 connect version 8 is installed on one machine and it works as a server. Here we define DSN.
On another machine DB2 client is installed. This client communicates with DB2 connect.
We create datasource on client as well as DB2 connect.
Lets take an example. Machine 28 is having Data services designer and machine 29 is having job server.
DB2 connect is installed on 29 and work as a gateway. On 28 DB2 client is installed.
We are using datastore database type DB2 and DB2 is on z/OS.
Manoj in data store property there is an ODBC admin option but I am unable to figure out where I have to set addition session parameter.
We are not using ODBC connection. Is it required to configure ODBC admin?
ritesh.kumar07 (BOB member since 2008-11-05)
additional session parameter option in datastore is available in 12.x not in earlier releases of DI
on the job server machine, open the ODBC Driver Manager (from Admin tools) and click on System DSN look for the DataSource name that you are using in DI Datastore, you may have created your Datsource from DB2 tool but it will also add the same to the system DSN
Select that Datasource and click on Configure in the next window, select Adavanced Option and click on Add, from the list Select TXNISOLATION property,click ok now you can select the isolation level you want
manoj_d (BOB member since 2009-01-02)
As per your valuable suggestion I opened ODBC datasource advance option and TXNISOLATION property value. Its value is already set to Read Committed (cursor stability).
I also tried data services 12.1.1.3 datastore property, using data admin option. It opens ODBC data source administration .There are user DSN, system DSN file DSN Drivers Tracing Connection Pooling options. You suggested me to type command SET CURRENT ISOLATION UR; over there.
I couldnt find out any option where I can type this command.
Can you please let me know in which option I have to type this command?
ritesh.kumar07 (BOB member since 2008-11-05)
try setting that value to Read Uncommitted and try from ODBC property
attached is the Datstore property that you can set in Data Services, if you don’t want to change this at System DSN level
DS_Properties.zip (20.0 KB)
manoj_d (BOB member since 2009-01-02)
Thanks a lot manoj for your prompt reply and continuous support.
In your last reply you suggested TXNISOLATION property as Read Uncommited. Currently it is set to Read Committed.
I tried to set Additional Session Parameter and typed following command.
SET CURRENT ISOLATION UR;
It is giving error An unexpected token UR was found …
Screen shot of error is attached for your reference.
ritesh.kumar07 (BOB member since 2008-11-05)
when i tried to set addional session perameter as
SET CURRENT ISOLATION UR;
i was getting error. i replaced UR with READ UNCOMMITTED;
SET CURRENT ISOLATION READ UNCOMMITTED;
In this case following warning is coming instead of error.
Warning:Cannot create stored procedure(s), resulting in non-optimal performance, to improve performance, you must authorize user to CREATE,DROP,and EXECUTE stored procedures.DB2 data source<%1> warning message for operation<%3>;<%4>,SQLState<%5>
ritesh.kumar07 (BOB member since 2008-11-05)
I will have to check this, the acutal error is getting suppressed
what happen if you set this from ODBC Administrator (DSN)
remove this from Datastore Session Parameter, and set this in ODBC DSN in the system and see if tables are still getting locked
manoj_d (BOB member since 2009-01-02)
I set isolation level to READ UNCOMMITED from ODBC Administrator and it is working fine till now.
It is not working from Datastore Session Parameter. I have mentioned error and warning message in my previous reply.
Is it bug ?
ritesh.kumar07 (BOB member since 2008-11-05)
run the following command from DB2 command line, use the same login that you are using the datastore
SET CURRENT ISOLATION READ UNCOMMITTED;
if its some permission issue then you will have to fix that, post the error message that it throws
I think the table getting locked for select, and not getting released from DI is a bug, setting the Isolation level is just a workaround, have seen similar issue with Informix also, changing the isolation to read uncommitted may not be feasible workaround in all situation
manoj_d (BOB member since 2009-01-02)
When I ran SET CURRENT ISOLATION READ UNCOMMITTED; from DB2 command line it gave following message.
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token “READ” was found following “”. Expected tokens
may include: “= ENCODING”. SQLSTATE=42601
Manoj, setting READ UNCOMMITED from additional session parameter or ODBC Administrator is a same thing and it is just a work around.
Can you please let me know what type of negative effects it has?
Our project is in a very critical stage can you please suggest what would be the best solution for that.
Is there any fix or permanent solution for that?
Waiting for your precious advice.
ritesh.kumar07 (BOB member since 2008-11-05)
check DB2 documentation for correct syntax that should be used, and if that is running fine from command line, then use the same from Datastore option
setting it at ODBC level or Datastore level is same, as long as you use this DSN for DI only
regarding the impact of this isolation level, is you will be reading the uncommitted data also in case the table is being modified by other application when you are reading the data from it, if you don’t modify this table when the job doing a select from this table is running, it should be ok
there is one more level for DB2 that is NO COMMIT, check the documenation what it does and if that can be used in your case
manoj_d (BOB member since 2009-01-02)
Can I use SQL transform and fetch data from DB2 table using for read only SQL statement and set READ COMMITTED instead of READ UNCOMMITED at ODBC level ?
ritesh.kumar07 (BOB member since 2008-11-05)
you can try that, only problem is SQL Transform will prevent any pushdown of joins if you are reading from 2 sources, the data may get cached
I think there is one more connection level parameter ACCESS_MODE = READ_ONLY, which can be set using Additional Connection Parameter option in Datastore, the problem I don’t know the actual command for that, try typing AccessMode=1; in the Additional Connection Parameter option to see if this works (revert the ODBC change you did to set the Txn Isolation to read uncommited)
if this work, then you can create a Datastore with this setting anduse that only for source tables
the other option is to file a case with support (since the table is getting locked from DI it should be unlocked also once the job is completed, though DI is not doing it explicitly but there should be a way to prevent this in DI)
manoj_d (BOB member since 2009-01-02)
I filed a case with SAP support . SAP reply is in bold
DB2 8.1 is not supported with Data Services 3.1 .
The first version of DB2 which is supported is 8.2.
If DS supports only 8.2 onwards then DB2 database upgradation will be requried and it would not be an easy task for me to Convince client because it can hamper so many other projects which are using this database.
Now i am confused what to do. Currently i am using READ UNCOMMITED option but this is work around.
ritesh.kumar07 (BOB member since 2008-11-05)