Adsense

Tuesday, October 28, 2014

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 large enough for the application or due to the transaction to delete / update rows in the table is overfilling the transaction logs. This error can be solved by increasing of LOGFILSIZ, LOGPRIMARY, and LOGSECOND .

To get the DB2 log file configuration, from a DB2 command prompt, run the following command

db2 get db cfg for database name

The output related to log file is displayed as follows .

Log file size (4KB) (LOGFILSIZ) = 1000
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2


In the above configuration , number of Primary Logs are 03 and Number of Secondary Logs are 02 .

Steps to increase number of Primary Logs / Secondary Logs

1. Stop all the DB2 UDB applications. The following command is used to stop all the applications

db2 force applications all

2) Increase LOGPRIMARY, LOGSECOND as per your requrement, if need, increase size of  LOGFILSIZ. The fllowing commands are used to update the logprimary & logsecond.




db2 update db cfg for dbname using logprimary N



db2 update db cfg for dbname using logsecond N



db2 update database config for db2name using LOGFILSIZ filesize

          -where N is greater than what is currently defined

eg.
  db2 update database config for ldapdb2 using LOGFILSIZ 5000

db2 update database config for ldapdb2 using LOGPRIMARY 5

db2 update database config for ldapdb2 using LOGSECOND 10
       

3) Restart DB2 using db2stop and db2start.

Now the problem is  solved..

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 for employee | grep -i  code

Database code page = 1252
Database code set = IBM-1252


 You can also run the following query to get the code-page.

db2 connect to dbname

db2 "Select codepage from syscat.datatypes where typename = 'VARCHAR'"

which returns 1252


We can create the database with unicode (UTF-8) support by the following ways

1) We can create our database from a Unicode (UTF-8) client (for eg. the UNIVERSAL locale of AIX or set DB2CODEPAGE registry variable on the client to 1208)

2) We can explicitly specify CODESET as "UTF-8" with valid TERRITORY code supported by DB2 Universal Database.

To create a Unicode database named "emp_utf" with the territory code for the US, give the following command.

db2 create db emp_utf using codeset utf-8 territory us collate using system

Now again check the code page with the command

db2 get db cfg for emputf |  | find "code" 

which will give following output

Database code page = 1208
Database code set = utf-8

Note : When a Unicode database is created, CHAR, VARCHAR, LONG VARCHAR, and CLOB data are stored in UTF-8 form, and GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, and DBCLOB data are stored in UCS-2 big-endian form.

Tuesday, October 21, 2014

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. For running the program you have to do the following. Assume that Employee database, qual table already been created in DB2.
1. Download DB2 JDBC driver for your db2 version from http://www-01.ibm.com/support/docview.wss?uid=swg21363866.

2. Include db2 driver path in the CLASSPATH
Steps to include to db2 drivers in the class path:

Copy the below db2 driver files  to any folder for example d:\jars\db2drivers.
 1. db2jcc.jar
 2. db2jcc_license_cisuz.jar
 3. db2jcc_license_cu.jar
4,  db2java.zip

              Now set the classpath to include the driver files as follows.
d:\db2test>set CLASSPATH=.;d:\jars\db2drivers\db2jcc.jar;d:\jars\db2drivers\db2jcc_license_cisuz.jar;d:\jars\db2drivers\db2jcc_license_cu.jar;d:\jars\db2drivers\db2java.zip;.
OR permanantly set by Right click on MyComputer ->Advanced Tab - > Environment variables - > select CLASSPATH and edit , set the variable value to above path.

 For connecting db2 in Servlet / DAO in struts, include the above driver files in the libraries (WebContent\WEB-INF\lib). If you use any development tool (IDE) like WSAD or RAD or Myeclipe, then you can include drivers for your application by accessing
                      Project->Properties->  Add External Jar from Project -> Java Build Path -> Libraries Tab -> Add External Jars in the IDE. 
package com.javaonline;

 import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

 public class Db2ConnectJava {
  public static void main(String[] argv) {
  try {
   
 // Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
  Class.forName("com.ibm.db2.jcc.DB2Driver");
  
  } 
  catch (ClassNotFoundException e) 
  {
   System.out.println("Class not found. Please include the right db2 jars in the Classpath");
   e.printStackTrace();
   return;
  }
  
  System.out.println("Great. DB2 driver is loaded successfully");
  
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rs=null;
  try {
   conn = DriverManager.getConnection("jdbc:db2:employee");
   
  if (conn != null)  System.out.println("Connection Sucessful");
  else System.out.println("Connection Failed ");
  
  pstmt=conn.prepareStatement("Select * from EmpList");
  rs=pstmt.executeQuery();
  
  System.out.println("S.No          Emp.Code                        Name                     Pay Scale ");
  System.out.println("----------------------------------------------------------------------------------");
  
  int sno=0;
  if(rs!=null)
  {
   while(rs.next())
   {
    sno++;
    System.out.println(sno + ".           "+rs.getString("empcode") +"                          "+ rs.getString("name") +"               "+ rs.getString("payscale"));
       }
  }
  
  if (sno<=0) 
  {
   System.out.println("No Employees Found");
  }
  
  } catch (SQLException e) {
   System.out.println("Some Error Occurs...");
   e.printStackTrace();
   return;
  }
  }
  }
 
