Topic
  • 2 replies
  • Latest Post - ‏2013-02-11T06:24:42Z by SystemAdmin
SystemAdmin
SystemAdmin
1245 Posts

Pinned topic How to control database instertions in streams

‏2013-02-08T05:08:11Z |
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,
Neha
  • Kevin_Foster
    Kevin_Foster
    98 Posts

    Re: How to control database instertions in streams

    ‏2013-02-08T17:01:56Z  
    I'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.

    -Kevin
  • SystemAdmin
    SystemAdmin
    1245 Posts

    Re: How to control database instertions in streams

    ‏2013-02-11T06:24:42Z  
    I'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.

    -Kevin
    Thank you so much for the reply. This is of help to me.

    Regards,
    Neha