Managing Return Codes for Script Runner (PTIDBRUN).

Document ID : KB000055791
Last Modified Date : 14/02/2018
Show Technical Document Details

Overview

Script Runner (ptidbrun.exe) can be used to execute ADT/ADTEME scripts via an external scheduling program such as CA's AutoSys. When Script Runner is used to execute scripts, the data movement server's scheduler spawns a separate process (i.e. IPrun.exe) that represents the running job. And since Script Runner uses the return code of IPrun, incorrect information can be passed back even if script failed. In other words, scripts can run successfully to completion (Iprun.exe returns a code of 0) even if the job the script was to perform failed. By using the SET_RETURN_VALUE Built In Function inside a script's StandardRoutineDefinitions code fragment, you can evaluate the execution of a script and then pass this information back to the external scheduling program.

NOTE: This technical document ONLY demonstrates how to modify the Communications error handler subroutine of the StandardRoutineDefinitions code Fragment. And depending on your needs, you may want to modify the Runtime error handler subroutine as well.

Understanding and Using Script Runner

To start a request from nearly any machine without having to install Advantage Data Transformer you use the Script Runner program ptidbrun.exe. This utility has the following features:

  1. The Script Runner starts a data movement request on a local or remote Server. The request will run on the Data Movement server immediately as long as the maximum number of simultaneous executions has not been exceeded. (You specify this number in the Scheduler tab of the Administer Servers dialog box in the Script Manager.) Otherwise, the request will run as soon as possible.
  2. Needs only Enterprise Communicator installed on the machine where you are running this utility. Advantage Data Transformer or the Server does not have to be installed on this machine if you are running the request remotely.
  3. The Script Runner requires that the Scheduler on the target Server is running.
  4. The Script Runner can be run from a scheduling utility.
  5. The Script Runner returns the same code that the script returns to the Script Manager, letting you know that the request ran successfully. See "Modifying IPrun Return Code To Match Script Communication Error" below for more information on how to return Source/Target errors that occurred within the request (Scripts ONLY).

You can run Script Runner from the command line by using the following syntax in the \Program Files\CA\Advantage Data Transformer\bin directory:

ptidbrun request [-s server_name] [-p password] [-n] [-? | -h]

Where:

  • request is the name or ID of the compiled request. You can obtain the ID from the messages that the Console produces as it runs a request. These messages are saved in the System Log, located in \Program Files\CA\Advantage Data Transformer\bin\log. A request is assigned an ID when it is first saved in the IDB.
  • -s server_name is the name of the Server where you want to run the request. If the Scheduler is local, this parameter is unnecessary.
  • -p password is the password for the administrator ID. This parameter is required if there is a password defined for the ID.
  • -n suppresses the banner, which displays the version number and copyright information.
  • -? and -h display the syntax and then exit the utility.

The example below runs a request called "Move_Data_SQL" on the Advantage Data Transformer Server called dm-server1. The Advantage Data Transformer administrators ID's password is "secret".

ptidbrun Move_Data_SQL -s dm-server1 -p secret

NOTE: The directory where Script Runner was installed MUST be in the System PATH Environment variable in order for a Scheduler program to execute ptidbrun.exe correctly. The "START" NT command should not be used when running ptidbrun.exe.

Return CodeDescription
0The request ran successfully
-1The Scheduler could not run the specified request. Possible causes for this code could be that the request does not exist or is not compiled using the current version of ADT compiler.
-2Ptidbrun.exe timed out waiting for a response from the Scheduler. This might occur because the Server is not running or PEC's RTserver is overloaded.
-3The script has been terminated with the kill command through the Server Console.
XXXXThe request has exited with a custom return code set by the SET_RETURN_VALUE() Built in Function of the ADT product's Scripting Language

Modifying IPrun Return Code To Match Script Communication Error

By modifying the IPrun return code to match that of the script's communication [error] state, you can then return this information back to the external scheduling program to manage the submission of any subsequent scripts. The following code fragment samples demonstrate what can be done using the buyilt in function SET_RETURN_VALUE(). There are many more Communication Errors possible and they can easily be added to the CASE Statements. As with any sample, the code will need to be modified to fit your environment.

Below is the Communications Error Handler routine modified to set the return value of the script equal to the error code returned by the communications handler. In this section, the code for Global Status and Notification is commented out.

NOTE: If you comment out the Script Runner Return code segment and uncomment the Global Status and Notification segment you will again ALWAYS get an error return of 7



