Re: Embedded SQL inserts to wrong library

[Jacob Smallwood <jacob.smallwood91@xxxxxxxxx>]

Hi Trevor,

True, that would probably eliminate the problem. I've been reluctant
to try that for a few reasons, namely the performance impact, the
large amount of SQL that would need to be rewritten to do this
(granted, it's only affecting two inserts at the moment, but if I
can't trust those two I don't think I can trust any of them), and the
simple inconvenience of having to use dynamic queries for everything.

Frankly, even if I ultimately get this working via a workaround, I'm
still hoping to understand what's going on. It makes me nervous fixing
it without knowing why it was broken. Can I be sure that my
unqualified selects aren't going to start retrieving the wrong
clients' data one day? If not, it would be a significant effort to
rewrite all our SQL as dynamic queries.

Thanks,

Jacob Smallwood

On Wed, May 14, 2014 at 1:37 PM, Briggs, Trevor (TBriggs2)
<TBriggs2@xxxxxxxxxxx> wrote:
> Wouldn't a more reliable solution be to build the INSERT statement in
> the RPG program including the library name and then do an EXECUTE
> IMMEDIATE?
>
> Trevor Briggs
> Analyst/Programmer
> Lincare, Inc.
> (727) 431-1246
> TBriggs2@xxxxxxxxxxx
>
> -----Original Message-----
> From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jacob
> Smallwood
> Sent: Tuesday, May 13, 2014 5:36 PM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: Embedded SQL inserts to wrong library
>
> 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=%2Frza
> jq%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
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
> ************************************************************************************************************************************************************************************************************
> This message originates from Lincare Holdings Inc. It contains information which may be confidential or privileged and is intended only for the individual or entity named above.
> It is prohibited for anyone else to disclose, copy, distribute or use the contents of this message.
> All personal messages express views solely of the sender, which are not to be attributed to Lincare Holdings Inc., and may not be copied or distributed without this disclaimer.
> If you received this message in error, please notify us immediately at MailAdmin@xxxxxxxxxxx or (800) 284-2006.
> ************************************************************************************************************************************************************************************************************
>
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.