pre-con ed: using sql to access your ca idms databases
TRANSCRIPT
World®’16
UsingSQLtoAccessYourCAIDMSDatabasesDavidRoss,Sr.PrincipalProductOwnerCATechnologies
MFX73E
MAINFRAMEANDWORKLOADAUTOMATION
2 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Abstract
Thissessiondescribeshowtorepresentyourexistingnetwork-definedCAIDMSdatabasesforrelationalmodelaccess.AvailableSQLaccessstrategiesarediscussedindetailsuchastheuseofproceduresandtableproceduresandtheuseofforeignkeys.
DavidRoss
CATechnologiesSr.PrincipalProductOwner,CAIDMS
3 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Agenda
LEVERAGINGANDEXTENDINGCAIDMS™
CAIDMSSQL
MAXIMUMSIX(6)WORDSPERBULLET
RELATIONALTONETWORKMAPPINGTECHNIQUES
CAIDMSSERVER
MAXIMUMSIX(6)WORDSPERBULLET
1
2
3
4
5
6
4 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
LeveragingandExtendingCAIDMS
5 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
HowDoYouMaximizeBusinessValue?
Largeinvestmentinlegacyapplications••Costlyandriskyconversion••Hardtofindlegacyskills
LeverageandExtend••Leverageinvestment,reducecost••Preserveapplications,reducerisk••Usecurrentdeveloperskills
6 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
LeveragingandExtendingCAIDMSWhatWeMean
••Keepyourdatabaseinplace••Accessfromwebservices••Usestandardinterfaces
LeverageCAIDMSdatabases
••Reuseyourapplicationbusinesslogic••Invokewebservices••Providewebservices
ExtendCAIDMS
applications
7 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
LeveragingCAIDMSHowYouCanDoIt
UseindustrystandardAPIs
••SQL••ODBC,JDBC••SOAP,REST••HTTP••TCP/IP
Enabledby
••CAIDMSSQL••CAIDMSServer••CAIDMSWebServices••CAIDMSSockets
8 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
CAIDMSModernizationArchitecture
CAIDMSCV
Consumer WebService
IDMSWebServices
PL/1COBOL
AppServer
WebService Consumer ConsumerApps
MobileApps
AppServerProvider
JDBC
IDMSProvider
LeverageExisting
Applications
WebService
IDMSSQL/JDBC
SQLProcedure
ADS
9 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
CAIDMSSQL
10 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
CAIDMSSQLRelationaltoNetworkMapping
••IdenticallynamedTable
••EquivalentRow
••AutomaticallyrenamedColumn
••PartialmappingReferentialconstraint
••ViaSCHEMARecorddefinition
••EquivalentRecordoccurrence
••ExceptODO,redefines,…Element
••LackofforeignkeysSet
SQL Network
11 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
CAIDMSSQLEnablesAccesstoNetworkRecordsInOneEasyStep
CREATE SCHEMA EMPSQLFOR NONSQL SCHEMAAPPLDICT.EMPSCHM V 100;
SQLSchema
Identifies
Networkschema Databaseinstance(optional)
DefinedinSQLcatalog
12 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
NoNeedtoCreateanewDatabase
Everynetworkrecordautomaticallyappearsasatable
OBTAINCALCbecomes
SELECT * FROMEMPSQL.EMPLOYEEWHERE “EMP_ID_0415” = 9771;
13 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
NetworkRecord
Appearsasatable••Namenottranslated••Encloseinquotesifneeded
EMPSQL.”DENTAL-CLAIM”
14 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
RecordElement
Lowestlevelelementappearsasacolumn••Nametranslated••Hyphenschangedtounderscores••SQLsynonymcanoverridename
TypemappedtoSQLtype
Someelementdefinitionsarenotmapped••Groupitems••Redefines••Occursdepending
15 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ANetworkSetMayAppearasaReferentialConstraint
Referencedtable
Referencingtable
DEPT-EMPLOYEE
DEPT-ID-0410
DEPARTMENT
ORG-DEMO-REGION
410 F 120 CALC
EMP-ID-0415
EMPLOYEE
EMP-DEMO-REGION
415 F 120 CALC
16 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
RelationalModelReferentialConstraintsBasedonDataValues
FOREIGN KEYPRIMARYKEY
DepartmentTable EmployeeTable
DEPT_ID DEPT_NAME
1001 SALES
1010 HUMAN RES
1050 ACCOUNTING
1090 PAYROLL
EMP_ID E_LNAME E_FNAME JOB DEPT
12345 CLEMENS SAMUEL J200 1010
21343 MCGEE AGNES J001 1001
31254 GIBSON JOHN J010 1050
43251 SCOTT WESTON J410 1050
17 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
NetworkModelChainedSetsareLinkedLists
18 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ChainedSetPointersNotExplicitDataValues
19 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
RelationalToNetworkMappingTechniques
Syntaxextensions
Views
Procedures••Tableprocedures••Calledprocedures
Foreignkeys••Referentialsets••Virtualforeignkeys
20 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
SyntaxExtensions
CAIDMSExtensionstoSQL••Noprogramming••Nodatabasechanges
SetNamePredicate••WHERE<setname>••Joincriteriaforsetnavigation••Supportsmostqueries
ROWIDPseudoColumn••BasedonDBKEY,hasthesamepersistence••Notdefinedincatalogordictionary••Especiallyusefulforupdatingrecords
21 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
EMPSCHMDatabase
COVERAGE-CLAIMS
EMP-COVERAGE
EMP-ID-0415
EMPLOYEE
EMP-DEMO-REGION
415 F 120 CALC
OCCURS DEPENDING ON
COVERAGE-CLAIMS
DENTAL-CLAIM
INS-DEMO-REGION
405 V 932 VIAEMP-COVERAGE
COVERAGE
INS-DEMO-REGION
400 F 20 VIA
22 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
UsingASetNamePredicate
SELECT EMP_ID_0415,INS_PLAN_CODE_0400,DENTIST-LICENSE-NUMBER-0405FROM EMPSQL.EMPLOYEE,EMPSQL.COVERAGE,EMPSQL.”DENTAL-CLAIM” CWHERE “EMP-COVERAGE”AND “COVERAGE-CLAIMS”.C
23 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
UsingaROWID
UPDATE EMPSQL.COVERAGE CSET SELECTION_YEAR_0400 = 20WHERE C.ROWID IN (SELECT CI.ROWIDFROM EMPSQL.EMPLOYEE E,EMPSQL.COVERAGE CI
WHERE “EMP-COVERAGE”AND EMP_ID_0415 = 23)
24 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Views
Encapsulatesyntaxextensions
Encapsulaterelationships
Overridecolumnnames
Enforcesecurity••Tables••Columns••Rows
25 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
UsingaView
CREATE VIEW EMPSQL.EMPL_DENTIST(EMPLOYEE_ID, PLAN_CODE, DENTIST_NUMBER) ASSELECT EMP_ID_0415,INS_PLAN_CODE_0400,DENTIST-LICENSE-NUMBER-0405FROM EMPSQL.EMPLOYEE,EMPSQL.COVERAGE,EMPSQL.”DENTAL-CLAIM” CWHERE “EMP-COVERAGE” AND “COVERAGE-CLAIMS”.C
SELECT * FROM EMPSQL.EMPL_DENTISTWHERE EMPLOYEE_ID = ‘0555’
26 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
TableProcedures
ExternalprogramthatissuesnativeDML
AccessedliketableinSQLDML••SELECT••INSERT••UPDATE••DELETE
Returnsaresultsetlikeatable
27 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
TableProcedureUseCases
Encapsulaterelationships
Solvestructuralproblems••Lackofembeddedforeignkeys••OCCURSDEPENDINGON
Requiresprogramming••CangeneratedwithCAIDMSSQLQuickBridge
Nodatabaseorapplicationchanges
28 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
EMPSCHMDatabase
COVERAGE-CLAIMS
EMP-COVERAGE
EMP-ID-0415
EMPLOYEE
EMP-DEMO-REGION
415 F 120 CALC
OCCURS DEPENDING ON
COVERAGE-CLAIMS
DENTAL-CLAIM
INS-DEMO-REGION
405 V 932 VIAEMP-COVERAGE
COVERAGE
INS-DEMO-REGION
400 F 20 VIA
29 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
DefiningATableProcedure
CREATE TABLE PROCEDUREDENTAL_CLAIM (
EMPLOYEE_ID NUMERIC(4, 0),INS_PLAN CHAR(3),TYPE CHAR(1),DENTIST_LIC NUMERIC(6, 0),PROC_CODE NUMERIC(4, 0),FEE DECIMAL(9,2))
EXTERNAL NAME EMPDCLAM;
30 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
UsingATableProcedure
SELECT PROC_CODE, FEEFROM DENTAL_CLAIMWHERE EMPLOYEE_ID = 0555
AND INS-PLAN = ‘004’AND TYPE = ‘F’AND DENTIST_LIC = 123456;
INSERT INTO DENTAL_CLAIMVALUES (666,‘004’,’F’,654321,5555,585.00);
31 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ForeignKeys
ReferentialSets– userdefined
VirtualForeignKeys– systemdefined
AllowuseofstandardSQL
Enforcereferentialconstraintenforcement
StandardODBCandJDBCmetadata
Compatibilitywithcommontools
32 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
EMPSCHMDatabase
DEPT-EMPLOYEE EMP-EXPERTISE
DEPT-ID-0410
DEPARTMENT
ORG-DEMO-REGION
410 F 120 CALCEMP-ID-0415
EMPLOYEE
EMP-DEMO-REGION
415 F 120 CALCEMP-EXPERTISE
EXPERTISE
EMP-DEMO-REGION
400 F 16 VIA
33 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ReferentialSets
Exposesetsasreferentialconstraints••Queries••Updates
Basedonrecordkeyvalues••Primarykey=UNIQUECALCorsystemownedindex••Foreignkey=columnvalueinmemberrecord
Defineonnetworkschemasetdefinition
VisiblethroughJDBCandODBCmetadata
Enableaccessforcommontoolsandapplicationframeworks
UseSQLtoaccessandmaintainnetworkdatabasesfromJava
34 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
EMPSCHMwithReferentialSets
DEPT-EMPLOYEE EMP-EXPERTISE
DEPT-ID-0410
DEPARTMENT
ORG-DEMO-REGION
410 F 120 CALC
EMP-ID-0425DEPT-ID-0415
EMP-ID-0415
EMPLOYEE
EMP-DEMO-REGION
415 F 120 CALCEMP-EXPERTISE
EXPERTISE
EMP-DEMO-REGION
425 F 16 VIA
35 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
UsingReferentialSets
§ NonstandardCAIDMSextensionsUPDATE EMPSQL.EXPERTISE
SET EXPERTISE_YEAR_0425 = 20WHERE ROWID IN (
SELECT X.ROWIDFROM EMPSQL.EMPLOYEE E,EMPSQL.EXPERTISE XWHERE “EMP-EXPERTISE”
AND EMP_ID_0415 = 23)
§ BecomestandardSQLUPDATE EMPSQL.EXPERTISE
SET EXPERTISE_YEAR_0425 = 20WHERE EMP_ID_0425 = 23)
36 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ImplementingReferentialSets
Defineprimaryandforeignkeysforset
Mayneedtorestructurememberrecord
Populateforeignkeysinmember
Maintainforeignkeysinmember
Alternativetomigration
••Candoincrementally••Lesscost••Lesseffort
37 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
VirtualForeignKeys
Exposesetsasreferentialconstraints••Queries••Updates
BasedonROWID••Virtualprimarykey=ROWID••Virtualforeignkey=FKEY_<set_name>
DefineonSQLschemadefinition
VisiblethroughJDBCandODBCmetadata
Enableaccessforcommontoolsandapplicationframeworks
UseSQLtoaccessandmaintainnetworkdatabasesfromJava
38 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
VirtualForeignKeyBenefits
§ UsestandardSQLtoaccessandupdateCAIDMS– RemovesINSERTandUPDATElimitations– CapabilitieslikenetworkDML– WriteapplicationsusingJava,.NET,andpopularframeworks– NospecialCAIDMSSQLsyntax
§ Easytoimplement– Nodatabasechanges– Nochangestonetworkdefinitions– Noneedfortableprocedures
39 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
EnablingVirtualForeignKeys
§ “Create”virtualkeyswithSQLschemadefinitioncreateschemaEMPSQL
fornonsql schemaEMPDICT.EMPSCHMversion100withvirtualkeys
§ ROWIDandvirtualforeignkeysbecomevisible– SELECT*– INSERTcolumnlistwhenlistisomitted– UPDATEcolumnlistwhenlistisomitted
§ “Remove”virtualkeysalterschemaEMPSQLwithoutvirtualkeys
40 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
EMPSCHMWithVirtualForeignKeys
DEPT-EMPLOYEE EMP-EXPERTISE
DEPT-ID-0410
DEPARTMENT
ORG-DEMO-REGION
410 F 120 CALC
FKEY_EMP_EXPERTISEFKEY_DEPT_EMPLOYEE
EMP-ID-0415
EMPLOYEE
EMP-DEMO-REGION
415 F 120 CALCEMP-EXPERTISE
EXPERTISE
EMP-DEMO-REGION
425 F 16 VIA
ROWID ROWID ROWID
41 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
UsingVirtualForeignKeys
§ NonstandardCAIDMSextensionsUPDATE EMPSQL.EXPERTISE
SET EXPERTISE_YEAR_0425 = 20WHERE ROWID IN (
SELECT X.ROWIDFROM EMPSQL.EMPLOYEE E,
EMPSQL.EXPERTISE XWHERE “EMP-EXPERTISE”
AND EMP_ID_0415 = 23)
§ BecomestandardSQLUPDATE EMPSQL.EXPERTISE
SET EXPERTISE_YEAR_0425 = 20WHERE FKEY_SET_EMP_EXPERTISE = (
SELECT ROWIDFROM EMPSQL.EMPLOYEEWHERE EMP_ID_0415 = 23)
42 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
43 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
44 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
45 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ComparisonofMappingTechniques
SQLExtensions No No No No Limited
Views Yes No No No Limited
TableProcedures Yes Yes No No Encapsulate
ReferentialSets Yes No Yes Maybe Referentialconstraints
VirtualKeys Yes No No No Referentialconstraints
StandardSQL Newprograms ExposesSetsApplicationchanges
Restructure
46 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
CAIDMSServer
47 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
CAIDMSServer
§ CAIDMSasaServer
§ ODBCdriver
§ JDBCdriver
§ DynamicSQL
48 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ODBCOpenDataBaseConnectivity
CallLevelInterface
Interoperability
MicrosoftWindows
ODBC.NET
49 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
ODBCArchitectureWindows
ODBC ApplicationProgram Interface
ODBC ServiceProvider Interface
Network and DBMS
ODBCApplication
ODBC DriverManager
ODBCDriver
DataSource
ODBCDriver
DataSource
50 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
CAIDMSODBCDriver
§ ODBC3.5
§ Wireprotocolcommunications
§ 32and64-bitdrivers
51 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
JDBC“JavaDataBaseConnectivity”
CallLevelInterface
Objectoriented
Interoperability
AnyJavaplatform
52 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
JDBCArchitecture
JavaVM
Java Application
DriverManager
DriverInterface
Networkand DBMS
Driver
URL
Driver
URL
53 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
CAIDMSServerArchitectureODBCandJDBC
SQLClientInterface
ODBCDriver JDBCDriver
SQLEngine
NetworkDBMS
54 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
JDBCDriverTypes
DBMS
ODBC
JDBC
Type 1
JDBC
Native
DBMS
Type 2
DBMS
JDBC
Type 4
Middleware
DBMS
JDBC
Type 3
55 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
CAIDMSJDBCDriver
§ JDBC4.0
§ “Universal”driver– Types2,3,4
§ 64-bitType2Driver
§ Type4JDBCDriver– DirectconnectionfromJDBCdrivertoCV
§ DistributedXAtransactions
56 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
57 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Type4JDBCDriver
CVJava
TCP/IP
JDBCDriver
NativeClient
JDBCServer
58 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Type4Server
TCPIPLineDriver
JSRVServerTask
SQLClient
SQLEngine
GenericListener
59 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
SYSGENServerDefinition
ADD PTERM TCPJSRVTYPE IS LISTENERPORT IS 3799TASK IS RHDCNP3JMODE IS SYSTEMPARM IS 'TASK=IDMSJSRV'.
ADD LTERM TCLJSRVPTERM IS TCPJSRV.
60 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
XAResourceManager
TCPIPLineDriver
JSRVServerTask
SQLClient XAClient
SQLEngine TransactionManager
GenericListener
61 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
CAIDMSServerCAIDMSServerenablesWindowsandJavadeveloperstouseSQLaccessandupdateCAIDMSdatabasesandapplications.
CAIDMSCAIDMSSQLandCAIDMSServerhelpusersleverageandextendtheirinvestmentinCAIDMSapplicationsanddata.
CAIDMSSQLCAIDMSSQLenablesdeveloperstouseSQLtoaccessandupdatenetworkdatabasesaswellastocreatenewSQLdefineddatabases.
Summary
@CAWORLD#CAWORLD ©2016CA.AllRIGHTSRESERVED.62 @CAWORLD#CAWORLD
MainframeandWorkloadAutomation
FormoreinformationonMainframeandWorkloadAutomation,pleasevisit:http://cainc.to/9GQ2JI
63 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
RecommendedSessionsSESSION# TITLE DATE/TIME
MFX100S StrategyandVisionforCAIDMS 11/16/2016at12:45pm
MFX101S LegacyisnotaDirtyWord 11/16/2016at1:45pm
MFX102S CAIDMS19.0WebServicesforModernization 11/16/2016at3:00pm
MFX103S CAIDMS19.0SQLEnhancementsforModernization 11/16/2016at3:30pm
MFX104S JavaAccesstoCA-IDMSDataatBT(BritishTelecom) 11/16/2016at4:45pm
MFX105SImplementationandUseofGenericVTAMResourceswithParallelSYSPLEXFeatures(CA andCAXIA) 11/17/2016at12:45pm
MFX106S CAIDMSBufferTuning 11/17/2016at1:45pm
MFX107S M3AServicesMonitor,Measure,ManageandAlert 11/17/2016at3:00pm
MFX108S BirdsofaFeather/StumptheTechie! 11/17/2016at3:45pm
64 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
MustSeeDemos
DemoNameProductXTheater#location
DemoNameServicesYTheater#location
DemoNameSolutionYTheater#location
DemoNameProductXTheater#location
65 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Questions?
66 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Thankyou.
Stayconnectedatcommunities.ca.com
67 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
©2016CA.Allrightsreserved.Alltrademarksreferencedhereinbelongtotheirrespectivecompanies.
Thecontentprovidedinthis CAWorld2016presentationisintendedforinformationalpurposesonlyanddoesnotformanytypeofwarranty. The informationprovidedbyaCApartnerand/orCAcustomerhasnotbeenreviewedforaccuracybyCA.
ForInformationalPurposesOnlyTermsofthisPresentation