Datastage aborts but tables still locked

vinnz · Post by **vinnz** » Fri Apr 23, 2004 2:18 pm

We have a few jobs that try to insert about 100,00 rows into certain tables in DB2. In this particular instance, what happened was that teh tables size was not defined properly and therefore tha table/tablespace got full and therefore datastage gave an error saying the resource was unavailable. All this was okay but when the DBA tried to rectify the situation he found that there were still a few active threads that held a lock on the table even after the job aborted in Datastage.

This job has an ODBC stage to write to the target with transaction isolation set to 'none'. We did not set any isolation on this job as this was a plain insert into the table for data migration.
Any pointers on why thsi is happening or what we might have been doing to cause this?

source: SQL Server 2000
target: DB2 on OS/390
Datastage: 7.0 on AIX

thanks in advance,
paul

vinnz · Post by **vinnz** » Sun Apr 25, 2004 1:41 pm

One of the guys that I work with suggested that setting the Isolation to Cursor Stability might help. Does this help?

I am trying to find a good book which has more about these settings so anything you guys can recommedn will also be helfpful.

thanks,
paul

ogmios · Post by **ogmios** » Sun Apr 25, 2004 2:39 pm

This is something with DataStage, has always been like that

Nothing helps.

Ogmios

vinnz · Post by **vinnz** » Mon Apr 26, 2004 8:09 am

I am trying to recreate this problem and try the 'Cleanup Resources" option in Director to see if it helps. I am not sure if it pertains to locks inthe database. However, even if it does, I suspect this is a temporary fix since if one of the other scheduled daily jobs fails in production, then it would be terrible to have locks remain indefinitely on the application tables until someone cleans up the resources/locks in director.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 26, 2004 11:36 pm

It's become less clear to which locks you are referring. Is it locks in the source/target databases (which should be released by abort processing in DataStage, according to the API specification for writing plug-in stages), or is it locks in the DataStage Repository and, if so, which ones? This, too, ought not to happen, but there are circumstances where it can happen (timing issues and network dropouts, primarily).

vinnz · Post by **vinnz** » Fri Apr 30, 2004 2:03 pm

The locks that I refer to are actually the locks in the database on the tables. Datastage shows the job as aborted but bringing up the performance monitor on DB2 apparently shows some active threads still holding locks on the table.

I tried setting the transaction isolation to cursor stability/Read Committed but this did not help things either. Once the tablespace filled up, each insert statement failed with a "Resource unavailable exception" as would be expected but the job also slowed to a crawl and remained so until aborted thru director. And the locks remained on the table for inconsistent times and sometimes till the threads were terminated by the DBA.

thanks,
paul

ogmios · Post by **ogmios** » Fri Apr 30, 2004 2:32 pm

It happens sometimes; but mostly in the very serious cases, usually "transaction log full" or "tablespace full". The only way out is to have your DBA do a "db2 force application" on the corresponding uvsh.

For some reason the db2 connection remains open, this has been this way since at least DataStage version 4.x

Ogmios