SQL "Execution timeout expired." Exception Thrown By Jobs Run With WCI 10.3.x Automation Server

일단 등록해 놓자!

Applies to:

Oracle WebCenter Interaction - Version: 10.3.0.0 and later   [Release: and later ]
Information in this document applies to any platform.
Edited for currency on 1/23/12.

Symptoms

Jobs that run queries against the DB where the result takes longer than 30s to return fail with exceptions similar to the following in both the joblog and Automation logging spy:

Mar 23, 2010 12:45:15 PM- JobShell: Exception occurred when processing operation operation 0:22: Error in function PTScheduler.DeleteJobHistory (olderThen == March 16, 2010 12:44:40 PM PDT): Exception: [plumtree][SQLServer JDBC Driver]Execution timeout expired. SQL: '/* DELETE_FROM_JOB_LOGS:ANSI */ DELETE FROM PTJOBLOGS WHERE INSTANCEID < (SELECT MAX(INSTANCEID) FROM PTJOBHISTORY WHERE RUNTIME <=?)'

Changes

This problem can occur in all 10.3.0.x environments, including those patched to 10.3.0.1, as well as in 10.3.3. In all cases, apply the solution provided in this article.

Cause

This behavior is documented in Bug 9691347.

A ptspy log of the Automation Server during a job has debug entries similar to the following:

Retrieving subscriptions for Context automationserver. <ptLogMsgEnd>
Unable to retrieve DatabaseCommandTimeout from configuration settings, using default value: 30 <ptLogMsgEnd>

Whereas a Portal startup on the same environment has entries similar to the following:

Retrieving subscriptions for Context portal.
Retrieved setting DatabaseCommandTimeout from component portal:SystemProperties.
Setting DatabaseCommandTimeout read as 60 (integer)

This indicates that the Automation Server context is unable to read the settings in the 'portal:SystemProperties' section of the automation server configuration.xml file and a default value of 30s is used instead. Though the 10.3.0.1 patch resolves the issue created by Bug:9691347 it does so by utilizing a new attribute, 'DatabaseCommandTimeout', in the 'portal:SystemProperties' section of the configuration.xml which is included only on machines that host a Portal or Admin Portal and not standalone Automation Server hosts. Furthermore, even if your Portal and Automation services are hosted on the same machine it is possible that your environment is not configured to have Automation take advantage of the new feature.

Solution

The solution is to include the 'portal:SystemProperties' section in the Automation Server configuration.xml file and configure the Automation Server to utilize the 'DatabaseCommandTimeout' attribute. Accomplish this with the following steps:

1. Edit configuration.xml on machine hosting Automation Server.
2. Navigate to the 'client' area of "portal:SystemProperties" section. If this section does not exist, add the following:

<component name="portal:SystemProperties" type="http://www.plumtree.com/config/component/types/portal/systemproperties">
<setting name="ServerName">
<value xsi:type="xsd:string">localhost</value>
</setting>
<setting name="MachineName">
<value xsi:type="xsd:string">localhost</value>
</setting>
<setting name="PerformanceComments">
<value xsi:type="xsd:integer">1</value>
</setting>
<setting name="DebuggingMode">
<value xsi:type="xsd:integer">0</value>
</setting>
<setting name="DoctypeSpecification">
<value xsi:type="xsd:integer">1</value>
</setting>
<setting name="LayoutMode">
<value xsi:type="xsd:integer">1</value>
</setting>
<setting name="VirtualDirectoryPath">
<value xsi:type="xsd:string">/portal/</value>
</setting>
<setting name="HTTPEntryPoint">
<value xsi:type="xsd:string">server.pt</value>
</setting>
<setting name="HTTPPort">
<value xsi:type="xsd:integer">80</value>
</setting>
<setting name="HTTPSecurePort">
<value xsi:type="xsd:integer">443</value>
</setting>
<setting name="SSOVirtualDirectoryPath">
<value xsi:type="xsd:string">/portal/</value>
</setting>
<setting name="SSOServletName">
<value xsi:type="xsd:string">sso/SSOLogin.aspx</value>
</setting>
<setting name="DatabaseCommandTimeout">
<value xsi:type="xsd:integer">1200</value>
</setting>
<clients>
<client name="automationserver"/>
</component>

3. Specify the desired 'DatabaseCommandTimeout' value.
4. Modify the 'clients' entries to include the Automation Server context:

Before:

<clients>
<client name="portal"/>
</clients>

After:

<clients>
<client name="portal"/>
<client name="automationserver"/>
</clients>

5. Restart automation service.
6. During restart, confirm with Logging Spy that the Automation Server reads and loads the 'DatabaseCommandTimeout' value.
7. Repeat on any machine hosting an automation server instance.

Please note that the solution presented in this article applies ONLY to environments patched to 10.3.0.1 or higher. Older versions are not coded to utilize the 'DatabaseCommandTimeout' attribute.

References

NOTE:1163858.1 - WebCenter Interaction Master Note
Bug 9691347
Bug 8339671
Bug 7822162