It seems that at every customer site I visit the database administrators and application programmers are separated into “us and them”. Never the two groups communicate about application issues unless there is a performance or cost issue. Most of the time these performance problems manifest themselves after production implementation, and then it’s a fire fight with most of the effort focusing on the database. Application development today happens at an increasingly expedited pace, and most of the database access is “database agnostic”, meaning it is standard SQL acceptable to a variety of database management systems. The application developers rely on database administrators for creation of database objects and access authority, and that’s where development ends.
It does not have to be that way. There are tremendously powerful programming possibilities with SQL, and although it might not be database agnostic, many of these features are translatable across RDBMS’s with only a modest amount of effort. Utilizing these more advanced SQL features can drastically reduce development time and improve performance, and so why wouldn’t you use them?
SQL as a Programming LanguageSQL has evolved into far more than a data access language. With complex expressions and functions SQL can filter and transform data into whatever the application requires. If these complex SQL statements perform filtering and/or aggregation they can provide a significant performance advantage over the equivalent application program. Joins can reduce the number of SQL calls a program makes to the database while reducing the amount of application code necessary to process data.
Complex processing can be incorporated into SQL utilizing such constructs as nested table expressions, common table expressions, correlated nested table expressions, scalar SQL expressions in a SELECT list, and recursive SQL. Table expressions are SQL statements coded as expressions within SQL statements. They can be used to create intermediate results for further processing within a single SQL statement.
This is an example of using a nested table expression as part of a join to get both line item and aggregate results in the same SQL statement.
SELECT EMPNO, SALARY, DEPTSAL.AVGSAL
FROM EMP EMP
INNER JOIN
(SELECT WORKDEPT, AVG(SALARY)
FROM EMP
GROUP BY WORKDEPT
) DEPTSAL(WORKDEPT, AVGSAL)
ON EMP.WORKDEPT = DEPTSAL.WORKDEPT
A SQL scalar expression, which is a SQL statement that returns a single result (1 column of 1 row) can be placed virtually anywhere in a SQL statement, including the SELECT clause and the WHERE clause. Here is an example of using these expressions several times within a statement.
SELECT EMPNO, (SELECT COUNT(*)
FROM EMP SE
WHERE SE.WORKDEPT = E.WORKDEPT)
AS DEPT_HEAD_COUNT,
CASE WHEN E.SALARY = (SELECT MAX(SALARY)
FROM EMP MS)
THEN 'MAX SALARY'
ELSE 'REGULAR PERSON'
END AS MAX_INDICATOR
FROM EMP E
WHERE SALARY BETWEEN
(SELECT AVG(SALARY)
FROM EMP) AND
(SELECT MAX(SALARY)
FROM EMP)
OLAP expressions can be utilized to perform complex aggregations.
More information on OLAP:
Recursive SQL:
Stored ProceduresStore procedures offer a way to encapsulate data access and programming within a single database call. Stored procedures can be native, written in procedural SQL language, or external procedures written in a variety of programming languages. This allows for easy program access to what would otherwise be a complicated data access problem and could also be a performance improvement if the stored procedure call encapsulates several SQL statements as well as program logic, and only returns an answer to the caller. Converting legacy programs into stored procedures can be an effective way to quickly web enable legacy functionality. Native SQL procedures execute internal to the Db2 engine and can utilize zIIP engines for remote requests. These zIIPs, or System z Integrated Information Processors are used to offload certain workloads normally designated for the central processors. Doing this can result in significantly lower software licensing costs.
User-Defined FunctionsIf the database does not offer a function that an application can provide then a user-defined function allows for database extensibility. Existing functions can be overloaded for user-defined data types, and new external or SQL functions can be written to do just about anything. Once again, if the functions are used to simplify a complex application process then they can be a performance benefit. External User-defined functions, similar to external stored procedures, can be used to invoke legacy application programs from within the context of SQL statements. SQL functions can be written in procedural SQL and can return a scalar result or a table to the caller. This means that parameterized views are possible.
More information on UDFs:
XML and LOBs
Large objects can be stored in Db2 tables. This enables for storage of things such as documents, audio, images, and video. There is definitely an advantage to storing LOBs within Db2 in that these objects can be related to normal relational data, as well as being backed up and recovered together with the relational data.
More information on LOBs:
Db2 allows for XML document storage within tables similar to the way LOBs are stored. However, Db2 provides for a completely separate programming language, SQL/XML, including FLWOR expression for complex parsing and processing of XML documents. It is even possible to build indexes into XML documents for fast search processing. Functions exists to convert XML to tables and back again, enabling the convergence of XML document storage together with relational data.
There is an entire series on XML and Db2 on the IDUG web site beginner’s blog:
Database Automation
There exists database automation built in to Db2 that can save months of application development time. Database enforced referential integrity maintains database relationships, and also can be used to propagate deletes. This can eliminate a lot of application logic, and reduce the amount of time the application calls the database. Temporal and archive tables allow for automatic movement of data to history tables or archive tables. This alone can save significant programming time, and is far superior in multiple ways from the equivalent application logic. First, it is a performance improvement in that that application is not responsible for moving the data resulting in fewer trips to the database. Second, since the temporal assignment is controlled by the database it can be easily switched on or off, allowing for more flexibility. As a bonus since the application is not responsible for moving data then there is reduced risk of error, and certainly a reduction in testing time. Time travel queries simplify access to temporal data and can save significant programming time.
More information on temporal and archive tables:
There is additional database automation available in the form of triggers, identity columns, sequence objects, and row change timestamps. All of these features are useful for eliminating programming logic and improving performance. Row change timestamps are especially useful when employing an optimistic locking strategy. I’ve been doing some research on Java batch processing, and Db2 row change timestamps would fit perfectly with an optimistic locking strategy promoted by Java batch frameworks, such as Spring Batch.
Spring Batch intro:
More information about row change timestamp:
Additional information about advanced programming features, including triggers:
REST ServicesOne exciting recent addition to Db2 for z/OS is the addition of the REST API. This allows for SQL statements and stored procedures to be accessed via a RESTful call to the Db2 distributed address space via a service call using a URI. This allows for all that power that can be incorporated into SQL to be invoked from a simple REST service call. No Db2 client necessary, and the results are placed in an easy to parse JSON document.
More information on REST:
SummaryTo overlook the features of Db2 for programming enablement is, in my opinion, a big mistake. Utilizing the available database features in SQL can reduce development time and increase performance. There is virtually no limit to what can be accomplished in SQL. Explore it!