Michael_Lowry

General purpose EXEC VARA for running arbitrary SQL statements

Discussion created by Michael_Lowry on Feb 14, 2018
Latest reply on Apr 19, 2018 by Michael_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.
e5i0kekqpzxg.png

 

The SQL query looks up the PWP.
2n5sc0h79vjb.png

 

The actual EXEC VARA does not have hard-coded values. Instead, it inherits parameter values from identically named object/script variables.
22p9f4j59sfq.png

 

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!

Outcomes