sp_dbtidy
- Matthew Wren
On This Page:
This utility tidies data from the ServiceOptimizer database that is deemed to have expired. The list of tables that are tidied is given in Database Tidied Tables.
Warning
It should be noted that sp_dbtidy creates indexes during its processing and for Oracle it creates these indexes in an ‘Online’ manner. This means that other operations on the table being indexed are not blocked during the index creation. The downside to this being that extra rollback segments space may be needed.
Performance Tip
In some cases it may take sp_dbtidy too long to complete the tidying taskwithin the time the business can allow. In such cases the tidying task may be broken down into groups (see the –g command line option); additionally the spfru process may also be set up to tidy jobs during its normal running, thus allowing job booking, etc. to occur whilst also tidying jobs.
As a general rule this utility should be done whilst ServiceOptimizer system is NOT running. However, in busy systems the audit data can be tidied using this utility whilst ServiceOptimizer is running.
sp_dbtidy Command Line
This utility is configured entirely by command line parameters as follows:
-d <database string> -e <expiry date> [-a <audit expiry date>] [-c <commit count>] [-j] [-x] [-i <intray expiry date>] [-g <groups to delete>] [-r <maximum runtime in minutes>] [-w <Index creation Mode>] [-D <operative deletion file>] [-J <job deletion file>]
Where:
Command line option
| Mandatory / Default | Meaning | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
-d <database string> | Mandatory | The database that needs to be tidied. The <database string> is of the format specified in New Instance Set Up | |||||||||||||||
| -e <expiry date or days in past> | Mandatory | Will drop any data that is older than this date. If this is a number then the date is calculated as today minus this number of days into the past. Date format The format for the dates: <expiry date>, <audit expiry date> and <intray expiry date> is: ‘DD/MM/YYYY’. See also the ‘–j’, -i and ‘–a’ parameters. Jobs that have expired but are part of a linkage will not be removed until all jobs in the linkage have expired. Scheduled jobs are deemed to have expired when their expected_end is before the specified date. It is not possible to tidy jobs that are within the ServiceOptimizer memory horizon. | |||||||||||||||
| -a <audit expiry date or days in past> | If not specified then use value specified for –e | This is the same as the –e parameter but is specific to the audit trail data, if specified then overrides the –e parameter. It is possible to tidy the audit trails up to but not including today. If only the –e parameter is specified then the date must be valid for both the ‘–a’ and ‘–e’ parameters. | |||||||||||||||
| -i <intray expiry date or days in past> | If not specified no intray jobs are removed | Remove any intray jobs older than this given date, format similar to the –e parameter. ‘older’ is defined to be an intray job whose ‘appointment_latest’ or ‘contract_completion’ date (as defined in the database table: sp108_job_details) is before the given date. If the –j parameter is not specified then only intray jobs that are phone cleared will be removed, otherwise all jobs deemed to have expired will be removed. Jobs in the intray that have expired but are part of a linkage with a job still current on the schedule will not be removed.
If all members of the linkage are in the intray then member jobs of the linkage will be removed, even though there are still members that have not expired. It is not possible to tidy jobs that are within the ServiceOptimizer memory horizon. | |||||||||||||||
| -c <commit count> | 500 rows is used as the default value | This is a performance enhancement as it reduces the amount of history keep by the database during the run of sp_dbtidy. | |||||||||||||||
| -j | Only drop jobs at Dispatch Status Cleared | If specified then drop any job regardless of its Dispatch Status. | |||||||||||||||
| -x | If unspecified then auditing is enabled. | Turns off auditing of sp_dbtidy’s deletions from the ServiceScheduling database. | |||||||||||||||
| -g | If not specified and neither –D nor –J have been specified, then all groups are tidied. | A comma-separated list of groups to be tidied. The following groups are supported:
See Database Tidied Tables. for details of which tables are tidied in which group | |||||||||||||||
| -r | By default there’s no maximum runtime. | Maximum number of minutes for which sp_dbtidy should run. | |||||||||||||||
| -w <Index creation Mode> | Defaults to Block |
For performance reasons dbtidy can introduce indexes. If dbtidy finds a pre-existing index with the same name it neither creates nor drops the index. NB Customers using non-enterprise Oracle instances must use the NoIndex setting. | |||||||||||||||
| -D | None | A file specifying a list of employees to be deleted. Please seek further guidance from SERVICEPower support. | |||||||||||||||
| -J | None | A file specifying a list of jobs to be deleted. Please seek further guidance from SERVICEPower support. |
Example commands
sp_dbtidy –d MSSQL:server:dbname:: -e 10/03/2003 | Will invoke the standard mode and connect to a SQL Server database ‘dbname’ on ‘server’ using a trusted connection, and delete all data that has ‘expired’ up to but NOT including the 10th March 2003. Audit expiry date is assumed to be the same as the normal expiry date (i.e. that specified for the –e option). Only cleared jobs are deleted. It will not delete intray jobs |
sp_dbtidy –d MSSQL:server:dbname:: -e 14 –g Global | Will invoke the standard mode and connect to a SQL Server database ‘dbname’ on ‘server’ using a trusted connection, and delete all data that has ‘expired’ up to but NOT including (today – 14) days. Only Global data is deleted – jobs and audit data is unaffected. |
sp_dbtidy –d MSSQL:server:dbname:: -e 7 –g Audit –r 60 | Will invoke the standard mode and connect to a SQL Server database ‘dbname’ on ‘server’ using a trusted connection, and delete all audit data that has ‘expired’ up to but NOT including (today – 7) days. Only audit data is deleted – global data and jobs are unaffected. Tidying will run for at most 60 minutes. |
sp_dbtidy –d oraUser/oraPass –e 3 –x –i 100 | Will invoke the standard mode and connect to an Oracle schema whose user name and password is ‘oraUser’ and ‘oraPass’, and delete all data that has ‘expired’ up to but NOT including today – 3 days. Again only cleared jobs are deleted. It also assumes the same day offset for the audit trail data and that sp_dbtidy should not audit its changes to sp100, sp108, sp111. It will delete intray jobs that are older than 100 days that have been ‘phone cleared’. |
sp_dbtidy –d ORACLE::oraUser:oraPass –e 3 –a 10/03/2003 –i 100 –c 1000 –j | Will invoke the standard mode and connect to an oracle schema which has a user/password ‘oraUser’ and ‘oraPass’ and tidy general data that is older than 3 days (offset from eff_date) and audit data that is older than 10th March 2003. The commit count is after 1000 rows of data has been written as opposed to the default 500 rows and all jobs regardless of their activity status are deleted. Will delete all intray jobs that have expired: note that these jobs have not been completed. No warning will be given by ServiceScheduling as to the dangers involved in this action. |
sp_dbtidy –d MSSQL:server:dbname:: -J c:\data\jobsToDelete | Will invoke the Job Deletion mode and connect to a SQL Server database ‘dbname’ on ‘server’ using a trusted connection, and delete all the jobs specified in the file c:\data\jobsToDelete listed by job_ref. |
sp_dbtidy –d MSSQL:server:dbname:deleter:overlord –D c:\data\empsToDelete | Will invoke the Job Deletion mode and connect to a SQL Server database ‘dbname’ on ‘server’ using a SQL Server authenticated user called ‘deleter’ with password ‘overlord’, and delete all the employees specified in the file c:\data\empsToDelete listed by employee_ref. |