My database deign is such that i have a master table and 5 other child tables linked with a foreign key. this is to maintain referential integrity in data.
I have 6 such streams of which 1 stream is to be inserted into master table. This is the stream which needs to get inserted into database first followed by streams corresponding to child table
But now it does happen that child stream tries to insert into database before the parent record is inserted thus violation referential integrity.
I have set "transaction_batchsize" and "rowset_size" both equal to 1 for master stream which needs to insert first assuming that each tuple would gets inserted as it is processed.
And for the child streams i have set the values to 40 assuming that 40 records would be inserted together and in the meanwhile master record would be inserted.
But it looks as if these parameters do not meet my requirements.
Can someone please suggest how do i control insertion of data into database i.e which streams data gets inserted first should be controlled by me.
Thanks a lot,
This topic has been locked.
2 replies Latest Post - 2013-02-11T06:24:42Z by SystemAdmin
Pinned topic How to control database instertions in streams
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Kevin_Foster 120000GMHC98 PostsACCEPTED ANSWER
Re: How to control database instertions in streams2013-02-08T17:01:56Z in response to SystemAdminI'd probably experiment first with the Delay operator on the child streams. If you know your data arrival patterns and your application latency then that might be all you need to get the parent record safely into the database ahead of the children.
If that doesn't work (due to irregular latency, etc.) then you might need some combination of Custom and Switch operators that control when the child tuples are allowed to arrive at their ODBC operators.