Buy One SQL, Get One Free
Simplify statements by writing them to perform more than one operation
2/11/2015 5:50:11 AM |
By Meg Bernal
What is buy one, get one (BOGO) SQL you might ask? BOGO SQL allows you to buy one (SQL), get one (SQL) free. No, BOGO SQL is not an official name, it’s a name I’ve given to the set of SQL statements that allow you to do more than one operation within a single SQL statement. It’s not always a single SQL statement that offers you a BOGO, but you will understand why I call them BOGO SQL.
You know what it’s like when you go into the grocery store and the grocer is offering two bottles of wine for the price of one. You look for your favorite weeknight bottle of wine hoping it is being offered with the promotion. It’s your lucky day and it’s on special. Some examples of BOGO SQL that allow you to write a single SQL statement and have it perform two or more SQL operations are Rowset Cursors, Scrollable Cursors, MERGE, SELECT … FROM … INSERT/UPDATE/DELETE/MERGE and the multiple SET assignment.
BOGO SQL is attractive in many ways. It allows you to simplify your application logic. Who has picked up someone else’s SQL and thought, ‘Wow, that’s a whole lot of SQL?’ BOGO SQL might allow you to rewrite complex applications or provide you a means to write future applications with simplicity. Second, you might save time sending the database one SQL statement compared to N SQL statements. Imagine the network savings in a distributed environment when your application sends over fewer SQL statements. Your boss will love to hear about the savings or, better yet, your fellow DBA will praise your talent.
I’ll provide some examples below to demonstrate the BOGO SQL concept, but please don’t copy and paste the code because I’ve taken the liberty to show you the minimum amount of SQL to illustrate the concept and to keep the article shorter. Some code is in bold to emphasize what the text is referring to. For detailed information, please refer to the DB2 for z/OS Knowledge Center documentation. Read on to see if there’s a BOGO SQL out there for you.
Rowset cursors were first introduced in DB2 for z/OS V8. Rowset cursors allow you to define a cursor over N number of rows and fetch N (or M) number of rows from that result table. Traditional cursors only allow one row at a time be fetched from a cursor. For a rowset cursor, on the DECLARE CURSOR statement you specify the WITH ROWSET POSITIONING syntax then on the FETCH statement you specify how many rows to fetch.
Let’s assume the ACCOUNT table has 1,000 rows and you want to fetch 20 rows. Here’s some sample code from a PL/I application that uses a rowset cursor to fetch 20 rows of data:
DECLARE myAccountCursor CURSOR WITH ROWSET POSITIONING FOR SELECT ID FROM ACCOUNT;
FETCH NEXT ROWSET FROM myAccountCursor INTO :myIDArray FOR 20 ROWS;
Prior to rowset cursors your application might have looked something like the following where you used a loop to make 20 fetch requests:
DECLARE myAccountCursor CURSOR FOR SELECT ID FROM ACCOUNT;
DO WHILE (xMORE = 0 & myNumRows <= 20);
FETCH FROM myAccountCursor INTO :myID;
myNumRows = myNumRows + 1;
With the rowset cursor example, DB2 processed four SQL statements: DECLARE, OPEN, FETCH and CLOSE. In the non-rowset cursor example, DB2 processed 23 SQL statements: DECLARE, OPEN, 20 FETCHes and CLOSE.
One other sleek feature with a rowset cursor is that you can update a specific row of the rowset or all rows of the rowset with the FOR ROW N OF ROWSET clause on the positioned-update. If you want to update all rows of the rowset ensure to specify the FOR UPDATE OF clause on the cursor and issue the positioned-update statement: UPDATE ACCOUNT SET BALANCE = :myDeposit + BALANCE WHERE CURRENT OF myAccountCursor. All 20 rows of the rowset cursor will have their BALANCE columns updated.
Similarly, if you want to update a single row of the rowset, specify the specific row you want updated on the positioned-update statement:
UPDATE ACCOUNT SET BALANCE = :myDeposit + BALANCE WHERE CURRENT OF myAccountCursor FOR ROW 5 OF ROWSET
DB2 for z/OS V8 also included scrollable cursors when it shipped rowset cursors. With a scrollable rowset cursor you can position within the cursor and fetch multiple rows at once. I’m tired thinking about what an application might look like if it had to do its own scrolling and fetching, let alone having to write a scrolling application.
Using the rowset cursor example above against the ACCOUNT table, let’s say you want to display the first 20 rows of the result set followed by the last 10 rows of the result set. I’ve modified the sample rowset cursor code above to fetch the first and last set of rows of data:
DECLARE myAccountScrollableCursor INSENSITIVE SCROLL CURSOR WITH ROWSET POSITIONING FOR SELECT ID FROM ACCOUNT;
FETCH FIRST ROWSET FROM myAccountScrollableCursor INTO :myIDArray FOR 20 ROWS;
FETCH LAST ROWSET FROM myAccountScrollableCursor INTO :myIDArray FOR 10 ROWS;
DB2 for z/OS offers four different types of scrollable cursors: ASENSITIVE, INSENSITIVE, SENSITIVE STATIC and SENSITIVE DYNAMIC. In the example above, I’ve used an INSENSITIVE SCROLLable cursor to tell DB2 to lock in my results during OPEN cursor processing and fetch from that locked in set of values. In the example above, DB2 processed two FETCH requests and returned 30 rows of data.
The MERGE SQL statement was introduced in DB2 for z/OS V9, allowing the user to either INSERT values into a table or UPDATE existing values in a table. The INSERT or UPDATE happens during the MERGE statement processing. Prior to DB2 V9, you might have written something like the following in your application to populate the BALANCE column of the ACCOUNT table:
INSERT INTO ACCOUNT(ID, BALANCE, …) VALUES(:myID, :myDeposit, ….);
-- when ID represents a duplicate key, update existing key
IF SQLCODE = -803 THEN
UPDATE ACCOUNT SET BALANCE = BALANCE + :myDeposit WHERE ID = :myID;
With the MERGE statement, a single SQL statement can be sent into DB2 to either INSERT or UPDATE the target table. In the example below, we have five sets of IDs and amounts to update the ACCOUNT table. The IDs and amounts are stored in host-variable arrays :myIDArray and :myAmountArray. Recall, host-variable arrays were introduced in DB2 V8 for multiple row insert and multiple row fetch.
MERGE INTO ACCOUNT AS Tgt
USING VALUES(:myIDArray, :myAmountArray) FOR 5 ROWS AS Src(ID,AMT)
ON Tgt.ID, Src.ID
WHEN MATCHED THEN UPDATE SET BALANCE = Tgt.BALANCE + Src.AMT
WHEN NOT MATCHED THEN INSERT (ID,BALANCE) VALUES (Src.ID,Src.AMT)
NOT ATOMIC CONTINUE ON SQLEXCEPTION
SELECT FROM INSERT/UPDATE/DELETE/MERGE Statement
Sometimes, the source value being sent into DB2 isn’t necessarily the value stored in a table. Similarly, source values can be generated by DB2. Triggers can affect the original source values and DB2 might generate values for generated columns or sequences. Knowing what the actual value that was stored in the table could be useful.
Let’s assume there’s an AFTER INSERT trigger on our ACCOUNT table that modifies the actual amount the BALANCE column gets updated with (i.e., interest earned) and a transaction ID is generated. You could write in your application:
INSERT INTO ACCOUNT(ID, BALANCE) VALUES(:myID, :myAmt);
IF SQLCODE >= 0 THEN
SELECT ID, BALANCE, TRANS_ID INTO :newID, :newBalance, :newTransID
FROM ACCOUNT WHERE ID = :myID;
Instead of writing two SQL statements like above, a single SQL statement can be written to obtain the final values stored in the table:
SELECT ID, BALANCE, TRANS_ID INTO :newID, :newBalance, :newTransID
FROM FINAL TABLE (INSERT INTO ACCOUNT(ID, BALANCE) VALUES((:myID, :myAmt));
The SELECT FROM INSERT support was delivered in DB2 for z/OS V8 while SELECT FROM MERGE/UPDATE/DELETE were all delivered in DB2 for z/OS V9.
Multiple SET Assignment Statement
This last BOGO example is something we don’t see taken advantage of enough, yet it provides another opportunity to reduce costs, especially in a distributed environment. Starting in DB2 for z/OS V6, you can specify multiple SET assignments into a single SET assignment. Here’s an example of something we see often regarding multiple SET assignments:
SET :myTMS = CURRENT TIMESTAMP;
SET :myID = (SELECT SUBSTR(ID,8,11) FROM T1 WHERE ACCT = ‘12345’);
SET :myBalance = (SELECT CURR_BALANCE(‘12345) FROM SYSIBM.SYSDUMMY1);
The above three SQL statements can be written into a single SQL statement:
SET (:myTMS, :myID, :myBalance) = (CURRENT TIMESTAMP,
(SELECT SUBSTR(ID,8,11) FROM T1 WHERE ACCT = ‘12345’),
(SELECT CURR_BALANCE(‘12345) FROM SYSIBM.SYSDUMMY1));
With the single SQL statement above, DB2 will evaluate all of the items on the right-hand side of the assignment statement and set their corresponding targets specified on the left-hand side of the assignment statement. One word of caution, when you collapse multiple SET assignments into a single SET assignment, make sure one SET assignment statement is not referencing another SET assignment’s target. Take a peek at some applications you maintain or have—gulp—written yourself and see if there are opportunities to collapse multiple SET assignments into a single SET assignment. You might be surprised that a little refactoring could make you a superstar.
Not all BOGO offers prompt you to open your wallets. You might find some of these buy one (SQL), get one (SQL) free offerings as buy one (SQL), get one (SQL) half-off due to some additional effort to understand the details of the features or how the features might affect the application’s execution. I hope you will consider some of the SQL features DB2 for z/OS has to offer in an attempt to simplify application logic, reduce costs and excite you to write some cool SQL. The SQL Reference describes all the features discussed above. All of the topics can be found in the Statements chapter of the SQL Reference.
Meg Bernal has been a DB2 for z/OS Developer at the Silicon Valley Laboratory for 17 years. She has participated on many SQL enhancements throughout the releases of DB2 such as Muliple-Row Fetch, Scrollable Cursors, SELECT FROM INSERT/UPDATE/DELETE and INSTEAD OF TRIGGERs. Most recently, Meg has been dedicated to providing SQL PL support since native SQL PL procedures were first introduced in DB2 9 for z/OS.