All Forums Database
Flavien 5 posts Joined 07/10
27 Jul 2010
SQL History

Hello,

I'm currently working on Teradata SQL Assistant and i'm really interested in the History Panel.
I would like to find the same result as this panel, but in a SQL way, historically, for all my users etc etc ... is there any table with this informations ?
The solution needed is just to see all DML statements that happened on my database. So the type of order, the table(s) on where the statement was executed, and the error code or the number of lines ...
I didnt found it in DBC and Teradata lack of community so i hope your help will solve my problem !

Thanks to you all, Flavien

dnoeth 4628 posts Joined 11/04
27 Jul 2010

Hi Flavien,
there are two kinds of logs in Teradata:
- AccessLog, mainly a security log, guaranteed to be written, but the souce code logged is limited to 8KB.

But you probably want
- QueryLog, mainly a performance log, might loose some info, but records the full source code plus a lot of details.
Current data can be queried using views in dbc starting with QryLog and is probably moved into a history database once after a while.

You should talk to your dba, which log is enabled on your system and ask for access to it.

Dieter

Dieter

Flavien 5 posts Joined 07/10
27 Jul 2010

Thanks, it seems it's exactly what i want, but then comes further problems :

- I'm currently logging for every user, but does it also logs every database or just one ?
- Is my view QryLog instantly refreshed ?
- What is the size limit of that view ?
- On what table this view is based on ? Can i purge it ?

Flavien.

dnoeth 4628 posts Joined 11/04
27 Jul 2010

Hi Flavien,

- I'm currently logging for every user, but does it also logs every database or just one ?
the querylog is not based on accessed databases (this might be done with AccessLog), but on accessing users/accounts.

- Is my view QryLog instantly refreshed ?
The data is collectec in buffers which are flushed regularly (not later than 10 minutes by default)

- What is the size limit of that view ?
"size limit"? Number of rows? There's no limit other than the available perm space in dbc (that's why it's moved into a history db)

- On what table this view is based on ? Can i purge it ?
dbc.DBQLogTbl et al.
If you're a dba (or got appropriate rights), you can purge it.
There are some scripts available for download to purge/maintain these tables.

You'll find lots of details in the Database Administration manual,
CHAPTER 13 Tracking Processing Behavior with the Database Query Log (DBQL)

Dieter

Dieter

Flavien 5 posts Joined 07/10
27 Jul 2010

Thanks for that help, and i also read that Chapter 13.

Anyway that solution won't be accepted, my client doesn't wants me to have the rights to delete on the DBC, and i can't have that table to gain too much weight, or it will become really bad :)

robpaller 159 posts Joined 05/09
27 Jul 2010

The recommended practice is for DBQL data to be regularly moved to a historical database that is designed to facilitate analytical queries of the data (e.g. compression and multilevel PPI) and purge the unneeded data from the DBC tables. This should be covered in the manuals that Dieter has referenced.

Flavien 5 posts Joined 07/10
27 Jul 2010

Yes i agree Robpaller. But i don't have the rights to delete on the DBC tables, and my client will never let it happen.

monisiqbal 119 posts Joined 07/09
02 Aug 2010

Just to add a bit to Dieter's answer:

- Is my view QryLog instantly refreshed ?
>The data is collectec in buffers which are flushed regularly (not later than 10 minutes by default)
It depends on the amount of data being logged, size of the queries etc. because there is a buffer size for data being logged for DBQLogTbl (QryLog) and once the buffer is filled (or END QUERY LOGGING is executed) then only it is written to disk i.e. visible in the database. So in cases when there is extremely low load on your system, the logging interval can be greater.

robpaller 159 posts Joined 05/09
02 Aug 2010

Flavien, the point was your client's DBA (unless that's you) should be doing that already if they are using DBQL to log the systems query workload. Then using views of the DBC DBQL tables you should be able to access logged information that has not been swept to the history database if your needs are closer to real time and not yesterday's logged work.

LUCAS 56 posts Joined 06/09
18 Apr 2012

Hi everybody,

I use to request on dbc.qrylog (v13.10) for a while, and i just wonder why "NumResultRows" is never filled (SpoolUsage is OK).

Something i missed ? another column to keep the number of rows returned by Insert, delete, update ?

Thanks for help,

Pierre

jdarton 4 posts Joined 10/05
19 Apr 2012

Flavien, this probably comes too late for your purposes, but...

Our DBA doesn't allow read access to DBA.QryLog either.  Fotunately, SQL Assistant stores the query history in an access database.  A user can query the SQL in access.  You can find the location of the database by clicking: Tools > Options > File Paths > History File

I placed the access database on a network folder and point all instances of SQL Assistant within my department to that location.  That allows each employee in the department to share SQL in an easy, automated fashion.  We even built a web GUI to search and display the queries.

 

You must sign in to leave a comment.