ca idms™ 19.0 sql enhancements for modernization

World®’16

CAIDMS™Version19.0SQLEnhancementsforModernizationDaveRoss,SeniorPrincipalProductOwnerCATechnologies

MFX103S

MAINFRAMEANDWORKLOADAUTOMATION

Thecontentprovidedinthis CAWorld2016presentationisintendedforinformationalpurposesonlyanddoesnotformanytypeofwarranty. The informationprovidedbyaCApartnerand/orCAcustomerhasnotbeenreviewedforaccuracybyCA.

ForInformationalPurposesOnlyTermsofthisPresentation

Abstract

CustomersneedtoleveragetheirinvestmentinCAIDMS™tocreatenewapplicationsbasedonservicesintheapplicationeconomy,andtheyneedtodothiswithcurrenttechnologiesanddeveloperskillsets. ThissessionshowshowSQLenhancementsinCAIDMS19.0improvestandardscompliance,developerproductivity,andcompatibilitywiththirdpartytoolsandapplications. TheSQLvirtualforeignkeyfeatureenablesdeveloperstousestandardSQLtoaccessandupdatenetworkdatabaseswithouttheneedtousenetworkDMLortableprocedures. EnhancementstoSQLDDLenableuserstodefinedatabasesusingstandardDDLcompatiblewithotherdatabases.

DavidRoss

CATechnologiesSr.PrincipalProductOwner,CAIDMS

Agenda

SQLVIRTUALFOREIGNKEYS

ISOSTANDARDSQLCONSTRAINTDDL

CAIDMSSQLRelationaltoNetworkMapping

••IdenticallynamedTable

••EquivalentRow

••AutomaticallyrenamedColumn

••PartialmappingReferentialconstraint

••ViaSCHEMARecorddefinition

••EquivalentRecordoccurrence

••ExceptODO,redefines,…Element

••LackofforeignkeysSet

SQL Network

RelationaltoNetworkMappingTechniques

Syntaxextensions

Procedures••Tableprocedures••Calledprocedures

Foreignkeys••Referentialsets

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

StandardSQL Newprograms ExposesSetsApplicationchanges

Restructure

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

VirtualPrimaryKey

§ PseudocolumnROWID– Uniquelyidentifiesarow– Availableforeverytable– Notnullable

§ DatatypeROWID– Length8bytes– ContainsDBKEYandPageInfo– Valuenotpersistentandthusnotatrueprimarykey– Relationaloperatorsupportextended(<,>,<=,>=)

VirtualForeignKey

§ PseudocolumnFKEY_setname– “setname”istheSQLtransformednameofset– ValueisROWIDofownerofset-nameorNULLifnomembership– Availableforeverysetinwhichrecordismember– Alwaysappearsnullable,butcannotsettoNULLforMAsets

§ DatatypeROWID– Mustbe8bytes– containsDBKEYandPageInfoofsetowner

VirtualForeignKeys

§ VirtualforeignkeyscanbereferencedinSQLstatementslikeauser-definedcolumn

§ Includedinlistof“all”columns(SELECT*)

§ IncludedinINSERTandUPDATEdefaultcolumnlist– Whencolumnlistisexcluded– Columnorder:

§ Userdefinedcolumns§ ROWID§ Virtualforeignkeysinalphabeticalorder

EnablingVirtualForeignKeys

§ “Create”virtualkeyswithSQLschemadefinitioncreateschemaEMPSCHM

fornonsql schemaEMPDICT.EMPSCHMversion100withvirtualkeys

§ ROWIDandvirtualforeignkeysbecomevisible– SELECT*– INSERTcolumnlistwhenlistisomitted– UPDATEcolumnlistwhenlistisomitted

§ “Remove”virtualkeysalterschemaEMPSCHMwithoutvirtualkeys

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

400 F 16 VIA

ROWID ROWID ROWID

SELECTExamples

§ ObtainDEPARTMENTdataofanEMPLOYEEselect*fromDEPARTMENTwhereROWID=(selectFKEY_DEPT_EMPLOYEEfromEMPLOYEEwhere EMP_ID=1001)

§ JoinDEPARTMENTandEMPLOYEEselectd.*,e.*fromDEPARTMENTd,EMPLOYEEewhered.ROWID =e.FKEY_DEPT_EMPLOYEE

MoreSELECTExamples

§ SelectallEXPERTISErecordsforanEMPLOYEEselect*fromEXPERTISEwhereFKEY_EMP_EXPERTISE=

(selectROWIDfromEMPLOYEEwhereEMP_ID=1001)

