DB2 timestamps and SQL Server
I've been working with a document database in DB2, getting the document metadata into a SQL Server data warehouse. I've run across some issues with the way DB2 timestamp data types are converted into SQL Server datetime2 when you access the DB2 server via a linked server in SQL.
(Aside: I generally don't like using linked servers in SQL, they tend to increase coupling and contribute to "data spaghetti". I'd rather specify a data connection in an SSIS package that uses the DB2 OLE DB provider. However, in this case, for a bunch of reasons I won't go into here, we are using a linked server with the OLE2DB provider.)
This is the query that I wanted to execute:
select COMPONENTID,ATTR0000001053 as AccountNumber,ATTR0000001040 as CreationDateTime,ATTR0000001054 as Description,ATTR0000001044 as DocumentSource,ATTR0000001043 as DocumentType,ATTR0000001046 as Creator,LASTCHANGEDTS FROM cm.LS.ICMADMIN.ICMUT01024001 where LASTCHANGEDTS > '2017-4-1'
At runtime, I would replace the date with the last refresh date.
SQL Server recognized the data types of the two columns ATTR0000001040 and LASTCHANGEDTS as datetime2. In DB/2, these are known as the data type timestamp - basically the same as datetime2 (and totally different from the SQL server data type called timestamp).
Occasionally I would get this error:
Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime2.
I decided to use OPENQUERY to have more control over what exactly got executed on the DB2 server. This function sends the query directly to the linked server without any local parsing, so I could use DB2 functions to help me with those pesky dates.
This SQLServerCentral.Com forum post helped me to figure out how to convert the timestamp in DB2, but it used CAST([SomeDateField] AS varchar(26)), which returned text-formatted dates like this:
2017-03-16-11:04:37.765300
I thought this looked like the "ODBC canonical format", which is style #121 for the CONVERT or TRY_CONVERT functions in SQL.
Those dates like above wouldn't convert using the style format 121, and it took me a minute to see why.
There is a dash between the date and time in the string. Did you see it?
I went on a short goose chase considering how I could use SUBSTRING to extract the parts of the string and put it back together with a space, but I decided there must be a better way in DB2.
It turns out there is a helpful DB2 function called VARCHAR_FORMAT that allows you to specify a format string to use when converting from one data type (like timestamp) to VARCHAR. I could specify the format string 'YYYY-MM-DD HH24:MI:SS.NNNNNN' and get the "ODBC canonical" format exactly.
varchar_format(ATTR0000001040, 'YYYY-MM-DD HH24:MI:SS.NNNNNN')
From the resultset of the OPENQUERY statement, I could use TRY_CONVERT to convert the text-formatted value back to a SQL datetime2.
try_convert(datetime2, [ATTR0000001040],121) [CreationDatetime]
Try_convert is nice because if it fails to convert, it returns NULL rather than an error.
Now my full statement looks like this:
SELECT
rtrim([COMPONENTID]) AS ComponentId
,ATTR0000001053 as AccountNumber
,try_convert(datetime2, ATTR0000001040,121) as CreationDatetime
,ATTR0000001054 as Description
,ATTR0000001044 as DocumentSource
,ATTR0000001043 as DocumentType
,ATTR0000001046 as Creator
,try_convert(datetime2,LASTCHANGEDTS,121) as RecordModifiedDate
from OPENQUERY(cm, 'select COMPONENTID,ATTR0000001053,varchar_format(ATTR0000001040, ''YYYY-MM-DD HH24:MI:SS.NNNNNN'') as ATTR0000001040,ATTR0000001054,ATTR0000001044,ATTR0000001039,ATTR0000001048,ATTR0000001043,ATTR0000001046,varchar_format(LASTCHANGEDTS , ''YYYY-MM-DD HH24:MI:SS.NNNNNN'') as LASTCHANGEDTS FROM LS.ICMADMIN.ICMUT01024001 where LASTCHANGEDTS > ''2017-4-1''')
Oddly enough, I don't actually get any NULL values for those two dates after doing it this way. All the text-formatted dates convert successfully. (Yes, I am puzzled by this, but whatever, mission accomplished!)
I find that data type conversion issues between systems can often consume a lot of time. Hopefully someone will find this blog post helpful when they are having problems with DB2 timestamps and SQL datetime2 conversions.
Mike