Finally, there it was, the most exciting and awaited week of the year. I got a chance to live my dream, to be at the International DB2 User Group Tech Conference (IDUG), Philadelphia. I must confess, “Its not about being at a conference, its about the whole new experience with which I will live ever after”. Being a first time attendee and speaker, the blogs on IDUG experience from Ember Crooks (at db2commerce.com) and Pavan Kristipati (at db2talk.com) really helped me. Also, slide-decks from the past IDUG conferences served a good reference and a starting point. I reached out for tips to the people I admire from the DB2 Community – Mike Krafick, Ember Crooks and Pavan Kristipati. They happily extended their helping hand towards me and Mike actually reviewed my presentation and helped me to improve it. Thank you Mike! Here is a summary of tips that these experts offered – I hope it will be of help to the first time attendees and speakers.
In part-1 of Federated Database Performance, we discussed what federation is, how it works and a few tips at the query level to improve the federated database performance.
In part-2, we will discuss few configuration parameters that will help us in performance tuning of the federated systems.
The performance of the query which includes data access on the remote databases mainly depends on the PUSHDOWN mechanism. Almost all parameters that IBM provides for improving the performance of the SHIP operation is dependent on this mechanism.
Using pushdown mechanism, DB2 optimizer tries to push as many operations as possible to the remote database server so that filtering of data can be done at the remote server and only the required data is transferred to the federated server. So using pushdown, we can filter the data remotely instead of bringing all data to the local DB2 server and then perform filtering. This will improve the performance of the database system by reducing the network traffic and data transfer cost and time.
SYSCAT.SERVEROPTIONS view has some interesting parameters which can be helpful in federated environments. SYSCAT.SERVEROPTIONS view catalogs the federated server settings for each remote data source. Each row specifies the server-specific options and corresponding values.
Few basic and important parameters are discussed below.
IBM’s Federated Database Technology:
In large organizations, usually there are many units working independently on different modules of an application. Each unit may use different relational or even a non-relational databases for the development. There must be a technology that addresses the communication issues between these different database management systems. This is exactly where IBM’s Federated Database Technology comes into the picture.
Formally, federation technology can be described briefly as:
1. Federation allows the users and applications to access data from more than one RDBMSs in a single request.
2. Its a special type of distributed database management system that allows the distributed request across the multiple databases at the same time.
3. Federation consists of a DB2 instance, a database or federated database and one or more remote databases of same or different type.
To get more insights on federation, developerWorks article IBM Federated Database Technology is recommended.
Being a developer of DB2 LUW, I had written many Migration scripts where I performed data manipulation on bulk data. Most known problem for such operations on bulk data is that most of the time we face transaction log full problem:
This problem occurs frequently when the amount of data is huge. For example, consider a performance database or production database table having millions of customers and we need to manipulate all the customer data for some value.
To resolve this problem, DBAs usually configure the database parameters such as LOGBUFSZ, LOGFILSIZ, LOGPRIMARY and LOGSECOND. This approach is trail and error approach where we need to set the particular values and try for the operations. Understanding these parameters is not in the scope of this post.
You can get a brief overview of Managing DB2 Transaction Log Files in Ember Crook’s post. You can understand different parameters, settings and the potential issues from this post.
I will discuss the ways to perform such bulky operations using the PL/SQL Stored Procedures.