Ensuring Data Integrity using Suprtool
Contents
1. 10 Ensuring Data Integrity using Suprtool Check completeness At least five status records per work order gt get d process gt extract work order gt sort work order gt duplicate none keys count total status code gt output worksum link temp gt xeq gt input worksum gt if st count lt gt 5 or st total 1 lt gt 150 gt list standard title Non Standard work orders 11 Lets say that each work order goes through 5 stages and that each stage For Techies generates a detail record with a status code of 10 20 30 40 and 50 respectively You can use the Duplicate command to summarize count and subtotal the status codes for each work order In this scenario a standard work order will count up to five and subtotal the status codes to 150 10 20 30 40 50 The Total option of the Duplicate command generates a new variable for each field selected and names the variables ST TOTAL 1 ST TOTAL 2 etc One field counts the five stages and its variable name is ST COUNT The Define command can later rename these fields so they are more meaningful gt define work orders st count References For a complete sample gt define status checksum st total 1 f script see page 18 gt if work orders lt gt 5 or status checksum lt gt 150 11 Ensuring Data Integrity using Suprtool Check completeness using HowMessy m Only 5 records per work order gt input loadfile gt if dataset D P2. You may not think you have any invalid dates your application program does a good job of validation but do you have any flag values that have special meaning E g 999999 1 0 NEVER NOTADATE The sample jobstream shows you all invalid dates and flag values and how often they appear in the file References For a complete sample script see page Sample 21 15 Ensuring Data Integrity using Suprtool Comparing records in two files m Records in two files should have the same values in the address field gt link input filel gt Llink link file2 gt link output file3 gt link xeq gt input file3 gt if address 1 lt gt address 2 gt list standard title Addresses don t match 16 If you have two files that are supposed to mirror each other you can use For Techies Suprtool to look for discrepancies You need to be able to match the records based on some common field Then in the matching records you can look for differences Create a composite file using Suprlink that has all the fields you are interested in from both files References For a complete sample script see page Sample 22 16 Ensuring Data Integrity using Suprtool P Summary of Suprtool Integrity Checks m Easy to validate many important facts and relationships m No need to write programs m Fast execution m Sample job streams can be modified One way to make the jobs more intelligent is to initialize a
3. ccyymmdd S invalid order date order date order date none keys count standard title Invalid date values counted Full sample job stream for comparing records in two files job dbcheck mgr sales db Irun suprtool pub robelle base sales 5 reader get d sales define address 1 address extract order no address 1 sort order no output filel link xeq base archive 5 reader get old sales define address 2 address extract order no address 2 sort order no output file2 link xeq link input filel link link file2 link output file3 link xeq input file3 if address 1 lt gt address 2 list standard title Addresses don t match between files xeq exit eoj Ensuring Data Integrity using Suprtool Invalid dates m Dates that don t make sense gt item order date date ccyymmdd gt if Sinvalid order date 1992 T2374 s 6 7 e 6 fo mM 2 6 4 is e 7 e sa a 2 2 a 5 7 es 0 a 15 To find date values that are syntactically incorrect use the invalid For Techies function of the If command invalid w s introduced invalid looks for dates whose values don t fit the rules for the date type in Suprtool version 4 0 defined in the Item command For example a month of 0 a day of 99 or in 1997 a day of 31 for month 11 Of course invalid takes leap years into account To find only the valid dates use IF NOT INVALID field name
4. comment situations comment comment comment comment comment sales price quantity not equal to amount sales tax not equal to 7 sales commissions greater than 15 payroll payments with computation errors file suprlist dev lp Irun suprtool pub robelle base get if list xeq get if list xeq get if list xeq base get if list exit leoj sales 5 reader d sales price quantity lt gt amount standard title Price Qty not Amount d sales amount 0 07 lt gt sales tax standard title Sales tax not 7 of sales amount d commissions commission owed sales amt gt 0 15 standard title Commissions greater than 15 payrol 5 reader d payments reg hrs pay rate ot hrs pay rate 1 5 amp lt gt pay amount standard title Paycheck computation errors Full sample job stream for completeness test job dbcheck mgr sales db comment Integrity Check 7 Completeness comment This job runs Suprtool to verify that the Control comment Dataset has sales tax records for the 50 valid comment states and that all 50 state codes are present comment M CONTROL is the dataset of control table records comment the key control key 2 bytes table type and comment 2 bytes table value comment Table 15 is for State Sales Tax records E g 15CA comment The file STATES is a 50 line file with valid state comment codes file suprlist
5. ETS m By date range gt item order date date yyyymmdd gt if order date lt date 6 last orders older than 6 months 6 last is the last day of the month six months ago Suprtool pattern matching uses the following operators For Techies selects records that match a pattern gt lt selects records that do not match a pattern You can use these special characters for pattern matching zero or more characters of any type a single numeric character a single alphanumeric character zero or more blank characters amp escape character next character is literal match E g amp matches the character Date checking requires an Item command to define the date format YYYYMMDD YYMMDD YYMM PHDATE ASK and CALENDAR are valid formats that support lt and gt comparisons in 10 test fora relat ye Suprtool date and today functions MMDDYY MMDDYYYY date with the today DDMMYY and DDMMYYYY formats can only support the Nnna seo a complete sample script operator on page 15 References How would you find dates greater than tomorrow Ensuring Data Integrity using Suprtool Correct data values continued m By subfield gt define code2 product code 2 1 pa code2 lt gt s T 2nd character must be S or T m By data class gt if part code lt gt alpha part codes not numbers or spaces The Define command identifies temporary fields that can be used with Fo
6. Ensuring Data Integrity using Suprtool Ensuring Data Integrity using Suprtool A Robelle Tutorial by Mike Shumko August 1997 Copyright 1997 Robelle Solutions Technology Inc Each production database needs programs to ensure that company rules and conventions are being followed Unfortunately these programs seldom get written This tutorial is for those who want to learn how to perform a wide range of integrity checks with Suprtool Robelle s high performance data tool on the HP 3000 It will include the following integrity checks e Do debits equal the credits e Are there customers with two addresses e Are all dates within the last 18 months e Are there order headers with no line items e Are there missing auxiliary records in KSAM or flat files e Does price times quantity equal extended amount in all records e Are there at least five status reports per work order Robelle Solutions Technology Inc Toll free 1 888 ROBELLE Suite 201 15399 102A Avenue 1 888 762 3553 Surrey B C Canada V3R 7K1 Telephone 604 582 1700 Fax 604 582 1799 E mail support robelle com Web www robelle com Suprtool is a trademark of Robelle Solutions Technology Inc Other product names and companies may be the trademarks of their respective owners For Techies References For further information on topics covered in this tutorial please consult the Suprtool User Manual Ensuring Data Integrity using Suprtool Wh
7. ROCESS and amp searchfield WORK ORDER and amp maxchain lt gt 5 or avechain lt gt 5 00 m Only one address per customer gt input loadfile gt it dataset D ADDRESSES and amp maxchain gt 1 12 Your Suprtool tape also includes the bonus program HowMessy which For Techies generates statistics for a database When it produces the report it also creates a temporary self describing file called Loadfile You can write detailed job stream that examine the HowMessy output Here are some of the fields from that file DATABASE DATASET DATASETTYPE CAPACITY ENTRIES LOADFACTOR SECONDARIES HIGHWATER SEARCHFIELD MAXCHAIN AVECHAIN References STDDEVIATION 12 Ensuring Data Integrity using Suprtool Relationships between files Batch control total must equal the sum of batch records 1 Create a link file of the batch control records with batch amt 2 Create a link file of the summarized transaction records 3 Use Suprlink to merge the two files gt input mergfile gt define actual total st total 1 gt if batch amt lt gt actual total gt list standard title Batches with incorrect totals 13 Some systems batch their transaction entries and keep a control recordin For Techies a master file To check whether the sum of the detail records equals the control amount you can create two self describing files One file contains the control totals from the control records and the other file reads the
8. at s Inside Page m Types of integrity checks 3 m Simple rule verification 4 Duplicate detail records 5 Correct data values 6 m Missing pseudo masters 8 Masters without details 9 m Relationships between fields 10 m Check completeness 11 Relationships between files 13 m Summary 20 2 This tutorial will show you how Suprtool can perform a wide range of For Techies integrity checks to ensure that data is both correct and complete We will end the tutorial with a set of job streams that show you complete examples of each integrity check References Ensuring Data Integrity using Suprtool Types of integrity checks 1 Simple rule verification Duplicate detail records Correct data values Missing pseudo master records Master records without attached details Relationships between fields Check completeness Sk 0 oi oe ee ON Relationships between files You will learn how to use Suprtool to perform the following integrity For Techies checks All sample output in this tutorial was 1 Do debits equal credits j generated using version 2 Customers with two address records 3 7 of Suprtool iX 3 Check data values by running on an HP 3000 pattern E g postal codes Series 927 MPE iX date range E g only the last 18 months version 5 0 defined subfield E g second character always an S list of valid values E g transaction types Master records kept in KSAM or flat files Order headers withou
9. detail records m Are there customers with two address records gt get d addresses gt sort cust no gt duplicate only keys gt list standard title Customers with duplicate addresses gt xeq Unlike master datasets detail datasets do not ensure unique keys For Techies Suprtool s Duplicate command provides an easy way to detect duplicate records The Keys option in the Duplicate command refers to the fields specified in the Sort command The sorted fields do not have to be IMAGE keys For example Input Output 1111 123 Main St 2222 33359 East Ave 2222 15458 West Ave 2222 33359 East Ave 3333 13 Lake Shore Dr References For more information on all the options to Suprtool commands consult the Suprtool User Manual WARNING If you use the Delete and the Duplicate commands in the same task Suprtool will delete much more than just the duplicate records because the Delete operation occurs in the input phase of Suprtool processing while the Duplicate operation occurs in the output phase If there are only a few duplicates then you can manually delete them with Suprtool s Dbedit For more information see the Suprtool User Manual or call Robelle Technical Support For a complete sample script see page 14 Ensuring Data Integrity using Suprtool Correct data values m By pattern gt if phone no gt lt phone number with area code gt if product gt lt QWIDGET we only sell WIDG
10. detail records and uses the Total option of the Duplicate command to create a summary record for each batch You can use Suprlink to merge the two files and then compare the two fields with the If command References For a complete sample script see page 19 13 Full sample job stream for correct data values job dbcheck mgr sales db comment Integrity Check 3 Correct Data Values comment This job runs Suprtool to print these exception reports comment Phone numbers not in the form 604 582 1700 comment Product names that do not include the word WIDGET comment Order dates older than 6 months or beyond tomorrow comment Product codes whose 2nd character is not S or T comment Part codes that are not ALL letters file suprlist dev lp run suprtool pub robelle base sales db 5 reader get m customer if Phone no gt lt PEHR HHHH list standard title Irregular Phone numbers xeq get m product if description gt lt QWIDGET list standard title Non WIDGET Product names xeq get d order item order date date yyyymmdd if order date lt Sdate 6 last list standard title Orders older than 6 months ago xeq get d order if order date gt today 1 list standard title Orders made after tomorrow xeq get m product define code2 product code 2 1 if code2 lt gt S T list standard title Products whose 2nd char is not S or T xeq get m invent
11. dev lp Irun suprtool pub robelle base gl1 5 reader get m control define type control key 2 define value control key 3 2 table state table value file states if type 15 and not Slookup state table value list standard title Invalid State Sales tax records xeq get m control if type 15 extract value output mcontrol temp xeq input states define value 1 2 table tax table value file mcontrol if not lookup tax table value list standard amp title States missing from the Sales amp Tax table exit Full sample job stream for relationship test job dbcheck mgr sales db comment Integrity Check 8 Relationships Between Files file suprlist dev lp Irun suprtool pub robelle base gl 5 reader get m batch sort batch no extract batch no batch amt output mbatch temp link xeq get d transactions sort batch no duplicate none keys total trans amt extract batch no output dtrans temp link xeq link input dtrans link link mbatch link output mergfile temp link exit input mergfile define actual total st total 1 if batch amt lt gt actual total list standard title Batches with incorrect totals exit leoj Full sample job stream for invalid dates job dbcheck mgr sales db Irun suprtool pub robelle base get item if extract sort duplicate list xeq exit leoj sales 5 reader d sales order date date
12. les that are not IMAGE databases For Techies E g to facilitate sorted key access to the masters To check if detail The Table command records exist without masters you can create a table file of the master ignores any duplicates keys Then you can read the details using the Table command and the If before it starts sorting command with the lookup option Any detail records without keys in and processing the the table are orphans table file References For a complete sample script see page 16 Ensuring Data Integrity using Suprtool Masters without details m Order headers without line items gt get d order items gt extract order no gt sort order no gt out orditems temp link gt xeq gt get m order header gt table detail table order no sorted orditems gt if not lookup detail table order no gt list standard title Orders without line items 9 To find master records without details you can use a technique that For Techies creates a table file of the detail keys Then you can read the master records with the Table command followed by the If command with the lookup option Any master records without keys in the table are childless If you are reading large datasets over 100 000 records it is usually faster to sort a table file when you are creating it than to use the File option of the Table command Suprtool s Sort command uses our proprietary sort routines while the Table command use
13. ory if part code lt gt alpha list standard title Parts that are not ALL letters mers de a Full sample job stream for missing pseudo masters and details job dbcheck mgr sales db comment comment comment comment comment comment Integrity Check 4 Missing Pseudo Master Records This job runs Suprtool to list all the products in the inventory detail set that are missing from the KSAM prodmast file It also lists all order header records that have no detail line items file suprlist dev lp Irun suprtool pub robelle input define extract output xeq base get table if list xeq comment get extract sort out xeq get table if list exit prodmast KSAM master file product code 3 8 product code prodkeys temp link sales 5 reader d inventory master table product code file prodkeys not Slookup master table product code standard title Products without Masters Integrity Check 5 Masters Without Details d order items order no order no orditems temp link m order header detail table order no sorted orditems not lookup detail table order no standard title Orders without line items Full sample job stream for relationships between fields job dbcheck mgr sales db comment Integrity Check 6 Relationships Between Fields comment This job runs Suprtool to list the following
14. r Techies the If Extract Item Sort Table and Duplicate commands Temporary fields are especially useful in systems that reserve space for future fields E g FILLER 80 The maximum length of a command line is 256 characters To extend an If command you can use an ampersand amp at the end of the line to indicate continuation For expressions longer than 256 characters use the read function or the Table command You can use the following reserved words in an If statement to check whether all the characters in a field are one class ALPHA A Z a z 52 characters NUMERIC 0 9 10 characters References ALPHANUMERIC A Z a z 0 9 62 characters For a complete sample SPECIAL anything else 194 characters including space script see page 15 punctuation Roman 8 letters binary junk For an example of a How can you find fields with null values Table command with an If expression see the slide on the next page Ensuring Data Integrity using Suprtool Missing pseudo master records m Master records that are kept in KSAM or flat files gt input prodmast KSAM master file gt define product code 3 8 3 byte offset 8 byte length gt extract product code gt output prodkeys temp link gt xeq gt get d inventory gt table master table product code file prodkeys gt if not lookup master table product code gt list standard title Products without Masters Sometimes systems include master fi
15. s an insertion sort Remember to use the Sorted option with the Table command instead of the File option References For a complete sample script see page 16 Ensuring Data Integrity using Suprtool Relationships between fields m Does price multiplied by quantity always equal amount gt get d sales gt if price quantity lt gt amount gt list standard title Price Qty not Amount m Student Exercises Is the sales tax always 7 of the sales amount a ls the pay amount correct It should be the pay rate times the regular hours plus time and a half for overtime hours In most systems there are many built in formulas that need to be For Techies checked for compliance The If command supports arithmetic operations in the following order of precedence Parentheses have the highest precedence NOT Take the opposite logical negation AND Both conditions must be true OR One or the other condition must be true Unary minus Multiplication and division higher than and Addition and subtraction use parentheses where needed You cannot perform arithmetic operations on byte type fields and arithmetic expressions cannot start with a numeric constant E g 2 a 10 is invalid If you do have a byte type field consisting entirely of numeric digits you can redefine the field to a display type and use the new field name in an If command References For a complete sample script see page 17
16. t line items Does price X quantity amount Sa At least five status records per work order A control record for all 50 states References No group of transactions totals a negative amount 8 Do batch totals total of all batches As we go through these examples feel free to contribute your ideas and we ll see if Suprtool can handle them Ensuring Data Integrity using Suprtool Simple rule verification Do the debits equal the credits run suprtool pub robelle gt base fms gl1 5 reader gt get d transactions gt output null gt total trans amt gt xeq Totals SUN JAN 29 1995 2 56 PM TRANS AMT 81451 IN 6 OUT 6 CPU Sec 1 Wall Sec 1 4 Financial transactions are often processed in batches If there are For Techies programming bugs or if jobs abort a batch may become unbalanced by Each pass can total up losing a credit or debit transaction You can use the Total command to to 15 fields quickly check whether the sum of all the transactions equals zero By default the sum calculated by the Total command is displayed on the screen STDLIST You can also append the sum to the output file by using the following commands set squeeze off total file filename append References For a complete sample script see page 14 To locate an unbalanced batch with the Total option of the Duplicate command see the example on page 19 Ensuring Data Integrity using Suprtool Duplicate
17. variable to zero Then after each Suprtool task increment it by the value of the variable SuprtoolOutCount If at the end of the job the value of the new variable is still zero then the job s Stdlist can be deleted job dbcheck mgr sales db outclass 5 setvar errorcount 0 run suprtool pub robelle task 1 setvar errorcount errorcount suprtooloutcount task 2 setvar errorcount errorcount suprtooloutcount exit lif errorcount 0 then set stdlist delete endif leoj 17 For Techies Suprtool automatically generates the variable SuprtoolOutCount with the number of records selected References 17
Download Pdf Manuals
Related Search