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

I use:

EXEC SQL CONNECT TO :myRMT USER :rmtUser USING :rmtPWD;

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;                                
   else;                                                     
      exec SQL SET CONNECTION :store.rmt;                    
   endif;                                                    
endif;                                                       

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?]