Manoj K Sardana works for IBM Software labs information management group . He graduated from National Institute of Technology Surathkal, India with B. Tech in computer Science. He is IBM certified DB2 V91 application developer and solution developer for XML and related technology. In free time,he enjoy playing cricket and listening music.

Transaction logs

Yesterday One of my colleague pinged me regarding a problem she was facing with the DB2. As per her description, the application is trying to reset the database (which I interpreted as trying to clear out data so a lot of delete/updates), and during the operation, she is getting sqlcode of -964 which says transaction full. As per her she has done this operation a lot of time and never faced this problem. Whats new now ? what changed that the more logs then usual are getting generated ?. I saw the diag.log entry which directly indicating the same reason "transaction log full". I suggested her to increase transaction log my increasing the logfilesz. After trying a size of 10 times the the normal setting, the problem still persist. We tried increasing the number of primary log files too but the same result. This gave me a doubt that there is something wrong with the application. As she didn't have the code for the application, I suggested her to try out the infinite logging option. Unfortunately, that too failed as its started giving error disk full. There is surely something wrong with the application. When i saw the diag.log, here is the entries

2008-07-22-02.17.25.857213+360 I4618169C459 LEVEL: Error
PID : 24342 TID : 1 PROC : db2logmgr (TRADEDB) 0
INSTANCE: db2inst1 NODE : 000
FUNCTION: DB2 UDB, data protection, sqlpgArchiveLogFile, probe:3160
MESSAGE : Failed to archive log file S0000000.LOG to
/jdk/db2inst1/TRADEDB/NODE0000/C0000000/ from
/home/db2inst1/db2inst1/NODE0000/SQL00004/SQLOGDIR/ with rc =
-2029060079.

2008-07-22-02.17.25.891161+360 I4618629C323 LEVEL: Error
PID : 24342 TID : 1 PROC : db2logmgr (TRADEDB) 0
INSTANCE: db2inst1 NODE : 000
FUNCTION: DB2 UDB, data protection, sqlpgArchivePendingLogs, probe:1500
MESSAGE : Log archive failed with rc -2029060079 for LOGARCHMETH1.

2008-07-22-02.17.46.893751+360 I4618953C377 LEVEL: Error
PID : 24342 TID : 1 PROC : db2logmgr (TRADEDB) 0
INSTANCE: db2inst1 NODE : 000
FUNCTION: DB2 UDB, data protection, sqlpgArchiveLogDisk, probe:2620
RETCODE : ZRC=0x870F0011=-2029060079=SQLO_PATH "an invalid path"
DIA8514C An invalid file path, "", was specified.

These entries are coming repeatedly and increasing the size of the log and once the disk is full, it gives a dump and shut down the database. The path mentioned in the logarchmeth1 is a valid path and accessible by the user. I am not sure which path is invalid here.

Anyway reinstalling the application (which will create a new database too) solved her problem but this is not always possible specially in real time. Do we have any tool which will recognize the recursive pattern in the log file (it might be caused by a recursive pattern in the application code) and do some corrective action instead of just keep feeling the log untill disk full giving an impression that the system is hanged. Your suggestions are welcome.

new meebo widget

Today I saw a very cool widget from meebo. I saw it in Leon Katsnelson blog on freeDB2.com. Meebo is a site which gives you a consolidated view of all your messengers like gtalk, msn, yahoo etc. It also gives you this widget which will give a flexibility to the people who are visiting your blog or site, chat with you live. You just need to create a meebo id and cut paste the HTML code into some space of your site. I just did that on my ittoolbox blog. Have a look how it works and happy chatting with the people who visit your blog and like to chat with you live.

http://it.toolbox.com/blogs/db2admin/

Data Modelling : how difficult or easy it is.

