Hi,

I'm not sure where the most appropriate place is for questions regarding Oracle Rdb (i.e. the old DEC Rdb database system for VMS), so I thought I would try it here first...

We have an Rdb database running on an Alpha machine, which is accesed by users on Windows NT using the "ODBC Driver for Oracle Rdb".

This database is accessed by many users and we need to start monitoring their usage of it. It would be too difficult to track down every client PC and turn on local ODBC tracing for this task.

What I'm hoping for, is that there is a way to enable some sort of trace/logging on the server side which writes the client SQL statements out to a file.

Does anyone know of such a facility? We've already tried setting "ALTER SESSION LOG BRIEF" in the Oracle SQL/Services service SQL initialization file, but this only enables the tracing for clients which use Oracle Net8, i.e. for OCI services, not ODBC services.

Any ideas?

2. Trace Settings on the Server Side.
--------------------------------------
You enable tracing through the logical SQLSRV$SERVER_LOG which can be defined
as a system-wide logical or at the SQL/Services executor process level.
o Example of tracing all SQL/Services services -
$ DEFINE/SYSTEM SQLSRV$SERVER_LOG 15
(Please consult the section called "Logging Flag Values" at the end of
this bulletin for the meaning of value 15.)
o Example of tracing a specific SQL/Services service -
1) Create a process INIT file:
$ CREATE SYS$MANAGERQLSRV$INIT.COM/PROT=W=RE
$ !
$ DEFINE SQLSRV$SERVER_LOG 68
$ !
<CTRL>Z
(Please consult the section called "Logging Flag Values" at the
end of this bulletin for the meaning of value 68.)
2) Modify the SQL/Services service to activate tracing.
o Use the command line interface:
$ MCR SQLSRV_MANAGE
SQLSRV> ALTER SERVICE GENERIC PROCESS_INITIALIZATION
SQLSRV> 'SYS$MANAGERQLSRV$INIT.COM
or,
o Use Oracle DBAPack for Rdb7:
- Open the Oracle Rdb SQL/Services Manager
(Click on SQL_Services Manager.)
- Open the SET/SHOW Service screen for your service
(Double-click on service name.)
- Modify the Process Init File
(Enter SYS$MANAGERQLSRV$INIT.COM.)
3) Restart the SQL/Services service:
o Use the command line interface:
SQLSRV> CONNECT SERVER;
SQLSRV> SHUTDOWN SERVICE GENERIC;
SQLSRV> START SERVICE GENERIC;
or,
o Use Oracle DBAPack for Rdb7:
Restart your service when you are prompted to do so.
o Name and location of logfiles -
- Logfiles are written into SYS$SYSDEVICE:[SQLSRV$DEFLT].
- The logfile name for the GENERIC SQL/Services service is SQS*GENERI*.LOG.
- Other log files:
SYS$MANAGERQS*MON*.LOG :for the SQL/Services monitor
SYS$MANAGERQS*DIS*.LOG :for the SQL/Services dispatchers (if named DIS*)
(Used for authentication and authorization failures and executor or
dispatcher failures.)

Hi,

Thanks for this information. We have followed your instructions and the system does indeed generate a log file for us now. However, the actual, original SQL which was sent by the client is truncated, i.e. it only shows the first 50 or so bytes of the query and the rest does not appear. This is with a logging value of 68. Is there any setting which would get SQL/Services to print out the entire SQL statement?

Also, your post suggests:

(Please consult the section called "Logging Flag Values" at the end of this bulletin for the meaning of value 15.)

Where can I find these logging flag values? They are not at the end of your reply, and I can't seem to find anything like it on the web (using Google).

Regards,

Hi David,

Did you find logging flag values for SQLSRV$SERVER_LOG?
I need them also.

I'm afraid not, we gave up on that long ago.