Midrange News for the IBM i Community

Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
SQL Connect To Using is Ignoreing User
has no ratings.
Published: 25 Jan 2013
Revised: 25 Jan 2013 - 4136 days ago
Last viewed on: 22 May 2024 (4815 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL Connect To Using is Ignoreing User Published by: Bob Cozzi on 25 Jan 2013 view comments(1)

I have an application that connects to a remote database using embedded SQL.

I use:


But when I look in the joblog, it is using the current job's user profile to create the connect, not the user I specify in the RMTUSER variable.

The remote systems (the ones calling the SQL CONNECT TO statement) are on IBM i5 v5r4 whereas the headquarters systems is on IBM i v7r1 so 3-level names aren't an option yet.

I really don't want to create the same user on both system as we have people who are not permitted to get to certain systems, but those same systems house data to which they need application-level access.

Here's the cut/paste from the program itself:

Inside the field named store.rmtUser is 'BOBBY' and yet when the connection is attempted it is using the User Profile from the job instead.

if (store.rmt <> '');                                        
   if (store.firstTime);                                     
      EXEC SQL CONNECT TO   :store.rmt                       
                       USER :store.rmtUser USING :store.rmtPwd;          
      store.firstTime = *OFF;                                
      exec SQL SET CONNECTION :store.rmt;                    

It is in a fetch loop and I am getting data from the local source machine and the remote system for the display.

I though CONNECT TO   USER USING would do the trick. Any suggestions?

[UPDATE: More info... if the user calling the app has USRCLS(*SECOFR) the CONNECT TO USER USING works by connecting to the remote server with the user in the RMTUSER variable.

The specific user we were trying to get working, has USRCLS(*SYSOPR) and no, I did not setup this system.

I wonder what needs to change to make this thing work?]

Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Comment on: SQL Connect To Using is Ignoreing User
Posted: 11 years 3 months 27 days 12 hours 49 minutes ago
Edited: Fri, 25 Jan, 2013 at 12:15:21 (4136 days ago)

Kept digging and found this; it is a two step process:

Step 1 is performed ONCE on each remote location connecting back to the host server:


Step 2 is performed ONCE for each User Profile that does NOT exist on the host server or that does NOT have *SECOFR user class:


Of course 'HOSTSRV' is replaced with the remote database server name (must be in all upper case). HOSTUSER and HOSTPWD are replaced with the  user ID and PWD being used to connect to the host server.


But in the end, I'm still not happy with this solution. It means that in my RPG program I have to do a CONNECT TO XXXX User HOSTUSER Using :pwd; and if I wanted to connect using another user, I'd have to go add it to the SVRAUTE too. This just seems goofy to me.