• Are you backups taking too long?
• What is the trend?
• How many logs are we archiving?
• How quickly do we archive them at peak time?
This can help determine whether there is sufficient capacity in the backup facility. It is especially important if a shared tape library is being used. In this scenario, there could be significant contention during the backup window.
This can then have a knock on impact onto the batch window – which in turn can cause an outage during the online day.
23
24
Example 1 – Backups / Logs
• Where does the information come from?
• List history backup all for db?
• User exit logs?
• Diaglog?
• Admin list history table function?
• If you make it easy you deliver more
• It demonstrates the benefit of the work
24
25
Example 1 – Backups / Logs
• Use the admin list history table function
• Have a control table with the last timestamp for information exported
• Export . . . Select * from table(admin_list_hist()) as alh_tab where start_time > last timestamp or
end_time > last timestamp
• It is important to have the ‘or’
• What happens if you export after the backup has started?
The ‘or’ means that you pick up backups that have started but not finished – perhaps the server crashed – and also those backups that had started prior to the previous export but hadn’t finished. That way you can be certain of getting the complete picture.
25
26
Example 1 – Backups / Logs
• Load the data into a temporary table
• Use the merge statement to update the data into the base tables
• Yes – tables - plural
• There is quite a bit of information in there
The merge statement gives the ability to combine the insert of new backups in the same statement as the update of existing backups (those that had started but not finished). Each database has a unique id for entries into the admin list history, so it works very well.
26
27
Example 1 – Backups / Logs
• A table for backup information
• Another for log archives
• Another for loads
• Another for the rest – or break it down further
• The number of tables is up to you
• If backup timings are critical, one for backups and one for the rest is a good start
• It means you deliver, but don’t lose data
27
28
Example 1 – Backups / Logs
• What important log information do I have?
• Log number
• Operation / Type
• The important thing is really throughput
• Decide how often you want it summarised
• .. Select year,month, day, hour, count(*)
• .. Select year, julian date, count(*)
28
29
Example 1 – Backups / Logs
• Then aggregate further to keep for longer
• .. Select year, month, count(*)
• This wont be many rows and you can keep for a long time
• So what?
• It doesn’t want to be ‘eye-balled’
• This makes it more difficult / fun depending on your point of view
• Opportunity to ‘play’
29
30
Example 1 – Backups / Logs
• Assume I have year, month, day, count
• What do I want to know?
• Is it getting worse year on year?
• Is it getting worse day by day?
• Year on year – definitely.
• Day by day could be difficult to see if it’s one log per week or Mondays are always bad
30
31
Example 1 – Backups / Logs
• So let’s break it down.
• Q1. Did we archive more logs last month than in previous year?
• With logs_for_month (log_year, total_logs) as
• (select log_year, sum(*) from log_history
• Where month = month(current date -1 month)
• Group by year;
This common table expression gathers the total number of logs for the previous month per year. So if we are in November it will give the annual totals for October.
31
32
Example 1 – Backups / Logs
• Select log_year, total_logs,
• Total_logs – sum(total_logs) over (order by log_year rows between 1 preceding and 1 preceding) as change
• From logs_for_month
• Order by year;
• This now gives me an order list with the change from the previous year
• I can now see how it is growing
I still reference BOB Lyle’s 2001 presentation from Florence when I am looking at OLAP SQL. Not surprising that it won the best overall presentation!
32
33
Example 1 – Backups / Logs
• I could have gone further
• I could had had that as a common table
expression and selected the last row with the total + 5 * change to see how many logs will be
archived in five years
• This query is in the notes
• Or you could look a the change over two years
with logs_for_month (log_year, total_logs) as (select log_year, count(*) from loginfo group by log_year),
logs_change (log_year, total_logs, change_logs) as (select log_year, total_logs,
total_logs - sum(total_logs) over (order by log_year rows between 1 preceding and 1 preceding) as change
from logs_for_month)
select total_logs, total_logs + (5 * change_logs) from logs_change where log_year = (select max(log_year) from logs_change)
;
33
34
Example 1 – Backups / Logs
• Q2. Is it increasing week by week or month by month?
• The same method could be used
• You can use the week function to give a week number, order by year, week, sum them up
• Or by quarter?
34
35
Example 1 – Backups / Logs
• Of course having the date in quarters really wants the information pivoting
• SELECT Year,
MAX(CASE WHEN Quarter = 1 THEN Results END) AS Q1, MAX(CASE WHEN Quarter = 2 THEN Results END) AS Q2, MAX(CASE WHEN Quarter = 3 THEN Results END) AS Q3, MAX(CASE WHEN Quarter = 4 THEN Results END) AS Q4 FROM Sales
GROUP BY Year
This query puts all the Q1 figures in Q1, Q2 in Q2 and so on. It then groups it so that instead of having:
Year Quarter Results
36