Default Values in Columns

Posted on 2009-04-27
Last Modified: 2012-05-06
Fellow experts.  Thanks in advance for your help.

I have a Db2 database that is hit by a number of Access databases through ODBC connections (roughly 240 users).  The only data type that I have problems with is Timestamp when I attempt to set the default value for a column on the Db2 server to be "Current Timestamp".  It turns out that the microseconds cause a problem with MS Access.

I know I can remove the Microseconds with this formula:
current timestamp - MICROSECOND (current timestamp) MICROSECONDS
That works VERY well.  What I would like to do is be able to build that formula into the default value for a column.  I've tried every combination I can think of, including building a formula (CREATE FORMULA AccessTDS) that I could call as the default value for the column, setting a trigger to update the column on insert, etc.  Sadly, nothing I've tried has worked.  After struggling with this and searching IBM's extensive help library, I'm surrendering and asking for help.

Does anyone out there know how I can set the default or 'generated by' or whatever for a timestamp column to be this formula:
current timestamp - MICROSECOND (current timestamp) MICROSECONDS

Thanks again for your help,

Question by:MrXmas
    LVL 45

    Expert Comment

    Hi MrXmas,

    I'm not a bit fan (or user) of generated columns, but they certainly do have their place.  :)

    That said, your options will vary a bit depending upon exactly which version of DB2 you're running.  On LUW version 9, you can generate most any column.  The DB2 documentation offers this:

    CREATE TABLE t1 (c1 INT,
                        c2 DOUBLE,
                        c3 DOUBLE GENERATED ALWAYS AS (c1 + c2)
                        c4 GENERATED ALWAYS AS
                           (CASE WHEN c1 > c2 THEN 1 ELSE NULL END))

    It implies that you should be able to do something like this:

    CREATE TABLE mytable (
      dt timestamp default current datetime,
      dt1 timestamp GENERATED ALWAYS AS (dt - microsecond (dt))

    LVL 4

    Author Comment


    I tried something like this although I attempted to use the "GENERATED ALWAYS AS (current timestamp - MICROSECOND (current timestamp) MICROSECONDS).

    Would the solution above not give me two columns, one with dt as the Current Timestamp and dt1 as the same value sans Microseconds?  The issue I'm having is that the very presence of the microseconds seems to cause Access to have issues with the table.

    Thanks for your input.

    LVL 45

    Assisted Solution

    Hi MrXmax,

    Quite a number of things are not compatible between DB2 and SQL Server.  Even worse between DB2 and Access.

    It may be necessary to run a query on the DB2 side to generate the data that you want to retrieve via Access.  You can always create a view on DB2 that formats the date/time as you want, then call the view from Access.

    CREATE VIEW myview AS
      substr (cast (current timestamp as varchar (30)), 1, 20)
    FROM mytable

    Instead of the 'current timestamp' value, use the value from the table.  This will pass a string that contains the date and time.

    You can also pass the date and time as separate columns, if you so choose.

    SELECT date (current timestamp), time (current timestamp)
    FROM mytable

    LVL 1

    Accepted Solution

    I think your problem is more with how the driver interacts with DB2 more than the need to round down the timestamp precision. You can apply a client side configuration to get around the issue. In the db2cli.ini file of the SQLLIB folder (usually C:\Program Files\IBM\SQLLIB) add the following to the section of the database you're having trouble with:


    See the following for more info:

    If you do want to round down your default values I don't think generated always will work with a special register like current_timestamp so you'll have to use a trigger and call a function like:

    timestamp_iso( current_time ).

    This will convert a time (which has no milliseconds) to a timestamp and default the date part to today.

    LVL 4

    Author Closing Comment


    Thanks for your help.  I've been working on this for what seems like far longer than the solution will actually be worth.  I'm not sure why IBM doesn't allow me to use the special registers within formula in default values, or user defined formulas, but that's just the case.  I'm going to go ahead and use triggers instead and simply set the value to the formula I need as the data is added.  
    I've split the points as evenly as I could.  I would gladly award points to IBM if they would correct what seems like an obvious oversight in Db2...

    Many thanks for your help with this,

    LVL 1

    Expert Comment

    Hi MrXMas,

    If you would like some feedback from IBM you can post at:

    Quite a few of the developers of the DB engine respond regularly there. Serge is particularly helpful.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    Title # Comments Views Activity
    db2jcc.jar vs db2jcc4.jar 1 4,022
    SQL Duplicate Query 9 354
    Using select statement from input file with DB2 EXPORT 1 496
    DB2 V9.7 restore 5 22
    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now