To explain some more, use the command CHGDDMTCPA to set the parameter Lowest authentication method to *USRID or *USRIDPWD. After this, you run SQL CONNECT statement that provides user ID (or ID + PW) that exists in the target LPAR BEFORE running your SQL SELECT statement. 

CONNECT TO <target LPAR ID> USER <user ID> USING <password> 

------------------------------
Satid S
------------------------------

This article should help. Its a way of defining a 'service account' to be used for these types of transactions without the need to either have every profile on every lpar or to have the passwords be the same across all lpars for a specific profile. You will likely want to discuss this with your system admins as well though. Good luck - hope this helps

Simplified DDM and DRDA authentication entry management using group profiles (ibm.com)

@Rich Malloy Thanks for the inputs.

In this article it talks about a group profile and adding necessary users to that profile. Then give access to that group profile for remote database. 

Wish we can use ADDSVRAUTE command, so no need to mention user credentials in the program while trying to connect to RDB.

However in our scenario, there are many thousands of user profiles already created and in use. If I create a new group profile, is it possible to assign all existing user ids to that profile? Also believe any new user profiles created, had to be explicitly added to this group. Any suggestions or recommendations please?

Thanks!

This article should help. Its a way of defining a 'service account' to be used for these types of transactions without the need to either have every profile on every lpar or to have the passwords be the same across all lpars for a specific profile. You will likely want to discuss this with your system admins as well though. Good luck - hope this helps

Simplified DDM and DRDA authentication entry management using group profiles (ibm.com)

Hi Prakash - you don't need a new group profile, its just one way of accomplishing this. But yes, you can work with your admins/security engineers to add all those users to a new group profile if you desire. Another approach with this is to simply add all your users that you want to execute this into the "database" via addsvraute command. you can use a CL program to do that for you pretty easily enough. 

@Rich Malloy Thanks for the inputs.

In this article it talks about a group profile and adding necessary users to that profile. Then give access to that group profile for remote database. 

Wish we can use ADDSVRAUTE command, so no need to mention user credentials in the program while trying to connect to RDB.

However in our scenario, there are many thousands of user profiles already created and in use. If I create a new group profile, is it possible to assign all existing user ids to that profile? Also believe any new user profiles created, had to be explicitly added to this group. Any suggestions or recommendations please?

Thanks!

This article should help. Its a way of defining a 'service account' to be used for these types of transactions without the need to either have every profile on every lpar or to have the passwords be the same across all lpars for a specific profile. You will likely want to discuss this with your system admins as well though. Good luck - hope this helps

Simplified DDM and DRDA authentication entry management using group profiles (ibm.com)

Thanks @Rich Malloy. Will check with our admin group for that approach of adding all users.

Hi Prakash - you don't need a new group profile, its just one way of accomplishing this. But yes, you can work with your admins/security engineers to add all those users to a new group profile if you desire. Another approach with this is to simply add all your users that you want to execute this into the "database" via addsvraute command. you can use a CL program to do that for you pretty easily enough. 

@Rich Malloy Thanks for the inputs.

In this article it talks about a group profile and adding necessary users to that profile. Then give access to that group profile for remote database. 

Wish we can use ADDSVRAUTE command, so no need to mention user credentials in the program while trying to connect to RDB.

However in our scenario, there are many thousands of user profiles already created and in use. If I create a new group profile, is it possible to assign all existing user ids to that profile? Also believe any new user profiles created, had to be explicitly added to this group. Any suggestions or recommendations please?

Thanks!

This article should help. Its a way of defining a 'service account' to be used for these types of transactions without the need to either have every profile on every lpar or to have the passwords be the same across all lpars for a specific profile. You will likely want to discuss this with your system admins as well though. Good luck - hope this helps

Simplified DDM and DRDA authentication entry management using group profiles (ibm.com)

Welcome Prakash. One other thought..if this is NOT an interactive process and is only done in batch...perhaps the job that runs in batch could be done via a service account instead of running it under the individual user profiles that submitted it. 

Thanks @Rich Malloy. Will check with our admin group for that approach of adding all users.

Hi Prakash - you don't need a new group profile, its just one way of accomplishing this. But yes, you can work with your admins/security engineers to add all those users to a new group profile if you desire. Another approach with this is to simply add all your users that you want to execute this into the "database" via addsvraute command. you can use a CL program to do that for you pretty easily enough. 

@Rich Malloy Thanks for the inputs.

In this article it talks about a group profile and adding necessary users to that profile. Then give access to that group profile for remote database. 

Wish we can use ADDSVRAUTE command, so no need to mention user credentials in the program while trying to connect to RDB.

However in our scenario, there are many thousands of user profiles already created and in use. If I create a new group profile, is it possible to assign all existing user ids to that profile? Also believe any new user profiles created, had to be explicitly added to this group. Any suggestions or recommendations please?

Thanks!

This article should help. Its a way of defining a 'service account' to be used for these types of transactions without the need to either have every profile on every lpar or to have the passwords be the same across all lpars for a specific profile. You will likely want to discuss this with your system admins as well though. Good luck - hope this helps

Simplified DDM and DRDA authentication entry management using group profiles (ibm.com)