V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?

John Bucaria

V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?
Hello All!

One of our developers attempted to load an empty database with a Java application using SQL. It was unsuccessful and many tables were left in "check pending" status. Normally I associate "check pending" as a consequence of running the Load utility with "enforce no". I didn't realize it could also be a consequence of an "SQL Operation". Entries in SYSCOPY indicate ICTYPE = L , STYPE = M for the tables in question. What are the SQL statements that can result in check pending status? I know that ICTYPE = L refers to mass delete and truncate statements but when I test those on a parent table, they fail with RI violations, which is what I would have expected.

Thanks,
John

Philip Sevetson

V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?
(in response to John Bucaria)
John,

I've never heard of a SQL operation causing a CHKP status. I thought you could only get that by running DSNUTILB LOAD, overwriting the parent table or loading with ENFORCE=NO.

If you're really feeling adventurous, I suppose you could crash the subsystem during a large write to the subject table - I'd expect that to lead to a rollback on the next -START DB2, but if you did it with a cold start, you might get that. Even there, though, I'd expect RECP rather than CHKP.

--Phil Sevetson

From: Bucaria, John [mailto:[login to unmask email]
Sent: Tuesday, May 08, 2018 12:21 PM
To: [login to unmask email]
Subject: [DB2-L] - V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?

Hello All!

One of our developers attempted to load an empty database with a Java application using SQL. It was unsuccessful and many tables were left in "check pending" status. Normally I associate "check pending" as a consequence of running the Load utility with "enforce no". I didn't realize it could also be a consequence of an "SQL Operation". Entries in SYSCOPY indicate ICTYPE = L , STYPE = M for the tables in question. What are the SQL statements that can result in check pending status? I know that ICTYPE = L refers to mass delete and truncate statements but when I test those on a parent table, they fail with RI violations, which is what I would have expected.

Thanks,
John

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

John Bucaria

V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?
(in response to Philip Sevetson)
I'm with you on this, Phil. I would never expect SQL operations to result in check pending.

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, May 08, 2018 1:00 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?

John,

I've never heard of a SQL operation causing a CHKP status. I thought you could only get that by running DSNUTILB LOAD, overwriting the parent table or loading with ENFORCE=NO.

If you're really feeling adventurous, I suppose you could crash the subsystem during a large write to the subject table - I'd expect that to lead to a rollback on the next -START DB2, but if you did it with a cold start, you might get that. Even there, though, I'd expect RECP rather than CHKP.

--Phil Sevetson

From: Bucaria, John [mailto:[login to unmask email]
Sent: Tuesday, May 08, 2018 12:21 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?

Hello All!

One of our developers attempted to load an empty database with a Java application using SQL. It was unsuccessful and many tables were left in "check pending" status. Normally I associate "check pending" as a consequence of running the Load utility with "enforce no". I didn't realize it could also be a consequence of an "SQL Operation". Entries in SYSCOPY indicate ICTYPE = L , STYPE = M for the tables in question. What are the SQL statements that can result in check pending status? I know that ICTYPE = L refers to mass delete and truncate statements but when I test those on a parent table, they fail with RI violations, which is what I would have expected.

Thanks,
John

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

Colin Clayton

V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?
(in response to John Bucaria)
I'd be interested in seeing the Java 'load' your developer ran.

The STYPE=M indicates that MAXPARTITIONS was altered, was that connected to the 'load' operation ?



Sent from my Samsung Galaxy smartphone.


-------- Original message --------
From: "Bucaria, John" <[login to unmask email]>
Date: 08/05/2018 17:21 (GMT+00:00)
To: [login to unmask email]
Subject: [DB2-L] - V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?

Hello All!

One of our developers attempted to load an empty database with a Java application using SQL. It was unsuccessful and many tables were left in “check pending” status. Normally I associate “check pending” as a consequence of running the Load utility with “enforce no”. I didn’t realize it could also be a consequence of an “SQL Operation”. Entries in SYSCOPY indicate ICTYPE = L , STYPE = M for the tables in question. What are the SQL statements that can result in check pending status? I know that ICTYPE = L refers to mass delete and truncate statements but when I test those on a parent table, they fail with RI violations, which is what I would have expected.

Thanks,
John

-----End Original Message-----

John Bucaria

V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?
(in response to Colin Clayton)
I've been scouring the DB2xMSTR log and find that the developer started running his insert operation while I was still in the process of defining the database. This is making a little more sense now ...

From: Clayton, Colin [mailto:[login to unmask email]
Sent: Tuesday, May 08, 2018 1:30 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?

I'd be interested in seeing the Java 'load' your developer ran.

The STYPE=M indicates that MAXPARTITIONS was altered, was that connected to the 'load' operation ?



Sent from my Samsung Galaxy smartphone.


-------- Original message --------
From: "Bucaria, John" <[login to unmask email]<mailto:[login to unmask email]>>
Date: 08/05/2018 17:21 (GMT+00:00)
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?

Hello All!

One of our developers attempted to load an empty database with a Java application using SQL. It was unsuccessful and many tables were left in "check pending" status. Normally I associate "check pending" as a consequence of running the Load utility with "enforce no". I didn't realize it could also be a consequence of an "SQL Operation". Entries in SYSCOPY indicate ICTYPE = L , STYPE = M for the tables in question. What are the SQL statements that can result in check pending status? I know that ICTYPE = L refers to mass delete and truncate statements but when I test those on a parent table, they fail with RI violations, which is what I would have expected.

Thanks,
John

-----End Original Message-----

-----End Original Message-----

Kai Stroh

RE: V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?
(in response to John Bucaria)

It was probably an ALTER TABLE ADD CONSTRAINT where either a check constraint or a foreign key was defined, and the table or tables that were involved already contained data. In this case, I think Db2 does not enforce the constraint immediately because that could take a very long time if the table has millions of rows. Instead, the object goes into check pending. (This might be different if CURRENT RULES = 'STD' is used though).

John Bucaria

V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?
(in response to Kai Stroh)
Yes, Kai, I believe that you are right.

From: Kai Stroh [mailto:[login to unmask email]
Sent: Tuesday, May 08, 2018 3:17 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: V11 CM for z/OS - Can an "SQL Operation" Cause Check Pending?


It was probably an ALTER TABLE ADD CONSTRAINT where either a check constraint or a foreign key was defined, and the table or tables that were involved already contained data. In this case, I think Db2 does not enforce the constraint immediately because that could take a very long time if the table has millions of rows. Instead, the object goes into check pending. (This might be different if CURRENT RULES = 'STD' is used though).

-----End Original Message-----