DL / 2IMS to DB2 MIGRATION

Technical Overview

Nov. 7, 2001 - Tele ConferenceThomas Beyer Carole Rice Stan Hoey

Lightyear Consulting Ltd. 1-877-LIGHTYR

dl2@lightyr.comwww.lightyr.com

L I G H T Y E A R

TECHNICAL OVERVIEW - AGENDA• CIRCLE GROUP and LIGHTYEAR CONSULTING OVERVIEW

• DL/2 OVERVIEW

• MIGRATION PROCESS OVERVIEW

• DB2 TABLE DESIGN + MAPPING / DATA MIGRATION

• SW INSTALLATION and SUPPORT

• POST-MIGRATION APPLICATION MAINTENANCE

• RUN TIME CONSIDERATIONS

• ImstC: IMS/DC REPLACEMENT WITH CICS - NEW SOLUTION

• CUSTOMER SUCCESSES , KEY CONTACTS AND NEXT STEPS

2

CIRCLE COMPUTER GROUP

• FORMED IN 1978

• SKILLS ORIENTED and TECHNICAL FOCUS

• EDUCATION, CONSULTANCY, SOFTWARE

• US OFFICE - mainly for CICS/DB2 Education

• S/W Distributors in USA, Scandinavia, Germany, Italy, France, Japan, South America, Israel, Australia

• SW Distributor in USA is Lightyear Consulting Ltd.

• IBM BEST Team Partner

• Ownership of all CICS courses run by IBM world-wide

3

Lightyear Consulting Ltd.

• Formed 1991 in W-Canada, expansion into USA in 1996

• IBM BESTeam Partner

• IBM Beacon Award Winner in 1999

• Winner of Best IBM OS/390 SW Distributor in 2000

• Skilled in and certified by IBM in IMS, DB2, CICS and OS/390

• Education, Consultancy, Software Development

• Offices in Calgary, Berkeley, Los Angeles, Phoenix

• Signed distributor agreement for DL/2 specifically to address IMS

customers that have stated their intent/desire to move off IMS

• In-house consultants and external contractor network to provide IMS,

DB2, JAVA, WebSphere or DL/2 specific skills and/or project

management

4

D L / 2 - What are the Benefits ?

Conventional Migration Effort From IMS To DB2

Analysis Re -programming effort Testing

Analysis DL/2 Testing

IMS To DB2 using DL/2

• SIGNIFICANT RISK ELIMINATION AND COST REDUCTION• Application code is not re-written• Application functionality is not compromised• Database by Database migration - phased-in approach• Use IMS and DB2 concurrently during migration• Use of modern relational approaches to write new programs ( ODBC,

JDBC, SQL, Java, tools, …) and change data structures• Relational skills (both DBA and programming) readily available in

marketplace 5

Selected D L / 2 Customer List - Worldwide

DL/2 in Production:============== AMC NJ Merchandising*Ball State University IN Education*Duomo Italy Insurance El Al Israel Air Freight*Garanti Bank Turkey Finance*GKB Switzerland Finance*HMLR UK Government*Houghton Mifflin MA PublishingITNET UK Government*McCormick Foods MD NBK Kuwait Banking*NCS IA Education ResourceSony Europe Belgium DistributionWelfide Japan Pharmaceuticals*Wright State University OH Education

7

DL/2 in Pilot============Clarica Insurance (Canada)

DL/2 in Conversion=============SBB Belgium GovernmentBandag IA Manufacturing & Services

* - IMS/DB License Eliminated

• ISPF MAPPING/GENERATION FACILITY> Map IMS fields to DB2 Columns> Generate DL/2 driver programs

• CALL INTERCEPTION FACILITY> Replaces IMS stub with DL/2 stub

• DBOS UTILITY

• DL/2 TEST AND COMPARE FEATURE

• DL/2 MAPPER

• SAMPLE COBOL, PL/1 AND ASSEMBLER EXITS SUPPLIED

D L / 2 COMPONENTS

8

DB2 Database DesignDB2 Database Design

Write/test exits

LOADDB2

MIGRATION PROCESS OVERVIEW

• REQUIRES APPL KNOWLEDGE AND DL/2 SKILL

MAP

TEST TEST

REMAP

