All about REORG, EXPLAIN and NULL in DB2

DB2 REORG, EXPLAIN and NULLS and important questions asked.

What is REORG and how would you use it? Please expand on the reasons for using this command over others?

The REORG command is used on a piece of physical storage to reorganize data. The REORG command is a very helpful command to remember as it allows you to reclaim lost space, restore free space and reorganize overflowed rows back to their correct alignment.

You generally use this command after a period of heavy database use, installs and to restore order to the database in the event it becomes fragmented.  In addition to all of these benefits, you can use this command to increase read performance and server speed.

What is a NULL value? What are the pros and cons of using NULLS?

The correct answer to this question is two-fold. You must first of all know the piece of knowledge and then weigh how you yourself would use them. This allows the interviewers to see how your knowledge maps to your working style, and how well you can apply what you know to specific situations to solve problems.

A good answer to this kind of question would be something like this:

Null values allow you to indicate that rather than a lack of data in any given byte, you can use a Null value to specify that there is no value in that byte instead of anything else. This is similar to how you might block out a few hours on a concert flyer with “To be confirmed”. Whilst this is helpful, it requires more effort on the behalf of the programmer to create a piece of software to realise the database is using Null values in the first place.

How would you go about Implementing a Database 2 Stored Procedure?

Stored procedures are blocks of reusable code that have been saved to access either in the future or by any other program. By saving your reusable code using stored procedures, you can save a lot of time and effort in the future.

You may want to use stored procedures for reducing network traffic and returning specified sets of results to another application. Another use of reusing your code by saving into a sorted procedure could be using them to allow certain programs to access only a portion of the data on the server. This would be helpful for allowing access without granting the privileges to the applications.

Database 2 actually comes with a fair few stored procedures and implementing them is easy. Within the Database 2 software you simply have to create a procedure and then call the procedure through the command line interface or via another program.

As Database 2 is such a large program, you will want to know which are the functions that are used most frequently and which functions are the most use under average circumstances. Your interviewers may also ask you quick fire questions similar to this one:

Explain the EXPLAIN statement?

In this situation, you are expected to explain the requested function of the software. As this is only a quick answer, you will be expected to answer a few of these types of questions to show that you have a wide knowledge base and are fluent with the most useful and common command for Database 2. Being able to answer these questions quickly and decisively will take quite a lot of study as there are a very wide range of commands that will be used over and over. Taking [this course] will help you figure out which commands are the most important and show you how their syntax works and what the commands in question do.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.