ADASTRIP Presented by Treehouse Software ADASTRIP-7/01 ADASTRIP A Utility for Extracting Large Volumes of Data from ADABAS Quickly ADASTRIP-7/01 The Problem! How to extract large volumes of data as efficiently as possible from.
Download ReportTranscript ADASTRIP Presented by Treehouse Software ADASTRIP-7/01 ADASTRIP A Utility for Extracting Large Volumes of Data from ADABAS Quickly ADASTRIP-7/01 The Problem! How to extract large volumes of data as efficiently as possible from.
ADASTRIP Presented by Treehouse Software ADASTRIP-7/01 1 ADASTRIP A Utility for Extracting Large Volumes of Data from ADABAS Quickly ADASTRIP-7/01 2 The Problem! How to extract large volumes of data as efficiently as possible from our ADABAS transaction database(s) in order to populate the new Data Warehouse ADASTRIP-7/01 3 Special Requirements • Must have minimal or no impact on on-line database users • We already have batch window processing constraints • CPU efficient • Solution should be easy to implement and cost effective • Time limited, no programming resources ADASTRIP-7/01 4 What are our options? • Batch NATURAL program to extract the data and perform some extra processing • Unload, decompress, and process file using NATURAL or COBOL, etc. • COBOL direct call program • Any other options, maybe a utility ADASTRIP-7/01 5 Batch NATURAL Program • Too slow for millions of records being processed sequentially • Impacts on-line systems (our system is 24 hours a day, 7 days a week) • We have limitations on batch window • Can improve performance with PREFETCH or MULTI - FETCH ADASTRIP-7/01 6 Using ADABAS Utilities • Can be faster than Batch program reading database through MPM • Reading flat file from NATURAL or COBOL is easy to implement • Decompression may take lots of resources • May not want entire file(s) • Large work files may be needed • DBA needs to be involved in utility runs ADASTRIP-7/01 7 Direct Calls • May be slightly faster than NATURAL • COBOL and NATURAL batch are comparable in terms of performance • Batch window constraint, still too slow • COBOL direct calls, limited programming resources • What about the impact on the MPM? ADASTRIP-7/01 8 What if... • We had a utility that: – would extract the data simply, quickly, and efficiently – was parameterized – was easy to install and use – was very CPU efficient and fast to execute – didn’t impact on-line performance – produced multiple flat files for further processing with one pass of the database ADASTRIP-7/01 9 Introducing ADASTRIP A utility for extracting data quickly and efficiently from ADABAS databases, either direct or from the backup tapes. ADASTRIP-7/01 10 ADASTRIP Features • Fast, efficient extraction of multiple data files from one or more ADABAS files • One pass through backup tapes or database to extract data needed • Flat files can be input to pre-processing programs or loaded directly to the database • User-exits available for extra processing ADASTRIP-7/01 11 Solution • Implement ADASTRIP to extract data from the ADASAV backup tapes • All data can be extracted in one run • No impact on on-line databases • Most recent data can be merged from the latest QDUMP or PLOG • Extra processing of data can be done after extraction prior to load of database ADASTRIP-7/01 12 Example Production Run • Production job to populate a Relational MIS database • 100 extract files • Records extracted (as few as 75 records and as many as 5 million per file) total of approximately 15 million records extracted • Elapsed time approximately 60 minutes • CPU consumed approximately 51 seconds ADASTRIP-7/01 13 ADASTRIP Operations BACKUP PARMS USER EXIT ADASTRIP EXTRACT FILE 1 EXTRACT FILE 2 EXTRACT FILE N PLOG/QDUMP MORE... ADASTRIP-7/01 14 Uses for ADASTRIP • • • • • ADASTRIP-7/01 Extraction of the file structure (FDT) only Normalization of PE/MUs is possible Recovery of historical data from past backups Creation of test data Deferred processing of critical reports 15 Applications of ADASTRIP • • • • • • • ADASTRIP-7/01 Recovery of historical data Recovery of “lost” NATURAL programs Export to other systems or DBMSs Import data between ADABAS databases Fast processing avoids ADABAS I/O Deferred processing Replace ADAULD 16 Benefits of ADASTRIP • Moving sequential processing away from ADABAS reduces load • Faster processing, avoids ADABAS I/O • Deferred processing, reports can be run from data extracted from a backup • Processing on synchronized data (a backup has the data fixed at a point in time) ADASTRIP-7/01 17 Recovery of a NATURAL Program • ADASTRIP parameters needed: – NATSYS FILE 8 – NATSYS FIELDS LJ LK – NATSYS TEST A LJ(8).EQ.C’LOSTLIB’ – NATSYS TEST B LJ+8(8).EQ.C’PROG’ – NATSYS TEST C LJ+8(8).EQ.C’PROG2’ – NATSYS RULE A+B – NATSYS RULE A+C ADASTRIP-7/01 18 ADASTRIP V3 Changes/Enhancements • Available in MS DOS files on a 3.5” diskette or an e-mail attachment to facilitate fast transmission of new releases, upgrades, and zaps • Support for ADABAS V7.x, and backwards compatibility with ADABAS V6.x, and V5.x • New security exit, can be called prior to accessing data • Changes to index processing • Multi-tasking read allows true parallel processing of backup tapes ADASTRIP-7/01 19 ADASTRIP V3 Changes/Enhancements • Reductions in memory usage because of shared internal buffer pool • Increases in various limits • Enhanced selection criteria • Support for 191 PE occurrence's, 16k fields and null fields • JOINFILE utility allows files to be joined record for record on a common key • Enhanced user exit examples (e.g., data conversion to other formats) • Support for COBOL user exits ADASTRIP-7/01 20 Futures • • • • • ADASTRIP-7/01 Security exit to implement RACF/ACF rules On-line generation of JCL and parameters Further enhanced user exits Increases in internal limits New user exits 21