nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

DD failing with Referential integrity

bturns

OD 7.2.1
Oracle 11

Hi, I'm running into a weird issue where the same DD script is succeeding on one environment but failing on another. For one environment the DD script is choosing to deploy recognizing that there is referential integrity and it creates IWTEMP tables to sort and insert the rows in the correct order. However, for another environment DD is choosing not to recognize the referential integrity. Has anyone seen this happen before. I've attached the DD config file.

Find more posts tagged with

Comments

Rick Poulin

It would be helpful if you could provide a sample deployment log from each target.

Based on the config file you provided, I'm guessing you're talking about the table referencing itself via the node_parent_id column? My experience with DD is that it'll never sort rows for you at all. It'll insert replicants in the order it finds the in the DCR/XML, and it'll order files in the order that they were provided in a filelist. If you do area-comparison deployments, there is no guarantee on the ordering of the files. I'd call it dumb luck if your deployment relies on such assumptions and actually worked on one of the targets.

bturns

I've attached the log files. In one log you'll notice the use of an IWTEMP table. From the looks of it that is where DD sorts through rows before inserting them. The other log doesn't use the IWTEMP table.

worries

There is foreign key constraint for the column node_parent_id for table wpp_tax_node in the Database schema but not mentioned in the DD deployment file.

Try to match whole description of table in schema and DD file.so that you may come to a clue

Rick Poulin

There is foreign key constraint for the column node_parent_id for table wpp_tax_node in the Database schema but not mentioned in the DD deployment file.

Try to match whole description of table in schema and DD file.so that you may come to a clue

Sorry, but I wholeheartedly disagree with that statement. Whether or not DD's schema matches the database only matters if you're using iwsyncdb. Otherwise, I find that the least amount of info you give DD, the better.

Back on topic, those files you provided appear to be from the same environment, which conflicts with your original question. In any case, it looks like one log is the original insert and the other is the update. The timestamps conflict with that assessment, so I can only imagine someone messed with the IWDELTRACKER and/or your data in between.

IWTEMP tables are only used when DD attempts to reconcile changes between replicants rows (or any table where multiple rows are inserted by the same DCR source), so it's not unusual for there to be no mention of it in some instances of your deployment.

In both cases, you'll notice that there's an insert/update done with a parent key reference to WPP::LFCCNRC2F *before* that row is inserted/updated. The reason one of them fails is because it wasn't already in the table, from the looks of it. Bottom line is you should use some pre-processing (workflow or tuple processor) to reorder your XML with parents before children, such that referential integrity within your XML data is in order.

bturns

The deployments are running from the same TS instance to different Databases. It's the same deployment running to two different DB's with the same schema. If you look at the logs you'll see a different DB connect string in each log file.

Rick Poulin

Ok, so that explains the data discrepancy, but my last paragraph above still stands. The only reason why one deployment works is because it already has supporting data in the table. Wipe that table (or remove WPP::LFCCNRC2F and its children) and it'll stop working, same as the other one. The fix is still building a data preprocessing layer to order your rows properly.

SpanExample.PNG

(Topic_36803)OS_BIRT_2.6.2_BulletText.rptdesign

bturns

This doesn't really make sense because in both cases I cleared the DB before running the deployment. In addition, when deploying the same xml file to a similar table in each DB, one DB creates IWTEMP tables and the other does not create IWTEMP tables. The scenario you've suggested where it creates IWTEMP tables depending on the file type doesn't match this situation where it's the same file type and same destination DB.

Any other thoughts?

Rick Poulin

This doesn't really make sense because in both cases I cleared the DB before running the deployment.

If that's the case, you haven't provided a sample log where the table was initially empty and the deployment went through successfully. If it did go through once like that, then your data must have been different (either in the XML or DB or both) such that the ordering of the inserts respected the constraints -- it's the only explanation because DD will never order the rows in any given table for you regardless of how you configure it w.r.t. relational integrity.

In addition, when deploying the same xml file to a similar table in each DB, one DB creates IWTEMP tables and the other does not create IWTEMP tables. The scenario you've suggested where it creates IWTEMP tables depending on the file type doesn't match this situation where it's the same file type and same destination DB.

That's not what I said at all. What I said is that IWTEMP tables kick into action only if the DCR was previously deployed, as recorded by IWDELTRACKER. Try this as a simple test: in the DB that's showing IWTEMP usage, remove the rows in IWDELTRACKER that match that DCR path and then deploy again. You'll find that IWTEMP is not used (and your deployment may fail because of duplicate rows..).

bturns

Ok, i'll try this out.

Thanks for the clarification.

ShashankJain

I think you should try removing the data from the both the instances and commiting it.

I also agree if there is a foreign,parent key combination it should be mapped in the dd config file.
IWDELTRACKER manages the same relationships.

Then give it a shot.