IC66037: DRDA: ONSTAT -G SES/ONSTAT -G SQL DO NOT SHOW "RETAIN UPDATE LOCKS" IN ISOLATION LEVEL FOR POOLED CONNECTIONS

APAR status

• Closed as program error.

Error description

• DRDA: onstat -g ses/onstat -g sql do not show "retain update
  locks" in isolation level for pooled connections
  
  onstat -g ses/onstat -g sql might not show "retain update locks"
  in isolation level when Data Server Provider for .Net is used.
  
  This happens when the connection is retrieved from the pool and
  the isolation level for the connection was changed.
  oncheck -k shows that the expected locks were set so it is only
  a problem of the output from onstat -g ses/onstat -g sql.
  
  
  Scenario:
  
  RETAIN UPDATE LOCKS is set via sysdbopen
  
  CREATE PROCEDURE "public".sysdbopen()
     SET LOCK MODE TO WAIT 15;
     SET ENVIRONMENT RETAINUPDATELOCKS 'COMMITTED READ';
  END PROCEDURE;
  
  The isolation level for the connection is set via
  
  IfxConnectionStringBuilder builder = new
  IfxConnectionStringBuilder();
    builder.IsolationLevel = "ReadCommitted";
  
    or alternating
  
    builder.IsolationLevel = "ReadUncommitted";
  
  
  
  Expected:
  
  Sess       SQL            Current            Iso Lock       SQL
  ISAM F.E.
  Id         Stmt type      Database           Lvl Mode       ERR
  ERR  Vers  Explain
  144        SELECT         mydb               CRU Wait 15    0
  0    -     Off
  
  Retrieved:
  
  Sess       SQL            Current            Iso Lock       SQL
  ISAM F.E.
  Id         Stmt type      Database           Lvl Mode       ERR
  ERR  Vers  Explain
  144        SELECT         mydb               CR  Wait 15    0
  0    -     Off
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * IDS 10.00 and above                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * "RETAIN UPDATE LOCKS" is set via sysdbopen() when the        *
  * connection is first established.                             *
  * Since connection pooling is on, this connection may be       *
  * obtained for new connect reuqests.                           *
  * If the isolation level is ever changed, "onstat -g sql xxx"  *
  * doesn't show "RETAIN UPDATE LOCKS"                           *
  * anymore.                                                     *
  *                                                              *
  * This only occurs for common client that use DRDA protocol    *
  * (i.e. .NET, JCC).                                            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IDS 11.50.xC8 and above.                          *
  ****************************************************************
  

Problem conclusion

• The problem is fixed in IDS 11.50.xC8
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSY

     UP