Official Content
  • This documentation is valid for:

Detecting and analyzing database lock situations isn't an easy task in general, so the applications generated using the GeneXus Java generator can be monitored using JMX to help with that task.

To detect locks in the database, different tools can be used (depending on the DBMS) to access the database and provide information about the JDBC connections that are locking the resources. After having identified the locks in the database, in general it's necessary to identify the actual user who is executing that lock, among other useful information.

First, remember that web applications use a connection pool to manage the JDBC connections to the database and that this pool is owned by the User configured in the data store properties (UserName PropertyUser Password Property). 

With JMX you can obtain the additional information you need:

  • The actual user who is executing the connection, through the PhysicalId attribute of the JDBC connection, and the IP and UserId attribute of the JMX User. This has proven to be very useful in intranet applications.
  • In addition, given the JDBC connection that is causing the lock, you can identify the GeneXus object which is executing (LastObject property) and the SQL statement being executed (LastSQLStatement property).

Example

Consider a web application using iSeries DBMS. When a lock is detected, the JOB (QZDASOINIT) number which is locking can be identified using the iSeries console. The QZDASOINIT job corresponds to a JDBC connection.

How do I identify the JDBC connection in the JMX console?

The physical ID attribute of the JDBC connection in JMX console matches the JOB number of the QZDASOINIT.
So given the JOB number, you can get the corresponding JDBC connection.

jobs in iSeries console
Figure #1. wrkactjob command executed on the iSeries console

Go to the pack GeneXusApplicationServer.ApplicationName.DataStore.ConnectionPool.Connection and look at the JDBC connections. Try to find a connection whose PhysicalId matches the QZDASONIT number.

PhysicalId attribute of JDBC connection
Figure #2. PhysicalId attribute of JDBC connection

How do I identify the user who is executing that JDBC connection?

It may be necessary to know who the actual user of the JDBC connection is when you need to perform an action over that connection (kill the connection that is locking the resources, for instance).

Given that in web applications there is no relation between the actual user of the application and the user of the JDBC connection, getting that information is not an easy task.

Getting the IP of the user's machine

The LastUserAssigned property of the JDBC connection gives the ID of the JMX user. 

LastUserAssigned JMX property
Figure #3. LastUserAssigned JMX property

Search for the JMX user given its ID, and look at its attributes under the GeneXusApplicationServer.ApplicationName.User pack. The IP attribute of the JMX user is the IP of the PC from where the request was established.

Ip property of a JMX user
Figure #4. Ip property of a JMX user

Getting the UserId

In the web application, if the setUserId function is used, the user can be saved in the context and retrieved using the UserId function. The UserId property of the JMX user returns the same as the UserId function.

The setUserId function needs to be executed each time the connection is given to the user (it can be in the Before Connect or in the After Connect).

Consider the following code in the proc which runs After Connect:

&username = &websession.Get("USERNAME") //The &websession has been saved after the login.
​&er = setuserid(&username)

In this example, the UserId value saved using the SetUserId function can be retrieved from the JMX console, at the GeneXusApplicationServer.ApplicationName.User pack:

UserId property of JMX user
Figure #5. UserId property of JMX user

See Also

Monitoring GeneXus applications



Last update: February 2024 | © GeneXus. All rights reserved. GeneXus Powered by Globant