BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RobK777
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

2 REPLIES 2
Patrick
Opal | Level 21

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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