Tuesday, January 17, 2017

The DB2 12 for z/OS Blog Series - Part 1: SQL Pagination

Today’s blog post kicks off a new series of posts that examine new features and functionality delivered with the latest and greatest new version of DB2 foir z/OS – Version 12.

We’ll begin with a nice new feature that should make it easier for programmers writing queries where the program needs to be able to deal with a subset of results. For example, to show the first 25 rows, then the next, etc. This is a frequent requirement for mobile and web applications that are common these days.

This separating of rows into piece can now be accomplished quite easily in DB2 12 with the new OFFSET syntax. OFFSET is specified as a clause of the SELECT statement and it is used to specify the number of rows in the result table that will be skipped before
any rows are retrieved.

The offset clause is simple to code. You just need to determine the number of rows that you want to skip and code that in the clause. For example, to skip 5 rows you would code OFFSET 5 ROWS. Simple, right? Well, conceptually, yes, but in practice you have to be careful.

First of all, you must know where/when you can code an OFFSET clause. OFFSET can be specified on a subselect or fullselect that is outermost fullselect in a prepared SQL statement or a DECLARE CURSOR statement. OFFSET is also permissible in a SELECT INTO statement. However, you cannot code an OFFSET clause in a view definition, an MQT, the RETURN statement of SQL table functions, in row permissions or column masks, or in the outermost fullselect for a sensitive dynamic cursor. A further restriction is that the statements that includes the OFFSET clause cannot contain an expression that is not deterministic or that has external action.

The second, and I think more interesting aspect to consider when using the OFFSET clause is that your result set must be predictable to assure that you are retrieving useful data. As we all should know, the access path chosen by the optimizer can alter the order in which rows are returned. If you want to access rows in sets of 25, for example, then you want to make sure that each successive set does not contain any rows from previous sets, and does not omit any rows in between sets.

So how do we do this? The best approach is to code up an ORDER BY clause that specifies columns that uniquely identify each row in the result table. If there are duplicates, then there is no way to verify that you are getting the data that you want. IN other words, the order of the rows is not deterministic. The data being skipped will not be predictable and it is highly likely that you will not be accessing all of the data that you want (or perhaps even accessing the same data twice).

It is also important to understand that if the OFFSET number of rows is greater than the number of rows in the intermediate result table, you will get an empty result.

So let’s look at a quick example. Suppose we are looking for employees earning more than $50000. Further, we want to skip the first 10 rows of the EMP table, and grab the next 10. This SQL should do the trick:

SELECT *
FROM EMP
WHERE SALARY > 50000.00
ORDER BY EMPNO
OFFSET 10 ROWS
FETCH FIRST 10 ROWS ONLY;

The OFFSET 10 ROWS will cause the first 10 qualifying rows to be skipped. The FETCH clause gets the next 10 qualifying rows (after skipping the first 10).

But OFFSET is not the only new feature to help developers with SQL pagination. Also available with DB2 12 is data-dependent pagination, which uses row value
expressions in a basic predicate. That sounds more complicated than it really is, so let’s look at an example:

We can code a WHERE clause like this to search more rows with a name greater than mine:

WHERE (LASTNAME, FIRSTNAME) > (′MULLINS′, ′CRAIG′)

This is easier than what we previously had to do (before DB2 12), which was breaking the WHERE up as follows:

WHERE (LASTNAME = ′MULLINS′ AND FIRSTNAME > ′CRAIG′)
OR (LASTNAME > ′MULLINS′)


The bottom line is that application coding for pagination becomes a lot easier in DB2 12 for z/OS…

Monday, January 09, 2017

The Tao of DB2 - Part 15: Putting it all together!

We rejoin our soon-to-be-retiring DBA and the intern who is tasked with replacing him as the old DBA continues to teach the young intern about Tao and DB2.

"Soon, I will be retiring, and all of this will be yours," said the expert DBA. "Are you ready?"

"I am trying my best," said the intern. "Do you have any more advice for me before you go?"

"Ahhh... you are wise to ask. Let me again reach into the Tao to deliver a lesson..."



Squinting confusedly at his mentor the intern mumbled "…but a fish has nothing to do with my job."

The Taoist DBA corrected his intern saying "There are lessons to be learned everywhere and from everything."

The lesson here is that you should not attempt to fix what isn't broken. Just like a small fish can be spoiled simply by handling it, so can a well-tuned DB2 application. Establish service level agreements and if all is running within the agreed-upon service level, leave it alone.

"Another lesson from the Tao," said the elder DBA, "is that by giving as much care to the end as to the beginning, there will be few failures."

"You have done a great job preparing me," said the intern. "I feel my confidence rising,but I am frustrated that I am not yet a master."

Taoist DBA winked and said "Then you know how it feels!"


