Archives of the TeradataForum

Message Posted: Fri, 04 Mar 2016 @ 09:14:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I use below query to calculate number of sessions used by each fastload job separately - can be easily rewritten for multiload.

It returns correct results but is based on undocumented magic number, which can be found in column logonsource. Without referencing this number it is impossible to distinguish technical sessions used by fastloads run at the same time.

The magic value may be repeated during the same day, so condition based on start and stop time are also required.

     lock row access
     sel sessionid
       , max(lsn)   LSN
       , max(a.t1)   t1
       , max(a.t2)   t2
       , max(loadingtime)   loadingtime
       , count(*)   #sessions
       , max(table_Loaded)   table_loaded
       , max(rowcnt)(integer)   rowcnt

      from
      (
       select sessionid
         , max(case when appid='fastload'
                    then 0
                    else 1
                    end)   utype
         , max(lsn) lsn
         , max( case when statementtype='begin loading'
                     then starttime
                     else null
                     end)   t1
          , max( case when statementtype='end loading' or abortflag='T'
                      then firstresptime
                      else null
                      end)   t2
          , max( case when statementtype='end loading'
                      then utilityrowcount
                      else 0
                      end )   rowcnt
          , '% ' || trim(max(substr(logonsource,index(logonsource,'  '),10) (varchar(16))  )) || ' %'   lookfor1
          , '% ' || trim(max(appid)) || ' %'   lookfor2
          , (t2-t1 hour(2) to second(2) )   loadingtime
          , max(username)   username
          , max( case when statementtype='begin loading'
                      then substr(querytext ,15, index(querytext,' errorfiles ') -15)
                      else null
                      end   (varchar(32))
               )   table_loaded
        from dbc.DBQLOGTBL
           where appid in ( 'tptload','fastload' )  and logdate= ........
           group by 1
           having t1 is not null and t2 is not null
          ) a

      join (
         select logonsource,sessionno
           , cast(logondate as timestamp(2)) + cast(cast(cast(logontime as format '99:99:99.99')
                  as char(11)) as interval hour to second(2))   t1
           , cast(logdate as timestamp(2)) +  cast(cast(cast(logtime as format '99:99:99.99')
                  as char(11)) as interval hour to second(2))   t2
           from dbc.logonoff
           where logdate= ........ and event='logoff'
            and sessionno not in ( select sessionid from DBC.DBQLOGTBL  where logdate= ..... group by 1) )   b
           on b.logonsource like a.lookfor1 and b.logonsource like a.lookfor2 and b.t1 < a.t2
           and b.t2 > a.t1  group by a.sessionid

	<Prev	Next>		<<First	<Prev	Next>	Last>>