IBM WTX Pass DB PK as FK to separate table

Question

Using IBM Websphere Transformation Extender...

I'm working on a scenario where there are parent/child relationships and the input data and I want to maintain that parent/child relationship in two database tables. The data is similar to this:

Parent/Child Indicator,Fname,Lname,Age

P-Bob,Smith,32
C-Sally,Smith,4
C-Tommy,Smith,6
P-Chris,Gomez,65
C-Anna,Gomez,32

I want to put all the parents in their own db table and the children in a separate table.

My thought as to how to accomplish this would be to create a functional map for the parent rows, capture it's data (along with db auto-generated primary key) and insert into the parent table. Then within that functional map, I would create another functional map for each child row and pass the PK from the parent table to establish a relationship between the two tables.

Is that the correct approach for a relational database? If so, I can't seem to get it working correctly because I can't seem to change the output cards within the nested functional maps to point to the right db table and insert correctly. Anyone with any thoughts on this situation would be greatly appreciated. Thank you.

Answer 1

You can do the binding of the data on the type tree that reads it.

Assuming your example, your type tree can be something like:

Family (Group)[0:S](
    Parent (Group)(Initiator P, Fname text, Lname text, Age number)[1]
    Child (Group)(Initiator C, Fname text, Lname text, Age number)[1:S]
)

Then you can easily process each family member on a functional map like:

=F_LoadFamily(Family, DBLOOKUP(get auto primary key))

Inside the functional map you have several options:

• Call one stored procedure which will do the inserts on the respective tables depending if it is a parent or a child.
• Call another functional map depending on the record you want to insert

etc.