how to get the most out of your db2 and analytics investment · how to get the most out of your db2...

World®’16

HowtoGettheMostoutofYourDB2andAnalyticsInvestmentRonColmone @RonColmoneCATechnologies #CAWorldSr ConsultingArchitect

MFX81E

MAINFRAMEANDWORKLOADAUTOMATION

Abstract

ThissessionwillfocusonanewmainframeDB2®toolscustomeroranon-DB2customer

interestedinDB2solutionsfromCATechnologiesandhelpingthemunderstandthevalue

propositionandthereal-lifeusecases(administration,changemanagement,performance

optimization,monitoring,etc.)whereDB2toolshelpcustomersreduceoperationalcosts,reduce

risksassociatedwithdowntime/availability,improveservicelevelsandimproveuser

productivity.

RonColmone

CATechnologiesSr ConsultingArchitect

AgendaCLONINGUSINGCARC/MIGRATOR™FORDB2FORZ/OS

DB2REALTIMESTATS(RTS)FORAUTOMATION

TIPSANDHINTSWITHTHETOOLS

PACKAGECOMPAREINTEGRATIONWITHCHANGECONTROL

DETECTORCOLLECTIONANDREPORTINGPROFILES

MANAGEDB2ANALYTICSACCELERATOR(IDAA)

CARC/QUERY®FORDB2FORZ/OSHDDLLINE COMMAND5

UseCase:CloningusingRC/Migrator(Merger)

CARC/Migrator(Merger)

§ MoveorCopyTable/IndexSpacesfromoneSubsystemtoanother(couldbesameSSID)

§ SharedDASDrequiredbetweensubsystems

§ Norequirementtounloadandreloadtables

§ CopytheVSAMdatasetsfromsourcesubsystemtotargetincludingcatalogstatistics

§ Flashcopy supportedtoeliminateoutageonsourcesystem

§ OBIDscanbereservedortranslatedinplaceontargetsystem

CloningDatausingCARC/Migrator– MStrategy

CARC/Migrator(Merger)

§ CustomerinitiallyusedUnload/LoadMigrationprocesstomovedatafromProductiontoTest

§ SecondphasewastoremoveunnecessaryIndexesfromthemoveprocess

§ ThenconvertedMigrationstrategytouseRC/Merger– largereductioninprocessingtime

§ ImplementedFlashCopy Support(RC/MergerR19feature)

Customercasestudy

CARC/Migrator(Merger)CustomerstatisticsmovingfromstandardUnload/LoadmethodtoRC/Merger

Note:Cloningprocessisnowhandling2-3x

thedataoriginallyprocessed

UsingUnload/LoadProcessing

Removedsomeunnecessaryindexes

ConvertedtoRC/Mergerprocess

ImplementedSNAPSHOTwithRC/Merger

CARC/Migrator(Merger)CloningDatausingCARC/Migrator– MStrategy

SpecifyRC/MergerMove/CopyAnalysisStrategyOptions.

ReserveOBIDS=YwillremoveneedtotranslateOBIDsunlesscollision.

SourceExecutionofReadOnlyensuresconsistentcopy.

GlobalChangesallowobjectattributestobealteredontargetsystem.

CopySourceTablespaces toTargetTablespaces.

RC/MergerwillallocateTargetVSAMdatasetsifrequired.

CallRC/MergertoReserveOBIDsifavailable.

UseCase:DB2RealTimeStatistics(RTS)forAutomation

DB2RealTimeStatsforAutomationDatabaseAnalyzer– ObjectSelectionusingRTS

§ DB2ObjectselectionbasedonRTSusingReorgbestpracticeselectioncriteria(DSNACCOXlike)

§ ExecutionofPDAExtractProceduretogenerateOnlineReorgsJOBsforselectedTablespacestiedtoObjectSelectionProfile

§ OnlineReorgJCLgeneratedintotargetlibraryforeachobjectselectedbasedonbestpracticeselectioncriteria.

UseCase:PackageCompareIntegrationWithChangeControl

PPAPackageCompareIntegrationWithChangeControlIdentifyingAccessPathChanges

PPAPackageCompareIntegrationWithChangeControl

§ AddBatchProcessstepstoChangeControlprocessinordertodetectSQLperformanceimpactduetoSQLchanges

§ CurrentExplainofPackagecanbeusedtocreatetheBaseline

§ FutureExplainwithComparisontoCurrentExplaintodetectimpactofSQLchanges

§ OptionscanbespecifiedtosetThresholdvalueoncostcomparison

CostCompareanalysis

CVCCOPTScontainsoptionsforCostComparereportincludingthresholds.

AddJCLsteptoChangeControlProcesstoComparebaselinetofutureexplaininordertoanalyzeaccesspathchangesofpackage.

PackagecompareofOldversusNewpackagecompareresultedin26improvedSQLstatements.

