ADABAS

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
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 Only Way to communicate directly to ADABAS is
through Direct Calls
7
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

Natural for maximum flexibility
ADABAS Utilities for maximum performance
12
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
Leveraging Proven Technology
ADABAS Extraction
ADABAS
ADASAV Backup
DB2
ADABAS Utilities
Predict
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
Desired Target
User-specified Select Logic
Optional Variables
20
The NatQuery Extraction Solution
Server Environment

• ExtractionWithNatural

Natural
Natural Program
Natural Program
NatQuery
User Extract Specification
Access
Excel
XML
...
Workstation Environment
21
The NatQuery Extraction Solution
Server Environment

• ExtractionWithADABAS UtilitiesAndNatural

Natural
Natural Program
ADACMP Utility
ADACMP Parameters
Natural Program
NatQuery
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
Desired Target
User-specified Select Logic
Optional Variables
27
Server Integration Overview

• File Transfer Protocol (FTP) Integration
• Direct FTP into batch
• Indirect FTP into batch
• Just FTP (Manual Execution)
• Manual Integration
• INDFILE (IBM)
• Manual FTP
• Other Methods...

Mainframe Environment
Automated Communication Is Achieved
Using Standard FTP
Workstation Environment
NatQuery
28
Direct 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 FTPed 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 FTPed to workstation

Workstation Environment
NatQuery
Request Output
Local Log
29
In-Direct 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 FTPed 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 FTPed to workstation

User Request
User Request
Workstation Environment
NatQuery
Request Output
Local Log
30
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 FTPed 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 FTPed to workstation

User Request
User Request
Workstation Environment
NatQuery
Request Output
Local Log
31
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
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
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
• NatQuery generates all required objects
• All Parameters and Programs

47
NatCDC Base Components
NatCDC
SORT
ADASEL
NatQuery
Server Environment
Workstation Environment
48
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

51
NatCDC Features

• Full Support for Expanded Files
• Physical to Logical ISN conversion
• Full Generation of all Required Objects
• All Parameters
• All Programs
• All JCL
• Data is supplied with Standard Header
• Transaction Date, Time, ISN, Seq, ...

52
NatCDC Features

• Field Selection Options
• C values available as data
• Fields may be selectively omitted
• Integration to ETL Tools
• CFD generation
• DSX generation (Ascential DataStage)
• Time Differential Handling Options
• Automatic
• Manual

53
NatCDC Features

• 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
• ...

54
NatCDC Benefits

• Cost Effective
• Uses vendor supplied utility
• Uses Natural
• One Tool - Dual Use
• Data Warehouse Change Data Capture (CDC)
• End-User Extraction
• ...

55
NatQuery
and
NatCDC
Simple ideas with enormous potential.
www.treehouse.com tsi_at_treehouse.com