MVSFORUMS.com

Catherine · Posted: Wed Jul 01, 2009 4:30 am Post subject: -438 sqlcode

Hi,

Im getting -438 SQLCODE while inserting a row in DB2 table. Can you please help me why is this error occuring. Imnt able to underatand the description provided for this sqlcode

Thanks
Catherine

dbzTHEdinosauer · Posted: Wed Jul 01, 2009 4:38 am Post subject:

I imagine that there is an INSERT TRIGGER associated with your table.

If there was no text provided in the DSNTIAR output, then the trigger creator was rather lazy.

Search you sysibm tables for the trigger associated with your table,
and determine, from the trigger ddl - sqlstate may help - why your INSERT was invalid.
_________________
Dick Brenholtz
American living in Varel, Germany

dbzTHEdinosauer · Posted: Wed Jul 01, 2009 5:11 am Post subject:

I have updated my previous post.
_________________
Dick Brenholtz
American living in Varel, Germany

Catherine · Posted: Wed Jul 01, 2009 7:51 am Post subject:

Hi dina,

Thanks very much for info

kolusu · Posted: Wed Jul 01, 2009 12:00 pm Post subject:

Catherine,

I looked up sqlcode of -438 and it is caused by using Raise_Error function and how did you get it on insert?

Descripiton of -438 error

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNMCJ12/2.3.178?

and description of Raise_Error function

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQJ12/3.2.85

Kolusu

dbzTHEdinosauer · Posted: Wed Jul 01, 2009 12:31 pm Post subject:

kolusu · Posted: Wed Jul 01, 2009 12:57 pm Post subject:

dbzTHEdinosauer,

Am I interpreting it wrong? I understood that even in triggers you should be using raise_error function or SIGNAL SQLSTATE statement to get the error of -438. what am I missing here?

Kolusu

dbzTHEdinosauer · Posted: Wed Jul 01, 2009 1:15 pm Post subject:

Triggers and Stored Procs, when they SIGNAL - setting the SQLSTATE - will automatically generate a -438 sqlcode (or +438 if first two of sqlstate are 00 or 01).

When someone attempts an insert on a table, that has an insert trigger, if the trigger wishes to 'create an error', the SIGNAL sqlstate is used - which automatically sends a -438. When a trigger can generate more than one error or to differentiate between different triggers, different sqlstates can be used (along with an info text) so that people will know what trigger they violated.

upon finish of the stored proc or trigger (return to the sql statement - INSERT in this case), the sql statement originally executed will return an SQLCODE of -438.

I hope that I have interpreted your question properly. The TS did not know why -438 was returned for the INSERT. I suggested interrogation of triggers on the table in question. I failed to mention that stored procs should also have been searched.
_________________
Dick Brenholtz
American living in Varel, Germany

kolusu · Posted: Wed Jul 01, 2009 1:30 pm Post subject:

dbzTHEdinosauer,

Thanks. My DB2 knowledge is getting rusty Sad

kolusu

dbzTHEdinosauer · Posted: Wed Jul 01, 2009 3:13 pm Post subject:

Tell you a little story:

while back I was consulting at a site where my contract was to make a modification whereby a value of zero would not be allowed as a column value, an error that was happening in 'certain' cases. A little research showed me that the idiots at this site had 19 modules that would make an insert of the row and 43 would do an update - to include this column. I showed management that by use of a trigger, we could not only enforce this validation rule, but isolate the code that did not provide proper validation. a 3 month budgeted project was finished in 3 weeks. I remember -438 because I was the point man for -438 sqlcodes comming out of testing.

my contract was eventually extended to cover 18 months. so, I remember -438's. I have forgotten a lot of other negative sqlcodes that I have had to research; but night and day for over a week I slept, ate and drank -438.
_________________
Dick Brenholtz
American living in Varel, Germany