Oooh, that's evil. Had a consult with the in-house DBA. This works for me on V7.1.1:
select count(*) from nodes where node_name not in (select node_name from filespaces where node_name is not null) In V6-7 there is 1 filespace with a NULL node name - looks to me like something created by the funky backup of the DB2 DB. DBA says because the result table created by "select node_name from filespaces" has that NULL value in it, DB2 somehow can't do the "not in" match (or not in the way it would seem logical to me, anyway). Seems like a lousy system, but I didn't feel like taking the time to dig into the bowels of DB2 doc on exactly what "not in" considers. There's an even more complicated way by adding a "left join" and forcing the subordinate select to build the result table that you would expect without a NULL in it. But this is the simplest way. Wanda Prather TSM and Disaster Recovery Consultant ICF International Enterprise and Cybersecurity Systems Division -----Original Message----- From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Schofield, Neil (Storage & Middleware, Backup & Restore) Sent: Friday, January 09, 2015 10:44 AM To: ADSM-L@VM.MARIST.EDU Subject: Re: [ADSM-L] Changed SQL behavior under server 6.2.3.1 David Thanks for the suggestion. I would have thought both were equivalent, but I tried each of them anyway and both gave the wrong answer: tsm: XXXXX>select count(*) from nodes where node_name not in (select node_name from filespaces) Unnamed[1] ------------ 0 tsm: XXXXX>select count(*) from nodes where not node_name in (select node_name from filespaces) Unnamed[1] ------------ 0 Regards Neil > Hi, > the last query should be select count(*) from nodes where node_name > not in (select node_name from filespaces) > > NOT keyword between column name and IN keyword. > > David RIGAUDIERE Neil Schofield Tivoli Storage Manager SME Backup & Recovery | Storage & Middleware | Service Delivery | Group IT LLOYDS BANKING GROUP ________________________________ I: 75813101 | T: 0113 235 3101 | M: 07979 703303 | E: neil.schofi...@lloydsbanking.com<mailto:neil.schofi...@lloydsbanking.com> 50 Cote Lane, Dawsons Corner, Pudsey, LEEDS LS28 5ED TNT46 (PUD/FL1/WW/098) Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555. Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500. Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 08457 21 31 41. Cheltenham & Gloucester plc. Registered Office: Barnett Way, Gloucester GL4 3RL. Registered in England and Wales 2299428. Telephone: 0845 603 1637 Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority. Cheltenham & Gloucester plc is authorised and regulated by the Financial Conduct Authority. Halifax is a division of Bank of Scotland plc. Cheltenham & Gloucester Savings is a division of Lloyds Bank plc. HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813. This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.