Advanced SQL is all around us, so we better speak the language

This month’s topic is “advanced SQL”.  This is always a tricky title, as what may be advanced to me, may not be advanced to you. So let me give you my definition of advanced SQL. 

“Advanced SQL is any SQL statement that goes beyond the scope
of the straight forward use of the SELECT-FROM-WHERE clauses”.

This may seem like a very broad scope, but often it seems that once beyond that scope, it all depends on your general coding experience, the practice you had with a certain feature and so on.

Every DB2 release brings new features to make SQL within DB2 a more complete programming language and because of this more of our developers start coding their queries with more complexity. 

Sometimes our developers use advanced SQL very consciously by coding features like recursive SQL, using Xquery or Xpath expressions, using merge (or even better select from merge).  They might also use extensive OLAP functionalities and use techniques as windowing and such. They might code extensive and complex SQL stored procedures.

Some of these techniques may be very well known by the DBA, others may be completely new. Let’s take the case of recursive SQL, some developers and DBA’s use it daily, others never use it. Whether or not you find this “advanced” SQL depends on your experience.  You would have to be familiar with common table expressions (CTE), you would have to know that within the common table expression you have to do starting select and then a union all with the common table expression itself.  Example:

SQL 1.jpg

Similarly, the technique of windowing within OLAP functionality might be very well known to some developers and some DBAs, yet others might not yet have encountered it. You would have to understand that your OLAP function (RANK, DENSE_RANK, SUM, and AVERAGE, running totals, moving average...) will not be applied/calculated on the entire result, but will only be divided up in the different partitions/windows on which they applied. Example: give the people of your company a ranking based on their salary (no windowing) and give the people of your company a ranking based on their salary within their own department (windowing)

SQ 2.jpg

Now this is a very simple example, but when this kind of advanced SQL writing is applied to running totals, moving averages, etc. then it can reduce the amount queries run, data transferred and Excel reports and recalculation done.

Sometimes they do it unconsciously by using SQL generators like Hibernate.  Anyone that ever looked at a query generated by such a tool will agree, this is (needlessly) complex.  A tool like hibernate quickly resorts to one massive query using many left outer joins, mixed with inner joins and full outer joins. Even as a seasoned DBA, when being presented with such a query, the question of “what are they really trying to get?” comes to mind.

DB2 11 for z/OS introduced global variables which will make SQL even more powerful, these global variables can be of an array data type, turning an advanced SQL statement even more advanced. Global variables are so powerful it is believed they will be used quite a lot. DBA and developers should have a working knowledge.

This month we will be looking a different aspects of advanced SQL, so get your developers involved.  Get them trained (IDUG conferences are a good place to start). Train yourself in advanced SQL.  Play around with it.  Keep your skills and that of your developers up to date, knowing what is available is half the battle, knowing what is available on other platforms or cross platform can give your company an edge.


As DBA’s we are expected to tune all types of queries, from transactional to complex warehouse queries. We are expected to provide support and training to our developers in matters like SQL stored procedures. We are expected to know advanced SQL.

2 Likes
Recent Stories
Lessons Learned of Locking and Latches by Adrian Burke

Db2 for z/OS locking basics

A Beginners Guide to Locks, Latches, Claims and Drains in DB2 for z/OS by Steve Thomas