Re: Embedded SQL inserts to wrong library

Hi Thomas,

Thanks for finding these. SI44190 looks especially promising to me, as
the description seems to match my scenario exactly. I guess it didn't
occur to me to search for a PTF since every other time I think there's
a bug in the OS it just turns out I'm doing something stupid :) I'll
see about getting these loaded.

Jacob Smallwood

On Thu, May 15, 2014 at 1:35 AM, <thomas.raddatz@xxxxxx> wrote:

Jacob,

Did you already check the following APARs, which might be related to your
problem:

PTF SI48393:
http://www-912.ibm.com/n_dir/nas4apar.nsf/a95748a674672bcd862577070050fc83/ee9ce639a6e9c87286257a9d003cb5e6?OpenDocument&Highlight=2,sql,library,list

PTF: SI44190
http://www-912.ibm.com/n_dir/nas4apar.nsf/a95748a674672bcd862577070050fc83/4e38fd458388fbed862578d2003ccbf8?OpenDocument&Highlight=2,sql,library,list

PTF: SI48281
http://www-912.ibm.com/n_dir/nas4apar.nsf/a95748a674672bcd862577070050fc83/a7e5d039edf37ead86257a86003c8905?OpenDocument&Highlight=2,sql,library,list


Actually there a lots of APARs related to the "wrong library" problem. Feel
free to go to "http://www-912.ibm.com/n_dir/nas4apar.nsf/$$Search?openform";
and search for "sql library list" to get the list of APARs from which I took
the three examples from.

Regards,

Thomas.

rpg400-l-bounces@xxxxxxxxxxxx schrieb am 13.05.2014 23:35:42:

Von: jacob.smallwood91@xxxxxxxxx
An: rpg400-l@xxxxxxxxxxxx,
Datum: 14.05.2014 16:47
Betreff: Embedded SQL inserts to wrong library
Gesendet von: rpg400-l-bounces@xxxxxxxxxxxx

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
--
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.

--
IMPORTANT NOTICE:
This email is confidential, may be legally privileged, and is for the
intended recipient only. Access, disclosure, copying, distribution, or
reliance on any of it by anyone else is prohibited and may be a criminal
offence. Please delete if obtained in error and email confirmation to the
sender.