General purpose EXEC VARA for running arbitrary SQL statements

[Michael A. Lowry]

Based on an earlier idea, I developed an EXEC VARA and related objects that can run arbitrary SQL and return the results. Here is an example object I created for testing, with values filled-in.
https://us.v-cdn.net/5019921/uploads/editor/ix/e5i0kekqpzxg.png" width="645">

 

The SQL query looks up the PWP.

 

The actual EXEC VARA does not have hard-coded values. Instead, it inherits parameter values from identically named object/script variables.
https://us.v-cdn.net/5019921/uploads/editor/tf/22p9f4j59sfq.png" width="645">

 

So all you have to do is in the parent/calling objects, set these object/script variables, and then access the EXEC VARA using GET_VAR, PREP_PROCESS_VAR, or a curly-brace-style VARA object reference like {UC4.GP_SQL.VARA_EXEC,,1}.

 

The job looks like this:
Pre-process

:READ &AGENT_OR_AGENTGROUP#,,
:READ &Login#,,
:READ &Queue#,,
:READ &SQL_Connection#,,
:READ &SQL_Server#,,
:READ &SQL_Database#,,
:READ &SQL_Query#,,
:READ &PPR_ReportType#,,
:READ &PPR_Filter#,,
:READ &PPR_Def1#,,
:READ &PPR_Def2#,,
:READ &Output_Delimiter#,,
:INC UC4.RESOLVE_AGENT_GROUP.JOBI
:PUT_ATT HOST = &Agent#
:PUT_ATT QUEUE = &Queue#
:IF &SQL_Connection# <> " "
: PUT_ATT CONNECTION = &SQL_Connection#
:ELSE
: PUT_ATT LOGIN = &Login#
: PUT_ATT SERVER_NAME = &SQL_Server#
: PUT_ATT DATABASE_NAME = &SQL_Database#
:ENDIF
! Pass parameters for PREP_PROCESS_REPORT to post-process tab.
:RSET &PPR_ReportType# = &PPR_ReportType#
:RSET &PPR_Filter# = &PPR_Filter#
:RSET &PPR_Def1# = &PPR_Def1#
:RSET &PPR_Def2# = &PPR_Def2#
:RSET &Output_Delimiter# = &Output_Delimiter#

Process

:JCL_CONCAT_CHAR "§"
&SQL_Query#
:JCL_CONCAT_CHAR

Post-process

:IF &Create_Ouput_DS# = "YES"
! Check whether there was an error in the pre-process tab.
: IF &PreProc_Error# <> "No error"
: PRINT "Error in pre-process. Job did not run."
: SET &Status# = "PreProc_Error"
: SET &Error_Message# = &PreProc_Error#
: ELSE
! Verify that the job ran successfully.
: SET &RC# = GET_UC_OBJECT_STATUS(, , "RETCODE")
: IF &RC# <> 0
: SET &Status# = "Job_Error"
: SET &Error_Message# = "Job error. RC: &RC#"
: ELSE
: SET &Status# = "Job_OK"
: ENDIF
: ENDIF
: IF &Status# = "PreProc_Error" OR "Job_Error"
: PRINT "ERROR: &Error_Message#"
: INCLUDE UC4.CREATE_DATA_SEQUENCE_WITH_ERROR_MESSAGE.JOBI
: ELSE
: INCLUDE UC4.CREATE_DATA_SEQUENCE_FROM_JOB_OUTPUT.JOBI
: ENDIF
:ENDIF

The JOBI objects whose names begin with UC4.CREATE_DATA_SEQUENCE_ run the steps that actually create the output data sequence. See the attached XML.

 

Here’s a demonstration of how to use this VARA:

 

UC4.LOOK_UP_PWP.SCRI

