Transcript No Slide Title

ADABAS
Extraction
&
Change Data Capture
Presented by
Chris S. Bradley
NatWorks, Inc.
The Question…
Where Do You Need
YOUR
ADABAS
Data Today?
TSI 4/05
2
The Problems…



End-User Extraction
Data Warehouse
Extraction
Web Publishing / Data
Exchange
Adabas
DATA
End-User
Extraction
TSI 4/05
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
TSI 4/05
4
ADABAS - Two Major Issues
How to Access ADABAS
 ADABAS data structures

TSI 4/05
5
Accessing ADABAS Data
ADABAS
ADABAS Direct Calls
The Only Way to communicate directly to ADABAS is through
Direct Calls
TSI 4/05
6
Accessing ADABAS Data continued
ADABAS
ADABAS Direct Calls
Option #1
Option #2
Option #3
Option #4
Option #5
Imbedded
SQL / ODBC
Natural
ADABAS
Vendor
Utilities
Utilities
Direct Calls
TSI 4/05
7
ADABAS Data Structures

All standard data formats are supported:


Supports two basic field types



Elementary fields
“recurring fields” (MUs)
Consecutive fields may be grouped


TSI 4/05
alphanumeric, binary, fixed point, floating point,
packed decimal, unpacked decimal, ...
A group may be repeated - Periodic Groups (PEs)
Periodic Groups may contain one or more MultipleValue Fields
8
ADABAS Data Structures continued

ADABAS has unique data structures:
Fields - “MUs” (array structure)
 Periodic-Groups - “PEs” (table structure)
 MUs in PEs (multi-dimensional structure)
 Multi-Valued

ADABAS has some “difficult” data types
 IBM

STCK-based Date and TimeStamp fields
EBCDIC to ASCII
 Packed
Fields
 Sign Byte Handling
TSI 4/05
9
ADABAS Extraction Facts




TSI 4/05
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
10
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
TSI 4/05
11
Solving ADABAS Data Access
ADABAS
ADABAS Direct Calls
 Embrace What Exists (ROI)
 Best Understood Solution
 High Performing Solution
TSI 4/05
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
TSI 4/05
13
a
Data Extraction Solution
for
ADABAS
Leveraging Proven Technology
ADABAS Extraction
ADABAS™
ADASAV
Backup
DB2™
Predict™
Natural™
ADABAS™
Utilities
Target
of Choice
XML
Tamino™
Load Ready
Data
RDBMS
of Choice
EXCEL
TSI 4/05
ACCESS
Desktop
Tools
XML/XSL
Load Ready
Data
ETL Tool
of Choice
15
The NatQuery Extraction Solution
NatQuery works by acting as an on-demand Natural
Programmer.
From a graphical user interface a User is enabled to:






TSI 4/05
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)
16
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
TSI 4/05
Generation
Component
17
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.
TSI 4/05
18
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
TSI 4/05
User-specified
Select Logic
Optional
Variables
Desired
Target
19
The NatQuery Extraction Solution
Server Environment

Extraction
With
Natural
User
Extract
Specification
Natural
Natural
Program
DATA
Natural
Program
NatQuery
Workstation Environment
TSI 4/05
Adabas
DATA
Access
Excel
XML
...
DATA
Other Environment
20
The NatQuery Extraction Solution
Server Environment

Extraction
With
ADABAS Utilities
And
Natural
User
Extract
Specification
Natural
ADACMP
Utility
Natural
Program
ADACMP
Parameters
NatQuery
Workstation Environment
TSI 4/05
Adabas
DATA
Natural
Program
DATA
DATA
Other Environment
21
The NatQuery Extraction Solution
Requests Execute in “batch”

Better control of requests



More efficient execution environment over
“online”


Significantly Less overhead
NatQuery handles Job Control Language (JCL)


TSI 4/05
Request execution can be easily scheduled
Impact on online production applications are controlled
Template approach provides easy set-up /
maintenance
Dynamic substitution makes templates executable
22
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
TSI 4/05
23
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
TSI 4/05
24
NatQuery Administration Overview
3) Capture Expanded DDM Info
JCL
DDMs
Descriptor
Statistics
File
Occurrence
Relationships Information
Administration Component
End-user
Component
NatQuery
TSI 4/05
Generation
Component
File I/O
Parameters
Predict
Metadata
Administration Information
provides NatQuery with
application-specific
generation intelligence
25
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
User-specified
Files / Fields Select Logic
TSI 4/05
Optional
Variables
Desired
Target
26
Server Integration Overview

File Transfer Protocol (FTP) Integration




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
NatQuery
TSI 4/05
Automated
Communication
Is Achieved Using
Standard FTP
27
Direct FTP Integration Overview


User Builds Query Specification
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
Request Executes


Server Environment
JES (MVS), POWER (VSE)
Batch
Execution updates Remote Log, creates
Output
User
Request
User Retrieves Output
Output automatically FTP’ed to workstation
User
Workstation Environment
Request
Natural
NatQuery
Program

FTP
Local
Log
TSI 4/05
Request
Output
Remote
Log
Request
Output
28
In-Direct FTP Integration Overview


User Builds Query Specification
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


Server Environment
Execution updates remote log, creates output
User Retrieves Output

Output automatically FTP’ed to workstation
Workstation Environment
NatQuery
Local
Log
TSI 4/05
NatQuery
FTP Server
User
Request
Natural
Program
Request
Output
FTP
User
Request
User
Request
User
Log
Request
Output
29
Just FTP Integration Overview