The lesson here is that persistence is a quality that all successful DBAs possess. Perplexed by a problem, it is always that next “thing” that will solve it… and that is a good quality. Nobody can know everything, but the more you work at it, the more things will become second nature to you as a DB2 DBA. 

"As a final lesson, let me tell you that you will learn to be ‘one with DB2’ as you apply your knowledge and these lessons to the job at hand.  Don’t panic, use your training, and the Tao of DB2 will flow through you…" were the next to last words the Taoist DBA had to say to the intern.





And with that, his mentor disappeared...


Thursday, December 22, 2016

Happy Holidays 2016

It is that time of year again. When we all take some time to pause and spend time with our family, friends and loved ones. We take time off of work to celebrate and reflect on the past year... and to welcome the new year.

No matter what holiday tradition you celebrate, I wish you an enjoyable holiday season. Happy holidays, that is!



And we'll meet back here again in January 2017 to talk more about DB2 stuff!

Happy Holidays!

Thursday, December 15, 2016

The Tao of DB2 - Part 14: A Short Lesson on Wisdom

After a long day of dealing with problems the DBA and his intern were readying themselves to head home for the evening. The mentor glanced over at his trainee and saw that something was troubling him.

"You seem to be troubled," noted the DBA.

“I guess so," replied the intern. "We did a lot today, but a lot of our time was consumed answering what seemed to be the same, or at least similar, questions over and over. Surely it must be annoying to you to answer the same question multiple times a day?”

“Hmmm,” said the Taoist DBA, “how many times have I answered that?”

The lesson here is that every DBA must be ready to assist without wanting to bask in glory. Solving problems is more important than who caused them, and even who solved them.

"Remember always the reason we are here," replied the mentor.



"Not everybody has had the same access to the information that we as DBAs have. That can mean that sometimes you will have to explain things that you probably have explained many times before. This is not a bad thing. What is worse is to not even be asked!" said the DBA.

"Again, the words of Lao Tzu can help. He teaches us..."


"So I am a sage?" questioned the intern.

"That you have to ask tells you that no, you are not yet a sage," replied his mentor. "But keep learning and you may yet be able to act as a sage soon..."

Monday, December 05, 2016

The Tao of DB2 - Part 13: Constantly Learning More

It is time, once again, to look in on our intrepid young DBA as he continues to learn database administration as an intern to an older, wiser, but soon-to-be-retiring DBA.


The intern approached his mentor and said "You have taught me a lot in the past few months. I am more prepared now when it comes to supporting the developers, database/application performance management, making database changes, backup and recovery, and more. But surely there is more to learn?"

Impressed, his mentor shook his head and said "You are securely on the path to becoming a good DBA. Knowing what one does not know is as important as knowing what one does know!" 

He continued... "With that in mind, remember the Tao and the wise words of Lao Tzu, who said 'To know, yet to think that one does not know is best; not to know, yet to think that one knows will lead to difficulty.' "

Just then a developer came into their cubicle with his hair on fire shouting about not being able to access the test databases. "Oh, I’ve seen this problem before," said the intern. " All I have to do is start the database like before and…” 

“To a hammer all problems look like nails,” snapped his mentor. "Learn this lesson well! Do not rush to a solution, even when things may seem to be obvious. Take your time, learn all there is to know about the issue, and only then respond. Though you should rely on your past experience, do not force fit that experience to every problem."

"Hmmm..." said the intern, as he displayed the status of the test database to see find a table space that was in copy pending status. "It might be better to back that up than to just force start it."

"Yes,' sighed the mentor contentedly. "Every day is an opportunity to learn something new. Patience is always a virtue and humility is a quality that all DBAs would do well to exhibit."



And then the phone rang. The intern picked it up and listened as the person on the other end started to explain a WebSphere issue. His mentor listened intently to the intern and picked up on it not being a DB2 problem. Just as the intern was about to balk at helping his mentor stopped him and politely told the person on the phone to hold on for just a moment.

"But that is a WebSphere problem, not a database issue. Surely that is not my job?" balked the intern.

The mentor clucked and explained to his protege "When you say it is not your job, soon it will not be your job." 

The lesson here is that a good DBA must be a jack-of-all-trades. Expanding your knowledge and sphere of influence is always good. The more you know, the stronger you will become at your job and the more employable you will be in the long run.

"I feel like I have to know everything," complained the intern.

"Someone has to," laughed the Taoist DBA.

The job of database administration is complex and you cannot know everything. Learn as much as possible and always be willing to take on new challenges and learn from others. Understanding who is the expert in each technology at your company will help you to tackle problems in your own management discipline. Network with other DBAs, system programmers, developers, web masters, network administrators, and so on... both within your company and externally.