Page 1 of 1
PX informix commit performance
Posted: Sun Nov 14, 2004 12:59 pm
by gcoles
I am running a job that reads a sequential file, goes through a transform stage and updates or inserts rows to an informix database. The performance using 4 cpus is great until the job closes. It appears that it is doing the commit of all the transactions at the end and taking a performance hit. I would like to take advantage of the parallel processing which is excellent until the job normally closes.
I solution to this would help in using PX, otherwise I will be using server jobs.
George
Posted: Sun Nov 14, 2004 3:00 pm
by ray.wurlod
In server jobs you can set the transaction size in the Informix CLI stage (or the ODBC stage, if that's what you're using). A value of 0 will commit all rows sent as a single transaction; a value of 1 will commit one row at a time. Somewhere in between is probably a good value.
Have you considered using the Informix bulk loader stage? Using your own control file gives as much flexibility as you need. Again, I am referring to server job here.
The PX stage type (Informix Enterprise stage) does not give you as much control over transactions, since the load is partitioned. Read Chapter 14 of the Parallel Job Developer's Guide for full information, particularly about the connection mode parameter.
You specified 390 as your platform; are you seeking a mainframe-based answer also?
Re: PX informix commit performance
Posted: Sun Nov 14, 2004 5:08 pm
by gcoles
gcoles wrote:I am running a job that reads a sequential file, goes through a transform stage and updates or inserts rows to an informix database. The performance using 4 cpus is great until the job closes. It appears that it is doing the commit of all the transactions at the end and taking a performance hit. I would like to take advantage of the parallel processing which is excellent until the job normally closes.
I solution to this would help in using PX, otherwise I will be using server jobs.
This is for UNIX only. 390 is a mistake.
George
Re: PX informix commit performance
Posted: Sun Nov 14, 2004 5:22 pm
by gcoles
gcoles wrote:gcoles wrote:Thanks for your reply. I have taken a look at section 14 and unfortunately as you mentioned there is very little. When running in parrallel I noticed I can, for example load 2Million + rows in 2-5 seconds, however, the commit takes half an hour in commit time(or something?) and really slows down the cpus. There could be other issues-like cpu or informix(rollback,etc) setup. If there is a solution to this it would make parrallel processing using informix awesome.
George