Today I got my developerworks blog working and I am quite excited to talk on the topics I am working from so long and do like to share with others. To start with, I like to share my first RED BOOK and first article. The RED BOOK titled "DB2 Express-C: The Developer Handbook for XML, PHP, C/C++, Java, and .NET" talk about the various aspects of application development with DB2 Express-C version 9. It has dedicated chapter for major languages supported by DB2 and a chapter on XML. You can download the book from http://www.redbooks.ibm.com/redpieces/abstracts/sg247301.html?Open.
My first article is just got published last week. It talks about the new and enhanced XML features of DB2 V9.5. You can have a look at the article here http://www.ibm.com/developerworks/db2/library/techarticle/dm-0711sardana/index.html[Read More]
DB2 Administration and Application Development on Cloud (BlueMix)
Sardana 06000055HU 1,260 Views
XML, XML and XML... most of the data in this world can be represented in XML format. But sometime it is not baneficial to do that specially when their is tight coupling with the data and data is really obeying strict rules. But at the same time, when data need to be very flexible and you like to store only those things which has some logical values, XML is the way. It will save your memory if there are a lot of propeties/column which as null value. In RDBMS, NULL is a logical identity while in XML world, there is nothing like NULL, its just either value exist or not. While in RDBMS, metadata is stored only once (one column name), in XML metadata will be repeated for every record. Then what the benefit ? There is no need to stored metadata at a different location which means no catalog tables. but yes there is schemas which will do the job.While RDBMS cant work without catalog tables, XML can work without schemas.
Now coming to how XML fits within RDBMS ?RDBMS stored the data in a table format. Each column has a strict data typing. So to store XML, either we need to split into the parts which has strong datatypes or assume that full XML, a binary or character streams. In first case, we need to create metadata first and then split the data and in second, we are completely ignoring the metadata associated with the XML document. We need a way where we save the metadata and at the same time there is no need to split the metadata from the XML to retain its flexibility. Which means storing the XML in such a way that it preserve its structure and at the same time fit into RDBMS model. And here comes the innovative solution of pureXML.
DB2 pureXML is a technology which work with both relational and XML data under one umbrella. It can query both XML and relational data at the same time using SQL/XML language. the benefit is that applications now can concentrate on business logic rather then worrying how to handle XML data coming in their way. DB2 will take care of your XML data from storing it in memory to query it, transform it, represent it. DB2 treat XML as just another data type in his repository and provide you the functions to work with this datatype.
XQuery is the language to query XML data and for this new XML data type, DB2 provide you the flexibility to query this data type using XQuery only. At the same time, it integrate XQuery and SQL together using SQL/XML. So you can query a table to select the relational columns where some condition in XML has met. So far So good.[Read More]
Earlier today, I was preparing the presentation on DB2 pureXML. At the end of the presentation, I wanted to put some references and came across these DB2 Games. A nice idea to teach DB2 to the people who are new to DB2. I like the detective game more interesting then business one. It is easier but interesting too. I think it will be very useful to teach people DB2 and SQL/XQuery. Here is the link to download these games.
DB2 Business game
DB2 Detective Gamehttp://www.ibm.com/developerworks/edu/dm-dw-dm-0402kubasta-i.html
Have fun learning DB2[Read More]
Isolation levels are generally associated with DBA activity. But sometime an application developer too want to change it, may be for particular type of queries. So ever wonder how you can update the isolation within level your java application. Do it with the method setTransactionIsolation of connection object for example
where TRANSACTION_SERIALIZABLE is a JDBC constant. Below is the table for constant correspond to each isolation level
TRANSACTION_SERIALIZABLE --- RR
TRANSACTION_REPEATABLE_READ --- RS
TRANSACTION_READ_COMMITED --- CS
TRANSACTION_READ_UNCOMMITED --- UR[Read More]
While working from last 3.5 years under different mangers, I always use to think, on which platform I compare my managers. How I can say which is the best and which is not so good comparatively(might be individual's personal choice but still it will be good if their will be some criteria). I always wonder what to speak when a manager ask me to give feedback. Today I read an article which gives me some points to consider while preparing for such a feedback session. Thanks to my team mate Jayasimha who sent the link for this article. Have a look
Dont miss the link in the article which gives 10 qualities to be measure up and see how many times you like to say thanks to your manager.[Read More]
Sardana 06000055HU 1,450 Views
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
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[Read More]
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.[Read More]
Sardana 06000055HU 1,905 Views
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.[Read More]
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.
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
So enjoy reading and create your web application for free.[Read More]
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.[Read More]
Sardana 06000055HU 1,975 Views
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).
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.
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.[Read More]
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.[Read More]