Project and Portfolio Management Practitioners Forum
cancel

TIMEOUT ISSUE while migrating .sql files

Highlighted
Subhac
Valued Contributor.

TIMEOUT ISSUE while migrating .sql files

Hi All,

 

We are facing TIMEOUT ISSUE while migrating .sql files migration through PPM.

 

we are looking for some type of “WAIT” or “SLEEP” command that PPM will recognize to wait as some of these scripts that are being executed are taking anywhere to 5 minutes to 30 minutes, and PPM is timing out around the 50 second mark.

 

Please advise.

 

Error Message:

 

16:06:32  15  /
Preparing for further command execution
echo KSC_EXIT_STATUS $? 1
echo KSC_EXIT_STATUS $? 2
echo KSC_EXIT_STATUS $? 3
echo KSC_EXIT_STATUS $? 4
echo KSC_EXIT_STATUS $? 5

KSC Simple Respond Command Failed [Mon Sep 29 10:07:27 EST 2014]


Aborting command execution.
Closing SSH2 Session
Errors occurred during command execution.
 

 

Thanks,

Sravya

19 REPLIES
Mohit_Agrawal
Outstanding Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi Sravya,

 

Please add "$" "echo $" at the end of the line like below and then try.

 

ksc_simple_respond "sqlplus [DEST_ENV.ENV.DB_USERNAME]@[DEST_ENV.ENV.DB_CONNECT_STRING] @$driver" -hide "Enter password:" "[DEST_ENV.ENV.DB_PASSWORD]" "$" "echo $"

 

Please let us know the output.

 

Thanks

Mohit

JHarris941
Super Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

You can also increase the timeout value for the object type in the workbench. We have Materialized Views that can take up to 5 hours to complete and we had to do that. I would say try to increase the timeout value first. 

 

Jajcen

Subhac
Valued Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi Mohit,

 

Thanks for your response.

 

We have already tried the option of appending $echo$ at the end of KSC_SIMPLE_RESPOND statement, but of no use..

 

So we got below suggestion from HP:

 

Workaround / Fix

Make sure to set all the prompts to be "$" in both source and destination environments .profile and add "$" "echo $" at the end of ksc_simple_respond command line.

 

 

They suggested to add $echo$ at the end as you suggested and also to add "$" in both Source and destination environments.profile.

 

As we are not sure about where exactly is the environment.profile, we checked with HP team and they suggested below:

 

The .profile is a file from Cygwin, so you will have to go to the Cygwin folder and search for a file called bash .profile.

 

Could you please let us know where exactly we find cygwin\environment\.profile on a Unix Box as our PPM is installed on a Unix Box?

 

Thanks in Advance.

changl
Respected Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

In addition to the suggestions below from support, please be aware the legacy SSH and SSH2 client implementations do not respond to keep alive messages.

 

The workaround in the past for long duration SSH connections was to increase

 

ClientAliveCountMax

 

on the SSH Server to avoid encountering a timeout. 

 

PPM's new SSH2 Client implementation, most likely does not have this issue.

Mohit_Agrawal
Outstanding Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi Sravya,

 

The .profile file for source and destinations are on those servers only. You can ask DBAs that manitain the servers, they should be aware about the .profile on their servers.

 

And do you have $ in your Env DB password?

If yes then please try once after removing $ from your password.

 

 

Thanks

Mohit

Subhac
Valued Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi Changl,

 

Could you please let us know where exactly we have this 'Client Active Count Max'.If possible any screenprint for ref.

 

We have gone through the server.cong file and we found Keep_Alive_Interval that is currently set to: 2.

 

Is this something related to keep alive messages that you are talking below? 

 

Please do mention about the client Active Count Max as we are eagerly waiting for a fix for this Timeout issue?

 

Thanks in Advance.

changl
Respected Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi Subhac,

 

grep -i clientalive /etc/ssh/sshd_config

 

Will show you the SSH Server's configuration, if this is where your sshd_config file is located.  

 

This would be the SSH server that you're connecting to, specified by your PPM environment.  If this keepAlive issue is the problem, you should be able to increase ClientAliveCountMax and restart the SSH server.

 

Also please follow up with the previous recommendations also

 

