XTST Prsentation Technique Franais

1
SQL-Transparency
XT-SOFT
2
Without SQL-Transparency
Batch and CICS programs access directly the VSAM
Files.
VSAM
SUPPLIER File
VSAM Request
VSAM Request
CICS TRANSACTION
VSAM Request
VSAM
BATCH PROGRAM
VSAM Request
CUSTOMER File
3
With SQL-Transparency
In our example, the CUSTOMER VSAM File has been
migrated by SQL-Transparency into an SQL table.
VSAM Requests of Batch and CICS programs,
accessing this file, will be converted into SQL
Requests by the SQL-Transparency Interface this
will be done, without any program modification.
VSAM
SUPPLIER File
VSAM Request
VSAM Request
CICS TRANSACTION
VSAM Request
SQL Request
SQL- Transparency
BATCH PROGRAM
VSAM Request
SQL Request
4
SQL-Transparency Processing in CICS Environment
The files to be managed by SQL-Transparency are
defined in a VSE phase (XTSTPRM), loaded in SVA.
For CICS programs, each VSAM request is compared
with XTSTPRM phase to check if this file is
defined in it. In our example, SUPPLIER File is
not defined in XTSTPRM phase, so the VSAM request
is not converted.
SVA
XTSTPRM
SQL/DS
CUSTOMER
CUSTOMER
INVOICE
INVOICE
...
CICS Program
EXEC CICS READ DATASET ( SUPPLIER') RIDFLD
('00100') INTO (REC1)
VSAM Request
VSAM
SUPPLIER
5
SQL-TransparencyProcessing in CICS Environment
In this second example, the CUSTOMER file is
defined in XTSTPRM. So, the VSAM Request is
converted into an SQL Request, by the
SQL-Transparency Interface.
SVA
XTSTPRM
SQL/DS
SQL Request
CUSTOMER
CUSTOMER
INVOICE
INVOICE
...
CICS Program
EXEC CICS READ DATASET ('CUSTOMER') RIDFLD
('010501') INTO (REC2)
VSAM
SUPPLIER
6
SQL-TransparencyProcessing for Batch Environment
For BATCH Programs, the File checking into
XTSTPRM Phase is done once at VSAM OPEN Time, in
order to switch either towards SQL or VSAM for
additional file requests of this program.
SVA
CUSTOMER
INVOICE
...
BATCH Program
OPEN CUSTOMER OPEN SUPPLIER
VSAM
SUPPLIER
7
SQL-Transparency
Supported VSAM Files
8
KSDS File
The KSDS VSAM File will be migrated into
relational table by SQL-Transparency.
KSDS /VSAM
SQL/DS
EMPLOYEE
STAFF
EMPLOYEENO SURNAME FIRSTNAME SALARY
HIRE_DATE
SQL-Transparency
EMPLOYEE
EMPLOYEENO SURNAME FIRSTNAME SALARY
HIRE_DATE
9
KSDS File (MULT type)
the KSDS VSAM File having records of various
types (MULT type) will be migrated by
SQL-Transparency into different tables ( one
table per record type).
SQL/DS
KSDS / VSAM
INVOICE
INVOICE-HEADER
INVOICE N INVOICE DATE CUSTOMER N
INVOICE
INVOICE-DETAILS
INVOICE N PRODUCT N QTY

SEQ
INVOICE N 00 INVOICE DATE CUSTOMER N
INVOICE N 01 PRODUCT N QTY
INVOICE N 02 PRODUCT N QTY
10
KSDS File (MULT Type)
In fact, in our example, we will get three
tables The first one (MULT type) giving the
location of each line in the other two tables
(SUBS type).
KSDS / VSAM
SQL/DS
INVOICE
INVOICE
INVOICE-DETAILS
INVOICE
INVOICE N TABLE NAME
0000000100 INVOICE-HEADER
0000000101 INVOICE-DETAILS
0000000102 INVOICE-DETAILS
INVOICE
HEADER RECORD
INVOICE-HEADER
INVOICE N SEQ INVOICE DATE CUSTOMER N
INVOICE N INVOICE DATE CUSTOMER N
00000001 00 .......
.......
0000000100 ....... .......
DETAILS RECORDS
INVOICE N SEQ PRODUCT N QTY

00000001 01 .......
....... 00000001 02 .......
.......
INVOICE-DETAILS
INVOICE N PRODUCT N QTY

0000000101 ....... .......
0000000102 .......
.......
11
KSDS File (NEXT type)
In our example, the VSAM File, containing more
than 255 fields, will be migrated into several
relational tables (one table cannot exceed 255
columns), the second table being the continuation
of the first one (NEXT type).
SQL/DS
KSDS / VSAM
CUSTOMER-END
CUSTOMER
CUSTOMER-FIRST
CUST-NO CREATION-DATE NAME1 NAME2
ADDR1 ADDR2 ...
CUSTOMER-END
CUST-NO DISCOUNT1 DISCOUNT2 BANK
AGENCY ACCOUNT-NO
SQL-Transparency
...
CUSTOMER-NUMBER CREATION-DATE NAME1 NAME2
ADDRESS1 ADDRESS2
...
DISCOUNT1 DISCOUNT2 BANK AGENCY
ACCOUNT-NUMBER
Field  256
12
ESDS File
The ESDS VSAM File will be migrated, by
SQL-Transparency, into one relational table,
the last column will contain the RBA.
SQL/DS
ESDS / VSAM
OPERATION
OPERATION
DATE TIME OPERATION VISA FILE
RBA
SQL-Transparency
OPERATION
DATE TIME OPERATION VISA
FILE
13
RRDS File
The RRDS VSAM File will be migrated into one
relational table by SQL-Transparency, the
last column will contain the RRN.
RRDS / VSAM
SQL/DS
MESSAGE
MESSAGE
MESSAGE
MESSAGE RRN
SQL-Transparency
MESSAGE
MESSAGE 1
Record1
MESSAGE 2
Record2
14
ALTERNATE File (AIX)
One Alternate VSAM File (AIX) will be migrated
into one Index defined on the relational table,
by SQL-Transparency.
KSDS / VSAM
SQL/DS
INDEX
STAFF
EMPLOYEE
INAME
SQL-Transparency
STAFF
EMPLOYEENO SURNAME FIRSTNAME SALARY
HIRE_DATE
AIXNAME
SURNAME
EMPLOYEE

EMPLOYEENO SURNAME FIRSTNAME SALARY
HIRE_DATE
15
SQL-Transparency
Implementation

• ) Data Migration

• ) SQL-Transparency definition

16

SQL-Transparency
First of all , we need to migrate VSAM Files into
SQL Tables, file after file. Then, we need to
define Transparency Interface for each supported
file. Now, programs, without any modification,
will access SQL migrated data .
1) XTST Migration Tool
VSAM Files
Relational Tables
Yesterday
2) XTST Transparency Interface
To-day
VSAM Program
17
SQL-TransparencyOn-Line Description of Files
This panel is the Main Menu of the On_Line
description of Files managed by
SQL-Transparency. To display this panel we need
to enter the XTST CICS transaction.
18
Main Menu
First, we need to type in the VSAM File (DDNAME )
and press PF5 (or option5).
19
File Description
On this panel the characteristics of the VSAM
File to migrate into DB2 Table have to be
defined.
20
File Description
In our example, the EMPLOYE Cluster will be
migrated into the STAFF Table. Press PF4Save,
and then PF10Columns.
21
Columns Description
Then, we need to define each table column (cname)
and each VSAM field from where the associated
column will be loaded (type, length, start).
22
Columns Description
Notice that the type and the length of columns
(Sqltype et Sqllen) have to be defined only if
different of the type and the length of VSAM
fields for example, HIRE_DATE.
23
Columns Description
Press PF4Save, before continuing with the next
description by PF11Switch.
24
Columns Description
On this panel, we can give a value, in the SQL
Forced field, that will be forced for the
associated column, in case of invalid or unknown
VSAM Data.
25
Columns DescriptionForced Value
In our example, if VSAM fields SALARY and
HIRE_DATE are invalid or unknown, the associated
SQL columns will be forced to NULL (?). Then
press PF11.
26
Columns DescriptionData Format
We can provide a Format for each column. In our
example the Format allows the conversion of the
VSAM date into SQL DATE. n means position N in
the VSAM field.
27
File Description
Let us save column description by PF4Save, just
before leaving the SQL-Transparency File
description by PF3End.
28
Different steps of Transparency Implementation
XTST
XTST_FILE XTST_MULT XTST_COLUMN
Description of VSAM Files and associated DB2
tables managed by SQL-Transparency
Generation of VSAM-SQL Data Transfer program
between VSAM and DB2 buffers (DEFINE Function)
Creation of DB2 Tables and views from the
previous CICS definition (CREATE Sub-Function)
Load of DB2 Tables from VSAM Files ( LOAD
Sub-Function)
JOB XTSTMIGR
MIGRATE Function
Creation of DB2 Indexes and Packages containing
static SQL requests for BATCH and CICS (GENERATE
Sub-Function)
JOB XTSTPRM
Definition of Files in SQL-Transparency
Table (XTSTPRM Phase)
Load of SQL-Transparency Programs into SVA
JOB XTSTSDL
29
Different steps ofTransparency Implementation
For new Files Definition of associated Programs
(File and Fileàà) in the PPT
Transparency Start
Transparency Start
XTON
JOB XTSTSTRT
FILE OPEN/CLOSE
XEMT
TEST
Transparency Stop
JOB XTSTSTOP
Transparency Stop
XTOF
BATCH
CICS
30
Yesterday Environment
Yesterday means just before the SQL-Transparency
Implementation. Programs accessed VSAM Files or
accessed, simultaneously, VSAM Files and SQL
Tables
31
To-day Environment
To-day means just after the migration of all
VSAM Files into SQL Tables and after the
SQL-Transparency Implementation. These programs,
without any modification, access now SQL tables
through SQL-Transparency Interface.
32
Tomorrow Environment
Tomorrow means just after the SQL-Transparency
Implementation. New programs written will access
directly relational data.