To resolve this issue I dropped the primary key and added it back which I'm guessing is the standard way to 'rebuild' a primary key constraint. At first the constraint could not be added due to a duplicate key value on two rows. Once I cleaned up the data, I was able to add the constraint. I'm curios as to how a duplicate key was allowed while the constraint was in place. Does a duplicate key slip past the primary key occasionally?

------------------------------
Patrick Conner
------------------------------

Dear Patrick

>>>> Does a duplicate key slip past the primary key occasionally? <<<<

I wonder if this is a known bug? Or a quirk in its operation when encountering some rare situation?    The latter point reminds me of the early days when I began my career with AS/400 in early 1990s and I found in a few customer cases data corruption errors when reading data from physical files with Query/400 which made me wonder why my customers wrote corrupted data or was it physical disk issue?    I encountered an enlightening moment a few years later reading a published article by a member of DB2 expert team from IBM Rochester that DDS-created PF DOES NOT do data validation at write time but does it at read time while SQL-created table does data validation at write time and this difference is a cause of possible write of corrupt data into DDS-created PF for some situations. 

------------------------------
Satid S
------------------------------


Original Message:
Sent: Fri October 11, 2024 05:04 PM
From: Patrick Conner
Subject: How do I address SQL7008 error with reason 9

To resolve this issue I dropped the primary key and added it back which I'm guessing is the standard way to 'rebuild' a primary key constraint. At first the constraint could not be added due to a duplicate key value on two rows. Once I cleaned up the data, I was able to add the constraint. I'm curios as to how a duplicate key was allowed while the constraint was in place. Does a duplicate key slip past the primary key occasionally?

------------------------------
Patrick Conner

Interesting... I thought validation time (before/after) was determined by the language used. I understood that RPG validates data when retrieving records and SQL validates when outputting rows. But I digress. 

The situation still makes no sense because I know DDS unique keys are honored as well as DDL primary keys. Focusing on the primary key which was the constraint that was violated, I assume I have to consider the duplicate a glitch and just move on.

Dear Patrick

>>>> Does a duplicate key slip past the primary key occasionally? <<<<

I wonder if this is a known bug? Or a quirk in its operation when encountering some rare situation?    The latter point reminds me of the early days when I began my career with AS/400 in early 1990s and I found in a few customer cases data corruption errors when reading data from physical files with Query/400 which made me wonder why my customers wrote corrupted data or was it physical disk issue?    I encountered an enlightening moment a few years later reading a published article by a member of DB2 expert team from IBM Rochester that DDS-created PF DOES NOT do data validation at write time but does it at read time while SQL-created table does data validation at write time and this difference is a cause of possible write of corrupt data into DDS-created PF for some situations. 

------------------------------
Satid S


Original Message:
Sent: Fri October 11, 2024 05:04 PM
From: Patrick Conner
Subject: How do I address SQL7008 error with reason 9

To resolve this issue I dropped the primary key and added it back which I'm guessing is the standard way to 'rebuild' a primary key constraint. At first the constraint could not be added due to a duplicate key value on two rows. Once I cleaned up the data, I was able to add the constraint. I'm curios as to how a duplicate key was allowed while the constraint was in place. Does a duplicate key slip past the primary key occasionally?

------------------------------
Patrick Conner

Interesting... I thought validation time (before/after) was determined by the language used. I understood that RPG validates data when retrieving records and SQL validates when outputting rows. But I digress. 

The situation still makes no sense because I know DDS unique keys are honored as well as DDL primary keys. Focusing on the primary key which was the constraint that was violated, I assume I have to consider the duplicate a glitch and just move on.

------------------------------
Patrick Conner
------------------------------


Original Message:
Sent: Mon October 14, 2024 09:46 PM
From: Satid S
Subject: How do I address SQL7008 error with reason 9

Dear Patrick

>>>> Does a duplicate key slip past the primary key occasionally? <<<<

I wonder if this is a known bug? Or a quirk in its operation when encountering some rare situation?    The latter point reminds me of the early days when I began my career with AS/400 in early 1990s and I found in a few customer cases data corruption errors when reading data from physical files with Query/400 which made me wonder why my customers wrote corrupted data or was it physical disk issue?    I encountered an enlightening moment a few years later reading a published article by a member of DB2 expert team from IBM Rochester that DDS-created PF DOES NOT do data validation at write time but does it at read time while SQL-created table does data validation at write time and this difference is a cause of possible write of corrupt data into DDS-created PF for some situations. 

