CANCEL_WORK procedure - Cancel work

The CANCEL_WORK stored procedure cancels either a specific activity (for example, a SQL statement), or all activity for a connected application.

To cancel a specific activity, you pass in the application handle, unit of work ID, and activity ID for the activity that you want to cancel. To cancel all activity for a connected application, you pass in the application handle. Any changes associated with the canceled activity are rolled back.

Syntax

The schema is SYSPROC.

Procedure parameters

major_version

An input and output argument of type INTEGER that indicates the major document version. On input, this argument indicates the major document version that the caller supports for the XML documents passed as parameters in the procedure (see the parameter descriptions for xml_input, xml_output, and xml_message). The procedure processes all XML documents in the specified version, or returns an error (+20458) if the version is not valid. On output, this parameter specifies the highest major document version that is supported by the procedure. To determine the highest supported document version, specify NULL for this input parameter and all other required parameters.

Supported versions: 1

minor_version

An input and output argument of type INTEGER that indicates the minor document version. On input, this argument specifies the minor document version that the caller supports for the XML documents passed as parameters for this procedure (see the parameter descriptions for xml_input, xml_output, and xml_message). The procedure processes all XML documents in the specified version, or returns an error if the version is not valid. On output, this parameter indicates the highest minor document version that is supported for the highest supported major version. To determine the highest supported document version, specify NULL for this input parameter and all other required parameters.

Supported versions: 0

requested_locale

An input argument of type VARCHAR(33) that specifies a locale. If the specified language is supported on the server, translated content is returned in the xml_output and xml_message parameters. Otherwise, content is returned in the default language. Only the language and possibly the territory information is used from the locale. The locale is not used to format numbers or influence the document encoding. For example, key names and values are not translated. The only translated portion of the XML output and XML message documents are the text for hint, display name, and display unit of each entry. The caller should always compare the requested language to the language that is used in the XML output document (see the document locale entry in the XML output document).

Currently, the only supported value for requested_locale is en_US.

xml_input

An input argument of type BLOB(32MB) that specifies an XML input document (encoded in UTF-8) that contains input values for the procedure.

For this procedure, the XML input document must specify an application handle. If you want to cancel a specific activity, the XML input document must also specify optional parameters that identify a unit of work ID and an activity ID. A complete XML input document for this stored procedure looks something like the following document:

<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
   <key>Document Type Name</key><string>Data Server Cancel Work Input</string>
   <key>Document Type Major Version</key><integer>1</integer>
   <key>Document Type Minor Version</key><integer>0</integer>
   <key>Required Parameters</key>
   <dict>
      <key>Application Handle</key>
      <dict>
         <key>Display name</key><string>Application Handle</string>
         <key>Value</key><integer>10</integer>
         <key>Hint</key>
         <string>
         Numeric value equivalent to the application handle to be cancelled
         </string>
      </dict>
   </dict> 
   <key>Optional Parameters</key> 
   <dict>
      <key>Unit Of Work Id</key>
      <dict>
         <key>Display Name</key><string>Unit Of Work Id</string>
         <key>Value</key><integer>20</integer>
         <key>Hint</key>
         <string>
         Numeric value that specifies the unit of work id of the activity 
         that is to be cancelled 
         </string>
      </dict>
      <key>Activity Id</key>
      <dict>
         <key>Display Name</key><string>Activity Id</string>
         <key>Value</key><integer>10</integer>
         <key>Hint</key>
         <string>
         Numeric value equivalent to the activity id to be cancelled
         </string>
      </dict>
   </dict>
</dict>
</plist>

If you specify the application handle of the application where the stored procedure is running, the procedure returns a warning (SQL20458).

xml_filter

An input argument of type BLOB(4K) that specifies a valid XPath query string. Use a filter when you want to retrieve a single value from an XML output document. For more information, see the topic that describes XPath filtering.

The following example selects the value for the Data Server Product Version from the XML output document:

/plist/dict/key[.='Data
Server Product Version']/following-sibling::string

. If the key is not followed by the specified sibling, an error is returned.

xml_output

An output parameter of type BLOB(32MB) that returns a complete XML output document in UTF-8. If a filter is specified, this parameter returns a string value. If the stored procedure is unable to return a complete output document (for example, if a processing error occurs that results in an SQL warning or error), this parameter is set to NULL.

The XML output is determined by the values that you specify for major_version and minor_version:

Major version	Minor version	`xml_output` value
NULL	NULL	NULL
1	0	The status of the activity that the procedure attempted to cancel.

When the procedure operates in complete mode, this parameter returns an XML document that you can modify and pass back to the procedure as the xml_input parameter. This approach provides a programmatic way to create valid XML input documents. For more information, see the topic about complete mode.

xml_message

An output parameter of type BLOB(64K) that returns a complete XML output document of type Data Server Message in UTF-8 that provides detailed information about a SQL warning condition. This document is returned when a call to the procedure results in a SQL warning, and the warning message indicates that additional information is returned in the XML message output document. If the warning message does not indicate that additional information is returned, then this parameter is set to NULL.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

When CANCEL_WORK is being used to cancel all activity (force application) for a connected application one of the following authorities is also required:

SYSADM
SYSCTRL
SYSMAINT

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.

Example

Example 1: Return the highest supported version of the procedure.

db2 "call sysproc.cancel_work(null,null,null,null,null,?,?)"

The following is an example of output from this query:

Value of output parameters
--------------------------
Parameter Name : MAJOR_VERSION
Parameter Value : 1

Parameter Name : MINOR_VERSION
Parameter Value : 0

Parameter Name : XML_OUTPUT
Parameter Value : -

Parameter Name : XML_MESSAGE
Parameter Value : -

Return Status = 0

Example 2: Cancel a specific activity.

db2 "call sysproc.cancel_work(1,0,'en_US',blob(
<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
   <key>Document Type Name</key><string>Data Server Cancel Work Input</string>
   <key>Document Type Major Version</key><integer>1</integer>
   <key>Document Type Minor Version</key><integer>0</integer>
   <key>Required Parameters</key>
   <dict>
      <key>Application Handle</key>
      <dict>
          <key>Display name</key><string>Application Handle</string>
          <key>Value</key><integer>1</integer>
          <key>Hint</key>
          <string>
          Numeric value equivalent to the application handle to be cancelled
          </string>
      </dict>
   </dict>                           
   <key>Optional Parameters</key>        
   <dict>
      <key>Unit Of Work Id</key>
      <dict>
          <key>Display Name</key><string>Unit Of Work Id</string>
          <key>Value</key><integer>2</integer>
          <key>Hint</key>
          <string> 
          Numeric value that specifies the unit of work id of the activity 
          that is to be cancelled 
          </string>
      </dict>
      <key>Activity Id</key>
      <dict>
         <key>Display Name</key><string>Activity Id</string>
         <key>Value</key><integer>3</integer>
         <key>Hint</key>
         <string>
         Numeric value equivalent to the activity id to be cancelled
         </string>
      </dict>
   </dict>
</dict>
</plist> ) ,null,?,?)"

The following is an example of output from this query:

Value of output parameters
--------------------------
Parameter Name : MAJOR_VERSION
Parameter Value : 1

Parameter Name : MINOR_VERSION
Parameter Value : 0

Parameter Name : XML_OUTPUT
Parameter Value : x'3C3F78...'

Parameter Name : XML_MESSAGE
Parameter Value : -

Return Status = 0

If the CANCEL_WORK procedure is able to cancel the activity, the XML output document contains the following content:

