Re: Embedded SQL inserts to wrong library

Jacob, here are a couple of things to consider...

Regarding the SQLPKG, this really only becomes important if you specify something other than *LOCAL for the database (the RDB parameter on the compile). As you haven't mentioned that I take it you are leaving it at the default value (which should be *LOCAL).

Our environment is similar to yours in that we have roughly 70 plants using the same i server for back-office processing. As one would expect, the support staff and others in corporate need access to multiple plants, and while we still use green-screen apps for most of our solutions we have provided a *master* menu for plant selection prior to getting into the systems that sets the library list for the chosen plant. I've encountered situations where not closing an SQL connection winds up bringing in data from another (previously visited) plant. I don't recall if this became an issue when we went to 7.1 or if it existed prior to that. Unfortunately my co-worker that developed our CGI app is out of the office this week, as I'm sure he would have chimed in (I seem to remember him talking about library lists). Unless you have done something fancy in your HTTP server configuration, I suspect that most of the programs are being run by the proxy user profiles (QTMHHTP1 and QTMHHTP). If that's the case, then it's entirely possible your programs are not ending when you think they are.

You mentioned an activation group, so it could be that the SQL database *session* is already open. Despite what we may think, SQL doesn't always abide by the rules when it comes to changing library lists in the middle of a job. I have made it a habit to include logic in all my SQLRPGLE programs to (re-)establish the connection at start-up (via SQL's CONNECT TO and SET CONNECTION directives) and then close the connection just prior to setting on LR (via the DISCONNECT directive).

Just a thought - while not the most elegant solution, you could always consider placing the DB library name in a data area and then using qualified table names in your code. I know it's not an issue with cursor/fetch (if you create an SQL statement and use the PREPARE directive), and I would think you could specify a variable for the library in a declarative SQL statement (I haven't tried that as we don't use those much in RPG).

hth!

------------- Begin Message -------------
Group: midrange.rpg400-l
MsgID: <mailman.62.1400165045.31233.rpg400-l@xxxxxxxxxxxx<mailto:mailman.62.1400165045.31233.rpg400-l@xxxxxxxxxxxx>>

Jacob Smallwood <jacob.smallwood91@xxxxxxxxx<mailto:jacob.smallwood91@xxxxxxxxx>> wrote:

Hi Jonathon,

We've got this set to *NONE, which if I understand correctly means to
use the library list when the naming convention is *SYS.

Thanks,

Jacob Smallwood

On Thu, May 15, 2014 at 10:03 AM, Jonathan Ball <jonball52@xxxxxxxxx<mailto:jonball52@xxxxxxxxx>> wrote:

Wondering about the DFTRDBCOL value for the program module.


On 5/13/2014 2:35 PM, Jacob Smallwood wrote:

Hi all,

The background: We've got a variety of RPG CGI programs (actually,
most are written in the PML language used by BCD's Websmart tool,
which then converts them to RPG) running in the IBM HTTP Server
(5770DG1). We host multiple clients on our system, each of whom has a
more or less identical set of tables but in their own library. On each
request we grab the client ID, set the library list appropriately, and
then go about our business.

This seemed to be working fine on 6.1, but ever since we moved to a
new 7.1 box last week, we're getting a couple dozen inserts every day
going to the wrong libraries. They're coming from two different
programs, but the SQL statements are identical between those programs:

C/Exec SQL
C+ INSERT INTO KWHNOTE ( HDATE, HSEQ, NETYPE, NECODE, NETIME, NLINE)
VALUE
C+ S( :HDATE, :HSEQ, :NETYPE, :NECODE, :NETIME, :NLINE) with NC
C/End-Exec

For example, we'll have a request come in from client ID 104 which
should go to KWDB104/KWHNOTE, but it ends up going to KWDB108/KWHNOTE.
I attached a trigger to this table that does a DSPJOB OUTPUT(*PRINT)
whenever this happens, so I know that KWDB108 wasn't in the library
list at the time (I can provide that listing if anyone thinks it would
be useful).

Now, I'm getting past my depth at this point, but it's my
understanding that an open data path is being created for this
statement which is then saved so it can be reused. However, according
to the documentation that I found

(http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Frzajq%2Freduceopens.htm),
the ODP shouldn't be reused if "A change to the library list since the
last open has occurred, which changes the table selected by an
unqualified referral in system naming mode." I confirmed with DSPPGM
that we're using Naming convention: *SYS.

Does anyone who understands these things better have any idea why
these inserts are hitting the wrong library? Additional info that
might be relevant: all these programs use the same activation group
(WEBSMART). All are compiled with CLOSQLCSR(*ENDACTGRP). I suspect
that *ENDMOD would eliminate this issue, but I'm reluctant to change
that without understanding why it's happening in the first place.
They're compiled on V5R4 then sent to the 7.1 system.

Thanks,

Jacob Smallwood

--

-------------- End Message --------------



- Dave Sager
Solus Christus