Re: Embedded SQL inserts to wrong library

[Jonathan Ball <jonball52@xxxxxxxxx>]

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