Today I started reading the book "Database Administration : The Complete Guide to Practices and Procedures". I started with the 3rd chapter which talk about the data modeling. I am not sure if the data modeling work falls into the tasks a DBA should perform. At the same time as its one time activity, having a independent team to do the data modeling may result into no-work for the team once the database is implemented. As there are a lot of tools available to model the data, despite of that, is data modeling is really difficult ?. The book says efficient data model will allow you to minimize the data redundancy, maintain integrity, better data sharing and access and maintain consistency and while modeling you should consider how the data can be used in future instead of the current usage. But predicting about how the data can be used in future and really a difficult task. With new technologies coming every day, the usage of the data can go any way. Predicting the future use correctly will really make a data model a good one (of course, prediction should be right for that).I think now I need to start looking for some article which will give me some insight on how to predict the different manner a data can be accessed in future?. Do let me know if you know anything related.

IDUG India 2008

I got a chance to present in upcoming IDUG event this year again. Last year while I was totally in pureXML support for DB2, this year I am more towards the Java Programming and performance. This year my topic for the session is "Approaches to Java application development: Static Vs Dynamic" and I will be covering the different options a database programmer has while developing java application. There are a lot of technology available to interact with database from your Java web application. Two of them are EJBs, JPA. Taking an example of EJB, most of the time EJBs are generated by the tools and frameworks and doesn't give you the control on the SQL statements. IBM comes up with a new technology pureQuery, which allow developer a complete control over the SQLs and at the same time provide a high performance access to the database. To know more on that have a look at this article

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0804lamb/?S_TACT=105AGX11&S_CMP=ART

In my session, I will be covering the different options available to database developer specifically DB2 database programmer. It includes the traditional ways JDBC along with the newly available ways like pureQuery. I will also be differentiating between the 2 ways to executing an SQL ie Static and Dynamic.

You can see the complete brochure of the event at the IDUG site here.

http://idug.org/wps/portal/idug

Registration process is also available here. So hope to see you there.

A Trip to Goa

After almost 3 months of hard work in the office, I thought of taking a break and planned to make a trip to goa. Being very near to Goa in my college days, I never get a chance to see this most preferred tourist place in India. It was a off season there, but I planned this trip with some of my friends (or I will say they planned and I just joined). We spent 2 exciting and happening days there. From the first day vigator beach, where we are almost in between the high tide sea layers and still survived to the last days in forts and temples, we enjoyed to the fullest. Some of the things to remember apart from the tourist places are the play of cards in train, Tamil's singing, the view of Dhoodhsagar falls from the train and not to forget the video shoots from none other then me. It was a pleasant break to refresh all of us to start working again.

DBA Traits : Did certification really help

Yesterday, I read a small article on DBA traits in Craig Mullins blog. I agreed to what he mentioned in the article. Some traits he mentioned are organized and capable of succinct planning, adaptable, insatiably curious and should have some people skills too. He says "DBAs are expected to know everything about everything -- at least in terms of how it works with databases". When I shared this article with some of my colleagues, I was a little surprised by the responses. One of the question come out of the discussion is "Do DBA certification help becoming a DBA ?". Some says no as it will never give you a feel of real world problems. According to me it depends on who is doing the certification. A person who the real work experience may see no value in doing the certification however for a person who never get a chance to see the real world, A DBA certification may help at least giving a little insight into the kind of problem DBA face. It will at least enable then to see the problem in a right manner and approach in the right direction. I will say certification is the first step to become a DBA and a DBA will never want to go back to the first step. So value of certification depends on the step where you are in the DBA ladder.

While I was in discussion with my colleagues, I was looking for some book which will give me a complete list of task which a DBA need to perform. I found a book "Database Administration: The Complete Guide to Practices and Procedures" again written by Craig Mullins. Hoping that I will find the stuff which I am looking for in this book.

DB2 Advance DBA certification - finally complete

