advanced dba – high availability for data
TRANSCRIPT
![Page 1: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/1.jpg)
World®’16
AdvancedDBA– HighAvailabilityforDataKevinShumaVP,ProductManagementCATechnologies
MAINFRAMEANDWORKLOADAUTOMATION
MFX65E
![Page 2: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/2.jpg)
2 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Abstract
ThissessiondescribestheavailableoptionsforsitesthatneedtokeeptheirCADatacomdatauseraccessibleforextendedperiodsoftime.Thisincludefunctionalitytoreorganize,compressandmovedatawithoutinterruptinguseraccess.
ThissessionwillprimarilycoverthenewfeaturesavailableintheCADatacom®/DButilityprocessingthatallowsuserstocontinuetoaccessdatatableswhiletheDBAisperformingneededdatabasemanagementactivities.
KevinShuma
CATechnologiesVP,ProductManagement
![Page 3: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/3.jpg)
3 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Agenda
WHATISHIGH-AVAILABILITYFORDATA?
FUNCTIONALITYAVAILABLETOKEEPDATAAVAILABLE
CURRENTLYPLANNEDFUNCTIONALITY
SUMMARY
1
2
3
4
![Page 4: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/4.jpg)
4 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Whatishigh-availability?
“Highavailabilityisacharacteristicofasystem,whichaimstoensureanagreedlevelofoperationalperformance,usuallyuptime,forahigherthannormalperiod.”
“Ifausercannotaccessthesystem,itis- fromtheuserspointofview– unavailable.Generally,thetermdowntimeisusedtorefertoperiodswhenasystemisunavailable.”
AccordingtoWikipedia
24x7
![Page 5: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/5.jpg)
5 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Whatishigh-availabilityfordata?
§ Theabilityforuserstoaccesstheirdata(tables)anytimetheDatacomenvironment(MUF)isup– Donotinterruptuserdataaccessfor
§ Datasetexpansions§ Datareorganizations§ Indexreorganizations(defragmentation)§ Indexchanges(addingordeletingkeydefinitions)§ Changingcompressionalgorithms§ DASDrestructuringanddatarowrestructuring§ JustabouteverythingaDBA“does”tomanagethesystem
FromaCADatacomperspective
![Page 6: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/6.jpg)
6 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Functionalityavailabletokeepdataavailable
![Page 7: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/7.jpg)
7 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Datasetexpansions
§ CADatacomsupportsmultiplewaystoexpanddataandindexareaswithoutanoutage– Dynamicextend(dictionaryattributes)
§ Canbeupdatedtoexistingdatabasedefinitions§ ProvidestheMUFtheabilitytotriggerarequestforanewextentwhenthecurrentextent(s)arefull
– Ondemandextend§ ConsolecommandthattriggersaMUFtorequestanewextent
– CanbeplacedinaDBUTLTYbatchjob
§ Doesnotrequiredynamicextendtobeturnedon
Weneedourdataandindexareastoexpand(fornewentries)withoutanoutage
![Page 8: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/8.jpg)
8 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Datasetexpansions- OnlineAreaMoveprocess
§ DeliveredinV14.01
§ Usedforcaseswhereoriginaldataset(indexorarea)nolongermeetsthecurrentneed
§ Extentstoosmall– maximumnumberofdynamicextendsbeingreached§ Notenoughvolumesallocated– canonlyextendonallocatedvolumes§ Needtomovetoanewer,larger,fasterDASDdevice§ Sitesdoesnotwantdatasetswithmultipleextentstoremainforlongerperiods
§ Otherneeds
Weneedourdataandindexareastoexpand(fornewentries)withoutanoutage
![Page 9: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/9.jpg)
9 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Datasetexpansions- OnlineAreaMoveprocess(cont’d)
§ DefineandPREINIT(DBUTLTY)new“.new”targetdataset– Createwiththe“new”requirementsforextentsandvolumes– Sameblocksizeassource
§ WhenreadyissueaconsolecommandtodoONLINE_AREA_MOVE– Opens“.new”datasettoMUF– EstablishesanIOdirectortomanagewhichdatasethascurrentblock– Beginscopyblockforblockfromolddatasetto.new– WhendoneusesanI/Ogatetopausethesystem
§ Scratchorrenamecurrenttooldandrename“.new”tocurrent
Weneedourdataandindexareastoexpand(fornewentries)withoutanoutage
![Page 10: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/10.jpg)
10 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Datasetreorganizations
§ Determineifareorganizationisneeded– Datarowreuseallowsspaceindatasetstobereused
§ Defaultre-useisrandom(DSOP=1)§ Newdatarowsbeingaddedmaybeplacedoutof(nativekey)sequence§ Withcompressionormulti-tableareassomefragmentationoffreespaceinthedatablocksmayoccur
– Withgoodbufferingand/orMRDFalotofthedis-organizationishidden
– Manyreorganizationsaredonewithoutrealneed§ Don’timproveperformanceoraffectfragmentation
Weneedourdataputbackintonativesequence(forbatchperformance)
![Page 11: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/11.jpg)
11 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Datasetreorganizations(cont’d)
§ Usingthehistorydatabase(DBID1007)todetermineif– Thereissignificantsequential(nativekey)processing
Weneedourdataputbackintonativesequence(forbatchperformance)
![Page 12: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/12.jpg)
12 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Datasetreorganizations(cont’d)
§ Usingthehistorydatabase(DBID1007)todetermineif– Thereissignificantreductioninsequentialefficiency
Weneedourdataputbackintonativesequence(forbatchperformance)
Re-orgdoesnotimproveefficiency
Re-orgimprovesefficiency
![Page 13: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/13.jpg)
13 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Datasetreorganizations(cont’d)
§ UsingtheDBUTLTYDATANEreporttodetermineifthedataisoutofsequence
Weneedourdataputbackintonativesequence(forbatchperformance)
![Page 14: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/14.jpg)
14 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Datasetreorganizations(cont’d)
§ UsingtheDBUTLTYDATASPreporttodetermineifthedatablockshavealotoffragmentedspace
Weneedourdataputbackintonativesequence(forbatchperformance)
![Page 15: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/15.jpg)
15 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Datasetreorganizations(cont’d)
§ OfflineReorg(causesanoutage)– DBULTYBACKUP(SEQ=Native),INIT,LOAD– DBUTLTYREORG(parallelreorg)– AREAcontrolscanbeusedtodojustoneareaofadatabase
§ OnlineReorg– TriggeredbyDBUTLTY,butrunsinMUF(availableforzIIP)– Allowsuserstocontinuetoaccessdata– Movesrowswithintheexistingdataarea– Someindexspacewillbeused(MovedRecordIndex)– Fullyrecoverable/restart-able
Choosingtherightmethod
![Page 16: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/16.jpg)
16 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Datasetreorganizations(cont’d)
§ OnlineReorg(cont’d)– DBUTLTYOLREORGREFGROUP=nn(greaterthanzero)
§ Reorganizesbymovingrowsfromoneblocktoanother§ Makessequence“better”
– DBUTLTYOLREORGREFGROUP=0(RequiresDSOP4or5)§ Reorganizesbymovingrowsinordertoemptyblocks§ Requiresapercentageofempty(notin-use)blocks§ Allnewblocksarefullyloaded§ Typicallyresultsinsmallerwellorganizeddatatable
Choosingtherightmethod
![Page 17: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/17.jpg)
17 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Indexreorganizations(defragmentation)
§ OfflineRETIX(causesanoutage)– DBULTYINITandRETIXoftheindex– AREAcontrolscanbeusedtodojustoneareofadatabase
§ OnlineDEFRAG– TriggeredbyDBUTLTY,butrunsinMUF(availableforzIIP)– Allowsuserstocontinuetoaccessdata– Defragments/combinesDXX(low-level)ofindex– Reducesin-useblocks,improvesperformance– Fullyrecoverable/restart-able– Doesnotreduce“levels”
Choosingtherightmethod
![Page 18: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/18.jpg)
18 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Indexreorganizations(defragmentation)(cont’d)
§ OnlineDEFRAG(cont’d)– Lowcost,runsinMUF– Compressesindexes(reducesin-useblocks)– Improvedperformanceofbuffers
Useitfreely
![Page 19: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/19.jpg)
19 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Indexchanges(addingordeletingkeydefinitions)
§ PriortoV15– OnlyonlineabilityisthroughSQLALTERtable
§ Causessignificantdatarow“lock”iflargenumberofrows§ Notrecommendedforproduction
§ DeliveredinV15– Onlinefunctionality
§ Abilitytoremoveakey§ Abilitytoaddakey
Addorremovingindexesfromexistingtableswhileprocessing
![Page 20: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/20.jpg)
20 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Indexchanges(cont’d)
§ Removingakey– Logicallydeleteakey(index)foratable
§ Internalindexstillmaintainedwhenrowsaddedordeleted§ Canbere-instatedifusersdetectthatthekeywasneeded
– Physicallydeleteakey(index)foratable§ Locksinlogicaldelete§ Removesindexentriesfromtheindexarea(IXXorInn)
– Spaceavailableforotherindexestouse
Removingindexesfromanexistingtablewhileprocessing
![Page 21: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/21.jpg)
21 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Indexchanges(cont’d)
§ Addinganewkey(index)foratable– AddskeytoDirectoryCXX– Keyenabledtomaintenancecommands– Internaltaskstartedtoreadallrows,addnewindex
§ Whencompletekeyismadeavailable– Canbeaddedintoindex–
§ IXXarea§ ExistingInn area(multi-datasetindex)§ BrandnewInn area(multi-datasetindex)
Addingindexestoanexistingtablewhileprocessing
![Page 22: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/22.jpg)
22 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Indexchanges(cont’d)
§ Swapwhereakey(index)resides– Addaparallelkeytoonebeingusedin
IXXorInn– Swapusageforuserapplicationsfrom
oldtonew– Deleteoldkeyandreclaimspace– LesslevelsreducesI/O– AllowsCOVEREDtobemuchmore
effective§ CoverthespecifickeyIDsthataremost
‘timecritical’
Movingindexeswithinanexistingtablewhileprocessing
Multi-datasetindex
IXX
I01
I01
IXX
![Page 23: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/23.jpg)
23 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Changingtablecompression
§ Datarowcompressioncanbeusedto– Reducedatarowfootprint(saveDASD)– Improvebuffer,memoryandzIIPutilization– Makedataless“readable”byexternalviewers
§ TSO,DITTO,SORT,etc.
§ OnlineCompress(TA24)– AllowstheDBAtoadd/remove/changecompression
specificationswithoutinterruptinguserdataaccess– FullsessionatCAW2016discussingthisnewcapability
AllowingtheDBAtochangecompressionwithoutadataaccessoutage
![Page 24: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/24.jpg)
24 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
OtherchangesinV15
§ MUFRESTARTinparallel,muchfaster
§ DynamicanddirectedextendVIRTUALbasearea– IncludestheCBSindex– IncludestheTTMindex
§ ExtendthelogareaLXX
§ ExtendtheDirectoryCXX
§ UniqueRowIdentifier(URI)reuse
RemovingMUFoutagesthataffectuserdataaccess
![Page 25: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/25.jpg)
25 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Currentlyplannedfunctionality
![Page 26: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/26.jpg)
26 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
UnderConsideration
§ ONLINE_AREA_RELEASE– Freeunused/neverusedspaceatbackofdataset
§ Dataareaonly– Inasecondcanreleasespacebacktosystem
§ PastMaximumusedblocks§ PastHighUsedBlock§ GoodforareasthatwereoversizedatINIT
Itemsthatareinthecurrentbacklogofnewfunctionality
![Page 27: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/27.jpg)
27 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
UnderConsideration(cont’d)
§ ONLINEBlockSizeAlter(Pre-initializeanewdataarea– PartONLINE_AREA_MOVE,PartOLREORG
§ Pre-initializeanewdataarea– “Right-sized”extents
§ Differentblocksize§ Datadictionarydriven§ Movesrowsbynativekeytonewarea
– AnyADDITduringalsogoestocurrentblocksonotinorder
§ Whendoneoldareascratched,newarearenamed
Itemsthatareinthecurrentbacklogofnewfunctionality
![Page 28: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/28.jpg)
28 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
UnderConsideration(cont’d)
§ ONLINE_TABLE_MOVE– PartONLINE_AREA_MOVEandpartOLREORG
§ Pre-initializeanewdataarea– “Right-sized”extents
§ Imagineoldarea1000cylindersbutcurrently1recordonlasttrack
– Newareacanbe2tracksandwillrunveryfast
§ Movesrowsbynativekeytonewarea– AnyADDITduringalsogoestocurrentblocksonotinorder
§ Whendoneoldareascratched,newarearenamed
Itemsthatareinthecurrentbacklogofnewfunctionality
![Page 29: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/29.jpg)
29 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
SummaryAFewWordstoReview
ExperiencesOnesitehasusedthesenewfunctionstokeeptheiruserdataavailable24x7forover4years.
KeyTopicsIntoday’sworld,user’sdatamustbeaccessible24x7
FindingsCADatacomcontinuestodelivernewfunctionalitythatallowstheDBAtoperformneededactivitieswithoutinterruptinguseraccess.
![Page 30: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/30.jpg)
@CAWORLD#CAWORLD ©2016CA.AllRIGHTSRESERVED.30 @CAWORLD#CAWORLD ©2016CA.AllRIGHTSRESERVED.30
MainframeandWorkloadAutomation
FormoreinformationonMainframeandWorkloadAutomation,pleasevisit:http://cainc.to/9GQ2JI
![Page 31: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/31.jpg)
31 ©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
MFX98SUsingSysview tomonitorandmeasureyourCADatacomenvironment– ADandDB
11/17/2016at3:00pm
MFX99S BirdsofaFeather/StumptheTechie! 11/17/2016at3:45pm
![Page 32: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/32.jpg)
32 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Questions?
![Page 33: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/33.jpg)
33 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
Thankyou.
Stayconnectedatcommunities.ca.com
![Page 34: Advanced DBA – High Availability for Data](https://reader034.vdocuments.us/reader034/viewer/2022042723/58ef2a341a28abb0538b4657/html5/thumbnails/34.jpg)
34 ©2016CA.ALLRIGHTSRESERVED.@CAWORLD#CAWORLD
©2016CA.Allrightsreserved.Alltrademarksreferencedhereinbelongtotheirrespectivecompanies.
Thecontentprovidedinthis CAWorld2016presentationisintendedforinformationalpurposesonlyanddoesnotformanytypeofwarranty. The informationprovidedbyaCApartnerand/orCAcustomerhasnotbeenreviewedforaccuracybyCA.
ForInformationalPurposesOnlyTermsofthisPresentation