Difference between revisions of "Reading Spectrum Data Change Records"
>Johno (→Overview) |
>Johno (→Editing the Job Parameters) |
||
Line 19: | Line 19: | ||
===Editing the Job Parameters=== | ===Editing the Job Parameters=== | ||
− | The job contains a prompts section to customize the extract. You may specify a date range | + | 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 *** | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | + | # 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 *** | *** End of Prompts section *** |
Revision as of 18:42, 12 January 2017
Contents
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"