REM *
REM * Communications error handler
REM * The behavior for Comm_Error is that it continues processing EXECPT for errors in the CASE statements
SUB Comm_Error()
DEF term AS BOOLEAN
DEF msg AS STRING
REM * Start of custom variable declarations for Script Runner
DEF comm_exit AS INT
REM * End of custom variable declarations for Script Runner
REM * MESSAGE line below is used for getting exact Commerrcode for CASE statements below.
REM * Comment out MESSAGE line below for normal operations.
MESSAGE("Commerrcode is  ",COMMERRCODE())   
DO WHILE COMMERRCODE() <> _IPRET_SUCCESS
   msg = "Communications error: " + COMMERRFORMAT() + " at line # " + TOSTRING(ERL())
   IF ERRLABEL() <> "" THEN
      msg = msg + " near label " + ERRLABEL()
   END IF
   MESSAGE(msg)
IF TRUE = COMMERRGENERIC() THEN
   SELECT CASE COMMERRCODE()
   CASE _IPRET_NOT_CONNECTED
      term = TRUE
      comm_exit = _IPRET_NOT_CONNECTED
   CASE _IPRET_MEMORY_ERROR
      term = TRUE
      comm_exit = _IPRET_MEMORY_ERROR
   CASE _IPRET_BIND_ERROR
      term = TRUE
      comm_exit = _IPRET_BIND_ERROR
   CASE _IPRET_TABLE_NOT_FOUND
      term = TRUE
      comm_exit = _IPRET_TABLE_NOT_FOUND
   CASE _IPRET_NO_KEY_FIELDS 
      term = TRUE
      comm_exit = _IPRET_NO_KEY_FIELDS 
   END SELECT
END IF
IF FALSE = COMMERRGENERIC() THEN
   SELECT CASE COMMERRCODE()
   CASE 10003                  REM* Microsoft SQL : Login failed for user 
      term = TRUE
      comm_exit = 10003
   CASE 208                    REM* Microsoft SQL :  Invalid object name
      term = TRUE
      comm_exit = 208
   CASE 12154                  REM* ORA-12154: TNS:could not resolve service name
      term = TRUE
      comm_exit = 12154
   CASE 01017                  REM* ORA-01017: invalid username/password; logon denied
      term = TRUE
      comm_exit = 01017
   CASE 00942                  REM* ORA-00942: table or view does not exist
      term = TRUE
      comm_exit = 00942
   CASE 00001                  REM* ORA-00001: unique  constraint violated
      term = TRUE
      comm_exit = 00001
   END SELECT
END IF
COMMERRCLEAR()
LOOP
REM * Start of code for Script Runner Return code.
REM * Comment the next 4 lines out if using Global Status and Notification
IF TRUE = term THEN
    SET_RETURN_VALUE(comm_exit)     REM* Sets IPrun return value to real communication error.
    STOP
END IF
REM *  End of code for Script Runner Return code.  
REM *
REM * Start of the code that should be uncommented for Global Status and Notification
REM * IF TRUE = term THEN
REM *
REM * We can store the Error Message in a Global Variable
REM * global_status is declared in StandardRoutineDeclarations:
REM *
REM *     tmp = GLOBALGET(global_status)
REM *     IF ISNULL(tmp) THEN
REM *         GLOBALCREATE(global_status, TRUE, TRUE)
REM *     END IF
REM * GLOBALSET(global_status, "Error - " + COMMERRFORMAT() ) ' set global var to indicate failure
REM *
REM * Send out Notification:
REM * Single quotes can be included in msg_var by COMMERFORMAT, which causes an error if 
REM * you try to send msg_var as the body of an MS-Outlook e-mail.  The following loop 
REM * removes them:
REM *     DEF pos AS CARD
REM *     pos = INSTR(msg_var, "'") 
REM *     DO WHILE pos <> 0
REM *         msg_var = LEFT(msg_var, pos - 1) + RIGHT(msg_var, LEN(msg_var) - pos)
REM *         pos = INSTR(msg_var, "'") 
REM *     LOOP
REM * IF ISNULL(msg_var) THEN
REM *     msg_var = "NULL" 
REM * END IF
REM *
REM * If you're running SQL-Mail and Outlook, you can un-rem this next command for e-mail notification:
REM * Send_Exchange_Mail("User Name", "ADT job: " + job_name + ": Fatal comm error", msg_var,"") 
REM *
REM * If you are using the Global Status and Notification your return value will ALWAYS be 7!
REM *
REM *     SET_RETURN_VALUE(7)    ' So that Workflow scheduled dependent jobs wont run
REM *     
REM *     STOP
REM * 
REM * END IF
REM *
REM * End of the code that should be uncommented for Global Status and Notification
END SUB

Below is the v2.0 GA version of the Communications Error Handler routine from the StandardRoutineDefinitions code fragment with no SET_RETURN_VALUE commands used.


