Processing Options that Affect Program Design
With this blog post we are reaching a half of the planned content of this series of blogs. This fifth part of the DB2 11 for z/OS Developer certification test requires the knowledge about processing options you use when preparing and running your program. The focus is especially on precompile options, bind options, special registers, global variables, and options at the statement level. Below is a brief set of links I found relevant to this field.
All in all, this part is a bit shorter, although there are many details to follow. But again, I would not expect IBM wants you to memorize all individual options, special registers, or global variables. If you find other topics that should be covered, please share them with others in the comments below.
Understand when and how to use precompile options
- You use the precompiler when you have a program containing a static SQL. The introduction to the process is described here. Also, see more details in the previous blog of the series.
- In this section you can see an overview of the precompiler processing, especially it shows the input and output of the precompiler.
- This is how you can use the precompiler (and coprocessor) options.
- All the precompiler options are listed and described here. Also, check the default values that are listed in the next section.
- Please note that if you are using SQLJ in Java applications, you must follow a preparation process that has been referred in the previous However, I believe that this part of the test focuses on traditional applications.
Understand when and how to use Bind Options & Connection Settings
- DB2 has a large number of BIND and REBIND options, all listed here. Also, you can check Steve Thomas' great IDUG presentation about BIND and REBIND options in DB2. I assume you don't need to know every single option in order to successfully pass the test, but you should focus on some of the options which follow below.
- You can refresh the knowledge of BIND processing in the following section.
- As this subsection mentions connection settings, I would focus, among others, on the Bind options related to connections:
- This is how you bind for a remote access and a description of related Bind options.
- The CURRENTSERVER option determines the location to connect to.
- The DBPROTOCOL option specifies the protocol to be used when connecting to a remote server. The DRDACBF protocol has been introduced in DB2 11 as a performance improvement for the distributed applications.
- One of the most important BIND options are those related to locks, review the subsections for further details.
- It also includes one of the most confusing options - CURRENTDATA. You can read more about it on Craig Mullins' blog.
- I also found interesting the USECURRENTLYCOMMITTED option of CONCURRENTACCESSRESOLUTION, which was introduced in DB2 10. If you have experience with other database management systems than DB2, this might sound similar to you to multi-version concurrency control (MVCC).
- You should also be familiar with the isolation levels, for the related BIND options refer to this link.
Understand when and how to use Special Registers & Built-in Global Variables
- Global variables were introduced with DB2 11 and DB2 comes with few built-in global variables. On the other hand, special registers are part of DB2 probably from the beginning (any DB2 historian here?).
- All available special registers are listed here, but not all can be updated by the SET The following section describes the rules for using the special registers, for example the behavior after commit or rollback.
- Two new special registers are part of the DB2 11 improvement for the temporal tables:
- For built-in global variables refer to this page. The two important variables are GET_ARCHIVE and SYSIBMADM.MOVE_TO_ARCHIVE, which are part of the Archive table feature in DB2 11. For more details about their usage follow this link.
- It is worth to mention that beside the global built-in variables, users can define their own global variables, see this link.
- To make it a bit more confusing, there also exist so called session variables (they already were present prior to DB2 11), look here. To get a value of a session variable, you can use GETVARIABLE.
Understand when and how to use options at the statement level
- Certain (BIND) options can be used at the statement level, one family of such options are the isolation levels.
- The lock modes at the statement level are slightly related.
- I also mention SKIP LOCK DATA, which can be useful in certain scenarios. However, use it carefully as it - as the name implies - skips all locked data.
- The world of optimization hints that can be used for influencing the access path selection can also help to customize certain bind and system options. Refer to the statement-level optimization hints. DB2 11 also brought the possibility of overriding the default predicate selectivity estimates at the statement level. However, I would say that these options should only be used with care in very specific cases. On the other hand, it is good to know that they exist at least.
- There are also certain options that can be determined dynamically depending on the DYNAMICRULES option, as described here.
- It is also good to know that in some situations you can dynamically select the plan and package or override the values used for resolving the package lists.
- Did you know that there are two rules that apply to SQL behavior? DB2 and STD as described here. However, it is probably not related to this topic as it affects ALTER/CREATE TABLE and GRANT/REVOKE, refer to this section.