Coding SQL in Application Programs
After going through the topics in the previous blogs - Database Objects, Data Retrieval and Manipulation, and Distributed Access to DB2, we are approaching the part of the test 313 related to program coding and preparation called Coding SQL in Applications Programs. From my viewpoint, this part refers mainly to traditional z/OS applications and their preparation, but certainly it might useful for programmers of other applications as well, because there are sections about common principles like error handling and multi-row operations. Also, the differences between the static and dynamic SQL are being discussed here, which might be interesting especially in these days when dynamic SQL is more attractive.
Most of the information found in this section can be found in the Application Programming and SQL guide.
Manage the use of cursors within a program
This section is a huge topic itself. As usual, I would say you don’t need to know every single detail, but still there is a lot of information related to managing cursors.
- A general overview of cursors can be found in the Introduction to DB2 guide.
- An ultimate source of information about various cursors and their usage is this section in the Application programming guide. I will slightly elaborate on this section in the following links:
- The starting point is the section listing and describing the types of cursors. In short - there are two basic types types of cursors - scrollable and non-scrollable. Scrollable cursors have an important characteristics - sensitivity. The scrollable cursors can be - INSENSITIVE and SENSITIVE. To make things more interesting the Sensitive cursors can be STATIC or DYNAMIC. Moreover, the scrollable cursors can follow static cursor model (INSENSITIVE or SENSITIVE STATIC) or dynamic cursor model (SENSITIVE DYNAMIC). Are you still following me?
- The other characteristics of cursors is whether they are held (definition includes WITH HOLD) or non-held cursors. The position of a held cursor after commit depends on the cursor type as described in the previous link.
- The basic usage of row positioned cursors is DECLARE, OPEN, SQL (FETCH, positioned UPDATE, positioned DELETE), and CLOSE as described here.
- Few examples about FETCHing the rows.
- Did you know that instead of fetching just one row only, you can fetch a row set? Refer to this section about row set positioned cursors.
- I’ve mentioned scrollable cursors above. The advantage of scrollable cursors is, well, that they are scrollable.. Also see the example here.
- Make sure you understand the difference between scrollable cursors, see this nice comparison. There is also a section describing how to use scrollable cursors efficiently.
- You can even intermix row-positioned and rowset-positioned FETCH statements.
- OK, now we know how to use cursors. But how to determine the attributes of the cursors in the runtime? Check SQLCA or GET DIAGNOSTICS.
- This section describes how to use positioned You could use the positioned DELETE in a similar manner.
- SQL syntax related to cursors:
- In case you are writing a Java application, refer to the section "Specifying updatability, scrollability, and holdability for ResultSets in JDBC applications". For ODBC application, you can see more detail about scrollable cursors on this link.
Apply best practices for error handling
- Error handling is (or should be) an integral part of every decent application program. You most probably know this from the programming classes or from the practice better than me. As for DB2, you can find some hints how to deal with errors returned from DB2 in the Application programming guide:
- However, this section covers what is usually called traditional applications running on z/OS. Applications using JDBC and ODBC can use the standard means provided by their means.
Demonstrate knowledge of when to use dynamic versus static SQL
- Introduction to DB2 for z/OS guide describes the basic differences between static and dynamic It is also worth noting that Java can be used for executing dynamic, but static SQL also, it is called SQLJ.
- The dynamic SQL itself is greatly covered in this section and its subsections.
- It starts with a discussion of static and dynamic SQL differences.
- The important is to go through the all subsections and see what’s needed for different kinds of dynamic statements. The basic common pattern is PREPARE and EXECUTE.
- Dynamic SQL usually performs worse than static SQL. Here are some tips what to check at the first place.
- The obligatory syntax can be used as a reference for more details:
Demonstrate knowledge of multi-row syntax
- The multi-row operations are one of my favorites. I wrote a blog about them recently, you can read it here (sorry for the commercial). There was also a recent discussion on DB2-L about the multi-row from the performance perspective.
- To start with multi-row operations you should understand the host variable arrays. See here and here. Also follow the links, how to create the arrays in several programming languages.
- The following section of the Application development guide describes how to insert multiple rows of data.
- And here is how you can retrieve multiple rows. This link overlaps with the previous section about cursors and shows how to execute a SQL statement using a rowset cursor.
- Using host variable arrays for dynamic SQL may require more work, see what’s needed here.
- SQL reference can give you more syntax related details:
- If you are developing Java applications, refer to this section. And also a section about making a batch updates, and another one about batch queries. A similar section for ODBC applications on bulk inserts using SQLBulkOperations().
Understand when and how to use DCLGEN
- You can declare the tables your program is using within the program itself. It helps to check the SQL during the pre-compile phase and also provides the documentation within the program. DCLGEN is a tool that helps building the declare statements using the information from the DB2 catalog and generating the corresponding host variable structures for high level languages.
- The advantages of table/view declarations are listed here.
- DCLGEN itself is briefly described here and the following section shows how to use it. You can even see an example with screenshots here.
- Once you have your declarations built, you can include them in your program using this
- As usual SQL guide can help with the syntax details for:
Understand how to prepare the program for execution
- The overall preparation can be understand from the following diagram. It shows an overview of precompile-compile-link-bind steps.
- All the details are contained within this
- It is also worth noting the attachment facilities like the universal language interface (new in DB2 10).
- I would say that this section was mainly to understand the preparation of a traditional z/OS program for execution. The use of a dynamic SQL via ODBC or JDBC is usually simpler because everything is already prepared. In case of SQLJ it is a bit different, more similar to the previously described steps. The overall process is here.