JavaScript method: SCFile.doPurge()

This method enables you to purge a set of ITSMA Service Management records directly in the back-end RDBMS while bypassing record level processing. The doPurge() method is an alternative to doDelete() or doRemove(). It enables the back-end RDBMS to do most of the processing, thereby improving execution times when deleting a large amount of data from a single table.

The doDelete() method requires you to select a list of records from the database, iterate over each record in the list and perform a “delete” on a record by record basis. When the delete is performed, Service Management respects workflow actions as defined in record level triggers. The doPurge() method reduces the overhead of much of this by sending one SQL statement to the RDBMS to purge the data. As a result of the RDBMS processing the record set purge in one transaction, Service Management does not have control to process record level triggers on the data being purged. Standard Service Management trigger processing will not occur on these records.

You should use doPurge() only when you need to quickly delete a large record set from a single Service Management table, and it does not matter whether Service Management triggers are processed.

Caution Purging data has some risks. Use doPurge() with caution. It is the equivalent of issuing a SQL statement to the RDBMS such as, DELETE FROM "SM_TABLE_NAME" WHERE "COLOUMN_NAME" = 'value'.

Some issues to understand and consider before using this method:

  • Data referential integrity could be an issue by purging data that is related to other types of Service Management data. This method does not execute normal record level workflow processing in the applications layer. When you turn off triggers and run doPurge(), you will bypass normal workflow processing in addition to record level triggers.
  • No Service Management rollback or undo is available after you run doPurge().
  • No Service Management audit or other tracking is available to indicate changes made by doPurge(). Consider coding your own audit mechanism.

You can retrieve the number of records deleted from a table by doPurge() by using the JavaScript method: getPurgedRowCount()

Note  When you use doPurge(), Service Management deletes a set of records. This causes a delete to the back-end database. When Service Management attempts to delete a large set of records, the database could run out of space for this activity and cause an error. Each supported database platform provides some type of transaction or undo log to back up data changes and allow a rollback in case of errors. If you encounter such an error, please contact your database administrator and ask them to increase the size available for this purpose.

Some examples of the errors you could encounter are:

Oracle: SQL code=30036 message=ORA-30036: unable to extend segment by % in undo tablespace

SQL Server SQL State: 42000-9002 Message: [Microsoft][SQL Native Client][SQL Server]The transaction log for database '%' is full

DB2 SQL State: 57011--964 Message: [IBM][CLI Driver][DB2/NT] SQL0964C The transaction log for the database is full. SQLSTATE=57011

Syntax

SCFile_object.doPurge( query );

Arguments

The following arguments are valid for this method:

Argument Description
query

String, Boolean, or QueryCond object

This argument specifies the query you want to use to search for Service Management records. You must use the slash character to escape out quotation marks and any special characters restricted from JavaScript. Boolean values (true or false) or a QueryCond object are also acceptable.

Return values

Returns RC_SUCCESS if successful and RC_ERROR on failure.

The following conditions will cause a doPurge() failure:

  • Calling doPurge() on an uninitialized SCFile or a read-only SCFile
  • Calling doPurge() with an empty query argument
  • Calling doPurge() with an invalid argument type (not a string)
  • Calling doPurge() with one (or more) invalid (or unmapped) fields.
    A basic validation is performed on the field names to ensure they exist in the DBDICT. This validation will not cause a failure of the method; it will return RC_SUCCESS. However, in this case, it will default to a “false” query and no records will be deleted.
  • Calling doPurge() with an unsupported complex type SCFile relation.
    See the limitations below for supported types.
  • Calling doPurge() with an SCFile object which has “delete” triggers defined and enabled.

When the method fails, it has no effect.

Limitations

Only simple SCFile relations are supported:

  • Must not be a join file or a merge file
  • Must have only 1 main table alias (m1)
  • Must not have any alias tables (a1,a2,….)
  • No LDAP mapped fields
  • The table must not have “delete” triggers defined and enabled

Example

This example does the following:

  • Searches the kmknowledgebaseupdates table for records where docid="docid1"
  • Deletes whatever it finds.

This example requires the following sample data:

  • Records in kmknowledgebaseupdates table where docid = "docid1"
var fileName = "kmknowledgebaseupdates";
var recordList = new SCFile(fileName);
var deleteRC = recordList.doPurge("docid = \"docid1\"");