How to use Call DSExecute

[nchincholikar]

Hi,
I want to use output of below query in transformer; I have created routine for the same which is not giving any output.

$INCLUDE DSINCLUDE JOBCONTROL.H

DB2Cmd1="db2cmd; db2 connect to target user user1 using pass; db2 SELECT 1 FROM schema.CUST_HDR fetch first 1 rows only; db2 connect reset;"

Call DSExecute("DOS", DB2Cmd1, ScreenOutput, ErrCode)

Ans=ScreenOutput

[ArndW]

What is the contents in the log if you

Code: Select all

CALL DSLogWarn(ScreenOutput,'')

?
Also, what is your ErrorCode value?

[bikan]

I'm getting 0 for ErrorCode value, and after adding below line

Code: Select all

CALL DSLogWarn(ScreenOutput,'') 

I'm getting below Test output

TEST #1
*******

Arg1 =

Test completed.

DSLogWarn called from :
Message to be logged is...
>

Result =


Let me know what needs to be modified ...

[ArndW]

That doesn't look quite right and didn't help much, please try

Code: Select all

CALL DSLogWarn('Command "':DB2Cmd1:'" returned status ':ErrCode:' and screen output "':ScreenOutput:'".','')

My first guess is that DB2CMD is not in the path for your shell; but the output of the debugging statement above might confirm that.

[ray.wurlod]

What does db2cmd do? It finishes, then there's a db2 command. What does it do? Then there's another two db2 commands. Is that really what you intended?

[bikan]

Hi I got following output after adding

Code: Select all

CALL DSLogWarn('Command "':DB2Cmd1:'" returned status ':ErrCode:' and screen output "':ScreenOutput:'".','')

TEST #1
*******

Arg1 =

Test completed.

DSLogWarn called from :
Message to be logged is...
> Command "db2cmd; db2 connect to target user user1 using pass; db2 SELECT 1 FROM SCHEMA.CUST_HDR fetch first 1 rows only; db2 connect reset;" returned status 0 and screen output "".

Result =

Code: Select all

Ray,
I have a following requirement.
I want to get max(Act_Nbr) which was generated by one job and using this numbe I want to increment Act_Nbr by in next job. So I'm trying to get Max number using Query via routine.

[bikan]

Hi I got following output after adding

Code: Select all

CALL DSLogWarn('Command "':DB2Cmd1:'" returned status ':ErrCode:' and screen output "':ScreenOutput:'".','')

TEST #1
*******

Arg1 =

Test completed.

DSLogWarn called from :
Message to be logged is...
> Command "db2cmd; db2 connect to target user user1 using pass; db2 SELECT 1 FROM SCHEMA.CUST_HDR fetch first 1 rows only; db2 connect reset;" returned status 0 and screen output "".

Result =

Ray,
I have a following requirement.
I want to get max(Act_Nbr) which was generated by one job and using this numbe I want to increment Act_Nbr by in next job. So I'm trying to get Max number using Query via routine.

[ArndW]

The output looks just right. Remember, the DB2CMD command starts a new command shell, so the output is no longer visible and therefore your ScreenOutput is empty. Try it without the (unnecessary) DB2CMD.

[bikan]

After removing DB2CMD I'm getting below message ..

EST #1
*******

Arg1 =

Test completed.

returned status 8 and screen output "DB21061E Command line environment not initialized.> ".

Result = DB21061E Command line environment not initialized.

[ArndW]

Hmmm, that means this command is necessary under windows. How about trying

Code: Select all

db2cmd "db2 connect to target user user1 using pass; db2 SELECT 1 FROM SCHEMA.CUST_HDR fetch first 1 rows only; db2 connect reset;"

or checking on how the db2cmd /i option might help in redirecting output.

I saw this example on the IBM page:

Code: Select all

db2cmd /c /w /i db2 get dbm cfg > myoutput

which might help as well.

[ray.wurlod]

Why not just create a server job to execute the query and load the result either into that job's user status area or into a file for subsequent retrieval?