Session Debug and Logging Database Commands

 

Session Debug:

Session Debug has been in MicroStation since the MicroStation Database connections were introduced.  This handy command gives you insight into the SQL that is being generated from MicroStation or the MicroStation Database API.  It is probably the first and foremost tool for investigating Database connectivity or SQL Syntax issues.

The Session Debug command is part of the Database MDL app (server.ma).   Therefore Session Debug cannot be used until after a database connection is established.  Once you have a database connection simply key in:

Session Debug On

By turning on session debug you presented with a MicroStation Message Window.  As you perform operation against the database, debug information will be written to the message window. 

The debug information can be copied from the message window by high lighting the test and doing a <ctrl> <v>.  In some case it is valuable to save off some of all of the debug information collected during a database session.  To save off the messages to a text file the following commands can be used.

  • dmsg openmsgfile c:\sessionDebug.txt
    • This key in command will open the file c:\sessionDebug.txt and begin writing the session debug messages to this text file.
  • dmsg closemsgfile
    • This key in command can be used to close an open message file.
  • dmsg appendmsgfile c:\sessionDebug.txt
    • This key in command can be used to append to an existing message file.

 

One issue with using the Session Debug Key In is that you cannot debug database connection issues.  The reason is because the key in command is only available after you complete the database connection and server.ma is loaded.  You could work around this by connecting to another database starting Session Debug and then trying the connection that is failing.  The best approach is to set the configuration variable MS_SESSIONDEBUG. 

set MS_SESSION_DEBUG=1

By setting this variable either at the Environment Level or as a MicroStation Configuration variable, Session Debug information will begin populating the message window as soon as the first database operation is executed.

BSILOGGING:

In MicroStation V8i the same logging that is used in ProjectWise was implemented in the database components.  The logger is very flexible and by default is set up to write to a log file.  If you are familiar with the logging in ProjectWise it works exactly the same way.  There is an XML file that contains the categories and the level of logging you want to record.  In the case of MicroStation the file bsilog.config.xml resides in the directory:

$(_USTN_WORKSPACEROOT)\System\Data

Which on most systems is:

C:\Documents and Settings\All Users\Application Data\Bentley\MicroStation\WorkSpace\System\data

To have it log debug information for the MicroStation Database components add the following categories of interest and set the severity to debug.

<category name="ustation.database.sessiondebug">     <priority value="debug"/></category>

<category name="ustation.database.oracle">           <priority value="debug"/></category>

<category name="ustation.database.budbc">            <priority value="debug"/></category>

<category name="ustation.database.odbc">             <priority value="debug"/></category>

The last step in the process is to enable the logging functionality in MicroStation by setting the environment variable MS_BSILOG_ENABLE.

set MS_BSILOG_ENABLE=1

Once everything is in place MicroStation will begin writing messages to the log file.  The log file will be written to the output directory:

${USERPROFILE}\Application Data\Bentley\Logs\

Capturing SQL Text from the DB:

A less accurate way of capturing issued SQL Statements at the database level.  This is less accurate because on a large RDBMS there could be hundreds of connections to the database each issuing thousands of SQL Statements.  I still mention this method because if you need the information quickly and you are the only person on the Database Server it works quite well.  In Oracle you can execute the below query to see the 50 recently executed SQL statements.  Again, this is great if you are working on a test server by yourself.  The problem comes in when multiple users are hitting the same server.  It isn't as easy to pick out the SQL statements issued for a particular user. 

Select SQL_TEXT, to_char (LAST_ACTIVE_TIME, 'MM-DD-YYYY:HH24:MI') from v$sql where rownum < 50 order by LAST_ACTIVE_TIME desc;

Also be aware that the SQL_TEXT column only gives you the first 1000 characters of the SQL Statement.

For SQL Server you need to be aware of the version and also the compatibility level of the database.  See my earlier Blog post on SQL Server Compatibility levels:

http://communities.bentley.com/Other/Old_Site_Member_Blogs/Bentley_Employees/b/allen_brown_bentleys_blog/archive/2009/02/24/are-you-using-sql-server-2005-are-you-really-using-sql-server-2005.aspx

For SQL Server 2000 you can use the following SQL Statement:

select objtype, sql, usecounts
from syscacheobjects
ORDER BY usecounts DESC

For SQL Server 2005 and 2008 use the following:

select TOP 100 objtype,
LEFT([sql].[text], 100) as [text],usecounts
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts DESC

Whether you are simply debugging a database connection string or doing some query tuning the above mentioned techniques will help you see what SQL is being sent from MicroStation to the Database server.