DB2 SQL on z: Can't make the arithmetic work

Philip Sevetson

DB2 SQL on z: Can't make the arithmetic work
I'm trying to eliminate old data from a suite of tables. Table B is a parent to all, and one of the children is Table A. I delete from B, then delete orphans from A.

I think I'm doing something wrong here, and I don't understand what it is. Can I get someone to look at this and tell me why these two numbers don't add up?

**********

1) This is the first check. I have a parent B and a child A, not governed by formal RI. An orphan can exist, and apparently many do (coding error).

---------+---------+---------+---------+---------+---------+
SELECT COUNT(*) "OLD ORPHANS"
FROM A
WHERE A.DOC_NUM NOT IN
(SELECT B.DOC_NUM
FROM B
);
---------+---------+---------+---------+---------+---------+
OLD ORPHANS
---------+---------+---------+---------+---------+---------+
22410
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100


2) Now, I'm going to be eventually deleting a lot of parents according to a rule. This should increase the number of orphans:

---------+---------+---------+---------+---------+---------+
SELECT COUNT(*) "MAYBE NEW ORPHANS"
FROM A
WHERE A.DOC_NUM NOT IN
(SELECT B.DOC_NUM
FROM B
WHERE DATE(B.CREATED_TIMESTAMP) < CURRENT DATE - 18 MONTHS
AND DATE(B.EFF_DATE) < CURRENT DATE - 18 MONTHS
);
---------+---------+---------+---------+---------+---------+
MAYBE NEW ORPHANS
---------+---------+---------+---------+---------+---------+
448276
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100


3) So far, so good. Remove parents from the "in" list, and the result is to increase the number of orphans. Now, actually delete those parents:

---------+---------+---------+---------+---------+---------+
DELETE FROM B
WHERE DATE(B.CREATED_TIMESTAMP) < CURRENT DATE - 18 MONTHS
AND DATE(B.EFF_DATE) < CURRENT DATE - 18 MONTHS;
---------+---------+---------+---------+---------+---------+
DSNE615I NUMBER OF ROWS AFFECTED IS 757032
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0


4) So we've deleted a lot of rows from the parent table. Since we used the same predicates to delete in (3), as to select in (2), the number of orphans should increase to the same "new" level; 4 should equal 2, right??

---------+---------+---------+---------+---------+-------
SELECT COUNT(*) "CONFIRMED NEW ORPHANS"
FROM A
WHERE A.DOC_NUM NOT IN
(SELECT B.DOC_NUM
FROM B
);
---------+---------+---------+---------+---------+-------
CONFIRMED NEW ORPHANS
---------+---------+---------+---------+---------+-------
80021
DSNE610I NUMBER OF ROWS DISPLAYED IS 1


CONCLUSION:
I don't understand what happened here. Does anyone have a way of describing this where (4) does not equal (2), and this number makes sense? Or am I actually looking at a bad result from the SQL engine?? Help!!


--Phil Sevetson
**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.**

Lockwood Lyon

DB2 SQL on z: Can't make the arithmetic work
(in response to Philip Sevetson)
Phil,

A quick guess is that in #2 you are counting A's NOT IN a subset of
B. That isn't "new orphans", is it?

"New Orphans" would be A's that ARE IN the subset of B. Yes? No?

- Lock Lyon

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, April 16, 2018 4:15 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 SQL on z: Can't make the arithmetic work

I'm trying to eliminate old data from a suite of tables. Table B is
a parent to all, and one of the children is Table A. I delete from
B, then delete orphans from A.

I think I'm doing something wrong here, and I don't understand what
it is. Can I get someone to look at this and tell me why these two
numbers don't add up?

**********

1) This is the first check. I have a parent B and a child A, not
governed by formal RI. An orphan can exist, and apparently many do
(coding error).

---------+---------+---------+---------+---------+---------+
SELECT COUNT(*) "OLD ORPHANS"
FROM A
WHERE A.DOC_NUM NOT IN
(SELECT B.DOC_NUM
FROM B
);
---------+---------+---------+---------+---------+---------+
OLD ORPHANS
---------+---------+---------+---------+---------+---------+
22410
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100


2) Now, I'm going to be eventually deleting a lot of parents
according to a rule. This should increase the number of orphans:

