Difference between revisions of "Reading Spectrum Data Change Records"

From Vital Soft Wiki
Jump to: navigation, search
>Johno
(Editing the Job Parameters)
>Johno
(Editing the Job Parameters)
 
Line 22: Line 22:
  
 
  *** Start of Prompts Section ***
 
  *** Start of Prompts Section ***
 
+
# StartDate is the range start-date (YYYYMMDD format)
+
# StartDate is the range start-date (YYYYMMDD format)
# EndDate is the range end-date (YYYYMMDD format)
+
# EndDate is the range end-date (YYYYMMDD format)
# OutputFile is the fully qualified file name of the final SD file.
+
# OutputFile is the fully qualified file name of the final SD file.
# IncludeShareHist is the flag to include SH-HISTORY ("Y" or "N")
+
# IncludeShareHist is the flag to include SH-HISTORY ("Y" or "N")
# IncludeLoanHist is the flag to include LN-HISTORY ("Y" or "N")
+
# IncludeLoanHist is the flag to include LN-HISTORY ("Y" or "N")
 
+
export StartDate=20160101
+
export StartDate=20160101
export EndDate=20160131
+
export EndDate=20160131
export OutputFile=$SPXHOME/FI/DATA/DC-FILE
+
export OutputFile=$SPXHOME/FI/DATA/DC-FILE
export IncludeShareHist=Y
+
export IncludeShareHist=Y
export IncludeLoanHist=Y
+
export IncludeLoanHist=Y
 
   
 
   
 
  *** End of Prompts section ***
 
  *** End of Prompts section ***

Latest revision as of 18:44, 12 January 2017

Reading Spectrum Data Change Records

Overview

The SH-HISTORY and LN-HISTORY datasets (HISTRY database) contain data change records that are stored in a binary format. AskPlus provides two special functions designed to read and interpret these records. To use these functions the datachng.sl (or .so Itanium) must be installed in the /ASKPLUS directory. An AskPlus/SUPRTOOL job is available that extracts specific data change records based on their ENTRY-DATE and creates an SD file that can be used for reporting with AskPlus or Visimage3.

Installing the Data Change Files

The data change library file and the extract job are provided in a single gz download. Choose the pa32 file for PARISC and the it64 file for Itanium. Transfer the gz file to the /ASKPLUS directory on UNIX and execute the following commands to unzip the gzip file and extract the files from the tar file. You should be logged on as the ask plus user while installing these files.

gunzip datachange_pa32.tar.gz
tar -xvf datachange_pa32.tar

or

gunzip datachange_it64.tar.gz
tar -xvf datachange_it64.tar

The library file (datachng.sl or datachng.so) must remain in the /ASKPLUS directory. The extract job (ask_datachange_job) should be moved to a location with access to the Spectrum databases and may be renamed. The contents of the job may also be copied into UC4 a script.

Download data change files here.

Editing the Job Parameters

The job contains a prompts section to customize the extract. You may specify a date range and the name of the final SD file that is created. You can also specify whether or not to include share history and/or loan history.

*** Start of Prompts Section ***

# StartDate is the range start-date (YYYYMMDD format)
# EndDate is the range end-date (YYYYMMDD format)
# OutputFile is the fully qualified file name of the final SD file.
# IncludeShareHist is the flag to include SH-HISTORY ("Y" or "N")
# IncludeLoanHist is the flag to include LN-HISTORY ("Y" or "N")

export StartDate=20160101
export EndDate=20160131
export OutputFile=$SPXHOME/FI/DATA/DC-FILE
export IncludeShareHist=Y
export IncludeLoanHist=Y

*** End of Prompts section ***

Accessing the SD file in AskPlus/Visimage

The SD file may be used in AskPlus with an OPEN command or in Visimage using the View > Host Databases window.

The file contains one record for each item with a recorded data change value. These data change records have a DC-TYPE = "Change" and include both a before (DC-BEFORE-VALUE) and an after (DC-AFTER-VALUE) value for the item. The file also contains constant records (DC-TYPE = "Const") that, in some cases, help to identify the record that was updated. Const records do not have an after value. The before and after values are stored as X50. Numeric values can be easily converted by AskPlus.

Each record contains ENTRY-DATE, ACCOUNT, SUFFIX, TELLER-INTL, COMMAND, SEQUENCE and many more fields that identify the data change. The specific data change records are as follows:

Actual Change Records:

DC-TYPE:         = "Change"
DC-ITEM-NAME:    The name of the item (field) that was changed.
                 - Array items are shown with the index, for example: ADDRESS-LINES(02)
DC-ITEM-TYPE:    The Eloquence data type. For example: I2, X30
DC-BEFORE-VALUE: The value of the item before the change.
DC-AFTER-VALUE:  The value of the item after the change.

Constant (Lookup) Records:

DC-TYPE:         = "Const"
DC-ITEM-NAME:    The name of the lookup (field) to help identify the change.
DC-ITEM-TYPE:    The Eloquence data type. For example: I2, X30
DC-BEFORE-VALUE: The value of the lookup item.
DC-AFTER-VALUE:  Blank.

Sample AskPlus Query

Once the extract job has been run, and the DC-FILE has been created, the following will find all changes made to the APR field.

/ASKPLUS/askplus
open /ASKPLUS/visimage/DC-FILE;
find primary=DC-FILE; outsel=(@)
DC-TYPE = "Change" and DC-ITEM-NAME = "APR"