JDBC Driver with a child view - is it possible?


I have got something working, but I don't know if it's the best / ocrrect way to solve the issue.

I am configuring a JDBC Driver,, under IDM 4.7.1 against SQLServer. For the sake of argument, as the fundamentals stay the same, I have an employee View and I want to synchronize phone numbers (also in a View) which are 0..n. VW_EMP (Employee) will be defined reading directly from the Employee table, and VW_PHONE will be defined to read directly from the Phone table. I 'think' it is possible to configure the JDBC Driver to process the two views (parent / child) just as it would with a parent / child table configuration. However, I cannot get this configuration to work.

I have read the documentation, and the information around synchronization of data held in Views is not 100% understandable (at least by me).

What I have done thus far is to create a single Employee View with the usual single valued column definitions and one column (for the phone numbers) prefixed with mv_. The View just joins the parent / child tables.

This causes the View to have rows = phone numbers X employees.  The SHIM handles this well, as an update to the Employee Table only comes through to the Engine once, even though the view has 'updated' as many times as there are phone numbers.  Also, updating a phone number (adding / removing / modifying) also works, and the JDBC Driver does what it's meant to do.

However, somehow this does not seem correct to me.  If we have 1000 employees and 5 phone numbers per employee, is it really that we need a View with 5000 rows in order to get the JDBC Driver to work.

If it is, then that's OK. But it seems inefficient to me.

Thanks for any feedback.