Re: DB2 Timestamps with time zone information

Yes, that is what I'm using DB2 Connect. What i'm mentioning though is that DB2 Connect Entity Framework driver for DB2 on i has no support for DateTimeOffset .NET data types because no equivalent data type is available on DB2 for the i, but is available on DB2 for Z/OS.

________________________________________
From: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
Sent: Thursday, August 11, 2016 11:06 AM
To: Midrange Systems Technical Discussion
Subject: Re: DB2 Timestamps with time zone information

Hi Matt

Allow me to settle down a bit - coming from a different angle as you do
is useful and helpful - the idea of an agnostic approach is desirable.

I have to ask about your statement that all databases have an entity
framework driver - can you expand on that - I've not heard of those yet.

I did look up "Entity Framework ORM Driver", got to the DB2 9.5 page
(LUW) and found this -

The Data Server Provider for .NET allows your .NET applications to
access the following database management systems:

* DB2® Database for Linux, UNIX, and Windows, Version 9 (or later)
* DB2 Universal Database™ Version 8 for Windows, UNIX, and Linux-based
computers
* DB2 Universal Database Version 7.3 (or later) for VSE & VM, through
DB2 Connect™
* DB2 Universal Database Version 6 (or later) for OS/390® and z/OS®,
through DB2 Connect
* DB2 Universal Database for iSeries® (using OS/400® V5R1, or later),
through DB2 Connect
* IBM Informix®, Version 11.10 or later
* IBM UniData® 7.1.11 or later
* IBM UniVerse 10.2 or later


Note the 5th bullet for DB2 Universal Database for iSeries - that is IBM
i in the old days - and it says you need to use a product called DB2
Connect.

So I think the support might be there - check the documentation for
limitations, of course.

Thanks
Vern

On 8/8/2016 7:17 PM, Matt Olson wrote:

Shoot. Thanks guys, I couldn't find the docs for 7.3. We plan on upgrading to 7.3 soon.

As for baiting, sorry. I just keep running into one wall after another with IBM i's flavor of DB2. Too many for just one developer IMHO.

Perhaps I'm doing things most developers have never tried on the IBM i.

In this particular instance I was hooking up the Entity Framework ORM driver for .NET. to a product called Identity Server V3 (open source). In Identity Server it uses a particular data type called "Datetimeoffset" to persist authentication tokens expiration date/time to your database storage of choice (any database with an entity framework driver, which is basically all of them).

One of the selling points of ORM's is to make querying the database agnostic. I could write it once for DB2, pick it up, and the same .NET program could theoretically run on MS SQL, or MySQL with no code changes (largely a pipe dream, but will likely fill 99% of that goal with just a few edge cases needing to be ironed out such as this one).

I this case I had the Identity Server running perfectly fine on MS SQL Server, and repointed my connection string to DB2 on the I and thats when errors pointed to the lack of support for datetimeoffset (aka timestamps with timezone information).

Also, it appears this actually isn't in LUW version, only Z/OS (so wierd!). I didn't actually find reference to LUW, but instead was reverse engineering the DB2 entity framework code to see how they were making the determination that this particular flavor of DB2 didn't support timezones and it appeared it was available on LUW, but I may have misread the code and it was actually meaning Z/OS supported it.

When I get around to it i'll be writing another RFE. But for now i've decided to ditch DB2 on the i for this information and will be storing it on our MS SQL cluster.

I'll also be talking to the developer of the identity Server open source project to see why he picked datetimeoffset as the method of storing tokens rather then the more commonly used DateTime data type and making everything UTC time.

________________________________________
From: Charles Wilt <charles.wilt@xxxxxxxxx>
Sent: Thursday, August 4, 2016 1:59 PM
To: Midrange Systems Technical Discussion
Subject: Re: DB2 Timestamps with time zone information

Doesn't appear to be supported on 7.3 either...

Charles

On Thu, Aug 4, 2016 at 2:49 PM, Matt Olson <Matt.Olson@xxxxxxxx> wrote:

So apparently I may have found another DB2 deficiency with the IBM I's
flavor of DB2 that is causing no end of pain and suffering.

Does anyone know if 7.3 perhaps supports this SQL:

CREATE TABLE tz(tstz TIMESTAMP WITH TIME ZONE);

Right now on 7.1 it doesn't seem to work.

Works just fine on DB2 LUW versions of DB2.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.