DSXchange

I'm trying to run a DB2(UDB) insert sequentially in a DB2 stage and the system is running it parallel and I get:
"When checking operator: Ignoring requested execution mode [seq] because of conflict with operator's pre-defined mode."

This gives me a deadlock as I'm locking myself.

This, I haven't seen before. I don't have anything in the buildop directory in the project. Where could this 'pre-defined mode' be specified?

Thanks,

Ande

If you job design uses a buildop then you must generate it, which will create files in the buildop directory.

How are you sure you are deadlocking yourself?
Are you using user defined SQL?

1. This job doesn't.
2. Yes - 100% sure
3. Sort of. This particular job was left by an Ascential supplied contractor. It consists of a row generator creating 0 rows of a variable in one column. This streams to the DB2 stage(only 2 operators in job) which does a write to a dummy table and on the close, runs a sql that inserts to a table from another table.

Mode selected is sequential(running PX). Row generator is parallel(doesn't make a difference). Watching the job run in Director, I see that the sequential request is ignored and the DB2 stage runs parallel, causing a deadlock. This runs fine on another machine where the sequential request is not ignored.

BOHICA!

This is a situation where DataStage is being asked to do something that it is really not designed to do.

Do you REALLY want to just copy a table to another table? Then why not do it within EE's framework:

[UDB] -> [copy] -> [UDB]

This is a case of 'why try to fix a complicated solution that does not work the way DataStage is designed to behave?'

Before you argue that your current solution 'worked sometimes' -- your goal, as a developer, is to make the best solution in the least amount of time. My time is very expensive, and if I spend a day investigating and solving this convulted solution, I would be billing someone thousands of dollars. My client would not be happy with that kind of bill.

My client would be much happier with the bill for the 15 minutes it would take to invoke the above solution based on the business requirement I believe you have explained in your second post.

If you need to have a performant solution, just build a stored procedure and call it from outside DataStage.

Enjoy.

I agree..mostly..but you missed the whole point:

The client wants this to work. Since it was designed and coded by a developer supplied by Ascential/Torrent, how could it be wrong....etc.

It's certainly not something I'd come up with or code for a client. This is not a choice, and if you're a contractor with any experience, you've had this situation yourself. You have to make the thing run....

Ande

I am aware and empathize with you for having someone from Ascential who did not meet up with my personal standard of quality development skills.

Please repeat what I stated to the client:

There are no perfect solution. Do you want to spend hours, which equals to a large amount of money out of your budget, trying to find a solution to this mysterious behavior? Or do you want to do it the way it should have been built in the first place, which would take much less time?

It would help if you build the job and test it before you tell them that, as you can point to the finished job as an easier decision for them to make.

If they still want to stick to Ascential's solution, have them contact Ascential to have it fixed.

This is not contributing to a solution to the problem.

Can anyone with a possible solution help?

Thanks,

Ande

Dump it to disk (text file) in an intermediate stage, then read it from there. At the end of dumping any SELECT locks can be released.

I guess that as the DB2 stage is running in parallel and so the close command is being run 'once for each node' and thus causing the deadlock. If so you'll have to force the job to use only one node.

Try running the job with only one node in the DataStage/Configuration/<FileName>.apt ?

Eric wrote:I guess that as the DB2 stage is running in parallel and so the close command is being run 'once for each node' and thus causing the deadlock. If so you'll have to force the job to use only one node.

Try running the job with only one node in the DataStage/Configuration/<FileName>.apt ?

Doesn't an apt change require a server bounce?

ray.wurlod wrote:Dump it to disk (text file) in an intermediate stage, then read it from there. At the end of dumping any SELECT locks can be released.

yes - that would work. Unfortunately, this job runs on the production system with no problem. It's the fail-safe system where it doesn't work. They don't want to touch production that works, but the job has to work on the fail-safe system as that's where they are runing jobs to match production(on an irregular basis).

And so it goes....

Andet wrote:Doesn't an apt change require a server bounce?

Nope.
If you make changes with the Admin client Environemt / Job design (job environment parameters) then the values are taken at run time.

Only if you place variables in the dsenv file do you require a DataStage server bounce.

Anyway after thinking about this I think it may be the number of nodes on your DB2 config (INSTHOME/sqllib/db2nodes.cfg) that will determine the number of times the close statement is executed.

Are both machines connecting to the same DB2 database?

Be careful.

Configurating the db2nodes.cfg will take effect across the entire server.

Standard practice is to include $APT_CONFIG_FILE parameter on every job to allow for granularity in controlling number of nodes. One job flow may work best for 4 nodes with 2 DB2 nodes. Another job flow may not even use any DB2 nodes. Third job flow may grind to a halt unless you do 1 node with 1 db2 node.

If you already do that, great! Just call that job using the 1 DB2 node configuration file from director/dsjob/whatever you use.

It's a messy setup: 2 nodes/servers, each having 2 partitions; 2 partitions on one node is the ODS and 2 partitions on the other node is the Data Mart. The SQL having the problem is copying a staging table from the ODS node to a table in the Data Mart. Both tables are partitioned.
[I did not design this setup nor do I think that it doesn't need to be redone - this is what I have to work with for now].
The job is run on the server with the ODS partitions.