Automic Workload Automation

Expand all | Collapse all

Post-processing of arrays returned by REST jobs

Jump to Best Answer
  • 1.  Post-processing of arrays returned by REST jobs

    Posted 10 days ago
    Edited by Michael Lowry 7 days ago
    I want to do some post-processing of the output of a REST job. The rest job returns several arrays stored in object variables. I want to return the values from these arrays in a data sequence so that I can wrap the REST jobs into EXEC VARA objects.

    However, I don't want to have to write customized post-processing for each REST job. I want one JOBI I can include in the Post-Process tab of every similar job.

    Is there a way to get a list of the variables and/or arrays set in the Definition & Parsing tab of a REST job? Is there a way to automatically process these variables?

    Failing that, is there a way to fill an array by indirect reference?

    By this I mean something like:
    :DEFINE &varValues1#,STRING,100
    :FILL &varValues1#[] = GET_SCRIPT_VAR(&varName1#)

    Unfortunately, the :FILL command does not currently work with the GET_SCRIPT_VAR function. If you try it, you’ll get the following error:
    U00020264 Runtime error in object 'SYSTEM.GLOBAL.CREATE_DATA_SEQUENCE_FROM_REST_JOB_RESPONSE.JOBI', line '00009': Invalid function for FILL command.

    It would be great if there were a function, e.g., PREP_PROCESS_RESPONSEVARS, that provides a data sequence with the names and types of the variables generated in the Response / Definition & Parsing tab of REST job. This could be combined with a new PREP_PROCESS_ARRAY function that could be used to parse the response variables that are arrays.

    This would make it relatively straightforward to write the general purpose REST output parsing JOBI that I have in mind.


  • 2.  RE: Post-processing of arrays returned by REST jobs

    Posted 7 days ago
    I do not think so that you can implement a fully custom Post Process function which deals with these Web Service generated variables.

    However you can create a SQLI VARA which reads the list of variables from a REST Job. You cannot use the variables names in GET_SCRIPT_VARS but you can use them within your Post Process implementation in order to run only these code elements which using directly the variable names.
    Here is an example:

    :SET &HND#=PREP_PROCESS_VAR("VARA.SQLI.GET.REST.OUTPUT.VARIABLE")
    :PROCESS &HND#
    :   SET &VarName# = GET_PROCESS_LINE(&HND#,1)
    :   SET &ColumName# = GET_PROCESS_LINE(&HND#,3)
    :   PRINT "&VarName# &ColumName#"
    :   IF &VarName# = "DATES#"
    :       P "&DATES#[1]"
    :   ENDIF
    :ENDPROCESS​


    Here is the SQL statement, tested with a Postgre DB.

    Select OCV_Value, OCV_VName from OCV where OCV_OH_idnr IN (SELECT OH_Idnr FROM OH where OH_Name = ? and OH_Client = ? ) and (ocv_vname like 'response_part_%_useArray' OR ocv_vname like 'response_part_0_script_%_resourceName')

    ------------------------------
    Sr. Solution Architect
    Broadcom
    ------------------------------



  • 3.  RE: Post-processing of arrays returned by REST jobs

    Posted 7 days ago
    Edited by Michael Lowry 7 days ago
    Thanks, @kay.koll. I came up with something similar. I found the data in OCV, and with the help of GMB on StackOverflow, composed a query that returns the variable details in separate columns.

    SQL query:
    select
    oh_idnr, oh_name
    ocv_vname_common,
    max(case when ocv_var_name = 'code_0' then ocv_value end) code_0,
    max(case when ocv_var_name = 'queryType' then ocv_value end) queryType,
    max(case when ocv_var_name = 'resourceName' then ocv_value end) resourceName,
    max(case when ocv_var_name = 'saveTo' then ocv_value end) saveTo,
    max(case when ocv_var_name = 'useArray' then ocv_value end) useArray
    from (
    select
    oh_idnr, oh_name,
    regexp_substr(ocv_vname, 'response_part_0_script_\d_') ocv_vname_common,
    regexp_replace(ocv_vname, 'response_part_0_script_\d_', '') ocv_var_name,
    CAST(ocv_value as VARCHAR(200)) as OCV_Value
    from OH
    left join OCV on OH.OH_Idnr = OCV_OH_Idnr
    where 1=1
    and OH_Client = ?
    and OH_DeleteFlag = 0
    and OH_Name = 'UC4.EBMTK.GET_PROFILE_LIST-2.JOBS_REST'
    and OCV_VName like ?
    ) t
    group by oh_idnr, oh_name, ocv_vname_common
    ​Example output:

    OH_IDNR OCV_VNAME_COMMON CODE_0 QUERYTYPE RESOURCENAME SAVETO USEARRAY
    8420518 UC4.EBMTK.GET_PROFILE_LIST-2.JOBS_REST $[*].name JSONPath profileNames# variable TRUE
    8420518 UC4.EBMTK.GET_PROFILE_LIST-2.JOBS_REST $[*].id JSONPath profileIds# variable TRUE
    8420518 UC4.EBMTK.GET_PROFILE_LIST-2.JOBS_REST $[*].description JSONPath profileDescriptions# variable TRUE
    8420518 UC4.EBMTK.GET_PROFILE_LIST-2.JOBS_REST $[*].environment JSONPath profileEnvironments# variable TRUE

    This works only for REST jobs that return & parse single-part responses. It could potentially be expanded to support multi-part responses, but this would be a bit of work.

    This addresses the first obstacle: finding the names of the variables.

    Now the second obstacle remains: parsing the content of these variables when they are arrays whose names are not known ahead of time.


  • 4.  RE: Post-processing of arrays returned by REST jobs

    Posted 7 days ago
    Edited by Michael Lowry 7 days ago
    FWIW, here's what I have so far:
    :PRINT "--- BEGIN SYSTEM.GLOBAL.CREATE_DATA_SEQUENCE_FROM_REST_JOB_RESPONSE.JOBI ---"
    :SET &NAME# = &$NAME#
    :SET &Hnd1# = PREP_PROCESS_VAR(SYSTEM.GLOBAL.LIST_REST_JOB_RESPONSE_VARIABLES.VARA_EXEC)
    :SET &PPV_Counter# = 0
    :PROCESS &Hnd1#
    : SET &PPV_Counter# = &PPV_Counter# + 1
    : IF &PPV_Counter# = 1
    !: SET &Result# = CREATE_PROCESS(NEW) ! -- To be continued
    : PRINT "REST job response variables:"
    : ENDIF
    : SET &VarName# = GET_PROCESS_LINE(&Hnd1#,5)
    : SET &VarArray# = GET_PROCESS_LINE(&Hnd1#,7)
    : IF &VarArray# = 'false' OR 'FALSE'
    : SET &VarValue# = GET_SCRIPT_VAR(&VarName#)
    : PRINT "&&&VarName# : &VarValue#"
    : ELSE
    : PRINT "Arrays not supported yet."
    : ENDIF
    :ENDPROCESS
    :CLOSE_PROCESS &Hnd1#
    :PRINT "--- END SYSTEM.GLOBAL.CREATE_DATA_SEQUENCE_FROM_REST_JOB_RESPONSE.JOBI ---"

    Come to think of it, even if there were a way to parse the arrays, I'm not sure there’s a truly elegant way to present multiple values from multiple arrays in a single output data sequence. Perhaps something like this:

    Variable_Name Array Value1 Value2 Value3 Value4 Value5
    profileNames# TRUE DEV ITE PROD EXP2 EXP
    profileIds# TRUE 1 2 3 4 5
    profileDescriptions# TRUE Development Testing Production Experimental 2 Experimental
    profileEnvironments# TRUE DEV ITE PROD EXP2 EXP

    If Array is FALSE, then only Value1 will be populated. It could work I guess. One challenge will be finding out already when writing the header row how many columns are needed. It would be necessary to check the length of every array before even building the data sequence.


  • 5.  RE: Post-processing of arrays returned by REST jobs

    Posted 7 days ago
    Edited by Michael Lowry 7 days ago
    Oh, and for what it's worth, we are moving away from SQLIs. Long-running SQLIs can hang WPs and cause problems with the AE server itself. We have moved to SQL jobs with EXEC VARA wrappers.


  • 6.  RE: Post-processing of arrays returned by REST jobs

    Posted 6 days ago
    @kay.koll: Is it possible to use GENERATE_SCRIPT_VARS to generate array variables?



  • 7.  RE: Post-processing of arrays returned by REST jobs

    Posted 6 days ago
    I do not think so that the GENERATE_SCRIPT_VARS can be used to create arrays. I do not see an option to define whether a variable is an array or not.

    I do not know any solution to generate Array variables as runtime.
    My proposal is to retrieve all variables definitions from the REST Job definition and use for the non-array variables the 'GET_SCRIPT_VAR() .
    You have to create in your code a list of predefined Array variable names of all array variables used in your REST jobs.
    See my example script above.

    ------------------------------
    Sr. Solution Architect
    Broadcom
    ------------------------------



  • 8.  RE: Post-processing of arrays returned by REST jobs

    Posted 6 days ago
    Edited by Michael Lowry 5 days ago
    Yeah that's what I thought. Dynamic processing of arrays does not seem possible in the current version. The names simply must be known ahead of time. This somewhat reduces the utility of arrays as a way of passing data from the REST response to the AE. I prefer not to hard-code names any more than necessary.


  • 9.  RE: Post-processing of arrays returned by REST jobs
    Best Answer

    Posted 5 days ago
    Edited by Michael Lowry 10 hours ago


  • 10.  RE: Post-processing of arrays returned by REST jobs

    Posted 5 days ago
    Edited by Michael Lowry 5 days ago
    Here's what I have in mind:
    :SET &HND1# = PREP_PROCESS_RESPONSEVARS(&$RUNUD#)
    :PROCESS &HND1#
    : SET &ResponseVarName# = GET_PROCESS_LINE(&HND1#,1)
    : SET &ResponseVarType# = GET_PROCESS_LINE(&HND1#,2)
    : IF &ResponseVarType# <> "ARRAY"
    : SET &ResponseVarValue# = GET_SCRIPT_VAR(&ResponseVarName#)
    : PRINT "&&&ResponseVarName# : &ResponseVarValue#"
    : ELSE
    : SET &ResponseVarLength# = GET_PROCESS_LINE(&HND1#,3)
    : DEFINE &MyArray#,STRING,&ResponseVarLength#
    : FILL &MyArray#[] = GET_SCRIPT_VAR(&ResponseVarName#)
    : SET &HND2# = PREP_PROCESS_ARRAY(&MyArray#)
    : SET &ArrayCounter# = 0
    : PROCESS &HND2#
    : SET &ArrayCounter# = &ArrayCounter# + 1
    : SET &ResponseVarValue# = GET_PROCESS_LINE(&HND2#,1)
    : PRINT "&&&ResponseVarName#[&ArrayCounter#] : &ResponseVarValue#"
    : ENDPROCESS
    : CLOSE_PROCESS &HND2#
    : ENDIF
    :ENDPROCESS
    :CLOSE_PROCESS &HND1#

    This requires delivery of three enhancement requests:
    1. PREP_PROCESS_RESPONSEVARS to provide list of REST job response variables
    2. PREP_PROCESS_ARRAY() to process elements of an array
    3. Fill array by indirect reference using GET_SCRIPT_VAR function in :FILL command



  • 11.  RE: Post-processing of arrays returned by REST jobs

    Posted 4 days ago
    Edited by Michael Lowry 4 days ago
      |   view attached
    Ok, I figured out a very roundabout way of doing what I had in mind. Attached are a bunch of JOBI, JOBS_SQL, and VARA_EXEC objects. The main JOBI is SYSTEM.GLOBAL.OUTPUT_HANDLING_POSTPROC.JOBI. It belongs in the post-process tab of the JOBS object whose output/response needs to be processed. To use the JOBI, you have to first set a few variables to tell it how to behave. Here's an example.
    :SET &Create_Ouput_DS# = "YES"
    :SET &Verbose_Logging# = "YES"
    :SET &Output_Delimiter# = ";"
    :SET &Job_Type# = "REST"
    :INC SYSTEM.GLOBAL.OUTPUT_HANDLING_POSTPROC.JOBI
    :SET &ResponseHnd# = LOAD_PROCESS(&$RunID#,&Results#)
    :SET &NumColumns# = GET_PROCESS_INFO(&ResponseHnd#,COLUMNS)
    :SET &NumRows# = GET_PROCESS_INFO(&ResponseHnd#,ROWS)
    :IF &NumRows# > 0
    : SET &NumColumns# = GET_PROCESS_INFO(&ResponseHnd#,COLUMNS)
    :ELSE
    : SET &NumColumns# = 0
    :ENDIF
    :SET &NumColumns# = FORMAT(&NumColumns#)
    :SET &NumRows# = FORMAT(&NumRows#)
    :PROCESS &ResponseHnd#
    : SET &VarName# = GET_PROCESS_LINE(&ResponseHnd#,1)
    : SET &VarType# = GET_PROCESS_LINE(&ResponseHnd#,2)
    : IF &VarName# = "profileNames#"
    : PRINT "Found profile names."
    : ENDIF
    : IF &VarType# = "STANDARD"
    : SET &VarValue# = GET_PROCESS_LINE(&ResponseHnd#,3)
    : ELSE
    : SET &ColumnCounter# = 3
    : WHILE &ColumnCounter# < &NumColumns#
    : SET &ColumnCounter# = &ColumnCounter# + 1
    : SET &ColumnCounter# = FORMAT(&ColumnCounter#)
    : SET &CurrentValue# = GET_PROCESS_LINE(&ResponseHnd#,&ColumnCounter#)
    ! Insert here handling of array variable values.
    : IF &VarName# = "profileNames#"
    : PRINT "Profile: &CurrentValue#"
    : ENDIF
    : ENDWHILE
    : ENDIF
    :ENDPROCESS
    :CLOSE_PROCESS &ResponseHnd#

    The JOBI uses several SQL queries (via EXEC VARAs) to do its magic:
    1. Look up the names of the REST response variables created by the job.
    2. Find out the maximum array length of these variables.
    3. One by one, read the values of these variables.
    4. Create an output data sequence that contains all the response variable names, their types (STANDARD/ARRAY), and their values.

    This was almost certainly more trouble than it's worth, but it was an interesting exercise. I think it might be more interesting to rework this into a more targeted tool that creates a single output data sequence based on the values of a specific REST response array variable.

    Anyway, enjoy!

    Attachment(s)



  • 12.  RE: Post-processing of arrays returned by REST jobs

    Posted 4 days ago
    Edited by Michael Lowry 4 days ago
      |   view attached

    Attachment(s)



  • 13.  RE: Post-processing of arrays returned by REST jobs

    Posted 4 days ago
    Edited by Michael Lowry 4 days ago
      |   view attached

    Attachment(s)



  • 14.  RE: Post-processing of arrays returned by REST jobs

    Posted 4 days ago
    This is an impressive solution.
    However I will support you feature request to extend the ':FILL' / GET_SCRIPT_VAR.
    Thanks for sharing the solution

    ------------------------------
    Sr. Solution Architect
    Broadcom
    ------------------------------



  • 15.  RE: Post-processing of arrays returned by REST jobs

    Posted 3 days ago
    Thanks. Come to think of it, there ought to be a straightforward way to copy an existing array to a new one.


  • 16.  RE: Post-processing of arrays returned by REST jobs

    Posted 4 days ago
      |   view attached
    Here's the XML file containing the AE objects.

    Attachment(s)



  • 17.  RE: Post-processing of arrays returned by REST jobs

    Posted 4 days ago
    I've tried four times to attach the XML, without success. Contact me if you want it.


  • 18.  RE: Post-processing of arrays returned by REST jobs

    Posted 3 days ago
    Hi Michael,

    Thanks for sharing this. Not sure if something has changed but the xml is visible (in every post where you attached it)​:

    Best regards,
    Antoine