Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
7 replies Latest Post - ‏2013-02-17T08:23:28Z by StefanMihokovic
lliang
lliang
15 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: About the default value on column in DB2

    ‏2013-01-16T11:06:17Z  in response to lliang
    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
      ACCEPTED ANSWER

      Re: About the default value on column in DB2

      ‏2013-01-29T12:10:37Z  in response to SystemAdmin
      Remember though that the trigger will get called for every insert you make into the DB. This will take a hit on write performance.
      • SystemAdmin
        SystemAdmin
        17917 Posts
        ACCEPTED ANSWER

        Re: About the default value on column in DB2

        ‏2013-01-29T14:56:27Z  in response to ParulJ
        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.
  • ParulJ
    ParulJ
    5 Posts
    ACCEPTED ANSWER

    Re: About the default value on column in DB2

    ‏2013-01-29T12:11:42Z  in response to lliang
    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
      ACCEPTED ANSWER

      Re: About the default value on column in DB2

      ‏2013-01-29T12:55:09Z  in response to ParulJ
      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?
      • lliang
        lliang
        15 Posts
        ACCEPTED ANSWER

        Re: About the default value on column in DB2

        ‏2013-01-30T02:49:08Z  in response to ParulJ
        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
          ACCEPTED ANSWER

          Re: About the default value on column in DB2

          ‏2013-02-17T08:23:28Z  in response to lliang
          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