------------------------------
Satid S


Original Message:
Sent: Fri October 11, 2024 05:04 PM
From: Patrick Conner
Subject: How do I address SQL7008 error with reason 9

To resolve this issue I dropped the primary key and added it back which I'm guessing is the standard way to 'rebuild' a primary key constraint. At first the constraint could not be added due to a duplicate key value on two rows. Once I cleaned up the data, I was able to add the constraint. I'm curios as to how a duplicate key was allowed while the constraint was in place. Does a duplicate key slip past the primary key occasionally?

------------------------------
Patrick Conner

Dear Patrick

I attach herewith the article I mentioned in case you are curious. IIRC, the article was published in NEWS3X/400 magazine (or was it when the magazine changed its name to iSeries Magazine? I do not remember exactly) if you recognize the name. 

------------------------------
Satid S
------------------------------


Original Message:
Sent: Tue October 15, 2024 08:33 AM
From: Patrick Conner
Subject: How do I address SQL7008 error with reason 9

Interesting... I thought validation time (before/after) was determined by the language used. I understood that RPG validates data when retrieving records and SQL validates when outputting rows. But I digress. 

The situation still makes no sense because I know DDS unique keys are honored as well as DDL primary keys. Focusing on the primary key which was the constraint that was violated, I assume I have to consider the duplicate a glitch and just move on.

------------------------------
Patrick Conner


Original Message:
Sent: Mon October 14, 2024 09:46 PM
From: Satid S
Subject: How do I address SQL7008 error with reason 9

Dear Patrick

>>>> Does a duplicate key slip past the primary key occasionally? <<<<

I wonder if this is a known bug? Or a quirk in its operation when encountering some rare situation?    The latter point reminds me of the early days when I began my career with AS/400 in early 1990s and I found in a few customer cases data corruption errors when reading data from physical files with Query/400 which made me wonder why my customers wrote corrupted data or was it physical disk issue?    I encountered an enlightening moment a few years later reading a published article by a member of DB2 expert team from IBM Rochester that DDS-created PF DOES NOT do data validation at write time but does it at read time while SQL-created table does data validation at write time and this difference is a cause of possible write of corrupt data into DDS-created PF for some situations. 

------------------------------
Satid S


Original Message:
Sent: Fri October 11, 2024 05:04 PM
From: Patrick Conner
Subject: How do I address SQL7008 error with reason 9

To resolve this issue I dropped the primary key and added it back which I'm guessing is the standard way to 'rebuild' a primary key constraint. At first the constraint could not be added due to a duplicate key value on two rows. Once I cleaned up the data, I was able to add the constraint. I'm curios as to how a duplicate key was allowed while the constraint was in place. Does a duplicate key slip past the primary key occasionally?

------------------------------
Patrick Conner

Interesting... I thought validation time (before/after) was determined by the language used. I understood that RPG validates data when retrieving records and SQL validates when outputting rows. But I digress. 

The situation still makes no sense because I know DDS unique keys are honored as well as DDL primary keys. Focusing on the primary key which was the constraint that was violated, I assume I have to consider the duplicate a glitch and just move on.

Dear Patrick

>>>> Does a duplicate key slip past the primary key occasionally? <<<<

I wonder if this is a known bug? Or a quirk in its operation when encountering some rare situation?    The latter point reminds me of the early days when I began my career with AS/400 in early 1990s and I found in a few customer cases data corruption errors when reading data from physical files with Query/400 which made me wonder why my customers wrote corrupted data or was it physical disk issue?    I encountered an enlightening moment a few years later reading a published article by a member of DB2 expert team from IBM Rochester that DDS-created PF DOES NOT do data validation at write time but does it at read time while SQL-created table does data validation at write time and this difference is a cause of possible write of corrupt data into DDS-created PF for some situations. 

------------------------------
Satid S


Original Message:
Sent: Fri October 11, 2024 05:04 PM
From: Patrick Conner
Subject: How do I address SQL7008 error with reason 9

To resolve this issue I dropped the primary key and added it back which I'm guessing is the standard way to 'rebuild' a primary key constraint. At first the constraint could not be added due to a duplicate key value on two rows. Once I cleaned up the data, I was able to add the constraint. I'm curios as to how a duplicate key was allowed while the constraint was in place. Does a duplicate key slip past the primary key occasionally?

------------------------------
Patrick Conner

I discovered that the invalidation reason does get logged and can be retrieved:

