ADABAS Extraction Change Data Capture Chris S Bradley

ADABAS Extraction & Change Data Capture Chris S. Bradley Nat. Query & Nat. CDC Nat. Works, Inc. 1

The Question… Where Do You Need YOUR ADABAS Data Today? Nat. Query & Nat. CDC 2

The Problems… l l l End-User Extraction Data Warehouse Extraction Web Publishing / Data Exchange Adabas DATA End-User Extraction Nat. Query & Nat. CDC DATA Data Warehouse Extraction Web Publishing / Data Exchange 3

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 Nat. Query & Nat. CDC 4

ADABAS - Two Major Issues l l Nat. Query & Nat. CDC How to Access ADABAS data structures 5

Accessing ADABAS Data ADABAS Direct Calls The Only Way to communicate directly to ADABAS is through Direct Calls Nat. Query & Nat. CDC 6

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

l ADABAS Data Structures All standard data formats are supported: ä l Supports two basic field types ä ä l alphanumeric, binary, fixed point, floating point, packed decimal, unpacked decimal, . . . 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 Nat. Query & Nat. CDC 8

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

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

ADABAS Extraction - Conclusions l l 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 Natural for maximum flexibility ADABAS Utilities for maximum performance Nat. Query & Nat. CDC 11

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

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

a Data Extraction Solution for ADABAS

Leveraging Proven Technology ADABAS Extraction ADABAS™ ADASAV Backup DB 2™ Predict™ Natural™ ADABAS™ Utilities Target of Choice XML Tamino™ Load Ready Data RDBMS of Choice EXCEL Nat. Query & Nat. CDC ACCESS Desktop Tools XML/XSL Load Ready Data ETL Tool of Choice 15

The Nat. Query Extraction Solution Nat. Query works by acting as an on-demand Natural Programmer. From a graphical user interface a User is enabled to: l l l 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) Nat. Query & Nat. CDC 16

The Nat. Query Extraction Solution Internally, Nat. Query can be thought of as having three components: l l l Administrative Component End-User Component Generation Component Nat. Query Administration Component End-user Component Nat. Query & Nat. CDC Generation Component 17

The Nat. Query 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 Nat. Query will be used against. Nat. Query Administration Component End-user Component Generation Component The Administration Component provides Nat. Query with the ability to capture application-specific intelligence. Nat. Query & Nat. CDC 18

The Nat. Query Extraction Solution l The End-User Component ä l 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 Nat. Query & Nat. CDC User-specified Select Logic Optional Variables Desired Target 19

The Nat. Query Extraction Solution Server Environment l Extraction With Natural Adabas Natural Program User Extract Specification Nat. Query & Nat. CDC Natural Program Nat. Query Workstation Environment DATA Access Excel XML . . . DATA Other Environment 20

The Nat. Query Extraction Solution Server Environment l Extraction With ADABAS Utilities And Natural User Extract Specification Natural ADACMP Utility Natural Program ADACMP Parameters Nat. Query Workstation Environment Nat. Query & Nat. CDC Adabas DATA Natural Program DATA Other Environment 21

The Nat. Query Extraction Solution Requests Execute in “batch” l l l 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 Nat. Query handles Job Control Language (JCL) ä Template approach provides easy set-up / maintenance ä Dynamic substitution makes templates executable Nat. Query & Nat. CDC 22

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

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

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

Nat. Query Generation Overview l The End-User Component allows for the easy entry of extract specifications. l 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 User-specified Files / Fields Select Logic Nat. Query & Nat. CDC Optional Variables Desired Target 26

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

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

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

Just FTP Integration Overview l l User Builds Query Specification User Submits Request ä ä ä l 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 ä l Server Environment Execution updates remote log, creates output User Retrieves Output ä Output automatically FTP’ed to workstation Workstation Environment Nat. Query Local Log Nat. Query & Nat. CDC User Request Natural Program Request Output FTP User Request User Log Request Output 30

Nat. Query Integration to ETL Tools Nat. Query Generates Descriptions of Extract Layout l DSX Generation ä ä l Data. Stage Exchange file (Data. Stage proprietary format) Allows for Full Integration of Predict Metadata CFD Generation ä DATA COBOL File Definition (in copybook format) Workstation Environment Nat. Query DSX Files CFD Files Generation Component Nat. Query & Nat. CDC SGT Files ETL tool Import 31

Nat. Query Features l Optimized Access to Source File(s), Based on User. Entered Selection Criteria ä Automatic determination and generation of best access method – – ä l 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 Nat. Query & Nat. CDC 32

Nat. Query Features l Full handling of All ADABAS Field Types ä l Date, Time. Stamp, Packed, Integer, Binary, . . . Data Conversion at Extract Level ä ä l continued. . . 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 Nat. Query & Nat. CDC 33

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

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

Nat. Query Features l continued. . . Direct Integration to ETL Tools ä Automatic generation of interface files – DSX and / or CFD files l 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 ä l Nat. Query & Nat. CDC 36

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

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

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

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

the Change Data Capture Solution for ADABAS Nat. Works, Inc.

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

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

ADABAS CDC (Change Data Capture) Issues in accessing PLOG: l l 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 Nat. Query & Nat. CDC 44

ADABAS CDC ADABAS utilities solve most PLOG issues: l 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 ä l Nat. Query & Nat. CDC 45

ADABAS CDC Nat. CDC / Nat. Query solves remaining issues: l Nat. CDC 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 l Nat. Query generates all required objects ä All Parameters and Programs Nat. Query & Nat. CDC 46

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

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

Nat. CDC Features l l l 100% Data Integrity ä All transactions handled, even Backouts Simple Mainframe Installation ä One Single Natural object program (Nat. CDC) ä 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 Nat. Query & Nat. CDC 49

Nat. CDC Features l Variable-Length to Fixed Length conversion ä Final Layout is User-Determined ä Recurring Fields Padded or Truncated – Exception Reports Produced Automatically l 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 Nat. Query & Nat. CDC 50

Nat. CDC Features l Full Support for Expanded Files ä l Full Generation of all Required Objects ä ä ä l Physical to Logical ISN conversion All Parameters All Programs All JCL Data is supplied with Standard Header ä Nat. Query & Nat. CDC Transaction Date, Time, ISN, Seq#, . . . 51

Nat. CDC Features l l l Field Selection Options ä C* values available as data ä Fields may be selectively omitted Integration to ETL Tools ä CFD generation ä “DSX” generation (Ascential Data. Stage) Time Differential Handling Options ä Automatic ä Manual Nat. Query & Nat. CDC 52

Nat. CDC Features l Data Output Options ä Logical Last – Single Record flagged as Store, Update or Delete ä Logical First and Last – One or two records flagged as Before or After All Extensive Reporting Options ä Occurrence Exception Processing ä Store, Update, Delete Counts ä Total Before and After images ä. . . ä l Nat. Query & Nat. CDC 53

Nat. CDC Benefits l Cost Effective ä ä l One Tool - Dual Use ä ä l Uses vendor supplied utility Uses Natural Data Warehouse Change Data Capture (CDC) End-User Extraction . . . Nat. Query & Nat. CDC 54

Nat. Query & Nat. CDC 55