DB2 Equivalant for Rownum of Oracle - ROW_NUMBER() OVER()

Syntax - Select ROW_NUMBER() OVER(), column_name1 from table

SQL0803N / SQLSTATE=23505

Error during Execute
 23505(-803)[IBM][CLI Driver][DB2] SQL0803N  One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "" constrains table "" from having duplicate values for the index key.  SQLSTATE=23505


Probably you are trying to insert a value in primary key / unique key which is already present and thus violating primary key, unique key constraints. Please make sure you are not entering duplicate values in primary key / unique key.

A parent row cannot be deleted because the relationship "R_1" restricts the deletion

Error during Execute

 23504(-532)[IBM][CLI Driver][DB2] SQL0532N  A parent row cannot be deleted because the relationship "R_1" restricts the deletion.  SQLSTATE=23504

You are trying to delete a specified row in the parent table which has dependencies on child table(s). First delete the record in the child table(s) and then try to delete the record in the parent table.

JDBC Example – Batch Update using PreparedStatement

Using Batch Update feature of PreparedStatement you can Insert, Update or Delete multiple records with single database hit. For example if you want to insert multiple records into database using Batch Update you will just need to conect to database once. You can loop through records and add it to bach and once done you can execute the bach i.e insert all records in a single shot.


Below is a code snippet which shows how to insert few records in batch process, via JDBC PreparedStatement

PreparedStatement ps=con.prepareStatement("INSERT INTO BS_Books (cat_id,book_title,book_details,book_price,book_author) values (?,?,?,?,?)");

ps.setInt(1, 1);
ps.setString(2, "Book1");
ps.setString(3, "Details of book1");
ps.setDouble(4, 100.0);
ps.setString(5, "Author1");
ps.addBatch();//Adding record to the batch

ps.setInt(1, 1);
ps.setString(2, "Book2");
ps.setString(3, "Details of book2");
ps.setDouble(4, 150.0);
ps.setString(5, "Author2");
ps.addBatch();//Adding record to the batch

ps.executeBatch();//Executing the batch

Please see the complete self explanatory java code below

Hibernate reverse engineering demo using Eclipse

Reverse Engineering: The most powerful feature of Hibernate Tools is a database reverse engineering tool that can generate domain model classes and Hibernate mapping files, annotated EJB3 entity beans, HTML documentation or even an entire JBoss Seam application in seconds. With the help of Eclipse you can do reverse Engineering. Lets see step by step to reverse-engineer database tables to generate hibernate POJO classes and mapping XML files using hibernate-tools (eclipse).

About the example: I am using My Eclipse 8.6 and MySQL. We will generate POJO classes and mapping XML files etc and latter run a test program. Please see the self explanatory screen shots below.

1. Create a New Java project

File -> New -> Java Project

MySQL - JDBC example for BLOB storage

A blob (binary large object) is a collection of binary data stored as a single entity in a database. Blobs can be used to store images, audio or other multimedia files. There are four variation of blob datatype -

• TINYBLOB - Maximum length of 255 (2^8 - 1) characters i.e 25 bytes
• BLOB - Maximum length of 65535 (2^16 - 1) characters i.e 64 KB
• MEDIUMBLOB - Maximum length of 16777215 (2^24 - 1) characters i.e 16 MB
• LONGBLOB - Maximum length of 4294967295 (2^32 - 1) characters i.e 4GB

About the example: We will create a table in mySql and see how to insert/reterive an image using JDBC.

First lets create a table in MySQL. Please see the syntax below, we have used MEDIUMBLOB as it would be sufficient for medium size object stotage. You can use any other as per your requirement. We have just 3 columns pic_id as a auto increment primary key, pic_name will hold the picture name and pic_file a blob which will store actual picture.

CREATE TABLE blobtest (

pic_id INT NOT NULL AUTO_INCREMENT,

PRIMARY KEY (pic_id),

pic_name VARCHAR(100) NOT NULL,

pic_file MEDIUMBLOB

);

CLI0611E Invalid column name. SQLSTATE=S0022

COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0611E  Invalid column name. SQLSTATE=S0022
    at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.rsException(Unknown Source)
    at COM.ibm.db2.jdbc.app.DB2ResultSet.findColumn(Unknown Source)
    at COM.ibm.db2.jdbc.app.DB2ResultSet.getString(Unknown Source)

As the get above error suggest, The column name specified in the Query or in resultSet is invalid.

resultSet.getString("COLUMN_NAME");

Please re-check all the column names against table name and make sure it is correct.

SQLSTATE=42903

 I was in need to pass a max value in where condition. My folly, what i did is :

Incorrect : Select * from table_name where field = MAX(field);



Got below mentioned error in DB2

  42903(-120)[IBM][CLI Driver][DB2/SUN64] SQL0120N  Invalid use of an aggregate function or OLAP function.  SQLSTATE=42903
 (0.47 secs)

Correct :

Correct way is Inline Query

Select * from table_name where files = (Select MAX(filed) from table_name);

DB2 : Get all column name of a particular Table

Query :


SELECT TABNAME,COLNAME from SYSCAT.COLUMNS where TABNAME='table_name'


You can use above mentioned query for getting all column name of a particular table in DB2.

DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704

If you get below mentioned error


Exception in thread "main" com.ibm.db2.jcc.b.eo: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=SCHEMA.TABLE_NAME, DRIVER=3.53.95
        at com.ibm.db2.jcc.b.bd.a(bd.java:676)
        at com.ibm.db2.jcc.b.bd.a(bd.java:60)
        at com.ibm.db2.jcc.b.bd.a(bd.java:127)
        at com.ibm.db2.jcc.b.gm.c(gm.java:2484)
        at com.ibm.db2.jcc.b.gm.d(gm.java:2461)
        at com.ibm.db2.jcc.b.gm.a(gm.java:1962)
        at com.ibm.db2.jcc.t4.db.g(db.java:138)
        at com.ibm.db2.jcc.t4.db.a(db.java:38)
        at com.ibm.db2.jcc.t4.t.a(t.java:32)
        at com.ibm.db2.jcc.t4.sb.h(sb.java:141)
        at com.ibm.db2.jcc.b.gm.bb(gm.java:1933)
        at com.ibm.db2.jcc.b.gm.a(gm.java:2799)
        at com.ibm.db2.jcc.b.gm.a(gm.java:604)
        at com.ibm.db2.jcc.b.gm.executeQuery(gm.java:588)

Possible reason could be Table do not present in that schema. Please make sure that you provide correct schema name and table name. Also check for typo in Table name or Schema name.

I got the above error when i mistakenly passed wrong table name for the schema. Hope it resolved your error.

DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;

If you get below mentioned error


Exception in thread "main" com.ibm.db2.jcc.b.eo: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;SCHEMA.TABLE_NAME;END-OF-STATEMENT, DRIVER=3.53.95
        at com.ibm.db2.jcc.b.bd.a(bd.java:676)
        at com.ibm.db2.jcc.b.bd.a(bd.java:60)
        at com.ibm.db2.jcc.b.bd.a(bd.java:127)
        at com.ibm.db2.jcc.b.gm.c(gm.java:2484)
        at com.ibm.db2.jcc.b.gm.d(gm.java:2461)
        at com.ibm.db2.jcc.b.gm.a(gm.java:1962)
        at com.ibm.db2.jcc.t4.db.g(db.java:138)
        at com.ibm.db2.jcc.t4.db.a(db.java:38)
        at com.ibm.db2.jcc.t4.t.a(t.java:32)
        at com.ibm.db2.jcc.t4.sb.h(sb.java:141)
        at com.ibm.db2.jcc.b.gm.bb(gm.java:1933)
        at com.ibm.db2.jcc.b.gm.a(gm.java:2799)
        at com.ibm.db2.jcc.b.gm.a(gm.java:604)
        at com.ibm.db2.jcc.b.gm.executeQuery(gm.java:588)




Possible reason could be SYNTAX error. Please make sure all syntax are correct upto DB2 standard. Verify the syntax of your query. Even don't add  semi-colon (;) at the end of the query.

In this case if you are using DB2 Universal Driver. 

Syntax :

Class.forName("com.ibm.db2.jcc.DB2Driver");
Connection con=DriverManager.getConnection("jdbc:db2://hostname:port/Schema","username","password");


