CA Workload Automation EE r11.3 Report Server. Fred Brisard

CA Workload Automation EE r11.3

Report Server

Terms of This Presentation

This presentation was based on current information and resource allocations as of October

2009 and is subject to change or withdrawal by CA at any time without notice.

Notwithstanding anything in this presentation to the contrary, this presentation shall not serve

to (i) affect the rights and/or obligations of CA or its licensees under any existing or future

written license agreement or services agreement relating to any CA software product; or (ii)

amend any product documentation or specifications for any CA software product. The

development, release and timing of any features or functionality described in this presentation

remain at CA‟s sole discretion. Notwithstanding anything in this presentation to the contrary,

upon the general availability of any future CA product release referenced in this presentation,

CA will make such release available (i) for sale to new licensees of such product; and (ii) to

existing licensees of such product on a when and if-available basis as part of CA maintenance

and support, and in the form of a regularly scheduled major product release. Such releases

may be made available to current licensees of such product who are current subscribers to CA

maintenance and support on a when and if-available basis. In the event of a conflict between

the terms of this paragraph and any other information contained in this presentation, the

terms of this paragraph shall govern.

For Informational Purposes Only

Certain information in this presentation may outline CA‟s general product direction. All

information in this presentation is for your informational purposes only and may not be

incorporated into any contract. CA assumes no responsibility for the accuracy or completeness

of the information. To the extent permitted by applicable law, CA provides this document “as

is” without warranty of any kind, including without limitation, any implied warranties or

merchantability, fitness for a particular purpose, or non-infringement. In no event will CA be

liable for any loss or damage, direct or indirect, from the use of this document, including,

without limitation, lost profits, lost investment, business interruption, goodwill, or lost data,

even if CA is expressly advised of the possibility of such damages.

Topics

> Consolidated Reporting

> Report Server

> Application Reporting

Consolidated Reporting Overview

HISTFILE

CA Workload

Automation

EE

Report Server

Cache

(100 entries)

Report

Server

Reporting

Consolidated Reporting

> A database version of the History File (HISTFILE)

> Reporting Tools



any report tool that supports the selected database



the existing ESP reporting with the new SQL command

> Use the database views



ESP_JOB_HIST



ESP_APPL_HIST

Consolidated Reporting – Some Details

> View definitions



available in the SSCPSAME library



for example the DB2 samples are CYBRSS06 and

CYBRSS07.

> Flag fields - „T‟ and „F‟

> By default null are displayed as „-‟

Consolidated Reporting – Table vs. View

JHRNAME

JHRQUAL

JHRSCHED

JHRCML

JHRENDT

JHRSEXEC

TABLE

ESP_JHR

JOBA

QUALA

9/1/2009 11.31

T

9/1/2009 11.45

T

JOBB

QUALB

7/29/2009 20.00

F

7/29/2009 20.30

T

JOBC

QUALC

5/15/2009 8.40

F

null

T

JOBNAME

JOBQUAL

SCHEDDATE

SCHEDT

STATUS

VIEW

ESP_JOB_HIST

JOBA

QUALA

9/1/2009

11.31

COMPLETE

JOBB

QUALB

7/29/2009

20.00

ENDED

JOBC

QUALC

5/15/2009

8.40

STARTED

create view esp_job_hist

as select JHRNAME JOBNAME,

JHRQUAL JOBQUAL,

date(JHRSCHED) SCHEDDATE,

…

Consolidated Reporting – SQL REPORT



Example using the REPORT command page mode

Consolidated Reporting - SPUFI

Consolidated Reporting - SPUFI

Consolidated Reporting - Qualifier

Create table MyTable

user1.MyTable

user2.MyTable

Create table

user2.MyTable

Select from MyTable

Login to the

database as user1

Login to the

database as user2

>

The full table and view names is

qualifier.name

>

If the qualifier was not provided in

the SQL statement, then the

Reporting Tools

> CA Workload Automation EE Reporting

> BusinessObjects



