Frequency of image copy jobs' execution

Bharath Nunepalli

Frequency of image copy jobs' execution

Hi All,
My requirement is to find the frequency of image copy jobs execution for the databases.

I tried to code a SQL query on SYSIBM.SYSCOPY, but not 100% successful in that.

 

Did any one code a SQL query (or any other process) to find the frequency of image copy jobs execution?

If yes, please share the details with me.

 

Bharath Nunepalli,

Senior DB2 DBA.

Philip Sevetson

Frequency of image copy jobs' execution
(in response to Bharath Nunepalli)
Bharath,

What result are you looking for? Number of executions (Full copy, Incremental Copy, Partition Level?) in a week or month? Or something else?

--Phil

From: Bharath Nunepalli [mailto:[login to unmask email]
Sent: Friday, December 29, 2017 10:28 AM
To: [login to unmask email]
Subject: [DB2-L] - Frequency of image copy jobs' execution


Hi All,
My requirement is to find the frequency of image copy jobs execution for the databases.

I tried to code a SQL query on SYSIBM.SYSCOPY, but not 100% successful in that.



Did any one code a SQL query (or any other process) to find the frequency of image copy jobs execution?

If yes, please share the details with me.



Bharath Nunepalli,

Senior DB2 DBA.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Bharath Nunepalli

RE: Frequency of image copy jobs' execution
(in response to Philip Sevetson)


Phil,

I want number of executions (we take only FULL COPY ICs) in a month or a quarter.

Each database is backed up by more than one COPY job.

 

Bharath Nunepalli,

Senior DB2 DBA.

Philip Sevetson

Frequency of image copy jobs' execution
(in response to Bharath Nunepalli)
That seems like an easy task - ? This gets me results:

SELECT DBNAME, TSNAME, DSNUM, COUNT(*) AS "NBR OF COPIES TAKEN"
FROM SYSIBM.SYSCOPY
WHERE ICTYPE = 'F'
AND DATE(TIMESTAMP) >= CURRENT DATE - 92 DAYS
/* 92 days is the length of the fourth quarter in the standard Gregorian calendar */
AND DBNAME LIKE 'PROD%'
AND ICBACKUP = ' '
/* ICBACKUP = ' ' is the identifier for a local, primary backup */
GROUP BY DBNAME, TSNAME, DSNUM
;

The results look like this:
DBNAME TSNAME DSNUM NBR OF COPIES TAKEN
-------- -------- ----- -------------------
XXXXX SAAAAAAA 0 61
XXXXX SBBBBBBB 0 31
XXXXX SCCCCCCC 0 22

Note that you’ll want to change the DBNAME predicate ☺

From: Bharath Nunepalli [mailto:[login to unmask email]
Sent: Friday, December 29, 2017 11:58 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Frequency of image copy jobs' execution


Phil,

I want number of executions (we take only FULL COPY ICs) in a month or a quarter.

Each database is backed up by more than one COPY job.



Bharath Nunepalli,

Senior DB2 DBA.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Philip Sevetson

Frequency of image copy jobs' execution
(in response to Philip Sevetson)
And, one warning – the number of image copies recorded in the catalog is, of course, affected by the use of MODIFY RECOVERY. Adding a MIN(DATE(TIMESTAMP)) column, to identify databases which retain less history than the length of your period of interest, might be prudent. Thus:

SELECT DBNAME, TSNAME, DSNUM
, COUNT(*) AS "NBR OF COPIES TAKEN"
, MIN(DATE(TIMESTAMP)) AS "EARLIEST COPY"
FROM SYSIBM.SYSCOPY
WHERE ICTYPE = 'F'
AND DATE(TIMESTAMP) >= CURRENT DATE - 92 DAYS
/* 92 days is the length of the fourth quarter in the standard Gregorian calendar) */
AND DBNAME LIKE 'D%'
AND ICBACKUP = ' '
/* ICBACKUP = ' ' is the identifier for a local, primary backup */
GROUP BY DBNAME, TSNAME, DSNUM
;


DBNAME TSNAME DSNUM NBR OF COPIES TAKEN EARLIEST COPY
-------- -------- ----- ------------------- -------------
DXXXX SAAAAAAA 0 61 2017-11-29
DXXXX SBBBBBBB 0 31 2017-12-14
DXXXX SCCCCCCC 0 22 2017-11-28
DXXXX SDDDDDDD 0 22 2017-11-28
DXXXX SEEEEEEE 0 42 2017-11-29


From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, December 29, 2017 12:24 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Frequency of image copy jobs' execution

That seems like an easy task - ? This gets me results:

SELECT DBNAME, TSNAME, DSNUM, COUNT(*) AS "NBR OF COPIES TAKEN"
FROM SYSIBM.SYSCOPY
WHERE ICTYPE = 'F'
AND DATE(TIMESTAMP) >= CURRENT DATE - 92 DAYS
/* 92 days is the length of the fourth quarter in the standard Gregorian calendar */
AND DBNAME LIKE 'PROD%'
AND ICBACKUP = ' '
/* ICBACKUP = ' ' is the identifier for a local, primary backup */
GROUP BY DBNAME, TSNAME, DSNUM
;

The results look like this:
DBNAME TSNAME DSNUM NBR OF COPIES TAKEN
-------- -------- ----- -------------------
XXXXX SAAAAAAA 0 61
XXXXX SBBBBBBB 0 31
XXXXX SCCCCCCC 0 22

Note that you’ll want to change the DBNAME predicate ☺

From: Bharath Nunepalli [mailto:[login to unmask email]
Sent: Friday, December 29, 2017 11:58 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Frequency of image copy jobs' execution


Phil,

I want number of executions (we take only FULL COPY ICs) in a month or a quarter.

Each database is backed up by more than one COPY job.



Bharath Nunepalli,

Senior DB2 DBA.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Bharath Nunepalli

RE: Frequency of image copy jobs' execution
(in response to Philip Sevetson)

Phil,

Sorry, I guess I'm not clear in explaining my requirement.

When I say frequency of execution, I mean the average number of days between each execution of image copy jobs.

 

Bharath Nunepalli,

Senior DB2 DBA.

Philip Sevetson

Frequency of image copy jobs' execution
(in response to Bharath Nunepalli)
That gets ugly fast. You need a multi-stage query: First, join every qualifying entry in the table to the next highest qualifying entry for that DBNAME || TSNAME || CHAR(DSNUM). Now, compute the TIMESTAMPDIFF in days between the two. Now, take the result set and get AVG of the computed TIMESTAMPDIFF. (You might want to also get the MAX and MIN TIMESTAMPDIFF to be sure you’re not seeing large gaps or clusters, and a COUNT to be sure that you have enough data that an average is meaningful.)

From: Bharath Nunepalli [mailto:[login to unmask email]
Sent: Friday, December 29, 2017 12:35 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Frequency of image copy jobs' execution


Phil,

Sorry, I guess I'm not clear in explaining my requirement.

When I say frequency of execution, I mean the average number of days between each execution of image copy jobs.



Bharath Nunepalli,

Senior DB2 DBA.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Bharath Nunepalli

RE: Frequency of image copy jobs' execution
(in response to Philip Sevetson)

I tried something like this. But, didn't get accurate output.

WITH BASE AS
(
SELECT DBNAME
,ICDATE
,ROW_NUMBER() OVER (PARTITION BY DBNAME ORDER BY ICDATE) AS RN
FROM SYSIBM.SYSCOPY WHERE DBNAME='DBNAME' AND ICDATE>170601 AND JOBNAME LIKE 'DB%'
)
SELECT B1.DBNAME,
AVG(TIMESTAMPDIFF(32,CHAR(TIMESTAMP(DSN8.ALTDATE((SUBSTR(B1.ICDATE,1,2)||'.'||SUBSTR(B1.ICDATE,3,2)||'.'||SUBSTR(B1.ICDATE,5,2)),'YY.M.D','YYYY-MM-DD'))
- TIMESTAMP(DSN8.ALTDATE((SUBSTR(B2.ICDATE,1,2)||'.'||SUBSTR(B2.ICDATE,3,2)||'.'||SUBSTR(B2.ICDATE,5,2)),'YY.M.D','YYYY-MM-DD')))))
FROM BASE B1
INNER JOIN
BASE B2
ON B1.DBNAME=B2.DBNAME
AND B2.RN =B1.RN-1
GROUP BY B1.DBNAME

 

Bharath Nunepalli,

Senior DB2 DBA.

Philip Sevetson

Frequency of image copy jobs' execution
(in response to Bharath Nunepalli)
1) You probably need to partition by (DBNAME, TSNAME, DSNUM), and select ICTYPE='F' AND ICBACKUP=' ' (two spaces).

2) Not sure what ALTDATE does, so I can’t figure that out.

3) _Why_ are you using ICDATE instead of TIMESTAMP and DATE(TIMESTAMP)???

From: Bharath Nunepalli [mailto:[login to unmask email]
Sent: Friday, December 29, 2017 12:56 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Frequency of image copy jobs' execution


I tried something like this. But, didn't get accurate output.

WITH BASE AS
(
SELECT DBNAME
,ICDATE
,ROW_NUMBER() OVER (PARTITION BY DBNAME ORDER BY ICDATE) AS RN
FROM SYSIBM.SYSCOPY WHERE DBNAME='DBNAME' AND ICDATE>170601 AND JOBNAME LIKE 'DB%'
)
SELECT B1.DBNAME,
AVG(TIMESTAMPDIFF(32,CHAR(TIMESTAMP(DSN8.ALTDATE((SUBSTR(B1.ICDATE,1,2)||'.'||SUBSTR(B1.ICDATE,3,2)||'.'||SUBSTR(B1.ICDATE,5,2)),'YY.M.D','YYYY-MM-DD'))
- TIMESTAMP(DSN8.ALTDATE((SUBSTR(B2.ICDATE,1,2)||'.'||SUBSTR(B2.ICDATE,3,2)||'.'||SUBSTR(B2.ICDATE,5,2)),'YY.M.D','YYYY-MM-DD')))))
FROM BASE B1
INNER JOIN
BASE B2
ON B1.DBNAME=B2.DBNAME
AND B2.RN =B1.RN-1
GROUP BY B1.DBNAME



Bharath Nunepalli,

Senior DB2 DBA.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Bharath Nunepalli

RE: Frequency of image copy jobs' execution
(in response to Philip Sevetson)

I'll try to partition by (DBNAME, TSNAME, DSNUM) and re-run the query.

I forgot that there is TIMESTAMP column in SYSCOPY, so used ICDATE :)

 

Bharath Nunepalli,

Senior DB2 DBA.

Bharath Nunepalli

RE: Frequency of image copy jobs' execution
(in response to Philip Sevetson)

TIMESTAMPDIFF function assumes that one month has 30 days.

I'm not getting correct results even when I use DAYS function.

Example. When I use TIMESTAMPDIFF to get days between 2017-09-28 and 2017-12-28,

I'm getting 90 but the correct result is 92.

Which function should I use the get the correct number of days between 2 dates?


Bharath Nunepalli,

Senior DB2 DBA.

Philip Sevetson

Frequency of image copy jobs' execution
(in response to Bharath Nunepalli)
The manual notes that 30-day rule about months. I don’t know why it’s done that way, but there isn’t a fix for it.

You’d probably have to create a UDF which correctly calculates dates. You probably will want to include code to account for leap year.

--Phil

From: Bharath Nunepalli [mailto:[login to unmask email]
Sent: Friday, December 29, 2017 2:00 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Frequency of image copy jobs' execution


TIMESTAMPDIFF function assumes that one month has 30 days.

I'm not getting correct results even when I use DAYS function.

Example. When I use TIMESTAMPDIFF to get days between 2017-09-28 and 2017-12-28,

I'm getting 90 but the correct result is 92.

Which function should I use the get the correct number of days between 2 dates?



Bharath Nunepalli,

Senior DB2 DBA.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Sam Baugh

Frequency of image copy jobs' execution
(in response to Philip Sevetson)
If all you care about is just difference in days then use the DAYS function
per date/timestamp and calculate the difference.

select days(d1)
, days(d2)
, days(d1) - days(d2) + 1
, timestampdiff(16,char(d1-d2))
from (
select timestamp('2017-12-28') as d1
, timestamp('2017-09-28') as d2
from sysibm.sysdummy1
) as x
with ur

On Fri, Dec 29, 2017 at 1:47 PM, Sevetson, Phil <[login to unmask email]>
wrote:

> The manual notes that 30-day rule about months. I don’t know why it’s done
> that way, but there isn’t a fix for it.
>
>
>
> You’d probably have to create a UDF which correctly calculates dates. You
> probably will want to include code to account for leap year.
>
>
>
> --Phil
>
>
>
> *From:* Bharath Nunepalli [mailto:[login to unmask email]
> *Sent:* Friday, December 29, 2017 2:00 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: Frequency of image copy jobs' execution
>
>
>
> TIMESTAMPDIFF function assumes that one month has 30 days.
>
> I'm not getting correct results even when I use DAYS function.
>
> Example. When I use TIMESTAMPDIFF to get days between 2017-09-28 and
> 2017-12-28,
>
> I'm getting 90 but the correct result is 92.
>
> Which function should I use the get the correct number of days between 2
> dates?
>
>
>
> Bharath Nunepalli,
>
> Senior DB2 DBA.
>
>
> -----End Original Message-----
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
> -----End Original Message-----
>

Bharath Nunepalli

RE: Frequency of image copy jobs' execution
(in response to Sam Baugh)

Sam,

Thanks for the query.

Yes, I have tried days function earlier to get the output.

After not getting expected result from date/time functions, I wanted to make sure I didn't try something wrong.

And, I'm unable to understand why IBM decided to go with only 30 days for month for TIMESTAMPDIFF function.

 

Bharath Nunepalli,

Senior DB2 DBA.

Bharath Nunepalli

RE: Frequency of image copy jobs' execution
(in response to Philip Sevetson)

Finally came up with this query.Got expected result.

Thanks Phil & Sam for helping me.

 

WITH
OCCUR(OCC) AS
(SELECT
COUNT(DISTINCT ICDATE)
FROM SYSIBM.SYSCOPY
WHERE
DBNAME = 'dbname' AND
ICTYPE = 'F' AND
ICDATE >= 170601 AND
ICBACKUP = ' ' AND
JOBNAME LIKE 'DB%')
,DIFF(DAYS) AS
(SELECT
DAYS(MAX(TIMESTAMP)) - DAYS(MIN(TIMESTAMP)) + 1
FROM SYSIBM.SYSCOPY
WHERE
DBNAME = 'dbname' AND
ICTYPE = 'F' AND
ICDATE >= 170601 AND
ICBACKUP = ' ' AND
JOBNAME LIKE 'DB%')
SELECT INT(DAYS/OCC) FROM OCCUR, DIFF;

 

Bharath Nunepalli,

Senior DB2 DBA.

Carl Castrianni

RE: Frequency of image copy jobs' execution
(in response to Bharath Nunepalli)

Here's another possible solution which may give more accurate results depending upon your requirements.
To me, as an example, if 3 image copies were taken on the 1st, 2nd and 11th day of the month then the frequency would be 5 instead of 3.  My reasoning is there are 2 frequency intervals to measure (between 1st and 2nd which is 1 day and between 2nd and 11th which is 9 days).  So the average of those 2 intervals is (1 + 9) / 2 intervals = 5 as calculated by the following select:

SELECT DBNAME,
       TSNAME,
       (DAYS(MAX(TIMESTAMP)) - DAYS(MIN(TIMESTAMP))) / (COUNT(DISTINCT ICDATE)-1) FREQ_INT,
       DECIMAL((DAYS(MAX(TIMESTAMP)) - DAYS(MIN(TIMESTAMP))) / DECIMAL((COUNT(DISTINCT ICDATE)-1)),7,2) FREQ_DEC
  FROM SYSIBM.SYSCOPY
 WHERE DBNAME = 'dbname'
   AND ICTYPE = 'F'
   AND ICDATE >= '170601'
   AND ICBACKUP = ' '
   AND JOBNAME LIKE 'DB%'
 GROUP BY DBNAME, TSNAME
 ORDER BY 1, 2;

Bharath Nunepalli

RE: Frequency of image copy jobs' execution
(in response to Carl Castrianni)

Carl,

Thanks for providing an alternate solution.

 

Bharath Nunepalli,

Senior DB2 DBA.