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 (AT) gmail (DOT) com> wrote

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... (AT) nowhere (DOT) com> wrote:
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:
I dont see a cloumn called lpn_id in the table....

Rahul Babbar wrote:
I dont see a column called lpn_id in the table....

Rahul Babbar wrote:

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?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Rahul Babbar wrote:
* 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... (AT) yahoo (DOT) com> wrote:
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... (AT) de (DOT) ibm.com> wrote:
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... (AT) ca (DOT) ibm.com> wrote:
Hi,

A PMR has been raised for it.

Rahul