CostofSQLstatementwentfrom60to1(ms)sinceAccesstypechangedfromTS_SCNtoIXDATA.

PPA– NewR19FeaturetoSimplifyCompareProcess

§ RebindCompareAnalysis(RCA)commandtosimplifycomparecurrent/futureaccesspathsforapackage

§ IssuesCurrentExplainofPackage,followedbyFutureExplain

§ ComparesFuturetoCurrentexplainresultsandgeneratestheRebindCompareAnalysisreport

§ ShowonlySQLstatementswhereaccesspathhaschanged

§ RCAcanbeissuedfrommostreportswhereaPackagelineexists

RebindCompareAnalysisReport

PPARebindCompareAnalysis(RCA)

RCALinecommandcanbeexecutedonmostreportscontainingaPackagedetailline.Current/FutureexplainsfollowingbyComparewithRebindAnalysisReportforSQLwithAccessPathchanges.

PPARebindCompareAnalysis(RCA)

RebindCompareAnalysisreportshowsacostimprovementfrom44to1(ms)whenrebindingpackageonDB2V10duetoColFnE=R(ColumnFunctionevaluatedatdataretrieval)

UseCase:DetectorReportingandCollectionProfileUsage

DetectorReportingandCollectionProfileUsageDefineapplicationgroupsusingreportingprofiles

ApplicationprofilescanbedefinedforcollectionsofPLANs,Packages,CorrID,andUserswhichmakeupanapplication.

Inthisexample,RTPPLNMNandRTPREGarethecollectionofPLANnamesthatarespecifiedintheprofilethatmakeoftheApplicationRTPAPPL.

DetectorReportingProfileUsageViewingDB2activitybyApplicationReportingProfile

ViewBy‘Prof’(optionF)providesanaggregateviewofallactivityfortheselectedintervalsbyallPLANsdefinedinthespecificapplicationprofiles.

Inthisexample,RTPAPPListheapplicationIDassociatedwiththeapplicationprofilethataggregatesthecollectionofPLANSthatmakeuptheapplication.Allotheractivityintheintervalisrepresentedby‘*NoProfile*’.

DetectorReportingProfileUsageDrilldownintoactivitywithintheselectedApplication

Byselectinganapplicationprofilewithan“S”linecommandontheViewByProfiledisplay,activitycanthenbedisplayedforeachPLAN(orprofiletype)individuallywithintheApplication.

FurtherDrilldowntoPrograms,DynamicSQL,orotherKeyscanthenbeselected.

UseCase:CARC/QueryHDDLLineCommand

CARC/QueryHDDLLineCommand

§ HDDLLinecommandonaDB2objectwillgenerateDDLfortheobjectalongwithitsParentandDependentObjects.

GenerateDDLforspecifiedobjectalongwithParentandDependentObjects

UseCase:ManageDB2AnalyticsAccelerator

ManageDB2AnalyticsAcceleratorUseCARC/QueryforIDAAadministration

AbilitytoStart/Stop/DisplayandPINGAcceleratorfromRC/QAcceleratorList

RC/QAcceleratorTablelistreportprovidestableleveladministrationofyourIDAA

IDAATablelevelfunctionsareavailabletoDefine,Alter,Remove,Load,etc.

§ LoadDB2TableintoIDAA– RC/QALOADCommand

– CanloadrangeofpartitionsorentireTable

– UsesIDAALoaderutility

– MostcommandssuchasALOADareavailableusingBatchProcessorScripts

DB2AnalyticsAcceleratorCASYSVIEW®PerformanceManagementforDB2

§ Subsystemstatistics:AcceleratorDetails– SYSACDTL:

AcceleratorDetails– HSACCDTL/

HSUACDTL:AcceleratorServerDetails/Summary

TipsandHintsWithDB2Tools

DB2Tools

§ “?”– PrimaryandLinecommands

§ Freeze/Unfreeze/Reportcommands

§ QFILEandQPRINTcommands

§ ALLprimarycommandinCARC/Query

§ EQF– ExtendedQueryFacility– TheSQLLinecommand

§ RC/QueryUserDefinedLinecommands

Tips,HintsandUsefulstuff

DB2Tools– TipsUsingtheISPFInterfaceUseof“?”foravailablePanelandLineCommands

Enter“?”inPrimaryorLinecommandfieldtogetalistofavailable

commands

DB2Tools– TipsUsingtheISPFInterfaceFreeze,UnfreezeandReportCommands

§ CreateCustomizableReportformatsusingtheReportCommand

§ Freeze/Unfreezecommandsallowforfixingcolumnstoscrollleft/right

§ QPRINT/QFILEcommandstoprint/savereport

DB2Tools– TipsUsingtheISPFInterfaceALLPrimarycommandinCARC/Query

