Modernizing the CA Datacom Data – SQL Access Part II

World®
’16
Modernizing the CA Datacom®
Data – SQL Access Part II
Kevin Shuma, VP Product Management
CA Technologies
MFX95SB
MAINFRAME AND WORKLOAD AUTOMATION

2 © 2016 CA. ALL RIGHTS RESERVED.@CAWORLD #CAWORLD
© 2016 CA. All rights reserved. All trademarks referenced herein belong to their respective companies.
The content provided in this CA World 2016 presentation is intended for informational purposes only and does not form any type of
warranty. The information provided by a CA partner and/or CA customer has not been reviewed for accuracy by CA.
For Informational Purposes Only
Terms of this Presentation

Abstract
This is a two part session that provides an overview of steps
necessary to take make CA Datacom® available for modern
applications via SQL and Server.
For many shops their CA Datacom®/DB environment was
created before the implementation of SQL. This session will
walk through the steps necessary to make all the their data SQL
accessible using CA Datacom® SQL. From there we add the
ODBC and JDBC support provided by CA Datacom® Server. Once
completed you will have data that is fully accessible to modern
applications development environments.
Kevin Shuma
CA Technologies
VP, Product
Management

Agenda
WHAT IS A DATACOM VIEW
IMPLEMENTING DATACOM VIEWS
USING DBSQLPR
SECURITY CONSIDERATIONS
SUMMARY
1
2
3
4
5

What is a Datacom VIEW?
§ Extension to SQL for Datacom only
– Supports all current SQL access
– Adds SQL access for
§ Compound columns
§ Single-dimension arrays
§ Redefined columns
§ Simple “low-impact” method to utilize legacy data structures
with SQL
Datacom SQL Extension

SQL Column Report
SQL ACCESS STD. – From Part I
STD SQL access may be limited for
certain legacy structures

SQL Column Report
SQL ACCESS – DATACOM VIEW
STD SQL access may be limited for
certain legacy structures
SQL ACCESS - DATACOM
indicates if column is accessible by
a DATACOM VIEW
Columns that were not accessible
using standard SQL may be fully
accessible using a DATACOM VIEW

Implementing a DATACOM VIEW
§ Syntax the same as standard CREATE VIEW with one change
– CREATE DATACOM VIEW
§ Group columns are available as character columns
§ Redefined columns available as defined
§ Repeating columns available using
column_sqlname[nn]
i.e. part_number[1], part_number[2], part_number[3]
Using Standard SQL DDL Syntax With Datacom Extensions

Generating a DATACOM VIEW syntax
§ Use either DDUTILTY or DDUPDATE batch utilities
– Input transactions:
+UTL LIBRARY,SRC
+UTL LANGUAGE,SQL
+UTL MODE, DATACOM-VIEW
+UTL COPY,TABLE,POLICY-HISTORY(PROD),POHDVIEW
§ Output
– SYSPRINT – report showing the created view
– SYSPUNCH – output dataset with created view syntax
Using Datadictionary Utility to Generate a View Model to Use

./ ADD NAME=POHDVIEW
CREATE DATACOM VIEW
SYSUSR.POLICY_INFO_VIEW
(
POLICY_NUMBER,
DIVISION_NUMBER,
AGENT_ID,
OWNER_ID,
POLICY_TYPE,
TRANSACTION_CODE,
TRANSACTION_TYPE,
TRANSACTION_SEQ,
PAYMENT_SCHEDULE,
-- START ARRAY: SCHEDULE_PAYAMT_MONTH
SCHEDULE_PAYAMT_MONTH_01,
. . . .
-- END ARRAY: SCHEDULE_PAYAMT_MONTH
SCHEDULE_COMMENT,
Generates entries for each of
the entries in a repeating
group
Datacom View Generator Output - SYSPUNCH
Generates an entry for each
group column

Datacom View Generator Output – SYSPUNCH (Cont’d)
-- START REDEFINE: PAYMENT_RECORD
-- REDEFINES: SCHEDULE-PAYMENT
PAYMENT_RECORD,
PAYMENT_DATE,
PAYMENT_REFERENCE,
PAYMENT_AMOUNT,
PAYMENT_COMMENT,
-- START REDEFINE: NOTES_RECORD
-- REDEFINES: PAYMENT-RECORD
NOTES_RECORD,
NOTES_DATE,
NOTES_COMMENT,
POLICY_FILLER
Create on set of entry
for each definition
Payment row definition
Transaction-type “PAY”
Payment row definition
Transaction-type “NOT”
For redefinitions you must choose one row definition per view!!

DATACOM VIEW – POH_SCHEDULE
./ ADD NAME=POHSCHED
CREATE DATACOM VIEW
(
POLICY_NUMBER,
DIVISION_NUMBER,
AGENT_ID,
OWNER_ID,
POLICY_TYPE,
TRANSACTION_CODE,
TRANSACTION_TYPE,
TRANSACTION_SEQ,
PAYMENT_SCHEDULE,
SCHEDULE_PAYAMT_MONTH_JAN,
SCHEDULE_PAYAMT_MONTH_FEB,
.. . .
SCHEDULE_PAYAMT_MONTH_DEC,
SCHEDULE_COMMENT
)
AS
SELECT
POLICY_NUMBER,
DIVISION_NUMBER,
AGENT_ID,
OWNER_ID,
POLICY_TYPE,
TRANSACTION_CODE,
TRANSACTION_TYPE,
TRANSACTION_SEQ,
PAYMENT_SCHEDULE,
SCHEDULE_PAYAMT_MONTH[01],
. . . .
SCHEDULE_COMMENT
FROM
SYSUSR.POLICY_INFO
WHERE TRANSACTION_TYPE = ‘SCH’
WITH CHECK OPTION;
1st
definition
Repeating
group

DATACOM VIEW – POH_PAYMENT
CREATE DATACOM VIEW
(
POLICY_NUMBER,
DIVISION_NUMBER,
AGENT_ID,
OWNER_ID,
POLICY_TYPE,
TRANSACTION_CODE,
TRANSACTION_TYPE,
TRANSACTION_SEQ,
PAYMENT_RECORD,
PAYMENT_DATE,
PAYMENT_REFERENCE,
PAYMENT_AMOUNT,
PAYMENT_COMMENT
)
AS
SELECT
POLICY_NUMBER,
DIVISION_NUMBER,
AGENT_ID,
OWNER_ID,
POLICY_TYPE,
TRANSACTION_CODE,
TRANSACTION_TYPE,
TRANSACTION_SEQ,
PAYMENT_RECORD,
PAYMENT_DATE,
PAYMENT_REFERENCE,
PAYMENT_AMOUNT,
PAYMENT_COMMENT
FROM
SYSUSR.POLICY_INFO
WHERE TRANSACTION_TYPE = ‘PAY’
WITH CHECK OPTION.
2nd
definition
Series of
columns

DATACOM VIEW – POH_NOTES
CREATE DATACOM VIEW
(
POLICY_NUMBER,
DIVISION_NUMBER,
AGENT_ID,
OWNER_ID,
POLICY_TYPE,
TRANSACTION_CODE,
TRANSACTION_TYPE,
TRANSACTION_SEQ,
NOTES_RECORD,
NOTES_DATE,
NOTES_COMMENT,
)
AS
SELECT
POLICY_NUMBER,
DIVISION_NUMBER,
AGENT_ID,
OWNER_ID,
POLICY_TYPE,
TRANSACTION_CODE,
TRANSACTION_TYPE,
TRANSACTION_SEQ,
NOTES_RECORD,
NOTES_DATE,
NOTES_COMMENT
FROM
SYSUSR.POLICY_INFO
WHERE TRANSACTION_TYPE = ‘NOT’
WITH CHECK OPTION.
3rd
definition
Set of
different
columns

Datacom View Best Practices
§ When redefinitions exist
– Always use a where clause to “match” rows to definitions
– Always use a WITH CHECK OPTION to protect “updates”
§ When group columns referenced
– Data is treated as character
– Only update group column when children are CHARACTER
§ Avoid updates that would place mismatched data in child columns
§ Avoid updating group and child columns at the same time
– Group and child updates could overlay each other
– Order of SET COLUMN= will be implemented in-order

Datacom View Best Practices (Cont.)
§ DATACOM VIEWS can be joined together like any other view
or table
– Allows the joining of two rows in same table
– Provides SQL access to tables not normally available to SQL
§ DATACOM VIEWS can be updated
– Only one view per UPDATE/DELETE/INSERT statement

Complex Definitions
§ Some tables may need additional Datadictionary work in
conjunction with DATACOM VIEWs to get complete access
– Multiple dimension arrays
§ Consider redefinition in Datadictionary for higher levels
– Compound arrays
§ Arrays built at group level with multiple elements
– Other variations

Complex Definitions (Cont’d)
§ To make more definitions available to DATACOM VIEW
– Add additional redefinitions at the end of the table
§ Does not affect existing processing
§ No overhead other than Dictionary definitions

Using DBSQLPR
§ Simple batch utility to execute SQL statements
– DDL
§ Add schemas
§ Create tables, indexes, etc.
§ Avoid things like “alter table .. add column” which could run a long time
– DML
§ Add, update and delete rows
§ SQL Queries
– Reports
– Output formatted for spreadsheets
Datacom SQL Utility

Executing DBSQLPR
Datacom SQL Utility
//DBSQLPR EXEC PGM=DBSQLPR
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SNAPPER DD SYSOUT=*
//STDERR DD SYSOUT=*
//STDOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//OPTIONS DD *
AUTHID=SYSADM
SQLMODE=DATACOM
WORKSPACE=128
PRTWIDTH=133
NOFORMFEED
PAGELEN=60
ROWLIMIT=100000
MSG=NN
PLANAME=KEVIN
/*
Executing
JCL
Executing
Options
//SYSIN DD *
SELECT
BOROUGH_NUMBER,
BOROUGH_NAME,
TAX_DISTRICT,
TAX_CODE,
MAP_CODE,
MAP_CODE_ID1,
MAP_CODE_ID2,
MAP_CODE_TYP1,
MAP_CODE_TYP2,
MAP_BLOCK,
MAP_BLOCK_DET,
HOUSE_NUMBER,
HOUSE_NUMBER_EXT,
STREET_NAME,
STREET_NAME_QUAL,
STREET_DIVISION,
MAP_SEQUENCE
FROM
UNCOMP.UNCOMPRESS;
SQL
Statements
1
2
3

Executing DBSQLPR (Cont’d)
§ Comments can be imbedded
– Asterisk in column 1 “*”
– Dashes in column 1 and 2 “- -”
Datacom SQL Utility
//SYSIN DD *
* GET TABLES THAT WERE CREATED IN THE DEFAULT SQL AREA
-- LIST THE DBID AND SQL TABLE NAME
SELECT DBID, TABLE_NAME FROM SYSADM.DIR_TABLE WHERE DBID = 16;

§ Multiple statements allowed in SYSIN
– Each statement terminated by a “;” semi-colon
§ Can imbed “commit”
– Ensures the work is committed
Datacom SQL Utility
//SYSIN DD *
INSERT INTO . . . . . . ; COMIT;
DELETE FROM . . . . . ;
COMIT;
//SYSIN DD *

§ OPTIONS control execution
– AUTHID=SYSADM Sets default AUTHID to use
– SQLMODE=DATACOM Choose SQL mode
– WORKSPACE=128 Sets up space to process requests
– PRTWIDTH=133 Determines output width
– PAGELEN=60 Determines page length
– ROWLIMIT=100000 Stops after nnnn rows returned
– Lots more … See CA Datacom SQL Guide
Datacom SQL Utility

DBSQLPR Output
§ Three output formats
– *$WIDE
§ Left to right format (standard report)
§ If more columns than will fit on line the headings and data lines will wrap
§ Will revert to *$THIN if too many line wraps (>3)
– *$THIN or *$COLUMN
§ Top to bottom format (one column head and column value per line)
– DATASEPARATOR=;
§ Changes output to a delimiter separated value output
Datacom SQL Utility

DBSQLPR Output – Wide
Datacom SQL Utility
Command Line Options
____________________
Option File Options
___________________
AUTHID=SYSUSR
MSG=NN
NOFORMFEED
PAGELEN=60
PLANAME=KEVIN
PRTWIDTH=133
ROWLIMIT=1000
SQLMODE=DATACOM
WORKSPACE=128
INPUT STATEMENT:
SELECT DIR_NAME,
DBID,
TABLE_NAME,
AREA_NAME,
TABLE_ID,
CHNG_MASTER_KEY,
DUP_MASTER_KEY,
PIPELINE
FROM SYSADM.DIR_TABLE WHERE DBID = 16;
Echo options to output
Echo query to output

DBSQLPR Output – Wide (Cont’d)
Datacom SQL Utility
DIR_NAME DBID TABLE_NAME AREA_NAME TABLE_ID CHNG_MASTER_KEY DUP_MASTER_KEY PIPELINE
CHAR(8) N.N. UNS. SMALLINT N.N. CHAR(3) N.N. CHAR(3) N.N. UNS. SMALLINT N.N. CHAR(1) N.N. CHAR(1) N.N. CHAR(1) N.N.
____________ __________________ ____________ ____________ __________________ _______________ ______________ ____________
QAMUF4 16 B03 SQ1 3 Y Y Y
QAMUF4 16 SYS SQ1 63 Y Y Y
___ 30 rows returned ___
=================================================
== DBSQLPR is completing with return code 0000 ==
== ==
== Statements Found: 00001 ==
== Statement Errors: 00000 ==
== Statement Warnings: 00000 ==
=================================================
Query output in wide format
DBSQLPR execution results

DBSQLPR Output – Thin
Datacom SQL Utility
DIR_NAME, <QAMUF4 >, CHAR(8) NOT NULL
DBID, <16>, UNS. SMALLINT NOT NULL
TABLE_NAME, <B03>, CHAR(3) NOT NULL
AREA_NAME, <SQ1>, CHAR(3) NOT NULL
TABLE_ID, <3>, UNS. SMALLINT NOT NULL
CHNG_MASTER_KEY, <Y>, CHAR(1) NOT NULL
DUP_MASTER_KEY, <Y>, CHAR(1) NOT NULL
PIPELINE, <Y>, CHAR(1) NOT NULL
DIR_NAME, <QAMUF4 >, CHAR(8) NOT NULL
DBID, <16>, UNS. SMALLINT NOT NULL
TABLE_NAME, <B40>, CHAR(3) NOT NULL
AREA_NAME, <SQ1>, CHAR(3) NOT NULL
TABLE_ID, <40>, UNS. SMALLINT NOT NULL
CHNG_MASTER_KEY, <Y>, CHAR(1) NOT NULL
DUP_MASTER_KEY, <Y>, CHAR(1) NOT NULL
PIPELINE, <Y>, CHAR(1) NOT NULL
.
.
.
___ 30 rows returned ___
=================================================
== DBSQLPR is completing with return code 0000 ==
== ==
== Statements Found: 00001 ==
== Statement Errors: 00000 ==
== Statement Warnings: 00000 ==
=================================================
Same echo of options and query text as in wide
Query output in column format
Blank line in between rows
DBSQLPR execution results

DBSQLPR Output – DSV Output
Datacom SQL Utility
Options
//OPTIONS DD *
AUTHID=SYSUSR
SQLMODE=DATACOM
WORKSPACE=128
ROWLIMIT=1000
DATASEPARATOR=;
NOECHO
NOTYPE
NOPAGES
NOFORMFEED
PRTWIDTH=1500
DIR_NAME; DBID;TABLE_NAME;AREA_NAME;TABLE_ID;CHNG_MASTER_KEY;DUP_MASTER_KEY;PIPELINE;
QAMUF4 ; 16;B03 ;SQ1 ; 3;Y ;Y ;Y ;
QAMUF4 ; 16;B38 ;SQ1 ; 38;Y ;Y ;Y ;
QAMUF4 ; 16;B40 ;SQ1 ; 40;Y ;Y ;Y ;
QAMUF4 ; 16;B41 ;SQ1 ; 41;Y ;Y ;Y ;
QAMUF4 ; 16;B42 ;SQ1 ; 42;Y ;Y ;Y ;
QAMUF4 ; 16;B46 ;SQ1 ; 46;Y ;Y ;Y ;
QAMUF4 ; 16;B49 ;SQ1 ; 49;Y ;Y ;Y ;
QAMUF4 ; 16;B55 ;SQ1 ; 55;Y ;Y ;Y ;
QAMUF4 ; 16;B56 ;SQ1 ; 56;Y ;Y ;Y ;
.
.
.
DIR_NAME;DBID;TABLE_NAME;AREA_NAME;TABLE_ID;CHNG_MASTER_KEY;DUP_MASTER_KEY;PIPELINE;
QAMUF4;16;B03;SQ1;3;Y;Y;Y;
QAMUF4;16;CAT;SQ1;37;Y;Y;Y;
QAMUF4;16;CLA;SQ1;39;Y;Y;Y;
.
.
Options
//OPTIONS DD *
AUTHID=SYSUSR
SQLMODE=DATACOM
WORKSPACE=128
ROWLIMIT=1000
DATASEPARATOR=;
NOECHO
NOTYPE
NOPAGES
NOFORMFEED
PRTWIDTH=1500
SQUISH

DBSQLPR Output – DSV Output (Cont’d)
Easily Uploaded to Spreadsheet

Security Considerations
§ SQL and Server open up data access (and update)
– This openness may cause some security concerns
§ Strongly recommend using Datacom’s external security interface
– Path level security controls access by path as well as access language
§ Batch, CICS, Server, Dataquery, Other
§ Navigational (RAAT, SAAT), SQL
§ Consider starting with
– Read-only databases (SQL INTENT=R)
– Read-only server regions
Using SQL and Server

Experiences
SQL and Server can be
implemented to provide
modern access without
disruption to existing
applications
Key Topics
Datacom Views
DBSQLPR Utility
SQL Security
Findings
Datacom Views can make
legacy data SQL-able without
costly data row restructuring
DBSQLPR can be you best “new
friend”
Make sure security is
considered before opening up
access
Summary
Part II

Recommended Sessions
SESSION # TITLE DATE/TIME
MFX92S Strategy and Vision for CA Datacom and CA Ideal™ 11/16/2016 at 12:45 pm
MFX93S What’s new in CA Datacom 11/16/2016 at 1:45 pm
MFX94S Reducing TCO with Datacom Compression (Presspack) 11/16/2016 at 3:00 pm
MFX95SA Modernizing the CA Datacom data – SQL Access Part I 11/16/2016 at 3:45 pm
MFX95SB Modernizing the CA Datacom data – SQL Access Part II 11/16/2016 at 4:30 pm
MFX96S CA Datacom/AD Installation/Upgrade and Maintenance 11/17/2016 at 12:45 pm
MFX97S
CA Datacom/AD Report Utilization, and
information gather for Support
11/17/2016 at 1:45 pm
MFX98S
Using CA SYSVIEW® to monitor and measure your CA
Datacom environment – AD and DB
11/17/2016 at 3:00 pm
MFX99S Birds of a Feather/Stump the Techie! 11/17/2016 at 3:45 pm

Questions?

Stay connected at communities.ca.com
Thank you.

Modernizing the CA Datacom Data – SQL Access Part II

Recommended

More Related Content

What's hot (20)

Viewers also liked (18)

Similar to Modernizing the CA Datacom Data – SQL Access Part II (20)

More from CA Technologies (20)

Recently uploaded (20)

Modernizing the CA Datacom Data – SQL Access Part II