14.6. Reset Session State

14.6.1. ALTER SESSION RESET

Used forResetting session state to its initial values

Available inDSQL, PSQL

Syntax 

  |ALTER SESSION RESET

ALTER SESSION RESET resets the current user session to its initial state. It can be useful for reusing the connection by a client application (for example, by a client-side connection pool). When this statement is executed, all user context variables are cleared, contents of global temporary tables are cleared, and all session-level settings are reset to their initial values.

It is possible to execute ALTER SESSION RESET without a transaction.

Execution of ALTER SESSION RESET performs the following steps:

  • Error isc_ses_reset_err (335545206) is raised if any transaction is active in the current session other than the current transaction(the one executing ALTER SESSION RESET) and two-phase transactions in the prepared state.

  • System variable RESETTING is set to TRUE.

  • ON DISCONNECT database triggers are fired, if present and if database triggers are not disabled for the current connection.

  • The current transaction (the one executing ALTER SESSION RESET), if present, is rolled back. A warning is reported if this transaction modified data before resetting the session.

  • Session configuration is reset to their initial values. This includes, but is not limited to:

    • DECFLOAT parameters (TRAP and ROUND) and reset to the initial values defined using the DPB at connect time, or otherwise the system default.

    • Session and statement timeouts are reset to zero.

    • The current role is restored to the initial value defined using DPB at connect time, and — if the role changed — the security classes cache is cleared.

    • The session time zone is reset to the initial value defined using the DPB at connect time, or otherwise the system default.

    • The bind configuration is reset to the initial value defined using the DPB at connect time, or otherwise the database or system default.

    • In general, configuration values should revert to the values configured using DPB at connect time, or otherwise the database or system default.

  • Context variables defined for the USER_SESSION namespace are removed.

  • Global temporary tables defined as ON COMMIT PRESERVE ROWS are truncated (their contents is cleared).

  • ON CONNECT database triggers are fired, if present and if database triggers are not disabled for the current connection.

  • A new transaction is implicitly started with the same parameters as the transaction that was rolled back (if there was a transaction)

  • System variable RESETTING is set to FALSE.

ⓘ
Note

  • The context variables CURRENT_USER and CURRENT_CONNECTION will not be changed.

  • As isql starts multiple transactions for a single connection, ALTER SESSION RESET cannot be executed in isql.

14.6.1.1. Error Handling

Any error raised by ON DISCONNECT triggers aborts the session reset and leave the session state unchanged. Such errors are reported using primary error code isc_session_reset_err (335545206) and error text "Cannot reset user session".

Any error raised after ON DISCONNECT triggers (including the ones raised by ON CONNECT triggers) aborts both the session reset and the connection itself. Such errors are reported using primary error code isc_ses_reset_failed (335545272) and error text "Reset of user session failed. Connection is shut down.". Subsequent operations on the connection (except detach) will fail with error isc_att_shutdown (335544856).