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
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.
Registration process is also available here. So hope to see you there.
DB2 Administration and Application Development on Cloud (BlueMix)
Matching: db2 X
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]
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.
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]
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]
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.
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]
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]
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]
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]