SQL History

Database
Enthusiast

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
Tags (4)
10 REPLIES
Senior Apprentice

Re: SQL History

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
Enthusiast

Re: SQL History

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.
Senior Apprentice

Re: SQL History

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
Enthusiast

Re: SQL History

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 :)
Enthusiast

Re: SQL History

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.

Enthusiast

Re: SQL History

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.
Enthusiast

Re: SQL History

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.
Enthusiast

Re: SQL History

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.
Enthusiast

Re: SQL History

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