Using time stamp - Performance

kishore erukulapati

Using time stamp - Performance
We want to fetch rows from a table which are older than "N" (retention-days
) days
and write into a flat file for archival purpose using COBOL program.

We want to find a best solution to acheive this .

Solution :

Cursor was declared with the following where clause :

Days(:ws-current-date) - Days (creation_date_time) >= :retention-days . ( 2
host variables )

Alternative solution :

step-1 :

select char(date(days(:ws-current-date) - :retention-days),ISO)
into :archive-before-date
from a small table which has 150 rows.

step-2 :

Cursor declared with the following where clause :

Where cre_date_time <= :archive-before-date-time ( DB2 Time stamp )

which one of the above is the best solution ? Is there any other simple and
efficient way ?

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



Michael Hannan

Re: Using time stamp - Performance
(in response to kishore erukulapati)
kishore,

If you are on DB2 V5, then step 1 and step 2 of alternative solution can be
combined into 1 SQL and indexable on cre_date_time:

Where cre_date_time <= TIMESTAMP(DATE(:ws_current_date)
- (:retention_days) DAYS, TIME('00:00:00'))

On earlier versions of DB2, do it like this:

Where cre_date_time <=
(Select TIMESTAMP(DATE(:ws_current_date)
- (:retention_days) DAYS, TIME('00:00:00'))
FROM DUMMY_ONE_ROW_TABLE)

Dummy one row table must have exactly 1 row for this to work.
e.g. SYSIBM.SYSDUMMY1 available in recent DB2 version.

From: Michael Hannan

>From: kishore erukulapati <[login to unmask email]>
>Subject: Using time stamp - Performance
>To: [login to unmask email]
>
>We want to fetch rows from a table which are older than "N" (retention-days
>) days
>and write into a flat file for archival purpose using COBOL program.
>
>We want to find a best solution to acheive this .
>
>Solution :
>
>Cursor was declared with the following where clause :
>
>Days(:ws-current-date) - Days (creation_date_time) >= :retention-days . ( 2
>host variables )
>
>Alternative solution :
>
>step-1 :
>
>select char(date(days(:ws-current-date) - :retention-days),ISO)
>into :archive-before-date
>from a small table which has 150 rows.
>
>step-2 :
>
>Cursor declared with the following where clause :
>
>Where cre_date_time <= :archive-before-date-time ( DB2 Time stamp )
>
>which one of the above is the best solution ? Is there any other simple and
>efficient way ?
>
>Thanks ,
>Kishore
>______________________________________________________
>Get Your Private, Free Email at http://www.hotmail.com
>
>
>


>



Andrew (CALBRIS) Puddy

Re: Using time stamp - Performance
(in response to Michael Hannan)
Another option is use unload utility with a where clause.
This can also be used to unload the rest and reload to get rid of the
achived records
hth

Andrew Puddy
Phone 07 3867 1747
Mobile 0419 824 076