Web Intelligence



Crystal Reports

> CA-Easytrieve

> Any reporting tool that can read SQL databases

More on this will be reviewed in the Reporting Lab

Report Server Topics

> Overview

> What you need

> Configuration

> The RSERVER command

Overview

> Propagate HISTFILE records to a database

> ESP tracks all changes to the History File and transfers

them to the Report Server which records them to the

database

> Database updates can occur in



real-time

Overview

> Supported databases are



DB2



Oracle



SQL Server

> Users can select the fields that the server propagates to

the database

Overview

> Only one ESP system can cannot connect to a specific

server.

> Multiple servers can propagate the history information to

a single database and table.

> New SQL command allows the existing reporting feature

to access data from a database.

Installation

> SMP/E FMID CEACB30 must be installed

> The database host name, port number and name.

Installation

> The database class directory in USS. When it‟s not

available, the JAR file can be copied from Windows in

binary mode.

Database

JAR File Name

DB2 UDB

db2jcc.jar

db2jcc_license_cu.jar

db2jcc_license_cisuz.jar

Oracle

ojdbc14.jar

Configuration

> The server parameters are in a new parmlib member

called RSERENV. It can be copied from sample

CYBRSS01.



Parameter name must start on first column

Parmlib Example

ESP_JAVA_HOME=/sys/java31bt/v5r0m0/usr/lpp/java/J5.0

ESP_JDBC_PATH=/sys/DB2V910/usr/lpp/db2910_jdbc/classes

RServerPort=6671

RServerDbType=DB2

RServerDbHost=usilca31.ca.com

RServerDbPort=5058

RServerDbName=D91APTIB

RServerDbUser=MIZDA02

RServerDbQueryUser=MIZDA02

RServerJHRTable=esp_jhr

RServerAHRTable=esp_ahr

RServerJHRFields=

RServerAHRFields=

RServerLogLevel=1

RServerQueryLimit=1000

Configuration

> Store password for Database User IDs



Use the existing ESP PASSWORD command with

TYPE(RSERVER)

Configuration

> Create the server started task procedure by copying it

from CYBRSS02 sample

Tracking HISTFILE updates

> Use the RSERVER command to control tracking of

updates to the History File by ESP Tracking Manager

> Syntax –

RSERVER [TRACK|NOTRACK]

> The update (record key only) is saved in the COMMQ file

until being confirmed by the server

HISTFILE

COMMQ

ESP Tracking

Manager

ESP Report

Server Cache

(100 entries)

XME

JHR/AHR

Client Server communication

//IEFPROC EXEC PGM=BPXBATCH,

// PARM='PGM /bin/sh /ESP/seacpath/CYBRS002'

//STDOUT DD SYSOUT=*

//STDERR DD SYSOUT=*

//STDENV DD

DISP=SHR,DSN=PARMLIB(RSERENV)

ESP

Subsystem

Report

Server

Client

CYBES144

Active/Inactive

Connected/

Disconnected

USS

Report Server STC

ServerRead/

ServerNotReady

Tracking

Manager

CYBJS082

ESP_JAVA_HOME=/sys/java31bt/v5r0m0/usr/lpp/ java/J5.0 ESP_JDBC_PATH=/sys/DB2V910/usr/lpp/db2910_ jdbc/classes RServerPort=6671 RServerDbType=DB2 RServerDbHost=usilca31.ca.com …….

RSERENV Parmlib member

Report

1

2

Controlling the server using the RSERVER

> The syntax of the RSERVER command –

RSERVER [START|STOP|STATUS]

Controlling the server using the RSERVER

> Use the HOST parameter when you start the server

> Look for error messages in the STDERR sysout.

> Log messages are written to the STDOUT sysout.

> Validate the four statuses in the STATUS output

STDOUT – Java output

Creating the database views

> The tables are created by the server during its

initialization

Updating the database in batch mode

> Users can tailor and submit sample CYBRSS03 to copy

