Posted: Mon Apr 07, 2008 6:02 am Post subject: Help with coding complex SQL (DB2 z/OS v8 CM)
Hi everyone,
Tearing my hair out a little bit with this one, if anybody is able to offer suggestions it would be most appreciated... It sounds fairly simple on the face of it, but I'm having trouble with the coding.
I'm trying to interrogate SYSIBM.SYSCOPY (to create a list of Modify Recovery statements) at DBNAME/TSNAME/DSNUM level.
Our rules are:-
1) If the timestamp of the latest Full IC for a db/ts/dsnum combo is over 3 months old, return a row with the date of that timestamp.
2) If the timestamp of the latest Full IC is under 3 months old, find and return a row with the date of the most recent syscopy row that is over 3 months old of any type for that db/ts/dsnum.
3). If no Full IC exists, or there are no entries of any type over 3 months old, a row is not required in the result set for that db/ts/dsnum.
4) Must work at v8 CM.
The (incomplete) query is shown below to demonstrate the relevant fields - at present this just returns the latest FIC timestamp for each DB/TS/DSNUM combo where a FIC exists. I've tried adding self joins, correlated sub-queries, case statements etc to this, but cannot get the desired results.
SELECT DBNAME, TSNAME, DSNUM, MAX(TIMESTAMP)
FROM SYSIBM.SYSCOPY
WHERE ICTYPE = 'F'
GROUP BY DBNAME, TSNAME, DSNUM
WITH UR;
We have some SYSCOPY tables with record counts in the millions, so reasonable performance is important.
Thanks in advance, your suggestions are eagerly anticipated!
select dbname, tsname, dsnum, max(TIMESTAMP)
from sysibm.syscopy
where timestamp < current timestamp - 3 months
and ICTYPE = 'F'
group by dbname, tsname, dsnum
Hi Videlord,
Thanks for your reply... however, your SQL does not meet the requirements of all the rules. Please let me know if they are not clear.
Any other takers for this SQL challenge???
Thanks,
Grimp.
select dbname, tsname, dsnum, max(TIMESTAMP)
from sysibm.syscopy
where timestamp < current timestamp - 3 months
and ICTYPE = 'F'
group by dbname, tsname, dsnum
union all
select dbname, tsname, dsnum, max(TIMESTAMP)
from sysibm.syscopy
where timestamp < current timestamp - 3 months
and exists (select 1 from sysibm.syscopy
where timestamp > current timestamp - 3 months
and ICTYPE = 'F' )
group by dbname, tsname, dsnum
select dbname, tsname, dsnum, max(TIMESTAMP)
from sysibm.syscopy
where timestamp < current timestamp - 3 months
and ICTYPE = 'F'
and not exists (select 1 from sysibm.syscopy
where timestamp > current timestamp - 3 months
and ICTYPE = 'F' )
group by dbname, tsname, dsnum
union all
select dbname, tsname, dsnum, max(TIMESTAMP)
from sysibm.syscopy
where timestamp < current timestamp - 3 months
and exists (select 1 from sysibm.syscopy
where timestamp > current timestamp - 3 months
and ICTYPE = 'F' )
group by dbname, tsname, dsnum
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
