How to Configure JDBC Datasource (JNDI) in Tomcat 7 with DB2 in Ecplise Example.

The following steps tells how to Configure JDBC Data Resources (JNDI) in Tomcat 7 with DB2 in Ecplise with Example. Assume that Eclipse is configured with Apache Tomcat Application Server 7.0. A database connection pool creates and manages a pool of connections to a database. It is always better to recycle and reuse already existing connections to a dB than opening a new connection. Tomcat supports DBCP connection pool from the Commons project by default. It is also possible to use any other connection pool that implements javax.sql.DataSource Suppose...

Read More

Java to create & use DataSource Object in DB2 in the application using DataSource interface without using tools

The preferred way to connect to a database is using DataSource interface rather than using DriverManager interface. DriverManager class requires JDBC driver class name and driver URL. So we have to mention the Driver class and Driver URL which are specific to a JDBC vendor, driver implementation. This reduces the Portabilty of the Application. To improve potability of your applications among data sources, DataSource interface may be used. JDBC version 2.0 provides the DataSource interface. The following program creates datasource object to connect...

Read More

How to display the number of rows affected in DB2 INSERT / DELETE / UPDATE / MERGE commands

 By default, DB2 does not print the number of records affected when we run the DML  commands like INSERT,  DELETE, UPDATE or MERGE in the command prompt. So to display number of records affected when you run the above DML commands, you can use  following options. You  can use the option m, in the command itself as given below db2 -m update employee set payscale='42000-3%-72000' where doj<='1976-01-01'   Number of rows affected : 50 DB20000I  The SQL command completed successfully. Also, You...

Read More

DB2 Gui Tools and Commands to start GUI tools

DB2 Universal Database (UDB) has many graphical tools that make the DBA’s database management job easier and it improves the productivity of the developer in application development. The following are the Gui Tools available in DB2 UDB. 1. Control Center , 2. Command Center , 3. Configuration Assistance , 4. Health Center, 5. Task Center , 6. Development Center , 7. Journal , 8. Memory Visualizer , 9. Information Center , 10. Replication Center Now We are going to the use of db2 gui tools and how to invoke these Tools All the GUI tools can be...

Read More

How to Write own Java user-defined functions in DB2 & call UDFs in DB2 SQL with Example Step by Steps

            User Defined Functions (UDFs) are own functions written by the user which can be used in SQL, DDL or DML statements. UDfs can be written in two ways on DB2 Universal Database. One is SQL UDFs which is based on procedural extensions to the SQL language, highly used by other DBMS. The Other one is External UDFs which is written host languages like Java and others. Now let us write a user defined function in Java which can be used in in SQL. For example, In oracle INITCAP function returns the first letter...

Read More

How to call db2 stored procedure from java program with example?

JDBC provides three statement interface which is used to send an SQL statement to the database server. 1. Statement 2. PreparedStatement which extends Statement 3. CallableStatement which extends PreparedStatement Vendors of JDBC Driver provide classes that implement the above interfaces. Without a JDBC driver, you cannot create objects based on these interfaces. Database connection is required to to create statement object. This tutorial...

Read More

Solution for DB2 transaction log for the database is full. SQLSTATE=57011 .

Before go to the solution, Please go through the post to understand the LOGFILSIZ parameter and how to change the log file size. In DB2 ,Some times you may get the following error,when any transaction like Insert, Update or Delete occurs on a database. DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0964C The transaction log for the database is full. SQLSTATE=57011. The above error may be caused due to the database log file is not...

Read More

Create Database in DB2 with Unicode (UTF-8) support - Command

By default, in DB2, databases are created in the code page of the application which creates them. In windows, Simply create a database with the following command db2 create db employee             - where employee is the database name. By default employee database is created with the code page 1252 where as code page 1208 will have utf-8 support. You can see the codeset of a database by giving the following command. In Windows :  db2 get db cfg for employee | find "code" In Linux :   db2 get db cfg...

Read More

Db2 Jars and Class Path set to connect db2 database using java code with JDBC driver.

If your are using DB2 database in your java application, it is always required to write code for connecting DB2 database. Db2 Database can be connected by either using connection pool or  using JDBC. The following example java code is for connecting and accessing DB2 database using JDBC. It loads the DB2 JDBC Driver using Class.forName  and connects to the DB2 database Employee and prints the records of the table Department....

Read More

How to read images from BLOB field in db2 database table using java code.

     Tn this tutorial, we are going to see how to read Blob images stored in a db2 database table using java code. Let us assume that large objects like images or audio file (eg. Employee photos or Mp3 file) are stored in a db2 database table using BLOB fields. For storing images in db2 table, you can visit Store Images in BLOB. The images or audio files are stored as bytes in DB2. Db2 table stores only descriptor (locator...

Read More

Execute query using PreparedStatement in DB2 with example code in java

       Some times you may need to execute the same SQL statement many times with different parameters (input values). In this situation the best option is to use a PreparedStatement object. PreparedStatement in java is an interface that define the methods and properties that enable you to send SQL statements and receive data from database server. JDBC Drivers developed by database vendors provide classes that implement the PreparedStatement interface. Without a driver you cannot create objects on this interface.   ...

Read More

Storing Images with BLOB in db2 , How to insert Large Objects (audio , photo, etc) in db2 using java

           Do you need to store Large data objects such as Images (jpg, jpeg, gif , bmp , etc ..) and audios (MP3, etc..) in db2 database. DB2 provides specialized data types called LOBs (Large Objects) for storing large data objects. For storing large text, CLOB data type is used. For storing binary data ( photo images, audio songs), you can use BLOB type.           BLOB is Binary Large Object which can store binary data and the default size for BLOB is 1 mb. It can contain up to 2 gigabytes...

Read More

Db2 Terminate Vs Connect Reset , Disconnect

db2 Terminate and db2 connect Reset both break the connection to a database.              Connect Reset breaks a connection to a database, but does not terminate the back-end process. The Terminate command does both ie. break the connection to a database and terminate the back-end process. Suppose an application is connected to a database, or a process may be in the middle of a of work. We can use TERMINATE to make the database connection to be lost. When Terminate is issued, an internal commit is also...

Read More

Example SQL injection in db2. How to Solve SQL injection in db2?

SQL injection is a technique to attack web applications by inserting malicious code (user supplied data) into strings which are passed to an instance of Database Server for parsing and execution. SQL injection vulnerabilities allow an attacker to inject (or execute) SQL commands within an application. It is one of the dangerous application vulnerability. One way for avoiding SQL Injection, the user supplied data should be validated in both client side and server side  before parsing & executing the SQL statement so that it should not...

Read More

Db2 export command example to export data to external file format (del , ixf )

Suppose you want to transfer data from a db2 database table to another db2 database or within the same database, one of the solution is to use db2 export / import command . Using db2 export / import you can move your data to excel also. DB2 export command exports data selected by SQL statement from a table or view to an external files in the format of del, asc (ascii format), wsf, ixf etc.. del - delimited , wsf - worksheet format , ixf - Integration Exchange Format. The DEL format uses delimiter and column separator and decimal point....

Read More