On Oct 9, 12:09 am, Knut Stolze <sto...@de.ibm.com> wrote:
> Rahul Babbar wrote:
> > On Oct 8, 6:32 pm, "Mark A" <nob...@nowhere.com> wrote:
> >> "Rahul Babbar" <rahul.babb...@gmail.com> wrote in message
>
> >>news:1191846366.479178.75820@57g2000hsv.googlegr oups.com...
>
> >> > Hi,
>
> >> > I am getting the following error, while executing the simple insert
> >> > script on a few tables.
>
> >> > INSERT INTO <table_name>(<col1>) VALUES (1);
>
> >> > DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: =;FUNCTION
> >> > Message: No authorized routine named "=" of type "FUNCTION" having
> >> > compatible arguments was found.
>
> >> > The datatype of the column is integer and the table has a large number
> >> > of columns, however i am trying to insert only one column(same error
> >> > comes with more columns).
>
> >> > Further there is no trigger on that table.
> >> > What could be the possible causes of the issue?.
>
> >> > However, there may be some functions that might be used to insert the
> >> > records, when the table is altered, the functions could become
> >> > inconsistent/invalid. However, i am not sure how an invalid function
> >> > which inserts the records in a table can be the cause of failure of
> >> > this insert.
>
> >> > I regret giving only this much information but this is the info that i
> >> > have.
>
> >> > What could be the possible reasons of this error?
>
> >> > Thanks
> >> > Rahul
>
> >> Please do a db2look and post the DDL for the table. You can do this with
> >> the Control Center.
>
> > Here's the DDL (got from Quest Central)
>
> > CREATE TABLE ABC_ADDRESS
> > (TC_ABC_ID VARCHAR(50) NOT NULL,
> > ABC_ID BIGINT NOT NULL,
> > O_LOCATION_ID BIGINT NOT NULL,
> > O_LOCATION_ALIAS_ID VARCHAR(16),
> > O_ADDRESS_LINE_1 VARCHAR(75),
> > O_ADDRESS_LINE_2 VARCHAR(75),
> > O_ADDRESS_LINE_3 VARCHAR(75),
> > O_CITY VARCHAR(40),
> > O_STATE_PROV VARCHAR(2),
> > O_COUNTY VARCHAR(40),
> > O_POSTAL_CODE VARCHAR(10),
> > O_COUNTRY_CODE VARCHAR(2),
> > C_LOCATION_ID BIGINT NOT NULL,
> > C_LOCATION_ALIAS_ID VARCHAR(16),
> > C_ADDRESS_LINE_1 VARCHAR(75),
> > C_ADDRESS_LINE_2 VARCHAR(75),
> > C_ADDRESS_LINE_3 VARCHAR(75),
> > C_CITY VARCHAR(40),
> > C_STATE_PROV VARCHAR(2),
> > C_COUNTY VARCHAR(40),
> > C_POSTAL_CODE VARCHAR(10),
> > C_COUNTRY_CODE VARCHAR(2),
> > D_LOCATION_ID BIGINT NOT NULL,
> > D_LOCATION_ALIAS_ID VARCHAR(16),
> > D_ADDRESS_LINE_1 VARCHAR(75),
> > D_ADDRESS_LINE_2 VARCHAR(75),
> > D_ADDRESS_LINE_3 VARCHAR(75),
> > D_CITY VARCHAR(40),
> > D_STATE_PROV VARCHAR(2),
> > D_COUNTY VARCHAR(40),
> > D_POSTAL_CODE VARCHAR(10),
> > D_COUNTRY_CODE VARCHAR(2),
> > HIBERNATE_VERSION BIGINT
> > )
> > DATA CAPTURE NONE
> > IN USERSPACE1;
>
> > The following insert gives the above error.
> > insert into abc_address(lpn_id) values(1);
>
> The table contains 5 columns defined with NOT NULL. Your INSERT statement
> specifies only 1 column (and one that doesn't even exist in the table).
> How are the values for the other not-nullable columns provided? Maybe you
> do have some triggers?
>
> > However, the problem goes away after the table is dropped and
> > recreated.
>
> --
> Knut Stolze
> DB2 z/OS Utilities Development
> IBM Germany

Hi,

I had mentioned in my first post that there are no triggers on the
table.
Further, if i don't give the not nullable columns then, it gives the
error, "cannot assign null value to a not null column"
Further, we have tried giving all the values for not nullable
columns(you can trust me for that) and it gives the same error.

It starts giving "cannot assign null value to a not null column" error
once the table is recreated, and everything works fine.

one more observation i found out, iniatially it was giving this error
for many tables, however, i was able to fix for a table or two after
doing a reorg on the table. However, Reorg did not work for any other
tables.

Rahul