Hi Phil,
As far as I'm aware there is no way of generating a key like you want to
during a shred activity. Unfortunately, shred is not very flexible. It's
basically just an element to column mapping device. If it were only one
table being shredded into then you could use an identity column or a
trigger. But with child tables needing the same migrated key I'm not
seeing how it could be done without the potential for not getting the same
key. Such as a before insert trigger on the child table that queries the
parent table to get the latest key (the one just generated during insert by
the shred). But with a high transaction rate this could get ugly. If
possible (and it's probably not), it seems the best thing to do is generate
the key prior to formulating the XML. Then shred can just map it in.
Sorry I don't have better news, but maybe someone else has conquered this
one,
Jay
Jay Reavill
[EMAIL PROTECTED]
IBM Global Services
Tampa, Florida
Tel: (813) 356-5317, Tie Line 8-697-5317
-----------------------------------------------------
Happiness is not around the corner.
Happiness is the Contour (SVT) of the road.
-----------------------------------------------------
The early bird gets the worm,
but the second mouse gets the cheese.
-----------------------------------------------------
Philip Nelson
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
om> cc:
Sent by: Subject: [DB2EUG] XML Extender Help
Please
[EMAIL PROTECTED]
ash.org
10/01/2003 08:05
AM
Please respond to
Philip Nelson
Guys,
I'm working on a system which has to receive XML documents and shred
them into a DB2 database (UDB on AIX V7.2). I've got the DTD and sample
documents supplied to me by the developers. My task is to produce a
database schema and a DAD to allow the shredding to take place.
I've got lots of questions ...
Let's say the XML structure was -
<PERSONAL_DETAILS>
<NAME>Someone</NAME>
<AGE>50</AGE>
<ADDRESS ADDRTYPE="Home">
<ADDR1>Home Street</ADDR1>
<ADDR2>Home Town</ADDR2>
</ADDRESS>
<ADDRESS ADDRTYPE="Work">
<ADDR1>Work Street</ADDR1>
<ADDR2>Work Town</ADDR2>
</ADDRESS>
</PERSONAL_DETAILS>
In database terms I'd like to turn this into something like -
CREATE TABLE PERSONAL_DETAILS
(surrogate_key INTEGER NOT NULL,
NAME CHAR(50) NOT NULL,
AGE SMALLINT,
CONSTRAINT pk1 PRIMARY KEY
);
CREATE TABLE PERS_DETAILS_ADDRESS
(surrogate_key INTEGER NOT NULL,
address_seqnum SMALLINT NOT NULL,
ADDR1 VARCHAR(50),
ADDR2 VARCHAR(50),
CONSTRAINT pk2 PRIMARY KEY (surrogate_key, address_seqnum)
);
ALTER TABLE PERS_DETAILS_ADDRESS
ADD FOREIGN KEY (surrogate_key)
REFERENCES PERSONAL_DETAILS
ON DELETE CASCADE;
Questions are -
Can I get the DB2 shredding SP to produce the surrogate keys and
sequence numbers I need ?
If so, how do I write the DAD to allow this to happen ?
If anyone has done this sort of thing and would be willing to help me
then drop me an email. I can either use email, Instant Messenger or
phone.
Thanks
Phil Nelson
-
::: When replying to the list, please use 'Reply-All' and make sure
::: a copy goes to the list ([EMAIL PROTECTED]).
*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
*** For more information, check http://www.db2eug.uni.cc
-
::: When replying to the list, please use 'Reply-All' and make sure
::: a copy goes to the list ([EMAIL PROTECTED]).
*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
*** For more information, check http://www.db2eug.uni.cc