Workaround / Fix

Make sure to set all the prompts to be "$" in both source and destination environments .profile and add "$" "echo $" at the end of ksc_simple_respond command line.

 

As this type of problem with timeout has been resolve using those instructions.

 

 

Loc_Nguyen_PPM
Outstanding Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi Subhac,

 

In all source and destination environments the following configuration items need to be set in $HOME/.bash_profile or $HOME/.profile (UNIX) or cygwin/etc/profile (Cygwin on Microsoft Windows) to avoid SCP transfer failures:

export PS1='$ '
export TERM=dumb
stty cols 999

 

After that add "$" "echo $"  at the end of the line

 

ksc_simple_respond "sqlplus [DEST_ENV.ENV.DB_USERNAME]@[DEST_ENV.ENV.DB_CONNECT_STRING] @$driver" -hide "Enter password:" "[DEST_ENV.ENV.DB_PASSWORD]" "$" "echo $"

 

Regards.

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
fabsvz
Respected Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi Vinloc81

 

Can you let me know where can I find the .profile file on Unix?

 

Thanks

HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
Subhac
Valued Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi ,

 

When you login in PPM server box(Home Directory).Please enter ls -alrt | grep profile , you can see all the files with .profile . Then use cat .profile command, you can get the required details o0f .profile.

 

Hope this helps.

 

Thanks!!

Subhac
Valued Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi,

 

Thanks alot for the response.

 

We will make the below suggested changes and update you with the results:)

 

Regards

 

 

Dennis Handly
Acclaimed Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

>Can you let me know where can I find the .profile file on Unix?

 

In your home directory: ~/.profile

If you don't know this, you should probably let a Unix/Linux sysadmin make this change.

Subhac
Valued Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi 

 

 

 

  1. There is a “.txt” file on the PPM Database, that will list all of the Environments currently setup within PPM, and once we locate that “.txt” file, then we need to prefix a  “$” in front of those instances having the connectivity issues.

 Answer from HP: Yes there should be a text file name .profile so it can editable, also it has to be under the Cygwin folder in the PPM Server.

 

 2.   The only Database that needs to be updated is PPM only…..none of the R12 Databases need to have any parameters changed.

 

Answer from HP:  Exactly, in the PPM Database should be found all the different .profile for all the environments so the only Database that has to be changed is the PPM DB.

 

 

Our Query in point(1): Where can we find this env.txt file in PPM Server or the file what HP support person is suggesting. 

 

Please help us in finding this.

 

Thanks alot !!

 

Loc_Nguyen_PPM
Outstanding Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi,

 

The file they  suggesting is .profile. This is the file i told you should find it and made the changes as my previously post. Did you find it and made the changes ?

 

Please take a look the link below to know .profile on UNIX.

 

 

Regards.

 

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
Subhac
Valued Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi Vinhloc81,

 

Could you please check the attachment and confirm?

 

Thanks,

Sravya

Loc_Nguyen_PPM
Outstanding Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi,

 

Please take a look the link below

 

http://www.theunixschool.com/2011/07/what-is-profile-file.html

 

 

Regards.

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
Subhac
Valued Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi,

 

Thanks for the link provided..

 

Just want to confirm one thing.

 

Do we have CYGWIN folder on PPM SERVER.If Yes, Could you please let us know the path of it as HP Team suggested us below:

 

HP : Yes there should be a text file name .profile so it can editable, also it has to be under the Cygwin folder in the PPM Server.

 

Thanks!!

Loc_Nguyen_PPM
Outstanding Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

Hi,

 

Cygwin is only required when you install the PPM Server on Window environment. It is a Unix-like environment and command-line interface for Microsoft Windows. So, you don't need it on Unix environment.

 

Kudo is wellcome !!!

 

Thanks.

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
Dennis Handly
Acclaimed Contributor.

Re: TIMEOUT ISSUE while migrating .sql files

>Could you please check the attachment and confirm?

 

Please attach a text (.txt) file, not a picture of one.

(While they say a picture is worth a 1000 words, it isn't worth much if you can't grep or cut&paste words out of it.)

 

Your .profile has a fancy prompt, PS1, exactly what they said to change.