PROTOTYPEPROTOTYPE• MIGRATION BY DBD (or DBD group)

9

AnalysisAnalysis

Test Appls

Appl Prog

Appl PSB

DL/1

DFSLI000

EXECUTION PROCESS FLOW - 1

BEFORE DL2 AFTER DL2 - PART 1 OF 2

IMS I/O Area

Appl Prog

Appl PSB

DL/1

SQL Driver

NO

DB2

YES

DL/2 Stub

DFSLI000

10

EXECUTION PROCESS FLOW - 2

FOLLOWING CONVERSION TO DL2 - PART 2 OF 2

Appl Prog

TRPMAIN SQL Driver

DB2

DL/2 Stub

DFSLI000

DB2 I/O Area

FBE IRD

APPLICATION I/O AREA

11

DL/2 generatedstatic SQL driver

PHYSICAL DB2 DATABASE DESIGN

• DB2 TABLE FOR EVERY DL/1 Segment

• SEGMENT CONCATENATED KEY BECOMES DB2 PRIMARY KEY

• DB2 REFERENTIAL INTEGRITY • Ensures Parent/child relationship is maintained • For Physical and Logical Parents

• COPYBOOK FIELD BECOMES DB2 COLUMN (typically) • REDEFINED SEGMENT LAYOUTS HANDLED THROUGH EXITS

• OBSOLETE (Filler ?) FIELDS DO NOT NEED TO BE MIGRATED TO DB2

12

PHYSICAL DB2 DATABASE DESIGN

13

SEGASEGA

SEGBSEGB

SEGCSEGC

SEGDSEGD

DB2 Table Primary Key Columns

DB2_TABA KCOL_A

DB2_TABB KCOL_A, KCOL_BDB2_TABD KCOL_A, KCOL_D

DB2_TABC KCOL_A, KCOL_B, KCOL_C

• NON-KEYED AND SEQ,M SEGMENTS MUST BE MADE UNIQUE IN DB2

D L / 2 MAPPING

IMS I/O AREA

SEQ SCH1 SCH2

DB2 I/O AREA

• FIELD to COLUMN Relationship using IMS DBD and Copybook

• Mapping performed through DL/2 ISPF Dialogues or by DL/2 MAPPER

• ONE TIME ACTIVITY

C1 C2 C3 C4 C5 C6 C7 C8

14

MAPPING PROCESS

15

1. CREATE DB2 DATABASE

2. MAP PHYSICAL SEGMENTS (not VLC)

3. GENERATE DL/2 DBD and PROCOPT=A SQL DRIVER

4 UNLOAD DATA FROM IMS

5. CONVERT UNLOAD FILE AND LOAD DB2 TABLES

6 REGISTER DBD TO DL/2

7. TEST USING DBOS> MAPPING> EXITS

8. MAP and TEST SECONDARY INDEXES

Test Applications

RE-ENGINEERING

• IMS PICTURE FIELDS ==> DB2 DATE> Typically 4 byte packed field X’0971229C’ becomes 1997-12-29

• OBSOLETE FIELDS

> Omit from DB2 Table (Fields defined as ‘FILLER’)

• MOVING FIELD POSITION

• CHANGING FIELD ATTRIBUTES > Extending field size> Numeric fields to DECIMAL, INTEGERER, SMALLINT

• ADDING ADDITIONAL COLUMNS> For future use by ‘new’ DB2 applications

16

• COPYBOOK REDEFINES

• PROBLEM IMS allows differing data types in the same record structure Handled by applications using masks.

• RESOLUTION DL2 provides granularity through Exits and additional DB2 tables

RE-ENGINEERING

ADDITIONAL RE-ENGINEERING THROUGH FBE & IRD EXITS

17

REDEFINED SEGMENT

18

LAYOUT=A

LAYOUT=B

LAYOUT=C

XCOMMON AREA

DB2TAB_COMMON KCOL_A, KCOL_B + Common area columnsDB2TAB_LAYOUT_A KCOL_A, KCOL_B + Layout=A columnsDB2TAB_LAYOUT_B KCOL_A, KCOL_B + Layout=B columnsDB2TAB_LAYOUT_C KCOL_A, KCOL_B + Layout=C columns

