If you have been working in the software industry for some time, you are probably already familiar with the application design and lifecycle. You usually start with a problem or idea you want to solve, you do some research, some coding, testing, and voila you can ship it to your customers and make some profit. Well, in my experience, it does not work this way in reality.
You are probably developing your application following some of the available methodologies, like waterfall that was popular some time ago, any of the agile methodologies that are popular today, or maybe something completely different.
I think that methodologies are good. But, in my opinion, what matters is the attitude to work. If you like your job and the project you are working on, you will be thinking thoroughly about the application design and its lifecycle, which includes architecture, prototyping, coding, testing, benchmarking, reviews, marketing, and so on.
Moreover, if you like your job and you work with compatible people, you will be doing your best even if you don't like the methodology that is used; and vice versa, if you don't like what you are doing, you are probably not doing your best regardless the methodology.
This part of the certification test is about the application lifecycle. But don't worry, it is not about the methodology you should adopt, it is about the generic tasks you should consider like testing, verifying the results, getting some test data, and performance. And of course, main focus here is on DB2 and SQL.
As usual, we have already covered some of the topics in previous blogs of the series. If you think something is missing, please share your thoughts in the comments to help others.
Ability to validate execution results are correct
- I believe this section is to remind you that you should test your applications and validate the expected results. IBM gives you few hints how to test and debug your application.
- For example, there are subsections discussing how to prepare for creating test data by analyzing application data needs and creating test data structures.
- The next step lists the possible ways to populate the test tables with data. You can use INSERT from SELECT SQL statement, or the LOAD utility for instance.
- How you can test your SQL? IBM lists SPUFI and Command line processor (see my blog about Command Line processor on z/OS USS), but you can use many other tools as well, for example IBM Data Studio.
- And of course your application should always check the return codes from DB2 by testing SQLCODE or SQLSTATE (for portable applications). SQLCA is crucial in this matter as it contains more details. Alternatively, you can use WHENEVER statement to branch to another area in your program if an error or a warning occurs. However, WHENEVER is not supported in REXX or Java applications.
- A factor that is often overlooked is handling and reporting error conditions. What happens if the DB2 instance is down when you try to connect? Are you capturing all nonzero SQL return codes and reporting the meaning and not simply the code? Do you have retry logic where it is needed?
- For reporting the errors and displaying the SQLCA fields, you can use DSNTIAR or GET DIAGNOSTICS with the MESSAGE_TEXT condition item.
Ability to test connections and stored procedures
- So we have two things here - (1) test connections, and (2) test Stored Procedures.
- Let's start with connections. To connect to DB2 from your application, you need to use some of the attachment facilities that DB2 provides, review the overall attachment facilities architecture and see how to connect from the individual attachments.
- If you are connecting from distributed applications you can use DDF.
- From your application, you can access distributed data using three-part table names or using the CONNECT statement explicitly, refer to this section.
- See the CONNECT statement for checking the successful or unsuccessful connection. GET DIAGNOSTICS can help you with validating the current connection, see DB2_CONNECTION_STATE
- To verify that you can connect to a DB2 server from JDBC, use the DB2jcc utility with the -url There is also a -sql option that allows you to submit an SQL statement.
- For ODBC and CLI connections you can follow these instructions.
- Your DBA can control and monitor the connections using the DB2 commands.
- Now let's move to stored procedures. IBM documents several tips how to test and debug stored procedures here. However, please keep in mind that you need to use a technique suitable for the given type of stored procedure. You will use a different means to debug native SQL stored procedures and different tools for external stored procedures.
- To recall how to call a stored procedure from an application program, refer to this What's important is to ensure the procedure completed successfully by checking any SQL error after the CALL.
- You can also check the status of the stored procedure. SQLCA or GET DIAGNOSTICS are your friends.
- Few words about monitoring stored procedures and setting the priorities.
- DISPLAY PROCEDURE command can show you the status of a procedure (started, stopped, …)
Ability to work with DB2 LOAD and UNLOAD utilities
- DB2 LOAD and UNLOAD utilities can be used as means for populating the test tables with some data. You can for example unload the data from the production, mask the confidential data (which is by the way not an easy task), and then load the data into your test tables.
- First, you need to unload the data with the UNLOAD The source for UNLOAD can be tablespaces or image copies. UNLOAD does not allow you to use SQL to match the rows you would want to unload, but you can use WHEN for predicates. You can also use SAMPLE keyword for sampling the rows, if you don't need all of them.
- Make sure to review the sample UNLOAD
- I would also mention DSNTIAUL sample application that was useful before UNLOAD was available in DB2. DSNTIAUL uses SQL to access DB2 and you can include any SQL statement that can be executed dynamically. DSNTIAUL can produce the LOAD statement in similar way UNLOAD can do that.
- To load the data, use the LOAD utility with the generated input from the UNLOAD or DSNTIAUL.
- Also, review the sample LOAD statements.
- Moreover, LOAD in DB2 11 has been enhanced with parallelism in more situations than before. Check the keyword PARALLEL of LOAD, which can reduce the elapsed time.
Ability to troubleshoot application performance
- A previous blog (and the corresponding certification test section) was about the performance as a whole. I would recommend looking there as I will point out only a few items here and in the next section.
- To troubleshoot application performance, you should probably first look at the EXPLAIN EXPLAIN is a great help for investigating the SQL performance.
- Few tips how to investigate access paths problems.
- You should also monitor the SQL performance.
- DB2 provides several facilities and tools for performance monitoring. Proactive monitoring is usually a key. Once you encounter a performance degradation and you have no data, it is usually much harder to figure out the cause.
- DB2 traces can be very helpful in this matter, for example statement level statistics.
- Here are few tips for investigating DB2 performance problems. For application tuning, you should probably focus on thread level performance. Look here for the response times explanation.
- Lock contention is one of the common problems of applications in a concurrent environment. Here are a few tips for analyzing the concurrency problems.
- There are also several user tables that can be used by optimization tools. Check what tools you have available, in worst case you can use them by hand though it is much harder tasks.
Ability to properly utilize DB2 Performance Features
- DB2 has many performance features that you can utilize in your applications. Just where to start? I usually tend to use the worse is better paradigm when I start working. The preliminary optimization is usually not worth it. On the other, hand I usually think about the code paths and what performance problems they can mean. So in general I use worse is better, do not over-engineer for performance, but keep the performance in mind. Of course, this is not a general hint, it may work in some cases, but not in the other ones.
- IBM releases a Performance Topics redbook with every new release of DB2 (as far as I can say). These redbooks are great source of performance hints. The book contains chapters on subsystem performance, utilities enhancements, but also application developer topics. DB2 11 for z/OS Performance Topics describes the performance improvements in DB2 11, especially the Chapter 8 - Sql and Chapter 9 - Application enablement are useful for application developers. However, if you want to learn more about performance enhancements in older releases, you must refer to older redbooks of this kind.
- To see all the performance features of DB2, you would need to review the Managing Performance guide.
- As a developer, I would say the focus should be on the SQL itself, like coding the effective predicates, trying to use existing indexes as much as possible, taking care about the join operations, subselects, table expressions, trying to avoid many calls to DB2, trying to allow parallelism…
- If you checked all of these and the SQL is still behaving badly, maybe it is a good time to discuss it with a DBA. Would an index help? The down side is, as you probably know, each new index causes slowing down the insert operations and more administration work. Would a DDL or a BIND option help? Maybe changing the lock size would help, or moving the table to a separate table space, or possibly a different isolation level; there are just too many possibilities…
- And as a last resort, I would ask about the subsystem configuration, maybe there is something that might help you. Are you using in memory data cache efficiently or are you using just the sparse index (default value for MXDTCACH is 20M), what about sort parameters? Again, too many possibilities to just list a handfull of them. On the other hand, I am pretty sure you would find an IDUG presentation that could help you.
- A good overview for application developers is provided by this section in the Managing Performance guide. It lists the main steps:
- Program your applications for concurrency. See the concurrency recommendations for application designers and how to improve concurrency of your application. Few notes about concurrency in Data Sharing.
- Writing efficient SQL queries and ways to improve the query performance.
- Use EXPLAIN to analyze the access paths data.
- Consider performance in the design of applications that access distributed data. Few tips how to improve the performance.
- Use stored procedures to improve performance, and consider performance when creating stored procedures.
- I would add the following items to the previous list:
- Programming for parallel processing
- Monitor and analyze the performance data, as we have discussed in the previous section.
- Benchmarking is usually very helpful during the development. DB2 provides means how to model a production subsystem on a test subsystem.
- If your shop is using IBM DB2 Analytics Accelerator, your query might be eligible for the acceleration. You can check whether it is eligible using this checklist. The acceleration itself is then transparent for your application and you are not required to change your program - both static and dynamic SQL are supported by the accelerator!