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).
DB2 Administration and Application Development on Cloud (BlueMix)
From archive: December 2007 X
Sardana 06000055HU 1,741 Views
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.
Sardana 06000055HU 1,683 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]