<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict><key>Document Type Name</key><string>Data Server Cancel Work Output</string>
   <key>Document Type Major Version</key><integer>1</integer>
   <key>Document Type Minor Version</key><integer>0</integer>
   <key>Data Server Product Name</key><string>QDB2/AIX64</string>
   <key>Data Server Product Version</key><string>9.7.0.0</string>
   <key>Data Server Major Version</key><integer>9</integer>
   <key>Data Server Minor Version</key><integer>7</integer>
   <key>Data Server Platform</key><string>AIX 64BIT</string>
   <key>Document Locale</key><string>en_US</string>
   <key>Successful Cancel Work Message</key>
   <dict>
      <key>Display Name</key><string>Successful Cancel Work Message</string>
      <key>Value</key><string>The activity has been cancelled successfully</string>
      <key>Hint</key><string></string>
   </dict>
</dict>
</plist>

Example 2: Cancel the application.

db2 "call sysproc.cancel_work(1,0,'en_US,blob(
<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
   <key>Document Type Name</key><string>Data Server Cancel Work Input</string>
   <key>Document Type Major Version</key><integer>1</integer>
   <key>Document Type Minor Version</key><integer>0</integer>
   <key>Required Parameters</key>
   <dict>
      <key>Application Handle</key>
      <dict>
          <key>Display name</key><string>Application Handle</string>
          <key>Value</key><integer>101</integer>
          <key>Hint</key>
          <string>
          Numeric value equivalent to the application handle to be cancelled
          </string>
      </dict>
   </dict>
</dict>
</plist> ),null,?,?)"

The following is an example of output from this query:

Value of output parameters
--------------------------
Parameter Name : MAJOR_VERSION
Parameter Value : 1

Parameter Name : MINOR_VERSION
Parameter Value : 0

Parameter Name : XML_OUTPUT
Parameter Value : x'3C3F78...'

Parameter Name : XML_MESSAGE
Parameter Value : -

Return Status = 0

If the CANCEL_WORK procedure is able to cancel the application, the XML output document contains the following content:

<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
   <key>Document Type Name</key><string>Data Server Cancel Work Output</string>
   <key>Document Type Major Version</key><integer>1</integer>
   <key>Document Type Minor Version</key><integer>0</integer>
   <key>Data Server Product Name</key><string>QDB2/AIX64</string>
   <key>Data Server Product Version</key><string>9.7.0.0</string>
   <key>Data Server Major Version</key><integer>9</integer>
   <key>Data Server Minor Version</key><integer>7</integer>
   <key>Data Server Platform</key><string>AIX 64BIT</string>
   <key>Document Locale</key><string>en_US</string>
   <key>Successful Cancel Work Message</key>
   <dict>
      <key>Display Name</key><string>Successful Cancel Work Message</string>
      <key>Value</key>
      <string>The application has been cancelled successfully</string>
      <key>Hint</key><string></string>
   </dict>
</dict>
</plist>

Example 3: Specify a filter to return the value of a successful cancel work message.

db2 "call sysproc.cancel_work(1,0,'en_US,blob(
<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
   <key>Document Type Name</key><string>Data Server Cancel Work Input</string>
   <key>Document Type Major Version</key><integer>1</integer>
   <key>Document Type Minor Version</key><integer>0</integer>
   <key>Required Parameters</key>
   <dict>
      <key>Application Handle</key>
      <dict>
          <key>Display name</key><string>Application Handle</string>
          <key>Value</key><integer>101</integer>
          <key>Hint</key>
          <string>
          Numeric value equivalent to the application handle to be cancelled
          </string>
      </dict>
   </dict>
</dict>
</plist> ),blob('/plist/dict/key[.="Successful Cancel Work Message"]
/following-sibling::dict[1]/key[.="Value"]
/following-sibling::string[1]'),?,?)"

The following is an example of output from this query:

Value of output parameters
--------------------------
Parameter Name : MAJOR_VERSION
Parameter Value : 1

Parameter Name : MINOR_VERSION
Parameter Value : 0

Parameter Name : XML_OUTPUT
Parameter Value : x'3C3F78...'

Parameter Name : XML_MESSAGE
Parameter Value : -

Return Status = 0

The following value is returned for xml_output:

 "The application has been cancelled successfully"