select system_index_name, system_table_name, system_table_partition, index_type, accpth_type, create_timestamp, last_build_timestamp, last_invalidation_timestamp, last_invalidation_reason_code, last_invalidation_reason_type, index_held, maintenance, recovery, index_valid, unique, partitioned

  from qsys2.SYSPARTITIONINDEXES x

  where system_table_schema = 'MYSCHEMA'  and system_table_name ='MYTABLE'

  order by system_table_schema, system_table_name, system_table_partition, system_index_name

Interesting... I thought validation time (before/after) was determined by the language used. I understood that RPG validates data when retrieving records and SQL validates when outputting rows. But I digress. 

The situation still makes no sense because I know DDS unique keys are honored as well as DDL primary keys. Focusing on the primary key which was the constraint that was violated, I assume I have to consider the duplicate a glitch and just move on.

Dear Patrick

>>>> Does a duplicate key slip past the primary key occasionally? <<<<

I wonder if this is a known bug? Or a quirk in its operation when encountering some rare situation?    The latter point reminds me of the early days when I began my career with AS/400 in early 1990s and I found in a few customer cases data corruption errors when reading data from physical files with Query/400 which made me wonder why my customers wrote corrupted data or was it physical disk issue?    I encountered an enlightening moment a few years later reading a published article by a member of DB2 expert team from IBM Rochester that DDS-created PF DOES NOT do data validation at write time but does it at read time while SQL-created table does data validation at write time and this difference is a cause of possible write of corrupt data into DDS-created PF for some situations. 

------------------------------
Satid S


Original Message:
Sent: Fri October 11, 2024 05:04 PM
From: Patrick Conner
Subject: How do I address SQL7008 error with reason 9

To resolve this issue I dropped the primary key and added it back which I'm guessing is the standard way to 'rebuild' a primary key constraint. At first the constraint could not be added due to a duplicate key value on two rows. Once I cleaned up the data, I was able to add the constraint. I'm curios as to how a duplicate key was allowed while the constraint was in place. Does a duplicate key slip past the primary key occasionally?

------------------------------
Patrick Conner

To resolve this issue I dropped the primary key and added it back which I'm guessing is the standard way to 'rebuild' a primary key constraint. At first the constraint could not be added due to a duplicate key value on two rows. Once I cleaned up the data, I was able to add the constraint. I'm curios as to how a duplicate key was allowed while the constraint was in place. Does a duplicate key slip past the primary key occasionally?

------------------------------
Patrick Conner
------------------------------

To resolve this issue I dropped the primary key and added it back which I'm guessing is the standard way to 'rebuild' a primary key constraint. At first the constraint could not be added due to a duplicate key value on two rows. Once I cleaned up the data, I was able to add the constraint. I'm curios as to how a duplicate key was allowed while the constraint was in place. Does a duplicate key slip past the primary key occasionally?

------------------------------
Patrick Conner
------------------------------

To resolve this issue I dropped the primary key and added it back which I'm guessing is the standard way to 'rebuild' a primary key constraint. At first the constraint could not be added due to a duplicate key value on two rows. Once I cleaned up the data, I was able to add the constraint. I'm curios as to how a duplicate key was allowed while the constraint was in place. Does a duplicate key slip past the primary key occasionally?

Patrick, 

To add to the wealth of information that the others have posted (and out of personal experience) something that might also explain the anomaly. 

It is possible to have constraints defined but that are not active, some might disable them for some reason unknown. 

It's likely that a constraint was enabled at some stage but with no data checking (possibly also for a slight minute or two when a process wrote the records). 

We found out the hard way with a role swap that ended up having inconsistencies due to the constraint differences, developers enabled to constraints but turned off the actual data checks from that. this somewhat I am not sure why the database engineers enable that functionality when it comes to Data Integrity. 

Our solution was that we had to go through each constraint , disable -> reenable WITH data check in ACS and fix data as it errors out. 

Perhaps something to look at as well in your environment. 

------------------------------
Marius le Roux
Owner
MLR Consulting
------------------------------


Original Message:
Sent: Fri October 11, 2024 05:04 PM
From: Patrick Conner
Subject: How do I address SQL7008 error with reason 9

To resolve this issue I dropped the primary key and added it back which I'm guessing is the standard way to 'rebuild' a primary key constraint. At first the constraint could not be added due to a duplicate key value on two rows. Once I cleaned up the data, I was able to add the constraint. I'm curios as to how a duplicate key was allowed while the constraint was in place. Does a duplicate key slip past the primary key occasionally?

------------------------------
Patrick Conner