I have to Sequence File joined,then transformed,then upsert into Oracle.
Reading from Sequence File is about 25000 rows/sec,but after joining the link shows that it is about 50 rows/sec,it is intolerable.
Then I find that when upserting into Oracle many records are rejected.Maybe there is too much space in my data,so it is too long for the datatype(varchar) specified in the Oracle and this makes Oracle rejects records.
I set the Environment Variable APT_STRING_PADCHAR to 0x00,and use trim() in the Transformer.This works,the job runs fast.
But there is another problem,if I use trim() in the Transformer,because some trimmed fields of some records are null,so these records are dropped and this is not conformed to the business logic.
How can I solve this problem?thanks.
The job is extremely slow after join
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 23
- Joined: Tue Jun 19, 2007 11:51 pm
The job is extremely slow after join
a newbie to an expert
Put a HandleNull() call in the derivations where you TRIM data that might be null and where you don't want it to be. Or a simple
Code: Select all
IF IsNull(In.Col) THEN SetNull() ELSE TRIM(In.Col)
-
- Participant
- Posts: 23
- Joined: Tue Jun 19, 2007 11:51 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
$1 (indeed regular expressions) are used in Derivation Substitution, not in derivation expressions themselves. For example, if you have 200 VarChar columns and want to apply a Trim() function to all of them, select them all then right click and choose Derivation Substitution from the menu. The rest is explained in the GUI.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.