comp.sys.ibm.as400.misc - IBM AS/400 miscellaneous topics.
I have an sql table with an identity column: CREATE TABLE mqahistp ( HistId integer generated always as identity, MsgqName char(10), ... ) ; When I add new columns to the table and recreate it I start to get duplicate key errors on INSERTs to the table. The application that uses the table is still being written so to get around the error I just "delete from mqahistp" to clear the table. I am using TURNOVER when the tables are recreated, so I am guessing TURNOVER uses CPYF or something to recover the data of the replace file. Which then throws off the IDENTITY sequence value. I have not looked into this very much. What is the recommended way in SQL to recover data in a replaced file while retaining the values in the identity column? -Steve
CREATE TABLE "MYSCHEMA"."T1" ( "ID1" DECIMAL(8,0) NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1 INCREMENT BY +1 MINVALUE +1 MAXVALUE +99999999 NO CYCLE CACHE 20 NO ORDER ) ) DATA CAPTURE CHANGES IN "USERSPACE1" ; db2 insert into myschema.t1 values (default). Is the real value inserted in the table logged and is it possible to retrieve it with the log analyis API? Bernard Dhooghe
I have a table with identity column. How do I get the identity value after the insert from ASP application. I am using windows 2000 and DB2 V7.2 Fix pack 10
Hi, I have an identity column on a table that is filling in gaps when I complete inserts. For example if i had rows with these ID's 1 2 4 5 7 Then I completed 2 inserts, rows would be inserted with ID's of 3 and 6. My issue is that if i tried to do two inserts one after the other I was getting the following error: ------------------------------ Commands Entered ------------------------------ INSERT INTO EVENT(EVENTNAME, DESC, EVENTDATE, EVENTTIME, DAYID, YEARID, VENUEID, ATTENDANCEID, FINANCEID, DAYNIGHTID, HOMETEAMID, AWAYTEAMID, EVENTCATEGORYID, ISEVENTACTIVE, EVENTTYPECLASSID) VALUES ('Test Event', 'Test Event Description', '2007-05-26', '4:10:00', 2, 0, 0, 1, 21, 0, 14, 18, 1, 'N', 25)@ ------------------------------------------------------------------------------ INSERT INTO EVENT(EVENTNAME, DESC, EVENTDATE, EVENTTIME, DAYID, YEARID, VENUEID, ATTENDANCEID, FINANCEID, DAYNIGHTID, HOMETEAMID, AWAYTEAMID, EVENTCATEGORYID, ISEVENTACTIVE, EVENTTYPECLASSID) VALUES ('Test Event', 'Test Event Description', '2007-05-26', '4:10:00', 2, 0, 0, 1, 21, 0, 14, 18, 1, 'N', 25) DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.EVENT" from having duplicate rows for those columns. SQLSTATE=23505 SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.EVENT " from having duplicate rows for those columns. Explanation: The INSERT or UPDATE object table "<table-name>" is constrained by one or more UNIQUE indexes to have unique values in certain columns or groups of columns. Alternatively, a DELETE statement on a parent table caused the update of a foreign key in a dependent table "<table-name>" that is constrained by one or more UNIQUE indexes. Unique indexes might support primary keys or unique constraints defined on a table. The statement cannot be processed because completing the requested INSERT, UPDATE or DELETE statement would result in duplicate column values. Alternatively, if a view is the object of the INSERT or UPDATE statement, it is the table "<table-name>" on which the view is defined that is constrained. If "<index-id>" is an integer value, the index name can be obtained from SYSCAT.INDEXES by issuing the following query: SELECT INDNAME, INDSCHEMA FROM SYSCAT.INDEXES WHERE IID = <index-id> AND TABSCHEMA = 'schema' AND TABNAME = 'table' where schema represents the schema portion of "<table-name>" and table represents the table name portion of "<table-name>". The statement cannot be processed. The table remains unchanged. User Response: Examine the definition for the index identified by "<index-id>". For an UPDATE statement, ensure that the specified operation is not itself inconsistent with the uniqueness constraint. If this does not show the error, examine the object table content to determine the cause of the problem. For an INSERT statement, examine the object table content to determine which of the values in the specified value list violates the uniqueness constraint. Alternatively, if the INSERT statement contains a subquery, the object table contents addressed by that subquery must be matched against the object table contents to determine the cause of the problem. For a DELETE statement, examine the identified dependent table for unique constraints on foreign keys that are defined with the rule ON DELETE SET NULL. This table has a foreign key column included in the identified unique index that cannot be set to null since there is already a null in the column for that table. Federated system users: isolate the problem to the data source failing the request (refer to the problem determination guide to determine which data source is failing to process the SQL statement) and examine the index definitions and data for the conditions listed previously. sqlcode : -803 sqlstate : 23505 If I wait for a second then Insert again it seems to work o.k, also now that the gaps are all filled the inserts are working fine, any ideas why this was happening and how I can avoid this error in the future? There is only a PK on the identity column, no unique constraint's at all.