Web Services SQL Transactions

As ILEastic is a multi threaded framework we need to be aware of the fact that multiple threads may be using SQL transactions simultaneously. We need to make sure that these threads don’t step on each others toes when it comes to transactions.

IBM has a wonderful solution for this where you actually have to do almost nothing.
The answer is: SQL Server Mode

From the IBM i Documentation web site:

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.

So we just have to call the Change Job API at the beginning of our web service program and we are ready for thread scoped transactions. Great!

Sometimes it is a bit tricky to call some system APIs. How does it look like with the Change Job API?

Not problem at all. We can easily encapsulate the code in a procedure and call that procedure at the start of the web service program.

dcl-proc setSqlServerMode;
  dcl-pr sys_changeJob extpgm('QWTCHGJB');
    job char(26) const;
    internalJobId char(16) const;
    format char(8) const;
    jobChangeInfo char(1000) const options(*varsize);
    errorCode likeds(qusec);
  end-pr;

  dcl-ds jobc0200 qualified template;
    numberKeys int(10) inz(1);
    fieldInfoLength int(10) inz(20);
    keyField int(10) inz(1922);
    dataType char(1) inz('C');
    reserved2 char(3);
    lengthData int(10) inz(1);
    data char(1) inz('1');
    reserved3 char(3);
  end-ds;


  dcl-ds error likeds(qusec) inz;
  dcl-ds jobData likeds(jobc0200) inz(*likeds);

  sys_changeJob('*' : '' : 'JOBC0200' : jobData : error);
end-proc;

Information about the Change Job API can be found at IBM i Documentation. There is also a Technote in the Redbooks section from Scott Forstie called “DB2 for i5/OS: SQL Mode Primer” (published in 2007). As we can see from the naming of the operating system this book is not brand new but there are many things we can learn from it.

One important fact that I didn’t find on the new IBM i Documentation web site is which job attributes are passed from the main job to the SQL Server Mode job. Luckily this can be found in the previously mentioned Technote.

The following attributes are taken from the user profile: …

A very important thing I missed on my first read of that Technote is that most attributes are taken from the user profile of the the main job and not the job attributes of the running job itself. So if you change the attributes (like the current library) in your program or on submitting your job it really doesn’t matter in regard to the SQL Server Mode job because the SQL Server Mode job will use the current library from the user profile and not from the running job.

The SQL Server Mode job also uses the default library list (system and user part). So we need to make sure that every table we want to access via SQL in a non-qualified way (without specifying the library) is in the default library list configured in the system or in the current library of the user profile.

Note: One last thing to mention is that I had a problem with the SQL MERGE statement when run in SQL Server Mode. A workaround for the MERGE statement would be to use an INSERT and UPDATE statement. But this problem has also been addressed by IBM and the fix is included in SF99704 LV17.

You can find an example on how to use this in the Tour of Champions web service module champions.rpgmod.