§ ShowallDEPARTMENTsincludingthosewithoutEMPLOYEEsselectd.*,e.*fromDEPARTMENTdleftjoinEMPLOYEEeond.ROWID =e.FKEY_DEPT_EMPLOYEE

INSERTUsingVirtualKeys

§ Usetoestablishsetmembership– SetvirtualforeignkeycolumntoownerROWIDorNULL– Setmembershipoptionsdefineintegrityconstraints– MAsetdisallowsNULLvaluedvirtualforeignkey

§ UseROWIDtosuggestDBKEYforaDIRECTrecord– Defaultsto-1onSTORE(firstavailableDBKEYinpagerangeused)– IgnoredifrecordlocationmodeisnotDIRECT

INSERTUsingVirtualKeys

§ Useofscalar-query-expressioninVALUESclause– NewR19.0featureforINSERTstatementsforallSQLschemas– Withvirtualkeys,allowsINSERTtocontainqueriesthatretrievethe

virtualforeignkeyvaluestobesetintheinsertedrow

§ Supportforvirtualkeysinquery-specification– AlternativetoVALUESclauseonINSERT– QueryresultmusthavesamenumberofcolumnsasnamedinINSERT

statement– Ifnocolumnsnamed,numberofcolumnsmustmatchthetotal

numberofcolumns,includingROWIDandallVFKs

INSERTExamplesVALUESClauseWithColumnList

insertintoEMPLOYEE(EMP_ID,EMP_FNAME,EMP_LNAME,FKEY_DEPT_EMPLOYEE,FKEY_OFFICE_EMPLOYEE)

values(976,’SEBASTIAN’,’VOLLMER’,(selectROWIDfromDEPARTMENTwhereDEPT_ID=9003),(selectROWIDfromOFFICEwhereOFFICE_CODE='002'))

INSERTExamplesVALUESClauseWithoutColumnList

insertintoEMPLOYEEvalues

(976,’SEBASTIAN’,’VOLLMER’,NULL,(selectROWIDfromDEPARTMENTwhereDEPT_ID=9003),(selectROWIDfromOFFICEwhereOFFICE_CODE='002'))

§ EMPLOYEEcolumnsareEMP_ID,EMP_FNAME,EMP_LNAME,ROWID,FKEY_DEPT_EMPLOYEE,FKEY_OFFICE_EMPLOYEE

INSERTExamplesSpecifyingVirtualKeysUsingQuery-specification

insertintoEMPOSITION(START_YEAR,FKEY_EMP_EMPOSITION,FKEY_JOB_EMPOSITION)

select2015,E.ROWID,J.ROWIDfromEMPLOYEEE,JOBJwhereE.EMP_ID=23andJ.JOB_ID=2025

UPDATE

§ VirtualForeignKeyscanbespecifiedinSETclause

§ EquivalenttoCONNECT/DISCONNECTDMLverbs

§ Allowedforsetswithmembershipoptions– OPTIONAL(OA/OM)– MANDATORYMANUAL(MM)whenrecordnotyetconnectedtoset

§ SETFKEY_setnamevaluesallowed– NULL– ROWIDofowneroccurrenceofsetname– ROWIDofmemberoccurrenceofsetname

UPDATEExamples

§ DisconnectingtheEMPLOYEEfromtheDEPT-EMPLsetupdateEMPLOYEEsetFKEY_DEPT_EMPL=NULL

whereEMP_ID=23

§ EMPLOYEEconnectedtoDEPARTMENT4000updateEMPLOYEEsetFKEY_DEPT_EMPL=

(selectROWIDfromDEPARTMENTwhereDEPT_ID=4000)whereEMP_ID=23

DELETE

§ Usevirtualkeycolumnsthesameasuser-definedcolumns

deletefromEMPLOYEEwhereFKEY_DEPT_EMPL=

(selectROWIDfromDEPARTMENTwhereDEPT_NAME=‘Sales’)

DiscoveringVirtualKeys

§ JDBCandODBCmetadataAPIsexposevirtualkeys

§ java.sql.DatabaseMetaData interfacemethods– getPrimaryKeys– getExportedKeys– getImportedKeys– getCrossReference

§ ODBCAPIfunctions– SQLPrimaryKeys– SQLForeignKeys

DiscoveringNetworkRelationships

VirtualKeysComparedtoOtherMappingTechniques

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

SQLDDLEnhancements

§ ISOStandardConstraintDefinitionDDL– CREATETABLE– ALTERTABLE– Uniqueconstraints– PrimaryKeyconstraints– Referentialconstraints

§ Improvesintegrationwithframeworksandtools

§ DISPLAYTABLE

DefiningUNIQUEConstraints

