pre-con ed: using sql to access your ca idms databases

World®’16

UsingSQLtoAccessYourCAIDMSDatabasesDavidRoss,Sr.PrincipalProductOwnerCATechnologies

MFX73E

MAINFRAMEANDWORKLOADAUTOMATION

Abstract

Thissessiondescribeshowtorepresentyourexistingnetwork-definedCAIDMSdatabasesforrelationalmodelaccess.AvailableSQLaccessstrategiesarediscussedindetailsuchastheuseofproceduresandtableproceduresandtheuseofforeignkeys.

DavidRoss

CATechnologiesSr.PrincipalProductOwner,CAIDMS

Agenda

LEVERAGINGANDEXTENDINGCAIDMS™

CAIDMSSQL

MAXIMUMSIX(6)WORDSPERBULLET

RELATIONALTONETWORKMAPPINGTECHNIQUES

CAIDMSSERVER

MAXIMUMSIX(6)WORDSPERBULLET

LeveragingandExtendingCAIDMS

HowDoYouMaximizeBusinessValue?

Largeinvestmentinlegacyapplications••Costlyandriskyconversion••Hardtofindlegacyskills

LeverageandExtend••Leverageinvestment,reducecost••Preserveapplications,reducerisk••Usecurrentdeveloperskills

LeveragingandExtendingCAIDMSWhatWeMean

••Keepyourdatabaseinplace••Accessfromwebservices••Usestandardinterfaces

LeverageCAIDMSdatabases

••Reuseyourapplicationbusinesslogic••Invokewebservices••Providewebservices

ExtendCAIDMS

applications

LeveragingCAIDMSHowYouCanDoIt

UseindustrystandardAPIs

••SQL••ODBC,JDBC••SOAP,REST••HTTP••TCP/IP

Enabledby

••CAIDMSSQL••CAIDMSServer••CAIDMSWebServices••CAIDMSSockets

CAIDMSModernizationArchitecture

CAIDMSCV

Consumer WebService

IDMSWebServices

PL/1COBOL

AppServer

WebService Consumer ConsumerApps

MobileApps

AppServerProvider

IDMSProvider

LeverageExisting

Applications

WebService

IDMSSQL/JDBC

SQLProcedure

CAIDMSSQL

CAIDMSSQLRelationaltoNetworkMapping

••IdenticallynamedTable

••EquivalentRow

••AutomaticallyrenamedColumn

••PartialmappingReferentialconstraint

••ViaSCHEMARecorddefinition

••EquivalentRecordoccurrence

••ExceptODO,redefines,…Element

••LackofforeignkeysSet

SQL Network

CAIDMSSQLEnablesAccesstoNetworkRecordsInOneEasyStep

CREATE SCHEMA EMPSQLFOR NONSQL SCHEMAAPPLDICT.EMPSCHM V 100;

SQLSchema

Identifies

Networkschema Databaseinstance(optional)

DefinedinSQLcatalog

NoNeedtoCreateanewDatabase

Everynetworkrecordautomaticallyappearsasatable

OBTAINCALCbecomes

SELECT * FROMEMPSQL.EMPLOYEEWHERE “EMP_ID_0415” = 9771;

NetworkRecord

Appearsasatable••Namenottranslated••Encloseinquotesifneeded

EMPSQL.”DENTAL-CLAIM”

RecordElement

Lowestlevelelementappearsasacolumn••Nametranslated••Hyphenschangedtounderscores••SQLsynonymcanoverridename

TypemappedtoSQLtype

Someelementdefinitionsarenotmapped••Groupitems••Redefines••Occursdepending

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

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

NetworkModelChainedSetsareLinkedLists

ChainedSetPointersNotExplicitDataValues

RelationalToNetworkMappingTechniques

Syntaxextensions

Procedures••Tableprocedures••Calledprocedures

Foreignkeys••Referentialsets••Virtualforeignkeys

SyntaxExtensions

CAIDMSExtensionstoSQL••Noprogramming••Nodatabasechanges

