The third episode of our series deals with distributed access to DB2 for z/OS. In this blog post, I would like to change the format a little bit. Instead of providing full links in the text, I will switch to standard hyperlinks in order to make the blog more readable. It should be also shorter and (hopefully) look better. If you have any ideas how to improve these blogs, please share your thoughts in the comments below.
Now let's move on to the main topics of this part. In order to answer the questions from this section, you should be familiar with JDBC, ODBC, .NET and you should understand how to configure them and how to connect to DB2 using these technologies. You should also understand the impact of distributed connections so that you can optimize for performance. For example, what I especially found useful was the section about ambiguous cursors and how they affect performance.
Manage connections to optimize performance
- To understand how to manage connections, I would recommend getting some knowledge about DB2 Distributed Data Facility (DDF) architecture first. A basic description is included in the Introduction to DB2: here and here.
- More details about the protocols can also be found in the Installation guide.
- One of the key things is the definition found in the above links:
- Distributed data is data that resides on a DBMS other than your local system.
- Your local DBMS is the one on which you bind your package. All other DBMSes are remote.
- Make sure to review the section about distributed data access. It shows how you can connect to a remote site explicitly or using three part names, various effects of distributed data access and ways to reduce network traffic.
- More details are written in the application programming guide, section Coding methods for distributed data.
- The Managing Performance guide provides more details about distributed connections from a performance perspective:
- The section "Improving performance for applications that access distributed data" lists several hints for writing performant distributed applications. The key message is to limit the number of network transmissions.
- make sure you understand the types of threads in DB2. This section focuses on distributed database access threads.
- You can also review the parent section and the parent sections discussing options for managing threads.
- These links show how a DBA can set thread limits, look here, and here.
- I think it is also useful to understand how DB2 pools access threads, though it is more a DBA than developer responsibility.
- This section contains some details about queuing of database access threads.
- And a slightly related section about timing out idle threads.
- One of the key options could be PKGREL (MODIFY DDF command).
- Last, but not least, you can learn more about controlling the DDF in the Administration guide and the MODIFY DDF
Identify what makes a cursor ambiguous
- I would start with the definition of an ambiguous cursor - A cursor is considered ambiguous if DB2 cannot tell whether it is used for update or read-only purposes, as written in this section. The section also lists problems that could be caused by ambiguous cursors.
- Interesting reading is also provided in the following Redbook, Chapter 10.
- If you need to refresh your knowledge about cursors in general, refer to this section in the application programming guide.
- The SQL reference can help with the description of clauses that enable non-ambiguous cursors:
Determine whether limited fetch or optimize clause should be used in a query
- This section basically explains the difference between FETCH FIRST n ROWS and OPTIMIZE FOR n ROWS clauses. First look at the syntax:
- The performance guide perfectly explains the reasons for using:
- The difference is also discussed in the Application programming guide.
Use best practices to determine generic program options
- Honestly, I was not sure what should be covered by this topic, because the SQL and BIND options are included in other sections of the test. Below are a few links I found relevant.
- The SQL reference lists precompiler and coprocessor options, ZPARMs and bind options affecting
- The Application programming guide discusses the SQL processing options and BIND options.
- The parent topic of this section lists some other details.
- This section lists the description and default options for SQL processing, for example DRDA options seem most relevant to Distributed access to DB2.
- This might be a bit off-topic, but it describes the application defaults load module created during the installation.
- Again, I would say a bit off topic, but you might want to review the BIND options. For example the RELEASE option and DBPROTOCOL might be crucial for distributed applications.
- The DBPROTOCOL is in addition mentioned in the What’s New guide in the sense of new performance improvements for distributed applications.
Set up a data source (dot net, java, odbc)
- The last subsection of this part expects you to understand how to set up DB2 for z/OS as a data source for several APIs. First thing to note is that IBM provides special guides for JDBC, ODBC, and .NET application programming:
- Here is a good introduction into JDBC connectivity. Also make sure to follow the links. For example, this link shows how to connect to DB2 for z/OS using JDBC and SQLJ.
- To test a JDBC connection you can use the DB2Jcc utility as described here together with the JCC trace for problem determination.
- You can see more about the JCC trace in this nice article.
- Finally, if everything works, you can use JDBC to execute the SQL statements.
- As for ODBC, there are basically two sections you can find on the IBM Knowledge Center. The first one is more oriented to z/OS applications using ODBC, and the second one is for ODBC connections outside z/OS. The API is basically the same, what differs is the configuration.
- Connecting using ODBC is described here, and this is how you register the ODBC driver
- ODBC is comprised of several API functions. To be able to connect, there is SQLConnect(). Also look here.
- This is how ODBC can be used to execute the statements.
- If anything goes wrong you can refer to diagnostic support of ODBC and debug (z/OS applications).
- To learn more about .NET and DB2 see the ADO.NET section.
- You can learn that there are three .NET providers:
- These links might be useful if you are developing using the Visual studio .NET: here, and here.
- If you want to learn more about the distributed access, you can also see the DB2 9 for z/OS Distributed Functions Redbook.