1. FIELD BUILD EXIT TO READ APPROPRIATE TABLE BASEDFIELD BUILD EXIT TO READ APPROPRIATE TABLE BASEDON FIELD XON FIELD X

2. IRD EXIT REQUIRED FOR UPDATE PROCESSING BASED ONIRD EXIT REQUIRED FOR UPDATE PROCESSING BASED ON FIELD X FIELD X

OTHER POTENTIAL SOLUTIONS

IMS SEGMENT

USER FIELD BUILD EXIT - POSSIBLE USE

• TO ENRICH DB2 DATA

Name Town City

DB2 Record Layout

IMS record

National Phone

• MANY OTHER POSSIBILITIES

Street Ctry

Name Town City InternationalPhone

Street Country name

Flag resolved by Appl pgm

Translated by FBE

nn

Mappedto

19

BIND LINK

SQL DRIVER GENERATION

STAGE I STAGE ll

DL/2

PSB

Prog logic;--EXEC SQL;--END EXEC;-End;

TRP_SRC

Precompile

DBRM ASSEMBLE

20

- SENSEGS- PROCOPTS

DATABASE and O/S FILE UTILITY - DBOS

• GENERALISED BATCH TOOL FOR DL/1 DBs> Extract, manipulate data > Supports all DL/1 DB Calls > Can create output PS files> Conditional field/record editing > Simple programming statements

• ESSENTIAL FOR DL/2 MIGRATION> Performs initial IMS DB unload> Converts DL/1 unload file into DB2 loadable format by

> Automatically maintaining the concatenated key structure> Applying User exits

> Can resolve data inconsistencies

• ESSENTIAL FOR TESTING DL/2

• SCAN UNLOAD FILE AND IDENTIFY DATA INCONSISTENCIES

21

RUN-TIME CONSIDERATIONS

• IMS APPLICATIONS NOW BECOME DB2 APPLICATIONS > Housekeeping Utilities in DB2> Local and Disaster Recovery Implications

• PACKAGE BIND / EXPLAIN

• TRPLIST / REGISTERED DATABASE IMPLICATIONS

• IMS DATABASES MUST BE DISABLED > Prevent Accidental Access

• DB2 PERFORMANCE / CAPACITY IMPLICATIONS

• CHANGES TO DB2 APPLICATION ENVIRONMENT> DL/2 Drivers May need to be Re-Genned

22

POST-MIGRATION APPL MAINTENANCE

• APPLICATION CHANGES TO DATABASE CALLS SHOULD BESQL-BASED

> New SQL Added to Application Programs> Existing DL/1 calls can be changed

• SIMPLIFIED DATABASE MAINTENANCE> Easy to increase Segment Length, but appl program changesstill required

• LINK-EDITS / MVS BINDER MUSTMUST USE DL2.LOAD INSTEAD OFRESLIB / SDFHLOAD

If IMS license is eliminatedIf IMS license is eliminated

• APPLICATION CHANGES MUST BE DB2-BASED• CHANGES TO IMS DBD and/or PSB NOTNOT ALLOWED• PROGRAMS USING IMS SERVICE CALLS MAY NEED TO BECHANGED

> XRST / Symbolic Checkpoint Support inn Future Release23

INSTALLING D L / 2• UNIQUE ‘RIGHT TO USE’ KEY FOR EACH CPU EXTENDEDANNUALLY

• ISPF TAILORING APPLICATION> Customised INSTALL AND SAMPLIB libraries

• NO SVCs, NO AUTHORISED LIBRARIES. NO IPL REQUIRED

• DL2 LOAD LIBRARY and DRIVERS LIBRARY;> STEPLIB in Batch and IMS/DC> DFHRPL in CICS

• BATCH JCL CHANGES FOR ACCESS TO DB2

• OPTIONALLY> Enable supplied Trace and Debugging Facilities

• FIXES DELIVERED BY EMAIL - USUALLY AMASPZAP FORMAT 24

FEATURES NOT SUPPORTED

• FASTPATH DBs using Calls not supported by IFF

• RULES HERE for non-keyed Segments

• HSSR

• Non-DL/1 calls to DL/1 DBs

• Independent AND IN SSAs

• Qn COMMAND CODE

• IRD calls to Secondary IXs as Standalone DBs

25

DL/2 in Sony - Europe

