The SQL Programming Language?

During the mid-1990s, when I was with IBM, I did some consulting work for a large media company. These folks had a very sophisticated data warehouse set up, with DB2 for z/OS being at the heart of it all. One day, a person on the company's data warehouse team asked me if I wanted to see the cool program he'd written in SQL. "Excuse me?" I said. "Don't you mean the cool program you wrote that contains SQL?" No, he really did mean "written in SQL." He showed me an SQL statement that was six or seven pages long, with lots of CASE expressions, that did some very sophisticated data manipulation and generated some very useful information from. About all I could say was, "Wow."

Keep in mind, folks, that this was more than 10 years ago. Think of what's happened since then on the mainframe side of the DB2 family:

• DB2 for z/OS Version 6 added a ton of new scalar functions, including CEILING, DAYOFWEEK, IFNULL, REPEAT, and TRUNCATE. Also new with V6 were triggers, user-defined functions (aka UDFs), user-defined data types (UDTs), and large objects (LOBs).
  
• DB2 for z/OS V7 enabled SQL writers to order result set data according to an expression with operators (not just by a column name or integer). V7 also introduced scrollable cursors, declared temporary tables, and, of course, the ability to write stored procedures entirely in SQL (using what has come to be called SQL PL, or SQL Procedural Language).
• DB2 for z/OS V8 gave us common table expressions, which in turn enabled people to do things using recursive SQL that had previously been very challenging (such as returning multiple values of one column from rows with the same key - a table design meant to eliminate repeating groups - as multiple column values of a single row). V8 also enriched SQL PL with statements such as ITERATE and SIGNAL.
• DB2 for z/OS V9 delivered all kinds of XML-related functionality, along with OLAP specifications, ROW CHANGE expressions, and new scalar functions such as BIGINT, CHARACTER_LENGTH, LOCATE_IN_STRING, and SOUNDEX. There are also new SQL DML statements, including MERGE and TRUNCATE.

The next version of DB2 for z/OS will make SQL even more powerful. The story on Linux/UNIX/Windows (LUW) platforms is much the same, with each release of DB2 expanding the range of things that can be done via SQL (SQL in the mainframe and LUW DB2 environments is almost entirely the same, with new SQL functionality sometimes showing up in DB2 for z/OS before being delivered in DB2 for LUW, and vice versa). I wondered, ten years ago, as to whether or not there would be a class of DB2 professionals who essentially work as SQL programmers. I wonder no more. Earlier today, in Googling "DB2 SQL PL", I saw a link to a job posting for a DB2 SQL PL developer.

This SQL coding specialization is a timely development (no pun intended) within the DB2 community. For some time now, I've been a proponent of an architecture in which a mainframe DB2 platform functions as a super-scalable, super-available, super-secure data server, processing requests from Java or .NET (or PERL or Python or Ruby or whatever) programs running on off-mainframe application servers (such an architecture is already relatively common in DB2 for LUW environments). I'm also big on SOA and the related concept of logically separate presentation, business, and data access logic layers within an application system. The data access layer is where SQL programmers can really shine (I'm a fan of server-side SQL).

Looking to get into the IT business, or looking for a new career direction? Consider becoming an SQL programmer. There's enough variety and creativity possible in that space to make for a rewarding work life.