Unable to spool information from a database job to file

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

Detailed Description and Symptoms

There is currently no way to use the "spool" command on a database agent job. However, there is a way to output the information to file through the job and use that file or transfer that file to another machine or share. Steps on how to do these are below.

Investigation
1) Put the regular commands that you would in the Form or Process tab.

<Please see attached file for image>

2) In the SQL tab, check "File" under "Job Report"

<Please see attached file for image>

This will write the information from the "report" of the report to file. The file will have a name like: O<ALPHA>.TXT.

3) <ALPHA> can be determined by getting the Run Number of the job and using the RUNNR2ALPHA Automic script function. To find the path and file name, the following can be put in the Post Process tab:

:SET &HOST# = GET_ATT(HOST)

:SET &FILE_PATH# = GET_VAR(UC_EX_PATH_JOBREPORT, &HOST#)

:P "UC_EX_PATH_JOBREPORT = &FILE_PATH#"

:SET &RUNNR# = SYS_ACT_ME_NR()

:SET &RUNNRALPHA# = RUNNR2ALPHA(&RUNNR#)

:P "ALPHA Value of Run Number &RUNNR# is &RUNNRALPHA#"

:P "Report can be found on &HOST# machine at:"

:P "&FILE_PATH#O&RUNNRALPHA#.TXT"

4) The Post Processing Report will show where the file is located:

<Please see attached file for image>

Environment

Release: AOATAM99000-8.0-Automic-One Automation Tools-Application Manager
Component:

Resolution

You can also use an OS job or filetransfer (JOBF) to copy or transfer this file to a different directory or machine. For an OS job (below shown in UNIX), you could use the below:
In the Post Process tab of the job from which the report is being written, you can put the following:

:SET &RET# = ACTIVATE_UC_OBJECT(JOBS.UNIX.MOVE.SQL.REPORT)

And the UNIX job from above (here called JOBS.UNIX.MOVE.SQL.REPORT) would run on the same machine as the SQL job and would have the following in the Process tab:

!Get Run Number for activating job

:SET &ACT_RUNNR # = SYS_ACT_PARENT_NR (ACT)

!Get Agent for activating job

: SET &ACT_HOST # = GET_STATISTIC_DETAIL (&ACT_RUNNR #, DST_HOST)

!Get Path where job reports are stored on Agent that activated job

:SET &FILE_PATH# = GET_VAR(UC_EX_PATH_JOBREPORT, &ACT_HOST#)

:P "UC_EX_PATH_JOBREPORT = &FILE_PATH#"

!Get Alpha value of run number for activating job

:SET &RUNNRALPHA# = RUNNR2ALPHA(&ACT_RUNNR#)

:P "ALPHA Value of Run Number &ACT_RUNNR# is &RUNNRALPHA#"

:P "Report can be found on &ACT_HOST# machine at:"

:P "&FILE_PATH#O&RUNNRALPHA#.TXT"

!Set NEW_PATH and NEW_FILE – path for new file, and name for New file

:SET &NEW_PATH # = "/u01/users/uc4/test"

:SET &NEW_FILE # = "&ACT_RUNNR#_SQL_REPORT.TXT"

!Copy file to new location

cp &FILE_PATH#O&RUNNRALPHA#.TXT &NEW_PATH#/&NEW_FILE#

For Filetransfers, the command below would be used to set the source file attribute from the Process tab:

:PUT_ATT FT_SRC_FILE = "&FILE_PATH#O&RUNNRALPHA#.TXT"