4-DB_Sizing_Report.r
# -----------------------------------------------------------------------------------# File Name : 4-DB_Sizing_Report.r# Author : Pierre Montbleau# Description : Database Sizing and Growth Analysis Report Script# Purpose : Analyze database growth patterns, calculate sizing trends, # and generate PDF reports with visualizations# Call Syntax : source("F:\\DBA\\Scripts\\R\\4-DB_Sizing_Report.r")# Parameters : days_back (default=30) - Number of days to analyze# output_file_name (optional) - Custom output PDF filename# Last Modified: 2024-01-15# -----------------------------------------------------------------------------------
# -----------------------------------------------------------------------------------# Load necessary libraries# -----------------------------------------------------------------------------------library(ggplot2)library(dplyr)library(DBI)library(odbc)
# -----------------------------------------------------------------------------------# Function to get database sizing data# -----------------------------------------------------------------------------------get_database_sizing_data <- function(days_back) { # Connect to the database conn <- dbConnect(odbc::odbc(), dsn = "[DB_NAME]", uid = "[USERNAME]", pwd = "[PASSWORD]") # Build and execute the query query <- paste0(" SELECT REPORT_ID, DB_NAME, DB_SIZE, CHECK_DATE FROM ATTSTATS.DATABASE_SIZING WHERE trunc(check_date) > trunc(sysdate-", days_back, ") ORDER BY CHECK_DATE") # Execute the query and fetch results result <- dbGetQuery(conn, query) # Disconnect from the database dbDisconnect(conn) return(result)}
# -----------------------------------------------------------------------------------# Procedure with PDF Output# -----------------------------------------------------------------------------------process_database_sizing <- function(days_back = 30, output_file_name = "database_sizing_report.pdf") {
# Get the data from the database data <- get_database_sizing_data(days_back) # Convert CHECK_DATE to datetime format data$CHECK_DATE <- as.POSIXct(data$CHECK_DATE, format = "%Y/%m/%d %H:%M:%S") # Summarize the database size over time (daily aggregation) data_summary <- data %>% group_by(date = as.Date(CHECK_DATE)) %>% summarize( avg_db_size = mean(DB_SIZE, na.rm = TRUE), min_db_size = min(DB_SIZE, na.rm = TRUE), max_db_size = max(DB_SIZE, na.rm = TRUE) ) %>% mutate(daily_growth = avg_db_size - lag(avg_db_size)) # Calculate total growth over the period total_growth <- data_summary$avg_db_size[nrow(data_summary)] - data_summary$avg_db_size[1] total_growth_date_start <- data_summary$date[1] total_growth_date_end <- data_summary$date[nrow(data_summary)] # Find the day with maximum and minimum growth max_growth_day <- data_summary[which.max(data_summary$daily_growth), ] min_growth_day <- data_summary[which.min(data_summary$daily_growth), ] # Plot Database Size Over Time plot_size <- ggplot(data_summary, aes(x = date, y = avg_db_size)) + geom_line(color = "blue") + labs(title = "Database Size Over Time", x = "Date", y = "Average Database Size (MB)") + theme_minimal() # Handle NA values in daily_growth by replacing them with 0 data_summary <- data_summary %>% mutate(daily_growth = ifelse(is.na(daily_growth), 0, daily_growth)) # Plot Daily Growth Over Time plot_growth <- ggplot(data_summary, aes(x = date, y = daily_growth)) + geom_bar(stat = "identity", fill = "green", alpha = 0.7) + geom_hline(yintercept = 0, color = "red", linetype = "dashed") + labs(title = "Daily Growth of Database Size", x = "Date", y = "Daily Growth (MB)") + theme_minimal() # Save both plots and summary to the specified output PDF file pdf(file = output_file_name) # Print textual summary to the PDF text_summary <- paste( "Database Sizing Report\n", "------------------------\n", "Total Growth Over the Period: ", total_growth, "MB\n", "From: ", total_growth_date_start, " to ", total_growth_date_end, "\n", "Day with Maximum Growth: ", max_growth_day$date, " (", max_growth_day$daily_growth, "MB)\n", "Day with Minimum Growth: ", min_growth_day$date, " (", min_growth_day$daily_growth, "MB)\n" ) grid::grid.text(text_summary, x = 0.1, y = 0.9, just = "left", gp = grid::gpar(fontsize = 10)) # Print the plots to the PDF print(plot_size) print(plot_growth)
# Close the PDF device dev.off() # Return key information return(list( total_growth = total_growth, total_growth_date_start = total_growth_date_start, total_growth_date_end = total_growth_date_end, max_growth_day = max_growth_day, min_growth_day = min_growth_day, plots_saved_to = output_file_name ))}
# -----------------------------------------------------------------------------------# Main execution# -----------------------------------------------------------------------------------
# Run the process_database_sizing function with default 30 days backresult <- process_database_sizing(days_back = 30)
# Print where the plots were savedcat("Plots saved to: ", result$plots_saved_to, "\n")
# Open the PDF fileif (.Platform$OS.type == "windows") { shell.exec(result$plots_saved_to) # For Windows} else { system(paste("open", shQuote(result$plots_saved_to))) # For macOS # For Linux, use xdg-open # system(paste("xdg-open", shQuote(result$plots_saved_to)))}
# -----------------------------------------------------------------------------------# End of Script# -----------------------------------------------------------------------------------message("Database sizing analysis script execution completed successfully.")# -----------------------------------------------------------------------------------
# -----------------------------------------------------------------------------------# Load necessary libraries# -----------------------------------------------------------------------------------library(ggplot2)library(dplyr)library(DBI)library(odbc)
# -----------------------------------------------------------------------------------# Function to get database sizing data# -----------------------------------------------------------------------------------get_database_sizing_data <- function(days_back) { # Connect to the database conn <- dbConnect(odbc::odbc(), dsn = "[DB_NAME]", uid = "[USERNAME]", pwd = "[PASSWORD]") # Build and execute the query query <- paste0(" SELECT REPORT_ID, DB_NAME, DB_SIZE, CHECK_DATE FROM ATTSTATS.DATABASE_SIZING WHERE trunc(check_date) > trunc(sysdate-", days_back, ") ORDER BY CHECK_DATE") # Execute the query and fetch results result <- dbGetQuery(conn, query) # Disconnect from the database dbDisconnect(conn) return(result)}
# -----------------------------------------------------------------------------------# Procedure with PDF Output# -----------------------------------------------------------------------------------process_database_sizing <- function(days_back = 30, output_file_name = "database_sizing_report.pdf") {
# Get the data from the database data <- get_database_sizing_data(days_back) # Convert CHECK_DATE to datetime format data$CHECK_DATE <- as.POSIXct(data$CHECK_DATE, format = "%Y/%m/%d %H:%M:%S") # Summarize the database size over time (daily aggregation) data_summary <- data %>% group_by(date = as.Date(CHECK_DATE)) %>% summarize( avg_db_size = mean(DB_SIZE, na.rm = TRUE), min_db_size = min(DB_SIZE, na.rm = TRUE), max_db_size = max(DB_SIZE, na.rm = TRUE) ) %>% mutate(daily_growth = avg_db_size - lag(avg_db_size)) # Calculate total growth over the period total_growth <- data_summary$avg_db_size[nrow(data_summary)] - data_summary$avg_db_size[1] total_growth_date_start <- data_summary$date[1] total_growth_date_end <- data_summary$date[nrow(data_summary)] # Find the day with maximum and minimum growth max_growth_day <- data_summary[which.max(data_summary$daily_growth), ] min_growth_day <- data_summary[which.min(data_summary$daily_growth), ] # Plot Database Size Over Time plot_size <- ggplot(data_summary, aes(x = date, y = avg_db_size)) + geom_line(color = "blue") + labs(title = "Database Size Over Time", x = "Date", y = "Average Database Size (MB)") + theme_minimal() # Handle NA values in daily_growth by replacing them with 0 data_summary <- data_summary %>% mutate(daily_growth = ifelse(is.na(daily_growth), 0, daily_growth)) # Plot Daily Growth Over Time plot_growth <- ggplot(data_summary, aes(x = date, y = daily_growth)) + geom_bar(stat = "identity", fill = "green", alpha = 0.7) + geom_hline(yintercept = 0, color = "red", linetype = "dashed") + labs(title = "Daily Growth of Database Size", x = "Date", y = "Daily Growth (MB)") + theme_minimal() # Save both plots and summary to the specified output PDF file pdf(file = output_file_name) # Print textual summary to the PDF text_summary <- paste( "Database Sizing Report\n", "------------------------\n", "Total Growth Over the Period: ", total_growth, "MB\n", "From: ", total_growth_date_start, " to ", total_growth_date_end, "\n", "Day with Maximum Growth: ", max_growth_day$date, " (", max_growth_day$daily_growth, "MB)\n", "Day with Minimum Growth: ", min_growth_day$date, " (", min_growth_day$daily_growth, "MB)\n" ) grid::grid.text(text_summary, x = 0.1, y = 0.9, just = "left", gp = grid::gpar(fontsize = 10)) # Print the plots to the PDF print(plot_size) print(plot_growth)
# Close the PDF device dev.off() # Return key information return(list( total_growth = total_growth, total_growth_date_start = total_growth_date_start, total_growth_date_end = total_growth_date_end, max_growth_day = max_growth_day, min_growth_day = min_growth_day, plots_saved_to = output_file_name ))}
# -----------------------------------------------------------------------------------# Main execution# -----------------------------------------------------------------------------------
# Run the process_database_sizing function with default 30 days backresult <- process_database_sizing(days_back = 30)
# Print where the plots were savedcat("Plots saved to: ", result$plots_saved_to, "\n")
# Open the PDF fileif (.Platform$OS.type == "windows") { shell.exec(result$plots_saved_to) # For Windows} else { system(paste("open", shQuote(result$plots_saved_to))) # For macOS # For Linux, use xdg-open # system(paste("xdg-open", shQuote(result$plots_saved_to)))}
# -----------------------------------------------------------------------------------# End of Script# -----------------------------------------------------------------------------------message("Database sizing analysis script execution completed successfully.")# -----------------------------------------------------------------------------------