Data Mover User Pool

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Data Mover User Pool

Data Mover currently uses ARC, TPTAPI and JDBC to extract data from a source system and load data to the target system. Based on different scenarios, Data Mover may choose to use one of the 3 mentioned load utilities.

When copying data from source to target, the user provides the login information for the source and target systems as part of the job definition that is used to login to the source and target systems to extract/load the data. There are certain limitations in providing the login information for each job that is executed:

  1. ARC does not support the same user being logged in to a system at the same time when performing a copy operation. For example, if two Data Mover jobs that use ARC are run at the same time with the same target user, then an error is thrown by ARC: "Same user already logged in to dbs". This prevents multiple ARC jobs to run at the same time using the same target user.
  2. For security reasons the DBA/Admin may not want to provide target system passwords to any user running the job.

In order to resolve these issues, a feature to support a pool of users was designed.

When using this feature, the user does not provide the target user name and password when executing the Create/Move command. Data Mover selects the target user from the pool of target users instead that is saved using the save_configuration command. When multiple ARC jobs are run at the same time, Data Mover checks if a particular user in the pool is already being used by an ARC task and allocates an alternate available user to other ARC tasks. This way each ARC task logs in to the target system with a different user, thus avoiding the "Same user already logged in to dbs" error. For TPTAPI/JDBC jobs, Data Mover may choose the same target user for 2 or more jobs being run at the same time since there is no problem with multiple users being logged in to a target system at the same time when using those utilities.

Using this feature

  1. The System Admin needs to set up a pool of target User IDs using the configuration file. To do this, run the list_configuration command first to get all of the configuration properties. There is a property called job.useUserIdPool that defaults to false

    Add target users to the pool as:

    <property>
    <key>job.useUserIdPool</key>
    <value>true</value>
    <targetUserPool>
    <system name="grenach">
    <user>
    <name>user1</name>
    <password>pass1</password>
    <encrypted_password></encrypted_password>
    </user>
    <user>
    <name>user2</name>
    <password>pass2</password>
    <encrypted_password></encrypted_password>
    </user>
    </system>

    <system name="poplin">
    <user>
    <name>user1</name>
    <password></password>
    <encrypted_password>dfsdfsdfsdfsed3543sdsfsdfaahrrtrtrtrtrt</encrypted_password>
    </user>
    </system>
    </targetUserPool>
    <description>Purpose: Use a target user from the pool of users. This enables running multiple arc tasks at the same time</description>
    </property>

    The above XML adds 2 target users to the pool for system Grenach and one target user to the pool for system Poplin. Unless the value is set to true, the pool of user IDs will not be used by any Data Mover job.

    Note: ARC users can also be added to the pool through the Data Mover admin portlet.

  2. Passwords can be provided as plain text or in encrypted format. Providing both will result in an error when trying to save the configurations. Providing no username/password when the value for this property is true will also result in an error when trying to save the configurations.

  3. Save the configuration properties using the save_configuration command.

  4. Once the pool of users has been saved, a job can be run using the user pool by specifying <use_userid_pool> in the job definition. E.g.

    <source_tdpid>dmdev</source_tdpid>
    <source_user>user_s</source_user>
    <source_password>pass</source_password>
    <target_tdpid>grenach</target_tdpid>
    <target_user></target_user>
    <target_password></target_password>
    <use_userid_pool>true</use_userid_pool>

    Trying to provide the target user/password when <use_userid_pool> is true results in a create time error. If <use_userid_pool> is set to false then the user will have to provide a target user name and password.

    Also, if the value for job.useUserIdPool was set to false when saving the configuration properties then trying to use the target User ID pool will result in an error.

  5. When <use_userid_pool> is set to true, Data Mover will chose a target user from the target User ID pool. If multiple ARC jobs are being run at the same time with <use_userid_pool> as true, then Data Mover chooses an available target user from the pool that is not already being used by other ARC jobs. This way multiple ARC jobs can run at the same time.

    For TPTAPI/JDBC jobs, Data Mover may choose the same target user for 2 or more jobs being run at the same time since there is no problem with multiple users being logged in to a target system at the same time when using those utilities.

  6. When running ARC jobs with <use_userid_pool> set to true, if all target users in the pool are already being used by other ARC tasks then the ARC task waits for other ARC tasks to complete and return the user to the pool. Once the user is available in the pool the waiting ARC task uses that particular user.

  7. When running an ARC job with <use_userid_pool> set to true:

    If a user in the pool is being used by another ARC job that does not use the User ID Pool (i.e. the target user was specified in the XML file), then

    that user is not chosen from the pool. An alternate user is chosen or if no users are available in pool, the ARC job waits until a user is available.

  8. When running an ARC job that DOES NOT use the User ID pool:

    • If the target user specified is present in the User ID pool and is being used by another ARC job that uses the User ID pool, then the job fails with the following error message:

      Error: Target user is present in the user ID pool and is currently being used by another ARC job. Please try running the job later.
    • The job can be restarted later.
  9. All target users in the target user pool (config_user_pool table) are marked as available every time the Data Mover Daemon is restarted or the job.useUserIdPool configuration property is modified.

Note: The user pool is only for target users. ARC does not have any restriction with same users logging in to the source system at the same time. Therefore, Data Mover does not use a user pool for source users.