The“ALL”primarycommandwillpopulateallobjectrowswiththeassociatedLinecommandtoenableaquickmethodofexecutingaspecificcommandforallobject.Linecommandoptionscanthenbeappliedtoallobjects.

DB2Tools– TipsUsingtheISPFInterfaceEQF– ExtendedQueryFacility

Anyscreenthatcontainsa‘Where=>’fieldcaninvoketheEQFSQLQueryEditor.

Y– CreateorUpdateEQFN– Don’tinvokeEQFS– ExecuteExtendedQuery

DB2Tools– TipsUsingtheISPFInterfaceEQF– ExtendedQueryFacility

AdditionalPredicatevaluesandORDERBYcolumnscanbeaddedtothebasequerybeingexecutedwithintheDB2Toolsinterface.

DB2Tools– TipsUsingtheISPFInterfaceCARC/Query– UserDefinedcommands

§ UserdefinedRC/QuerycommandsaredefinedviaUSALINEandidentifytheObjecttypetheyareavailabletobeusedon.

§ UsercommandscanbewritteninREXXorasacompiledprogramandLINKEDintothePTLLINELoadModule

USMLINE1 'USER-DEFINED LINE COMMANDS' USMLINE2 LISTCAT,(TS,IX,T,I), *

'IDCAMS Listcat of tablespace/indexspace' USMLINE2 FLDSTAT,(C), *

'Most frequent index column values' USMLINE2 EX,(T,V,C,S,U,P,I,A,VC,SG,DB,TS,BP,DR,CL,PK,DT,R,RI, *

CD,SQ,H,U,SY,L,SC,TG,AC,PM), *'Execute Clist or REXX Exec '

USMLINE2 TESTEX1,(T,V,C,S,U,P,I,A,VC,DT), *'SAMPLE REXX EXEC ',TYPE=R

DB2Tools– TipsUsingtheISPFInterfaceCARC/Query– UserDefinedcommands

AvailableUserdefinedcommandsforspecifiedDB2ObjectTypearelistedinthePanelwhenenteringa‘?’Linecommand.

CommandcanbeSelectedorenteredasLinecommand.

SampleREXXcommandisprovidedasexample.

ParticipateinCA’sDB2/IMSManagementCommunity!!communities.ca.com/community/ca-database-management-for-db2-and-ims

MainframeandWorkloadAutomation

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

RecommendedSessionsSESSION# TITLE DATE/TIME

MFX79E ProtectingandTappingIntoyourDataGoldmine:leveragingwhatresidesinyourmainframe 11/15/2016at9:00am

MFX80EIntelligentMainframeManagement- DataDrivenDatabase PerformanceManagement 11/15/2016at10:00am

MFX81EHowtoGettheMostoutofYourDB2,DB2Management,andAnalyticsInvestment 11/15/2016at11:00am

MFX88S StrategyandVisionforCADB2DatabaseManagement 11/17/2016at12:45pm

MFX90S DrivingDownCostsforDB2Management 11/17/2016at1:45pm

MFX91S BirdsofaFeather/StumptheTechieforCADB2Tools! 11/17/2016at3:00pm

MustSeeDemos

DemoNameProductXTheater#location

DemoNameServicesYTheater#location

DemoNameSolutionYTheater#location

DemoNameProductXTheater#location

Questions?

Thankyou.

Stayconnectedatcommunities.ca.com

Thecontentprovidedinthis CAWorld2016presentationisintendedforinformationalpurposesonlyanddoesnotformanytypeofwarranty. The informationprovidedbyaCApartnerand/orCAcustomerhasnotbeenreviewedforaccuracybyCA.

ForInformationalPurposesOnlyTermsofthisPresentation

how to get the most out of your db2 and analytics investment · how to get the most out of your db2...

Documents

db2 analytics accelerator loader for z/os

introducing qmf analytics for multiplatforms€¦ · qmf...

db2 12 for z/os previe · 2016-06-24 · “the way forward...

demonstrating near real-time analytics with ibm db2...

performing analytics on ims using the db2 analytics...

tech talk: db2 warehouse & integrated analytics system

ibm db2 analytics accelerator: high availability and ... ·...

ims to big data - computer measurement group real-time...

performing analytics on ims data using the db2 analytics...

db2 for z/os is serious about analytics: leveraging sql to

analytics for cached sql - alabama db2 users group ·...

investment opportunities in analytics

ibm db2 analytics accelerator trends and...

db2 real-time analytics meeting wayne, pa 2015 - idaa & db2...

db2 for z/os enterprise summit - mwdug update...

db2 analytics accelerator v5.1 technical update.pdf

idaa ibm db2 analytics accelerator la nouvelle donne bi...

ibm db2 analytics accelerator: real-life use cases analytics...

leveraging ibm db2 analytics accelerator to analyze your ims...

complete analytics with ibm db2 query management facility