Writing an SQL Program › SQL Sessions › Beginning and Ending an SQL Session

Beginning and Ending an SQL Session

Beginning an SQL Session

An SQL session begins when the program submits its first SQL statement. If that statement is a CONNECT, the session is connected to the dictionary specified by the statement and the session is said to be explicitly connected.

If the first statement is not a CONNECT, the session is automatically connected to a default dictionary.

Session Hierarchy

A hierarchy of database sessions occurs when an SQL invoked routine (an SQL procedure, table procedure, or function) starts its own session to access the database.

A database session that is started by a program executing as part of an SQL invoked routine is is a subordinate session since it is under the control of the SQL session within which the routine was invoked. The controlling session is referred to as the subordinate session's encompassing session. A top-level session is one that has no encompassing session.

     ┌──────────────┐
     │ Top-level/   │  Application issues:
     │ Encompassing │    -- CONNECT...
     │  Session     │    -- CALL "PROCA"
     │              │
     └──────────────┘
            │
            │
            │
     ┌──────────────┐
     │ Encompassing │  PROCA issues
     │     and      │    -- CONNECT...
     │ Subordinate  │    -- Invokes function FUNCB
     │   Session    │
     └──────────────┘
            │
            │
            │
     ┌──────────────┐
     │              │  FUNCB issues
     │ Subordinate  │    -- BIND RUNUNIT...
     │   Session    │
     │              │
     └──────────────┘

Default Dictionary

When establishing an automatically connected SQL session, CA IDMS connects the session to a default dictionary.

The default dictionary for a top-level session is established by:

• SYSIDMS DICTNAME parameter for a batch application
• Value of the DICTNAME attribute for the user session, as set by one of the following:
  • User profile
  • System profile
  • Default dictionary defined by the DBNAME table
  • DCUF SET DICTNAME system task
  • Call to IDMSIN01 to set the DICTNAME attribute

Note: For more information about SYSIDMS parameters and calling IDMSIN01, see the CA IDMS Common Facilities Guide.

The default dictionary for a subordinate session is determined by the initiating routine definition's DEFAULT DATABASE parameter.

• If DEFAULT DATABASE CURRENT was specified, the default dictionary is the dictionary to which the encompassing SQL session is connected.
• If DEFAULT DATABASE NULL was specified (or defaulted), the default dictionary is determined in the same way as for a top-level session.

Note: For more information about the DEFAULT DATABASE parameter of the CREATE PROCEDURE, CREATE TABLE PROCEDURE or CREATE FUNCTION statements, see the CA IDMS SQL Reference Guide.

SQL Statements that End a Session

If the SQL session began automatically (that is, no CONNECT statement was issued), it ends when the program issues one of these statements:

• COMMIT
• ROLLBACK
• COMMIT RELEASE
• ROLLBACK RELEASE
• RELEASE

If a CONNECT statement was executed to start the session, it ends when the program issues one of these statements:

• COMMIT RELEASE
• ROLLBACK RELEASE
• RELEASE

If an encompassing session ends, all of its subordinate sessions end also.

Automatic Session Termination

If a batch application program terminates execution by returning control to the operating system, SQL sessions still in progress are terminated automatically as if the application had issued a ROLLBACK RELEASE statement.

If a program returns control to a teleprocessing system or issues certain teleprocessing statements, such as FINISH TASK, SQL sessions still in progress may or may not be terminated depending on the event or statement issued and whether the session is suspended.

Note: For more information about the effect of teleprocessing statements on SQL sessions, see Effect of Teleprocessing Statements and Events.