modernizing the ca datacom data – sql access part ii
TRANSCRIPT
![Page 1: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/1.jpg)
World®’16
ModernizingtheCADatacom®Data– SQLAccessPartIIKevinShuma,VPProductManagementCATechnologies
MFX95SB
MAINFRAMEANDWORKLOADAUTOMATION
![Page 2: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/2.jpg)
2 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
©2016CA.Allrightsreserved.Alltrademarksreferencedhereinbelongtotheirrespectivecompanies.
Thecontentprovidedinthis CAWorld2016presentationisintendedforinformationalpurposesonlyanddoesnotformanytypeofwarranty. The informationprovidedbyaCApartnerand/orCAcustomerhasnotbeenreviewedforaccuracybyCA.
ForInformationalPurposesOnlyTermsofthisPresentation
![Page 3: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/3.jpg)
3 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Abstract
ThisisatwopartsessionthatprovidesanoverviewofstepsnecessarytotakemakeCADatacom®availableformodernapplicationsviaSQLandServer.
FormanyshopstheirCADatacom®/DBenvironmentwascreatedbeforetheimplementationofSQL.ThissessionwillwalkthroughthestepsnecessarytomakeallthetheirdataSQLaccessibleusingCADatacom®SQL.FromthereweaddtheODBCandJDBCsupportprovidedbyCADatacom®Server.Oncecompletedyouwillhavedatathatisfullyaccessibletomodernapplicationsdevelopmentenvironments.
KevinShuma
CATechnologiesVP,ProductManagement
![Page 4: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/4.jpg)
4 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Agenda
WHATISADATACOMVIEW
IMPLEMENTINGDATACOMVIEWS
USINGDBSQLPR
SECURITYCONSIDERATIONS
SUMMARY
1
2
3
4
5
![Page 5: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/5.jpg)
5 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
WhatisaDatacomVIEW?
§ ExtensiontoSQLforDatacomonly– SupportsallcurrentSQLaccess– AddsSQLaccessfor
§ Compoundcolumns§ Single-dimensionarrays§ Redefinedcolumns
§ Simple“low-impact”methodtoutilizelegacydatastructureswithSQL
DatacomSQLExtension
![Page 6: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/6.jpg)
6 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
SQLColumnReportSQLACCESSSTD.– FromPartI
STDSQLaccessmaybelimitedforcertainlegacystructures
![Page 7: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/7.jpg)
7 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
SQLColumnReportSQLACCESS– DATACOMVIEW
STDSQLaccessmaybelimitedforcertainlegacystructures
SQLACCESS- DATACOMindicatesifcolumnisaccessiblebyaDATACOMVIEW
ColumnsthatwerenotaccessibleusingstandardSQLmaybefullyaccessibleusingaDATACOMVIEW
![Page 8: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/8.jpg)
8 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ImplementingaDATACOMVIEW
§ SyntaxthesameasstandardCREATEVIEWwithonechange– CREATEDATACOM VIEW
§ Groupcolumnsareavailableascharactercolumns
§ Redefinedcolumnsavailableasdefined
§ Repeatingcolumnsavailableusingcolumn_sqlname[nn]i.e.part_number[1],part_number[2],part_number[3]
UsingStandardSQLDDLSyntaxWithDatacomExtensions
![Page 9: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/9.jpg)
9 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
GeneratingaDATACOMVIEWsyntax
§ UseeitherDDUTILTYorDDUPDATEbatchutilities– Inputtransactions:
+UTLLIBRARY,SRC+UTLLANGUAGE,SQL+UTLMODE,DATACOM-VIEW+UTLCOPY,TABLE,POLICY-HISTORY(PROD),POHDVIEW
§ Output– SYSPRINT– reportshowingthecreatedview– SYSPUNCH– outputdatasetwithcreatedviewsyntax
UsingDatadictionary UtilitytoGenerateaViewModeltoUse
![Page 10: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/10.jpg)
10 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
./ 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,
SCHEDULE_PAYAMT_MONTH_02,
. . . .
SCHEDULE_PAYAMT_MONTH_12,
-- END ARRAY: SCHEDULE_PAYAMT_MONTH
SCHEDULE_COMMENT,
Generatesentriesforeachoftheentriesinarepeatinggroup
DatacomViewGeneratorOutput- SYSPUNCH
Generatesanentryforeachgroupcolumn
![Page 11: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/11.jpg)
11 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DatacomViewGeneratorOutput– 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
Createonsetofentryforeachdefinition
PaymentrowdefinitionTransaction-type“PAY”
PaymentrowdefinitionTransaction-type“NOT”
Forredefinitionsyoumustchooseonerowdefinitionperview!!
![Page 12: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/12.jpg)
12 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DATACOMVIEW– POH_SCHEDULE./ ADD NAME=POHSCHED
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,
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_PAYAMT_MONTH[02],
. . . .
SCHEDULE_PAYAMT_MONTH[12],
SCHEDULE_COMMENT
FROM
SYSUSR.POLICY_INFO
WHERE TRANSACTION_TYPE = ‘SCH’
WITH CHECK OPTION;
1stdefinitionRepeatinggroup
![Page 13: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/13.jpg)
13 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DATACOMVIEW– POH_PAYMENT./ ADD NAME=POHSCHED
CREATE DATACOM VIEW
SYSUSR.POLICY_INFO_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.
2nddefinitionSeriesofcolumns
![Page 14: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/14.jpg)
14 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DATACOMVIEW– POH_NOTES./ ADD NAME=POHSCHED
CREATE DATACOM VIEW
SYSUSR.POLICY_INFO_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.
3rddefinitionSetof
differentcolumns
![Page 15: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/15.jpg)
15 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DatacomViewBestPractices
§ Whenredefinitionsexist– Alwaysuseawhereclauseto“match”rowstodefinitions– AlwaysuseaWITHCHECKOPTIONtoprotect“updates”
§ Whengroupcolumnsreferenced– Dataistreatedascharacter– OnlyupdategroupcolumnwhenchildrenareCHARACTER
§ Avoidupdatesthatwouldplacemismatcheddatainchildcolumns§ Avoidupdatinggroupandchildcolumnsatthesametime
– Groupandchildupdatescouldoverlayeachother
– OrderofSETCOLUMN=willbeimplementedin-order
![Page 16: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/16.jpg)
16 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DatacomViewBestPractices(Cont.)
§ DATACOMVIEWScanbejoinedtogetherlikeanyotherviewortable– Allowsthejoiningoftworowsinsametable– ProvidesSQLaccesstotablesnotnormallyavailabletoSQL
§ DATACOMVIEWScanbeupdated– OnlyoneviewperUPDATE/DELETE/INSERTstatement
![Page 17: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/17.jpg)
17 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ComplexDefinitions
§ SometablesmayneedadditionalDatadictionaryworkinconjunctionwithDATACOMVIEWstogetcompleteaccess– Multipledimensionarrays
§ ConsiderredefinitioninDatadictionaryforhigherlevels– Compoundarrays
§ Arraysbuiltatgrouplevelwithmultipleelements– Othervariations
![Page 18: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/18.jpg)
18 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ComplexDefinitions(Cont’d)
§ TomakemoredefinitionsavailabletoDATACOMVIEW– Addadditionalredefinitionsattheendofthetable
§ Doesnotaffectexistingprocessing§ NooverheadotherthanDictionarydefinitions
![Page 19: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/19.jpg)
19 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
UsingDBSQLPR
§ SimplebatchutilitytoexecuteSQLstatements– DDL
§ Addschemas§ Createtables,indexes,etc.§ Avoidthingslike“altertable..addcolumn”whichcouldrunalongtime
– DML§ Add,updateanddeleterows§ SQLQueries
– Reports
– Outputformattedforspreadsheets
DatacomSQLUtility
![Page 20: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/20.jpg)
20 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ExecutingDBSQLPRDatacomSQLUtility
//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 /*
ExecutingJCL
ExecutingOptions
//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;
SQLStatements
1
2
3
![Page 21: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/21.jpg)
21 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ExecutingDBSQLPR(Cont’d)
§ Commentscanbeimbedded– Asteriskincolumn1 “*”– Dashesincolumn1and2 “- -”
DatacomSQLUtility
//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;
![Page 22: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/22.jpg)
22 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ExecutingDBSQLPR(Cont’d)
§ MultiplestatementsallowedinSYSIN– Eachstatementterminatedbya“;”semi-colon
§ Canimbed“commit”– Ensurestheworkiscommitted
DatacomSQLUtility
//SYSIN DD * INSERT INTO . . . . . . ; COMIT;DELETE FROM . . . . . ; COMIT;
//SYSIN DD * SELECT DBID, TABLE_NAME FROM SYSADM.DIR_TABLE WHERE DBID = 100; SELECT DBID, TABLE_NAME FROM SYSADM.DIR_TABLE WHERE DBID = 16;
![Page 23: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/23.jpg)
23 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ExecutingDBSQLPR(Cont’d)
§ OPTIONScontrolexecution– AUTHID=SYSADM SetsdefaultAUTHIDtouse– SQLMODE=DATACOM ChooseSQLmode– WORKSPACE=128 Setsupspacetoprocessrequests– PRTWIDTH=133 Determinesoutputwidth– PAGELEN=60 Determinespagelength– ROWLIMIT=100000 Stopsafternnnnrowsreturned– Lotsmore… SeeCADatacomSQLGuide
DatacomSQLUtility
![Page 24: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/24.jpg)
24 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DBSQLPROutput
§ Threeoutputformats– *$WIDE
§ Lefttorightformat(standardreport)§ Ifmorecolumnsthanwillfitonlinetheheadingsanddatalineswillwrap§ Willrevertto*$THINiftoomanylinewraps(>3)
– *$THINor*$COLUMN§ Toptobottomformat(onecolumnheadandcolumnvalueperline)
– DATASEPARATOR=;§ Changesoutputtoadelimiterseparatedvalueoutput
DatacomSQLUtility
![Page 25: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/25.jpg)
25 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DBSQLPROutput– WideDatacomSQLUtilityCommand 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;
Echooptionstooutput
Echoquerytooutput
![Page 26: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/26.jpg)
26 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DBSQLPROutput– Wide(Cont’d)DatacomSQLUtilityDIR_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 B38 SQ1 38 Y Y YQAMUF4 16 B40 SQ1 40 Y Y YQAMUF4 16 B41 SQ1 41 Y Y YQAMUF4 16 B42 SQ1 42 Y Y YQAMUF4 16 B46 SQ1 46 Y Y YQAMUF4 16 B49 SQ1 49 Y Y YQAMUF4 16 B55 SQ1 55 Y Y YQAMUF4 16 B56 SQ1 56 Y Y YQAMUF4 16 B58 SQ1 58 Y Y YQAMUF4 16 B59 SQ1 59 Y Y YQAMUF4 16 B61 SQ1 61 Y Y YQAMUF4 16 B62 SQ1 62 Y Y YQAMUF4 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 == =================================================
Queryoutputinwideformat
DBSQLPRexecutionresults
![Page 27: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/27.jpg)
27 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DBSQLPROutput– ThinDatacomSQLUtilityDIR_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 == =================================================
SameechoofoptionsandquerytextasinwideQueryoutputincolumnformatBlanklineinbetweenrows
DBSQLPRexecutionresults
![Page 28: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/28.jpg)
28 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DBSQLPROutput– DSVOutputDatacomSQLUtility
Options//OPTIONSDD*AUTHID=SYSUSRSQLMODE=DATACOMWORKSPACE=128ROWLIMIT=1000DATASEPARATOR=;NOECHONOTYPENOPAGESNOFORMFEEDPRTWIDTH=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;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; QAMUF4;16;B58;SQ1;58;Y;Y;Y; QAMUF4;16;B59;SQ1;59;Y;Y;Y; QAMUF4;16;B61;SQ1;61;Y;Y;Y; QAMUF4;16;B62;SQ1;62;Y;Y;Y; QAMUF4;16;CAT;SQ1;37;Y;Y;Y; QAMUF4;16;CLA;SQ1;39;Y;Y;Y; ..
Options//OPTIONSDD*AUTHID=SYSUSRSQLMODE=DATACOMWORKSPACE=128ROWLIMIT=1000DATASEPARATOR=;NOECHONOTYPENOPAGESNOFORMFEEDPRTWIDTH=1500SQUISH
![Page 29: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/29.jpg)
29 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DBSQLPROutput– DSVOutput(Cont’d)EasilyUploadedtoSpreadsheet
![Page 30: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/30.jpg)
30 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
SecurityConsiderations
§ SQLandServeropenupdataaccess(andupdate)– Thisopennessmaycausesomesecurityconcerns
§ StronglyrecommendusingDatacom’sexternalsecurityinterface– Pathlevelsecuritycontrolsaccessbypathaswellasaccesslanguage
§ Batch,CICS,Server,Dataquery,Other
§ Navigational(RAAT,SAAT),SQL
§ Considerstartingwith– Read-onlydatabases(SQLINTENT=R)– Read-onlyserverregions
UsingSQLandServer
![Page 31: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/31.jpg)
31 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ExperiencesSQLandServercanbeimplementedtoprovidemodernaccesswithoutdisruptiontoexistingapplications
KeyTopicsDatacomViewsDBSQLPRUtilitySQLSecurity
FindingsDatacomViewscanmakelegacydataSQL-ablewithoutcostlydatarowrestructuringDBSQLPRcanbeyoubest“newfriend”Makesuresecurityisconsideredbeforeopeningupaccess
SummaryPartII
![Page 32: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/32.jpg)
32 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
RecommendedSessionsSESSION# TITLE DATE/TIME
MFX92S StrategyandVisionforCADatacomandCAIdeal™ 11/16/2016at12:45pm
MFX93S What’snewinCADatacom 11/16/2016at1:45pm
MFX94S ReducingTCOwithDatacomCompression(Presspack) 11/16/2016at3:00pm
MFX95SA ModernizingtheCADatacomdata – SQLAccessPartI 11/16/2016at3:45pm
MFX95SB ModernizingtheCADatacomdata – SQLAccessPartII 11/16/2016at4:30pm
MFX96S CADatacom/ADInstallation/UpgradeandMaintenance 11/17/2016at12:45pm
MFX97S CADatacom/ADReportUtilization,andinformationgatherforSupport 11/17/2016at1:45pm
MFX98SUsingCASYSVIEW®tomonitorandmeasureyourCADatacomenvironment– ADandDB
11/17/2016at3:00pm
MFX99S BirdsofaFeather/StumptheTechie! 11/17/2016at3:45pm
![Page 33: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/33.jpg)
33 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Questions?
![Page 34: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/34.jpg)
34 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Stayconnectedatcommunities.ca.com
Thankyou.
![Page 35: Modernizing the CA Datacom Data – SQL Access Part II](https://reader034.vdocuments.us/reader034/viewer/2022042604/58739fb31a28ab85438b7471/html5/thumbnails/35.jpg)
@CAWORLD#CAWORLD ©2016CA.AllRIGHTSRESERVED.35 @CAWORLD#CAWORLD
MainframeandWorkloadAutomation
FormoreinformationonMainframeandWorkloadAutomation,pleasevisit:http://cainc.to/9GQ2JI