QSH Error Trapping

[nandas400]

Hey Friends

I am using QSH to update a physical file in my CLLE program. I am trying to trap the error message whenever the update statement fails.But I am not able to trap the error message.

Please see the code sample below:

 DCL        VAR(&MSGID) TYPE(*CHAR) LEN(7)             
 DCL        VAR(&MSGDTA) TYPE(*CHAR) LEN(4)            
 DCL        VAR(&DATE) TYPE(*CHAR) LEN(6)              
 DCL        VAR(&CMD) TYPE(*CHAR) LEN(100) VALUE('db2 +
              "update mylib/myfile set numfield = +   
              ABCDEF"')                              
                
 CHGENVVAR  ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE('Y') 
 MONMSG     MSGID(CPFA981) EXEC(DO)                    
 ADDENVVAR  ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE('Y') 
 ENDDO      
                                           
 CHGENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE('NONE')  
 MONMSG     MSGID(CPFA981) EXEC(DO)                    
 ADDENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE('NONE')  
ENDDO      
                                       
QSH        CMD(&CMD)                              
MONMSG     MSGID(QSH0005 QSH0006 QSH0007) EXEC(DO)
SNDPGMMSG  MSG('Update to file MYFILE failed') + 
             TOUSR(MYPROF)                       
ENDDO                                             
RMVENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT)             
RMVENVVAR ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG)         

ENDPGM

In the above statement , I am trying to update a numeric field with Character value so that the SQL statement would fail and I can trap that error but eventhough the SQL statement fails in QSH, the error is not sent to my CLLE so I could not handle that error.


Your help is greatly appreciated.

Thank You

Regards
Nanda...

[jamief]

Re: QSH Error Trapping

I recommend RPG!

[soup_dog]

Re: QSH Error Trapping

No kidding! Some nice embedded SQL in an RPGILE program would be a piece of cake.

[Mercury]

Re: QSH Error Trapping

It's possible to "trap" this kind of error in a CL. I don't have a system at hand so I can't precisely recall the right way to use it but I'll tell you how one can do tomorrow if you like.

[Marc_d]

Re: QSH Error Trapping

Handling QShell Errors in a CL Program

Article ID: 17841 Posted January 8th, 2004 in Systeminetwork.com > RPG Programming

Q: I'm running QShell commands in my CL programs.
How can I prevent the output from displaying on the screen?
How can I tell whether an error occurred when the QShell command ran?

A: Starting in V5R1, there are environment variables that can be used to control the screen output and the error handling.
You set them using the ADDENVVAR command as discussed below.

Click here to find out more !

Handling QShell Errors in a CL Program
Article ID: 17841Posted January 8th, 2004 in RPG Programming ,No Department Q: I'm running QShell commands in my CL programs. How can I prevent the output from displaying on the screen? How can I tell whether an error occurred when the QShell command ran?

A: Starting in V5R1, there are environment variables that can be used to control the screen output and the error handling. You set them using the ADDENVVAR command as discussed below.

This CL program demonstrates the problem:

PGM
    STRQSH     CMD('ls /dirthatdoesnotexist')
ENDPGM 

When you run that program, a screen will pop up in front of the user that shows an error message stating that the directory cannot be found. However, it's often better for QShell to alert your program, rather than the user, so that it can handle the error. If you set the QIBM_QSH_CMD_OUTPUT environment variable to "NONE," this screen will no longer appear.

PGM
  /* TELL QSHELL NOT TO DISPLAY OUTPUT ON SCREEN  */

       RMVENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT)
       MONMSG MSGID(CPFA981) /* ENVVAR DOES NOT EXIST */
       ADDENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(NONE)
  /* EXECUTE COMMAND -- WE WON'T KNOW IF IT FAILS, HOWEVER.  */

       STRQSH     CMD('ls /dirthatdoesnotexist')
  /* RESTORE DEFAULT BEHAVIOR. */

       CHGENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(STDOUT)
ENDPGM 

Although the user no longer sees the problem, the CL program still doesn't know that an error occurred. If we set the QIBM_QSH_CMD_ESCAPE_MSG variable to Y, an escape message will be sent to alert us that something failed.

PGM

 /* IF VARIABLES WERE SET PREVIOUSLY, REMOVE THEM SO THAT OUR +
    ADDENVVAR COMMANDS WORK.                                  */

       RMVENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT)
       MONMSG MSGID(CPFA981) /* ENVVAR DOES NOT EXIST */

       RMVENVVAR  ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG)
       MONMSG MSGID(CPFA981) /* ENVVAR DOES NOT EXIST */
 /* SET THE COMMAND OUTPUT TO NONE, AND TELL QSH TO ISSUE +
    ESCAPE MESSAGES FOR ERRORS.                               */

       ADDENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(NONE)
       ADDENVVAR  ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE(Y)
 /* EXECUTE OUR QSHELL COMMAND */

       STRQSH CMD('ls /dirthatdoesnotexist')

       MONMSG MSGID(QSH0005 QSH0006 QSH0007) EXEC(DO)
            SNDPGMMSG  MSGID(CPF9897) MSGF(QCPFMSG) MSGDTA('QShell +
                         command failed!')
       ENDDO
 /* RESTORE DEFAULT BEHAVIOR */

       CHGENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(NONE)
       CHGENVVAR  ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE(Y)
ENDPGM 

Now the program knows that the command failed, but it still doesn't know exactly what went wrong. We can get a bit more information if we retrieve the "exit status."

In QShell, each command that you run returns an exit status number ranging from 0 to 255. When this number is 0, the command was successful. When this number is 1 or more, it means that something went wrong. In the example above, the directory could not be found, so the LS command returned a 1 to the shell. When a command in QShell completes in this manner, the QShell will send you a QSH0005 escape message,and you can retrieve the exit status number by examining the first four bytes of the message data.

It's also possible for a QShell command to stop running because it was sent a signal from another process. If that should happen, the command cannot return an exit status because the command never completed. QShell will notify you of this by sending you a QSH0006 message, and you can retrieve the signal number from the first four bytes of message data.

There's one other way that a QShell command could end. If there's a bug in a QShell utility, it might crash, returning an escape message to the QShell. If that happens, QShell will send you a QSH0007 message to tell you about the failure.

This final sample program demonstrates retrieving the information from the three messages described above.

PGM
       DCL VAR(&MSGID) TYPE(*CHAR) LEN(7)
       DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(256)
       DCL VAR(&RESULT) TYPE(*CHAR) LEN(4)
       DCL VAR(&STATUS) TYPE(*DEC) LEN(10 0)
       DCL VAR(&SIGNAL) TYPE(*DEC) LEN(10 0)
       DCL VAR(&CHARSTAT) TYPE(*CHAR) LEN(10)
       DCL VAR(&CHARSIG) TYPE(*CHAR) LEN(10)

 /* IF VARIABLES WERE SET PREVIOUSLY, REMOVE THEM SO THAT OUR +
    ADDENVVAR COMMANDS WORK.                                  */

       RMVENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT)
       MONMSG MSGID(CPFA981) /* ENVVAR DOES NOT EXIST */

       RMVENVVAR  ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG)
       MONMSG MSGID(CPFA981) /* ENVVAR DOES NOT EXIST */
 /* SET THE COMMAND OUTPUT TO NONE, AND TELL QSH TO ISSUE +
    ESCAPE MESSAGES FOR ERRORS.                               */

       ADDENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(NONE)
       ADDENVVAR  ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE(Y)
 /* EXECUTE OUR QSHELL COMMAND */

       STRQSH CMD('ls /dirthatdoesnotexist')
 /* HANDLE ERRORS. */

       MONMSG MSGID(QSH0005 QSH0006 QSH0007) EXEC(DO)

            RCVMSG  MSGTYPE(*LAST) RMV(*YES) MSGDTA(&MSGDTA) +
                      MSGID(&MSGID)

          /* CPF0005 - QSHELL COMMAND ENDED "NORMALLY."           +
                                                                  +
               NOTE:  THIS DOES *NOT* MEAN THAT THE COMMAND       +
                      SUCCEEDED, BUT RATHER THAT THE COMMAND      +
                      WAS NOT ENDED BY ANOTHER PROGRAM OR BY SOME +
                      KIND OF FAILURE. (THIS IS THE MOST COMMON   +
                      MESSAGE)                                   */

            IF (&MSGID *EQ 'QSH0005') DO
                CHGVAR VAR(&RESULT) VALUE(%SST(&MSGDTA 1 4))
                CHGVAR VAR(&STATUS) VALUE(%BIN(&RESULT))
                CHGVAR VAR(&SIGNAL) VALUE(0)
            ENDDO

          /* QSH0006 - QSHELL COMMAND ENDED WHEN IT RECEIVED A  +
                SIGNAL.                                         */

            IF (&MSGID *EQ 'QSH0006') DO
                CHGVAR VAR(&RESULT) VALUE(%SST(&MSGDTA 1 4))
                CHGVAR VAR(&SIGNAL) VALUE(%BIN(&RESULT))
                CHGVAR VAR(&STATUS) VALUE(-1)
            ENDDO

          /* QSH0007 - QSHELL COMMAND ENDED DUE TO AN EXCEPTION,  +
               FOR EXAMPLE THE PROGRAM CRASHED WITH A CPF MSG.   */

            IF (&MSGID *EQ 'QSH0007') DO
                CHGVAR VAR(&STATUS) VALUE(-1)
                CHGVAR VAR(&SIGNAL) VALUE(-1)
            ENDDO
       ENDDO

       /* WHEN &STATUS IS NOT ZERO, WE KNOW OUR COMMAND FAILED.    +
          HERE WE CAN TRY TO HANDLE THE ERROR IF WE LIKE           +
                                                                   +
          THE POSSIBLE VALUES ARE DIFFERENT FOR EACH QSHELL        +
          COMMAND, SO FOR THE SAKE OF DEMONSTRATION, I'LL JUST     +
          SEND AN ESCAPE MESSAGE.                                 */

       IF (&STATUS *NE 0) THEN(DO)
            CHGVAR VAR(&CHARSTAT) VALUE(&STATUS)
            CHGVAR VAR(&CHARSIG) VALUE(&SIGNAL)
            SNDPGMMSG  MSGID(CPF9897) MSGF(QCPFMSG) MSGDTA('QShell +
                         command failed with status ' *CAT +
                         &CHARSTAT *CAT ' and signal ' *CAT &CHARSIG)
       ENDDO
 /* RESTORE DEFAULT BEHAVIOR */

       CHGENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(NONE)
       CHGENVVAR  ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE(Y)
ENDPGM 

[Mercury]

Re: QSH Error Trapping

Message IDs QSH0005, 6 and 7 will tell you that a QSHELL command failed when not recognized but you get no message if the QSHELL command syntax is right but the embedded statement failed for whatever reason.

I tried to solve this issue and here's what I have come up with.

pgm
   /* Handling SQL run errors in QSHELL */
   
   /* File MyLib/qshlog must be created first. +
      CRTPF MyLib/qshlog RCDLEN(80) */   
 
   dclf MyLib/qshlog

   dcl &cmd   *char 256
   dcl &redri *char   1   x'29'
   DCL VAR(&MSGID) TYPE(*CHAR) LEN(7)
   DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(256)
   DCL VAR(&RESULT) TYPE(*CHAR) LEN(4)
   DCL VAR(&STATUS) TYPE(*DEC) LEN(10 0)
   DCL VAR(&SIGNAL) TYPE(*DEC) LEN(10 0)
   DCL VAR(&CHARSTAT) TYPE(*CHAR) LEN(10)
   DCL VAR(&CHARSIG) TYPE(*CHAR) LEN(10)

   clrpfm qshlog

   ADDENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(NONE) +
                REPLACE(*YES)
   MONMSG     MSGID(CPFA980) EXEC(DO)
   RCVMSG     MSGTYPE(*EXCP) RMV(*YES)
   ENDDO

   OVRDBF     FILE(STDOUT) TOFILE(QSHLOG) +
                OVRSCOPE(*JOB)

   ADDENVVAR  ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE(Y) +
                REPLACE(*YES)
   MONMSG     MSGID(CPFA980) EXEC(DO)
   RCVMSG     MSGTYPE(*EXCP) RMV(*YES)
   ENDDO

   CHGVAR &CMD ('(db2 "update MyLib.MyTable +
                            set numfield = ABCDEF" > +    
      /qsys.lib/MyLib.lib/qshlog.file/qshlog.mbr)')

    STRQSH CMD( &cmd )
    /* From "Handling QShell Errors in a CL Program" +
       By Scott Klement                              +
    */
    MONMSG MSGID(QSH0005 QSH0006 QSH0007) EXEC(DO)
     RCVMSG  MSGTYPE(*LAST) RMV(*YES) MSGDTA(&MSGDTA) +
               MSGID(&MSGID)

     /* CPF0005 - QSHELL COMMAND ENDED "NORMALLY."           +
     IF (&MSGID *EQ 'QSH0005') DO
         CHGVAR VAR(&RESULT) VALUE(%SST(&MSGDTA 1 4))
         CHGVAR VAR(&STATUS) VALUE(%BIN(&RESULT))
         CHGVAR VAR(&SIGNAL) VALUE(0)
     ENDDO

    /* QSH0006 - QSHELL COMMAND ENDED WHEN IT RECEIVED A  +
          SIGNAL.                                         */
       IF (&MSGID *EQ 'QSH0006') DO
          CHGVAR VAR(&RESULT) VALUE(%SST(&MSGDTA 1 4))
          CHGVAR VAR(&SIGNAL) VALUE(%BIN(&RESULT))
          CHGVAR VAR(&STATUS) VALUE(-1)
       ENDDO

    /* QSH0007 - QSHELL COMMAND ENDED DUE TO AN EXCEPTION,+
         FOR EXAMPLE THE PROGRAM CRASHED WITH A CPF MSG.+
    */
       IF (&MSGID *EQ 'QSH0007') DO
          CHGVAR VAR(&STATUS) VALUE(-1)
          CHGVAR VAR(&SIGNAL) VALUE(-1)
       ENDDO
    ENDDO

    IF (&STATUS *NE 0) THEN(DO)
      CHGVAR VAR(&CHARSTAT) VALUE(&STATUS)
      CHGVAR VAR(&CHARSIG) VALUE(&SIGNAL)
      SNDPGMMSG  MSGID(CPF9897) MSGF(QCPFMSG) MSGDTA('QShell +
            command failed with status ' *CAT +
            &CHARSTAT *CAT ' and signal ' *CAT &CHARSIG)
    ENDDO
    /* End "Handling QShell Errors in a CL Program" excerpt */

    RCVF
    MONMSG     MSGID(CPF0864) EXEC(GOTO CMDLBL(FINPGM))

    IF (%SST(&QSHLOG 1 8) *NE 'DB20000I') +
      SNDPGMMSG  MSG(&redri *cat ' Erroneous SQL stm or row not fnd. +
          See file MyLib/QSHLOG ')
    ELSE SNDPGMMSG  MSG(&qshlog)

FINPGM:
    DLTOVR     FILE(STDOUT) LVL(*JOB)

endpgm 

When the program runs as is it'll display "Erroneous SQL stm or row not fnd" because tried to update a numeric fld with some character value.
If something is wrong in the QSHELL command, it will display "QShell command failed with status sss and signal xxx".
If all is running fine, it will display "DB20000I THE SQL COMMAND COMPLETED SUCCESSFULLY.".

[nandas400]

Re: QSH Error Trapping

Thanks a lot Philippe for the help and your time.

I appreciate it.

Regards
Nanda.

[nandas400]

Re: QSH Error Trapping

Thank You Marc

Regards
Nanda

[gonen]

Re: QSH Error Trapping

Thanks for this sample

I am running an SQL statement which drops a column.

The SQL fails and the error I get on the MYLIB/QSHLOG looks as:

**** CLI ERROR *****
SQLSTATE: 57014
NATIVE ERROR CODE: -952
Processing of the SQL statement ended. Reason code 10.

The SQL statement that I run is:

 CHGVAR     VAR(&CMD) VALUE('(DB2 "ALTER  TABLE +           
              L7V0603DB2.RIACTBUFF  DROP  COLUMN  +         
              ACTIVITY_NAME" > +                            
              /QSYS.LIB/MYLIB.LIB/QSHLOG.FILE/QSHLOG.MBR)')

How can I catch the actual AS400 error CPxxxxxx ?
I need to add it to the "Reply List Entries " ?


Thanks for your great help here !!!!!

======================================
So I found another thig that might help

The NATIVE ERROR CODE -952 corresponds to SQL0952 which means Processing of the SQL statement ended. Reason code &1.
the &1 is Reason Code 10 which means - 10 -- A cancel reply to an inquiry message was received

so the only thig that's left is to find the inquiry message (the CPxxxxxx) and it does not appear on the joblog


hope it help to undestand how to get the CPxxxxx message
===============================================

thanks again

[DeadManWalks]

Re: QSH Error Trapping

Not sure how to change from SYS convention to SQL naming convention. Try switching the lib.file to lib/file.

On a side note, why bother with QSH. many other simpler ways to do this.

[gonen]

Re: QSH Error Trapping

need QSH...

Sorry - its pre-written

naming convention is not the issue here..


Thanks !!!!!!

[Scott Klement]

Re: QSH Error Trapping

Not sure how to change from SYS convention to SQL naming convention. Try switching the lib.file to lib/file.

The QShell 'db2' command does not have any support for SYS naming convention... only SQL.

I wrote my own replacement command for QShell that does support sys naming convention, though.

[gonen]

Re: QSH Error Trapping

But the issue raised here is trap of SQL code / Inquiry message code in QSH.

What does it have to do with naming convention ?

Sorry.

Do I miss something ?

[Scott Klement]

Re: QSH Error Trapping

But the issue raised here is trap of SQL code / Inquiry message code in QSH.

I wasn't replying to you, I was replying to DeadManWalks.

What does it have to do with naming convention ? Sorry. Do I miss something ?

As near as I can tell, it has nothing to do with naming convention. It also has nothing to do with CPxxxxx messages. The Qshell tools do not use OS/400 error handling mechanisms, they use Unix error handling mechanisms. (Unless I'm wrong?)

[tomliotta]

Re: QSH Error Trapping

need QSH...

Sorry - its pre-written

I'm not clear on what means. It makes it seem as if you can't make any changes to the code. So, what exactly are you needing for help?

In short, why do you need to use QShell for this? What OS version are you running? By V5R3, it's possible to do an ALTER TABLE without leaving CL by calling SQL CLI APIs. It might be possible even before V5R3, but I can't recall trying with CL back then.

Tom