Re: Embedded SQL inserts to wrong library

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.