If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: =;FUNCTION During Insert

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 10
Question DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: =;FUNCTION During Insert

I am having a strange error in DB2 V 9.1(On AIX Version 5.3). The DB2 has fix pack 3 also installed on it.

When I am doing an Insert into a table, I am encountering the below error.

INSERT INTO tableabc(abc, def, ghi, jkl) VALUES (1, 1, 1, 1);

The primary key, data types all are taken care of during the insert.

The error received is as below.


DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: =;FUNCTION
Message: No authorized routine named "=" of type "FUNCTION" having compatible arguments was found.

A select on the same table works fine.

Also, when I am trying to create a procedure that is dependent on this table, I am running into the same exact error.

Temporary Solution that we found is to Drop and Create the table & indexes again, and the Insert, Create Procedure works fine without a single change in the SQL.

We need a solution which does not involve a drop of table.

We already raised a PMR with IBM, but in the mean time.. Anybody there who can help?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,721
As we told you in the other newsgroup already: you have 5 NOT NULL columns (w/o defaults) in the table and assign only values for 4 of them. Unless you have some triggers in the picture that assign a value to the remaining NOT NULL columns, the above statement cannot work.

In short, we would need the complete DDL of all objects associated with the table and the INSERT statement.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 10
All the Not Null columns in the table are provided values.

Basically, the exact Insert script runs without any errors once the table is Dropped and recreated with the same Structure(Not Null, Null, Default values, PKs & FKs) and Indexes.

And more over, should it not error out with SQLCODE: -407, SQLSTATE: 23502 if a Value is not supplied to a Not Null field?
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2003
Posts: 113
"DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: =;FUNCTION
Message: No authorized routine named "=" of type "FUNCTION" having
compatible arguments was found. "

this msg is detected because (for some reason) DB2 thought that you like to execute a function named '='. It definitely doesn't come from the insert-stmt.
Some we need to look somewhere else:
1) trigger
2) UDT
3) check-contraint


by all means, the '=' should come from somewhere. which is the starting point
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,721
Quote:
Originally Posted by jraveendranath
All the Not Null columns in the table are provided values.

Basically, the exact Insert script runs without any errors once the table is Dropped and recreated with the same Structure(Not Null, Null, Default values, PKs & FKs) and Indexes.

And more over, should it not error out with SQLCODE: -407, SQLSTATE: 23502 if a Value is not supplied to a Not Null field?
The main issue is that you don't provide us with a full description of the scenario - most preferably with a script to reproduce the error. Your SQL statements simply don't line up...
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Location: Belgium
Posts: 1,126
Quote:
Originally Posted by jraveendranath
DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: =;FUNCTION
Message: No authorized routine named "=" of type "FUNCTION" having compatible arguments was found.
What are the datatypes for the columns abc, def, ghi, jkl ?
I would expect that error when one of those is a distinct type, without an assignment function from an integer.
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 10
The table structure is as below.

The insert has values only for the Not Null coulmns. None of the other fields are populated.

CREATE TABLE DIS_EVENT (
DIS_EVENT_ID BIGINT NOT NULL,
DIS_ID BIGINT NOT NULL,
EVENT_SEQ SMALLINT,
FIELD_NAME VARCHAR(40),
OLD_VALUE VARCHAR(100),
NEW_VALUE VARCHAR(100),
CREATED_SOURCE_TYPE SMALLINT NOT NULL,
CREATED_SOURCE VARCHAR(25),
CREATED_DTTM TIMESTAMP NOT NULL,
DIS_DETAIL_ID BIGINT,
PRIMARY KEY(DIS_EVENT_ID)
)
GO
ALTER TABLE DIS_EVENT
ADD CONSTRAINT DIS_EVENT_DIS_FK
FOREIGN KEY(DIS_ID)
REFERENCES DISP(DIS_ID)
GO

Hi nidm,

First thing we did was to check for any Triggers, but none on this table and there are no check constraints as well. There was a stored procedure depending on this table, but as mentioned, there are no triggers to initiate this stored procudure.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,721
Again, a full scenario (including CREATE TABLE and INSERT statement) would have been helpful.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: May 2003
Posts: 113
hi,

I couldn't tell how the -440 comes from. but this looks troubling
"The insert has values only for the Not Null coulmns. None of the other fields are populated. "

You probably still need to provide the 'NULL' for those NULLable columns. I will write an INSERT stmt like:
INSERT INTO
DIS_EVENT (
DIS_EVENT_ID BIGINT NOT NULL,
DIS_ID BIGINT NOT NULL,
EVENT_SEQ SMALLINT,
FIELD_NAME VARCHAR(40),
OLD_VALUE VARCHAR(100),
NEW_VALUE VARCHAR(100),
CREATED_SOURCE_TYPE SMALLINT NOT NULL,
CREATED_SOURCE VARCHAR(25),
CREATED_DTTM TIMESTAMP NOT NULL,
DIS_DETAIL_ID BIGINT
)
VALUES( 1, 1, NULL, NULL, NULL, NULL, 1, NULL, current timestamp, NULL);

Bascially, you still need to put 'NULL's in the slot instead of omitting them.

BTW, I assume that you are using DB2/LUW?
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 10
DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: =;FUNCTION During Insert

Hi all,

Thanks for your responses.

The issue was resolved after we raised a PMR with IBM support. The issue was with referential integrity corrruption. (APAR IZ00678). -440 is also sometimes encountered as part of this issue it seems. After we dropped and recreated our FKs, the issue went away.

The catch here is even if you upgrade to the latest fixpak, the issue does not get resolved, because the referentail integrity constarints are already corrupted. So dropping and recreating is the only option. I guess IBM also has a patch just in case to clean up this mess.

ps. The same sqlcode was encountered even when we were trying to delete from the table. Both the issues got resolved now.

Thanks
Ravee
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On