Regarding StatementText in accesslog

Database
Enthusiast

Regarding StatementText in accesslog

While i am executing this query it is showing zero records 

sel  StatementText

 from dbc.accesslog where  UserName=  'abcd' and LogonDate=11/28/2013;

But the user abcd has fired queries on the same date.

Can any one pls help...

Thanks in advance

4 REPLIES
Senior Apprentice

Re: Regarding StatementText in accesslog

Your WHERE-condition on LogonDate is not valid, 11/28/2013 is not a DATE, it's a numeric calculation resulting in an INTEGER 0.

sel  StatementText
from dbc.accesslog where UserName= 'abcd' and LogonDate=DATE '2013/11/28';
Enthusiast

Re: Regarding StatementText in accesslog

Hi Dieter,

Thanks for the reply but when i used the below query 

sel  StatementText

from dbc.accesslog where  UserName=  'abcd' and LogonDate= '2013/11/28';

Its taking '2013/11/28' as date variable as for Explain paln

 Next, we do an all-AMPs RETRIEVE step from DBC.AccLogTbl in view

     accesslog by way of an all-rows scan with a condition of (

     "(DBC.AccLogTbl in view accesslog.LogonDate = DATE '2013-11-28')

     AND ((TRANSLATE((DBC.AccLogTbl in view accesslog.UserName )USING

     UNICODE_TO_LOCALE WITH ERROR )(CHAR(30), CHARACTER SET LATIN, NOT

     CASESPECIFIC))= 'abcd ')")

But not retriving rows. Its showing Zero rows. Please help me

Regards,

Praveen.

Senior Apprentice

Re: Regarding StatementText in accesslog

Hi Praveen,

if this was an ANSI session it might be due to case sensitivity, but based on explain you seem to run in Teradata session mode.

Are you shure that Access Logging is switched on for that user/object combination? 

Enthusiast

Re: Regarding StatementText in accesslog

Hi Dieter,

I am not sure on that . I will check with it. Thanks.

Regards,

Praveen.