Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    Zurich, Switzerland
    Posts
    4

    Unanswered: SQL Trace Logging on Oracle Rdb

    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. #2
    Join Date
    Sep 2003
    Posts
    1
    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.)

  3. #3
    Join Date
    Aug 2003
    Location
    Zurich, Switzerland
    Posts
    4

    RDB Logging (flag value meanings?)

    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,

  4. #4
    Join Date
    Sep 2010
    Posts
    1
    Hi David,

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

  5. #5
    Join Date
    Aug 2003
    Location
    Zurich, Switzerland
    Posts
    4
    I'm afraid not, we gave up on that long ago.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •