 |
|
|
 | Privileges and Privilege Subjects |  | 602SQL Server | System Triggers |  |
SQL server operation can be traced using the tracing tools and logged into text files called logs. You can get various information from logs:
The configuration administrator may specify which information should be traced and logged (except for tracing of data reading and writing, which is enabled by the security administrator) - see SQL server administration.
If you need to trace more information types, you can define individual logs. Each log has its own name with the same restrictions as other objects' names in 602SQL.
A SQL server always automatically generates a basic server log marked with an empty name. Other logs have to be defined and since then exist until the server is terminated. Each log is recorded into a text file (outside the database). The basic log is recorded in to the wbsqllog.txt file and located in a folder specified in the Database management window, Files tab as the Basic log (by default the same folder that houses the database file). Files for the user defined logs are set when creating these logs.
If a file already exists when defining a log, new entries will be appended to the file. If the file doesn't exist, it's created.
New log can be defined as follows : open the Monitor window from the 602SQL Development Client on the Trace tab. Select the Create a new temporary log action from the popup menu anywhere in the grid. A window appears, where you may set the logname, name and location of the log file and log format (if the log should have a nondefault format - overview of formats is provided at the _sqp_define_log function description).
New log can be defined from a program by calling the standard _sqp_define_log procedure. Only the configuration administrator may define logs.
If you need to create a certain log on each server startup, add a call of the _sqp_define_log function into the system trigger for a server start (_on_server_start).
Log files may grow very big in size and complexity after a long-term intensive logging. The SQL server may create special logs for each day. You can enable this with the DailyBasicLog property for the basic log and with the DailyUserLogs property for other logs.
If this property is enabled, then the server appends the date to the log name in the form RRMMDD. After each midnight, the server begins a new separate log file. I.e. a log named SQLLOG from April 19th 2003 will be located in the SQLLOG030419.txt file.
Server logs its operation with respect to the specified tracing requests. A request is valid until it's disposed or until the server is terminated. Each request has these parameters:
There may be more valid requests in the same time for logging the same situation, same user and same objects into different logs. If you specify a request to log a new situation, new user or new object, or a new log, it won't affect the previous requests.
Tracing requests may be viewed, inserted, altered or deleted manually on the Trace tab of the Monitor window. The list of valid tracing requests can be obtained by the _iv_pending_log_reqs system query. Tracing requests can be set and deleted in a program by calling the standard _sqp_trace procedure. This requests may only be specified by the configuration administrator (data tracing requests only by the security administrator) - see SQL server administration.
Situations that may be logged are marked by identifiers stated in the first column when calling the _sqp_trace function. Letters shown in the second column are bound with the specific situation and appear in the log. The fourth column shows constant values, that are returned by the _iv_pending_log_reqs system query. Situations can be divided into three classes:
Global situation concerning the server runtime:
| Identifier | Description | Number | |
| TRACE_SERVER_FAILURE | F | global server failure (including network) | 2 |
| TRACE_NETWORK_GLOBAL | N | global network communication tracing | 4 |
| TRACE_START_STOP | A | information about starting and terminating server, and about errors that prevent a server to be executed | 16384 |
| TRACE_SERVER_INFO | B | basic information about server runtime (e.g. creating backup, disconnecting idle users) and information requested in the server console (e.g. show list of connected users) | 65536 |
| TRACE_BCK_OBJ_ERROR | O | a syntax error in an object that the server works with in the background - it may be a trigger, global declaration in a scheme, default column value or an integrity restriction in a table design | 2097152 |
No user have to be specified for logging these information.
Situations concerning actions of a specific client:
| TRACE_USER_ERROR | E | a user error | 1 |
| TRACE_SQL | S | executing a SQL statement (including prepared statements), the statement text including the values of dynamic parameters and client variables can be viewed in the context, therefore it's recommended to set the context for a value of 2 or 3 | 64 |
| TRACE_LOGIN | L | connecting and disconnecting of a client, login and logout of a user | 128 |
| TRACE_LOG_WRITE | W | explicit write to a log by calling the Log_write procedure | 1024 |
| TRACE_CURSOR | P | opening and closing of cursors, including the appropriate SELECT statement, if it's detectable | 256 |
| TRACE_IMPL_ROLLBACK | V | executing of a default RollBack operation due to an error | 512 |
| TRACE_PROCEDURE_CALL | P | calling a SQL procedure | 4194304 |
| TRACE_TRIGGER_EXEC | T | triggering a trigger | 8388608 |
| TRACE_USER_WARNING | I | user warning | 16777216 |
| TRACE_LOCK_ERROR | G | details about the error 136 (NOT_LOCKED - only in case when lock-timeout elapsed) and about the error 171 (DEADLOCK) | 67108864 |
| TRACE_WEB_REQUEST | H | URL with a HTTP request send to SQL server from the Web XML interface (only if web server emaulation is on) | 134217728 |
| TRACE_CONVERTOR_ERROR | J | error messages sent by an external fulltext plug-in (convertor) | 2684354568 |
You have to specify either a username of the user to log this information about his actions or leave an empty password to log all users actions.
The last situation type is concerning the table manipulation. You can log:
| TRACE_INSERT | i | inserting a new record into a table | 524288 |
| TRACE_DELETE | d | deleting table record | 1048576 |
| TRACE_WRITE | w | updating a record value in a table | 262144 |
| TRACE_READ | r | reading a value from a table record | 131072 |
Usually a specific table is set for these situations. If none is set, all tables except the system ones are traced. You can further specify the user whose actions should be logged or leave an empty user name to log all users actions.
A logging request for an user evoked action may be:
If a tracing request is bound with a username, then it applies to all clients already connected with this name as well as to the clients that will log in at a later time.
All logs are being recorded continuously into the files. These files can be viewed on various computers using the file network sharing tools. All clients may read the basic log. The other defined logs may be read only by the security administrator.
The most recent basic log entries are also visible in the server window.
A client may get the most recent entries of any log using the system query _iv_recent_log (a client who's not an administrator may view only the basic log). These records appear in the Monitor window on the Basic log tab. A client may get the tracing requests by calling the system query _iv_pending_log_reqs. The data amount that is accessible for the client is limited by the TraceLogCacheSize server property.
Mainly the user error information, if the full context is enabled, may be complex and hard to understand. Let's look at the common "Key duplicity" error. You can read the following in the log:
19.6. 11:24:49 E ANONYMOUS Key duplicity in table POMTAB2 (4700) occurred (40004) {172} [Key 1 : 2] [Table 16 (POMTAB2), index 0 (INDEX1)][Inserting to table 16, record 16000]
[Procedure TEST3_2] [Procedure TEST3_ENVELOPE] [SQL: CALL `TESTY`.`TEST3_ENVELOPE`(:<HOWMANYTIMES{2})]
This tells us the following: a record was inserted into the POMTAB2 table that collides with record number 4700 in a unique key value. This error is marked by a number 172, it's SQL equivalent is sqlstate 40004. The unique key has two parts, the first having value of 1, the second value of 2 (entry [Key 1 : 2]) - the key value that caused the error. The POMTAB table is marked by number 16. The unique index is number 0 and its name is INDEX1. The error occured when inserting a record 16000. The error occured in the TEST3 procedure call from the TEST3_ENVELOPE procedure. The TEST3_ENVELOPE procedure was called with a input parameter with value equal to 2.
Keep in mind when tracking the problem in data, that this error caused this transaction to roll back, therefore you won't be able to find a record 16000 in the table and if the record 4700 was also inserted in this transaction, then you won't find this either.
After turning a server off and then on again, only the basic log tracing requests are conserved, without username or table name bindings. All other requests need to be (if you wish to conserve them after the server is terminated) called by the function _sqp_trace in the _on_server_start system trigger.
| Privileges and Privilege Subjects | | 602SQL Server | System Triggers | |