---------+---------+---------+---------+---------+---------+
SELECT COUNT(*) "MAYBE NEW ORPHANS"
FROM A
WHERE A.DOC_NUM NOT IN
(SELECT B.DOC_NUM
FROM B
WHERE DATE(B.CREATED_TIMESTAMP) < CURRENT DATE - 18 MONTHS
AND DATE(B.EFF_DATE) < CURRENT DATE - 18 MONTHS
);
---------+---------+---------+---------+---------+---------+
MAYBE NEW ORPHANS
---------+---------+---------+---------+---------+---------+
448276
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100


3) So far, so good. Remove parents from the "in" list, and the
result is to increase the number of orphans. Now, actually delete
those parents:

---------+---------+---------+---------+---------+---------+
DELETE FROM B
WHERE DATE(B.CREATED_TIMESTAMP) < CURRENT DATE - 18 MONTHS
AND DATE(B.EFF_DATE) < CURRENT DATE - 18 MONTHS;
---------+---------+---------+---------+---------+---------+
DSNE615I NUMBER OF ROWS AFFECTED IS 757032
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0


4) So we've deleted a lot of rows from the parent table. Since we
used the same predicates to delete in (3), as to select in (2), the
number of orphans should increase to the same "new" level; 4 should
equal 2, right??

---------+---------+---------+---------+---------+-------
SELECT COUNT(*) "CONFIRMED NEW ORPHANS"
FROM A
WHERE A.DOC_NUM NOT IN
(SELECT B.DOC_NUM
FROM B
);
---------+---------+---------+---------+---------+-------
CONFIRMED NEW ORPHANS
---------+---------+---------+---------+---------+-------
80021
DSNE610I NUMBER OF ROWS DISPLAYED IS 1


CONCLUSION:
I don't understand what happened here. Does anyone have a way of
describing this where (4) does not equal (2), and this number makes
sense? Or am I actually looking at a bad result from the SQL
engine?? Help!!


--Phil Sevetson
**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-----

Philip Sevetson

DB2 SQL on z: Can't make the arithmetic work
(in response to Lockwood Lyon)
I don't _think_ so, Lock.

If a row A1 is a "maybe new orphan" under a query which excludes row B1 (Query 2), and then I delete B1 (Query 3), then A1 should be a "confirmed new orphan" in the final query (Query 4). But I think that's not what's happening here.

Am I reasoning this correctly? Anyone? It's leading me to doubt my reasoning. :(

Note: Query 1 is almost irrelevant to the problem; Rows in Query 1 should show up in both result sets, 2 and 4.

--Phil

From: Lock Lyon [mailto:[login to unmask email]
Sent: Monday, April 16, 2018 4:22 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 SQL on z: Can't make the arithmetic work

Phil,

A quick guess is that in #2 you are counting A's NOT IN a subset of B. That isn't "new orphans", is it?

"New Orphans" would be A's that ARE IN the subset of B. Yes? No?

- Lock Lyon

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, April 16, 2018 4:15 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - DB2 SQL on z: Can't make the arithmetic work

I'm trying to eliminate old data from a suite of tables. Table B is a parent to all, and one of the children is Table A. I delete from B, then delete orphans from A.

I think I'm doing something wrong here, and I don't understand what it is. Can I get someone to look at this and tell me why these two numbers don't add up?

**********

1) This is the first check. I have a parent B and a child A, not governed by formal RI. An orphan can exist, and apparently many do (coding error).

---------+---------+---------+---------+---------+---------+
SELECT COUNT(*) "OLD ORPHANS"
FROM A
WHERE A.DOC_NUM NOT IN
(SELECT B.DOC_NUM
FROM B
);
---------+---------+---------+---------+---------+---------+
OLD ORPHANS
---------+---------+---------+---------+---------+---------+
22410
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100


2) Now, I'm going to be eventually deleting a lot of parents according to a rule. This should increase the number of orphans:

---------+---------+---------+---------+---------+---------+
SELECT COUNT(*) "MAYBE NEW ORPHANS"
FROM A
WHERE A.DOC_NUM NOT IN
(SELECT B.DOC_NUM
FROM B
WHERE DATE(B.CREATED_TIMESTAMP) < CURRENT DATE - 18 MONTHS
AND DATE(B.EFF_DATE) < CURRENT DATE - 18 MONTHS
);
---------+---------+---------+---------+---------+---------+
MAYBE NEW ORPHANS
---------+---------+---------+---------+---------+---------+
448276
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100


