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.
[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.
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:
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.
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:
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.
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.