User Builds Query Specification
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


Server Environment
Execution updates remote log, creates output
User Retrieves Output

Output automatically FTP’ed to workstation
Workstation Environment
NatQuery
Local
Log
TSI 4/05
User
Request
Natural
Program
Request
Output
FTP
User
Request
User
Request
User
Log
Request
Output
30
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

DATA
COBOL File Definition (in copybook format)
Workstation Environment
NatQuery
DSX
Files
CFD
Files
Generation
Component
TSI 4/05
SGT
Files
ETL tool
Import
31
NatQuery Features

Optimized Access to Source File(s), Based on UserEntered Selection Criteria

Automatic determination and generation of best access method
–
–
–
–


Autmomatic Support for Multi-Fetch (Pre-Fetch)
Automated Integration to Server (FTP)




TSI 4/05
Descriptors, Super-Descriptors, Sub-Descriptors…
Read Logical, Read Physical, Find, Get
Zero coding effort required
Full Sensitivity of Suppression
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
32
NatQuery Features 
Full handling of All ADABAS Field Types



Conversion of ADABAS formats to ASCII equivalents
Full Ability to handle Sign Byte for numeric fields
Full handling of All ADABAS “recurring” data




TSI 4/05
Date, TimeStamp, Packed, Integer, Binary, ...
Data Conversion at Extract Level


continued...
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
33
NatQuery 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
NatQuery built Index
Two
Extract
Records
TSI 4/05
Chris Bradley 1 454 South Main Street Northfield ...
Chris Bradley 2 Suite 100
Northfield ...
34
NatQuery Features -
continued...
“Concatenating” a recurring data structure
One
Source
Record
One
Extract
Record
TSI 4/05
First-Name Last-Name Address-Line City ...
Suite 100
454 South Main Street
Chris Bradley 454 South Main Street Suite 100
Two occurrences
Northfield ...
35
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


TSI 4/05
36
NatQuery Features 
Support “Full Extract” or “Intersection Set”


Redefines, Constants, Expressions, Compress and
Dynamic (date-based and user input)
Direct Support for ADABAS utilities


Multi-File Flexible Extraction
Abilitity to Define Variables


continued...
ADACMP, ADASEL, ADACDC
Use of “Batch” Provides Controllable
Extraction
TSI 4/05
37
NatQuery Features 
Full Manipulation of Query Specifications




Ability to disallow Read Physical
Ability to set Record Limits
One Tool - Dual Use


TSI 4/05
“Test” Extracts
Administratively Contolled User Data Limits


Save, Save As, Delete
Query specifications stored with a long and short query
description
User Specifiable Data Extract Limits


continued...
End-User Extraction
Data Warehouse Extraction
38
NatQuery Features 
Minimal Mainframe Footprint



ADABAS, VSAM, DB2...
Integration to Desktop Tools




TSI 4/05
Just Natural, JCL and mainframe disk space
Existing ADABAS utilities (optional)
Extraction Capability to Any Data Source
Natural Can Talk to


continued...
MS Access
MS Excel
XML (with optional XSL)
Tab Delimited or User-Specified Delimiters
39
NatQuery Features 
Integration to PREDICT




TSI 4/05
Natural Security
ADABAS Security
Automatic Update Ability


review I/O generation process
Full Support of Native Security


All Field-Level PREDICT Metadata is made available within
NatQuery
Administrator can create their own
Ability to Trace I/O generation


continued...
Allows for centralized roll-out of new versions
...
40
the
Change Data Capture Solution
for
ADABAS
NatWorks, Inc.
Leveraging Proven Technology
ADABAS Change Data Capture & Transaction Auditing
ADABAS™
ADABAS
PLOG
Natural™
ADABAS™
Utilities
Load Ready
Data
RDBMS
of Choice
EXCEL
TSI 4/05
ACCESS
Desktop
Tools
XML/XSL
Load Ready
Data
ETL Tool
of Choice
42
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
PLOG 2
Adabas
TSI 4/05
PLOG 1
43
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
TSI 4/05
44
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


TSI 4/05
45
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

TSI 4/05
All Parameters and Programs
46
NatCDC Base Components
Raw
PLOG
Data
ADASEL / ADACDC utility supplied with ADABAS
ADASEL
NatCDC
SORT
Disk
System Sort Program
Tape
NatCDC utility (Single Optimized Natural Program)
NatQuery
Workstation Environment
TSI 4/05
Server Environment
47
NatCDC 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
NatCDC
Disk
Generated
Parameters
Tape
Fixed
Length
Data
SORT
Disk
Generated
Parameters
Tape
Generated
Parameters
Generated
Processing
Program
DWH
CDC
Data
Disk
Tape
NatQuery
DDM
Workstation Environment
TSI 4/05
Server Environment
48
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
TSI 4/05
49
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
TSI 4/05
50
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
TSI 4/05
Date, Time, ISN, Seq#, ...
51
NatCDC Features



TSI 4/05
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
52
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
 ...


TSI 4/05
53
NatCDC Benefits

Cost Effective
 Uses
vendor supplied utility
 Uses Natural

One Tool - Dual Use
 Data
Warehouse Change Data Capture
(CDC)
 End-User Extraction

TSI 4/05
...
54
and
Simple ideas with enormous potential.
www.treehouse.com | [email protected]