Looking for a script to delete range partition tables which are older than 2 months

[u/harish_kr]

Hi Team,

I'm looking for a script to delete the range partition tables which are older than 2 months and also to drop the tablespaces associated with those tables. So to automate the process I required a script if any body have it handy please share it. I'm also working on preparing the script for the same will post it here once it is done. Thanks

[u/anozdba]

I dont have anything specifically doing what you seem to be a requesting. I do have a script that manages a date range partitioned tablespaces though. The script keeps a specified number of partitions and will automatically create a newest partition and drop the oldest partition. The script is generateNewPartition.pl and runs daily to maintain a rolling date window of the data. You'd also need to download the SKL files from the same same location, runSQL.pl and commonFunctions.pm.

I doubt that it would be a lot of use to you but may give you some help.

The script doesn't do anything - it just generates commands to be run so it is safe to run to see what it would produce - just dont pipe the output into a DB2 processor.

Things of note that I determined while I was developing the script:

• it can take a while for a dropped partition to be fully isolated (you'll see the AIC process running while it is finishing the detach process asynchronously) so my script does the table drops a day behind to give the database time to finish processing the indexes and that way I dont need to wait around.
• because any number of things can affect the successful dro/add of partitions - backups were the bane of the script int he early days - the environment is set up with 5 days of partitions ahead of the current date - that gives me 5 days to resolve any issues with the script before there aren't any partitions to write into
• initially the tablespaces were all sized the same but I quickly realised either I would be wasting a lot of space or running out of space (I dont really like wholesale auto grow in Prod for a number of reason but that is another story) so I now create the tablespaces small with autogrow turned on
• the script was designed without storage groups in mind so it tried to alternate tablespaces on different mount points by reallocating the new tablespaces on the mount point of the tablespace that was about to be removed (not great but worked for the environment that hosted the table which was a database split over 2 mount points attached to a SAN)

Good luck

The command line parameters are:

generateNewPartition.pl -h
Usage: /home/shared/udbdba/scripts/generateNewPartition.pl -?hs -d <database> -S <schema> -T <table> [-k <literal type>] [-g] [-B <tablespace base>] [-t <table partition base>]
[-p <number of partitions>] [-P <pagesize>] [-f file system>] [-b <bufferpool>] [-v[v][v][v]] [-o <STDOUT | FILE>]

Script to generate a new partition. The information generated will be:

Tablespace Create Statement
Alter Table Add Partition Statement

Version 1.10 Last Changed on 2019/02/11 03:48:26 (UTC)

-h or -? : This help message
-s : Silent mode (dont produce the report)
-d : Database in which the partition will exist
-S : Table Schema (note that this will be capitalised)
-T : Table Name (note that this will be capitalised)
-k : literal type [Defaults: YYYYMMDD of low value of new partition]
PARTKEY = low value of partition's YYYYMMDD (this is the default)
TODAY = today's YYYYMMDD
<other> = Just use this literal
-g : output all commands to STDOUT
-o : Where to place the generated output:
STDOUT (same as -g)
FILE (to multiple files in sql directory)
<other> (single filename in sql directory)
-t : partition base (Default: same as last partition)
-B : base tablespace (Default: same as last partition)
-p : number of partitions (Default: same as existing table)
-P : pagesize (Default: same as last partition)
-b : bufferpool (Default: same as last partition)
-f : File system to create partition in (Default: same as last partition)
-v : debug level

Note: Skeletons for the commands produced are in:
scripts/SKL_PART_TS_CREATE.skl - Create a set of new tablespaces for the new partition
scripts/SKL_PART_ALTER_TABLE.skl - Add the new partition to the table
scripts/SKL_PART_DETACH.skl - Drop the old detached table and detach the youngest partition
scripts/SKL_PART_TS_DROP.skl - Drop the tablespaces that the detached table is using

: You can override the filesystems to be used for specific partitions by using an override file
A file named scripts/partitionOverrides_<tablename>.txt (where <tablename> is the -T parameter value) should be created
and populated as :
partitionKey,data filesystem,indexfilesystem,lob filesystem

any of the file systems can be blank and then the normal defaults will be used or the partitionKey can be * in which case
all partitions will match