Is there a better way to debug primary key collisions?

[eknight]

Right now we just get very basic logging information. Basically just that a primary key collision occured and in which column. Wouldn't it be possible to log at least the current row that triggered the collision?

[wyconian]

You can change the level of logging and audit from the properties of the build.

It's not always great and can be a bit misleading so you need to be careful.

Good luck :-)

[eknight]

I'm pretty sure I have maximum logging turned on.  ???

Do you get the row data for rows causing key collisions? It would be enough if I got the key value that caused the collision.

[wyconian]

Not reliably :(

When I come against a primary key constraint I normally do 1 of 2 things.  Either run the sql from the query against something like SQLTerm (or Toad) with a count against whatever the key is to try to find the duplication.  Or if the build is a more complicated disable the pK constraint, run the build into a temp table and do the same thing.

That should point you in the direction of what is causing the consttaint issue

[eknight]

Ha ha, that is exactly the process I am hoping to improve (I also use Toad).

I just seems like DS should have easy access to the specific row info because it just attempted an INSERT/UPDATE with specifically that row. It strikes me as somewhat trivial to output the key values along with the DB2 error message.

[wyconian]

Ah yeah sorry don't know a better way of doing it :-(

[MFGF]

If it's a common issue, you could think about adding a transformation model derivation to populate the build log file with the key value(s) from the build - use the LogMsg() function. You will need to enable user logging when the build is run to see these. When you get a key collision on output, it will be for the last set of key value(s) added to the log.

Just a thought...

MF.

[eknight]

When you get a key collision on output, it will be for the last set of key value(s) added to the log.

Yeah, that would be a good solution for development, but I couldn't leave that level of logging on in production. Unfortunately we already use user logging for smaller logs so I couldn't just turn off user logging when it goes to production. I'd have to manually remove the calls to LogMsg() or always check the runtime environement before calling LogMsg(), but at that point I'm getting into more effort and complexity than the problem is worth.

Thanks to both of you for the help though

[eknight]

I have just discovered in DataManager 10.2 the option to set the environment variable DS_UDA_BULKWRITE_ROWS=1. When I do this I get an excellent output about key collisions, because it displays the full INSERT statement that failed.

However when I read the IBM docs about this variable:

http://publib.boulder.ibm.com/infocenter/rentrpt/v1r0m1/index.jsp?topic=%2Fcom.ibm.swg.ba.cognos.ug_ds.10.1.1.doc%2Fc_ds_uda_bulkwrite_rows.html

It seems to imply that setting this value to 1 can have performance costs.

Does anyone have any experience with setting DS_UDA_BULKWRITE_ROWS=1 in a production environment?

Or can anyone explain to me what exactly an "array write" is and what sort of performance costs I can expect to have when I disable it?

Thanks,