How can we find when was the maximum LOAD utilites (Fload/Mload/Fexport) were running.

Database
Teradata Employee

How can we find when was the maximum LOAD utilites (Fload/Mload/Fexport) were running.

Hi all,

We are facing issue like "Too many load/unload tasks are running" due to maximum utilies limit reached. but how can we find when or at what time maximum fastload/mload, exports were running or load limit reached in system. is there any way to find through DBQL/Resusage or any other way.

some time we use rewind option(viewpoint) to check how many load utilies were running but i want to find when this situation was happen.

Please suggest.

6 REPLIES
N/A

Re: How can we find when was the maximum LOAD utilites (Fload/Mload/Fexport) were running.

Sandeep,

Appid or Queryband can be used to identify utilites in dbqlog.

Use with sessionid,starttime,firstresptime  and you should be able to identify peaks.

Rglass

Teradata Employee

Re: How can we find when was the maximum LOAD utilites (Fload/Mload/Fexport) were running.

thnks Glass

Re: How can we find when was the maximum LOAD utilites (Fload/Mload/Fexport) were running.

Sandeep -

To find distinct Utility jobs running on system at any given time use Appid + LSN (logon sequence number).

SEL lsn,clientaddr,username,appid,MIN(starttime) AS load_starttime,MAX(firstresptime)  AS load_endtime

FROM pdcrinfo.dbqlogtbl_hst

WHERE appid LIKE ('%LOAD%')

 AND logdate BETWEEN current_date -7 AND current_date

 AND lsn > 0

GROUP BY 1,2,3,4

;

Re: How can we find when was the maximum LOAD utilites (Fload/Mload/Fexport) were running.

Sandeep -

To find distinct Utility jobs running on system at any given time use Appid + LSN (logon sequence number).

SEL lsn,clientaddr,username,appid,MIN(starttime) AS load_starttime,MAX(firstresptime)  AS load_endtime

FROM pdcrinfo.dbqlogtbl_hst

WHERE

 appid LIKE ('%LOAD%','%EXP%')

 AND logdate BETWEEN current_date -7 and current_date

 AND lsn > 0

GROUP BY 1,2,3,4

;

Thanks!!

Teradata Employee

Re: How can we find when was the maximum LOAD utilites (Fload/Mload/Fexport) were running.

Hi Velu,

Thanks for the response.

still i am not able to find at what time maximum loads utilities were running in system. Please help how to use above quey output.

Re: How can we find when was the maximum LOAD utilites (Fload/Mload/Fexport) were running.

Summarize the data for 10min interval to get number of Utility Jobs (Load/Export) started on the system at high level

SEL CAST(load_starttime as DATE) as The_Date,

(EXTRACT (HOUR FROM load_starttime)) as The_Hr,

(EXTRACT (MINUTE FROM load_starttime)/10)*10 as Log10Min,

Appid,

Count(LSN) as UtilJobCnt

FROM

(

SEL lsn,clientaddr,username,appid,MIN(starttime) AS load_starttime,

MAX(firstresptime)  AS load_endtime

FROM pdcrinfo.dbqlogtbl_hst

WHERE

 appid LIKE ('%LOAD%','%EXP%')

 AND logdate BETWEEN current_date -7 and current_date

 AND lsn > 0

GROUP BY 1,2,3,4

) x

GROUP BY 1,2,3,4

;

If you look for further granularity modify the query for minute interval. Copy the query results to Excel and plot stacked line chart for better presentation.

(EXTRACT (MINUTE FROM load_starttime)  ) /10)*10

-to-

(EXTRACT (MINUTE FROM load_starttime)  ) 

Thanks!!