raghavanm1978
asked on
SQLLDr stops at 60 secs when called from websphere
1. our application runs on websphere, on linux.
2. The application calls SQLLDr
3. SQLLdr opens up, processes some records and stops and after some time websphere process is hung (obviously!)
4. When SQlLdr is opened directly from command prompt and the same .ctl file and .param files are used with the same text data, SQLLdr prefectly loads all the records.
Question is where is the problem. This is a highly critical issue and any help is greatly appreciated!
2. The application calls SQLLDr
3. SQLLdr opens up, processes some records and stops and after some time websphere process is hung (obviously!)
4. When SQlLdr is opened directly from command prompt and the same .ctl file and .param files are used with the same text data, SQLLdr prefectly loads all the records.
Question is where is the problem. This is a highly critical issue and any help is greatly appreciated!
Here's instructions on checking and changing your websphere default timeout.
http://www.techpaste.com/2011/07/12/steps-setting-custom-session-timeout-changing-default-oracle-driver-websphere/
http://www.techpaste.com/2011/07/12/steps-setting-custom-session-timeout-changing-default-oracle-driver-websphere/
ASKER
@dvz why would your solution help?
ASKER
@gary , I am trying out what has been said in the article.. Will let you know if it worked
You've deconstructed the problem down to ensuring that sqlldr works by itself. That suggests the root cause is something outside of sqlldr. If websphere is "hanging", one possibility is that it's timing out (set too low?). The article offers how to check and change the websphere default. Hope that helps.
WAS default session timeout is 30 minutes. This hang is apparently happening after 60 seconds, so it seems unlikely that it is related to the session timeout.
ASKER
I agree it is not the WAS default session time out. And as the code ranch article states, I have the same problem in that if lesser number of records are there, it gets processed. So must be something with the pipe that is being talked about in the article. I shall post the code here soon to get your help
ASKER
Here is the code :
command = "sqlplus -S "+_user+"/"+_password+"@"+ _sid+" @"+m_file_name;
String log_safe_command = "sqlplus -S *****/*****@***** @"+m_file_name;
Runtime r = Runtime.getRuntime();
long startTime = System.currentTimeMillis() ;
try {
Process process = r.exec(command);
command = log_safe_command;
if (process == null) {
Log.error(BatchImportManag er.LOGGER, Messages.g etString(E _PROCINIT, command));
throw new Exception(Messages.getStri ng(E_PROCI NIT, command));
}
else {
Log.info(BatchImportManage r.LOGGER,M essages.ge tString(I_ WAITINGPRO C, command));
process.waitFor();
String elapsed_time = (new Long((System.currentTimeMi llis() - startTime)/1000)).toString ();
Log.info(BatchImportManage r.LOGGER,M essages.ge tString(I_ PROCCOMPLE TE, new String[] {command, elapsed_time}));
StringBuffer strBuffer = new StringBuffer();
String line;
BufferedReader reader = new BufferedReader(new InputStreamReader(process. getInputSt ream()));
while ((line = reader.readLine()) != null) {
strBuffer.append(line).app end("\n");
}
reader.close();
if (process.exitValue() != 0) {
Log.error(BatchImportManag er.LOGGER, Messages.g etString(E _BADEXITCO DE, new String[]{(new Integer(process.exitValue( ))).toStri ng(), command}));
throw new Exception(Messages.getStri ng(E_BADEX ITCODE, new String[]{(new Integer(process.exitValue( ))).toStri ng(), command}));
}
}
}
catch (InterruptedException e) {
Log.error(BatchImportManag er.LOGGER, Messages.getString(E_WAITI NG, new String[] {command,e.toString()}));
throw new Exception(Messages.getStri ng(E_WAITI NG, new String[] {command,e.toString()}));
}
catch (IOException e) {
Log.error(BatchImportManag er.LOGGER, Messages.getString(E_EXECU TING, new String[] {command,e.toString()}));
throw new Exception(Messages.getStri ng(E_EXECU TING, new String[] {command,e.toString()}));
}
command = "sqlplus -S "+_user+"/"+_password+"@"+
String log_safe_command = "sqlplus -S *****/*****@***** @"+m_file_name;
Runtime r = Runtime.getRuntime();
long startTime = System.currentTimeMillis()
try {
Process process = r.exec(command);
command = log_safe_command;
if (process == null) {
Log.error(BatchImportManag
throw new Exception(Messages.getStri
}
else {
Log.info(BatchImportManage
process.waitFor();
String elapsed_time = (new Long((System.currentTimeMi
Log.info(BatchImportManage
StringBuffer strBuffer = new StringBuffer();
String line;
BufferedReader reader = new BufferedReader(new InputStreamReader(process.
while ((line = reader.readLine()) != null) {
strBuffer.append(line).app
}
reader.close();
if (process.exitValue() != 0) {
Log.error(BatchImportManag
throw new Exception(Messages.getStri
}
}
}
catch (InterruptedException e) {
Log.error(BatchImportManag
throw new Exception(Messages.getStri
}
catch (IOException e) {
Log.error(BatchImportManag
throw new Exception(Messages.getStri
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The java world article absolutely helped. If you coded according to that then everything works. Basically the problem was that the output from SQLldr was not read, hence the buffer was full, not allowing SQLLdr to continue
1) How is SQLLDR invoked? Show the code.
2) What error, if any are you getting? Provide a stacktrace.
Finally, look at this:
http://www.coderanch.com/t/549118/java/java/Trouble-executing-sqlldr-java
- Gary Patterson