Finally after 3 months of preparation, I have completed the DB 9 Advance DBA certification. It was my 2nd attempt. First time when I gave this exam, I was in an impression that it will be similar to the exams I gave earlier (DBA and Application Development) where I can just read certification guide and will be able to clear it but that was not the case here. When I attempted first time and failed by 4 marks I realized, I do need to read some concepts very much in details. Specifically HA and Performance. HA and performance makes 52% of the exam and you need to have good hold on these topics to get a good score. So I started reading the complete performance and HA guide. While reading I really felt that DB2 is not just SQL and some monitoring stuff. There are a lot in it. It also gave me the feeling how hard it can be to tune a database and how difficult it would be for a DBA to tune a database which is huge in size. This encourage me to read some more administration guide and go for DB2 problem determination certification. And yes that is my next goal for this year. Another certification and a new way to see a database tuning and problem determination.

In case you need some tips and questions, let me know. I will be happy to help who likes DB2 administration.

Package Cache in DB2 LUW

I was a little busy from past sometime so didn't get a chance to post anything. There are a lot new things I read from last 1 month. There are some interesting posts from Susan Visser about the availability of the books in India, about the salary survey, about IDUG and some polls. I also hear to the podcast from John Boyer about the XForm 1.1. but one interesting things I tried during this time is the use of package cache in DB2 LUW.

In my last post I talked about the advantages of Static run over dynamic. A dynamic query goes through the same phase as of static. The only difference is that in case of static, DB2 saves the compiled SQL statements in catalogs while in dynamic compilation occurs every time. So if DB2 provides some mechanism to save the compiled SQL in memory and use this in future if the same statement encountered again, A dynamic statement can give performance benefit even better then static in some cases. Package cache serve this ppurpose. If you think, in your application most of the trasactions are repeating, increasing the size if package cache (pckcachesz DB config parameter) will allow DB2 to save the compiled dynamic statement in memory and reuse it. This may not give you advantage if your statement is not repeating. The first time the dynamic statement will take its own time as it need to be compiled but from 2nd time onwards you can see the performace banefit. The real questions here is, will it be the alternate to static statement? i think its not. I am not sure how many statement we can cache ? . Apart from that, this activity is totally depend on the DB Manager when it decides to cache and when its not. If there are a lot of statement compared to the size of the cache, there are the possibility that the compiled statements are overwritting each other and hence providing no banefit. Apart from that this cache is allocated whenever the database is initialized and freed when the database shut down, hence the statement need to be cached every time the database initialized again.

Static or dynamic ?

Any SQL statement within DB2 can be executed either in a static way or dynamic way. While static gives the performance bonus at runtime, dynamic gives flexibility to decide on the query at run time itself. Any SQL statement execution goes through various phases like compilation, symentic analysis, query rewrite, access plan generation and execution etc. The basic difference between static and dynamic execution is time when an SQL goes through these phases. Static behavior takes the benefit of SQL known at the compile time and hence an opportunity to create the access plan at compile time itself.So at runtime DB2 will only execute the access plan. In dynamic all these phases will happen only at the run time.So one can say that any SQL can be run dynamically but not all can run statically. For static behavior to occur, SQL should be known at the compile time and the object referenced by the SQL should exist in the database as they are required to complete the access plan generation phase at compile time.

DB2 provides different ways of running an SQL statement statically. While C language provide embedded C for static behavior, java provides SQLj language (Embedded SQLj in Java) for static execution. For any statement to run statically, DB2 need to store the access plan in the database so that it can be used at the runtime. The object which is used to store this information is called packages. For each static application, DB2 creates a package with contains the details of each access plan and the corresponding SQL statement. For example code snippets for static and dynamic execution have a look at sqllib/samples directory. It contains samples for CLI (under cli directory), embedded C (under c directory), JDBC (Under java/jdbc directory) and SQLj (under java/sqlj directory).

Communication Error.. connection failed

Last week, I got a question regarding the connection failure. This problem is very common in DB2 and mostly because of the TCP/IP communication was not enabled properly. I have suggested the following steps to test if the TCP/IP is setup up properly.