3) So far, so good. Remove parents from the "in" list, and the result is to increase the number of orphans. Now, actually delete those parents:

---------+---------+---------+---------+---------+---------+
DELETE FROM B
WHERE DATE(B.CREATED_TIMESTAMP) < CURRENT DATE - 18 MONTHS
AND DATE(B.EFF_DATE) < CURRENT DATE - 18 MONTHS;
---------+---------+---------+---------+---------+---------+
DSNE615I NUMBER OF ROWS AFFECTED IS 757032
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0


4) So we've deleted a lot of rows from the parent table. Since we used the same predicates to delete in (3), as to select in (2), the number of orphans should increase to the same "new" level; 4 should equal 2, right??

---------+---------+---------+---------+---------+-------
SELECT COUNT(*) "CONFIRMED NEW ORPHANS"
FROM A
WHERE A.DOC_NUM NOT IN
(SELECT B.DOC_NUM
FROM B
);
---------+---------+---------+---------+---------+-------
CONFIRMED NEW ORPHANS
---------+---------+---------+---------+---------+-------
80021
DSNE610I NUMBER OF ROWS DISPLAYED IS 1


CONCLUSION:
I don't understand what happened here. Does anyone have a way of describing this where (4) does not equal (2), and this number makes sense? Or am I actually looking at a bad result from the SQL engine?? Help!!


--Phil Sevetson
**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-----

-----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.**

Horacio Villa

DB2 SQL on z: Can't make the arithmetic work
(in response to Philip Sevetson)
I think Lock is right. Query 3, looking for rows that "will be" orphans
when you delete its father, have to be IN and NOT IN the subset before you
delete the father.
Query 3 is counting "already orphans" and not "will be orphans".

Horacio Villa


Philip Sevetson

DB2 SQL on z: Can't make the arithmetic work
(in response to Horacio Villa)
Okay, I see this now. I have the conditional reversed in (2).

Horacio, Lock, thanks.

From: Horacio Villa [mailto:[login to unmask email]
Sent: Monday, April 16, 2018 4:38 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 SQL on z: Can't make the arithmetic work

I think Lock is right. Query 3, looking for rows that "will be" orphans when you delete its father, have to be IN and NOT IN the subset before you delete the father.
Query 3 is counting "already orphans" and not "will be orphans".

Horacio Villa



-----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.**

Philip Sevetson

DB2 SQL on z: Can't make the arithmetic work
(in response to Philip Sevetson)
The fix (reversing the conditions in the subselect) gave a consistent solution, resolving my problem. Thanks, again.

--Phil

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, April 16, 2018 4:46 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: DB2 SQL on z: Can't make the arithmetic work

Okay, I see this now. I have the conditional reversed in (2).

Horacio, Lock, thanks.

From: Horacio Villa [mailto:[login to unmask email]
Sent: Monday, April 16, 2018 4:38 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: DB2 SQL on z: Can't make the arithmetic work

I think Lock is right. Query 3, looking for rows that "will be" orphans when you delete its father, have to be IN and NOT IN the subset before you delete the father.
Query 3 is counting "already orphans" and not "will be orphans".

Horacio Villa


-----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-----
**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.**

Lockwood Lyon

DB2 SQL on z: Can't make the arithmetic work
(in response to Philip Sevetson)
Phil,

You either ARE or ARE NOT in the subset of people to which I reply,
"you are welcome" :)

Negative logic ... aaargh!

- Lock

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, April 16, 2018 4:46 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: DB2 SQL on z: Can't make the arithmetic work

Okay, I see this now. I have the conditional reversed in (2).

Horacio, Lock, thanks.

From: Horacio Villa [mailto:[login to unmask email]
Sent: Monday, April 16, 2018 4:38 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 SQL on z: Can't make the arithmetic work

I think Lock is right. Query 3, looking for rows that "will be"
orphans when you delete its father, have to be IN and NOT IN the
subset before you delete the father.
Query 3 is counting "already orphans" and not "will be orphans".

Horacio Villa

-----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-----