Results 1 to 10 of 10
  1. #1
    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?

  2. #2
    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

  3. #3
    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?

  4. #4
    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

  5. #5
    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

  6. #6
    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/

  7. #7
    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.

  8. #8
    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

  9. #9
    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?

  10. #10
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •