connect to :REMOTESYSTEM and CALL command

[tdavis]

I need to replace OS400 command RUNRMTCMD where I have used it many times in many programs over the past years (due to security concerns). I found that using SQL seems to be an efficient method, but diagnosing errors is hard. If errors do occur I get this message "Trigger program or external routine detected an error." That comes from the field MESSAGE_TEXT below. And the message IDs are SQL0443 and CPF2817. I can look up CPF2817 and find that is "Copy command ended because of error", but what is the error? Is that information hidden somewhere I might be able to use it?

command = "CPYF FROMFILE(SANTEST/CBBOREP) TOFILE(SANTEST/CBBOREP_BK)
FROMMBR(*FIRST) TOMBR(*FIRST) MBROPT(*REPLACE) CRTFILE(*YES)";
connect_system = %trim(REMOTESYS);                                                    
exec SQL connect reset;                            
exec SQL disconnect all;                            
exec SQL connect to :connect_system;                
exec SQL call QSYS2/QCMDEXC(:command);              
exec SQL Get Diagnostics Condition 1                
         :RtnSqlCode        = DB2_RETURNED_SQLCODE,
         :RtnSQLState       = RETURNED_SQLSTATE,    
         :MessageLength     = MESSAGE_LENGTH,      
         :MessageText       = MESSAGE_TEXT,        
         :MessageId         = DB2_MESSAGE_ID,      
         :MessageId1        = DB2_MESSAGE_ID1,      
         :MessageId2        = DB2_MESSAGE_ID2 ;    
exec SQL connect reset;                            
exec SQL disconnect all;                            
exec SQL connect to :localsys; 

[Peder Udesen]

It's an interestng piece of SQL code you have here.

If I understands it correct then you connects to another system and
this causes that you execute SQL commands on that.
Finally you disconnects from it and passes control back to your local system.

One thought - can you retrieve messages using the JOBLOG_INFO function on the remote system?
----

And now to the cause of the problem that was my first thought.
What if SANTEST/CBBOREP is empty?
Then the CPYF will fail. because you have used the MBROPT(*REPLACE) option.

If you delete or clears the tofile SANTEST/CBBOREP_BK before executing the CPYF and then
use MBROPT(*ADD) instead then it wil not fail.

So to round it up:

First clear / delete the file SANTEST/CBBOREP_BK

Then execute this
command = "CPYF FROMFILE(SANTEST/CBBOREP) TOFILE(SANTEST/CBBOREP_BK)
FROMMBR(*FIRST) TOMBR(*FIRST) MBROPT(*ADD) CRTFILE(*YES)"


I hope this will solve your problem.

[tdavis]

Thanks for your reply, but why the CPYF fails is not my end goal. You are correct in your assessment of the various reasons why the CPYF will fail, but what I am trying to do is capture that specific information; that is, return a message that says "From-file CBBOREP in SANTEST not found." instead of "Copy command ended because of error.".

I know that the first message is a diagnostic message and the second is the escape message. I can actually get the escape message ID from DB2_MESSAGE_ID1 and then lookup the error text in the message file. But in some cases the message is even more generic than that. The RUNRMTCMD command actually generates a spool file on the local system that I can either display or read to get more info on why the command failed on the remote system. I am looking for something similar for my replacement.