Troubleshooting

  1. When the System Admin saves a configuration file containing a pool of users, the users and encrypted passwords are stored in the repository table called config_user_pool. This table can be used to view all the users in the pool that are currently being used and other users that are available.

    Note: Every time a user from the pool is consumed by an ARC Task, the user is marked as unavailable in the config_user_pool table.

  2. If users in the pool were used up by multiple ARC jobs and the ARC processes were manually killed or the Agent was abnormally shutdown, then users may not be returned to the pool.  Restarting the DM Daemon or re-saving the DM configuration properties (save_configuration command) would return all the users back to the available state. If this happens then make sure that all ARC processes have been terminated as well. If the stale ARC processes have not been terminated then trying to login as one of the users in the pool may result in the "Same user already logged in to dbs" error.

9 REPLIES
Teradata Employee

Re: Data Mover User Pool

Hi,

you have written,

"When running ARC jobs with "use_userid_pool" set to true, if all target users in the pool are already being used by other ARC tasks then the ARC task waits for other ARC tasks to complete and return the user to the pool. Once the user is available in the pool the waiting ARC task uses that particular user."

for what duration of time the ARC job wait for the target user to return back to pool if all users in target user ID pool are being used by other ARC jobs? if any task does not return back to pool for 40 minutes or so, still the ARC job keep waiting or it will return the error ? if there is particular duration of time, the waiting jobs waits, can we change the wait timing by any means?

can you please share your views on this point?

Thanks,
Vikrant Dixit
Teradata Employee

Re: Data Mover User Pool

Hi Vikrant

The earlier releases of DM (before 13.10.00.03) had an automatic timeout for ARC jobs waiting on the pool. I believe the timeout was 5 mins. But this timeout has been removed in the newer versions of DM. In the newer versions an ARC job waiting for a user from the pool would continue to wait until a user is available.
The time was removed for the following reasons :

1. Other existing timeouts make it highly unlikely that any ARC job would hang indefinitely. For example if an ARC job hangs for 60 mins, then it is marked as failed and the user is returned back to the pool.

2. It is difficult to choose a reasonable timeout period. Some customers schedule running several large ARC jobs at once in which case they don't want an ARC job waiting for a user from the pool to timeout and fail.

They prefer the waiting ARC job to wait until a user is available and then continue running.

But i can see that having an optional configuration property to set a timeout for waiting ARC jobs would be a good feature to have in future for customers requiring this. I will bring this up to the team.

Thanks
Jaison
Enthusiast

Re: Data Mover User Pool

we have over 500 DM scripts and this feature would be a life saver to get away from non expiring passwords. Has any thought been analyzed to look into the potential for accomodation for accountstring parameter? This would be helpful as not all processes are equal!!
Teradata Employee

Re: Data Mover User Pool

@TeraAbe - we have created a jira task (DM-10383 ) for the logon mechanism and account string being added to the user pool

@Vikrant - we have created a jira task (DM-10384) to set a timeout parameter in user pool jobs.

These will be implemented in future releases. Thanks for your suggestions.

Jaison

Jaison
Teradata Employee

Re: Data Mover User Pool

Hi Jaison,

Thanks for working on this. this will definitely help to limit timeout for waiting jobs.

Thanks for working on this.

Vikrant Dixit

Re: Data Mover User Pool

Hi..

 

Our Data mover(DM) jobs are using  a pool of users for data mover arcmain and tptapi. Assume a scenario , DM has launched multiple tptapi sessions with username "john"(avalable from the user pool). Now another DM job runs in parallel and launches a arcmain, is there a possibility of DM job launching arcmain to pick up "john"? or will it up other pool of users available?

 We are getting frequent "same user logged on to the dbs error" and most of the DM  jobs using arcmain complete. We are not able to find any idle sessions from the same arcmain user while we check history on viewpoint using rewind.

Teradata Employee

Re: Data Mover User Pool

When ARC jobs are run simultaneously with TPT jobs, then the ARC jobs can use the same user from the pool as the TPT jobs. This is OK becasue the  "same user logged on to the dbs error"  only occurs when 2 ARC jobs are logged in to the target system usign the same user.

Its hard to say what is causin the  "same user logged on to the dbs error" error but here are a few reasons :

1. Maybe there is another ARC process outside of datamover (maybe a standalone arc process) that is logged in using one of the same user from the pool. DM does not validate if same users are logged in using a NON-DM application.

2. Maybe the user ID pool is in a bad state. High highly doubt this is the issue but restarting the DMDaemon should fix this issue.

Please make sure there are no orphaned ARC sessions on the TARGET system which could be causing this issue. 

Re: Data Mover User Pool

Thanks Jaison..We are trying to find out the stale archive session. Is there a table on the data mover side either on the DB or on the DM application side to check the assigned and unassigned pool of users? 

Teradata Employee

Re: Data Mover User Pool

You should check for stale sessions on the COPY (target)side. Same ARC users being logged on to the source system will not give you the same user logged on to the dbs error"  error.

Same ARC users logged on to the target system will give you that error. YOu can use view point to check if same users are logged on to target system. Did you try restarting the DMDaemon service just incase ?