Page 1 of 1
Lockout when updating rows using DB2 Connector
Posted: Tue May 10, 2011 6:21 am
by manuel.gomez
Hello guys,
I am having problems when updating information using DB2 Connector.
If I execute the job with a configuration file having more than one node, we get as many tasks raised in the database as partitions we are working with, and they all lock between them. Finally, job aborts after timeout.
When executing with just one partition, job finishes succesfully. I guess I must be doing something wrong, as I dont belive this connector has to be used as "one-node" configuration
Can you guys help?
Posted: Tue May 10, 2011 6:23 am
by chulett
How are you partitioning your data? Hash partitioned over the key fields?
Posted: Tue May 10, 2011 6:24 am
by manuel.gomez
chulett wrote:How are you partitioning your data? Hash partitioned over the key fields? ...
I just left auto partitioning
I will try with method you just suggested
Posted: Tue May 10, 2011 6:28 am
by manuel.gomez
Mmmmmm....I tried with explicit Hash partitioning over key fields (did not perform sort), but got same result: two tasks locking each other
Posted: Wed May 11, 2011 1:17 am
by manuel.gomez
I tried with transaction and array size set to 1, and it obviously works, but very slowly
I also tried to do "Bulk Load", but as variable $APT_CONFIG_FILE is included in my parameter set with all database connection parameter, I just execute this single job with one node (values are taken from prior sequence)
Can DB2 Enterprise stage be used in sequential mode? Any other?
Thanks a lot
Re: Lockout when updating rows using DB2 Connector
Posted: Wed May 11, 2011 7:17 pm
by Matt.C
We are experiencing similar problems with the DB2 Connector stage. We found that applying an index on the db2 table based on the update criteria and applying Hash partitioning worked.
Alternatively we went into the Advanced tab and changed the Execution method to Sequential. (NB when you open the connector properties the default view is based on the Input link, clicking on the Database icon, top left, will bring up the tab you need to change)
Matt
Posted: Wed May 11, 2011 9:17 pm
by greggknight
I had the same issue on SQL server
I added the option maxdup = 0
this option limited the sql to a single process instead of 10
maybe there is a sim option in DB2
Posted: Thu May 19, 2011 6:47 am
by lpadrta
Have you installed the DataStage 8.5 Fix Pack 1? The doc makes it sound like it addresses some issues with the DB2 Connector.
http://publib.boulder.ibm.com/infocente ... 85fp1.html