Results 1 to 5 of 5

Thread: Left Outer Join

  1. #1
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Unanswered: Left Outer Join

    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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

  3. #3
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    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;
    Rodney Krick

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Last edited by sathyaram_s; 05-24-04 at 08:17.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    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).
    Last edited by RKrick; 05-24-04 at 08:12. Reason: by the way...
    Rodney Krick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •