Advanced use of the TWS 8.6 command line reporting

by Pete Meechan

Following up on the earlier article An introduction to TWS 8.6 command line reporting, this tip provides some more advanced uses of the TWS 8.6 command line reporting functionality. The tip only discusses the options that can be used when creating a customised report and suggests some options for limiting the number of customised properties files that would need to be used.

The sample sql.properties file

The report options supplied with the sample sql.properties file are shown below (without the supporting comments included in the actual file)

PARAM_ReportTitle=Custom SQL
PARAM_ReportDescription=This is the report description
PARAM_IncludeSqlStatement=false
REPORT_TYPE=CUSTOM_SQL
QuerySQL=SELECT * from <DatabaseSchema>.<TableName>
REPORT_OUTPUT_FORMAT=html
OutputView=table
CSVSeparator=,
PARAM_ReportLayout=a4
PARAM_EnableCheckLimit=true
PARAM_UserLimit=10000

Specifying the custom SQL report options

The options supplied for the custom SQL report options can be edited in place to change the options as necessary. The table below provides a brief description of each option. 

        

   Option    Default    Description
PARAM_ReportTitle Custom   SQL Title of report
PARAM_ReportDescription This   is the report description Brief description of the report  contents
PARAM_IncludeSqlStatement false Include (true) the SQL statement used   to generate the report in the report output
REPORT_TYPE CUSTOM_SQL This indicates it is a custom SQL   report and cannot be changed
QuerySQL   The SQL query statement to be executed   for this report
REPORT_OUTPUT_FORMAT html The format of the report output –   choose from HTML (html), CSV (csv) or PDF (pdf)
OutputView table Custom SQL reports can only be   specified as a table view – charts are not supported
CSVSeparator , Default CSV delimiter is a comma. If   JSDL job definitions are included, specify a delimiter such as a pipe (|) to   avoid errors when used in Excel
PARAM_ReportLayout a4 Only used for PDF report formats;   other options include us-letter, us-legal or custom
PARAM_ReportHeight 297mm If custom specified for report layout,   page height
PARAM_ReportWidth 210mm If custom specified for report layout,   page width
PARAM_EnableCheckLimit true Limit the report out to number of rows   specified in the UserLimit option
PARAM_UserLimit 10000 Maximum number of rows to include in   the report

Generating a customised report

 

Assuming that reportCLI has been installed in the path /opt/ibm/TWA/ReportCLI , use the commend below to execute reportCLI with the default sql.properties file contents 

/opt/ibm/TWA/ReportCLI/reportcli.sh
-p /opt/ibm/TWA/ReportCLI/reports/templates/sql.properties
-r custom
-o /var/www/html/TWSreports/

 

Modifying the report options

 

To produce reports using options other than the default values specified in the sql.properties file, the required changes can be made to sql.properties and the command rerun. Obviously this could cause problems if other users were using the sql.properties options in their reports so it would be safer to copy sql.properties to another file and use that. However, this could very quickly lead to having to manage lots of different properties files to support the various reporting requirements.

An alternative and better solution is to specify the report options at run time, by providing overriding values to those specified in the properties file via the command line options. For example, the report format specified within the properties file (e.g. html) can be overridden on the command line by appending the option “– k REPORT_OUTPUT_FORMAT” as shown below 

/opt/ibm/TWA/ReportCLI/reportcli.sh
-p /opt/ibm/TWA/ReportCLI/reports/templates/sql.properties
-r custom
-o /var/www/html/TWSreports/
-k REPORT_OUTPUT_FORMAT=pdf

 

In fact most of the option values specified within the properties file can be overridden using the –k option of the reportcli command. The –k option can be specified multiple times to override multiple options for the specific execution of the report. For example, to specify a CSV format report with a delimiter of “|” and a limit of 500 rows, specify the following

/opt/ibm/TWA/ReportCLI/reportcli.sh
-p /opt/ibm/TWA/ReportCLI/reports/templates/sql.properties
-r custom
-o /var/www/html/TWSreports/
-k REPORT_OUTPUT_FORMAT=csv –k CSVSeparator=”|” –k PARAM_UserLimit=500

The flexibility provided by overriding different options at execution time helps to reduce the number of unique properties files required. Particularly with the choice of report format allows a PDF version of the same HTML based report to be produced so that it could for example be e-mailed to an external customer.

Overriding report options


Here are some suggestions for benefitting from the ability to override report options at run time.

Variable Table and Template Report Job Definition

Using the variable table functionality of TWS 8.5 and above, a simple report template job could be defined using values within a variable table to supply report options at runtime. As the maximum length of a TWS variable is 72 characters and SQL statements can be quite long, a number of individual variables can be used to define the complete SQL statement, such as ORB_REPCLI_SQL01 through ORB_REPCLI_SQL20. The number of variables can be increased if required for particularly long SQL statements (to a maximum of 16K in total).

To do so, add the following variables to the MAIN_TABLE (default) variable table. If a variable is not specified elsewhere, TWS will use these values to resolve the variable values and by default, produce a list of the calendars defined to TWS.

ORB_REPCLI_DESC “Custom report generated by TWS reportCLI”
ORB_REPCLI_FMT “html”
ORB_REPCLI_NAME “custom”
ORB_REPCLI_OUT “/var/www/html/TWSreports/”
ORB_REPCLI_PATH “/opt/ibm/TWA/ReportCLI/”
ORB_REPCLI_PROP “sql.properties”
ORB_REPCLI_SQL “false”
ORB_REPCLI_SQL01 “select * from MDL.CALENDARS_V”
ORB_REPCLI_SQL02 “”
ORB_REPCLI_SQL03 “”
ORB_REPCLI_SQL04 “”
ORB_REPCLI_SQL05 “”
ORB_REPCLI_SQL06 “”
ORB_REPCLI_SQL07 “”
ORB_REPCLI_SQL08 “”
ORB_REPCLI_SQL09 “”
ORB_REPCLI_SQL10 “”
ORB_REPCLI_SQL11 “”
ORB_REPCLI_SQL12 “”
ORB_REPCLI_SQL13 “”
ORB_REPCLI_SQL14 “”
ORB_REPCLI_SQL15 “”
ORB_REPCLI_SQL16 “”
ORB_REPCLI_SQL17 “”
ORB_REPCLI_SQL18 “”
ORB_REPCLI_SQL19 “”
ORB_REPCLI_SQL20 “”
ORB_REPCLI_TEMPL “/opt/ibm/TWA/ReportCLI/reports/templates/”
ORB_REPCLI_TITLE “Custom SQL report”
ORB_REPCLI_TOC “false”

 

The variables listed above can then be overridden within individual job stream definitions by specifying a job stream level variable table. The job stream variable table need only specify values to be overridden from the default MAIN_TABLE. Typically this would be the report title (ORB_REPCLI_TITLE), report description (ORB_REPCLI_DESC), report format (ORB_REPCLI_FMT), report output file name (ORB_REPCLI_NAME) and the SQL statement to be executed (ORB_REPCLI_SQLnn).

Based upon the above variable definitions, a sample TWS report template job is shown below

CLYNELISH_AGT#ORB_TWS_CUSTOM_SQL_REPORT
SCRIPTNAME“^ORB_REPCLI_PATH^reportcli.sh -p ^ORB_REPCLI_TEMPL^^ORB_REPCLI_PROP^
-r ^ORB_REPCLI_NAME^ -o ^ORB_REPCLI_OUT^ -k PARAM_Toc=^ORB_REPCLI_TOC^
-k PARAM_IncludeSqlStatement=^ORB_REPCLI_SQL^
-k PARAM_ReportDescription=’^ORB_REPCLI_DESC^’
-k PARAM_ReportTitle=’^ORB_REPCLI_TITLE^’ -k REPORT_OUTPUT_FORMAT=^ORB_REPCLI_FMT^
-k QuerySQL=”^ORB_REPCLI_SQL01^ ^ORB_REPCLI_SQL02^ ^ORB_REPCLI_SQL03^ ^ORB_REPCLI_SQL04^ ^ORB_REPCLI_SQL05^ ^ORB_REPCLI_SQL06^ ^ORB_REPCLI_SQL07^ ^ORB_REPCLI_SQL08^ ^ORB_REPCLI_SQL09^ ^ORB_REPCLI_SQL10^ ^ORB_REPCLI_SQL11^ ^ORB_REPCLI_SQL12^ ^ORB_REPCLI_SQL13^ ^ORB_REPCLI_SQL14^ ^ORB_REPCLI_SQL15^ ^ORB_REPCLI_SQL16^ ^ORB_REPCLI_SQL17^ ^ORB_REPCLI_SQL18^ ^ORB_REPCLI_SQL19^ ^ORB_REPCLI_SQL20^

STREAMLOGON twsuser
DESCRIPTION “Orb custom SQL report using TWS command line reporting”
TASKTYPE UNIX
RECOVERY STOP


Report Job Stream Definition
 

To produce a report using the definitions listed above, all that needs to be done is create a job stream and an associated variable table definition. A sample job stream is shown below

SCHEDULE CLYNELISH_AGT#ORB_TWS_REPORT1
DESCRIPTION “TWS custom TWS reporting – uses settings in variable table below”
VARTABLE ORB_REPCLI_REPORT1
ON RUNCYCLE WORKDAYS “FREQ=DAILY;INTERVAL=1;BYWORKDAY” FDIGNORE
FOLLOWS CLYNELISH_AGT#ORB_TWS_REPORT1.@ PREVIOUS
:
CLYNELISH_AGT#ORB_TWS_CUSTOM_SQL_REPORT AS ORB_TWS_CUSTOM_SQL_REPORT_RUNCYCLES
END

 

A sample job stream level variable table is shown below including the SQL statement split over two variables.

VARTABLE ORB_REPCLI_REPORT1
DESCRIPTION “Report specific options for REPORT1 job”
MEMBERS
ORB_REPCLI_DESC “Run cycle definitions for Orb jobs on the CLYNELISH workstations”
ORB_REPCLI_FMT “html”
ORB_REPCLI_NAME “runcycles”
ORB_REPCLI_SQL01 “select * from mdl.run_cycle_refs_v where job_stream_wks_name like”
ORB_REPCLI_SQL02 “‘CLYNELISH%’ order by job_stream_wks_name, job_stream_name”
ORB_REPCLI_TITLE “Orb Data Run Cycles for CLYNELISH workstations”
END

 

After submitting the job stream for execution a typical job log output is shown below. The error message is due to US regional settings and can be ignored when using non-US settings.

===============================================================
= JOB       : CLYNELISH_AGT#ORB_TWS_REPORT1[(1138 05/02/12),(0AAAAAAAAAAAADCZ)].ORB_TWS_CUSTOM_SQL_REPORT_RUNCYCLES
= USER     : twsuser
= JCLFILE   : /opt/ibm/TWA/ReportCLI/reportcli.sh -p /opt/ibm/TWA/ReportCLI/reports/templates/sql.properties -r runcycles -o /var/www/html/TWSreports/ -k PARAM_Toc=false -k PARAM_IncludeSqlStatement=false -k PARAM_ReportDescription=’Run cycle definitions for Orb jobs on the CLYNELISH workstations’ -k PARAM_ReportTitle=’Orb Data Run Cycles for CLYNELISH workstations’ -k REPORT_OUTPUT_FORMAT=html -k QuerySQL=”select * from mdl.run_cycle_refs_v where job_stream_wks_name like ‘CLYNELISH%’ order by job_stream_wks_name, job_stream_name”
= TWSRCMAP :
= AGENT     : CLYNELISH_AGT
= Job Number: 211579381
= Wed May 2 11:39:02 BST 2012
===============================================================
Error: Can’t find resource for bundle common_logging, key en_US. Default file name reportcli.log is used
AWSBRC105I Report total build time: 11 seconds.
AWSBRC106I Report available on:  http://clynelish.scotchwhisky.local/TWSreports/runcycles.html.
===============================================================
= Exit Status           : 0
= Elapsed Time (Minutes) : 1
= Job CPU usage (ms) : 10994
= Job Memory usage (kb) : 121120
= Wed May 2 11:39:15 BST 2012
===============================================================

 

The resulting HTML report viewed through a browser looks like the sample shown below

runcycles large

Job Run Cycles Report

 

Tips and Tricks


Here are some tips and tricks discovered during the preparation of this article and some further work with the TWS 8.6 command line reporting functionality.

 

Automatically scheduling report creation


Using the approach outlined within this article allows TWS reports to be scheduled on a regular basis and avoids the need for manual creation of the reports. A bonus is that you have the full power of the TWS scheduling criteria available for deciding when reports need to be created!

Creating the same report in multiple formats


If it is necessary to create one or more reports in multiple formats, such as one copy in HTML format, one in PDF format and one in CSV format, consider creating three template jobs based on the example above – one for each report format, each with the “-k REPORT_OUTPUT_FORMAT=” option set to their respective report type instead of the variable setting.

A single job stream can then include the three report template jobs, using the other common variables (e.g. SQL statement, report title and description, etc.), but ignoring the report format variable setting. Hence a single job stream can produce the same report in three different formats in a single execution.

Report permissions

The file access permissions on the created report file are set to the umask of the report creator. In my testing when created through the scheduled job using the TWS instance owner this defaulted to only the TWS instance owner and group having read access. The HTML report file needed to have world read permission set before it was accessible through the web browser to anyone. Note that this is a one-time action for each new HTML report file.

Previous TWS versions


Some basic testing of the TWS 8.6 command line reporting has shown that it also works with earlier TWS versions – after a little editing of some scripts that is! Obviously the TWS 8.6 command line reporting functionality is only supported by IBM on TWS 8.6 so if you do make the following changes to use this functionality on earlier TWS versions “you are on your own” and unlikely to be able to get support from IBM in the case of problems.

To use the TWS 8.6 command line reporting functionality on earlier TWS versions (8.3 and higher), take the following steps

·         Uncompress the TWS command line reporting software to a suitable location e.g. /opt/ibm/TWS/ReportCLI

·         Make a backup copy of the reportcli.sh (UNIX/Linux) or reportcli.cmd (Windows) script

·         Edit the reportcli.sh or reportcli.cmd script and locate the line containing the “-version” keyword on the java command (towards the end of the script) and update the value to reflect the TWS version in use

  •  Save the changes
  •  Check if the command line reporting works

Further testing has shown that it may not be necessary to update the version parameter as all of the reports produced so far complete successfully whether the version setting reflects the actual TWS version or not.

Visits: 502