:SET &AGENT_OR_AGENTGROUP# = "SQL.ORACLE2"
:SET &Queue#               = "UC0"
:SET &SQL_Connection#      = "UC4.ORACLE_EXP2.CONN_SQL"
:SET &SQL_Query#           = "SELECT substr(MQSRV_Name,instr(MQSRV_Name,'#') + 1) as Process_Name FROM MQSRV WHERE MQSRV_Name IN (SELECT OH_Name FROM OH WHERE OH_OType='SERV') AND MQSrv_Type = 4"
:SET &PPR_ReportType#      = "REP"
:SET &PPR_Filter#          = "*"
:SET &PPR_Def1#            = "COL=DELIMITER"
:SET &PPR_Def2#            = "DELIMITER=@;@"
:SET &Output_Delimiter#    = ";"
:SET &PWP_Name# = ""
:SET &VaraObj# = "UC4.GP_SQL.VARA_EXEC"
:PRINT "Looking up PWP using VARA &VaraObj#."
:SET &VarHnd# = PREP_PROCESS_VAR(&VaraObj#)
:PROCESS &VarHnd#
:  SET &PWP_Name# = GET_PROCESS_LINE(&VarHnd#,1)
:ENDPROCESS
:CLOSE_PROCESS &VarHnd#
:IF SUBSTR(&PWP_Name#,1,7) <> "<ERROR:"
:  IF &PWP_Name# <> ""
:    PRINT "Primary work process: &PWP_Name#"
:  ELSE
:    PRINT "No results returned by VARA &VaraObj#."
:  ENDIF
:ELSE
:  PRINT "Error returned by VARA &VaraObj#:"
:  PRINT "&PWP_Name#"
:ENDIF

Report:

U00007000 'UC4.GP_SQL.JOBS_SQL' activated with RunID '0002959011'.
U00020408 Primary work process: WP001

Enjoy!

Attachment(s)

General purpose EXEC VARAs uc4_export.xml   26 KB 1 version

[Michael A. Lowry]

These EXEC VARAs are essentially pseudo-functions:

• The input parameters are the parameters passed to the EXEC VARA via object/script variables.
• The output value(s) is/are the result(s) returned by the VARA.

They can be used in two kinds of places:

• In AE scripting, using GET_VAR or PREP_PROCESS_VAR, and
• Anywhere a curly-brace-style VARA object reference is permitted. E.g.,{UC4.GP_SQL.VARA_EXEC,,1}.

[Michael A. Lowry]

I noticed an odd quirk:

• An SQL job with just &SQL_Query# in the process tab runs happily if a multi-line SQL statement is defined in the object variable &SQL_Query# on that object. However, if the same multi-line SQL statement is passed to the job via a parameter of the calling EXEC VARA, the job will fail. This error appears in the Agent log:
  

  ORA-01756: quoted string not properly terminated

• Using a JCL concatenation character (specified with:JCL_CONCAT_CHAR) does not help.
• The only way I was able to get it to work was to pass the &SQL_Query# parameter from the calling EXEC VARA as a single line.

I found that the problem is not specific to EXEC VARAs. Rather, it appears to be related to how multi-line content is passed through a prompt/read buffer. I was also able to reproduce the problem using SCRI objects that fill the read buffer and then call the SQL job using ACTIVATE_UC_OBJECT. If the read buffer containing the SQL query contains multi-line content, the SQL job does not work.

I opened INC00220184 for this problem.

[Michael A. Lowry]

CA opened a KB article about this: KB000084565 – Multi-line SQL in variable does not work in SQL JOBS

 

The KB article is a bit lacking on clarity, so I will provide the following feedback on how it might be improved:

This text:

In SQL JOBS that contain multiple lines of SQL the carriage returns works.  However, when a multi-line query is provided to the SQL JOBS via a variable coming from a VARA EXEC or SCRI object the same query fails.

This is the situation:

• The process tab of the SQL JOBS simply contains a variable (&SQL_QUERY#)
• A VARA EXEC or SCRI defines the variable &SQL_QUERY# and calls the SQL JOBS (as an executable object or with ACTIVATE_UC_OBJECT respectively)
• When the defined variable does not contain carriage returns this mechanism works.
• When the variable contains multiple lines the SQL JOBS will fail

should be changed to to make it clearer what works and what does not work:

For an SQL JOBS object, the following works:

• Single- or multi-line SQL in the process tab
• Single- or multi-line SQL stored in an object variable defined in Variables & Prompts, and used in the process tab
• Single-line SQL stored in an object variable passed to the JOBS object via an EXEC VARA or ACTIVATE_UC_OBJECT, and used in the process tab

The following does not work:

• Multi-line SQL stored in an object variable passed to the JOBS object via an EXEC VARA or ACTIVATE_UC_OBJECT, and used in the process tab

Or you could present the data in tabular form to make it even easier to understand:

Location	Single-line SQL	Multi-line SQL
SQL JOBS process tab	✔	✔
Object variable defined in Variables & Prompts of SQL JOBS, and used in the process tab	✔	✔
Object or script variable passed to the JOBS object via an EXEC VARA or ACTIVATE_UC_OBJECT, and used in the process tab of SQL JOBS	✔	✘

 

CA Support promised to update the documentation to make this limitation more explicit.

[Michael A. Lowry]

CA Support promised to update the documentation to make this limitation more explicit.

CA added the following line to the documentation of the :READ script statement:

Important! Do not use text values with line breaks in the read buffer. :READ ignores the characters after the first line break.

[Michael A. Lowry]

It would be nice if :READ buffers were not limited in this way. I submitted a new idea for this:

Allow multi-line content in :READ buffers 

If you like the idea, please vote for it.

[Michael A. Lowry]

Broadcom deleted the idea.

[Michael A. Lowry]

Thanks to an improved version of the UC4.RESOLVE_AGENT_GROUP.JOBI JOBI and a neat trick proposed by Wolfgang Brückler, I have now improved these EXEC VARAs so that they can return an informative error message in the results, even in cases where the target executable object could not be be executed — e.g., cases that would normally cause the job to end in status FAULT_OTHER.
https://us.v-cdn.net/5019921/uploads/editor/ko/8shxdlsr9bsg.png" width="396">
I’ll post the updated objects soon.

[Michael A. Lowry]

I tried to further generalize this approach by putting a script variable called &EXEC_OBJ# in the Executable object field of the the EXEC VARA.
https://us.v-cdn.net/5019921/uploads/editor/xn/6411cxl39ow5.png" width="391">

Although the Variables button is active when this field has focus, and variable syntax highlighting is applied to text in the fields, variable resolution does not appear to work in this field. When I try to access this EXEC VARA from an SCRI (with the &EXEC_OBJ# variable set of course), the following error appears:

U00010000 '0001', Object '&EXEC_OBJ#' was not found. Please enter the correct name.

I'm not sure whether this is a bug or not.

[Michael A. Lowry]

Ok, here's the latest and greatest revision, including a few extra lines to prevent message U00020558.

Attachment(s)

GENERAL_PURPOSE_EXEC_VARAS 2 uc4_export.xml   39 KB 1 version

[Michael A. Lowry]

I have generalized the JOBIs so that they can be used in more places, not just the tasks started by the general-purpose EXEC VARAs.

• Factored out the steps related to creating the output data sequence to a new JOBI called UC4.PARSE_OUTPUT_AND_CREATE_DATA_SEQUENCE.JOBI.
• Added support for jobs run on agent groups of mode all. In such cases, the output of each child task is handled. (Note: it's still not possible to use an agent group of mode all in the general-purpose EXEC VARAs, because the AE does not allow using :PUT_ATT to set the agent of a job to an agent group.)
• Moved the job log parsing steps to a new child JOBI called UC4.READ_JOB_OUTPUT.JOBI.
• Added default values and made the JOBIs more fault-tolerant.
• Switched to updated version of JOBI to set agent group of a job programmatically.

 

The updated objects are attached.

Attachment(s)

General purpose EXEC VARAs 3 uc4_export.xml.zip   7 KB 1 version

[Michael A. Lowry]

The XML file attached to the previous comment contains an error. When imported, several of the objects will appear as links instead of objects. I would like to replace the attachment, but there appears to be no way to edit, remove, or replace file attachments on existing discussion threads or comments.

Here is the corrected XML file.

Ping @Jason McClellan
​

Attachment(s)

General purpose EXEC VARAs 3.zip   8 KB 1 version