Karmeng

Tech Tip : CA Single Sign-On :Policy Server : Datetime field overflow error in smps log

Blog Post created by Karmeng Employee on Dec 1, 2016

Symptom:

Upgrade DB2 database from 9.7 to 10.5 causing error in smps log. Error shown every 5 minutes.

ie:

[ERROR][sm-xpsxps-00870] An error occurred when calling "SQLExecute" for "Housekeeping Policy Data Read" query

[ERROR][sm-xpsxps-00810] Native Diagnostic: 22008:0 [NS][ODBC DB2 Wire Protocol driver]Datetime field overflow. Error in parameter 1.

 

Environment:

Policy server: 12.52; Update: 01.02; Build: 766; CR: 02;

DB2 version:  IBM DB2 10.5

PS OS vendor and version: RHEL 5.11

 

Cause:

HouseKeeping thread will be querying for modified objects every 5 min (default) OR based on configuration in XPSConfing value CacheCheckDelay.

The value sent in for "xpsObject.obModifiedDTM" in the select statement exceeds the allowed range of the field.

SELECT DISTINCT obNumber,obCategory,obClass,obParentObject,obGUID,obTombstone,obCreatedDTM,obModifiedDTM,obUpdateBy,obUpdateMethod,ppAttribute,ppSequence,ppValueInteger,ppValueDTM,ppValueBoolean,ppValueString,ppValueLink FROM xpsObject LEFT OUTER JOIN xpsProperty ON xpsObject.obNumber = xpsProperty.ppObject WHERE ((xpsObject.obModifiedDTM>=?) AND (xpsObject.obCategory > 1)) ORDER BY obModifiedDTM, obCategory, obNumber, ppAttribute, ppSequence

 

Resolution:

set WorkArounds2=2 to data source in system_odbc.ini based on (KB: TEC1466733 http://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.tec1466733.html).

If this does not solve the issue, try

set TimestampTruncationBehavior=1

 

How to set this parameter in Windows environment

a) In regedit, browse to HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\datasource

b) Select the DSN of the DB2 ODBC connection you want to modify

c) Add a new key as a 'String Value'

d) Name the new key "TimestampTruncationBehavior"

e) Set the Value of 'TimestampTruncationBehavior' to "1"

 

How to set this parameter in Unix environment

a) Modified system_odbc.ini to include TimestampTruncationBehavior

ie:

[SiteMinder Data Source]
Driver=/app/CA/netegrity/siteminder/odbc/lib/NSdb227.so
Description=DataDirect 7.1 DB2 Wire Protocol
Database=SMPOLICY
IpAddress=xx.xx.xx.xx
TcpPort=xxxxx
Package=
PackageOwner=
GrantAuthid=PUBLIC
GrantExecute=0
IsolationLevel=CURSOR_STABILITY
DynamicSections=100
DMCleanup=2
WorkArounds2=2
TimestampTruncationBehavior=1

 

Additional Information:

http://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.tec1466733.html

Outcomes