Datetime field overflow error occurred when call SQLExecute for Housekeeping Policy Data Read

Document ID : KB000004856
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

Upgrade policy store 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.5PS 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

 

For DB2 Timestamp column:

The time portion of a timestamp value can includes a specification of fractional seconds. The default number of digits in the fractional seconds portion is 6. The length of a TIMESTAMP WITHOUT TIME ZONE column as described in the SQLDA is between 19 and 32 bytes, which corresponds to the length for the character-string representation of the value.

For example, a 19 byte character-string representation has no fractional seconds; a 26 byte character-string representation has 6 digits of fractional seconds. when we try to insert '2005-01-01 02:03:04.001234567' into a Timestamp column, the observed output would be "Date Time Over flow" error.

The reason for seeing the error is when the fractional part of the above timestamp(specified in nano seconds) is converted to microseconds, it results in 001234 and the rest of the digits (567) cannot fit into the Timestamp structure. While on the other hand, '2005-01-01 02:03:04.001234000' gets inserted successfully.

As explained earlier, when the fractional portion of the above timestamp value is converted to microsecond it results in 1234 microseconds which gets fit into the Timestamp structure.

Now, when TimestampTruncationBehavior is set to 1, it silently truncates fractional seconds as needed by destination to fit into the structure. Taking the above example, after setting TimestampTruncationBehavior=1, when we try to insert/read '2005-01-01 02:03:04.001234567' it succeeds by truncating the 567 digits (2005-01-01 02:03:04.001234000) at the end silently as required by the destination.