SetNamePredicate••WHERE<setname>••Joincriteriaforsetnavigation••Supportsmostqueries

ROWIDPseudoColumn••BasedonDBKEY,hasthesamepersistence••Notdefinedincatalogordictionary••Especiallyusefulforupdatingrecords

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

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

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)

Encapsulatesyntaxextensions

Encapsulaterelationships

Overridecolumnnames

Enforcesecurity••Tables••Columns••Rows

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’

TableProcedures

ExternalprogramthatissuesnativeDML

AccessedliketableinSQLDML••SELECT••INSERT••UPDATE••DELETE

Returnsaresultsetlikeatable

TableProcedureUseCases

Encapsulaterelationships

Solvestructuralproblems••Lackofembeddedforeignkeys••OCCURSDEPENDINGON

Requiresprogramming••CangeneratedwithCAIDMSSQLQuickBridge

Nodatabaseorapplicationchanges

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

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;

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);

ForeignKeys

ReferentialSets– userdefined

VirtualForeignKeys– systemdefined

AllowuseofstandardSQL

Enforcereferentialconstraintenforcement

StandardODBCandJDBCmetadata

Compatibilitywithcommontools

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

ReferentialSets

Exposesetsasreferentialconstraints••Queries••Updates

Basedonrecordkeyvalues••Primarykey=UNIQUECALCorsystemownedindex••Foreignkey=columnvalueinmemberrecord

Defineonnetworkschemasetdefinition

VisiblethroughJDBCandODBCmetadata

Enableaccessforcommontoolsandapplicationframeworks

UseSQLtoaccessandmaintainnetworkdatabasesfromJava

EMPSCHMwithReferentialSets

DEPT-ID-0410

DEPARTMENT

ORG-DEMO-REGION

410 F 120 CALC

EMP-ID-0425DEPT-ID-0415

EMP-ID-0415

EMPLOYEE

EMP-DEMO-REGION

EXPERTISE

EMP-DEMO-REGION

425 F 16 VIA

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)

ImplementingReferentialSets

Defineprimaryandforeignkeysforset

Mayneedtorestructurememberrecord

Populateforeignkeysinmember

Maintainforeignkeysinmember

Alternativetomigration

••Candoincrementally••Lesscost••Lesseffort

VirtualForeignKeys

Exposesetsasreferentialconstraints••Queries••Updates

BasedonROWID••Virtualprimarykey=ROWID••Virtualforeignkey=FKEY_<set_name>

DefineonSQLschemadefinition

VisiblethroughJDBCandODBCmetadata

Enableaccessforcommontoolsandapplicationframeworks

UseSQLtoaccessandmaintainnetworkdatabasesfromJava

VirtualForeignKeyBenefits

§ UsestandardSQLtoaccessandupdateCAIDMS– RemovesINSERTandUPDATElimitations– CapabilitieslikenetworkDML– WriteapplicationsusingJava,.NET,andpopularframeworks– NospecialCAIDMSSQLsyntax

§ Easytoimplement– Nodatabasechanges– Nochangestonetworkdefinitions– Noneedfortableprocedures

EnablingVirtualForeignKeys

§ “Create”virtualkeyswithSQLschemadefinitioncreateschemaEMPSQL

fornonsql schemaEMPDICT.EMPSCHMversion100withvirtualkeys

§ ROWIDandvirtualforeignkeysbecomevisible– SELECT*– INSERTcolumnlistwhenlistisomitted– UPDATEcolumnlistwhenlistisomitted

§ “Remove”virtualkeysalterschemaEMPSQLwithoutvirtualkeys

EMPSCHMWithVirtualForeignKeys

DEPT-ID-0410

DEPARTMENT

ORG-DEMO-REGION

410 F 120 CALC

FKEY_EMP_EXPERTISEFKEY_DEPT_EMPLOYEE

EMP-ID-0415

EMPLOYEE

EMP-DEMO-REGION

EXPERTISE

EMP-DEMO-REGION

425 F 16 VIA

ROWID ROWID ROWID

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)

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

