Automic Workload Automation

  • Private Community
Back to discussions
Expand all | Collapse all

GET_VAR with SQLI VARA that has object variables in its bind parameters

  • 1.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 10, 2014 04:17 PM

    Anyone tried to do this?

    I have a SEC_SQLI VARA with a couple of bind parameters, and I’m reading the data in a SCRI using GET_VAR. It works fine if the bind parameters in the SQLI are hard-coded to specific values It returns nothing if I use object variables in the bind parameters.



  • 2.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 11, 2014 10:47 AM

    Sounds like the bug I opened back in May and still waiting for a fix. The case is INC0301510



  • 3.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 12, 2014 04:57 AM

    The strange thing is that the SEC_SQLI object works fine with object variables in the bind parameters if the VARA is used as a data reference for a prompt set.

    I use another SEQ_SQLI VARA object as the data reference for a prompt set. When the prompt set is generated, the SEC_SQLI evaluates the object variable in the bind parameter (inheriting the value from the parent workflow). It then constructs the correct SQL query, and returns the expected results to the prompt set.

    I find it peculiar that it works in some situations and not in others.




  • 4.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 12, 2014 06:01 AM

    I tried putting the object variables directly in the query, like this:

    select count (distinct OH.OH_NAME) from OH
     where OH.OH_RefIdnr = '0'
     and OH.OH_OTYPE = '&OBJECT_TYPE#'
     and OH.OH_NAME like '&#PROJECT#%'

    This does not work with GET_VAR.

    For what it’s worth, I also tried using a dynamic SQLI VARA as the data reference for a prompt set, where the SQL query had object variables directly in the query instead of in bind parameters. This also did not work.




  • 5.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 12, 2014 06:30 AM

    I did a little more investigation and discovered something interesting. I was trying to work around this problem by storing the values for &OBJECT_TYPE# and &PROJECT# in a static VARA object. My thinking was that by referencing these using the {VARA,Key,Value} syntax in the bind parameters of the SQLI VARA, I could get it to work. It still did not work. Here is the SCRI I’m using:

    :SET &PROJECT# = "UC0"
    :SET &RET# = PUT_VAR UC0.MAL.TEMP.VARA,"PROJECT","&PROJECT#"
    :PRINT "Return code from PUT_VAR: &RET#"
    ! Process JOBP objects
    :SET &OBJECT_TYPE# = "JOBP"
    :SET &RET# = PUT_VAR UC0.MAL.TEMP.VARA,"OBJECT_TYPE","&OBJECT_TYPE#"
    :PRINT "Return code from PUT_VAR: &RET#"
    :SET &WORKFLOW_COUNT# = GET_VAR(&GET_OBJECT_COUNTS_SQLI_VREF_BP#,,"RESULT")
    ! Process JOBS objects
    :SET &OBJECT_TYPE# = "JOBS"
    :SET &RET# = PUT_VAR UC0.MAL.TEMP.VARA,"OBJECT_TYPE","&OBJECT_TYPE#"
    :PRINT "Return code from PUT_VAR: &RET#"
    :SET &JOB_COUNT# = GET_VAR(&GET_OBJECT_COUNTS_SQLI_VREF_BP#,,"RESULT")
    :PRINT "Project &PROJECT# has &WORKFLOW_COUNT# workflows and &JOB_COUNT# jobs."
    And here is the Activation report:
    2014-02-12 12:13:49 - U0020206 Variable '&GET_OBJECT_COUNTS_SQLI_VREF_BP#' was stored with value 'UC0.MAL.GET_OBJECT_COUNTS.SEC_SQLI_WITH_VARAREFS_IN_BIND_PARMS'.
    2014-02-12 12:13:49 - U0020206 Variable '&OBJECT_TYPE#' was stored with value ''.
    2014-02-12 12:13:49 - U0020206 Variable '&PROJECT#' was stored with value ''.
    2014-02-12 12:13:49 - U0020206 Variable '&PROJECT_LIST_VARA#' was stored with value 'UC0.MAL.PROJECT_LIST'.
    2014-02-12 12:13:49 - U0020408 Return code from PUT_VAR: PUT_VAR UC0.MAL.TEMP.VARA,"PROJECT","UC0"
    2014-02-12 12:13:49 - U0020408 Return code from PUT_VAR: PUT_VAR UC0.MAL.TEMP.VARA,"OBJECT_TYPE","JOBP"
    Note that the GET_VAR commands were run first, before the PUT_VARs. It seems that the Automation Engine is parsing the SCRI and looking for GET_VAR commands.  GET_VAR commands in the process tab are treated as equivalent to object variables in the Variables & Prompts tab. All GET_VAR commands are run during activation, before generation. The variables are resolved together with the variables in the Variables & Prompts tab, in alphabetical order. Turning on extended reports revealed this behavior:
    /a
      **** UC0.MAL.GET_OBJECT_COUNTS.SCRI|: 0005 lines suppressed ***
     :RESOLVE &GET_OBJECT_COUNTS_SQLI# = 'UC0.MAL.GET_OBJECT_COUNTS.SEC_SQLI'
    :RESOLVE &GET_OBJECT_COUNTS_SQLI_VARS_BP# = 'UC0.MAL.GET_OBJECT_COUNTS.SEC_SQLI_WITH_VARIABLES_IN_BIND_PARMS'
    :RESOLVE &GET_OBJECT_COUNTS_SQLI_VREF_BP# = 'UC0.MAL.GET_OBJECT_COUNTS.SEC_SQLI_WITH_VARAREFS_IN_BIND_PARMS'
    :RESOLVE &OBJECT_TYPE# = ''
    :RESOLVE &PROJECT# = ''
     :RESOLVE &PROJECT_LIST_VARA# = 'UC0.MAL.PROJECT_LIST'
     :DISABLE_SET_PVAL 'Y'
    ...




  • 6.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 12, 2014 10:50 AM

    Here is a simple example using &$ARCHIVE_KEY1# as the bind variable. This is a work around until the bug for local script variables is fixed.


    cd35e55252aa4183842950ada385e4.pnghttps://us.v-cdn.net/5019921/uploads/FileUpload/3e/cd35e55252aa4183842950ada385e4.png" width="990">


  • 7.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 12, 2014 11:31 AM

    Great! That works like a charm. Thanks a bunch, Dwight.



  • 8.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 14, 2014 12:05 PM

    Oops. I spoke too soon. If the value you want to insert with PUT_ATT comes from an object variable, it doesn’t appear to work. Here is my updated SEC_SQLI.

    1761274944c48707bfebdef2531091.pnghttps://us.v-cdn.net/5019921/uploads/FileUpload/27/1761274944c48707bfebdef2531091.png" width="751">

    In an earlier job in the workflow, I fill a VARA will the three-character project prefixes in the keys. These project prefixes are the unique identifiers of the individual projects that use the Automation Engine. All jobs and workflows owned by a project must begin with the project’s own three-character prefix. This is why bind parameter two has the % sign at the end. The query searches for objects of a certain type (JOBP or JOBS) whose names begin with the project prefix.

    Here is the SCRI that is supposed to populate value 1 and 2 in the VARA with the data retrieved from the query (the number of workflows and jobs in each project, respectively).

    :PRINT "Fetching number of workflows and jobs for each project."
    !If project list VARA does not exist, exit
    :IF GET_OH_IDNR(&PROJECT_LIST_VARA#) = 0
    :  PRINT "Project list VARA &PROJECT_LIST_VARA# does not exist."
    :  MODIFY_STATE RETCODE=50
    :ELSE
    !  Open VARA and fill in stats
    :  SET &VaraHnd# = PREP_PROCESS_VAR(&PROJECT_LIST_VARA#,,,)
    :  PROCESS &VaraHnd#
    :    PSET &PROJECT# = GET_PROCESS_LINE(&VaraHnd#, 1)

    :    PUT_ATT ARCHIVE_KEY1 = "&PROJECT#"
    !:    PUT_ATT ARCHIVE_KEY1 = "UC0"

    !     Process JOBP objects
    :    PSET &OBJECT_TYPE# = "JOBP"
    :    PUT_ATT ARCHIVE_KEY2 = "&OBJECT_TYPE#"
    !:    PUT_ATT ARCHIVE_KEY2 = "JOBP"
    :    SET &WORKFLOW_COUNT# = GET_VAR(UC0.MAL.GET_OBJECT_COUNTS.SEC_SQLI2)

    !     Process JOBS objects
    :    PSET &OBJECT_TYPE# = "JOBS"
    :    PUT_ATT ARCHIVE_KEY2 = "&OBJECT_TYPE#"
    !:    PUT_ATT ARCHIVE_KEY2 = "JOBS"
    :    SET &JOB_COUNT# = GET_VAR(UC0.MAL.GET_OBJECT_COUNTS.SEC_SQLI2)

    :    PRINT "Project &PROJECT# has &WORKFLOW_COUNT# workflows and &JOB_COUNT# jobs."
    :    PUT_VAR &PROJECT_LIST_VARA#,&PROJECT#,&WORKFLOW_COUNT#,&JOB_COUNT#
    :  ENDPROCESS
    :  CLOSE_PROCESS &VaraHnd#
    :ENDIF
    The GET_VAR commands work if the preceding :PUT_ATT command has a hard-coded value. It always returns a value of 0 if the preceding :PUT_ATT contains an object variable like &PROJECT#.

    Here’s an excerpt from the activation report:
    2014-02-14 17:36:15 - U0020408 Fetching number of workflows and jobs for each project.
    2014-02-14 17:36:15 - U0020230 Value '&PROJECT#' in object: 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK', line: '00016' (RunID: '0005307954') was changed from '' to ' ABC' using the command :PSET.
    2014-02-14 17:36:15 - U0020206 Variable 'PROJECT#' was stored with value ' ABC'.
    2014-02-14 17:36:15 - U0020230 Value '&OBJECT_TYPE#' in object: 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK', line: '00022' (RunID: '0005307954') was changed from '' to 'JOBP' using the command :PSET.
    2014-02-14 17:36:15 - U0020206 Variable 'OBJECT_TYPE#' was stored with value 'JOBP'.
    2014-02-14 17:36:15 - U0020230 Value '&OBJECT_TYPE#' in object: 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK', line: '00028' (RunID: '0005307954') was changed from 'JOBP' to 'JOBS' using the command :PSET.
    2014-02-14 17:36:15 - U0020206 Variable 'OBJECT_TYPE#' was stored with value 'JOBS'.
    2014-02-14 17:36:15 - U0020408 Project  ABC has 0 workflows and 0 jobs.
    Every line from the Object access report looks the same:
    GET_VAR    UC0.MAL.GET_OBJECT_COUNTS.SEC_SQLI2         0    (default)   
    If I uncomment the lines that run :PUT_ATT with hard-coded values, the GET_VAR works.

    I see three possible explanations:
    1. :PUT_ATT is simply not working when an object variable is provided in the value.
      This doesn’t seem likely, because I know I have provided values to :PUT_ATT before using object variables or script variables.

    2. :PUT_ATT is somehow being executedbeforethe object variable has been set.
      This also doesn’t seem likely. I have done some simple tests, and :PUT_ATT does not appear to work this way.

    3. GET_VAR is being executed before either the object variable has been set or before the :PUT_ATT has been run, or both.
      This seems the most likely explanation, but I don’t see any reason why it would work this way.*

    * My previousobservationabout the :RESOLVE statements in the workflow extended Script report appearinginterspersed with resolution of variables from the Variables & Prompts was probably a red herring — the :RESOLVE statements for the task GET_VARs disappeared from the workflow Script report as soon as I switched onGenerate at runtime. That was obviously an oversight on my part. Checking that check-box eliminated the :RESOLVE messages, but the odd behavior with GET_VAR remains.



  • 9.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 14, 2014 12:50 PM

    Have you executed this with an Activation protocol trace and see what shows for the modification of attributes for the Archive key?



  • 10.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 14, 2014 01:11 PM

     

    Mark Hadler said:

    Have you executed this with an Activation protocol trace and see what shows for the modification of attributes for the Archive key?

    Mark, I have enabled extended reports for the workflow and all constituent tasks. Is is one of those? If not, would you please tell me how to enable it?



  • 11.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 14, 2014 01:21 PM

    Select Execute, from either the Explorer or Edit, and select the Once option; then choose the Test options tab and check the required boxes and press OK.

    It's documented in the User Guide, search for "execution with options".



  • 12.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 14, 2014 03:17 PM
      |   view attached

    Ok, I have collected the trace. Please let me know if you learn anything from it.  :)

    Attachment(s)

    txt
    job trace.txt   6 KB 1 version


  • 13.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 14, 2014 04:24 PM

    Please excuse but I guess I’m not understanding what you are trying to accomplish with the traced code.  Hopefully it’s me and I’m just being obtuse, perhaps it's due to the impending long weekend.

    It appears that there are three Validity Keywords in the &PROJECT_LIST_VARA# Variable object:  UC0, UC4 and XMM.  As you go through the PROCESS loop you perform a PUT_ATT ARCHIVE_KEY2 to a value of &OBJECT_TYPE# that was PSET to “JOBP”.  I see the following message from the trace that states that it has placed that JOBP value into the Archive key.

              U0020407 Attribute modification -> 'ARCHIVE_KEY2'='JOBP'

    Then you have another PUT_ATT ARCHIVE_KEY2 to a value of &OBJECT_TYPE# that was PSET to “JOBS” which is followed by message -

              U0020407 Attribute modification -> 'ARCHIVE_KEY2'='JOBS'

    The means that the object that is executing this script will have its Archive key 2 set to JOBS when it completes.  Is that what is desired?

    How about adding a GET_ATT(ARCHIVE_KEY2) following the PUT_ATT to see what UC4 thinks is there.  I assume that when you look at the Statistics or Detail or an Activities Monitor for this object you are not seeing the JOBS (or is it JOBP that you desire) value in the Archive key 2.




  • 14.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 15, 2014 02:45 AM

    There are three :PUT_ATTs and two GET_VARs. All are inside a PREP_PROCESS_VARA loop. The value put into ARCHIVE_KEY1 comes from the VARA keys, so that is set once per key, or once per iteration of the loop. The values put into ARCHIVE_KEY2 are always "JOBP" and "JOBS", but there is a GET_VAR after each of these two :PUT_ATTs. So the attributes ARCHIVE_KEY1 and ARCHIVE_KEY2 will have a unique combination of values each time GET_VAR is called.

    I did as you advised, and inserted GET_ATT commands to confirm that the attributes are being set. It appears that they are. Here is the script as it looks now:

    :PRINT "Fetching number of workflows and jobs for each project."
    !If project list VARA does not exist, exit
    :IF GET_OH_IDNR(&PROJECT_LIST_VARA#) = 0
    :  PRINT "Project list VARA &PROJECT_LIST_VARA# does not exist."
    :  MODIFY_STATE RETCODE=50
    :ELSE
    !  Open VARA and fill in stats
    :  SET &VaraHnd# = PREP_PROCESS_VAR(&PROJECT_LIST_VARA#,,,)
    :  PROCESS &VaraHnd#
    :    PRINT Reading project name from &PROJECT_LIST_VARA#
    :    PSET &PROJECT# = GET_PROCESS_LINE(&VaraHnd#, 1)
    :    PRINT Setting ARCHIVE_KEY1 to &PROJECT#
    :    PUT_ATT ARCHIVE_KEY1 = &PROJECT#
    ! :    PUT_ATT ARCHIVE_KEY1 = "UC0"
    :    SET &AK1# = GET_ATT(ARCHIVE_KEY1)
    :    PRINT New value of ARCHIVE_KEY1: &AK1#
    :PRINT
    !     Process JOBP objects
    :    PSET &OBJECT_TYPE# = "JOBP"
    :    PRINT Setting ARCHIVE_KEY2 to &OBJECT_TYPE#
    :    PUT_ATT ARCHIVE_KEY2 = &OBJECT_TYPE#
    ! :    PUT_ATT ARCHIVE_KEY2 = "JOBP"
    :    SET &AK2# = GET_ATT(ARCHIVE_KEY2)
    :    PRINT New value of ARCHIVE_KEY2: &AK2#
    :    PRINT Running GET_VAR to fetch number of &OBJECT_TYPE# objects
    :    SET &WORKFLOW_COUNT# = GET_VAR(UC0.MAL.GET_OBJECT_COUNTS.SEC_SQLI2)
    :PRINT
    !     Process JOBS objects
    :    PSET &OBJECT_TYPE# = "JOBS"
    :    PRINT Setting ARCHIVE_KEY2 to &OBJECT_TYPE#
    :    PUT_ATT ARCHIVE_KEY2 = &OBJECT_TYPE#
    ! :    PUT_ATT ARCHIVE_KEY2 = "JOBS"
    :    SET &AK2# = GET_ATT(ARCHIVE_KEY2)
    :    PRINT New value of ARCHIVE_KEY2: &AK2#
    :    PRINT Running GET_VAR to fetch number of &OBJECT_TYPE# objects
    :    SET &JOB_COUNT# = GET_VAR(UC0.MAL.GET_OBJECT_COUNTS.SEC_SQLI2)
    :PRINT
    :    PRINT "Project &PROJECT# has &WORKFLOW_COUNT# workflows and &JOB_COUNT# jobs."
    ! :    PUT_VAR &PROJECT_LIST_VARA#,&PROJECT#,&WORKFLOW_COUNT#,&JOB_COUNT#
    : PRINT
    :  ENDPROCESS
    :  CLOSE_PROCESS &VaraHnd#
    :ENDIF
    And here is the Activation report from a run from the SCRI:

    2014-02-15 08:31:43 - U0020206 Variable '&GET_OBJECT_COUNTS_SQLI#' was stored with value 'UC0.MAL.GET_OBJECT_COUNTS.SEC_SQLI_WITH_VARAREFS_IN_BIND_PARMS'. 2014-02-15 08:31:43 - U0020206 Variable '&OBJECT_TYPE#' was stored with value ''. 2014-02-15 08:31:43 - U0020206 Variable '&PROJECT#' was stored with value ''. 2014-02-15 08:31:43 - U0020206 Variable '&PROJECT_LIST_VARA#' was stored with value 'UC0.MAL.PROJECT_LIST2.TEST'. 2014-02-15 08:31:43 - U0020408 Fetching number of workflows and jobs for each project. 2014-02-15 08:31:43 - U0020408 Reading project name from UC0.MAL.PROJECT_LIST2.TEST 2014-02-15 08:31:43 - U0020230 Value '&PROJECT#' in object: 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line: '00017' (RunID: '0005323837') was changed from '' to ' UC0' using the command :PSET. 2014-02-15 08:31:43 - U0020239 Runtime warning in object 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line '00017': Could not find parent task of type JOBP or JSCH. 2014-02-15 08:31:43 - U0020206 Variable 'PROJECT#' was stored with value ' UC0'. 2014-02-15 08:31:43 - U0020408 Setting ARCHIVE_KEY1 to  UC0 2014-02-15 08:31:43 - U0020408 New value of ARCHIVE_KEY1:  UC0 2014-02-15 08:31:43 - U0020408 2014-02-15 08:31:43 - U0020230 Value '&OBJECT_TYPE#' in object: 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line: '00025' (RunID: '0005323837') was changed from '' to 'JOBP' using the command :PSET. 2014-02-15 08:31:43 - U0020239 Runtime warning in object 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line '00025': Could not find parent task of type JOBP or JSCH. 2014-02-15 08:31:43 - U0020206 Variable 'OBJECT_TYPE#' was stored with value 'JOBP'. 2014-02-15 08:31:43 - U0020408 Setting ARCHIVE_KEY2 to JOBP 2014-02-15 08:31:43 - U0020408 New value of ARCHIVE_KEY2: JOBP 2014-02-15 08:31:43 - U0020408 Running GET_VAR to fetch number of JOBP objects 2014-02-15 08:31:43 - U0020408 2014-02-15 08:31:43 - U0020230 Value '&OBJECT_TYPE#' in object: 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line: '00035' (RunID: '0005323837') was changed from 'JOBP' to 'JOBS' using the command :PSET. 2014-02-15 08:31:43 - U0020239 Runtime warning in object 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line '00035': Could not find parent task of type JOBP or JSCH. 2014-02-15 08:31:43 - U0020206 Variable 'OBJECT_TYPE#' was stored with value 'JOBS'. 2014-02-15 08:31:43 - U0020408 Setting ARCHIVE_KEY2 to JOBS 2014-02-15 08:31:43 - U0020408 New value of ARCHIVE_KEY2: JOBS 2014-02-15 08:31:43 - U0020408 Running GET_VAR to fetch number of JOBS objects 2014-02-15 08:31:43 - U0020408 2014-02-15 08:31:43 - U0020408 Project  UC0 has 0 workflows and 0 jobs. 2014-02-15 08:31:43 - U0020408 2014-02-15 08:31:43 - U0020408 Reading project name from UC0.MAL.PROJECT_LIST2.TEST 2014-02-15 08:31:43 - U0020230 Value '&PROJECT#' in object: 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line: '00017' (RunID: '0005323837') was changed from ' UC0' to ' UC4' using the command :PSET. 2014-02-15 08:31:43 - U0020239 Runtime warning in object 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line '00017': Could not find parent task of type JOBP or JSCH. 2014-02-15 08:31:43 - U0020206 Variable 'PROJECT#' was stored with value ' UC4'. 2014-02-15 08:31:43 - U0020408 Setting ARCHIVE_KEY1 to  UC4 2014-02-15 08:31:43 - U0020408 New value of ARCHIVE_KEY1:  UC4 2014-02-15 08:31:43 - U0020408 2014-02-15 08:31:43 - U0020230 Value '&OBJECT_TYPE#' in object: 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line: '00025' (RunID: '0005323837') was changed from 'JOBS' to 'JOBP' using the command :PSET. 2014-02-15 08:31:43 - U0020239 Runtime warning in object 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line '00025': Could not find parent task of type JOBP or JSCH. 2014-02-15 08:31:43 - U0020206 Variable 'OBJECT_TYPE#' was stored with value 'JOBP'. 2014-02-15 08:31:43 - U0020408 Setting ARCHIVE_KEY2 to JOBP 2014-02-15 08:31:43 - U0020408 New value of ARCHIVE_KEY2: JOBP 2014-02-15 08:31:43 - U0020408 Running GET_VAR to fetch number of JOBP objects 2014-02-15 08:31:43 - U0020408 2014-02-15 08:31:43 - U0020230 Value '&OBJECT_TYPE#' in object: 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line: '00035' (RunID: '0005323837') was changed from 'JOBP' to 'JOBS' using the command :PSET. 2014-02-15 08:31:43 - U0020239 Runtime warning in object 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line '00035': Could not find parent task of type JOBP or JSCH. 2014-02-15 08:31:43 - U0020206 Variable 'OBJECT_TYPE#' was stored with value 'JOBS'. 2014-02-15 08:31:43 - U0020408 Setting ARCHIVE_KEY2 to JOBS 2014-02-15 08:31:43 - U0020408 New value of ARCHIVE_KEY2: JOBS 2014-02-15 08:31:43 - U0020408 Running GET_VAR to fetch number of JOBS objects 2014-02-15 08:31:43 - U0020408 2014-02-15 08:31:43 - U0020408 Project  UC4 has 0 workflows and 0 jobs. 2014-02-15 08:31:43 - U0020408 2014-02-15 08:31:43 - U0020408 Reading project name from UC0.MAL.PROJECT_LIST2.TEST 2014-02-15 08:31:43 - U0020230 Value '&PROJECT#' in object: 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line: '00017' (RunID: '0005323837') was changed from ' UC4' to ' XMM' using the command :PSET. 2014-02-15 08:31:43 - U0020239 Runtime warning in object 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line '00017': Could not find parent task of type JOBP or JSCH. 2014-02-15 08:31:43 - U0020206 Variable 'PROJECT#' was stored with value ' XMM'. 2014-02-15 08:31:43 - U0020408 Setting ARCHIVE_KEY1 to  XMM 2014-02-15 08:31:43 - U0020408 New value of ARCHIVE_KEY1:  XMM 2014-02-15 08:31:43 - U0020408 2014-02-15 08:31:43 - U0020230 Value '&OBJECT_TYPE#' in object: 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line: '00025' (RunID: '0005323837') was changed from 'JOBS' to 'JOBP' using the command :PSET. 2014-02-15 08:31:43 - U0020239 Runtime warning in object 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line '00025': Could not find parent task of type JOBP or JSCH. 2014-02-15 08:31:43 - U0020206 Variable 'OBJECT_TYPE#' was stored with value 'JOBP'. 2014-02-15 08:31:43 - U0020408 Setting ARCHIVE_KEY2 to JOBP 2014-02-15 08:31:43 - U0020408 New value of ARCHIVE_KEY2: JOBP 2014-02-15 08:31:43 - U0020408 Running GET_VAR to fetch number of JOBP objects 2014-02-15 08:31:43 - U0020408 2014-02-15 08:31:43 - U0020230 Value '&OBJECT_TYPE#' in object: 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line: '00035' (RunID: '0005323837') was changed from 'JOBP' to 'JOBS' using the command :PSET. 2014-02-15 08:31:43 - U0020239 Runtime warning in object 'UC0.MAL.GET_OBJECT_COUNTS.SCRI.AK.TEST', line '00035': Could not find parent task of type JOBP or JSCH. 2014-02-15 08:31:43 - U0020206 Variable 'OBJECT_TYPE#' was stored with value 'JOBS'. 2014-02-15 08:31:43 - U0020408 Setting ARCHIVE_KEY2 to JOBS 2014-02-15 08:31:43 - U0020408 New value of ARCHIVE_KEY2: JOBS 2014-02-15 08:31:43 - U0020408 Running GET_VAR to fetch number of JOBS objects 2014-02-15 08:31:43 - U0020408 2014-02-15 08:31:43 - U0020408 Project  XMM has 0 workflows and 0 jobs. 2014-02-15 08:31:43 - U0020408
    The runtime warnings can be ignored. They appear only because of the :PSETs. For these tests, I’m running the job outside of the workflow in which it normally runs.


  • 15.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Feb 18, 2014 07:04 AM

    This one turned out to have a very simple — but elusive — explanation. The VARA from which the script reads keys for the &PROJECT# script variable hadspaces at the beginnings of each key. So above, you’ll see that there is an extra space before each project name. E.g.,

    Project  XMM has 0 workflows and 0 jobs.
    As soon as I added an STR_TRIM to remove the extra spaces to the job that builds the VARA object, the problem went away. Sorry for the wild goose chase.


  • 16.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Oct 19, 2015 11:16 AM
    Our testing suggests that in v11.1.1, neither script/object variables nor predefined variables such as &$ARCHIVE_KEY1# works in SQLI bind parameters. Can anyone else confirm this?


  • 17.  GET_VAR with SQLI VARA that has object variables in its bind parameters

    Posted Dec 09, 2015 01:40 PM
    Our testing suggests that in v11.1.1, neither script/object variables nor predefined variables such as &$ARCHIVE_KEY1# works in SQLI bind parameters. Can anyone else confirm this?
    I have performed some additional testing in v11.2. It seems that both predefined variables and object/script variables work in the bind parameters of SQLI VARA objects, but only in some situations. GET_VAR and PREP_PROCESS_VAR seem to work alright, but if the SQLI VARA is used as a data reference for a prompt set element, then the prompt set won’t work correctly if the SQLI VARA has object/script variables in the bind parameters. I have reported this to Automic, and I will also do some additional testing soon.

    By the way, 11.2 appears to have a new bug that prevents saving a workflow/job that uses a prompt set with an SQLI VARA data reference that has variables in the bind parameters. Whether the problem manifests itself appears to depend on the type of variable used in the bind parameters of the SQLI VARA:
    • Object or script variables such as &OBJ_NAME# or &PROJECT# cause the error.
    • Task-level predefined variables such as &$ARCHIVE_KEY1# or &$OBJECT_TITLE# also cause the error.
    • System-level predefined variables such as &$CLIENT_DESC# or &$PHYS_DATE_DAY_OF_YEAR# do not cause the error.
    • Hard-coded values do not cause the error.
    • I have not tested VARA object references like {MYVARA,key,1} yet but I would guess that they would work OK.
    It seems that there is some sort of overly strict validation of the PRPT and associated SQL statements.

    A work-around is to hard-code the bind parameters, attach the prompt set to the workflow/job, and then go back and change the bind parameters back to variables.