All Forums Database
Proactive 10 posts Joined 07/14
09 Jul 2014
SELECT failed 7453 : interval field overflow

I want to get querycount which ran less than 30 secs, For that I wrote this query but its giving SELECT failed 7453 : interval field overflow. For that I tried to fetch record for five days only 

WHERE CollectTimeStamp between '2014-06-01 00:00:00' and '2014-06-05 23:59:59' 

and its worked(count(1)=172,963) . But for next five days 

WHERE CollectTimeStamp between '2014-06-05 00:00:00' and '2014-06-10 23:59:59' 

it again threw same error now I fetched for one day 

WHERE CollectTimeStamp between '2014-06-05 00:00:00' and '2014-06-06 23:59:59' 

it worked again(answerset count(1)=65407). 

 

My question is Why its behaving like this? How to overcome this error. cz here as i can see only changing time intervals in condition WHERE CollectTimeStamp between '2014-06-05 00:00:00' and '2014-06-06 23:59:59' effecting result. How to successfully run it for whole month i.e. 30 days

 

For reference:

Query used is

-------------------------

LOCKING ROW FOR ACCESS

SELECT count(1)

FROM DBC.DBQLOGTBL

WHERE CollectTimeStamp between '2014-06-01 00:00:00' and '2014-06-30 23:59:59' 

AND USERNAME='Proactive_USER'

and statementtype='SELECT'

and ((FIRSTRESPTIME - FIRSTSTEPTIME) second (4)) <= 30;

 

---------------------------------

And datatypes format for columns used:

 

CREATE MULTISET TABLE dbc.DBQLOGTBL ,FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

CollectTimeStamp TIMESTAMP(2) FORMAT 'YYYY-MM-DDBHH:MI:SS' NOT NULL,

StartTime TIMESTAMP(2) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z' NOT NULL,

 FirstStepTime TIMESTAMP(2) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z' NOT NULL,

 FirstRespTime TIMESTAMP(2) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z',

StatementType CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

UserName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC);

PRIMARY INDEX ( ProcID ,CollectTimeStamp );

 

 

@moderator plz move it to correct forum if u feel it not appropriate for database forum.

 

Thanks in advance.

P.s. Any other way to get querycount from dbc also welcomed as i just did it in beginner level. :)

dnoeth 4628 posts Joined 11/04
10 Jul 2014

There were queries running longer than 9999 seconds = 02h 46m 39s, so simply increase the interval. 
When you check the source for dbc.qryLogV you will notice a column named ElapsedTime which is calculated as ((firstresptime - starttime) hour(4) to second).
And then your condition will be

WHERE ElapsedTime <= INTERVAL '30' SECONDS

 

Dieter

Proactive 10 posts Joined 07/14
10 Jul 2014

Hey,
Thank u so much.Really helpful!!
but what if i write this
------------------------
 LOCKING ROW FOR ACCESS
SELECT count(1)
FROM DBC.DBQLOGTBL
WHERE CollectTimeStamp between '2014-06-01 00:00:00' and '2014-06-30 23:59:59' 
AND USERNAME='Proactive_USER'
and statementtype='SELECT'
and ((FIRSTRESPTIME - FIRSTSTEPTIME) hour(4) to second ) <= 30;
-------------------
(as i want execution time)
 but its not running saying select failed 5407: invalid operation date time or interval.
or i should add it in view another column ((FIRSTRESPTIME - FIRSTSTEPTIME) hour(4) to second ) (Named ExecutionTime ) ??
 

dnoeth 4628 posts Joined 11/04
10 Jul 2014

You can't compare an HOUR TO SECOND interval to an INTEGER. What is 30 supposed to be. hours, minutes or seconds?

((FIRSTRESPTIME - FIRSTSTEPTIME) hour(4) to second ) <= INTERVAL '30' SECOND;

 

Dieter

You must sign in to leave a comment.