Advanced use of the TWS 8.6 command line reporting
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_ReportDescription=This is the report description
QuerySQL=SELECT * from <DatabaseSchema>.<TableName>
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.
|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
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
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
-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_SQL01 “select * from MDL.CALENDARS_V”
ORB_REPCLI_TITLE “Custom SQL report”
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
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_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^”
DESCRIPTION “Orb custom SQL report using TWS command line reporting”
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
DESCRIPTION “TWS custom TWS reporting – uses settings in variable table below”
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
A sample job stream level variable table is shown below including the SQL statement split over two variables.
DESCRIPTION “Report specific options for REPORT1 job”
ORB_REPCLI_DESC “Run cycle definitions for Orb jobs on the CLYNELISH workstations”
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”
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
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.
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.