Topic
  • 7 replies
  • Latest Post - ‏2013-02-17T08:23:28Z by StefanMihokovic
lliang
lliang
15 Posts

Pinned topic About the default value on column in DB2

‏2013-01-16T05:20:49Z |
Hi there,

I have a requirement to create a table column with the default value of like "current_date + 5 hours" in DB2. I tried the following DDL, and DB2 won't allow it to run sucessfully.

"Alter Table T ADD COLUMN created_date date WITH DEFAULT current date + 5 hours"

Looking at the infocenter, I understand that In DB2 default value on column could only contain constant, special-register, current schema, null, cast function ... etc.

But I just want to raise it here in case you might have some other solution (or work-around) to reach the same effect in DB2?

Thanks in advance.
Updated on 2013-02-17T08:23:28Z at 2013-02-17T08:23:28Z by StefanMihokovic
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: About the default value on column in DB2

    ‏2013-01-16T11:06:17Z  
    You may use a trigger like this:

    CREATE TRIGGER My_trig
    BEFORE INSERT ON T
    REFERENCING NEW AS N
    FOR EACH ROW
    SET n.created_date = COALESCE(n.created_date ,current date + 5 hours)
  • ParulJ
    ParulJ
    5 Posts

    Re: About the default value on column in DB2

    ‏2013-01-29T12:10:37Z  
    You may use a trigger like this:

    CREATE TRIGGER My_trig
    BEFORE INSERT ON T
    REFERENCING NEW AS N
    FOR EACH ROW
    SET n.created_date = COALESCE(n.created_date ,current date + 5 hours)
    Remember though that the trigger will get called for every insert you make into the DB. This will take a hit on write performance.
  • ParulJ
    ParulJ
    5 Posts

    Re: About the default value on column in DB2

    ‏2013-01-29T12:11:42Z  
    Do you have a column that stores current date ?
    If so, you could use a "generated column" which will be current_date_column + 5 hours.
  • ParulJ
    ParulJ
    5 Posts

    Re: About the default value on column in DB2

    ‏2013-01-29T12:55:09Z  
    • ParulJ
    • ‏2013-01-29T12:11:42Z
    Do you have a column that stores current date ?
    If so, you could use a "generated column" which will be current_date_column + 5 hours.
    CREATE TABLE A
    (
    AOL VARCHAR(10) NOT NULL,
    AOL_OTHER timestamp default sysdate
    )
    GO

    This seems to work. However, I cant seem to figure out hwo to add 5 hours to the sysdate. Are you trying to accomplish a change of time zone with +5 hours?
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: About the default value on column in DB2

    ‏2013-01-29T14:56:27Z  
    • ParulJ
    • ‏2013-01-29T12:10:37Z
    Remember though that the trigger will get called for every insert you make into the DB. This will take a hit on write performance.
    Actually this case is a best match usage for before triggers usage. I don't believe that a before insert trigger affects the write performance.
    > As per DB2 information center:
    > The different activation times of triggers reflect different purposes of triggers. Basically, BEFORE triggers are an extension to the constraint subsystem of the database management system. Therefore, you generally use them to:
    > Perform validation of input data
    > Automatically generate values for newly inserted rows
    > Read from other tables for cross-referencing purposes
    > BEFORE triggers are not used for further modifying the database because they are activated before the trigger event is applied to the database. Consequently, they are activated before integrity constraints are checked.
  • lliang
    lliang
    15 Posts

    Re: About the default value on column in DB2

    ‏2013-01-30T02:49:08Z  
    • ParulJ
    • ‏2013-01-29T12:55:09Z
    CREATE TABLE A
    (
    AOL VARCHAR(10) NOT NULL,
    AOL_OTHER timestamp default sysdate
    )
    GO

    This seems to work. However, I cant seem to figure out hwo to add 5 hours to the sysdate. Are you trying to accomplish a change of time zone with +5 hours?
    Hi ParulJ,

    I understood that sysdate could work in DB2. But the need is just to add 5 hours to sysdate for a change of timezone.

    Actually I am migrating an App to DB2 from Oracle where there are CREATE_DATE columns whose default value is a call to a function which bascially returns sysdate + 5 hours. But DB2 can't support it. And I don't happen to have a column that stores systdate in the table.

    It looks like just store a sysdate would be an acceptable workaround (considering we can allow 5 hours difference)

    Thanks for your reply.
  • StefanMihokovic
    StefanMihokovic
    7 Posts

    Re: About the default value on column in DB2

    ‏2013-02-17T08:23:28Z  
    • lliang
    • ‏2013-01-30T02:49:08Z
    Hi ParulJ,

    I understood that sysdate could work in DB2. But the need is just to add 5 hours to sysdate for a change of timezone.

    Actually I am migrating an App to DB2 from Oracle where there are CREATE_DATE columns whose default value is a call to a function which bascially returns sysdate + 5 hours. But DB2 can't support it. And I don't happen to have a column that stores systdate in the table.

    It looks like just store a sysdate would be an acceptable workaround (considering we can allow 5 hours difference)

    Thanks for your reply.
    The problem can be solved with an additional "GENERATED ALWAYS" column to the column "created_date".

    By the way: To my knowledge, is the correct data type for DATE from Oracle in DB2 timestamp.

    
    CREATE TABLE test1 (  c1 
    
    char (4) , CREATE_TIME  timestamp WITH DEFAULT CURRENT TIMESTAMP , CREATE_TIME2 timestamp GENERATED ALWAYS AS ( CREATE_TIME + 5 HOURS ) )
    


    Regards/Grüße Stefan