1. Check that the DB2COMM registry variable os set to TCPIP.
2. The port number is defined properly in services file. Better to use port number directly instead of the name of the port.
3. dbm cfg SVCENAME parameter is assigned the correct value.
4. If the database is remote, it is cataloged properly.
5. Try pinging the server machine. Check if the IP for the server is dynamic. In which case server IP can change resulting in communication lost.
6. Try running LIST DATABASE DIRECTORY command and make sure that the database appear in the list.
7. Try running LIST NODE DIRECTORY and make sure the server node is cataloged properly.
8. Check if there is any firewall which is preventing the access to the server.
9. Try connection to the server using TELNET and DB2 Port.
10. Try connection to the database from CLP.

These are very simple tests to make sure network communication is fine. So next time you see any connection failure, try these tests to check the communication.

Create your Web Application free..

Today while browsing developerworks, I came across this tutorial which explain how to setup your system to create an web application from free softwares suits from IBM ie DB2 express-C, Eclipse and WAS Community edition. The tutorial explain how to install, configure and integrate these component and start creating your application. I think this will be beneficial for the students for their projects and at the same time for the people who likes to learn and create their sample web application and see the power of this suit. Here are what this tutorial cover in 2 part sessions.

# Downloading and installing DB2 Express-C 9.5

# Creating databases and manipulating data with tools in DB2 Express-C 9.5

# Downloading and installing Application Server 2.0

# Managing Application Server through the Web console

# Connecting Application Server to DB2 Express-C 9.5 using a JCA 1.5 connector

# Downloading and installing Eclipse

# Installing the Eclipse Web Tools Platform (WTP) server adapter for Application Server (formerly called the Application Server plug-in for Eclipse)

# Managing, browsing, and editing DB2 Express-C 9.5 data through the Eclipse IDE

# Testing Web applications in Eclipse using existing Application Server installation

# Rapidly developing and testing a JSP/JSTL Web application in Eclipse, with data access to DB2 Express-C 9.5, and deploying it to Application Server

# Configuring Application Server as a general Web server on the Internet

And here is the link to the tutorial first part

http://www.ibm.com/developerworks/edu/j-dw-java-kickstart-i.html

So enjoy reading and create your web application for free.

DB2 training to customer

As I promised that I will be putting the questions asked by the customer on DB2, so here are the some.

1. One of the questions was on GTT(Global Temporary Table). As documented, GTT are at the session level and will be flushed out once the session is closed. The question was, can we have 2 GTT with the same name in 2 different stored procedure. I think its possible but it seems it may create a conflict when we try to call both the stored procedure using same connection. As GTT are at the session/connection level, the GTT created in second stored procedure may conflict with the existing GTT created in the first. I wonder if the GTT are flushed out as soon as we come out of the stored procedure execution. I still need to play and find out the correct answer. Your feedbacks are welcome.

2. Second question was on stored procedure. As we know execution permission on the package are enough to call a stored procedure, now if one of the stored procedure is calling another inside it, do we need to grant explicit execute permission on the internal stored procedure or giving the permission on the external stored procedure will implicitly grant the permission on internal too. The question here was, in there scenario they have a lot of nesting of stored procedures and giving explicitly permission on each of the nested procedures is really cumbersome.

3. Oracle gives a flexibility to provide external hints to the SQL for optimization. According to them, these hints are really useful for them as they can force the query to use some indexes. They have the question that do we have something similar. Yes we do have but we never encourage to use it as DB2 optimizer is very much intelligent enough to decide on the indexes to be used and providing these hints may force optimizer to use user provided hints and may degrade the performance.

4. Do we have compiler directives ? I am not sure what they mean by this. There might be something in oracle.

Your comments on these questions are welcome.

Manoj

The eventful 4 days