CAIDMSServer

§ CAIDMSasaServer

§ ODBCdriver

§ JDBCdriver

§ DynamicSQL

ODBCOpenDataBaseConnectivity

CallLevelInterface

Interoperability

MicrosoftWindows

ODBC.NET

ODBCArchitectureWindows

ODBC ApplicationProgram Interface

ODBC ServiceProvider Interface

Network and DBMS

ODBCApplication

ODBC DriverManager

ODBCDriver

DataSource

ODBCDriver

DataSource

CAIDMSODBCDriver

§ ODBC3.5

§ Wireprotocolcommunications

§ 32and64-bitdrivers

JDBC“JavaDataBaseConnectivity”

CallLevelInterface

Objectoriented

Interoperability

AnyJavaplatform

JDBCArchitecture

JavaVM

Java Application

DriverManager

DriverInterface

Networkand DBMS

Driver

CAIDMSServerArchitectureODBCandJDBC

SQLClientInterface

ODBCDriver JDBCDriver

SQLEngine

NetworkDBMS

JDBCDriverTypes

Type 1

Native

Type 2

Type 4

Middleware

Type 3

CAIDMSJDBCDriver

§ JDBC4.0

§ “Universal”driver– Types2,3,4

§ 64-bitType2Driver

§ Type4JDBCDriver– DirectconnectionfromJDBCdrivertoCV

§ DistributedXAtransactions

Type4JDBCDriver

CVJava

TCP/IP

JDBCDriver

NativeClient

JDBCServer

Type4Server

TCPIPLineDriver

JSRVServerTask

SQLClient

SQLEngine

GenericListener

SYSGENServerDefinition

ADD PTERM TCPJSRVTYPE IS LISTENERPORT IS 3799TASK IS RHDCNP3JMODE IS SYSTEMPARM IS 'TASK=IDMSJSRV'.

ADD LTERM TCLJSRVPTERM IS TCPJSRV.

XAResourceManager

TCPIPLineDriver

JSRVServerTask

SQLClient XAClient

SQLEngine TransactionManager

GenericListener

CAIDMSServerCAIDMSServerenablesWindowsandJavadeveloperstouseSQLaccessandupdateCAIDMSdatabasesandapplications.

CAIDMSCAIDMSSQLandCAIDMSServerhelpusersleverageandextendtheirinvestmentinCAIDMSapplicationsanddata.

CAIDMSSQLCAIDMSSQLenablesdeveloperstouseSQLtoaccessandupdatenetworkdatabasesaswellastocreatenewSQLdefineddatabases.

Summary

MainframeandWorkloadAutomation

FormoreinformationonMainframeandWorkloadAutomation,pleasevisit:http://cainc.to/9GQ2JI

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

MustSeeDemos

DemoNameProductXTheater#location

DemoNameServicesYTheater#location

DemoNameSolutionYTheater#location

DemoNameProductXTheater#location

Questions?

Thankyou.

Stayconnectedatcommunities.ca.com

Thecontentprovidedinthis CAWorld2016presentationisintendedforinformationalpurposesonlyanddoesnotformanytypeofwarranty. The informationprovidedbyaCApartnerand/orCAcustomerhasnotbeenreviewedforaccuracybyCA.

ForInformationalPurposesOnlyTermsofthisPresentation

pre-con ed: using sql to access your ca idms databases

Technology

ca idms tm sql routines: an overview july 22, 2008 cal j....

a comparison of sql and nosql databases databases

hsq - databases & sql

no sql databases

unlike sql databases, - polito

no-sql databases

databases & microsoft sql server

databases : sql-introduction

develop sql databases with eclipse, sqlexplorer, … ·...

databases 2010 embedded sql

android chapter17 sql databases

some special issues (c) idms/sql news

ca telon application generator idms database sql option...

sql server 2008 databases

lecture 08 sql databases 9452

databases, sql and ms sql server

sql vs. nosql databases

relational databases and sql

characteristics of no sql databases

ca idms™ sql - webinterface idms 18 5... · for users new...