Anybody hit SQL0407N bug ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sergey Kashyrin - 27 Mar 2007 17:17 GMT

Hi,

I'm accessing DB2 v7 on z/OS from Java running on NT (Database server
= DB2 OS/390 7.1.1)
Tried both JDBC type 4 and type 2.

I'm getting regularly SQL0407N errors like this:

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0407N Assignment
of a NULL value to a NOT NULL column "" is not allowed. SQLSTATE=23502

So the column name is not present.
All values in insert statement are present and really not nulls.
There are no "NOT NULL" fields in this table, but primary key which value is
present in insert.
The request with exactly the same data might come thru later.

Anybody knows specific PTF for that ?

Regards,
Sergey

Reply to this Message

prm - 27 Mar 2007 18:53 GMT

Sergey,

You really need to check the object against which you are inserting the
check the data. Perhaps you have a referential integrity issue. Check your
table relationships.

Also, which version of the client driver are you using? v9, v8, v7, v6?

I don't think you'll find a PTF for this....

> Hi,
>
[quoted text clipped - 17 lines]
> Regards,
> Sergey

Reply to this Message

Sergey Kashyrin - 27 Mar 2007 20:48 GMT

The client (and JDBC driver) is DB2 Connect 8.1

The question is where to start to look ? DB2 traces (on 390...??? - ) ?

There are no any references. It's just a simple table like this:

CREATE TABLE STAT (
STARTTIME DECIMAL(18) NOT NULL
,TERMID CHAR(4) NOT NULL
,F1 CHAR(15), F2 CHAR(8), F3 CHAR(7), F4 CHAR(3)
,F5 VARCHAR(30), F6 VARCHAR(32), F7 CHAR(7), AMT DECIMAL(19,3), CURR
CHAR(3)
,DATECR DECIMAL(8), DATEEXP DECIMAL(8), DATELASTACT DECIMAL(8)
,COUNTRY CHAR(6), REGION CHAR(6), W DECIMAL(3), STATUS DECIMAL(3)
,BTYPE CHAR(2), STATUSMSG VARCHAR(128)
,INPUTDATA VARCHAR(32000)
,CONSTRAINT PK_STAT PRIMARY KEY(STARTTIME, TERMID)
)

The possible cause for this bug to appear might be the length of the string
for INPUTDATA (in failed request it was 9215 bytes, but I saw longer were
accepted) or some non-printable characters in this string...

prepared statement:
INSERT INTO STAT(STARTTIME, TERMID, F1, F2, F3, F4, F5, F6, F7, AMT, CURR,
DATECR, DATEEXP, DATELASTACT, STATUS, W, STATUSMSG, INPUTDATA, COUNTRY,
REGION, BTYPE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

(of course in jdbc index is from 1)
Parm 0 Long 1175023859859
Parm 1 String len 4 'F001'
Parm 2 String len 15 '9637 '
Parm 3 String len 7 'SSSSSSS'
Parm 4 String len 7 'M000003'
Parm 5 String len 3 '000'
Parm 6 String len 14 'xxxxxxxxxx-xxx'
Parm 7 String len 16 'xxxxxx xxx xxxxx'
Parm 8 String len 1 ' '
Parm 9 Double 800.0
Parm 10 String len 3 'USD'
Parm 11 String len 8 '20070327'
Parm 12 String len 8 '20080722'
Parm 13 String len 8 '20070327'
Parm 14 Integer 100
Parm 15 Integer 600
Parm 16 String len 100 '...skipped'
Parm 17 String len 9215 '...skipped'
Parm 18 String len 4 'US01'
Parm 19 String len 4 'NYR1'
Parm 20 String len 2 '00'
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0407N Assignment
of a NULL value to a NOT NULL column "" is not allowed. SQLSTATE=23502

> Sergey,
>
[quoted text clipped - 28 lines]
>> Regards,
>> Sergey

Reply to this Message

Jan M. Nelken - 27 Mar 2007 23:10 GMT

> Parm 11 String len 8 '20070327'
> Parm 12 String len 8 '20080722'
> Parm 13 String len 8 '20070327'

Can you try to match type of those three parameters with table definition?
String is not equal to Decimal ...

From CLP insert with your values works fine - can you post JDBC code
fragment which fails - inserting this one row for example.

Jan M. Nelken

Can

Reply to this Message

Sergey Kashyrin - 28 Mar 2007 16:04 GMT

Hi,

The issue has been identified.
It's for sure IBM issue, but it's possible it's been already fixed a while
ago
(our sysadmin didn't patch our DB2 OS/390 7.1.1 for a long time)
Once again, the client is running on Windows XP - both JDBC type 4 and type
2 (DB2 Connect v 8.1) work the same way.

That's a kind of a testcase
Table:

CREATE TABLE SSS (
STARTTIME DECIMAL(18) NOT NULL
)

fragment of java code:

try {
Connection con = DriverManager.getConnection(dbUrl, dbProp);
PreparedStatement ss = con.prepareStatement("INSERT INTO SSS
(STARTTIME) VALUES (?)");
long p1 = 999;
ss.setLong(1, p1);
ss.executeUpdate();
} catch(SQLException e) {
System.out.println("dberr: " + e);
}

For example the values from 1 to 799 were perfectly inserted. The values
from 800 to 999 failed. 1000 is okay ... strange things...
Didn't check for other values :-)

Regards,
Sergey

>> Parm 11 String len 8 '20070327'
>> Parm 12 String len 8 '20080722'
[quoted text clipped - 9 lines]
>
> Can

Reply to this Message