Failure due to unavailability of loader slots in Teradata

Hi All,

In my application, we are facing issue of failure due to unavailability of the loader slots. The utilities (fastload,Mload and Fast export) are triggered using the powercentre ETL tool. The powercentre workflow is in turn controlled via CA7 mainframes jobs for all the applications in my company.As there are many batches running at the same time for various projects thereby I feel that the maximum loader slots are consumed during some peak interval leading to this issue.

I have tried to analyse the total number of active sessions at each hour during the day as below :

Step 1 : Created a volatile table to store some values from the DBQLogTbl

Create volatile table XYZ
(session_id integer
,no_of_sessions integer
,start_time timestamp(2),
,end_time timestamp(2))
on commit preserve rows;

STEP 2: Inserted all the rows during the day corresponding to fast export,fastload and Mload along with the session start and end time.( Not sure whether the loader slot will be occupied till the start and end time of the session?)

insert into XYZ
select
sessionID
,count(1)
.min(starttime)
,max(starttime)
from DBC.DBQLogTbl
where appid in ('TPTEXP','TPTLOAD','TPTUPD')
group by 1;

Step 3 : extracted the total number of sessions during the time 01:00 till 01:59 by using the below query

select sum(no_of_sessions) from XYZ
where extract (hour from start_time) = 01

Once I get the data for all the hour interval, I can select some of the intervals with peak sessions and then check the username with the help of sessionID to identify the project name. Once we identify the list of the batches running simultaneously for different projects,the batch trigger time can be moved to avoid conflicts.

Can anyone please advice if this is a correct approach or can suggest some more efficient way of doing this.

Also, let me know if for step3, I can get the total no of sessions for each hour with a single query instead of manually editing the hour.

Thanks for your help!!