Jar file : db2jcc.jar

To download Jar files : Click here

Simple DB2 JDBC program

To run this example you need to set db2java.jar in your classpath

Below is the tested code, you can directly run this program, Just provide correct login credentials and table name.

/* TestDB2.java */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestDB2
{
    public static void main(String[] args) throws Exception
    {
        Connection con;
        Statement stat;
        Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");//Step 1: Loading Drivers

        con=DriverManager.getConnection("jdbc:db2:YOURSCHEMA","username","password");//Step 2: Making Connection

        stat=con.createStatement();//Step 3: Creating JDBC Statement

        String query = "select COLUMN_NAME from USER_NAME.TABLE_NAME;";

        ResultSet rset=stat.executeQuery(query);//Step 4: Execute the Statement

        while(rset.next())//Step 5: Looping through the ResultSet
        {
            System.out.println(rset.getString(1));
        }
        stat.close();//step 6: Close the Connection and Statement
        con.close();
    }
}

Below are the known exceptions, Please follow proper steps to avoid below exceptions.


Exception in thread "main" COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] SQL3
0082N  Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID").  SQLSTATE=08001

        at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
        at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(UnknownSource)
        at COM.ibm.db2.jdbc.app.DB2Connection.connect(Unknown Source)
        at COM.ibm.db2.jdbc.app.DB2Connection.(Unknown Source)
        at COM.ibm.db2.jdbc.app.DB2Driver.connect(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at TestDB2.main(TestDB2.java:13)

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc/YOURSCHEMA
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at TestDB2.main(TestDB2.java:13)

Exception in thread "main" COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/
SUN64] SQL0204N  "USER_NAME.TABLE_NAME" is an undefined name.  SQLSTATE=42704
        at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
        at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
        at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(UnknownSource)
        at COM.ibm.db2.jdbc.app.DB2Statement.execute2(Unknown Source)
        at COM.ibm.db2.jdbc.app.DB2Statement.executeQuery(Unknown Source)
        at TestDB2.main(TestDB2.java:19)

DB2 : the syntax of the string representation of a datetime value is incorrect

DB2 query to get data between two DATE range ie to get data between start date and end date. Basically comparism of dates in DB2.

while writing a SQL query i was getting below mentioned error.


the syntax of the string representation of a datetime value is incorrect

Correct Syntax : 

select OPRID,DATETIME_STAMP,H_SS_ID from Q.PROJECT where date(DATETIME_STAMP) between '2011-08-04' and '2011-08-06' order by DATETIME_STAMP

here it will get data from date range between  2011-08-04 and 2011-08-06. Note date syntax (YYYY-MM-DD)

Also note the date function DATE(..) it will convert complete date to a YYYY-MM-DD format.

 If you want data of one particular day

select OPRID,DATETIME_STAMP,H_SS_ID from Q.PROJECT where date(DATETIME_STAMP) = '2011-08-04'

The basics of DATE TIME in DB2

To get the current date, time, and timestamp using SQL, reference the appropriate DB2 registers:

SELECT current date FROM sysibm.sysdummy1 
SELECT current time FROM sysibm.sysdummy1 
SELECT current timestamp FROM sysibm.sysdummy1 

The sysibm.sysdummy1 table is a special in-memory table that can be used to discover the value of DB2 registers. 

Given a date, time, or timestamp, you can extract (where applicable) the year, month, day, hour, minutes, seconds, and microseconds portions independently using the appropriate function:

YEAR (current timestamp) 
MONTH (current timestamp) 
DAY (current timestamp) 
HOUR (current timestamp) 
MINUTE (current timestamp) 
SECOND (current timestamp) 
MICROSECOND (current timestamp) 

Extracting the date and time independently from a timestamp is also very easy:

DATE (current timestamp) 
TIME (current timestamp) 

You can also perform date and time calculations using, for lack of a better term, English: 

current date + 1 YEAR 
current date + 3 YEARS + 2 MONTHS + 15 DAYS 
current time + 5 HOURS - 3 MINUTES + 10 SECONDS 
 

To calculate how many days there are between two dates, you can subtract dates as in the following:

days (current date) - days (date('1999-10-22'))