The last 4 days was very happening days. Last Friday, I have started from Bangalore to Delhi to attend the marriage of my friend in Chandigarh which is 250 KM from Delhi. My flight got delayed by 2 hours followed a 2 and half hours in queue for security check in india's silicon valleys airport which delayed the flight for another hour. The airport is still the same as it was 5 years back but the city changed a lot. The speedy growth of the city exponentially increased the population of air traveler's and hence a lot more flights are coming to Bangalore. But small size of the airport is not able to take the load now. The queue was so long that the hall is full and the queue is started coming out of the airport, hopefully the things will improve when a new airport will open march next year. Later on saturday we met an accident when we are going to chandigarh by road. After seeing the car's condition, everybody should have said we are really lucky. Then 2 days of enjoy followed by the work.

After these eventful days, I participated in the DB2 Training to a customer which was again eventful in its own way. A lot of questions and a lot of discussions. I will put these questions in my next blog entry.

mashups..

Finally, I am able to successfully install the IBM Mashup starter kit with the help of Lauren. I come to know about this kit from Sreekanth blog entry "Get it done quickly - Mashup". In this entry he mentioned that this kit will help creating a dashboard with the data from various sources with zero coding,So This looks interesting and I thought of giving it a try. After 2-3 days of struggle and with the help of Stephen and Lauren, I was able to successfully install both QEDWiki and mashup hub which are the part of this kit. I still need to spend some time to play and do some experiment with it. README provided with this kit says that you need to use Express-C version of DB2 and zend core version 2.0.4 or later. But it works fine even with DB2 V9 ESE but not with previous version of zend core.

I will write about my experience using this kit soon in an another blog entry. Till then enjoy reading.

DB2 9 new features - A good book to learn DB2 new features

As not all the certification guides are available for DB2 Version 9, It looks a little difficult to go for the certification for V9. Susan Visser mention in one of her blog entry to read the book titled "IBM DB2 9 New Features" along with the corresponding V8 certification guide to prepare and pass the V9 certification. I wondered if this book is available in India and asked the same question to Susan and got a pleasing answer, the book is available through tata McGraw-Hill publication. When I visited their site, I found they need a manual order and I was not sure how many days it will take to get it delivered to me. Then after a google search, I found that you can order the book online though a cb-india portal, and they are delivering the book within 3 days from the order. They are giving 20% discount too. I just ordered the book as I need it for preparing for the DBA certification. Here is the portal link

www.cb-india.com

Here are the contents for this book, looks really interesting. It seems V9 features are covered thoroughly. Susan mentioned in her blog
"One of the major selling points of this book, besides the fact that it lists all the newest features is it's coverage of XML. There is no other book on the market that has such thorough coverage of XML on DB2 9."

So it will be nice reading. I am waiting for this book to reach me soon.

Part I. XML in DB2 New Hybrid Storage Engine
Ch. 1. What is XML?
Ch. 2. When Do I use pureXML?
Ch. 3. pureXML: The DB2 XML Database
Ch. 4. Tuning XML Performance

Part II. Autonomics and Manageability
Ch. 5. Up and Running Enhancements
Ch. 6. Self Tuning Memory Manager
Ch. 7. Automated Storage
Ch. 8. Other Autonomic and Manageability Enhancements

Part III. Application Development
Ch. 9. Rational Data Architect
Ch. 10. DB2 and the .NET Developer
Ch. 11. DB2 and the Java Developer
Ch. 12. DB2 and the Scripting Languages Developer
Ch. 13. DB2 and the XML Developer
Ch. 14. DB2 Developer Workbench
Ch. 15. Other Application Development Topics

Part IV. Security Enhancements
Ch. 16. Label-Based Access Control
Ch. 17. Additional Security Enhancements

Part V. SHARP Enhancements
Ch. 18. Larger Table Space Support
Ch. 19. Larger Object Support
Ch. 20. Raw Compression
Ch. 21. High-Availability Enhancements
Ch. 22. Additional SHARP Enhancements
Index