Character Delimited Support in Generate Source

Introduction

The GENERATE SOURCE functionality of the %CPDDUTL macro has been enhanced to allow customers to generate a ‘first cut’ of table and variable definitions for data sources directly from the raw data.

NOTE: Generate Source is more strict than %CxPROCES in terms of how the CDC data is presented to it, however this should not be a problem as typically generating table and variable source is not a daily event and the data can be manipulated to meet the requirements.

Requirements

• The data must contain 1 header record that contains delimited values that describe the data that follows.
• It is recommended that you include about 10 lines of data after the header record, as this is processed in order to assist in determining characteristics of the data.
• If the rawdata does not follow the rules described in the above 2 bullets then the data should be modified so that it meets those conditions.
• Although specifying the DELIM= parameter is not necessary, it is recommended. Generate Source will try and figure out the delimeter automatically, however it is not always possible depending on the format of the data.
• Only one table will be constucted per run of Generate Source when the INTYPE=CDC option is specified.
• The NAME= option can be used to specify the name of the table that you want to create. If not specified then UCDCTAB will be the default.

Example

The following is an example of how this facility can be used. Please refer to the IT Service Vision Macro Reference documentation for more information on the parameters to use with the GENERATE SOURCE control statement.

The data :-

machine,date,time,cpu utilization,value pct,i/o per sec,device name,average val,max val,min val,
tom,01JAN2000,01:00:00,0.5,20,6,devHarry,5,7,2,
tom,01JAN2000,01:05:00,0.6,21,7,devHarry,6,8,5,
tom,01JAN2000,01:10:00,0.7,22,8,devHarry,7,8,8,
tom,01JAN2000,01:15:00,0.8,23,9,devHarry,8,9,8,
tom,01JAN2000,01:20:00,0.9,24,10,devHarry,9,3,6,
tom,01JAN2000,01:25:00,0.0,25,11,devHarry,10,7,4,

The above data is only intended to illustrate the CDC support of Generate Source.

Start up IT Service Vision and submit the following code from the program editor;

%cpcat;
cards4;
generate source infile='c:\rawdata\cdctest.data'
intype=cdc
type=interval
delim=’,’
name=utest
filename='c:\temp\ddutl.txt';
;;;;
%cpcat(cat=work.temp.temp.source);
%cpddutl(entrynam=work.temp.temp.source);

The output of the above code is a file in c:\temp called ddutl.txt that contains table and variable definitions that will allow you to process the CDC data.The table and variable definitions that are created should be reviewed and modified if necessary. For more information on 'Interpretation Types' please refer to the How To/Macro help.

/*--------------------------------------------------------------*
| Table definition statements generated on 24MAR2000:16:32:13.25
| For PDB table.....: UTEST
| Table type........: INTERVAL
| Now sorted by.....:
| Will be sorted by.:
*--------------------------------------------------------------*/

DELETE TABLE NAME=UTEST NOERROR ;
CREATE TABLE NAME=UTEST
COLLECTOR='GENERIC' TOOLNAME=CHARDELIM TYPE=INTERVAL KEPT=YES
EXTNAME='CDCTABLE' IDNUM=0
LABEL='CDC Table' ARCHIVE=NO
DESCRIPTION='CDC Table'
DETAIL=(agelimit=10 )
DAY=(agelimit=45 )
WEEK=(agelimit=15 )
MONTH=(agelimit=18 )
YEAR=(agelimit=5 ) ;

/*----------------------------------------------------------*
| Variable definitions for table: UTEST
*----------------------------------------------------------*/

CREATE VARIABLE NAME=MACHINE EXTNAME='machine'
SUBJECT='N/A' LABEL='machine'
DESCRIPTION='machine'
FORMAT=. OID=. IDNUM=.
INTERPRET=STRING LENGTH=32
TYPE=CHARACTER KEPT=YES
;

CREATE VARIABLE NAME=DATE EXTNAME='date'
SUBJECT='N/A' LABEL='date'
DESCRIPTION='date'
FORMAT=DATE9. OID=. IDNUM=.
INTERPRET=DATE LENGTH=8
TYPE=NUMERIC KEPT=YES
INFORMAT=DATE9.
;

