Maxdb 7.7 - finding current active sql

[Former Member]

How do see what sql is currently running over the entire database and how do I map that back to a user session?

Is there a data dictionary view I can query for this information?

[markus_doehr2]

you can try

select * from running_commands

--

Markus

[Former Member]

This select returns 0 rows if I run it as the dbadmin user and I have another session running as another database user.

[former_member229109]

Hello Joe,

"How do see what sql is currently running over the entire database and how do I map that back to a user session?"

-> Are you SAP customer? In SAP area you have those possibilities too (SQL-Trace,

Database Assistant tool - db50 or db50n, ... >.

For SAP MAXDB documentation see the SAP note 767598.

If you are SAP customer, please review the SAP notes::

819324 FAQ: MaxDB SQL optimization

819641 FAQ: MaxDB Performance

What version of the database you have?

<'dbmcli -d <DB-name> -u <dbmuser>,<dbmuser-pwd> show version'>

-> 1) * Please check the active tasks first.

< you could use x_cons or dbmcli tools, for example,

'x_cons <DB-name> show active' or

'dbmcli -d <DB-name> -u <dbmuser>,<dbmuser-pwd> show active'

• Then display Database Sessions.

You can use the Database Manager GUI to display the system tables

that are assigned to the SYSINFO schema. These system table contain

information about the database states.

Please see document at

http://dev.mysql.com/doc/maxdb/en/6a/840b40c6c01961e10000000a155106/content.htm

2) The table for diagnose purpose is filled after the special command

"diagnose monitor parseid on" was executed.

You could run SQL statement as SYSDBA user using SQLStudio, for example.

After that every newly parsed command is inserted in the system table

sysparseid.

You could run the SQL statement 'select * from running_commands' to

review the actual running commands & the TASKID <first column>.

But be aware that the sysparseid table growth endlessly and could fill up

your database and this diagnose feature costs some performance.

Please use it in case the SQL statement need to be analyzed.

By the TASKID you could map that back to a user session by displaying

'Sessions' in database or Overview of Transactions.

To switch off => please run "diagnose monitor parseid off"

Thank you and best regards, Natalia Khlopina

For example::

I run as superdba user user SQL statement 'diagnose monitor parseid on'

< You run SQL statements using SQLStudio, for example. >

I connected to my database NLK as control user

Using dbmcli tool < control is dbm user of my database >::

Dbmcli on NLK>sql_connect superdba,admin

< & run the SQL statement as superdba user, SYSDBA user >

Dbmcli on NLK>sql_execute select * from running_commands

=>

28;'SAPICDT_';232;x'000B0439000238013C000000';'SELECT /*+ FIRST_ROWS (260) */ * FROM "DD02L" WHERE "AS4LOCAL" IN ( ? , ? , ? ) AND "TABNAME" LIKE ? AND "TABCLASS" IN ( ? , ? , ? , ? ) AND ROWNUM <= ?'

47;'';0;x'000BD8FF0000200000000000';'SELECT * FROM RUNNING_COMMANDS'

< In my case : database tasks T28 & T47 were active >

Dbmcli on NLK>sql_execute select * from sessions

< To review the information about connection between an application and a database task >

Reference to documents "Displaying Database Sessions" and "Displaying System Tables"::

http://dev.mysql.com/doc/maxdb/en/30/5ada5f596211d4aa83006094b92fad/content.htm

&

http://dev.mysql.com/doc/maxdb/en/6a/840b40c6c01961e10000000a155106/content.htm

Dbmcli on NLK>sql_execute diagnose monitor parseid off

< to switch off diagnose monitor >

Dbmcli on NLK>sql_execute select * from running_commands

...

100,Row not found

Dbmcli on NLK>exit

[Former Member]

I'm not a SAP customer.

Version:

Kernel Version 7.7.02 Build 010-123-153-906

RTE Version W32/Intel 7.7.02 Build 010-123-153-906

Thank you your post was helpful in looking at current activity.