By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,585 Members | 2,235 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,585 IT Pros & Developers. It's quick & easy.

Anybody hit SQL0407N bug ?

P: n/a
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
Mar 27 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
prm
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....
"Sergey Kashyrin" <sk*@resqnet.comwrote in message
news:6s***************@news.sisna.com...
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


Mar 27 '07 #2

P: n/a

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
"prm" <pr*******@swbell2.netwrote in message
news:iS*****************@newssvr13.news.prodigy.ne t...
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....
"Sergey Kashyrin" <sk*@resqnet.comwrote in message
news:6s***************@news.sisna.com...
>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



Mar 27 '07 #3

P: n/a
Sergey Kashyrin wrote:
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
Mar 27 '07 #4

P: n/a
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
"Jan M. Nelken" <Un**********@Invalid.Domainwrote in message
news:56*************@mid.individual.net...
Sergey Kashyrin wrote:
>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

Mar 28 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.