Left Outer Join

sathyaram_s · #1 (**permalink**) 05-21-04, 13:01

DB2 Version 8 , zOS

SQL Statment to generate a report of a list of tablespaces and the last full image copy timestamp. If there is no full image copy, then display 'NOCOPY'

This is the piece of SQL I'd expect will do this ... But, it doesn't ... All my LAST_IC columns are 'NOCOPY'.

Code:

SELECT A.DBNAME,A.NAME,COALESCE(char(C.ITIMESTAMP),'NOCOPY') as LAST_IC
      FROM
          SYSIBM.SYSTABLESPACE A
      LEFT OUTER JOIN
         (
          SELECT DBNAME,TSNAME,MAX(TIMESTAMP) AS ITIMESTAMP
              FROM
          SYSIBM.SYSCOPY
              WHERE ICTYPE = 'F'
              GROUP BY DBNAME,TSNAME
         ) C
          ON   A.DBNAME = C.DBNAME
          AND A.NAME = C.TSNAME

I guess I'm missing something very silly here ...

(I do have image copies !!
SELECT DBNAME,TSNAME,MAX(TIMESTAMP) AS ITIMESTAMP
FROM
SYSIBM.SYSCOPY
WHERE ICTYPE = 'F' and DBNAME='DSNDB06'
GROUP BY DBNAME,TSNAME
fetch first 1 row only

Output is :

DSNDB06 SYSALTER 2004-05-21 13:21:47.968565

)

Any pointers ?

TIA

Sathyaram

grofaty · #2 (**permalink**) 05-24-04, 01:47

Hi,

Not sure, but didn't you forget (lower case words):

Code:

...
LEFT OUTER JOIN
	select dbname, tsname itimestamp from
         (
          SELECT DBNAME,TSNAME,MAX(TIMESTAMP) AS ITIMESTAMP
...

Hope this helps,
Grofaty

RKrick · #3 (**permalink**) 05-24-04, 03:40

Sathyaram,

your SQL looks OK. Are you sure you're getting/analysing all the rows in the result set? Just to check it:
SELECT * FROM
(
SELECT A.DBNAME,A.NAME,COALESCE(char(C.ITIMESTAMP),'NOCOP Y') as LAST_IC
FROM
SYSIBM.SYSTABLESPACE A
LEFT OUTER JOIN
(
SELECT DBNAME,TSNAME,MAX(TIMESTAMP) AS ITIMESTAMP
FROM
SYSIBM.SYSCOPY
WHERE ICTYPE = 'F'
GROUP BY DBNAME,TSNAME
) C
ON A.DBNAME = C.DBNAME
AND A.NAME = C.TSNAME
) CC
WHERE LAST_IC <> 'NOCOPY'
WITH UR;

sathyaram_s · #4 (**permalink**) 05-24-04, 07:04

Rodney , Thanks for taking your time to reply ...

This morning I created federated connection to my z/OS db2 subsystem and defined nicknames for the two catalog tables ... Did the same query, and I got the correct result ..

hmmm .. Can't understand why it does not give the correct result ... The new Unicode catalog ???

Cheers
Sathyaram

RKrick · #5 (**permalink**) 05-24-04, 07:10

Maybe it's just a little bug... If you search the APAR DB by IBM for "OUTER JOIN", you get a lot of matchings. This feature isn't mature yet (although it's available since V5!!!). IBM should invest more money in testing it ;-)
By the way, I've tested youre SQL against a V5 and it works (maybe you're right, it's a colateral effect from unicode).

Internet Services

Web Development

Digital Marketing

Consumer Tech