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.
MAK999Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jagdish DevakuSr DB ArchitectCommented:
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.
0
Kent OlsenDBACommented:
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

0
MAK999Author Commented:
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?
0
Kent OlsenDBACommented:
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
0
MAK999Author Commented:
Actually I found the answer, it was the underlying trigger for the table insert caused the issue. A insert trigger gets fired and tried to insert record onto another table but the table structure has changed so the trigger did not provide value for all the columns, hence the exception.

I have been suspecting about trigger from the beginnig because of the error messag.

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.

Thanks for everyone's help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.