Insertion using host variable

kishore erukulapati

Insertion using host variable
Hi ,
We have a DB2 table column which is defined as time stamp data type .One
application program does inserts into this table by using working storage
host variable . This host variable value is set from current timestamp by
using seperate SQL . Will there be any improvement in performance if we
directly use current timestamp special register ? Which one of the above is
a good practice ?

thanks ,
Kishore
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Michael McCarthy

Re: Insertion using host variable
(in response to kishore erukulapati)
Kishore,

Using the CURRENT TIMESTAMP special register for each INSERT will cause DB2
to get the system time for each INSERT. This will not improve performance.
However, if the TIMESTAMP must be unique, CURRENT TIMESTAMP must be used for
every INSERT. If multiple rows (or multiple tables in the UOW) need the
TIMESTAMP for future JOIN predicates or WHERE clauses, the SET of a host
variable is the way to go.

Hope this helps,

Mike.





kishore erukulapati <[login to unmask email]> on 12/28/99 02:19:09 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Michael McCarthy/DTC)
Subject: [DB2-L] Insertion using host variable




Hi ,
We have a DB2 table column which is defined as time stamp data type .One
application program does inserts into this table by using working storage
host variable . This host variable value is set from current timestamp by
using seperate SQL . Will there be any improvement in performance if we
directly use current timestamp special register ? Which one of the above is
a good practice ?

thanks ,
Kishore
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com








Richard A Yevich

Re: Insertion using host variable
(in response to Michael McCarthy)
In the first case, it WILL improve performance since there will now only be
1 SQL statement, and not 2.

Regarding UNIQUENESS - CURRENT TIMESTAMP is not guaranteed to be unique.
Obviously, if there is only one process doing inserts, then you are not
going to get duplicates. But when there is more than one concurrent job
doing inserts, then you can. It is not stated anywhere in any IBM
publication that I am aware of that CURRENT TIMESTAMP is guaranteed to be
UNIQUE.

If concurrent processes are inserting, then uniqueness must be handled by
any of several other techniques, including a re-insert when a duplicate is
encountered.

Richard Yevich

> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
> Michael McCarthy
> Sent: Tuesday, December 28, 1999 1:37 PM
> To: [login to unmask email]
> Subject: Re: Insertion using host variable
>
>
> Kishore,
>
> Using the CURRENT TIMESTAMP special register for each INSERT
> will cause DB2
> to get the system time for each INSERT. This will not improve
> performance.
> However, if the TIMESTAMP must be unique, CURRENT TIMESTAMP must
> be used for
> every INSERT. If multiple rows (or multiple tables in the UOW) need the
> TIMESTAMP for future JOIN predicates or WHERE clauses, the SET of a host
> variable is the way to go.
>
> Hope this helps,
>
> Mike.
>
>
>
>
>
> kishore erukulapati <[login to unmask email]> on 12/28/99 02:19:09 PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> To: [login to unmask email]
> cc: (bcc: Michael McCarthy/DTC)
> Subject: [DB2-L] Insertion using host variable
>
>
>
>
> Hi ,
> We have a DB2 table column which is defined as time stamp
> data type .One
> application program does inserts into this table by using working storage
> host variable . This host variable value is set from current timestamp by
> using seperate SQL . Will there be any improvement in performance if we
> directly use current timestamp special register ? Which one of
> the above is
> a good practice ?
>
> thanks ,
> Kishore
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
>
> To change your subscription options or to cancel your
> subscription visit the
>
>
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]