§ Auniqueconstraintisspecifiedbycoding– UNIQUEonacolumndefinition– UNIQUE(<column-names>)inthecolumnlist

§ Aconstraintmaybenamed– CONSTRAINT<name>– Precedestheconstraintdefinition– Nameisgeneratedifnotnamed

§ Morethanoneuniqueconstraintmaybedefined

UNIQUEConstraintExample

createtableABC.TABLE1(COL1CHAR(8)unique,COL2CHAR(8),constraintCON1unique(COL2,COL1))

DefiningPrimaryKeyConstraints

§ APrimaryKeyconstraintisspecifiedbycoding– PRIMARYKEYonacolumndefinition– PRIMARYKEY(<column-names>)inthecolumnlist

§ Aconstraintmaybenamed– CONSTRAINT<name>– Precedestheconstraintdefinition– Nameisgeneratedifnotnamed

§ ONEPrimaryKeyconstraintpertable

PrimaryKeyConstraintExamples

createtableABC.TABLEX(COL1char(8)notnullprimarykey,COL2char(8)notnull,COL3char(8)notnull)

createtableABC.TABLEY(COL1char(8)notnull,COL2char(8)notnull,COL3char(8)notnull,primarykey(COL2,COL3))

GeneratedEnforcingIndex

§ Enforcesuniqueandprimarykeyconstraints

§ CreatedbyCREATETABLEandALTERTABLE– Suppressescreationofdefaultindex– Usesdefaultvaluesforindexattributes– CannotbealteredtobeNOTUNIQUE– CannotbedroppedwithDROPINDEXunlessreplacementdefined

§ GeneratedIndexnames– IDX###############– ###isa15digitnumberuniqueintheschema

ReplacingaGeneratedEnforcingIndex

§ UseCREATEUNIQUEINDEX

§ Columnsmustmatchenforcingindex

§ Inheritsenforcingindexattribute

§ Whenprimarykeyindexdropped– Areplacementinheritstheprimarykeyattribute

§ Generatedindexesdroppedautomatically

§ Non-generatedindexesmustbemanuallydropped

DefiningReferentialConstraints

§ Areferentialconstraintisspecifiedbycoding– REFERENCESclauseonacolumndefinition– FOREIGNKEYclauseinthecolumnlist

§ Aconstraintmaybenamed– CONSTRAINTname– Precedestheconstraintdefinition– Ifnotnamed,anameisgenerated

§ GEN###############

§ Morethanonereferentialconstraintmaybedefined

ReferentialConstraintsExample

createtableABC.TABLEX(COL1char(8),COL2char(8),foreignkey(COL1,COL2)referencesTABLEP,COL3char(8)notnull

constraintCON1referencesTABLEP(COL5))

DiscoveringConstraints

§ java.sql.DatabaseMetaData interfacemethods– getIndexInfo– getPrimaryKeys– getExportedKeys– getImportedKeys– getCrossReference

§ ODBCAPIfunctions– SQLStatistics– SQLPrimaryKeys– SQLForeignKeys

StandardSQLDDLSQLDDLenhancementsenableCAIDMStosupportdatabasedefinitionsprovidedbypopulartoolsandframeworks

CAIDMS19.0CAIDMS19.0enablesJavaandWindowsdeveloperstouseindustrystandardtechnologytocreate,access,andmaintainCAIDMSdatabases

SQLVirtualKeysSQLVirtualForeignKeysenabledeveloperstousestandardSQLtoaccessandupdatenetworkdatabases

SummaryAFewWordstoReview

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?

Stayconnectedatcommunities.ca.com

Thankyou.

MainframeandWorkloadAutomation

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

ca idms™ 19.0 sql enhancements for modernization

Technology

ca idms™ task analyzer idms 18 5... · the ca idms task...

ca ads™ for ca idms™ idms 18 5 user bookshe… · ca...

a idms™ 19.0 web services

data-driven decision-making solutions idms - … · 1 idms...

ca idms buffer tuning

overview vg-idms gateway extending ca-idms · 4...

according to ftpdocs.broadcom.comca idms dmlo is an...

ca idms r17.0 indexing enhancements january 22, 2009 dick...

idms overview

fme and idms

introduction to idms-ppt

ca idms - 19 · ca idms/dc sort overview this section...

ca idms - 19 · system overview ca idms dictionary loader...

ca idms - 19 · ca idms - 19.0 15-jan-2018 3/65 this...

ca idms - 19 fileca idms - 19.0 15-jan-2018 3/47 this...

smart grid and idms

release notes - ftpdocs.broadcom.com idms 18 5... · a...

ca idms™ version 19.0 new features and enhancement

secure idms for cloud computing environment - diva...

11. idms migration overview