Hi, Listers.

Thanks for your support and help always.

I am trying to get backup information for all tablespaces in all
production databases.

My first question is there are 14 ICTYPES in SYSCOPY table. Among them,
are F,I,R,S,Y,W,Z only valid backup types?


My second question is, when I run this,

SELECT DISTINCT(DBNAME||TSNAME), ICTYPE, SUBSTR(DSNAME,1,4)
FROM SYSIBM.SYSCOPY
WHERE DBNAME = 'MIRP'
AND ICTYPE <> 'Q'
ORDER BY ICTYPE;

I got this result. This is a part of the query.



MIRP ADDREXCL F BAR.
MIRP ADDREXCL F BKP.
MIRP AIQI F BAR.
MIRP AIQI F BKP.
MIRP AIQO F BAR.
MIRP AIQO F BKP.
MIRP AIQO F SSA.
MIRP BENETXN F BAR.
MIRP BENETXN F BKP.
MIRP BIETSE2H F BAR.
MIRP BIETSE2H F BKP.
MIRP CAIQI F BAR.
MIRP CAIQI F BKP.
MIRP CASENUM F BKP.
MIRP CBENE F BAR.
MIRP CBENE F BKP.
MIRP CBENETXN F BAR.
MIRP CBENETXN F BKP.
MIRP PTFCSR F BAR.
MIRP PTFCSR F BKP.

MIRP PTFCSR W MIRP
MIRP AIQI Z MIRP
MIRP AIQO Z MIRP
MIRP BENETXN Z MIRP
MIRP BIETSE2H Z MIRP
MIRP CAIQI Z MIRP
MIRP CASENUM Z MIRP
MIRP CBENE Z MIRP
MIRP CBENETXN Z MIRP


F is full backup, bar. is off site copy and bkp. on site copy. MIRP is
the database I am searching and the next column shows tablespaces.

As you can see, PTFCSR has 'F' type and 'W' type. Since PTFCSR has 'F'
type I don't want the same tablespace name appear with 'W' type. It is
redundant. I don't want to see that. All others are same. AIQI, BENETXN,
CAIQI, CASENUM, CBENE tables have both 'F' type and 'Z' tyes. I don't
want to see those table names with 'Z' type.

But for example, there is a tablespace name, 'ABC' (I am making up) that
has only 'Z' type not 'F' type. I want this tablespace name appear in my
query. How should I modify my SQL?






Thanks

James

DB2 & UDB email list listserv db2-l LazyDBA home page