DB2 SQL – There are No Limits

SQL is much more than a data access language. It is a full-fledged programming language. This means that any business or scientific question can be answered using SQL. There are no longer limits to the capabilities of the SQL language. By utilizing some built-in advanced SQL constructs and extensions, and also applying a little experience and know-how, you can program pretty much anything using SQL.

One of the greatest aspects of SQL is the orthogonal nature of the language in that any expression can be placed inside any other expression. This makes for a highly flexible language. Another aspect is the fact that an SQL statement is in fact an expression, and that means that we can place statements within statements. This allows us to build complex SQL programs in pieces and unit test each of those pieces. It also means that anyone with a core minimal SQL ability can construct simple statements, and then assemble them into something more complex.

So, why SQL? There are three main reasons:

  • Performance
  • Flexibility
  • Speed of Delivery

 

Performance

Many times poor performing applications are not the result of a poorly designed database or poorly configured database engine, but instead are the product of application designs that are not coded with performance in mind. That is, the applications are coded for concurrency, availability, flexibility and ease of programming. Other times they are constructed using various tools and frameworks that construct a data access layer using generic SQL statements. Many times this is the result of object oriented programming methodologies, or something built on top of those methodologies. The result is a multitude of simple straight forward SQL statements that flood a DB2 data server and consume all of the CPU and memory resources. These applications can also flood the network with a multitude of messages resulting in poor application response time.

Advanced SQL can offer relief when many simple statements are replaced by a single statement. The single statement then performs multiple accesses and processes. This results in fewer calls to the data server and more meaningful data processing much closer to the source of the data. To put things another way, if you can perform a “data intensive” process close to the data then that is going to be a significant performance advantage.

Flexibility

While some might tout the wonders of Java’s portability, it is SQL that is truly the most portable. This is because in most situations the SQL is running on the data server to which it is directed. If you’re an SQL programmer then your SQL programs are running on the data server. This means you can put that SQL program anywhere you have a connection to the data server, and if you put more of the logic in the SQL then that gives you more flexibility as to where you can put that statement. This gives you leverage with your hardware and software vendors, which means more money in your pocket.

Speed of Delivery

Most people don’t take the time to learn SQL at all. It’s perceived as a data access language and nothing more, however; this cannot be further from the truth. Given a little time, SQL can be reasonably simple to learn. Additionally, a number of free data servers can be installed on a student’s PC for practice, making for easy testing of code. Take the time to learn a little SQL and you’ll be surprised at how easy it is to accomplish complicated things.

Constructing Advanced SQL

Take a look at the SQL reference guide or the SQL application programming guide. You’ll find a lot of examples and inspiration that can lead to great SQL-based solutions. Between the flexibility of the language constructs and numerous built-in functions and features there are few things you can’t do. In addition, there are such things as stored procedures and user-defined functions that allow you to extend the capabilities of the database engine. Remember this basic concept; the input to an SQL statement is one or more tables, and the output from an SQL statement is a table. If the output from a statement is a table then that table can be input into another statement. You can have up to 225 table references in a single statement. That’s a lot of processing in one statement. Start thinking of an SQL statement as a process that has inputs and outputs, and then start thinking about chaining processes together or putting a call to one process inside another. Now, you are constructing advanced SQL!

The output from an SQL statement can be anything. Why not code everything you need in an SQL statement? While it may not be the best performing statement, it is probably the fastest way to deliver an answer to a business question. Many times what takes days of programming can take only hours in SQL.

Some things that have been output from SQL statements I have written:

  • An XML document
  • A Web page
  • An SQL statement
  • An assembler DSECT
  • A Web menu
  • A spreadsheet


The DB2 data server engine is a powerful thing. There are a lot of built-in performance features that can process a request quickly, saving the programmer from having to code their own complex process for performance.

Sheryl LarsenSQL Presentations

Many of you are familiar with Sheryl Larsen, who has been an SQL advocate since 1989. Sheryl has recently accepted a position with IBM as the World Wide DB2 for z/OS Technical Evangelist. She has been kind enough to give several of her pre-IBM presentations to IDUG for publication in our Content Committee file library. Look during the month of August 2013 as we publish this content.

“I believe Advanced SQL knowledge and skills are so very essential for today's and tomorrow's developers and database professionals. The advancement of SQL knowledge has been my focus for many years and I have a strong commitment and desire to keep it moving forward.  By teaming up with IDUG, I am able to share my Advanced SQL training materials with the world!  To your continued SQL learning! Enjoy!” –Sheryl Larsen

The Content Committee file library can be found here.

1 Like
Recent Stories
An Introduction to IBM Data Studio

The Basics of SQL Performance and Performance Tuning

Index Decluttering Opportunities in DB2 for Linux, UNIX, and Windows