Link to home
Start Free TrialLog in
Avatar of MAK999
MAK999

asked on

Error when inserting into DB2 database

After restoing latest production backup onto QA environment, we are getting below error message when inserting data into table

com.ibm.db2.jcc.c.SqlException: An error occurred during implicit system action type "7".  Information returned for the error includes SQLCODE "-117", SQLSTATE "42802" and message tokens "42802".

I found below explanation through internet but still dont know how to address the issue.

SQL0727N
An error occurred during implicit system action type action-type. Information returned for the error includes SQLCODE sqlcode, SQLSTATE sqlstate and message tokens token-list.
Explanation
Action type 7 --> implicit regeneration of a trigger
User response
Check the message associated with the SQLCODE of the SQL statement that failed. Follow the action suggested by that message.
For an invalid package, the REBIND command can be used to recreate the error or to explicitly validate the package once the cause of the error has been resolved.

Information returned for the error includes SQLCODE "-117", SQLSTATE "42802" and message tokens "42802".
      
SQLCode"-117" THE NUMBER OF INSERT VALUES IS NOT THE SAME AS THE NUMBER OF OBJECT COLUMNS       

Short Description: THE NUMBER OF VALUES ASSIGNED IS NOT THE SAME AS THE NUMBER OF SPECIFIED OR IMPLIED COLUMNS
The number of insert values in the value list of the INSERT statement is not the same as the number of object columns specified. Alternatively, the number of values on the right side of an assignment in a SET assignment statement or the SET clause of an UPDATE statement does not match the number of columns on the left side. System action: The statement cannot be executed. No data was inserted into the object table. Programmer response: Correct the statement to specify one and only one value for each of the specified object columns.
Avatar of Jagdish Devaku
Jagdish Devaku

hi,
SQL0117 SQLCODE -117 SQLSTATE 42802
Explanation: Statement inserts wrong number of values.
This means you are inserting data into more or less columns than required.
Have you checked the number of columns in both the databases for that particular table.
Please let me know if you find any.
Bye.
Avatar of Kent Olsen
Hi MAK999,

This error usually comes about because the SQL attempts to store a row using the implied column list and the target table has an identity column.  You'll need to specify the column list, leaving out the identity column and letting DB2 auto-assign the identity value while you set the other values.


Good Luck,
Kent

--
--  create a table with an identity column
--

create table sample_table
(
  id         integer not null generated always as identity,
  int_value  integer,
  char_value varchar (10)
);

--
-- You can not use the implied column list as there is no way to insert into the id column.
--

insert into sample_table values (1, 2, 'A');  -- doesn't work.  can't supply value for ID.

insert into sample_table values (2, 'A');  -- doesn't work.  Implied column list includes ID.

--
-- correct insert statement
--

insert into sample_table (int_value, char_value) values (2, 'A');

Open in new window

Avatar of MAK999

ASKER

The table doesnt have identity field, also the insert value match the number of column for the table.

I can understand the more insert value then table column, but what about the foremost error message com.ibm.db2.jcc.c.SqlException: An error occurred during implicit system action type "7".  

Is it something to with underlying trigger getting fired due to the insert?
Hi MAK,

The IBM documentation on this error is here:

  http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.msg.doc/doc/sql0727.htm

It isn't very helpful.  :(


The key would seem to be that you've restored the production database to a test database.  You probably have invalid objects due any of a number of things.  Ownership changes, alias or nickname differences, access to different schemas, access to different federated databases, etc.

Check the triggers associated with the table into which you're trying to insert.  There should be a clue there.


Kent
ASKER CERTIFIED SOLUTION
Avatar of MAK999
MAK999

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial