DI - DB2 WITH Statement

[RobK777]

Hello,

Does anyone have any experience structuring a JOIN transformation (or other SQL transformation) for DB2 WITH statement in DI?

Other than user written SQL I can't find a feasible replacement.  Please note that DB2 create query and global temporary tables are not allowed in our environment.

Any suggestions are greatly appreciated.

Regards,

Rob

[Patrick · Accepted solution]

I've never used DIS for DB2 queries but I have quite a bit of experience in using the DIS SQL join transformation. As there is no specific DB2 SQL join transformation and - in my opinion - the implementation of "normal" subqueries is already a pain, I strongly doubt that the SQL Join transformation supports specific DB2 WITH clauses.

So most likely you either need to define your query differently or you need user written code.

On a side note:

Something I've realised only lately is that you can actually define a subquery as a SAS SQL view using a separate SQL Join node and then use this view as source for the next SQL Join (using SAS SQL and not pass-through SQL). As a SQL view is nothing else than "encapsulated SQL code" the access engine then takes the whole SQL in the second node and sends it to the DB for execution. I like to take this approach as it firstly allows me to unit test sub-queries separately and also because I don't have to use the sub-query stuff within the SQL Join transformation (I find it really hard to change a sub-query and it's also not very transparent what happens when looking at the job flow).

[RobK777]

Hi Patrick,

Thanks for the reply.

Your suggestion regarding two joins one not being a pass-trough appears to be working.  I turned on _tree option to examine execution SQL and it appears that SAS/ACCESS is translating SAS SQL to native SQL before executing the query.

It is an interesting approach, however it may limit some functionality relating to native DB2 sql, but non the less it is a viable option.

Thanks again.

Rob