Anybody hit SQL0407N bug ?

[Sergey Kashyrin]

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.jdb c.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

[prm]

Re: Anybody hit SQL0407N bug ?

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" <ska@resqnet.co mwrote in message
news:6sbOh.16$u o.23576@news.si sna.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.jdb c.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
>
>

[Sergey Kashyrin]

Re: Anybody hit SQL0407N bug ?


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.jdb c.DB2Exception: [IBM][CLI Driver][DB2] SQL0407N Assignment
of a NULL value to a NOT NULL column "" is not allowed. SQLSTATE=23502


"prm" <prmurphyx@swbe ll2.netwrote in message
news:iScOh.9544 $JZ3.5993@newss vr13.news.prodi gy.net...

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" <ska@resqnet.co mwrote in message
news:6sbOh.16$u o.23576@news.si sna.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.jd bc.DB2Exception : [IBM][CLI Driver][DB2] SQL0407N
>Assignment of a NULL value to a NOT NULL column "" is not allowed.
>SQLSTATE=235 02
>>
>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
>>
>>

>
>

[Jan M. Nelken]

Re: Anybody hit SQL0407N bug ?

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

[Sergey Kashyrin]

Re: Anybody hit SQL0407N bug ?

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.g etConnection(db Url, dbProp);
PreparedStateme nt ss = con.prepareStat ement("INSERT INTO SSS
(STARTTIME) VALUES (?)");
long p1 = 999;
ss.setLong(1, p1);
ss.executeUpdat e();
} catch(SQLExcept ion e) {
System.out.prin tln("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" <Unknown.User@I nvalid.Domainwr ote in message
news:56tj2hF2ag h5gU1@mid.indiv idual.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