Archives of the TeradataForum

Message Posted: Mon, 01 Oct 2012 @ 23:34:24 GMT

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

In order to drop user Ids that have not logged in for 90 or more days, a number of things have to be considered. To only look at the dbc.LogonOff view, misses users who have never logged on. So to truly drop inactive Id's, you would need to SELECT from a dbc.Users view to get all users, and then LEFT OUTER JOIN to the dbc.LogonOff view, to get the maximum LogDate. You would only want to select users from the dbc view who were created more than 90 days ago and who had never logged on (NULL LogDate from dbc.LogonOff) or had a MAX(LogDate) older than 90 days. However, there are conditions you need to account for.

1) You have to retain data in the dbc.LogonOff table for at least 90 days

2) You need to not drop Id's that rarely or never log on, such as System_FE, Crashdumps, spool_reserve, etc.

3) You need to assure that dbc.LogonOff is never truncated (TD will sometimes completely purge this table during upgrades, which will make it look to your process that no one has ever logged on, causing you to drop Id's that shouldn't be dropped).

Regards,

Jim

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