SQL transform vs. Query

[system]

Hi ETL designers,

I have to write a small Data Integrator job. Currently our internal policy is to use SQL transforms and then a simple query transform to map the result into the final table.

My question is that Metadata Reports can only be used with jobs using queries only? Is the SQL standard compatible with the query transforms? Which is the faster?

I also have a problem with type conversions. Currently I use PL/SQL functions to do this for me. How does this affect the performance? What happens in Data Integrator when I invoke a PL/SQL function? And what happens when I invoke an SQL function (TO_DATE,TO_CHAR,LPAD, etc)

Thanks in advance,
Csisz

---

DBArchitect (BOB member since 2009-06-23)

[system]

Things you should never do - a collection and discussion

Approach: DataServices is so limited when it comes to the sql syntax supported in Queries, I do not want to read all data from a table, I want to use variables to read just a subset Solution: I use a SQL Transform everywhere Problem with that: A SQL Transform has lots of limitations, no impact lineage information, no partitioned readers, hard to read & modify the SQL statements typed, no full pushdown in the sense of an insert…select…, etc. And actually it is not needed. DataServices has an o…

---

Werner Daehn (BOB member since 2004-12-17)