REM *
REM * Communications error handler
REM *
REM * The default behavior for Comm_Error is to stop after the first communication error
REM * the program encounters.  Previous behavior allowed the Comm_Error subroutine
REM * to return an error and then continue processing the program unless a system error 
REM * occurred.  If you prefer the previous behavior where Comm_Error continues then remove
REM * the 'REM *' comments from the subroutine  
SUB Comm_Error()
DEF term AS BOOLEAN
DEF msg AS STRING
DO WHILE COMMERRCODE() <> _IPRET_SUCCESS
msg = "Communications error: " + COMMERRFORMAT() + " at line # " + TOSTRING(ERL())
IF ERRLABEL() <> "" THEN
msg = msg + " near label " + ERRLABEL()
END IF
MESSAGE(msg)
REM * Start of the code that should be uncommented for continuous program processing
REM *    IF TRUE = COMMERRGENERIC() THEN
REM *      SELECT CASE COMMERRCODE()
REM *      CASE _IPRET_NOT_CONNECTED
REM *        term = TRUE
REM *      CASE _IPRET_MEMORY_ERROR
REM *        term = TRUE
REM *      CASE _IPRET_BIND_ERROR
REM *        term = TRUE
REM *      END SELECT
REM *    END IF
REM * End of the code that should be uncommented for continuous program processing
COMMERRCLEAR()
LOOP
REM * Start of the code that should be uncommented for continuous program processing
REM *  IF TRUE = term THEN
STOP
REM *  END IF
REM * End of the code that should be uncommented for continuous program processing
END SUB

NOTE: You must remove the REM statements indicated above for continuous program processing

This next version of the Communications Error Handler routine is not only modified to set the return value of the script equal to the error code returned by the communications handler, but also allows storage of the error message and email notification to be sent to the Data Transformer Administrator.

NOTE: SET_RETURN_VALUE is always set to 7 on failure in this code fragment.


REM *
REM * Communications error handler
REM *
SUB Comm_Error()
REM * You can rem out this next !INCLUDE statement if you're not using SQL Mail and Outlook:
!INCLUDE "Exchange_Email_Definitions"           
reprocess = TRUE
DO WHILE COMMERRCODE() <> _IPRET_SUCCESS
   msg_var = "Communications error: " + COMMERRFORMAT() + " at line # " + TOSTRING(ERL())    
   IF ERRLABEL() <> "" THEN
      msg_var = msg_var + " near label " + ERRLABEL()
   END IF
   MESSAGE(msg_var)
   IF TRUE = COMMERRGENERIC() THEN
   SELECT CASE COMMERRCODE()
   CASE _IPRET_NOT_CONNECTED
      term = TRUE
   CASE _IPRET_MEMORY_ERROR
      term = TRUE
   CASE _IPRET_BIND_ERROR
      term = TRUE
   END SELECT
REM * Here, in ELSEIF statements, you can place error handling for specific
REM * source/target error codes.
REM * If SQL Server PK Violation Error for insert, we can redirect store-mode to Update 
ELSEIF  2601 = COMMERRCODE()  THEN
   MESSAGE("Unique Key violation on Record, " + record_key)
END IF
COMMERRCLEAR()
LOOP
IF TRUE = term THEN
REM * We can store the Error Message in a Global Variable
REM * global_status is declared in StandardRoutineDeclarations:
tmp = GLOBALGET(global_status)
IF ISNULL(tmp) THEN
   GLOBALCREATE(global_status, TRUE, TRUE)
END IF
   ' set our global variable to '            ' indicate failure
GLOBALSET(global_status, "Error - " + COMMERRFORMAT() )
REM * Send out Notification:
REM * Single quotes can be included in msg_var by COMMERFORMAT, which causes an error if 
REM * you try to send msg_var as the body of an MS-Outlook e-mail.  The following loop 
REM * removes them:
DEF pos AS CARD
pos = INSTR(msg_var, "'")
 
DO WHILE pos <> 0
   msg_var = LEFT(msg_var, pos - 1) + RIGHT(msg_var, LEN(msg_var) - pos)
   pos = INSTR(msg_var, "'") 
LOOP
 
IF ISNULL(msg_var) THEN
   msg_var = "NULL" 
END IF
 
REM * If you're running SQL-Mail and Outlook, you can un-rem this next command 
REM * for e-mail notification:
REM * Send_Exchange_Mail("DT_Admin_ID", "Transformer job: " + job_name + ": 
REM * Fatal communications error", msg_var,"") 
 
SET_RETURN_VALUE(7)    ' So that Workflow scheduled dependent jobs won't run
 
STOP
 
END IF
 
END SUB