26

European-wide parts distribution centre in Londerzeel, Belgium

BUSINESS DRIVERS

, Improved Availability - target 24x7

, Provide cheaper end-user ad hoc reporting

, Offset loss of IMS programming skills/resourcesDATADATA

PROTECT APPLICATION INVESTMENT IN IMS LEGACY SYSTEMS

DL/2 in Sony - Europe

27

MVS ENVIRONMENT

OS/390 1.2 IMS 5.1CICS 4.1 DB2 4.1COBOL/2

400 IMS Programs 60 IMS Databases 38 Secondary Indexes120Gb EMC DASD

18000 new orders processed every day

DL/2 Project Team

1 x DB2 DBA with CICS

2 x Cobol Programmers

GKB - Switzerland

28

, Mature applications written in the 1970s

, Assembler & PL/1, MANTIS and Command-CICS

, Accounts stored in multiple currencies

DB2

@ Target database is DB2 for ad hoc queries and SQL-based programming andand@ enhance application to meet current business needs, including Euro support

GKB - Switzerland

Many different representationsof dates - lengths, packed, char

BIT fields

Different currencies

Application built-in security

EXTENSIVE DATA RE-ENGINEERING

- IMS DB2 -

DB2 DATE columns pluswindowing

CHAR or DEC columns

Currency flag stored/calc.on retrieval - including Euro

Encryption/decryption

29

DL/2 in Garanti Teknoloji

, Major bank HQ in Istanbul , Over 8000 branches throughout Turkey

, SYSPLEX environment with DB2 Data Sharing

, Critical ACCOUNTS Database still in IMS

, 10 million transaction against ACCOUNTS DB per day

, Unwilling to go IMS data sharing, unable to use SYSPLEX to balance IMS DB load.

30

DL/2 in Garanti Teknoloji

OS/390 IMS 5.1DB2 5.1 CICS TS 1.2PL/1 for MVS

, 15 CICSs in Production

, 24 x 7 availability of ATMs

, 2 processer Sysplex

Record Type A

Record Type B

Record Type C

ACCOUNTS DB

RE-RE-ENGINEERINGENGINEERING # 1 # 1IMS Key:x’0033302222222C’DB2 Key: Branch x’00333C’

Account x’2222222C’

RE-ENGINEERING # 2RE-ENGINEERING # 2

30

31

IMS/DC to CICS: ImstC

ImstC provides a reliable and cost effective means of migrating yourIMS/DC Application programs to run under CICS

3- Stage Process

Stage 1 - ImstC Audit and Impact Analysis provides

Stage 2 - ImstC automated conversion

Stage 3 - ImstC run-time features

32

IMS/DC to CICS: ImstC

Stage 1 - ImstC Audit and Impact Analysis provides:

• The number of programs to be migrated.• The language and nature of each program identified• Load module - CSECT cross reference.• Program - Macro & Copybook usage cross reference.• An inventory of missing components.

This analysis is used to generate:

• A detailed project plan for the conversion.• Indications as to where ImstC automated migration may be

deployed.• An estimate of conversion time, resource and skill level

requirements33

IMS/DC to CICS: ImstC

Stage 2 - ImstC automated conversion:

• Generates CICS compliant versions of Assembler, COBOLand PL/I programs, with minimal or no source modification

• Converts MFS screen definitions to BMS

34

IMS/DC to CICS: ImstC

Stage 3 - ImstC run-time features:

• Intercept all IO and alternate PCB calls.• Automatically convert incoming & outgoing terminal data

streams and control data -attribute bytes, cursor positioningetc.

• Manage the IMS transaction environment - e.g. messageformats, long transaction names and the SPA.

• Control terminal message queues and supports pagingfacilities (PA keys)

35

Key Contacts

• Overall Executive in charge at Lightyear for DL/2:

• Thomas Beyer• Tel: 403-609-3988• tbeyer@lightyr.com

• Account Manager that will follow up with you and will be your keycontact:

• Carole Rice• Tel: 714-438-5391• crice@lightyr.com

• If everything else fails: dl2@lightyr.com

36

Next Steps:

• Follow-up on some technical issues

• Pilot porting project to assess validity (billable)

• Mentoring/training onsite (or in the UK)

• Development of a migration project

37