Automic Workload Automation

Expand all | Collapse all

Retrieve system return code from DB

  • 1.  Retrieve system return code from DB

    Posted 08-19-2014 09:20 AM

    Hey,

    How to retrieve all "system return code" from database?

    Thanks



  • 2.  Retrieve system return code from DB

    Posted 08-19-2014 01:00 PM

    Hi Haim, which product are you using? :-)  

    On a similar note, I would like to know if it's possible to retrieve the original system return code in AE, if you've used a condition/script to "modify status" ... which forces to change the "return code" to zero if you want ENDED_OK status.  



  • 3.  Retrieve system return code from DB

    Posted 08-19-2014 02:56 PM

    Jessica:

    We are under OM V8 and have not been able to locate table columns that retains the original return codes.  It appears that the EH/AH_RetCode/Status columns are changed when the MODIFY_STATE statement is used in the Post Process and not copied somewhere else; if it is, it’s well hidden.

    Our convention is to use the ADD_COMMENT function to record the original and subsequent change.  Not ideal, but better than nothing.  Sometimes we will subsequently use the PREP_PROCESS_COMMENT to extract the needed information.  It’s a bit cumbersome as just using a GET_STATISTIC_DETAIL function would be more straight forward.

    Sample code:

    :SET &rc = GET_STATISTIC_DETAIL(,RETURN_CODE) :SET &rc = FORMAT(&rc) :SET &src = GET_STATISTIC_DETAIL(,STATUS) :SET &src = FORMAT(&src) :SET &emsg = "Return_code=&rc, Status=&src" :PRINT &emsg :ADD_COMMENT ,"Original &emsg" :MODIFY_STATE STATUS_TEXT="Retcode=&rc, Status=&src" :MODIFY_STATE RETCODE=0

    Idan:

    If you are using OM or AE the above should work for retrieving either the job’s return code or its Status, i.e. "system return code", if in fact that is what you are asking.



  • 4.  Retrieve system return code from DB

    Posted 08-20-2014 02:16 AM
    Hey Jessica/Mark,
    I would like to know how can I get the return code by sql query from database (ms-sql).

    Thanks,
    Idan


  • 5.  Retrieve system return code from DB

    Posted 08-20-2014 01:51 PM

    Hi Idan, which product are you using?



  • 6.  Retrieve system return code from DB

    Posted 08-20-2014 03:56 PM
    I am assuming you are talking about the Status Codes.  Im not sure what else you mean.  
    You can't get ALL of the status codes from the Database.  Only those that have been used.

    SELECT DISTINCT EH_STATUS
    FROM UC4.DBO.EH  

    Distinct just allows you to get rid of the duplicates.  You can put some conditions on there to narrow it down to specific jobs if you want. 
    If you want a complete list of status codes and what they mean, it is available in the help documentation.  

     



  • 7.  Retrieve system return code from DB

    Posted 08-20-2014 05:15 PM

    For AE status codes:  Documentation > User Guide > Objects > Working with Objects > Returncodes > System Return Codes of Executable Objects 

    If you want the return codes from the jobs themselves... 

    For OM or AE: select ah_retcode from ah where ah_idnr = [your RUNID]

    For AM: select so_return_code from so_job_history where so_jobid = [your jobid]



  • 8.  Retrieve system return code from DB

    Posted 08-27-2014 02:52 AM
    Hey Jessica and Tyler,
    I meant to get all of the status code from db like Tyler said.
    I have the following query and I would like to get, by select query, all relevant system return code and not declare temp table:
    (I would like to do a daily checking for jobs in activities with specific status)

    declare @statusTable table
    (
         systemReturnCode int,
         taskStatusName varchar(1000)
    )
    insert into @statusTable values (1543,'Unknown')
    insert into @statusTable values (1560,'Workflow is blocked.')
    insert into @statusTable values (1561,'STOP - Client-wide stop of automatic processing.')
    insert into @statusTable values (1562,'HELD - Manual stop has been set.')
    insert into @statusTable values (1563,'STOP - Automatic processing has been stopped.')
    insert into @statusTable values (1564,'STOP - Queue processing has been stopped.')
    insert into @statusTable values (1852,'Rejected')
    select EH.EH_AH_Idnr as jobRunID,
            EH.EH_Name as jobName,
            EH.EH_OType as jobType,
            EH.EH_Status as statusId,
            status.taskStatusName as statusName,
            EH.EH_StartTime as jobStartTime,
            EH.EH_Client as clientNumber,
            EH.EH_RunTime as runTimeInSeconds,
            USR.USR_FirstName + ' ' + usr.USR_LastName as userFullName
    from EH inner join
    USR on EH.EH_USR_Idnr = USR.USR_OH_Idnr inner join
    @statusTable status on EH.EH_Status = status.systemReturnCode
    UNION ALL
    select AH_Idnr as jobRunID,
            AH_Name as jobName,
            AH_OType as jobType,
            AH_Status as stausId,
            null as statusName,
            AH_TimeStamp2 as jobStartTime,
            AH_Client as cilentNumber,
            AH_RunTime as runtimeInsSeconds,
            USR.USR_FirstName + ' ' + USR.USR_LastName as userFullName
    from AH inner join
    USR on AH.AH_USR_Idnr = USR.USR_OH_Idnr inner join
    @statusTable status on AH.AH_Status = status.systemReturnCode
    --DATEDIFF should retrive jobs were active in last day
    where DATEDIFF(DAY,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()),ah.AH_TimeStamp2 ) ,GETDATE())<=1


    Thanks,
    Idan


  • 9.  Retrieve system return code from DB

    Posted 08-27-2014 09:19 AM

    If you are only wanting to check jobs that are currently active in the activities window you, you only need the EH table.  The AH is an Archive.  

    We have a job that runs and check the current status of jobs and checks for any that might be hung up or overly exceeding a max run.  It ignores status that are waiting for other jobs in a workflow and stuff like that.  

    If you are using the Automation Engine, you can create a SQLI variable which will populate this info into the variable table that can be used in the Engine. 

    SELECT [EH_Name]

          ,[EH_AH_Idnr]

          ,[EH_Ert]

          ,[EH_RDate]

          ,[EH_StartTime]

          ,[EH_Status]

      FROM [uc4].[dbo].[EH]

      WHERE EH_Client LIKE '200'

      AND EH_OType NOT IN ('EVNT','JSCH')

      AND EH_Status < '1800'

      AND EH_Status NOT IN ('1700')

      /*these last three are all one statement---- AND ((x and y) or z)*/

      AND ((DATEPART(hh,SYSUTCDATETIME()) - DATEPART(hh, [EH_RDate]) >= 4 

      AND DATEPART(mi, [EH_RDate]) - DATEPART(mi, SYSUTCDATETIME()) <= 0)

      OR CONVERT(DATE, [EH_RDate]) < CONVERT(DATE, SYSUTCDATETIME()))



  • 10.  Retrieve system return code from DB

    Posted 09-03-2014 02:35 AM
    I got it.
    Thanks  :)