Re: Embedded SQL inserts to wrong library

[Jacob Smallwood <jacob.smallwood91@xxxxxxxxx>]

Hi Birgitta,

Yes, we did create the program with system naming convention (by
virtue of that being the default; we don't explicitly specify that
anywhere). I was under the impression that that's what I wanted,
however, because the link I was looking at made me think that was
necessary for library list changes to be detected in this scenario. Is
that not correct? We don't have a default schema set and I couldn't
find anywhere that we a do a SET SCHEMA in a scan of our codebase.

For reference, here's what DSPPGM shows as the SQL attributes of this
module/program:
DB2 for IBM i module attributes:
  Number of SQL statements . . . . . . . . . . . :   19
  Commitment control . . . . . . . . . . . . . . :   *NONE
  Relational database  . . . . . . . . . . . . . :   *LOCAL
  Naming convention  . . . . . . . . . . . . . . :   *SYS
  Allow copy of data . . . . . . . . . . . . . . :   *OPTIMIZE
  Allow blocking . . . . . . . . . . . . . . . . :   *ALLREAD
  Delay PREPARE  . . . . . . . . . . . . . . . . :   *NO
  Close SQL cursor . . . . . . . . . . . . . . . :   *ENDACTGRP
  Date format  . . . . . . . . . . . . . . . . . :   *ISO
  Date separator . . . . . . . . . . . . . . . . :   /
  Time format  . . . . . . . . . . . . . . . . . :   *HMS
  Time separator . . . . . . . . . . . . . . . . :   :
  Default Collection name  . . . . . . . . . . . :   *NONE
  SQL package name . . . . . . . . . . . . . . . :   LNNOTE
    Library  . . . . . . . . . . . . . . . . . . :     QTEMP
  Dynamic user profile . . . . . . . . . . . . . :   *USER
  SQL sort sequence table  . . . . . . . . . . . :   *HEX
  SQL language identifier  . . . . . . . . . . . :   ENU
  Connection method  . . . . . . . . . . . . . . :   *DUW
  SQL Path . . . . . . . . . . . . . . . . . . . :   *LIBL

Thanks,

Jacob Smallwood

On Wed, May 14, 2014 at 11:08 AM, Birgitta Hauser
<Hauser@xxxxxxxxxxxxxxx> wrote:
> Just a question:
> Did you create the program perhaps with SYSTEM Naming conventsion (or set
> the appropriate Option through an SET OPTION statement in your source code)
> Did you specify a default schema compile Option DFTRDBCOL when compiling
> your program (or set the appropriate option though an SET OPTION statement
> in your source code)
> Was a SET SCHEMA statement performed in jour job before calling your
> embedded SQL Program?
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
>
> -----Ursprüngliche Nachricht-----
> Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jacob
> Smallwood
> Gesendet: Tuesday, 13.5 2014 23:36
> An: rpg400-l@xxxxxxxxxxxx
> Betreff: 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)
> C+ VALUE 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%2
> Freduceopens.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.