Originally posted by: repanzer
I would most likley re-build the file before importing to the DB, having the second DB key field ready before the import of the DB.
Let's say a chunk is a 10 bytes, so here's your input;
Record(s)
10_byteTextFields(s) (N/L term)
Any 1 record may have 1 or more related chunks of text.
Here's the output I would use;
Records(s)
dbkey1
Group(s)
dbsecondkey(1:1)
1_text_chunK(1:1)
You map a record to record for the first functinal map, using the INDEX on Record, so every one record on your input is one record on your output (first dbkey). Then each 10_byteTextFields maps to a Group on your output, using the INDEX function on the 10_byteTextFields. In the second functinaol map, you map the INDEX and one chunk.
Here is what you get when you map;
Input data;
Here is the first record of text to map into the DB
here's 2nd
after mapping, you have left with;
11Here is th
12e first re
13ord of tex
14t to map i
15nto the DB
21here's 2nd
You use control break logic on the first field; when the first field changes, it's a group. (in type tree field1{LAST}=field1)
When you map it into the DB,
Input
File
Records(2)
Dbkey1 (NOT USED FOR DB KEY, JUST TO KNOW RELATED GROUPS OF TEXT)
Dbkey2
chunck.
Output
DB
Records(s)
Dbkey1
Dbkey2
chunck.
As you should know, you use the "Max" function to get the next DBkey1 field (or somethign like that), add one, and Write the record first using only the dbkey1, then you update that record later, so you're not using the DKkey1 in your input, or any new file with overwrite the previous records you created from a previous file.
Let's say you did that and it returned 250 to your first input card and your second input is your re-built file. Put that number in the first output card, so you can use GETANDSET.
You create a funtional map on "record" to "record", and include the 250 from your output card.
In the DBKey1:output2 field when you map the data;
IF(DBKEY2:input2 = 1, GETANDSET(output1:DBkey1, 1,1), DBkey1)
If the DKkey2 is a new record, add one to the last DBkey1 field you used to create a new primary key, else use the same primary key you just used, because the secondary key will be different.
(look up getandset to make sure i have it correctly, the " 1,1" may be wrong)
In the functinal map, use GETANDSET, which will add 1 to the 250, map 251, then leave it as 251, so the next record will increment and get you the new primary key.
#DataExchange#IBM-Websphere-Transformation-Extender#IBMSterlingTransformationExtender