Running the above program, you will get the below output.



Saturday, October 18, 2014

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 or pointer) which points the external location where the actual bytes are stored. 

         JDBC 2.0 provides methods like getBytes , getBlob to read Binary Data or BLOB data from the table. To get the actual images (jpeg , jpg, gif,..) or audio files (mp3,mp4, ..) , we have to read the bytes from the database and write into a external file. Now let us see how the following code works?.

        In our example Employee table has the fields empid, empname, empphoto,filepath where as empphoto is the blob field which is used to store employee photos. filepath contains the external file path including file name. The bytes read from the blob field is written into the external file in the location using filepath. To read the bytes from the blob field, we can either use

           byte[] image = rs.getBytes("empphoto") 
                     OR
           Blob blob_temp =rs.getBlob("empphoto"),
      byte[]image=blob_temp.getBytes(1,(int)blob_temp.length()).

             Static method writeToFile (byte[] image, String fileName) accepts image bytes and fileName. Bytes are written to file using the write method of FileOutputStream. Now the employee photo is ready in the location given in the filePath field.

package com.javaonline;
import java.sql.*;
import java.io.*;

public class Db2BlobRead {

  public static void main(String[] args)    throws SQLException
  {
      try {
     
         //Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
      Class.forName("com.ibm.db2.jcc.DB2Driver");
       
     } catch (Exception e) {
System.out.println(e);
                System.exit(1);
                     }
     Connection con = DriverManager.getConnection("jdbc:db2:test");  
     Statement stmt=null;
     ResultSet rs = null;
try {
    stmt = con.createStatement();
    rs = stmt.executeQuery("select empid , empname, empphoto, filepath from Employee");
while (rs.next())
{  
    if ((rs != null) ) {
 String empcode =rs.getString("empid");
 String empname =rs.getString("empname");
// Blob blob_temp =rs.getBlob("empphoto");
//  byte[] image=blob_temp.getBytes(1,(int)blob_temp.length());
         byte[] image = rs.getBytes("empphoto");
         String fileName = rs.getString("filepath");
         writeToFile(image, fileName);

System.out.println("Employee Code => " + empcode);
System.out.println("Employee Name => " + empname);
System.out.println("Employee Photo read and stored at  : " + fileName);
System.out.println();
                       }
}
     } catch (SQLException e)
                {
              e.printStackTrace();
                } finally {
                     try {
                       if (rs != null) rs.close();
                         if (stmt != null) stmt.close();
                         } catch (SQLException e)
                            {
                             e.printStackTrace();
                              }
                        }
}


// to write bytes into physical file.
private static void writeToFile(byte[] image, String fileName) {
    FileOutputStream file = null;
    try {
         file = new FileOutputStream(fileName);
         file.write(image);
         } catch (FileNotFoundException e) {
               e.printStackTrace();
              } catch (IOException e) {
              e.printStackTrace();
          } finally {
                try {
                   if (file != null) file.close();
                     } catch (IOException e)
                       {
                       e.printStackTrace();
                        }
                  }
            }
}


Output of above program :

Thursday, October 16, 2014

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. 

      As PreparedStatement interface extends Statement, methods of Statement objects such as execute(), executeQuery(), executeUpdate(), and executeBatch() work with the PreparedStatement object. But these methods are implemented to take input the parameters also. To bind values to input (IN) parameters, you can use the setXXX() methods where XXX represents the Java data type of the value. setXXX is used to convert the Java data type to the appropriate SQL data type for your target database. 

Use of the above methods are as follows

1. executeUpdate() is used to execute DML Statements like INSERT, UPDATE, DELETE, or DDL SQL statements

2. excuteQuery() is used to execute SELECT statements to query database.

3. excute() is used to process any DDL or DML statements

4. executeBatch() is used execute group of SQL statements (DDL, DML statements)

The following code snippet describes how the methods executeUpdate(), setInt(), setString() are used with PreparedStatement object. 

 
 String strSql  = "UPDATE student  SET MobileNo = ?  WHERE studentNo = ?"; 
       PreparedStatement pstmt = conn.prepareStatement(strSql);
       pstmt.setString(1,"9876543579");
       pstmt.setInt(2,5321);
       pstmt.executeUpdate(); 

PreparedStatement object has advantages over a generic Statement object.

1. Quicker than the generic Statement object because the SQL statement is precompiled and stored in a PreparedStatement object.  It can improve performance of frequently used SQL statements because this compiled statement is in memory and remains ready to use during different iterations.

2. Gives flexibility to supply input parameters at runtime

3. PreparedStatement object can also be used for statements that take no parameters.

4. PreparedStatement helps in defending against SQL Injection

Let us see an example of using PreparedStatement object, suppose you have a STUDENT table , which is having Student No, Student Name, Class, Address1, Address2 , City , State , Mobile No . Now you have the list of classes, suppose if you want to print the student details of MCA, MBA and B.Com . The following example shows how to use a PreparedStatement object to execute a SELECT statement for the above said example which has one input parameter: Note : either you can use classname or classcode as a parameter . Always better to use classcode.


