Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL SERVER MODE
has no ratings.
Published: 26 May 2015
Revised: 30 May 2015 - 3226 days ago
Last viewed on: 28 Mar 2024 (5269 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL SERVER MODE Published by: Bob Cozzi on 26 May 2015 view comments(2)

[EDIT: Please note that this website is using SQL Server Mode for its database CGI apps.]

Are you using SQL Server Mode? It is basically a QSYSWRK job that is started and connected to your job; it runs your SQL statements and transfers the results to your job. It's supposed to improve performance in some situations. The JAVA VM uses it so if you use Java JDBC to run SQL you're using SQL Server Mode.

Normally, embedded SQL statements are run within your current job. So the SQL statement is impacted by the environment, that is Library List, Overrides, activation group, etc. However the optional but strongly advocated SQL Server Mode ignores those things and runs your SQL statements in its own job. Once a job starts using SQL Server Mode, that job is locked into using it. Meaning all SQL statements within the job run in server mode (i.e., in the other "server" job, not the current job) and there is no way to switch back, short of sign off/sign on.

One issue that users are telling bothers them is that once their job is switched over to server mode, the STRSQL command is disabled for the job. So you loose that capability. Many users are using the iNav SQL Script tool on Windows to test and run SQL SELECT statements, SQL Query File can run SELECT and other statements from CL or Command Entry. And, on the surface, SQL Server Mode doesn't seem like much of an issue for Batch Jobs, but there's more to it.

When SQL Server Mode is active, all SQL statements run in another job. Therefore, think about it... this means that if you issue an OVRDBF or CHGCURLIB command those changes to the environment do not apply to the server job, but rather to you current job. Having SQL Server Mode active means not only do SQL statements run in another job, but you have to find a way to transfer your environmental changes to that SQL Server Job.

As of V5R4, IBM includes an SQL "Stored" Procedure named QCMDEXC. It is located in QSYS2 library and may be called in your RPG program, as follows:

EXEC SQL CALL QSYS2.QCMDEXC( :myCmd );

One of the biggest issues RPG developers have is including the : in front of the myCmd variable. If I run QCMDEXC directly, I do this:

QCMDEXC( myCmd : %len(myCmd));

However since the CALL to the QSYS2 version of this SQL Stored Procedure is in actuality an SQL statement, you have to pass the command as a host variable, hence the colon prefix.

EXEC SQL CALL QSYS2.QCMDEXC( :myCmd );  // This is CORRECT
EXEC SQL CALL QSYS2.QCMDEXC( myCmd );   // This is WRONG

Using this for an override or CHGLIBL/CHGCURLIB is fine, however remember that during Server Mode, it impacts the Server Job and NOT the job actually running your application. So you will ALSO need to mimic the command (run it a 2nd time) for the current job, using traditional interfaces, such as a call to the QCMDEXC API.

One of the things I use QSYS2.QCMDEXC for is to issue the ADDLIBLE and CHGCURLIB command. The problem with this approach is that the joblog is quickly filled up (in my application) with "Library already exists on library list" and "Library not added" messages, along with the usually propagation of *ESCAPE/Exception messages. Of course they don't cause your job to fail but the joblog ends up being greatly expanded. There is a LIBARARY_LIST_INFO function that I could scan first, but that's not going to happen. Now I use the SQL Query File UDFs and SPs to run those LIBL commands directly (see below).

The SQL Query File licensed program includes several UDFs and Stored Procedures that permit library list management; ADDLIBLE, RMVLIBLE, CHGCURLIB, and CHKLIBLE. These can be evoked using EXEC SQL QUSRSYS.ADDLIBLE( :myLib ) but may also be called using tradition RPG interfaces, ADDLIBLE( myLib ), therefore both environments (the SQL Server Job and the local user job) can be altered as follows:

EXEC SQL QUSRSYS.ADDLIBLE('MYDATALIB APPLIB');
ADDLIBLE('MYDATALIB APPLIB');

Line 1 changes the SQL Server Job, while Line 2 changes the job running the RPG program. Similar use of the QCMDEXC API or QSYS2.QCMDEXC stored procedure will impact both jobs. Here are a few tips about SQL Server Mode operation.

SQL Server Mode "Gotch ya's"

  • Once a job uses (switches to) SQL Server Mode is stays in Server Mode until the job ends.
  • If you use SQL from within Java (e.g., JDBC) or any Java function that may use JDBC, your job is switched to Server Mode.
    • Some IBM CL system commands use JDBC to do their database access.
  • All "Embedded SQL in RPG" or another HLL's will run in that Server Job once Server Mode is active.
  • Override DB (OVRDBF) commands have ZERO impract when using Server Mode. The OVRDBF is run in your job, while the SQL File I/O is run in the server job.
  • Changes to your job session environment, library list, current lib, log levels (i.e., CHGJOB) etc. are ignored. Again because the SQL being processed is run in a different job.

Solutions for SQL Server Mode

If you are writing RPG with embedded SQL, you may want to consider making your RPG code "Server  Mode Safe". Mean the code works whether or not the job is using SQL Server Mode. To accomplish that, consider the following:

If you do NOT Own a Licensed Copy of SQL Query File:

  • Use: EXEC SQL CALL QSYS2.QCMDEXC( :mVar ); to run CL commands. Since the command is being processed by an SQL routine, it too is run in the server job, hence the command (OVRDBF, ADDLIBLE, etc.) is run in that server job.

If you Own a Licensed Copy of SQL Query File:

  • Use one of the appropriate SQL Procedures or Functions bundled with SQL Query File:
    • EXEC SQL CALL QUSRSYS.ADDLIBLE('MYLIB YOURLIB OURLIB');
      • Adds the specified library/libraries to the library list of the server job
    • EXEC SQL  CALL qusrsys.CHGCURLIB('QGPL');
      • Changes the current library to QGPL in the server job
    • EXEC SQL CALL qusrsys.CHGLIBL('COZTOOLS QGPL QTEMP USRLIB MYDATA'); 
      • Changes the library list in the server job
    • EXEC SQL  CALL qusrsys.RMVLIBLE('QIWS');
      • Removes the library or libraries from the library list of the server job.

We also include a CHKLIBLE (Check Library List) command that returns 1 if the named library is found on the library list.

One issue we encountered with IBM's QSYS2.QCMDEXC procedure is that using it for ADDLIBLE/RMVLIBLE causes a number of messages to be generated in the joblog. No such messages are produced with the SQL Query File ADDLIBLE/RMVLIBLE procedures.

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: starbuck5250
Premium member *
Comment on: SQL SERVER MODE
Posted: 8 years 10 months 3 days 12 hours 39 minutes ago

I didn't know this even had a name, but here's a Redbook from 2007 that describes it.  Here's the back of the napkin version:

Enabling SQL Server Mode
There are two ways to programmatically request the use of SQL Server Mode within a job:

  • SQLSetEnvAttr() SQL CLI API, via the SQL_ATTR_SERVER_MODE attribute
  • QWTCHGJB() Work Management API, via the "Server mode for Structured Query Language" key 1922 within the JOBC0200 format


Whenever a connection is established using SQL Server Mode, the SQL7908 completion message is sent to the job log indicating the name of the QSQSRVR job that is being used, for example:
Job 188233/QUSER/QSQSRVR used for SQL server mode processing.

As stated before, using the native JDBC driver (also known as IBM Developer Kit for Java - licensed program 57xxJV1) implicitly enables SQL Server Mode.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL SERVER MODE
Posted: 8 years 10 months 3 days 12 hours 22 minutes ago
Edited: Fri, 29 May, 2015 at 09:42:07 (3227 days ago)

Buck,

In the SQL Query File product, I use the QWTCHGJB (Thread-Change Job) to switch to Server Mode if the program is running in Batch. Otherwise, it continues to run in whichever mode is currently be used. But I did add a new parameter to the RUNSQLF command:

SVRMODE(*BATCH | *ALL)

If you specify SVRMODE(*ALL) it runs in SQL Server Mode. Otherwise it only forces the job into SQL Server Mode if its a batch job.

I'm not a big fan of SQL Server Mode at this point. It requires a lot of detailed changes to accomodate things exactly as we would want them to work. However I think they figured nobody would notice. cool