1 / 55

ADABAS Extraction & Change Data Capture

ADABAS Extraction & Change Data Capture. Presented by. Chris S. Bradley. NatWorks, Inc. The Question…. Where Do You Need YOUR ADABAS Data Today?. DATA. DATA. Adabas. Data Warehouse Extraction. The Problems…. End-User Extraction Data Warehouse Extraction

lihua
Download Presentation

ADABAS Extraction & Change Data Capture

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADABAS Extraction & Change Data Capture Presented by Chris S. Bradley NatWorks, Inc.

  2. The Question… Where Do You Need YOUR ADABAS Data Today?

  3. DATA DATA Adabas Data Warehouse Extraction The Problems… • End-User Extraction • Data Warehouse Extraction • Web Publishing / Data Exchange Web End-User Extraction Web Publishing / Data Exchange

  4. The Message… A Software AG customer who has ADABAS & NATURAL already has the best tools to handle all requirements for Data Extraction & Change Data Capture

  5. ADABAS - Two Major Issues • How to Access ADABAS • ADABAS data structures

  6. Accessing ADABAS Data ADABAS ADABAS Direct Calls The OnlyWay to communicate directly to ADABAS is through Direct Calls

  7. Option #1 Imbedded Direct Calls Option #2 SQL / ODBC Option #3 Natural Option #4 ADABAS Utilities Option #5 Vendor Utilities Accessing ADABAS Data continued ADABAS ADABAS Direct Calls

  8. ADABAS Data Structures • All standard data formats are supported: • alphanumeric, binary, fixed point, floating point, packed decimal, unpacked decimal, ... • Supports two basic field types • Elementary fields • “recurring fields” (MUs) • Consecutive fields may be grouped • A group may be repeated - Periodic Groups (PEs) • Periodic Groups may contain one or more Multiple-Value Fields

  9. ADABAS Data Structures continued • ADABAS has unique data structures: • Multi-Valued Fields - “MUs” (array structure) • Periodic-Groups - “PEs” (table structure) • MUs in PEs (multi-dimensional structure) • ADABAS has some “difficult” data types • IBM STCK-based Date and TimeStamp fields • EBCDIC to ASCII • Packed Fields • Sign Byte Handling

  10. ADABAS Extraction Facts • FACT #1 NATURAL was developed by Software AG specifically to access ADABAS • FACT #2NATURAL represents the most widely used AND best understood solution for accessing ADABAS • FACT #3What NATURAL should not be used to do, ADABAS Utilities handle (with support from NATURAL) • FACT #4Extraction / CDC should be done in BATCH

  11. ADABAS Extraction - Conclusions • Natural allows flexible ADABAS Access • Natural easily handles all ADABAS data structures • Natural easily handles all ADABAS data types • Natural will always work with ADABAS Naturalfor maximum flexibility ADABAS Utilities for maximum performance

  12. Option #3 Natural Option #4 ADABAS Utilities Solving ADABAS Data Access ADABAS ADABAS Direct Calls • Embrace What Exists (ROI) • Best Understood Solution • High Performing Solution

  13. The Real Problem NO GENERATION • Generation is needed for: • Required Natural Objects • Required ADABAS Parameters • Required JCL / Script Processes

  14. NatQuery a Data Extraction Solution for ADABAS

  15. Web Target of Choice Tamino™ NatQuery RDBMS of Choice Leveraging Proven Technology ADABAS Extraction ADABAS™ ADASAV Backup DB2™ Predict™ ADABAS™ Utilities Natural™ XML Load Ready Data ETL Tool of Choice EXCEL ACCESS Desktop Tools XML/XSL Load Ready Data

  16. The NatQuery Extraction Solution NatQuery works by acting as an on-demand Natural Programmer. From a graphical user interface a User is enabled to: • Create Query Specifications • Generate Natural Data Extraction programs from these Specifications • Submit Generated Extract programs for execution • Remotely monitor execution status • Download Extracted Data • Load extracted data into MS Access, MS Excel or convert data into XML (with optional XSL)

  17. The NatQuery Extraction Solution Internally, NatQuery can be thought of as having three components: • Administrative Component • End-User Component • Generation Component NatQuery Administration Component End-user Component Generation Component

  18. The NatQuery Extraction Solution The Administrative Component is used by an Administrator to capture information that is specific to the platform, environment, and ADABAS data files that NatQuery will be used against. NatQuery Administration Component End-user Component Generation Component The Administration Component provides NatQuery with the ability to capture application-specific intelligence.

  19. The NatQuery Extraction Solution • The End-User Component • allows for the easy entry of extract specifications. • The Generation component • generates a Natural program from a specification. Administration Component End-user Component Generation Component Natural Program End-user Required Files / Fields User-specified Select Logic Optional Variables Desired Target

  20. DATA DATA DATA Adabas Other Environment The NatQuery Extraction Solution Server Environment • ExtractionWithNatural Natural Natural Program NatQuery Natural Program User Extract Specification Access Excel XML ... Workstation Environment

  21. DATA DATA DATA Adabas Other Environment The NatQuery Extraction Solution Server Environment • ExtractionWithADABAS UtilitiesAndNatural Natural ADACMP Utility Natural Program ADACMP Parameters NatQuery Natural Program User Extract Specification Workstation Environment

  22. The NatQuery Extraction Solution Requests Execute in “batch” • Better control of requests • Request execution can be easily scheduled • Impact on online production applications are controlled • More efficient execution environment over “online” • Significantly Less overhead • NatQuery handles Job Control Language (JCL) • Template approach provides easy set-up / maintenance • Dynamic substitution makes templates executable

  23. NatQuery Administration Overview 1) Provide Site-Specific Job Control Language (JCL) JCL • JCL Templates Provide: • Integration to Natural / ADABAS • Dynamic Process Customization Administration Component End-user Component Generation Component NatQuery

  24. NatQuery Administration Overview 2) Capture Natural Data Definition Modules (DDMs) JCL DDMs • DDMs are obtained: • Automatically via a User Request • Manually via an Import function Administration Component End-user Component Generation Component NatQuery

  25. NatQuery Administration Overview 3) Capture Expanded DDM Info JCL DDMs Descriptor Statistics File Relationships Occurrence Information File I/O Parameters Predict Metadata Administration Component Administration Information provides NatQuery with application-specific generation intelligence End-user Component Generation Component NatQuery

  26. NatQuery Generation Overview • The End-User Component allows for the easy entry of extract specifications. • The Generation Component converts an extract specification to an optimized Natural program. Workstation Environment Administration Component End-user Component Generation Component Natural Program End-user Required Files / Fields User-specified Select Logic Optional Variables Desired Target

  27. Server Integration Overview • File Transfer Protocol (FTP) Integration • Direct FTP into batch • Indirect FTP into batch • Just FTP (Manual Execution) • Manual Integration • IND$FILE (IBM) • Manual FTP • Other Methods... Mainframe Environment Automated Communication Is Achieved Using Standard FTP Workstation Environment NatQuery

  28. User Request Natural Program FTP Remote Log Request Output Direct FTP Integration Overview • User Builds QuerySpecification Server Environment • User Submits Request • Program is generated and imbedded into JCL / Script • Program and JCL / Script is FTP’ed to the Server • Local Log File is written JES (MVS), POWER (VSE) • Request Executes • Execution updates Remote Log, creates Output Batch User Request • User Retrieves Output • Output automatically FTP’ed to workstation Workstation Environment NatQuery Request Output Local Log

  29. User Request Natural Program FTP User Log Request Output In-Direct FTP Integration Overview • User Builds QuerySpecification Server Environment • User Submits Request • Program is generated and imbedded into JCL / Script • Program and JCL / Script is FTP’ed to the Server • Local Log File is written Batch Natural • Server Submits Request • Execution updates remote log, creates output NatQuery FTP Server • User Retrieves Output • Output automatically FTP’ed to workstation User Request User Request Workstation Environment NatQuery Request Output Local Log

  30. User Request Natural Program FTP User Log Request Output Just FTP Integration Overview • User Builds Query Specification Server Environment • User Submits Request • Program is generated and imbedded into JCL / Script • Program and JCL / Script is FTP’ed to the Server • Local Log File is written Batch Natural • User Manually Submits Request • Execution updates remote log, creates output • User Retrieves Output • Output automatically FTP’ed to workstation User Request User Request Workstation Environment NatQuery Request Output Local Log

  31. DATA DSX Files CFD Files SGT Files NatQuery Integration to ETL Tools NatQuery Generates Descriptions of Extract Layout • DSX Generation • DataStage Exchange file (DataStage proprietary format) • Allows for Full Integration of Predict Metadata • CFD Generation • COBOL File Definition (in copybook format) Workstation Environment NatQuery ETL tool Import Generation Component

  32. NatQuery Features • Optimized Access to Source File(s), Based on User-Entered Selection Criteria • Automatic determination and generation of best access method • Descriptors, Super-Descriptors, Sub-Descriptors… • Read Logical, Read Physical, Find, Get • Zero coding effort required • Full Sensitivity of Suppression • Autmomatic Support for Multi-Fetch (Pre-Fetch) • Automated Integration to Server (FTP) • Download DDMs (direct support for SYSTRANS utility) • Automatic Generation of required Descriptor Statistics • Automated Upload, Execute and Download of Results • Automated Extraction of PREDICT Meta Data

  33. NatQuery Features - continued... • Full handling of All ADABAS Field Types • Date, TimeStamp, Packed, Integer, Binary, ... • Data Conversion at Extract Level • Conversion of ADABAS formats to ASCII equivalents • Full Ability to handle Sign Byte for numeric fields • Full handling of All ADABAS “recurring” data • MUs, PEs, MUs in PEs • Administratively defined defaults and maximums • User over-ride of defaults (within allowed maximums) • Ability to “Flatten” or “Concatenate” at field level

  34. NatQuery Features - continued... “Flattening” a recurring data structure First-Name Last-Name Address-Line City ... One Source Record Suite 100 Two occurrences 454 South Main Street NatQuery built Index Two Extract Records Chris Bradley 1 454 South Main Street Northfield ... Chris Bradley 2 Suite 100 Northfield ...

  35. NatQuery Features - continued... “Concatenating” a recurring data structure First-Name Last-Name Address-Line City ... One Source Record Suite 100 Two occurrences 454 South Main Street One Extract Record Chris Bradley 454 South Main Street Suite 100 Northfield ...

  36. NatQuery Features - continued... • Direct Integration to ETL Tools • Automatic generation of interface files • DSX and / or CFD files • Automatic Linking of up to 5 “Primary” files • No User Knowledge Required • Support for Automatic “Look-up” files • Code-to-Text conversions, Administratively defined • Look-ups are “transparent” to the user

  37. NatQuery Features - continued... • Support “Full Extract” or “Intersection Set” • Multi-File Flexible Extraction • Abilitity to Define Variables • Redefines, Constants, Expressions, Compress and Dynamic (date-based and user input) • Direct Support for ADABAS utilities • ADACMP, ADASEL, ADACDC • Use of “Batch” Provides Controllable Extraction

  38. NatQuery Features - continued... • Full Manipulation of Query Specifications • Save, Save As, Delete • Query specifications stored with a long and short query description • User Specifiable Data Extract Limits • “Test” Extracts • Administratively Contolled User Data Limits • Ability to disallow Read Physical • Ability to set Record Limits • One Tool - Dual Use • End-User Extraction • Data Warehouse Extraction

  39. NatQuery Features - continued... • Minimal Mainframe Footprint • Just Natural, JCL and mainframe disk space • Existing ADABAS utilities (optional) • Extraction Capability to Any Data Source Natural Can Talk to • ADABAS, VSAM, DB2... • Integration to Desktop Tools • MS Access • MS Excel • XML (with optional XSL) • Tab Delimited or User-Specified Delimiters

  40. NatQuery Features - continued... • Integration to PREDICT • All Field-Level PREDICT Metadata is made available within NatQuery • Administrator can create their own • Ability to Trace I/O generation • review I/O generation process • Full Support of Native Security • Natural Security • ADABAS Security • Automatic Update Ability • Allows for centralized roll-out of new versions • ...

  41. NatCDC the Change Data Capture Solution for ADABAS NatWorks, Inc.

  42. NatCDC ADABAS PLOG Natural™ NatQuery RDBMS of Choice Leveraging Proven Technology ADABAS Change Data Capture & Transaction Auditing ADABAS™ ADABAS™ Utilities Load Ready Data ETL Tool of Choice EXCEL ACCESS Desktop Tools XML/XSL Load Ready Data

  43. PLOG 2 PLOG 1 Adabas The Source of ADABAS Changes ADABAS Protection Log (PLOG) • ADABAS’ transaction recovery mechanism • 100% data integrity, all transactions recorded • True “Point-in-Time” snapshot of ADABAS • Changed Data Available w/o ADABAS Access Mainframe Environment

  44. ADABAS CDC (Change Data Capture) Issues in accessing PLOG: • PLOG contains all transactions against all Files • PLOG is in compressed format • The same compression used by ADABAS • PLOG data is stored in Variable-Length records • Different from file to file and within same file • PLOG data requires “conversion” • EBCDIC to ASCII, date / time formats

  45. ADABAS CDC ADABAS utilities solve most PLOG issues: • ADASEL utility (ADABAS 6): • “Splits” PLOG transactions into separate files • One File for each requested ADABAS File • Handles Expanded Files • Decompresses PLOG records • ADACDC utility (ADABAS 7): • everything ADASEL does • direct delivery of Delta changes

  46. ADABAS CDC NatCDC / NatQuery solves remaining issues: • NatCDC converts variable-length to fixed-length • User-Specified number of MU and PE occurrences • PLOG Header converted • IBM STCK time, Expanded File ISNs are normalized • NatQuerygenerates all required objects • All Parameters and Programs

  47. Raw PLOG Data ADASEL / ADACDC utility supplied with ADABAS Disk System Sort Program Tape NatCDC utility (Single Optimized Natural Program) NatCDC Base Components NatCDC SORT ADASEL NatQuery Server Environment Workstation Environment

  48. One JCL Stream for each 20 files One JCL Stream for each file (or expanded file chain) Raw PLOG Data Raw FILE Data Fixed Length Data DWH CDC Data Disk Disk Disk Disk Tape Tape Tape Tape NatCDC Processing Overview Generated Processing Program NatCDC SORT ADASEL Generated Parameters Generated Parameters Generated Parameters NatQuery DDM Server Environment Workstation Environment

  49. NatCDC Features • 100% Data Integrity • All transactions handled, even Backouts • Simple Mainframe Installation • One Single Natural object program (NatCDC) • One Natural Program for each file • One JCL Stream per file • The Fastest and Most Trusted Decompression • SAG knows their own compression the best • Performance is Critical • CDC is a frequently occurring activity

  50. NatCDC Features • Variable-Length to Fixed Length conversion • Final Layout is User-Determined • Recurring Fields Padded or Truncated • Exception Reports Produced Automatically • Full Handling of all ADABAS data structures • MUs, PEs, and MUs in PEs • Automatic format translations: • EBCDIC to ASCII • Date and Timestamp • Sign handling of all numeric-based fields

More Related