#! /usr/bin/perl
########################################################################
# (c) Copyright IBM Corp. 2009 All rights reserved.
#
# The following sample of source code ("Sample") is owned by International
# Business Machines Corporation or one of its subsidiaries ("IBM") and is
# copyrighted and licensed, not sold. You may use, copy, modify, and
# distribute the Sample in any form without payment to IBM, for the purpose of
# assisting you in the development of your applications.
#
# The Sample code is provided to you on an "AS IS" basis, without warranty of
# any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
# IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
# MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
# not allow for the exclusion or limitation of implied warranties, so the above
# limitations or exclusions may not apply to you. IBM shall not be liable for
# any damages you suffer as a result of using, copying, modifying or
# distributing the Sample, even if IBM has been advised of the possibility of
# such damages.
#########################################################################
#
# SOURCE FILE NAME: qpwlmmig.pl
#
# TITLE: Generate a QP to WLM Migration script
#
# PURPOSE: Generates a script that will help migrate a QP environment to
# a WLM environment.
#
# DETAILS: This tool reads the DB2 Query Patroller tables and, based
# on information from those tables, will generate a script that
# will contain the DDL required to create a comparable WLM setup.
# It will also generate a second script file containing the DDL
# to undo the changes in the first script.
#
# EXECUTION: qpwlmmig.pl dbAlias userId passwd outputFile
#
# INPUTS: dbAlias - Database Alias
# userId - User Id (optional)
# passwd - Password (optional)
# outputFile - The name of the file that will be generated by this
# script.
#
# OUTPUT FILES: The file <outputFile> containing WLM DDL that will help the
# existing QP user set up their WLM environment. This
# generated file can be run using the following command:
#
# db2 -tf <outputFile>
#
# A second file <outFile>.DROP containing WLM DDL that will
# undo the changes from the <outputFile> script. This generated
# file can be run using the following command:
#
# db2 -tf <outputFile>.DROP
#
# PREREQUISITES:
# - The privileges held by the authorization ID of the user
# who runs this script must include DBADM
# - The privileges held by the authorization ID of the user
# who runs the generated script <outputFile> must include
# DBADM or WLMADM
# - The user who runs this cript must have write permission
# on the directory where the generated script <outputFile>
# is to be written.
# - This script has a dependency on the DB2WlmHist.pm file.
# If the user who runs this script first choses to copy
# this file to a different location before running it,
# he/she must also copy the DB2WlmHist.pm file to that
# same location
#
##########################################################################
select STDERR; $|=1;
select STDOUT; $|=1;
use strict;
use warnings;
use DBI;
use Data::Dumper;
# access the module for historical common functions
#----------------------------------------------------
use DB2WlmHist;
# For Format
use FileHandle;
use English;
# declare return code, statement handler, database handler and local variable
#----------------------------------------------------------------------------
my ($rc, $sth, $dbh, $valueYes, $valueNo, $typeUser, $typeGroup, $publicSubmitter, $defaultUserWorkload, $db2v95, $db2v97, $superclassName, $unmanagedSubclass, $dblevel, $originalMaxTotalQueries);
# defines for different values
#-----------------------------
$valueYes = "Y";
$valueNo = "N";
$typeUser = "U";
$typeGroup = "G";
$publicSubmitter = "PUBLIC";
$defaultUserWorkload = "DEFAULTUSERWORKLOAD";
$db2v95 = "DB2V95";
$db2v97 = "DB2V97";
$superclassName = "MAIN_SC";
$unmanagedSubclass = "UNMANAGED_SSC";
$originalMaxTotalQueries = -1;
# In QP, the max_queries_allowed can be set to -1 which
# means unlimited. The WLM thresholds don't have "unlimited"
# values so if QP has a max_queries_allowed value set to -1
# the resultant WLM threshold will be set to a really large value
# as is defined below and then that WLM threshold will be commented out.
# Also, in QP, the max_cost_allowed can also
# be set to -1. In that case, the associated work class will be
# set to a really high cost value and then that threshold will be
# commented out.
# ---------------------------------------------------------------------
my $unlimitedMaxQueries = -1;
my $unlimitedThresholdQueries = 100000;
my $unlimitedMaxCost = -1;
my $unlimitedWLMCost = 10000000000;
# Variables required for the drop script
#-----------------------------------------
# array of thresholds
#---------------------
my @thresholds;
my $thresholdIndex = 0;
my @commentedThresholds;
my $commentedThresholdIndex = 0;
# array of workloads
#-------------------
my @workloads;
my $workloadIndex = 0;
# array of service classes
#-------------------------
my @serviceClasses;
my $serviceClassIndex = 0;
# check and parse the command line arguments
# call the subroutine WLMArgChk to verify the arguments passed in.
#------------------------------------------------------------------
my ($database, $user, $password, $outFile) = QPWLMArgChk(@ARGV);
# extract the database name. It current shows up as dbi:DB2:databasename
#------------------------------------------------------------------------
my ($databaseText, $db2Version, @arr);
@arr = split(':', $database);
$databaseText= $arr[2];
print "\n Generate qptowlm.ddl script for database $databaseText .\n";
# connect to the database
#------------------------
print "\n Connecting to database...\n";
if ($password eq "-" or $user eq "-")
{
$dbh = DBI->connect($database, "", "", {AutoCommit => 0})
or die "Can't connect to $database: $DBI::errstr";
}
else
{
$dbh = DBI->connect($database, $user, $password, {AutoCommit => 0})
or die "Can't connect to $database: $DBI::errstr";
}
print "\n Connected to database $database.\n";
# Get the level of DB2 we are currently on
#-----------------------------------------
open(FILEHANDLE, "db2level |");
$dblevel=<FILEHANDLE>;
close(FILEHANDLE);
if ($dblevel =~ m/SQL0907/i)
{
$db2Version = $db2v97;
}
elsif ($dblevel =~ m/SQL0905/i)
{
$db2Version = $db2v95;
}
else
{
die "DB version is neither DB2V95 or DB2V97";
}
# Call the subroutine MainScriptGen to generate the DDL script.
#---------------------------------------------------------------
$rc = 0;
$rc = MainScriptGen();
if (!defined $rc)
{
die "\nSomething went wrong with generating the WLM DDL script\n";
}
else
{
# Print message indicating that the script was generated
#-------------------------------------------------------
print "\n $outFile", " and ",$outFile, ".DROP have been successfully generated. \n";
}
# Disconnect from the database
#-------------------------------
print "\n Disconnecting from database...\n";
$dbh->disconnect
or die $DBI::errstr;
print "\n Disconnected from database.\n";
##########################################################################
# Description : Checks and parses the command line arguments
# Input : An array containing the command line arguments that was
# passed to the calling function
# Output : Database name, user name, password, outputfile
###########################################################################
sub QPWLMArgChk
{
my $arg_c = @_; # number of arguments passed to the function
my @arg_l; # arg_l holds the values to be returned to calling function
if($arg_c > 4 or $arg_c < 4 or ($arg_c == 1 and (($_[0] eq "?") or
($_[0] eq "-?") or
($_[0] eq "/?") or
($_[0] eq "-h") or
($_[0] eq "/h") or
($_[0] eq "-help") or
($_[0] eq "/help"))) or
($arg_c == 4 and ($_[0] eq "-" or $_[3] eq "-" )))
{
die << "EOT";
Usage:
qpwlmmig.pl dbAlias userId passwd outputFile
EOT
}
# Set the database, user, password arguments
#-------------------------------------------
$arg_l[0] = "dbi:DB2:".$_[0];
$arg_l[1] = $_[1];
$arg_l[2] = $_[2];
$arg_l[3] = $_[3];
return @arg_l;
} # WLM ArgChk
#######################################################################
# Description : Main subroutine for generating the output DDL script.
# Input : None
# Output : Returns 0 on success, exits otherwise
#######################################################################
sub MainScriptGen
{
# Print out the initial header for the script.
#-------------------------------------------------------------------------
open FILE, ">$outFile" or die "\n Unable to open file $outFile\n";
print FILE "\n-----------------------------------------------------------";
print FILE "\n-- Generated script for Query Patroller (QP) to ";
print FILE "\n-- DB2 workload manager (WLM) migration ";
print FILE "\n-- ";
print FILE "\n-- Description: ";
print FILE "\n-- ";
print FILE "\n-- This generated script contains the DDL to create the ";
print FILE "\n-- WLM objects that best match your current DB2 Query ";
print FILE "\n-- Patroller setup. As much as possible, it follows ";
print FILE "\n-- the Best Practices white paper for DB2 Workload ";
print FILE "\n-- Management that can be found at the following location";
print FILE "\n-- ";
print FILE "\n-- http:\/\/download.boulder.ibm.com\/ibmdl\/pub\/software\/ ";
print FILE "\n-- dw\/dm\/db2\/bestpractices\/ ";
print FILE "\n-- DB2BP_Workload_Management_1008I.pdf ";
print FILE "\n-- ";
print FILE "\n-- Before running this script, you should become familiar";
print FILE "\n-- with the best practices outlined in this white paper. ";
print FILE "\n-- ";
print FILE "\n-- You should look through this script to confirm ";
print FILE "\n-- that the objects being created are the ones you ";
print FILE "\n-- really want and that they are named appropriately. ";
print FILE "\n-- If you decide to change the names of any of the ";
print FILE "\n-- objects in this script, you should also change the ";
print FILE "\n-- appropriate DDL in the DROP<filename> script ";
print FILE "\n-- ";
print FILE "\n-- ";
print FILE "\n-- Notes: ";
print FILE "\n-- ";
print FILE "\n-- This script does not take into consideration any ";
print FILE "\n-- DB2 governor configuration settings. If you are ";
print FILE "\n-- currently running the DB2 Governor, you need to ";
print FILE "\n-- configure similar controls using DB2 workload ";
print FILE "\n-- manager functionality separately. For more ";
print FILE "\n-- information, refer to the Best Practices white ";
print FILE "\n-- paper. ";
print FILE "\n-- ";
print FILE "\n-- This script contains DDL that will create WLM objects ";
print FILE "\n-- that are set up to collect either activity or aggregate";
print FILE "\n-- information. In order to capture this information, ";
print FILE "\n-- create WLM event monitors using the wlmevmon.ddl script";
print FILE "\n-- in the sqllib/misc directory. ";
print FILE "\n-- ";
print FILE "\n-- ";
print FILE "\n-- Before running this script: ";
print FILE "\n-- ";
print FILE "\n-- - connect to the database: ";
print FILE "\n-- db2 connect to <database> ";
print FILE "\n-- ";
print FILE "\n-- - the privileges held by the authorization ID of ";
print FILE "\n-- user who runs this script must include DBADM or ";
print FILE "\n-- WLMADM. ";
print FILE "\n-- ";
print FILE "\n-- To run this script, issue the following command: ";
print FILE "\n-- ";
print FILE "\n-- db2 -tf ", $outFile;
print FILE "\n-- ";
print FILE "\n-----------------------------------------------------------";
print FILE "\n";
# Add DDL to set the workload to the SYSDEFAULTADMWORKLOAD
# so that the script can run unaffected by any WLM DDL that
# may be run in the script itself
#-----------------------------------------------------------
print FILE "\n-- Set workload to SYSDEFAULTADMWORKLOAD ";
print FILE "\n------------------------------------------ ";
print FILE "\nSET WORKLOAD TO SYSDEFAULTADMWORKLOAD; ";
print FILE "\n ";
# Add DDL to create the two main service classes:
# - MAIN_SC - will eventually contain subclasses for
# each QP query class. Activities from
# submitter profiles whose queries were managed will
# run in those subclasses.
# It will also contain an "unmanaged" subclass where
# most of the activities bypassed or unmanaged by QP will
# initially run.
# - UNMANAGED_SSC - will be created inside the MAIN_SC and will
# have the activities that are bypassed by QP running
# in it. It will also have all of the non-DML activities
# running in it.
#----------------------------------------------------------------------
print FILE "\n-- Service superclass for managed activities ";
print FILE "\n--------------------------------------------- ";
print FILE "\nCREATE SERVICE CLASS ", $superclassName, ";";
print FILE "\n ";
print FILE "\n-- Service subclass for bypassed activities ";
print FILE "\n--------------------------------------------- ";
print FILE "\nCREATE SERVICE CLASS ", $unmanagedSubclass, " UNDER ", $superclassName, " COLLECT AGGREGATE ACTIVITY DATA; ";
print FILE "\n ";
# Workloads and thresholds for profiles
#----------------------------------------
AddWorkloadsAndThresholdsForSubmitters();
# Commented statements for QP system settings for max_total_queries
#-------------------------------------------------------------------
AddDBWASForQPSystemMaxTotalQueries();
# Workloads for QP system settings for bypassed applications
#-----------------------------------------------------------
AddWorkloadsForQPSystemBypassedApplications();
# Workloads for bypassed users
#-----------------------------
AddWorkloadsForBypassedUsers();
# Workloads for bypassed applications
#------------------------------------
AddWorkloadsForBypassedApplications();
# WLM objects for query classes
#---------------------------------
AddWlmObjectsForQueryClasses();
# Work class and work action for bypassed activities based on cost
#-----------------------------------------------------------------
AddWorkclassForBypassedDMLOnCost();
# Work class and work action for all other non-dml activities
#------------------------------------------------------------
AddWorkclassForNonDML();
# Set the workload back to automatic
#-----------------------------------
open FILE, ">>$outFile" or die "\n Unable to open file $outFile\n";
print FILE "\n\n-- Set workload to automatic ";
print FILE "\n----------------------------- ";
print FILE "\nSET WORKLOAD TO AUTOMATIC; ";
print FILE "\n ";
close(FILE);
# Generate the script file to drop the objects
#----------------------------------------------
GenerateDropScript();
$dbh->commit();
return 0;
} # MainScriptGen
#######################################################################
# Description : Routine to add CREATE WORKLOAD statements for each
# QP submitter profile. Also, add
# CREATE THRESHOLD statements when appropriate
# Input : None
# Output : Returns 0 on success, exits otherwise
#######################################################################
sub AddWorkloadsAndThresholdsForSubmitters
{
# Prepare and execute SQL statement that extracts the submitter profile
# information
#
# Note: The max_cost_allowed is a DOUBLE in QP and the max value for
# a threshold is a BIGINT. A DOUBE can be quite a bit larger
# than a BIGINT. If the max_cost_allowed is larger than the
# max value of a BIGINT, then simple set the max value that
# threshold value can have.
#-----------------------------------------------------------------------
my ($subId, $subName, $subType, $subIntercept, $subMaxQueries, $subSuspended, $subMaxCost);
my $selectStmt = "select id, name, type, intercept, max_queries_allowed, case when max_cost_allowed > 9223372036854775807 then 9223372036854775807 else cast(max_cost_allowed as BIGINT) end, suspended from db2qp.submitter_profile order by id";
my ($itemName, $itemWlName, $itemCostThName, $itemWlaThName) = " ";
my $hasBlanks = 0;
$sth = PrepareExecuteSql($dbh, $selectStmt);
# Fetch the submitter profiles and for each profile add a CREATE WORKLOAD
# statement into the script with the following rules:
# - Workload name = <submitter profile name>_<id>_<type>_WL
#
# - QP user profile type mapping
# - if the type is a user (U), the SESSION_USER connection
# attribute will be used
# - if the type is a group (G), the SESSION_USER GROUP connection
# attribute will be used
#
# - QP user profile intercept flag mapping
# - if the intercept flag is set to Y, the workload will map its
# activities to the MAIN_SC. From there, the work action set will
# eventually map its activities to the appropriate subclass based on the
# query class values.
# - if the intercept flag is set to N, the workload will map its
# activities to the UNMANAGED_SSC that resides within the MAIN_SC
# - if the submitter profile is PUBLIC and the intercept flag is set to Y,
# the default user workload will be updated to map its activities
# to the MAIN_SC. If the PUBLIC intercept flag is set to N, the
# default user workload will be altered to map its activities
# directly to the UNMANAGED_SSC.
#
# - QP user profile access suspended mapping
# - if the user profile has the access suspended flag turned on,
# the "DISALLOW DB ACCESS" attribute will be generated for the associated
# workload.
# - if the user profile has the access suspended flag turned off, the
# default value of "ALLOW DB ACCESS" attribute will be generated for the
# associated workload.
#
# - For each workload DDL that is generated, and associated GRANT USAGE
# statement will also be generated.
#
#
# Threshold Statements:
# - For each workload (including the SYSDEFAULTUSERWORKLOAD), if the
# associated QP Profile's intercept flag was set to Y, DDL statement
# creating an ESTIMATEDSQLCOST threshold will be included with its
# max value being the value of the max_cost_allowed from the QP profile.
#
# - For each submitter profile, also create a commented out line for the
# CONCURRENTWORKLOADACTIVITIES threshold.
#
# - threshold name will be generated using the following rules:
# - threshold name = <submitter profile name>_<id>_<type>_TH
# - value for threshold will be based on the max_queries_allowed value
# on the submitter profile
#
#-----------------------------------------------------------------------
open FILE, ">>$outFile" or die "\n Unable to open file $outFile\n";
print FILE "\n\n-- Workloads and thresholds for QP Submitter Profiles ";
print FILE "\n-- ";
print FILE "\n-- Note: ";
print FILE "\n-- The CONCURRENTWORKLOADACTIVITIES threshold is not ";
print FILE "\n-- a queuing threshold. If this threshold is exceeded, the ";
print FILE "\n-- activity will be stopped. For this reason, it is ";
print FILE "\n-- commented out for each workload. ";
print FILE "\n------------------------------------------------------------";
while (($subId, $subName, $subType, $subIntercept, $subMaxQueries, $subMaxCost, $subSuspended) = $sth->fetchrow())
{
my $blank = " ";
if ($subName =~ m/$blank/)
{
$hasBlanks = 1;
}
else
{
$hasBlanks = 0;
}
# Public submitter profile
#-------------------------
if ($subName eq $publicSubmitter)
{
# Generate ALTER WORKLOAD statement for default user workload
# and map to appropriate service class based on Intercept value
#---------------------------------------------------------------
if ($subIntercept eq $valueYes)
{
print FILE "\n\n-- The QP PUBLIC Profile was set to have QP ";
print FILE "\n-- intercept: ";
print FILE "\n-- Map activities from default user workload to the ";
print FILE "\n-- main service superclass so that the activities can";
print FILE "\n-- be mapped to the appropriate service subclass ";
print FILE "\n-- based on their cost. ";
print FILE "\n-----------------------------------------------------";
print FILE "\nALTER WORKLOAD SYSDEFAULTUSERWORKLOAD SERVICE CLASS ", $superclassName;
}
else
{
print FILE "\n\n-- PUBLIC Profile was set to have QP not intercept:";
print FILE "\n-- Map activities from default user workload ";
print FILE "\n-- directly to the unmanaged service subclass. ";
print FILE "\n-----------------------------------------------------";
print FILE "\n\nALTER WORKLOAD SYSDEFAULTUSERWORKLOAD SERVICE CLASS ", $unmanagedSubclass, " UNDER ", $superclassName;
}
# Generate DISALLOW attribute for profiles that have access suspended
#---------------------------------------------------------------------
if ($subSuspended eq $valueYes)
{
print FILE " DISALLOW DB ACCESS;";
}
else
{
print FILE ";";
}
$itemName = $defaultUserWorkload;
}
# All other user defined submitter profiles
#------------------------------------------
else
{
# Add the CREATE WORKLOAD statement
#----------------------------------
print FILE "\n\n-- Workload and threshold for QP ";
if ($subType eq $typeUser)
{
print FILE "user ";
}
else
{
print FILE "group ";
}
print FILE "profile ", $subName;
print FILE "\n-----------------------------------------------------";
# If the name has any blanks in it, then they are likely surrounded
# by quotes so we need to remove those quotes from the name
#--------------------------------------------------------------
$itemName = $subName."_".$subId."_".$subType;
if ($hasBlanks)
{
$subName =~ s/\'//g;
$itemName =~ s/\'//g;
$itemWlName = "\"".$itemName."\"";
$itemCostThName = "\"".$itemName."_COST_TH"."\"";
$itemWlaThName = "\"".$itemName."_WLA_TH"."\"";
}
else
{
$itemWlName = $itemName."_WL";
$itemCostThName = $itemName."_COST_TH";
$itemWlaThName = $itemName."_WLA_TH";
}
print FILE "\nCREATE WORKLOAD ";
print FILE "$itemWlName"," SESSION_USER ";
if ($subType eq $typeGroup)
{
print FILE "GROUP ";
}
# Map activities for workload to appropriate service class based on
# Intercept value
#-------------------------------------------------------------------
print FILE "( '$subName' ) SERVICE CLASS ";
if ($subIntercept eq $valueYes)
{
print FILE $superclassName, " ";
}
else
{
print FILE $unmanagedSubclass, " UNDER ", $superclassName, " ";
}
# Add the workload to the workload array
#---------------------------------------
$workloads[$workloadIndex] = $itemWlName;
$workloadIndex++;
# Generate DISALLOW attribute for profiles that have access suspended
#---------------------------------------------------------------------
if ($subSuspended eq $valueYes)
{
print FILE " DISALLOW DB ACCESS ";
}
# Create the workloads so that those representing the USER profiles
# are ahead in the evaluation order than those representing GROUP
# profiles
#-------------------------------------------------------------------
if ($subType ne $typeGroup)
{
print FILE "POSITION AT 1;"
}
else
{
print FILE "POSITION LAST;"
}
# Add the GRANT USAGE on the workload to the appropriate group or user
#---------------------------------------------------------------------
if ($subName ne $publicSubmitter)
{
print FILE "\n\nGRANT USAGE ON WORKLOAD ", $itemWlName, " TO ";
if ($subType eq $typeGroup)
{
print FILE "GROUP ";
}
else
{
print FILE "USER ";
}
if ($hasBlanks)
{
$subName =~ tr/a-z/A-Z/;
print FILE "\"",$subName,"\";";
}
else
{
print FILE $subName, ";";
}
}
}
if ($itemName eq $defaultUserWorkload)
{
$itemWlaThName = $itemName."_WLA_TH";
$itemWlName = "SYS".$defaultUserWorkload;
}
# If the version is V97, then also create an estimated cost threshold
# on the workload.
# Note: this threshold cannot be created on a workload in V95
#--------------------------------------------------------------------
if ($db2Version eq $db2v97)
{
if ($itemName eq $defaultUserWorkload)
{
$itemCostThName = $itemName."_COST_TH";
}
# This was set to unlimited in QP so it will be set to a really
# high value in WLM and then commented out because it is not
# a useful threshold.
#-----------------------------------------------------------------
if ($subMaxCost eq $unlimitedMaxCost)
{
# Add the threshold name to the commented threshold array
#----------------------------------------------------------
$commentedThresholds[$commentedThresholdIndex] = $itemCostThName;
$commentedThresholdIndex++;
$subMaxCost = $unlimitedWLMCost;
print FILE "\n\n-- QP value was -1 so set threshold to a high value";
print FILE "\n-- and then comment out the threshold ";
print FILE "\n-----------------------------------------------------";
print FILE "\n--";
}
else
{
# Add the threshold name to the threshold array
#----------------------------------------------
$thresholds[$thresholdIndex] = $itemCostThName;
$thresholdIndex++;
print FILE "\n\n";
}
print FILE "CREATE THRESHOLD ";
print FILE "$itemCostThName"," FOR WORKLOAD ", "$itemWlName";
print FILE " ACTIVITIES ENFORCEMENT DATABASE WHEN ESTIMATEDSQLCOST > $subMaxCost COLLECT ACTIVITY DATA STOP EXECUTION;";
}
# Now add the commented out CREATE THRESHOLD statement for
# CONCURRENTWORKLOADACTIVITIES THRESHOLD
#
# Note: In QP, the max_queries_allowed can be set to -1 which
# means unlimited. The CONCURRENTWORKLOADACTIVITIES threshold
# doesn't have an "unlimited" value so set it to a really
# large value.
#--------------------------------------------------------------------
if ($subMaxQueries eq $unlimitedMaxQueries)
{
$subMaxQueries = $unlimitedThresholdQueries;
}
print FILE "\n\n--CREATE THRESHOLD ";
print FILE "$itemWlaThName"," FOR WORKLOAD $itemWlName"," ACTIVITIES ENFORCEMENT WORKLOAD OCCURRENCE WHEN CONCURRENTWORKLOADACTIVITIES > $subMaxQueries COLLECT ACTIVITY DATA STOP EXECUTION;\n";
# Add the threshold name to the commented threshold array
#----------------------------------------------------------
$commentedThresholds[$commentedThresholdIndex] = $itemWlaThName;
$commentedThresholdIndex++;
}
$sth->finish;
close(FILE);
return 0;
} # AddWorkloadsAndThresholdsForSubmitters
#######################################################################
# Description : Routine to add CREATE WORKLOAD statements for each
# application listed to be bypassed in the QP system
# settings
# Input : None
# Output : Returns 0 on success, exits otherwise
#######################################################################
sub AddWorkloadsForQPSystemBypassedApplications
{
# prepare and execute SQL statement that extracts the QP System
# information
#----------------------------------------------------------------------
my ($sysInterceptApps, $sysExcludeApps);
my $selectStmt = "select intercept_application, exclude_applications from db2qp.qp_system";
my $interceptAllBut = "E";
my $itemName = " ";
my (@arr, $numApps, $i, $element, @arr2, $appName);
$sth = PrepareExecuteSql($dbh, $selectStmt);
# Fetch the QP system values. If the intercept application value is
# E (intercept all but the applications in the EXCLUDE_APPLICATIONS
# column), then generate a CREATE WORKLOAD command for each of the
# applications listed and use the APPLNAME connection attribute.
# Map those activities to the UNMANAGED_SSC service subclass.
#
# For each workload DDL that is generated, an associated GRANT USAGE
# statement to PUBLIC will also be generated.
#
#-----------------------------------------------------------------------
open FILE, ">>$outFile" or die "\n Unable to open file $outFile\n";
print FILE "\n-- Workloads for each bypassed application from the ";
print FILE "\n-- QP system settings ";
print FILE "\n------------------------------------------------------";
while (($sysInterceptApps, $sysExcludeApps) = $sth->fetchrow())
{
if ($sysExcludeApps and $sysInterceptApps eq $interceptAllBut)
{
@arr = split(',', $sysExcludeApps);
$numApps = scalar @arr;
for ($i = 0; $i < $numApps; $i++)
{
$element = $arr[$i];
# remove leading and trailing blanks
#------------------------------------
$element =~ s/\s+$//;
$element =~ s/^\s+//;
# Remove the . in the appname. For example, test.exe will become
# testexe
#-----------------------------------------------------------------
@arr2 = split('\.', $element);
if ($arr2[1])
{
$appName = $arr2[0].$arr2[1];
}
else
{
$appName = $arr2[0];
}
# Generate the CREATE WORKLOAD statement
#----------------------------------------
print FILE "\nCREATE WORKLOAD BYPASSAPPS_", $appName, "_WL APPLNAME ('", $element, "') SERVICE CLASS ", $unmanagedSubclass, " UNDER " , $superclassName, " POSITION AT 1;";
# Generate the GRANT USAGE statement
#-----------------------------------
print FILE "\n\nGRANT USAGE ON WORKLOAD BYPASSAPPS_", $appName, "_WL TO PUBLIC;\n\n";
# Add the workload to the workload array
#---------------------------------------
$workloads[$workloadIndex] = "BYPASSAPPS_".$appName."_WL";
$workloadIndex++;
}
}
}
$sth->finish;
close(FILE);
return 0;
} # AddWorkloadsForQPSystemBypassedApplications
#######################################################################
# Description : Routine to add CREATE WORK CLASS SET, CREATE WORK
# ACTION SET at the database level that will contain
# a concurrency threshold to be applied against DML that
# reflects the value in the max_total_queries value in
# the system settings.
# Note: this code will be commented out since the value
# will likely need to be changed due to the nature of QP
# bypassing activities from other applications or users
# Input : None
# Output : Returns 0 on success, exits otherwise
#######################################################################
sub AddDBWASForQPSystemMaxTotalQueries
{
# prepare and execute SQL statement that extracts the QP System
# information
#----------------------------------------------------------------------
my ($sysMaxTotalQueries);
my $selectStmt = "select max_total_queries from db2qp.qp_system";
$sth = PrepareExecuteSql($dbh, $selectStmt);
open FILE, ">>$outFile" or die "\n Unable to open file $outFile\n";
($sysMaxTotalQueries) = $sth->fetchrow();
# Hang on to the originalTotalMaxQueries so that we know whether
# we will need to generate drop code for this
#---------------------------------------------------------------
$originalMaxTotalQueries = $sysMaxTotalQueries;
# If the max_total_queries is set to a value and not unlimited
#--------------------------------------------------------------
if ($sysMaxTotalQueries > -1)
{
print FILE "\n-- Work class set and work action set to control the ";
print FILE "\n-- number of activities that can run at the database ";
print FILE "\n-- level. This represents the max_total_queries in ";
print FILE "\n-- qp_system. ";
print FILE "\n-- ";
print FILE "\n-- Note: ";
print FILE "\n-- Because there could be other activies bypassed ";
print FILE "\n-- asside from those with a larger estimated cost ";
print FILE "\n-- than what is specified in the DB2_QP_BYPASS_COST ";
print FILE "\n-- registry variable, the value for the threshold ";
print FILE "\n-- in the work action set will likely have to be ";
print FILE "\n-- changed. For this reason, the database work class ";
print FILE "\n-- set and database work action set is commented out ";
print FILE "\n------------------------------------------------------";
# Add in commented out CREATE WORK CLASS SET statement.
# This work class set will contain a work class representing DML
# and if there are queries being bypassed using the DB2_QP_BYPASS_COST
# registry variable, it will be further qualified by an timerone
# value
#---------------------------------------------------------------------
print FILE "\n\n-- Database work class set ";
print FILE "\n----------------------------- ";
print FILE "\n--CREATE WORK CLASS SET DATABASE_WCS ";
# Get the bypass cost value if it is specified
#------------------------------------------------
my $bypassCost;
open(FILEHANDLE, "db2set DB2_QP_BYPASS_COST |");
$bypassCost=<FILEHANDLE>;
close(FILEHANDLE);
# remove leading and trailing blanks
#------------------------------------
$bypassCost =~ s/\s+$//;
$bypassCost =~ s/^\s+//;
# Add in the work class representing DML
#---------------------------------------
print FILE "(WORK CLASS DML_WC WORK TYPE DML ";
# If there is a bypasscost value, then specify a timeron value in the
# work class
#----------------------------------------------------------------------
if ($bypassCost)
{
$bypassCost++;
# Add statement to add work class
#-----------------------------------------------------------------
print FILE "FOR TIMERONCOST FROM $bypassCost ";
}
print FILE "POSITION LAST);";
# Add in CREATE WORK ACTION SET statement that will be
# applied to the database.
# This work action set will contain a concurrency work action
# threshold representing the qp_system max_total_queries
#--------------------------------------------------------------
print FILE "\n\n-- Work action set for the database";
print FILE "\n-------------------------------------";
print FILE "\n--CREATE WORK ACTION SET DATABASE_WAS FOR DATABASE USING WORK CLASS SET DATABASE_WCS (WORK ACTION MAXQUERIES_WA ON WORK CLASS DML_WC WHEN CONCURRENTDBCOORDACTIVITIES > ", $sysMaxTotalQueries, " AND QUEUEDACTIVITIES UNBOUNDED COLLECT ACTIVITY DATA CONTINUE);\n";
}
$sth->finish;
close(FILE);
return 0;
} # AddWorkloadsForQPSystemBypassedApplications
#######################################################################
# Description : Routine to add CREATE WORKLOAD statement for the
# DB2_QP_BYPASS_USERS registry variable listing every
# user specified in the registry variable in the
# SESSION_USER workload attribute.
# Input : None
# Output : Returns 0 on success, exits otherwise
#######################################################################
sub AddWorkloadsForBypassedUsers
{
my $bypassUsers;
my (@arr, $numUsers, $i, $element);
open(FILEHANDLE, "db2set DB2_QP_BYPASS_USERS |");
$bypassUsers=<FILEHANDLE>;
close(FILEHANDLE);
open FILE, ">>$outFile" or die "\n Unable to open file $outFile\n";
# remove leading and trailing blanks
#------------------------------------
$bypassUsers =~ s/\s+$//;
$bypassUsers =~ s/^\s+//;
if ($bypassUsers)
{
@arr = split(',', $bypassUsers);
$numUsers = scalar @arr;
# If the registry variable was specified, then generate the CREATE
# WORKLOAD and GRANT USAGE statements
#-----------------------------------------------------------------
if ($numUsers > 0)
{
# Generate the CREATE WORKLOAD statement
#----------------------------------------
print FILE "\n\n-- WLM Object for the DB2_QP_BYPASS_USERS registry ";
print FILE "\n-- variable ";
print FILE "\n-- ";
print FILE "\n-- Add a workload for all the users specified in the ";
print FILE "\n-- registry variable and have those activities mapped ";
print FILE "\n-- to the unmanaged service subclass. ";
print FILE "\n------------------------------------------------------ ";
print FILE "\nCREATE WORKLOAD BYPASSUSERS_WL SESSION_USER (";
for ($i = 0; $i < $numUsers; $i++)
{
$element = $arr[$i];
# remove leading and trailing blanks
#------------------------------------
$element =~ s/\s+$//;
$element =~ s/^\s+//;
print FILE "'$element'";
if ($i < ($numUsers - 1))
{
print FILE ", ";
} else {
print FILE " ) ";
}
}
print FILE "SERVICE CLASS ", $unmanagedSubclass, " UNDER ", $superclassName, " POSITION AT 1;";
# Generate the GRANT USAGE statement
#-----------------------------------
print FILE "\n\nGRANT USAGE ON WORKLOAD BYPASSUSERS_WL TO PUBLIC;\n\n";
}
# Add the workload to the workload array
#---------------------------------------
$workloads[$workloadIndex] = "BYPASSUSERS_WL";
$workloadIndex++;
}
close(FILE);
return 0;
} # AddWorkloadsForBypassedUsers
#######################################################################
# Description : Routine to add CREATE WORKLOAD statement for the
# DB2_QP_BYPASS_APPLICATIONS registry variable listing
# every sepcified application in the registry variable
# in the APPLNAME workload attribute.
# Input : None
# Output : Returns 0 on success, exits otherwise
#######################################################################
sub AddWorkloadsForBypassedApplications
{
my $bypassApps;
my (@arr, $numApps, $i, $element);
open(FILEHANDLE, "db2set DB2_QP_BYPASS_APPLICATIONS |");
$bypassApps=<FILEHANDLE>;
close(FILEHANDLE);
open FILE, ">>$outFile" or die "\n Unable to open file $outFile\n";
# remove leading and trailing blanks
#------------------------------------
$bypassApps =~ s/\s+$//;
$bypassApps =~ s/^\s+//;
if ($bypassApps)
{
@arr = split(',', $bypassApps);
$numApps = scalar @arr;
if ($numApps > 0)
{
# Generate the CREATE WORKLOAD statement
#----------------------------------------
print FILE "\n\n-- WLM Object for the DB2_QP_BYPASS_APPLICATIONS ";
print FILE "\n-- registry variable ";
print FILE "\n-- ";
print FILE "\n-- Add a workload for all the applications specified ";
print FILE "\n-- in the registry variable and have those activities ";
print FILE "\n-- mapped to the unmanaged service subclass. ";
print FILE "\n------------------------------------------------------ ";
print FILE "\nCREATE WORKLOAD BYPASSAPPS_WL APPLNAME (";
for ($i = 0; $i < $numApps; $i++)
{
$element = $arr[$i];
# remove leading and trailing blanks
#------------------------------------
$element =~ s/\s+$//;
$element =~ s/^\s+//;
print FILE "'$element'";
if ($i < ($numApps - 1))
{
print FILE ", ";
} else {
print FILE " ) ";
}
}
print FILE "SERVICE CLASS ", $unmanagedSubclass, " UNDER ", $superclassName, " POSITION AT 1;";
# Generate the GRANT USAGE statement
#-----------------------------------
print FILE "\n\nGRANT USAGE ON WORKLOAD BYPASSAPPS_WL TO PUBLIC;";
# Add the workload to the workload array
#---------------------------------------
$workloads[$workloadIndex] = "BYPASSAPPS_WL";
$workloadIndex++;
}
}
close(FILE);
return 0;
} # AddWorkloadsForBypassedApplications
#######################################################################
# Description : Routine to generate ALTER statements to add a work
# class and a work action to bypass activities that
# that have an estimated cost that is less than or equal to
# that which is specified in the DB2_QP_BYPASS_COST
# registry variable.
# Input : None
# Output : Returns 0 on success, exits otherwise
#######################################################################
sub AddWorkclassForBypassedDMLOnCost
{
my $bypassCost;
open(FILEHANDLE, "db2set DB2_QP_BYPASS_COST |");
$bypassCost=<FILEHANDLE>;
close(FILEHANDLE);
# remove leading and trailing blanks
#------------------------------------
$bypassCost =~ s/\s+$//;
$bypassCost =~ s/^\s+//;
if ($bypassCost)
{
open FILE, ">>$outFile" or die "\n Unable to open file $outFile\n";
print FILE "\n\n-- WLM objects for the DB2_QP_BYPASS_COST registry variable ";
print FILE "\n-- ";
print FILE "\n-- Add a work class and a work action to map activities with ";
print FILE "\n-- an estimated cost equal to or less than that specified ";
print FILE "\n-- in the DB2_QP_BYPASS_COST registry variable to the ";
print FILE "\n-- unmanaged service subclass. ";
print FILE "\n------------------------------------------------------------- ";
# Add the work class reflecting the DML estimated cost
#------------------------------------------------------
print FILE "\nALTER WORK CLASS SET GLOBAL_WCS ADD WORK CLASS BYPASSCOST_WC WORK TYPE DML FOR TIMERONCOST FROM 0 to ", $bypassCost, " POSITION AT 1;";
# Add the work action to map the activities to the unmanaged service subclass
#--------------------------------------------------------------------------
print FILE "\nALTER WORK ACTION SET SC_WAS ADD WORK ACTION MAPBYPASSCOST_WA ON WORK CLASS BYPASSCOST_WC MAP ACTIVITY TO ", $unmanagedSubclass, ";";
}
close(FILE);
} # AddWorkclassForBypassedDMLOnCost
#######################################################################
# Description : Routine to generate ALTER statements to add a work
# class and a work action to represent all non_DML
# activities and have them mapped to the UNMANAGED_SSC.
# Input : None
# Output : Returns 0 on success, exits otherwise
#######################################################################
sub AddWorkclassForNonDML
{
open FILE, ">>$outFile" or die "\n Unable to open file $outFile\n";
print FILE "\n\n-- Add work class and work action to map non-DML activities ";
print FILE "\n-- to the unmanaged service subclass. ";
print FILE "\n--------------------------------------------------------------- ";
print FILE "\nALTER WORK CLASS SET GLOBAL_WCS ADD WORK CLASS NONDML_WC WORK TYPE ALL POSITION LAST;";
print FILE "\n\nALTER WORK ACTION SET SC_WAS ADD WORK ACTION MAPNONDML_WA on WORK CLASS NONDML_WC MAP ACTIVITY TO ", $unmanagedSubclass, ";";
close(FILE);
return 0;
} # AddWorkclassForNonDML
#######################################################################
# Description : Routine to add service classes, thresholds,
# work classes and work actions statements for each
# QP query class.
# Input : None
# Output : Returns 0 on success, exits otherwise
#######################################################################
sub AddWlmObjectsForQueryClasses
{
my ($id, $maxQueries, $maxCost, $description, $i, $itemName);
my $fromValueMaxCost = 0;
my $concThAdded = 0;
my $selectStmt = "select id, max_queries, max_cost, description from db2qp.query_class order by max_cost";
open FILE, ">>$outFile" or die "\n Unable to open file $outFile\n";
# Add in CREATE WORK CLASS SET statement.
# This work class set will eventually contain one work class for
# every QP query class.
#---------------------------------------------------------------
print FILE "\n\n-- Global work class set ";
print FILE "\n-------------------------- ";
print FILE "\nCREATE WORK CLASS SET GLOBAL_WCS;";
# Add in CREATE WORK ACTION SET statement that will be
# applied to the MAIN_SC service superclass.
# This work action set will eventually contain one work action
# for every QP query class
#--------------------------------------------------------------
print FILE "\n\n-- Work action set for the main service superclass";
print FILE "\n----------------------------------------------------";
print FILE "\nCREATE WORK ACTION SET SC_WAS FOR SERVICE CLASS ", $superclassName, " USING WORK CLASS SET GLOBAL_WCS;";
# For every QP query class add in the following:
# - add a work class to the work class set reflecting the DML
# cost that was specified in the query class
# - create service subclass in the MAIN_SC super class
# - add a mapping work action to the work action set to map
# activities to appropriate service subclass.
# - create a concurrency threshold using the max_value from the
# query class and apply it to the service subclass
#-----------------------------------------------------------------
$sth = PrepareExecuteSql($dbh, $selectStmt);
$i = 1;
print FILE "\n\n-- WLM objects for each QP query class ";
print FILE "\n-- - Add a work class containing the estimated cost from ";
print FILE "\n-- the associated QP query class ";
print FILE "\n-- - Add a service subclass where the DML statements that ";
print FILE "\n-- fall within an estimated cost range will run ";
print FILE "\n-- - Add a work action to map the activities that fall ";
print FILE "\n-- within the estimated cost range to the service class ";
print FILE "\n-- above ";
print FILE "\n-- - Add an activity concurrency threshold with the value ";
print FILE "\n-- that is specified in the associated QP query class ";
print FILE "\n----------------------------------------------------------- ";
while (($id, $maxQueries, $maxCost, $description) = $sth->fetchrow())
{
$itemName = "QUERYCLASS".$i;
# Add statement to add work class representing DML that falls within
# the estimated cost from the QP query class
#--------------------------------------------------------------------
print FILE "\n\n-- WLM objects for query class ID ", $id;
if ($description)
{
print FILE " (", $description, ")";
}
print FILE "\n--------------------------------------------------------------";
# Add statement to add work class
#-----------------------------------------------------------------
print FILE "\nALTER WORK CLASS SET GLOBAL_WCS ADD WORK CLASS ",$itemName,"_WC WORK TYPE DML FOR TIMERONCOST FROM $fromValueMaxCost TO $maxCost POSITION LAST;";
$fromValueMaxCost = $maxCost;
# Add statement to create service subclass where activities with
# estimated cost from query class will run
#----------------------------------------------------------------
print FILE "\n\nCREATE SERVICE CLASS ",$itemName,"_SSC UNDER ", $superclassName, " COLLECT AGGREGATE ACTIVITY DATA;";
# Add the threshold name to the threshold array
#----------------------------------------------
$serviceClasses[$serviceClassIndex] = $itemName."_SSC";
$serviceClassIndex++;
# Add statement to add work action that will map the activities
# from the associated work class to the appropriate service subclass
#----------------------------------------------------------------------
print FILE "\n\nALTER WORK ACTION SET SC_WAS ADD WORK ACTION ", $itemName,"_WA ON WORK CLASS ", $itemName, "_WC MAP ACTIVITY TO ", $itemName, "_SSC;";
# Add statement to create a concurrency threshold with a max value
# from the QP query class
#
# Note: In QP, the max_queries can be set to -1 which means unlimited.
# If unlimited is specified in QP, because there is no "unlimited"
# value for the threshold, set the value to a really high number
# and the concurrency threshold will be commented out.
#-------------------------------------------------------------------------
$concThAdded = 0;
if ($maxQueries eq $unlimitedMaxQueries)
{
print FILE "\n\n-- QP value was -1 so set threshold to a high value";
print FILE "\n-- and then comment out the threshold ";
print FILE "\n-----------------------------------------------------";
$maxQueries = $unlimitedThresholdQueries;
print FILE "\n--";
}
else
{
print FILE "\n\n";
$concThAdded = 1;
}
print FILE "CREATE THRESHOLD ", $itemName, "_CONC_TH FOR SERVICE CLASS ",$itemName, "_SSC UNDER ", $superclassName, " ACTIVITIES ENFORCEMENT DATABASE WHEN CONCURRENTDBCOORDACTIVITIES > ", $maxQueries, " AND QUEUEDACTIVITIES UNBOUNDED COLLECT ACTIVITY DATA CONTINUE;\n";
# Add the threshold name to the correct threshold array
#-------------------------------------------------------
if ($concThAdded)
{
$thresholds[$thresholdIndex] = $itemName."_CONC_TH";
$thresholdIndex++;
}
else
{
$commentedThresholds[$commentedThresholdIndex] = $itemName."_CONC_TH";
$commentedThresholdIndex++;
}
$i++;
}
$sth->finish;
close(FILE);
return 0;
} # AddWlmObjectsForQueryClasses
#######################################################################
# Description : Routine to get values for SYSDEFAULTWORKLOAD
# so that during the DROP routine, they can be set back
# to their original values
# Input : None
# Output : Returns the following values for the SYSDEFAULTWORKLOAD:
# - enabled
# - allowaccess
# - serviceclassname
# - parentserviceclassname
#######################################################################
sub GetDefaultUserWorkloadInfo
{
my ($i, $enabled, $allowAccess, $serviceClassName, $parentServiceClassName);
my @arg_l; # arg_l will hold the values to be returned to calling function
my $selectStmt = "select enabled, allowaccess, serviceclassname, parentserviceclassname from syscat.workloads where workloadname = 'SYSDEFAULTUSERWORKLOAD'";
# execute the statement and get the values
#-----------------------------------------------------------------
$sth = PrepareExecuteSql($dbh, $selectStmt);
$i = 1;
while (($enabled, $allowAccess, $serviceClassName, $parentServiceClassName) = $sth->fetchrow())
{
$arg_l[0] = $enabled;
$arg_l[1] = $allowAccess;
$arg_l[2] = $serviceClassName;
$arg_l[3] = $parentServiceClassName;
}
$sth->finish;
return @arg_l;
} # GetDefaultUserWorkloadInfo
#######################################################################
# Description : Routine to generate the script to drop all of the
# WLM objects.
# Input : None
# Output : Returns 0 on success, exits otherwise
#######################################################################
sub GenerateDropScript
{
my $dropOutFile = $outFile.".DROP";
my $i = 0;
open FILE, ">$dropOutFile" or die "\n Unable to open file $dropOutFile\n";
print FILE "\n-----------------------------------------------------------";
print FILE "\n-- Generated script for dropping the WLM objects from ";
print FILE "\n-- the generated QP to WLM migration script. ";
print FILE "\n-- ";
print FILE "\n-- Description: ";
print FILE "\n-- ";
print FILE "\n-- This generated script contains the DDL to drop the ";
print FILE "\n-- WLM objects that were part of the QP to WLM generated ";
print FILE "\n-- migration script. ";
print FILE "\n-- ";
print FILE "\n-- Before running this script: ";
print FILE "\n-- ";
print FILE "\n-- - connect to the database: ";
print FILE "\n-- db2 connect to <database> ";
print FILE "\n-- ";
print FILE "\n-- - the privileges held by the authorization ID of ";
print FILE "\n-- user who runs this script must include DBADM or ";
print FILE "\n-- WLMADM. ";
print FILE "\n-- ";
print FILE "\n-- To run this script, issue the following command: ";
print FILE "\n-- ";
print FILE "\n-- db2 -tf DROP", $outFile;
print FILE "\n-- ";
print FILE "\n-----------------------------------------------------------";
print FILE "\n";
# Add DDL to set the workload to the SYSDEFAULTADMWORKLOAD
# so that the script can run unaffected by any WLM DDL that
# may be run in the script itself
#-----------------------------------------------------------
print FILE "\n-- Set workload to SYSDEFAULTADMWORKLOAD ";
print FILE "\n------------------------------------------ ";
print FILE "\nSET WORKLOAD TO SYSDEFAULTADMWORKLOAD; ";
print FILE "\n ";
print FILE "\n-- Disable all the objects ";
print FILE "\n---------------------------------";
print FILE "\n\n-- Disable all the thresholds ";
print FILE "\n------------------------------";
for ($i = 0; $i < $thresholdIndex; $i++)
{
print FILE "\n ALTER THRESHOLD ", $thresholds[$i], " DISABLE;";
}
for ($i = 0; $i < $commentedThresholdIndex; $i++)
{
print FILE "\n-- ALTER THRESHOLD ", $commentedThresholds[$i], " DISABLE;";
}
print FILE "\n\n-- Disable the work action set ";
print FILE "\n-------------------------------";
print FILE "\nALTER WORK ACTION SET SC_WAS DISABLE;";
if ($originalMaxTotalQueries > -1)
{
print FILE "\n\n-- Disable the database work action set ";
print FILE "\n------------------------------------------";
print FILE "\n--ALTER WORK ACTION SET DATABASE_WAS ALTER WORK ACTION MAXQUERIES_WA DISABLE;";
print FILE "\n--ALTER WORK ACTION SET DATABASE_WAS DISABLE;";
}
print FILE "\n\n-- Disable all of the workloads ";
print FILE "\n---------------------------------";
for ($i = 0; $i < $workloadIndex; $i++)
{
print FILE "\n ALTER WORKLOAD ", $workloads[$i], " DISABLE;";
}
# Get the default user workload's original values and alter it so
# that it goes back to the way it was when this script was first
# run
#---------------------------------------------------------------------
my ($enabled, $allowAccess, $serviceClassName, $parentServiceClassName) = GetDefaultUserWorkloadInfo();
print FILE "\n\n-- Alter default user workload back to its original ";
print FILE "\n-- values ";
print FILE "\n--------------------------------------------------------";
print FILE "\n ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD ";
if ($allowAccess)
{
print FILE "ALLOW DB ACCESS;";
}
else
{
print FILE "DISALLOW DB ACCESS;";
}
print FILE "\n ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD ";
if (!$parentServiceClassName)
{
print FILE "SERVICE CLASS ", $serviceClassName, ";"
}
elsif ($serviceClassName eq "SYSDEFAULTSUBCLASS")
{
print FILE "SERVICE CLASS ", $parentServiceClassName, ";"
}
else
{
print FILE "SERVICE CLASS ", $serviceClassName, " UNDER ", $parentServiceClassName,";"
}
print FILE "\n\n-- Disable the service classes ";
print FILE "\n-------------------------------";
print FILE "\n ALTER SERVICE CLASS ", $unmanagedSubclass," UNDER ", $superclassName, " DISABLE;";
for ($i = 0; $i < $serviceClassIndex; $i++)
{
print FILE "\n ALTER SERVICE CLASS ", $serviceClasses[$i], " UNDER " , $superclassName, " DISABLE;";
}
print FILE "\n ALTER SERVICE CLASS ", $superclassName, " DISABLE;";
print FILE "\n\n-- Next, drop all the objects\n ";
print FILE "\n---------------------------------";
print FILE "\n\n-- Drop all the thresholds ";
print FILE "\n---------------------------";
for ($i = 0; $i < $thresholdIndex; $i++)
{
print FILE "\n DROP THRESHOLD ", $thresholds[$i],";";
}
for ($i = 0; $i < $commentedThresholdIndex; $i++)
{
print FILE "\n-- DROP THRESHOLD ", $commentedThresholds[$i],";";
}
print FILE "\n\n-- Drop the work action set ";
print FILE "\n----------------------------";
print FILE "\nDROP WORK ACTION SET SC_WAS;\n";
print FILE "\n\n-- Drop the work class set ";
print FILE "\n----------------------------";
print FILE "\nDROP WORK CLASS SET GLOBAL_WCS;\n";
if ($originalMaxTotalQueries > -1)
{
print FILE "\n\n-- Drop the database work action set ";
print FILE "\n---------------------------------------";
print FILE "\n--DROP WORK ACTION SET DATABASE_WAS;\n";
print FILE "\n\n-- Drop the work class set ";
print FILE "\n----------------------------";
print FILE "\n--DROP WORK CLASS SET DATABASE_WCS;\n";
}
print FILE "\n\n-- Drop all of the workloads ";
print FILE "\n------------------------------";
for ($i = 0; $i < $workloadIndex; $i++)
{
print FILE "\n DROP WORKLOAD ", $workloads[$i], ";";
}
print FILE "\n\n-- Drop all of the service classes ";
print FILE "\n-----------------------------------";
print FILE "\n DROP SERVICE CLASS ", $unmanagedSubclass," UNDER ", $superclassName, ";";
for ($i = 0; $i < $serviceClassIndex; $i++)
{
print FILE "\n DROP SERVICE CLASS ", $serviceClasses[$i], " UNDER " , $superclassName, ";";
}
print FILE "\n DROP SERVICE CLASS ", $superclassName, ";";
# Set the workload back to automatic
#-----------------------------------
print FILE "\n\n-- Set workload to automatic ";
print FILE "\n----------------------------- ";
print FILE "\nSET WORKLOAD TO AUTOMATIC; ";
print FILE "\n ";
close(FILE);
return 0;
} # GenerateDropScript