By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,683 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

getting very cryptic error while inserting the records

P: n/a
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

Oct 8 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
"Rahul Babbar" <ra***********@gmail.comwrote in message
news:11*********************@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.
Oct 8 '07 #2

P: n/a
On Oct 8, 6:32 pm, "Mark A" <nob...@nowhere.comwrote:
"Rahul Babbar" <rahul.babb...@gmail.comwrote in message

news:11*********************@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

Oct 8 '07 #3

P: n/a
m
Rahul Babbar wrote:
On Oct 8, 6:32 pm, "Mark A" <nob...@nowhere.comwrote:
>"Rahul Babbar" <rahul.babb...@gmail.comwrote in message

news:11*********************@57g2000hsv.googlegro ups.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.... :D

Oct 8 '07 #4

P: n/a
m
Rahul Babbar wrote:
On Oct 8, 6:32 pm, "Mark A" <nob...@nowhere.comwrote:
>"Rahul Babbar" <rahul.babb...@gmail.comwrote in message

news:11*********************@57g2000hsv.googlegro ups.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.... :D

Oct 8 '07 #5

P: n/a
Rahul Babbar wrote:
On Oct 8, 6:32 pm, "Mark A" <nob...@nowhere.comwrote:
>"Rahul Babbar" <rahul.babb...@gmail.comwrote in message

news:11*********************@57g2000hsv.googlegro ups.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
Oct 8 '07 #6

P: n/a
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
Oct 9 '07 #7

P: n/a
On Oct 8, 8:31 pm, m <mdlspo...@yahoo.comwrote:
Rahul Babbar wrote:
On Oct 8, 6:32 pm, "Mark A" <nob...@nowhere.comwrote:
"Rahul Babbar" <rahul.babb...@gmail.comwrote in message
>news:11*********************@57g2000hsv.googlegro ups.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.... :D
Sorry,

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

Still it gives the same error.

Rahul

Oct 9 '07 #8

P: n/a
On Oct 9, 12:09 am, Knut Stolze <sto...@de.ibm.comwrote:
Rahul Babbar wrote:
On Oct 8, 6:32 pm, "Mark A" <nob...@nowhere.comwrote:
"Rahul Babbar" <rahul.babb...@gmail.comwrote in message
>news:11*********************@57g2000hsv.googlegro ups.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

Oct 9 '07 #9

P: n/a
On Oct 9, 8:25 am, Serge Rielau <srie...@ca.ibm.comwrote:
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

Oct 9 '07 #10

P: n/a
Rahul Babbar wrote:
I had mentioned in my first post that there are no triggers on the
table.
Yes, you did.
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.
You should have told us that.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Oct 9 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.