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