0
130

Written by

Evgeny Shvarov
Senior Startups and Community Programs Manager at InterSystems Corporation
TEAM
Follow
Question Evgeny Shvarov · Jan 20

What is the way to see the latest executed SQL query?

#InterSystems IRIS #ObjectScript #SQL

Hi folks!

Is there any way to know the text of the SQL queries executed against the IRIS server?

My case: I have an SQL class query with a few parameters and and some combination of values causes unexpected results.

It'd be great if I could just copy the query from some log of queries done vs my IRIS the server. Cannot find it easily, though. Any advice?

Product version: IRIS 2025.3
Discussion (14)3
Log in or sign up to continue

Comments

Robert Cemper · Jan 20

If you use a custom class query %Library.Query type you may write your parameters to some
^mtemp.Evgeny($i(^mtermp.Egeny)) = ..... direct from the Execute method
or ^mtemp.Evgeny($h) = ....

For basic class query %SQL.Query () you may take the usual SQL approach

  • Create a SQL method that always returns 1 (TRUE)
  • You pass all your parameters into that method 
    • which does the ^mtemp trick and a QUIT 1
  • add to the WHERE clause  . . .  AND MYTRACE(par1,par2,---)=1

I refer to this a STATIC clause since it is only executed once by query
because of no reference to any column values 

It was my approach to SQL debugging
 

0
David Hockenbroch · Jan 20

The only places I know of are, if it caused an xDBC error, that in the system management portal under System Operation, System Logs, xDBC Error Logs, and if it was a query run through the management portal itself, you'd be looking in the global %sqlcq and for your search mask, use %sqlcq("NAMESPACE","SMPQueryHistory","USERNAME" to see the query history for a specific user. That's where the query history you see in the SQL section of the management portal is stored.

I don't know of anything overall, though.

0
David Hockenbroch  Jan 21 to Evgeny Shvarov

It might be nice to make such tracking conditional. Like "Log all queries that take over 10 seconds to run" or whatever number is appropriate for your usage.

0
Evgeny Shvarov  Jan 20 to David Hockenbroch

BTW, the global indeed contains the history! Thank you!

0
Vitaliy Serdtsev · Jan 21

Enable auditing for %System/%SQL/DynamicStatementQuery, %System/%SQL/EmbeddedStatementQuery, %System/%SQL/XDBCStatementQuery system events.

You can view last actual values of the passed parameters of SQL query in SMP: System > Processes > Process Details > Variables

0
Evgeny Shvarov  Jan 21 to Vitaliy Serdtsev

Thank you, @Vitaliy Serdtsev !

If enabled auditing gives the ability to see parameters, or the ability to copy/paste into SQL query tool a ready/made sql query that has been running in the system?

0
Vitaliy Serdtsev  Jan 21 to Evgeny Shvarov

If auditing is enabled, you can see the query without the actual parameter values. To copy the query to the SQL query tool, the query will also have to be cleaned of garbage.

For example, there is the following query

<FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">* from </FONT><FONT COLOR="#008000">del.a </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">id</FONT><FONT COLOR="#000000"><3</FONT>

From SMP:

Event: DynamicStatementQuery
Event Data: <FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#000080"> FROM </FONT><FONT COLOR="#008000">del . a </FONT><FONT COLOR="#000080">WHERE </FONT><FONT COLOR="#008000">id </FONT><FONT COLOR="#000000">< </FONT><FONT COLOR="#000080">?</FONT>
From DbVisualizer:
Event: XDBCStatementQuery
Event Data: <FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#000080"> INTO </FONT><FONT COLOR="#800000">:i%%col1</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">:i%%col2</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">:i%%col3 </FONT><FONT COLOR="#000080">FROM </FONT><FONT COLOR="#008000">del . a </FONT><FONT COLOR="#000080">WHERE </FONT><FONT COLOR="#008000">id </FONT><FONT COLOR="#000000">< </FONT><FONT COLOR="#800000">:%qpar</FONT><FONT COLOR="#000000">(1)</FONT>
0
Evgeny Shvarov  Jan 21 to Vitaliy Serdtsev

I see. And in my case some sets of parameters cause an anomaly, and it is not very effective to investigate it one-by-one

0
Vitaliy Serdtsev  Jan 21 to Evgeny Shvarov

@Evgeny Shvarov

Unfortunately, I have not yet found a documented and [Not Internal] way out of the box to see not only the text of the request, but also the values of its input parameters.

PS: take a look at the methods of the %SYSTEM.SQL.xDBC class. Here is an example of the log for the query above (from DbVisualizer):

SQL text (raw from client): 1 lines
line 1: SELECT * FROM del . a WHERE id < :%qpar(1)
Var Types: Parameter 1: Constant of type INTEGER
INPUT params:
%qpar(1) = 3
0
Stephen Canzano · Jan 21

You might find some success looking at the tables/sqlprocs found in the %SQL* schemas

0