CREATE VARIABLE NAME=TIME EXTNAME='time'
SUBJECT='N/A' LABEL='time'
DESCRIPTION='time'
FORMAT=TIME12.2 OID=. IDNUM=.
INTERPRET=TIME LENGTH=8
TYPE=NUMERIC KEPT=YES
INFORMAT=TIME8.
ISTATS =(
NOCOUNT SUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
DAY =(
NOCOUNT SUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
WEEK =(
NOCOUNT SUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
MONTH =(
NOCOUNT SUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
YEAR =(
NOCOUNT SUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
;

CREATE VARIABLE NAME=CUTLZTN EXTNAME='cpu utilization'
SUBJECT='N/A' LABEL='cpu utilization'
DESCRIPTION='cpu utilization'
FORMAT=PERCENT9.2 OID=. IDNUM=.
INTERPRET=PERCENT LENGTH=6
TYPE=NUMERIC KEPT=YES
ISTATS =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
DAY =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
WEEK =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
MONTH =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
YEAR =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
;

CREATE VARIABLE NAME=VLPCT EXTNAME='value pct'
SUBJECT='N/A' LABEL='value pct'
DESCRIPTION='value pct'
FORMAT=6.2 OID=. IDNUM=.
INTERPRET=PERCENT100 LENGTH=6
TYPE=NUMERIC KEPT=YES
ISTATS =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
DAY =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
WEEK =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
MONTH =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
YEAR =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
;

CREATE VARIABLE NAME=IOPRSEC EXTNAME='i/o per sec'
SUBJECT='N/A' LABEL='i/o per sec'
DESCRIPTION='i/o per sec'
FORMAT=BEST12.2 OID=. IDNUM=.
INTERPRET=RATE LENGTH=8
TYPE=NUMERIC KEPT=YES
ISTATS =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
DAY =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
WEEK =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
MONTH =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
YEAR =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
;

CREATE VARIABLE NAME=DVCNAME EXTNAME='device name'
SUBJECT='N/A' LABEL='device name'
DESCRIPTION='device name'
FORMAT=. OID=. IDNUM=.
INTERPRET=STRING LENGTH=200
TYPE=CHARACTER KEPT=YES
;

CREATE VARIABLE NAME=AVRGVAL EXTNAME='average val'
SUBJECT='N/A' LABEL='average val'
DESCRIPTION='average val'
FORMAT=BEST12.2 OID=. IDNUM=.
INTERPRET=AVERAGE LENGTH=8
TYPE=NUMERIC KEPT=YES
ISTATS =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
DAY =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
WEEK =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
MONTH =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
YEAR =(
NOCOUNT NOSUM AVERAGE NOMAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
;

CREATE VARIABLE NAME=MAXVAL EXTNAME='max val'
SUBJECT='N/A' LABEL='max val'
DESCRIPTION='max val'
FORMAT=BEST12.2 OID=. IDNUM=.
INTERPRET=MAXIMUM LENGTH=8
TYPE=NUMERIC KEPT=YES
ISTATS =(
NOCOUNT NOSUM NOAVERAGE MAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
DAY =(
NOCOUNT NOSUM NOAVERAGE MAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
WEEK =(
NOCOUNT NOSUM NOAVERAGE MAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
MONTH =(
NOCOUNT NOSUM NOAVERAGE MAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
YEAR =(
NOCOUNT NOSUM NOAVERAGE MAXIMUM NOMINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
;

CREATE VARIABLE NAME=MINVAL EXTNAME='min val'
SUBJECT='N/A' LABEL='min val'
DESCRIPTION='min val'
FORMAT=BEST12.2 OID=. IDNUM=.
INTERPRET=MINIMUM LENGTH=8
TYPE=NUMERIC KEPT=YES
ISTATS =(
NOCOUNT NOSUM NOAVERAGE NOMAXIMUM MINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
DAY =(
NOCOUNT NOSUM NOAVERAGE NOMAXIMUM MINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
WEEK =(
NOCOUNT NOSUM NOAVERAGE NOMAXIMUM MINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
MONTH =(
NOCOUNT NOSUM NOAVERAGE NOMAXIMUM MINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
YEAR =(
NOCOUNT NOSUM NOAVERAGE NOMAXIMUM MINIMUM NOUSS NOCV NORANGE
NOVARIANCE NOSTD NONMISS
)
;

CREATE VARIABLE NAME= HOUR
EXTNAME='HOUR'
LABEL='Hour of day'
DESCRIPTION='Hour_of_day'
KEPT=YES VALIDITY=. INTERPRET=INT TYPE=NUMERIC
LENGTH=4 FORMAT=2. INFORMAT=. IDNUM=.
OID= .
SUBJECT='N/A' ;

CREATE VARIABLE NAME= SHIFT
EXTNAME='SHIFT'
LABEL='Operations shift'
DESCRIPTION='Operations_shift'
KEPT=YES VALIDITY=. INTERPRET=STRING TYPE=CHARACTER
LENGTH=1 FORMAT=. INFORMAT=. IDNUM=.
OID= .
SUBJECT='N/A' ;

/*--------------------------------------------------------------*
| By, Class, ID, and Index variable lists for table: UTEST
*--------------------------------------------------------------*/

UPDATE TABLE NAME= UTEST
DETAIL =(
BYVARS= ' DATETIME HOUR SHIFT'
)
DAY =(
CLASSVARS= ' DATETIME HOUR SHIFT'
)
WEEK =(
CLASSVARS= ' DATETIME HOUR SHIFT'
)
MONTH =(
CLASSVARS= ' DATETIME HOUR SHIFT'
)
YEAR =(
CLASSVARS= ' DATETIME HOUR SHIFT'
)
;