Database considerations for multithreaded programming

Many of the database configuration, administration, and setup type interfaces are threadsafe. Database operations that are threadsafe include: Create File, Add Member, Delete File, and Remove Member. Refer to CL Reference or use the Display Command (DSPCMD) command to determine if a command is threadsafe. The online help information for that command lists any necessary conditions for threadsafety that apply to a command.

The database protects the I/O operations for the duration of the operation (read, update, insert, or delete). When you share an open instance of a file among threads, you must serialize access to the I/O feedback areas and I/O buffers to see valid information in these areas. These areas are under application control, and the database cannot protect them after the database operation has been completed.

An example is a read operation. If thread 1 is in the process of a read operation and thread 2 performs any I/O operation against the same open instance, thread 2 waits until thread 1 has completed the reading. The result of the read operation in thread 1 is placed in the I/O buffer. When control returns to thread 1, thread 2 begins its I/O operation. Without serialization, thread 2 can change the information in the I/O buffer before thread 1 can view the result.

If threads do not share an open instance of a file, no serialization is required.

Access to distributed database files used with the DB2® Multisystem and access to distributed data management (DDM) files of type *SNA are not threadsafe. Multithreaded jobs deny access to database files of these types. These file types cannot be made threadsafe because intersystem communications function (ICF) files and the entire Systems Network Architecture (SNA) layer are not threadsafe. If an attempt is made to open one of these file types, a CPF4380 message (Open attributes not valid in a multithreaded process) is sent to the function attempting to open the file.

You can fire trigger programs in a multithreaded job. The same threadsafety restrictions apply to trigger programs as to any other code that runs in a multithreaded job. Parameters on the Add Physical File Trigger (ADDPFTRG) command allow for specification of the trigger's threadsafety status and the action to take if the trigger is fired in a multithreaded job.

For logical files with multiple formats, the use of format selector programs is not threadsafe. You should not use format selector programs in a multithreaded job.

DB2 Structured Query Language (SQL) stored-procedure support provides a way for an SQL application to define and call an external program though SQL statements. Stored procedures can be called in a multithreaded job. The same thread safety restrictions apply to stored procedures as to any other code that runs in a multithreaded job. Unlike trigger programs, there is no way of specifying the stored procedure's thread safety status and the action to take if the stored procedure is called in a multithreaded job.

The use of DDL SQL statements might not be threadsafe. Data manipulation language (DML) statements are threadsafe.

Using the server mode for SQL is the preferred method for accessing databases with multithreaded applications. A job can use the server mode for SQL in order to manage multiple database connections and transactions. When the application is using server mode for SQL, the IBM® i operating system uses the connections in the job as a more encapsulated representation of the current database context than previously permitted in IBM i. It allows for connections to a database by multiple users, multiple connections to a database by the same or different users, and the existence of multiple, independent transactions by connections to a database.

Use one of the following mechanisms to activate server mode for SQL before data access occurs in the application:

• Use the Open Database Connectivity (ODBC) API, SQLSetEnvAttr() and set the SQL_ATTR_SERVER_MODE attribute to SQL_TRUE before doing any data access.
• Use the Change Job API, QWTCHGJB() and set the 'Server mode for Structured Query Language' key before doing any data access.
• Use Java™ Database Connectivity (JDBC) to access the database. JDBC automatically uses server mode to preserve required semantics of JDBC.

The CLI for SQL is threadsafe.

The introduction of threads does not change the scope of committable transactions. You can scope committable units of work to either a job-level commitment definition or an activation group-level commitment definition. A thread commit or rollback operation commits or rolls-back all operations done under the commitment definition. If you want an application where each thread (or group of threads) has a separate committable transaction, you must either use server mode for SQL or manage these transactions with separate activation groups.