DSXchange

Hi, I am using the new xml stage on 9.1 and I am having a problem with repeating groups that look like this: <c178 m="14">Y</c178> <c178 m="64">FIRMS</c178> <c178 m="157">11-25</c178> <c178 m="158">100K to 500K GBP</c178> <c178 m="159">N</c178> <c178...

Hi,

That is strange.

Check the director log for any warnings which might indicate why records are being lost.

Are DS and oracle looking at the same schema.table?

Remove all parallel settings from the stage, ie. keep it as simple as possible.

Cheers,
Bob.

Hi,

Try changing your sql to:

select DISTINCT
Field1
from Table

This will considerably reduce the amount of data coming from the database server which alone should speed things up.

This should also remove the need for the sort stage which again should speed things up.

Hope this help,
Bob.

Hi, I think I would start with oracle itself. If you run your sql statement in an oracle IDE does it still take 2.5 hours to complete? Is the table partioned? (A table with 90m rows is a good candidate for partioning!) Have the stats been run on the table recently? (Your dba will know) Poor stats wi...

Hi, I think we need to know a bit more about what is going on here: I assume the job is hanging after extracting data from the database using one of the database connect stages. If so, does the sql statement perform well when run from the SQL Server IDE? What sort of volumes are being returned betwe...

Hi,

From my own experience with working remotely, if you have the "Show Performance Statistics" turned on in Designer that will slow it down considerably.

Hope this helps.

Bob.

Hi I tried using the parallel reads option and it actually reduced the rows per second from 11k to 8k. I suspect this is because DS inserts an additional where clause to do the partitioning which simply slows down the processing. In addition, because I was using my own sql statement with table alias...

Hi Mike,

Unless I did something wrong in my test job, Min() and Max() both output zero, presumably because they are numeric functions not expecting non-numeric input?

Cheers,
Bob.

Hi Madhusudhan, If you use a transformer with stage variables set as follows: svSource: if DSLink2.source < DSLink2.destination then DSLink2.source else DSLink2.destination svDestination: if DSLink2.source < DSLink2.destination then DSLink2.destination else DSLink2.source and map those to their resp...

ChangeKey would be colA

I wouldn't have thought the logic was that difficult to follow.....

When I have done this sort of thing in the past I have had success but doing the following: Sort the data by colA,hash partition by colA and set the key change indicator on. I use three stage vars as follows: svLastRowInGroup: LastRowInGroup(colA) (LastRowInGroup() function is set to 1 for true, zer...

I believe that by mixing IsNull and ColName=value in the same expression you get unexpected results.

As suggested earlier, just use the IsNull check, if you haven't already.

Cheers,
Bob.

When performing reference matches it is a good idea to reduce the amount of data in the reference set, if possible. I don't know your data but, for example, if you were using address data there is no point in loading reference addresses for London when the source only contains addresses from Edinbur...

Hi Jerome, I think you'll find your problem is that the month and day values are not consistent. ie. you have 1 and 15 and it should be 01 and 15 etc. I use stage variables to fix this problem like this: Day value if len(field(DSLink21.Start_Date, "/", 1)) = 1 then "0":field(DSLi...

Hi Prasoon,

You should be ok if you use a DIFFERENT file in each transformer for the s/key generation.

Cheers,
Bob.