public void loadStudentDetails() throws CDRCException, CDRCFatalException {
   String strQuery;
   Connection conn = null;
   ResultSet rs = null;
   PreparedStatement pstmt = null;
   ArrayList className = new ArrayList();

   try {
    conn = ConnectionFactory.getConnection();
    className.add("MCA");
    className.add("MBA");
    className.add("B.Com");
   
    strQuery = "select StudentNo, StudentName , Address1, Address2, City , State , MobileNo from student where className=?   ";

    pstmt = conn.prepareStatement(strQuery);  
    
    Iterator it=className.iterator();
    while (it.hasNext())
    {
     String clname=it.next().toString();
    pstmt.setString(1,clname);
    rs = pstmt.executeQuery();

      System.out.println("Class Name :" + clname ); 
    while(rs.next())
    {
     System.out.println(rs.getInt("StudentNo"));
     System.out.println(rs.getString("StudentName"));
     System.out.println(rs.getString("Address1"));
     System.out.println(rs.getString("City"));
     System.out.println(rs.getString("MobileNo"));
    }
     
    }

   } catch (SQLException e) {
    System.out.println( "Error :" + e.toString());
    
   } finally {
    try {
   if (rs != null) {
      rs.close();
      rs = null;
     }
     if (pstmt != null) {
      pstmt.close();
      pstmt = null;
     }
  
     if (conn != null) {
      conn.close();
      conn = null;
     }
    } catch (SQLException e) {
     System.out.println( "Error :"  + e.toString());
    }
       }
   } 

In the above example, same query is used three times with different input values (parameters). When you use a PreparedStatement object to execute an SQL statement, Don't forget to use the PreparedStatement.close() method to explicitly close the PreparedStatement object when you finish processing the results. This frees (deallocates) the resources that were allocated to the SQL statement.

For more methods on PreparaedStatement, you can visit  http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html

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 of binary data. BLOB values are not stored in a database table , instead a descriptor (locator or pointer) is stored. This descriptor points to the physical location of the LOB. The actual LOB values are stored in tablespaces. This topic covers how to put BLOB data into table in the database.

          JDBC 2.0 provides methods for writing BLOBs. Binary Data can be stored in BLOB field using setBytes, setBinaryStream, setBlob methods of the PreparedStatement interface. In the following example, Employee details are stored along with his / her photos. For that excise, we have to create a table with BLOB field for storing employees photos. In our example, Employee table is created which has the fields of EmpId, EmpName, EmpPhoto, filePath. In this, EmpPhoto is a BLOB field. filePath field is used to store original physical file path and its name. This field is not mandatory. This field may be used  to create the image file with the same filename in the same path when reading blob data back. If the file name is not available, then you have to mention the file name with path in the application itself while reading blob data back. Now how to store Employee's photo in the blob field which is  kept as a physical file in a path (f:\temp\images\) in the machine. So we have to read the file as binary data, that can be done  by the below statements


              file = new FileInputStream(filename);

              int size = file.available();

              imageData = new byte[size];

              file.read(imageData); 


             In our example ,we have written readImage function which is used to read the image file and returns as array of byte data to the setBytes method.

package com.javaonline;
import java.sql.*;
import java.io.*;

public class Db2Blob {
    public static void main(String[] args)    throws SQLException 
    {
        try {
          // Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
           Class.forName("com.ibm.db2.jcc.DB2Driver");
       } catch (Exception e) {
  System.out.println(e);
                  System.exit(0); 
                       }
       Connection con = DriverManager.getConnection("jdbc:db2:test");    
       Statement stmt=null;
 try {     
        stmt = con.createStatement();
       //to create table with blob field (One time only)
         stmt.executeUpdate("CREATE TABLE Employee (EmpId varchar (10) , EmpName varchar (50) , EmpPhoto BLOB (5M), filePath varchar(100))");
   
         } catch (Exception e) {
  System.out.println("Tables already created, skipping table creation process");
           }
 String empId="1222";
 String empName="John";
 String empPhotoFile="f:/temp/images/"+empId+".jpg"; // photo file name
 int success=0;
         PreparedStatement pstmt = con.prepareStatement("INSERT INTO Employee VALUES(?,?,?,?)");
         pstmt.setString(1, empId);
         pstmt.setString(2, empName);
         pstmt.setBytes(3,readImage(empPhotoFile));
         pstmt.setString(4,empPhotoFile);
         success =    pstmt.executeUpdate();
         if(success>=1)  System.out.println("New Employee detail added");
          con.close(); 
 }
    
    
 //to read image from the physical file. 
 private static byte[] readImage(String filename) {

      byte[]  imageData = null;
      FileInputStream file = null;
      try {
           file = new FileInputStream(filename);
           int size = file.available();
           imageData = new byte[size];
           file.read(imageData);
      } catch (FileNotFoundException e) {
           e.printStackTrace();
               } catch (IOException e) {
           e.printStackTrace();
      } finally {
           try {
                if (file != null) file.close();
               } catch (IOException e) {
                e.printStackTrace();
                 }
             }
          return imageData;
      }
 }