Difference between revisions of "Reading Spectrum Data Change Records"

From Vital Soft Wiki
Jump to: navigation, search
>Johno
(Accessing the SD file in AskPlus/Visimage)
>Johno
(Editing the Job Parameters)
 
(17 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
== Reading Spectrum Data Change Records ==
 
== Reading Spectrum Data Change Records ==
 
===Overview===
 
===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 job is also available that extracts specific data change records based on their TYPE and ENTRY-DATE.
+
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===
 
===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 command.   
+
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.gz
+
   
 +
:gunzip datachange_pa32.tar.gz
 +
:tar -xvf datachange_pa32.tar
 
or
 
or
:gunzip datachange_it64.gz
+
: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.
 
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 [http://www.vital-soft.com/product-downloads/ here].
  
 
===Editing the Job Parameters===
 
===Editing the Job Parameters===
  
The job contains a prompts section to customize the extract.  You may specify a date range, specific record types 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.
+
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 ***
 
  *** Start of Prompts Section ***
  *
+
   
  * START-DATE is the range start-date (YYYYMMDD format)
+
  # StartDate is the range start-date (YYYYMMDD format)
  * END-DATE 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.
* The range maximum is 100 days from the start-date, regardless of end-date.
+
  # IncludeShareHist is the flag to include SH-HISTORY ("Y" or "N")
  *
+
  # IncludeLoanHist is the flag to include LN-HISTORY ("Y" or "N")
Enter the date range here:
+
   
newreg #START-DATE:I2 = 20140101
+
  export StartDate=20160101
newreg #END-DATE:I2 = 20140131
+
  export EndDate=20160131
 
+
  export OutputFile=$SPXHOME/FI/DATA/DC-FILE
*
+
  export IncludeShareHist=Y
* INCLUDE-SH-HIST is the flag to include SH-HISTORY ("Y" or "N")
+
  export IncludeLoanHist=Y
  * INCLUDE-LN-HIST is the flag to include LN-HISTORY ("Y" or "N")
 
  *
 
By default, the extract will include both SH-HISTORY and LN-HISTORY.
 
  newreg #INCLUDE-SH-HIST:X1 = "Y"
 
  newreg #INCLUDE-LN-HIST:X1 = "Y"
 
 
 
*
 
* VALID-TYPES is the list of the datachange HISTORY.TYPE values to look for.
 
*
 
Enter all valid types in a list.  Each line (except the last) must be terminated by a cat
 
newreg #VALID-TYPES:x300
 
  load #VALID-TYPES =
 
  "200,201,202,203,204,205,206,210," cat
 
  "300,303,304,305,306,307,308,309,313,314,315,396,399," cat
 
  "400,401,403,405,410,430,440,497," cat
 
  "500,506,510,550,551,552,560,570," cat
 
  "600,700,800,850,900,910,920,921,930,940,950,960,970"
 
 
 
*
 
* OUTPUT-FILE is the fully qualified file name of the final Self Describing file .
 
  *
 
Enter a fully qualified file name for the job to create.
 
  newreg #OUTPUT-FILE:X100 = “/ASKPLUS/visimage/DC-FILE”;
 
 
   
 
   
 
  *** End of Prompts section ***
 
  *** End of Prompts section ***
Line 61: Line 44:
 
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:
 
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:
  
  DC-TYPE:               Identifies the record as "Change" or "Const"
+
'''Actual Change Records:
  DC-ITEM-NAME     The name of the item (field) that was changed. Array items are shown as ADDRESS-LINES(02)
+
 
  DC-ITEM-TYPE       The Eloquence data type. For example: I2, X30
+
  DC-TYPE:         = "Change"
  DC-BEFORE-VALUE The value of the item before the change.  (The value of the item for a Const record.)
+
  DC-ITEM-NAME:    The name of the item (field) that was changed.
  DC-AFTER-VALUE   The value of the item after the change(Blank for a Const record.)
+
                  - 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===
 
===Sample AskPlus Query===
The following will find all changes made to the APR field.
+
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
 
  /ASKPLUS/askplus
 
  open /ASKPLUS/visimage/DC-FILE;
 
  open /ASKPLUS/visimage/DC-FILE;
 
  find primary=DC-FILE; outsel=(@)
 
  find primary=DC-FILE; outsel=(@)
  DC-TYPE="Change" and DC-ITEM-NAME = "APR"
+
  DC-TYPE = "Change" and DC-ITEM-NAME = "APR"

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"