Space announcements

   

This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

GENERATE ALWAYS with ROWID / AS IDENTITY columns

The following points relate to a column that is defined with a data type of ROWID or as an identity column (with the AS IDENTITY syntax):
  • When you define a table with either a ROWID or an identity column and use the syntax GENERATE ALWAYS for that column, Db2 creates and maintains a unique value in the column. Db2 does not permit an SQL statement to insert or update a value in this type of column.

  • When Db2 logs an INSERT or DELETE action on the table, the log record contains the current value stored in the ROWID or identity column. If Log Master generates an SQL INSERT statement based on the log record, it includes the current value.

Because the generated SQL statement attempts to insert a value in a column defined as GENERATE ALWAYS, it fails with a negative SQL code. To resolve this situation, perform one of the following actions based on your environment:

  • Exclude the ROWID or identity column from the generated SQL statements. For more information about excluding columns from generated output, see LOGSCAN column include/exclude definition. When you execute the generated SQL statement, the INSERT is successful, but Db2 creates a unique value in the ROWID or identity column that is different than the value that was originally associated with the table row.

  • Change the definition of the column to use the GENERATE BY DEFAULT syntax instead of GENERATE ALWAYS. When you execute the generated SQL statement, the INSERT is successful (unless the value happens to be the same as a value that already exists in the table).



Was this page helpful? Yes No Submitting... Thank you

Comments