HISTFILE records directly to the database

> The report server does not have to be up

> The RSERENV parmlib member is used

> The job consists of four steps

Updating the database in batch mode

Step 1 - Issue the RSERVERB command to generate missing

server parameters

RSERVERB START HISTID(<hist-fid>) HISTFILE('/tmp/histfile.bin')

# Report Server Batch Environment Variables

RServerSystemName=DANIEL

RServerHistID=HIST1

RServerHistFileName=/tmp/histfile.bin

RServerCKPT=B870807A6E4F0B59

The output is a temporary file

DSN=&&ESPBENV

Updating the database in batch mode

Step 2 - Extract records from the HISTFILE using the existing

REPORT command. In the example below all records that

were updated in the past hour are extracted.

//EXTRACT EXEC PGM=ESP,PARM='SUBSYS(<subsys>)',COND=(0,NE)

//HISTTEMP DD DISP=(,PASS),DSN=&&HIST,SPACE=(CYL,(20,20)),

// DCB=(DSORG=PS,RECFM=VB)

//SYSPRINT DD SYSOUT=*

//SYSIN DD *

REPORT

HISTFILE <hist-fid>

COPY SELECT DATASET(HISTTEMP)

CRITERIA

LASTUT GE NOW LESS 1 HOUR

ENDR

Updating the database in batch mode

Step 3 - Copy the extracted records to USS

//COPY EXEC PGM=IKJEFT01,COND=(0,NE)

//SYSTSPRT DD SYSOUT=*

//INDD DD RECFM=U,DISP=(OLD,DELETE),DSN=&&HIST

//OUTDD DD PATH='/tmp/histfile.bin',

// PATHOPTS=(OWRONLY,OCREAT),PATHMODE=(SIRUSR,SIWUSR)

//SYSPRINT DD SYSOUT=*

//SYSTSIN DD *

OCOPY INDD(INDD) OUTDD(OUTDD) BINARY

/*

Updating the database in batch mode

Step 4 - Execute the CYBRS003 shell script to update the

database

//RUN EXEC PGM=BPXBATSL,COND=(0,NE),

// PARM='PGM /bin/sh <seacpath>/CYBRS003' <==

SMP/E SEACPATH Target zone directory

//STDOUT DD SYSOUT=*

//STDERR DD SYSOUT=*

//STDENV DD DISP=SHR,DSN=ESP.PARMLIB(CYBRSS01)

// DD DISP=(SHR,DELETE),DSN=&&ESPBENV <==

Output of RSERVERB Command

Application Reporting

> Based on the existing ESP reporting feature using the

new command APPLREPORT

> An Application History Record(AHR) is generated and

written to a HISTFILE for each run and rerun of an

application when the application is

completed

> The new Application Tracking Definition Table(ATDT)

controls the selection of the HISTFILE

Application Reporting - APPLREPORT

Application Reporting - ATDT

> Application Tracking Definition Table



Selects the HISTFILE for an Application History Record



Decide whether or not to track a record

> Use the LOADATDT command (in your ESPPARM) to load

the table -

LOADATDT ESP.PARMLIB(ATDT)

Application Reporting - ATDT

Job Reporting Enhancements

> New Job history fields

Field Name

Description

Format

CRITJOB

Critical job

YES/NO

CRITPATH

Critical path

YES/NO

Task

Identify task jobs

YES/NO

USERSTAT

User status

String(28)

Job Reporting Enhancements

> Provide new time fields to allow users to sort the output

using the time value only (without date)

Existing field

New Field

Description

RDRON

RDRONTOD

Reader on time

EXECST

EXECSTTOD

Execution start time

ENDT

ENDTTOD

End time

PURGT

PURGTTOD

Purge time

COMPT

COMPTTOD

Completion time

Job Reporting Enhancements

> Example - Incorrect output when using the non-TOD field

in the SORT command

REPORT

SETWIDTH 20

DISPLAY ENDDATE,ENDT

SORT

ENDT