SEO

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

"Rahul Babbar" <rahul.babbar1@gmail.com> wrote in message
news:1191846366.479178.75820@57g2000hsv.googlegrou ps.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.

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.googlegrou ps.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;

ALTER TABLE ABC_ADDRESS
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;

ALTER TABLE ABC_ADDRESS
ADD CONSTRAINT ABC_ADDRESS_PK PRIMARY KEY
(ABC_ID
);

ALTER TABLE ABC_ADDRESS
ADD CONSTRAINT ABC_ADDRESS_FK FOREIGN KEY
(ABC_ID
)
REFERENCES ABC
(ABC_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

The following insert gives the above error.
insert into abc_address(lpn_id) values(1);

However, the problem goes away after the table is dropped and
recreated.

Rahul

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.googlegrou ps.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;
>
> ALTER TABLE ABC_ADDRESS
> LOCKSIZE ROW
> APPEND OFF
> NOT VOLATILE;
>
> ALTER TABLE ABC_ADDRESS
> ADD CONSTRAINT ABC_ADDRESS_PK PRIMARY KEY
> (ABC_ID
> );
>
> ALTER TABLE ABC_ADDRESS
> ADD CONSTRAINT ABC_ADDRESS_FK FOREIGN KEY
> (ABC_ID
> )
> REFERENCES ABC
> (ABC_ID
> )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION
> ENFORCED
> ENABLE QUERY OPTIMIZATION;
>
> The following insert gives the above error.
> insert into abc_address(lpn_id) values(1);
>
> However, the problem goes away after the table is dropped and
> recreated.
>
> Rahul
>

I dont see a cloumn called lpn_id in the table....

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.googlegrou ps.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;
>
> ALTER TABLE ABC_ADDRESS
> LOCKSIZE ROW
> APPEND OFF
> NOT VOLATILE;
>
> ALTER TABLE ABC_ADDRESS
> ADD CONSTRAINT ABC_ADDRESS_PK PRIMARY KEY
> (ABC_ID
> );
>
> ALTER TABLE ABC_ADDRESS
> ADD CONSTRAINT ABC_ADDRESS_FK FOREIGN KEY
> (ABC_ID
> )
> REFERENCES ABC
> (ABC_ID
> )
> ON DELETE NO ACTION
> ON UPDATE NO ACTION
> ENFORCED
> ENABLE QUERY OPTIMIZATION;
>
> The following insert gives the above error.
> insert into abc_address(lpn_id) values(1);
>
> However, the problem goes away after the table is dropped and
> recreated.
>
> Rahul
>

I dont see a column called lpn_id in the table....

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.googlegrou ps.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

Rahul Babbar wrote:
> 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.
This smells like an issue with a nested SQL object:
* Expression generated column
* check constraint
* trigger

I do recall an issue with generated column when a LOB was involved.

Either way this should be a PMR and if it's novel an APAR should be opened.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On Oct 8, 8:31 pm, m <mdlspo...@yahoo.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;
>
> > ALTER TABLE ABC_ADDRESS
> > LOCKSIZE ROW
> > APPEND OFF
> > NOT VOLATILE;
>
> > ALTER TABLE ABC_ADDRESS
> > ADD CONSTRAINT ABC_ADDRESS_PK PRIMARY KEY
> > (ABC_ID
> > );
>
> > ALTER TABLE ABC_ADDRESS
> > ADD CONSTRAINT ABC_ADDRESS_FK FOREIGN KEY
> > (ABC_ID
> > )
> > REFERENCES ABC
> > (ABC_ID
> > )
> > ON DELETE NO ACTION
> > ON UPDATE NO ACTION
> > ENFORCED
> > ENABLE QUERY OPTIMIZATION;
>
> > The following insert gives the above error.
> > insert into abc_address(lpn_id) values(1);
>
> > However, the problem goes away after the table is dropped and
> > recreated.
>
> > Rahul
>
> I dont see a column called lpn_id in the table....

Sorry,

The insert query is
insert into abc_address(abc_id) values(1);

Still it gives the same error.

Rahul

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

On Oct 9, 8:25 am, Serge Rielau <srie...@ca.ibm.com> wrote:
> Rahul Babbar wrote:
> > 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.
>
> This smells like an issue with a nested SQL object:
> * Expression generated column
> * check constraint
> * trigger
>
> I do recall an issue with generated column when a LOB was involved.
>
> Either way this should be a PMR and if it's novel an APAR should be opened.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

Hi,

A PMR has been raised for it.

Rahul