?
Solved

Default Values in Columns

Posted on 2009-04-27
6
Medium Priority
?
1,097 Views
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,

MrXmas
0
Comment
Question by:MrXmas
  • 2
  • 2
  • 2
6 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24245017
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))
);


Kent
0
 
LVL 4

Author Comment

by:MrXmas
ID: 24245174
Kent,

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.

MrXmas
0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 186 total points
ID: 24245228
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
SELECT
  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



Kent
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 1

Accepted Solution

by:
cmaslen earned 189 total points
ID: 24268640
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:

PATCH1=4

See the following for more info:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.cli.doc/doc/r0008804.htm

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.

Christian.
0
 
LVL 4

Author Closing Comment

by:MrXmas
ID: 31575137
Gentlemen,

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,

MrXmas.
0
 
LVL 1

Expert Comment

by:cmaslen
ID: 24303197
Hi MrXMas,

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

http://groups.google.com.au/group/comp.databases.ibm-db2/topics?lnk=srg&hl=en

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

Christian.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month17 days, 5 hours left to enroll

862 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