Execute Command Stage - Command ksh did not finished OK

[teddycarebears]

I am having a sequence with Execute Command Stage. I am trying to call sql loader to insert some bulk data. I have all needed parameters and I concatenate them to obtain the needed command. I can see the built command line in Director and it is ok. I execute that command line in shell and it works fine, but if I run the DataStage job that contains it I get the error bellow.

Command ksh did not finished OK, reply = '1'.

Unfortunately this task must be executed like this and not from a ksh file. Nevertheless any possible suggestions will be appreciated.

[ArndW]

The ksh script you are calling returns with a stderr status that is non-zero ("1" in this case) and thus DataStage is assuming that it has errors.
You can use a conditional trigger for the value of "1" as an output link for a successful job call.

[teddycarebears]

The ksh script you are calling returns with a stderr status that is non-zero ("1" in this case) and thus DataStage is assuming that it has errors.
You can use a conditional trigger for the value of " ...

This is all I can see from the message, the rest went to Premium Content

I do not have a file but a simple line that needs to be executed and when this line is executed on shell it works fine. In DataStage the things are not ok. Something like :

Code: Select all

Command: ksh

Parameters: sqllder CONTROL=my_path/my_ctrl_file.ctl USERID=my_oracle_user/my_oracle_pass@my_oracle_environment

[chulett]

Actually, it did not "work fine" from the command line, it's just that there was nothing there to report on the exit status code. As noted, a command that does not return a status code of "0" is considered to have failed in some fashion by anything that actually checks.

Your script should do the same, ensure a zero status for success and something else for a failure. Any idea why it is returning the "1"? Can you post your script?

[teddycarebears]

The code from the previous post is what I put in the 2 textboxes from the Execute Command Stage. Of course, user, password are different words but they do work in command line.

Perhaps it shouldn't be like this and I am doing it wrong.

My logic is to tell the Execute Command Stage to execute a ksh command with the value from the parameters line.

[chulett]

OK, fair enough. People typically do this from within a script where you have control over all aspects of the call. For example, my sqlldr "wrapper" script sets up all of the environment variables needed and double-checks the actual output of the loader log to ensure no errors occurred. All you'll be really sure of is that the sqlldr command itself ran ok, but you won't necessarily catch all errors that way.

One suggestion would be to put "sqlldr" as the command and the rest in the parameter box. I'd also be curious... is the command actually running and performing the load? Or is it failing to run? Have you inspected the contents of the .log file?

[teddycarebears]

I am almost sure it doesn't run at all since I am receiving a warning

Code: Select all

Controler problem: Unhandled failure (1) encountered executing command ksh.

[chulett]

That's another artifact of your original issue, the non-zero exit code which is also being reported by the "Automatically handle" option you've enabled in the Sequence job. An exact search here for "Controler problem: Unhandled failure" will reveal that.

So... you need to actually check. Is your data loaded? What does the .log file say?

[teddycarebears]

My data is not loaded. I am not sure at what .log are you thinking. Oracle .log or DataStage log ?

It was suggested to me that DataStage might not know the path to sqlldr and I am now trying to find it out.

[chulett]

I specified "dot log" and "file" to mean Oracle's sqlldr log file, if you don't see one then it does sound like you could have a path problem, something else that a wrapper script would solve for you.

Try fully pathing the sqlldr command.

[teddycarebears]

Unfortunately .log is not created not even if I use the full path to the sqlldr. I can say now that I am out of ideas

[Sainath.Srinivasan]

What about running the job again ? (possibly after clearing it appropriately).

Ensure to track the ctl and log files.

[chulett]

Find the log entry in the Sequence job's log for the Execute Command stage and double-click on it. Post the contents.

[creatingfusion]

include the following statement in the beginning of yours shell script if its not there....

#!/bin/ksh

Abhijit