Informix Table Read-Locking Problem

paragap · Post by **paragap** » Fri Dec 12, 2003 11:57 am

Hi,
I'm working with DataStage PX 6.0.1.
I am using a Shared Container to insert in an Informix Database (with ODBC stage).
While the job executes, the informix table gets locked (even for read), probably due to exclusive lock. I know this is necessary for database integrity purposes, however, I need this table to be readable by other users.

Is there any particular TRansaction Handling setting or any similar setting that will help me achieve this and circumvent the problem?

Thanks in advance

bigpoppa · Post by **bigpoppa** » Fri Dec 12, 2003 12:23 pm

Are you sure this is a DataStage problem? If you write to the table outside of DS, does the table get a read-lock?

- BP

Teej · Post by **Teej** » Fri Dec 12, 2003 3:06 pm

Also, do you still get the table lockout while using the Informix stage for PX? Is it even possible to use that stage for your infrastructure?

In fact, open question to everyone: Within PX, if provided an option to use the native stage, and an ODBC stage, why use ODBC?

-T.J.

paragap · Post by **paragap** » Sat Dec 13, 2003 6:32 am

I am not using Informix Extended Parallel Server (XPS). The operator provided by PX is for Informix XPS. I am constrained to use Informix 9.30.FC3X1.
This is, apparently not supported by PX in 6.0.1, hence the use of ODBC.

Yes, I think it must be a DS problem( or feature) to lock the table during writes in exclusive mode, and reads are prevented. Once the DS job gets over theres no problem.

I need to override this.

Any suggestions for transaction settings?
Please keep the suggestions coming.

Thanks again, guys.

jungle · Post by **jungle** » Sat Dec 13, 2003 7:58 am

Hi paragag.

In the ODBC stage properties look at the section "transaction handling"; there, set "rows per transaction" = 1.
Doing that way you set Datastage to execute a commit after every single row to be writted.
The setting i have just described is useful, for example, if records coming from the trasformer have to be written with three different trasformations on the same table.
If locks persist, look at constraints in the trasformer for target tables: ensure that the following are not present:

L4.REJECTEDCODE = DSE.NOERROR AND L4.REJECTED

If present, translate that form in a usual sql-style constraint.
And more, if you are skilled enough, you can always manage locks using the before and after section of the ODBC stage (using, for example, locks in share mode)

I hope that tips will help you.

Regards,
RRinaldi

Planet · Post by **Planet** » Mon Dec 15, 2003 5:51 am

When reading from informix, you can set the following in before sql to avoid locking the table.

set isolation to dirty read;

Regards,

Salim Omari
Planet Technologies
Postbus 1042
3800 BA AMERSFOORT
Tel: 030-6588450
Fax:030-6588290
E-mail: s.omari@planettechnologies.nl

paragap wrote:Hi,
I'm working with DataStage PX 6.0.1.
I am using a Shared Container to insert in an Informix Database (with ODBC stage).
While the job executes, the informix table gets locked (even for read), probably due to exclusive lock. I know this is necessary for database integrity purposes, however, I need this table to be readable by other users.

Is there any particular TRansaction Handling setting or any similar setting that will help me achieve this and circumvent the problem?

Thanks in advance

DSXchange