Can anybody please tell me how to find number of concurrent users firing query in teradata at a particular time in PAST.. lets say yesterday.
Can we do this in Pmon ( although we can see all the active users in Pmon, I want to see no. of maximum concurrent users at a specific point of time in past).
Please do reply..Its very urgent!!
I have searched on net.. we can find out the Users Count in Teradata Manager.. via Analyze>Trends>User Count.
But I am getting "No Rows found". I have changed the filter values also..but same result. :(
Actually for all the options in Trend tab,I am getting the same result: No Rows Found
Else all the options in Analyze tab e.g. Resource Histort etc are showing proper result .
Please let me know if any one has information regarding this issue.
Thanks a lot in advance :)
You would need to enable the data collection process before you can analyse historic data. Seems that this is not active in your environment.
Do you log all queries in DBQL on detail level?
If yes you could have a chance to get the info you search there.
thanks a lot ulrich for the information..
yes we do collect data in DBQL tables
Can u please tell me which table shud I look for...
I am searching in DBC.LogOnOff table... I want to find the number of concurrent users daily... i.e. number of users firing query simultaniously daily (atleast their logon time is same)
The definition of DBC.LogOnOff is as below:
REPLACE VIEW DBC.LogOnOff
SELECT Datefld(NAMED LogDate),
CAST(TRANSLATE(UserName USING UNICODE_TO_LOCALE WITH ERROR)
AS CHAR(30)) (NAMED UserName),
CAST(TRANSLATE(AccountName USING UNICODE_TO_LOCALE WITH ERROR)
AS CHAR(30)) (NAMED AccountName),
TRANSLATE(LogonSource USING UNICODE_TO_LOCALE WITH ERROR)
FROM DBC.EventLog WITH CHECK OPTION;
Please tell me how to do it..
You can use below sql to get concurrent user.
SEL count(*) FROM DBC.LOGONOFF
WHERE LogonDate = '2012/06/25'
AND Event = 'Logoff'
AND 120000 BETWEEN LogonTime AND LogTime
dbc.LogonOff is only information about when a session logged on/off, only dbc.QryLog carries info about executed queries.
So it depends on the definition of "concurrent":
- number of users logged on -> LogonOff
- number of sessions logged on -> LogonOff
- number of queries running in parallel -> QryLog, but this will not count those fast tactical queries which are usually not logged in QryLog, but in QryLogSummary
As a DBA, In teradata system how do you find which users are using hardcoded IP's address instaed of hostnames.
I treied eventlogs but does not seems helpful.
Please refer to the Teradata Database "Data Dictionary" Reference, Chapter 3 "Views Reference", the LogOnOffV[X] views. The following query should provide the kind of information that you're looking for:
select distinct ClientSystemUserId, ClientIpAddress, ClientTdHostName from DBC.LogOnOffV order by 1,2,3
The ClientSystemUserId column indicates the user name of the user on the client machine that connected to the Teradata Database. (It is not the Teradata Database user name.) This can help identify the end user.
The ClientIpAddress column indicates the IP address of the client machine. This can help identify the end user.
The ClientTdHostName column indicates the original hostname (before COP Discovery) that the application specified to the Teradata client interface software (JDBC Driver, ODBC Driver, etc.) and this column will contain an IP address if the application specified an IP address instead of a hostname.
You want to look for IP addresses in the third column returned by that query, because they will indicate connections made via an IP address instead of a hostname. The values in the first and second columns will help you identify the users and/or applications that are making those connections.