SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DI - DB2 WITH Statement

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

DI - DB2 WITH Statement

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


Accepted Solutions
Solution
‎07-23-2013 06:45 AM
Respected Advisor
Posts: 4,173

Re: DI - DB2 WITH Statement

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).

View solution in original post


All Replies
Solution
‎07-23-2013 06:45 AM
Respected Advisor
Posts: 4,173

Re: DI - DB2 WITH Statement

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).

Occasional Contributor
Posts: 12

Re: DI - DB2 WITH Statement

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 649 views
  • 0 likes
  • 2 in conversation