SQL server mode and thread-scoped transactions for commitment control

Commitment definitions with job-scoped locks are normally scoped to an activation group.

If a job is multithreaded, all threads in the job have access to the commitment definition and changes made for a particular transaction can be spread across multiple threads. That is, all threads whose programs run in the same activation group participate in a single transaction.

There are cases where it is desirable for transactional work to be scoped to the thread, rather than an activation group. In other words, each thread has its own commitment definition and transactional work for each commitment definition is independent of work performed in other threads.

DB2® for i provides this support by using the Change Job (QWTCHGJB) API to change the job to run in SQL server mode. When an SQL connection is requested in SQL server mode, it is routed to a separate job. All subsequent SQL operations that are performed for that connection are also routed to that job. When the connection is made, completion message SQL7908 is sent to the job log of the SQL server mode job indicating which job the SQL requests are being routed to. The commitment definition is owned by the job that is indicated in this message. If errors occur, it might be necessary to look at the job logs for both jobs to understand the source of the problem because no real work is done in the job performing the SQL statements.

When running in SQL server mode, only SQL interfaces can be used to perform work under commitment control. Embedded SQL or Call Level Interface (CLI) can be used. All connections made through embedded SQL in a single thread are routed to the same back-end job. This allows a single commit request to commit the work for all the connections, just as it can be in a job that is not running in SQL server mode. Each connection made through the CLI is routed to a separate job. The CLI requires work that is performed for each connection to be committed or rolled back independently.

You cannot perform the following operations under commitment control when running in SQL server mode:

• Record changes that are made with interfaces that are not SQL interfaces
• Changes to DDM files
• Changes to API commitment resources

You cannot start commitment control directly in a job running in SQL server mode.