-----Original Message-----
From: kishore erukulapati [mailto:[login to unmask email]
Sent: Thursday, 6 January 2000 5:49
To: [login to unmask email]
Subject: Using time stamp - Performance


We want to fetch rows from a table which are older than "N" (retention-days
) days
and write into a flat file for archival purpose using COBOL program.

We want to find a best solution to acheive this .

Solution :

Cursor was declared with the following where clause :

Days(:ws-current-date) - Days (creation_date_time) >= :retention-days . ( 2
host variables )

Alternative solution :

step-1 :

select char(date(days(:ws-current-date) - :retention-days),ISO)
into :archive-before-date
from a small table which has 150 rows.

step-2 :

Cursor declared with the following where clause :

Where cre_date_time <= :archive-before-date-time ( DB2 Time stamp )

which one of the above is the best solution ? Is there any other simple and
efficient way ?

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






Attachments

  • Puddy, Andrew (CALBRIS).vcf (<1k)

Bill Brown

Re: Using time stamp - Performance
(in response to Andrew (CALBRIS) Puddy)
If the retention perion remains constant I would recommend calculating the date in an initialization routine ONE TIME and placing the result in a host variable. By using this method DB2 doesn't have to calculate the date each time.

On Thu, 6 Jan 2000 10:32:46 +1000 Michael Hannan <[login to unmask email]> wrote:

kishore,

If you are on DB2 V5, then step 1 and step 2 of alternative solution can be
combined into 1 SQL and indexable on cre_date_time:

Where cre_date_time <= TIMESTAMP(DATE(:ws_current_date)
- (:retention_days) DAYS, TIME('00:00:00'))

On earlier versions of DB2, do it like this:

Where cre_date_time <=
(Select TIMESTAMP(DATE(:ws_current_date)
- (:retention_days) DAYS, TIME('00:00:00'))
FROM DUMMY_ONE_ROW_TABLE)

Dummy one row table must have exactly 1 row for this to work.
e.g. SYSIBM.SYSDUMMY1 available in recent DB2 version.

From: Michael Hannan

>From: kishore erukulapati <[login to unmask email]>
>Subject: Using time stamp - Performance
>To: [login to unmask email]
>
>We want to fetch rows from a table which are older than "N" (retention-days
>) days
>and write into a flat file for archival purpose using COBOL program.
>
>We want to find a best solution to acheive this .
>
>Solution :
>
>Cursor was declared with the following where clause :
>
>Days(:ws-current-date) - Days (creation_date_time) >= :retention-days . ( 2
>host variables )
>
>Alternative solution :
>
>step-1 :
>
>select char(date(days(:ws-current-date) - :retention-days),ISO)
>into :archive-before-date
>from a small table which has 150 rows.
>
>step-2 :
>
>Cursor declared with the following where clause :
>
>Where cre_date_time <= :archive-before-date-time ( DB2 Time stamp )
>
>which one of the above is the best solution ? Is there any other simple and
>efficient way ?
>
>Thanks ,
>Kishore
>______________________________________________________
>Get Your Private, Free Email at http://www.hotmail.com
>
>
>


>






Sibimon Philip

Re: Using time stamp - Performance
(in response to Bill Brown)
Bill,

Where cre_date_time <= TIMESTAMP(DATE(:ws_current_date)
- (:retention_days) DAYS, TIME('00:00:00'))

In the above SQL, will DB2 calculate timestamp for each record? Since the
retention period is populated before opening the cursor, I think timestamp
is calculated only once.

Thanks
Sibimon Philip
972-702-2515 - Office
972-417-3597 - Residence
E-mail - [login to unmask email]


-----Original Message-----
From: Bill Brown [mailto:[login to unmask email]
Sent: Thursday, January 06, 2000 6:23 AM
To: [login to unmask email]
Subject: Re: Using time stamp - Performance


If the retention perion remains constant I would recommend calculating the
date in an initialization routine ONE TIME and placing the result in a host
variable. By using this method DB2 doesn't have to calculate the date each
time.

On Thu, 6 Jan 2000 10:32:46 +1000 Michael Hannan <[login to unmask email]>
wrote:

kishore,

If you are on DB2 V5, then step 1 and step 2 of alternative solution can be
combined into 1 SQL and indexable on cre_date_time:

Where cre_date_time <= TIMESTAMP(DATE(:ws_current_date)
- (:retention_days) DAYS, TIME('00:00:00'))

On earlier versions of DB2, do it like this:

Where cre_date_time <=
(Select TIMESTAMP(DATE(:ws_current_date)
- (:retention_days) DAYS, TIME('00:00:00'))
FROM DUMMY_ONE_ROW_TABLE)

Dummy one row table must have exactly 1 row for this to work.
e.g. SYSIBM.SYSDUMMY1 available in recent DB2 version.

From: Michael Hannan

>From: kishore erukulapati <[login to unmask email]>
>Subject: Using time stamp - Performance
>To: [login to unmask email]
>
>We want to fetch rows from a table which are older than "N" (retention-days
>) days
>and write into a flat file for archival purpose using COBOL program.
>
>We want to find a best solution to acheive this .
>
>Solution :
>
>Cursor was declared with the following where clause :
>
>Days(:ws-current-date) - Days (creation_date_time) >= :retention-days . ( 2
>host variables )
>
>Alternative solution :
>
>step-1 :
>
>select char(date(days(:ws-current-date) - :retention-days),ISO)
>into :archive-before-date
>from a small table which has 150 rows.
>
>step-2 :
>
>Cursor declared with the following where clause :
>
>Where cre_date_time <= :archive-before-date-time ( DB2 Time stamp )
>
>which one of the above is the best solution ? Is there any other simple
and
>efficient way ?
>
>Thanks ,
>Kishore
>______________________________________________________
>Get Your Private, Free Email at http://www.hotmail.com
>
>
>


>













Terry Purcell

Re: Using time stamp - Performance
(in response to Sibimon Philip)
Sibimon,

You are correct, because the calculation does not involve any table columns
(ie. non-column expression); it will be calculated once only. The same
applies to the second example provided by Michael Hannan (this is why they
are both stage 1 and indexable in V5 & pre-V5 respectively).

Regards,
Terry Purcell
CPT Consulting

> -----Original Message-----
> From: Philip, Sibimon [SMTP:[login to unmask email]
> Sent: Friday, 7 January 2000 2:41 am
> To: [login to unmask email]
> Subject: Re: Using time stamp - Performance
>
> Bill,
>
> Where cre_date_time <= TIMESTAMP(DATE(:ws_current_date)
> - (:retention_days) DAYS, TIME('00:00:00'))
>
> In the above SQL, will DB2 calculate timestamp for each record? Since the
> retention period is populated before opening the cursor, I think timestamp
> is calculated only once.
>
> Thanks
> Sibimon Philip
> 972-702-2515 - Office
> 972-417-3597 - Residence
> E-mail - [login to unmask email]
>
>
> -----Original Message-----
> From: Bill Brown [mailto:[login to unmask email]
> Sent: Thursday, January 06, 2000 6:23 AM
> To: [login to unmask email]
> Subject: Re: Using time stamp - Performance
>
>
> If the retention perion remains constant I would recommend calculating the
> date in an initialization routine ONE TIME and placing the result in a
> host
> variable. By using this method DB2 doesn't have to calculate the date
> each
> time.
>
> On Thu, 6 Jan 2000 10:32:46 +1000 Michael Hannan
> <[login to unmask email]>
> wrote:
>
> kishore,
>
> If you are on DB2 V5, then step 1 and step 2 of alternative solution can
> be
> combined into 1 SQL and indexable on cre_date_time:
>
> Where cre_date_time <= TIMESTAMP(DATE(:ws_current_date)
> - (:retention_days) DAYS, TIME('00:00:00'))
>
> On earlier versions of DB2, do it like this:
>
> Where cre_date_time <=
> (Select TIMESTAMP(DATE(:ws_current_date)
> - (:retention_days) DAYS, TIME('00:00:00'))
> FROM DUMMY_ONE_ROW_TABLE)
>
> Dummy one row table must have exactly 1 row for this to work.
> e.g. SYSIBM.SYSDUMMY1 available in recent DB2 version.
>
> From: Michael Hannan
>
> >From: kishore erukulapati <[login to unmask email]>
> >Subject: Using time stamp - Performance
> >To: [login to unmask email]
> >
> >We want to fetch rows from a table which are older than "N"
> (retention-days
> >) days
> >and write into a flat file for archival purpose using COBOL program.
> >
> >We want to find a best solution to acheive this .
> >
> >Solution :
> >
> >Cursor was declared with the following where clause :
> >
> >Days(:ws-current-date) - Days (creation_date_time) >= :retention-days . (
> 2
> >host variables )
> >
> >Alternative solution :
> >
> >step-1 :
> >
> >select char(date(days(:ws-current-date) - :retention-days),ISO)
> >into :archive-before-date
> >from a small table which has 150 rows.
> >
> >step-2 :
> >
> >Cursor declared with the following where clause :
> >
> >Where cre_date_time <= :archive-before-date-time ( DB2 Time stamp )
> >
> >which one of the above is the best solution ? Is there any other simple
> and
> >efficient way ?
> >
> >Thanks ,
> >Kishore
> >______________________________________________________
> >Get Your Private, Free Email at http://www.hotmail.com
> >
> >
> >
>
>
> >
>
>
>
> the
>
>
>
>
>
> the
>
>
>
>
>
>
>