× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I have RTFMed.

Here is one item from the list of limitations documented for jobs in SQL Server mode at 7.2. Unfortunately it is found under SQL CLI:

Because the SQL is processed in the prestart job, there is no sensitivity to certain changes in the originating job. This includes changes to library list, job priority, message logging, and so forth. The prestart is sensitive to a change of the coded character set identifier (CCSID) value in the originating job, because this can affect the way data is mapped back to the program of the user.

Here is the URL:
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/cli/rzadprestrictions.htm

I suspect that the library list, at the point in time that SQL Server mode is invoked, is copied to the QSQSRVR job, and this library list is as set in Program B which does not contain the library you want. Or alternatively, the library list is not copied and program B uses fully qualified table references.

Hence your UPDATE statement with the unqualified table reference fails to find the file. When you change the UPDATE to have a qualified table reference, it finds the file.

I can't explain the SQL7908, unless it is only generated if there is an error in the SQL. It does confirm SQL server mode for the job :)

Regards,

Kevin Wright | IBM i Developer, LPC
+61 2 8907 0284
LANSA
Blog | Facebook | LinkedIn | Twitter
 
There's a quiet revolution afoot: lansa.com/game-changer

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of John R. Smith, Jr.
Sent: Wednesday, 6 December 2017 9:37 AM
To: 'RPG programming on the IBM i (AS/400 and iSeries)' <rpg400-l@xxxxxxxxxxxx>
Subject: RE: SQL with connections to multiple databases

Based on what is in the joblog, I am assuming it is not using the QSQSRVR job. Without the library hardcoded, the below is written to the joblog.
With the library hardcoded, this does not exist.

SQL7908 Completion 00 11/28/17 12:18:14.407035 QSQROUTS
QSYS *STMT QXDAEVT QSYS *STMT
From module . . . . . . . . : QSQSRVRC
From procedure . . . . . . : SQSERVER
Statement . . . . . . . . . : 8315
To module . . . . . . . . . : QXDASQL
To procedure . . . . . . . : QXDA_SQL
Statement . . . . . . . . . : 4458

Message . . . . : Job 517546/QUSER/QSQSRVR used for SQL server mode processing.

Cause . . . . . : A Structured Query Language (SQL) statement was executed
while running in SQL server mode. SQL statements for this connection or thread will be processed in job 517546/QUSER/QSQSRVR.

Technical description . . . . . . . . : SQL server mode was requested by either setting the SQL server mode job attribute, or by setting the server mode environment attribute via the SQL Call Level Interface. When running in this mode, SQL statements are processed by a separate job, which runs under the user profile specified for the connection.
The thread identifier is -1 and the connection is to Relational Database XXXXXXXXXX. If the Relational Database name is *N, this means that all connections for the thread will use the same job.


From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kevin Wright

Hi Nathan,

Look up limitations on what SQL can be run when a job is in SQL server mode?


I think you will find that it is still presenting the SQL UPDATE statement to the QSQSRVR job, but now it is successful.

Regards,

Kevin Wright | IBM i Developer, LPC

From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Nathan Andelin

Can you explain how hard coding a library name in an SQL update statement would bypass an QSQSRVR job? I'm afraid that I've missed the point.

On Tue, Dec 5, 2017 at 11:50 AM, John R. Smith, Jr. < smith5646midrange@xxxxxxxxx> wrote:

For everyone that was following this, I never figured out how to get
the job to disconnect from the QSQSRVR job so I gave up and hard coded
the library in the SQL update statement and that causes it to bypass
the QSQSRVR job.
I
didn't like the solution but it worked.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.