DB2 User Group Italia - 2016What's new and exciting in Tools for DB2 for z/OSElisabetta Curci e [email protected] Analytics on System Z Platform Sales Representative Italy 2016 IBM Corporation

Please Note:IBM’s statements regarding its plans, directions, and intent are subject to change or withdrawalwithout notice at IBM’s sole discretion.Information regarding potential future products is intended to outline our general product directionand it should not be relied on in making a purchasing decision.The information mentioned regarding potential future products is not a commitment,promise,or legal obligation to deliver any material, code or functionality. Informationabout potential futureproducts may not be incorporated into any contract.The development, release, and timing of any future features or functionality described for ourproducts remains at our sole discretion.Performance is based on measurements and projections using standard IBM benchmarks in acontrolled environment. The actual throughput or performance that any user will experience will varydepending upon many factors, including considerations such as the amount of multiprogramming in theuser’s job stream, the I/O configuration, the storage configuration, and the workload processed.Therefore, no assurance can be given that an individual user will achieve results similar to those statedhere.2 2016 IBM Corporation

AgendaIBM DB2 z/OS Tools TodayIBM DB2 z/OS Tools Directions 2016Some specific new toolsData Server Manager–What is DSM–Integrating Products and Timelines–Directions–FeaturesInfosphere System Z Connector for Hadoop3 2016 IBM Corporation

IBM DB2 z/OS Tools and z Analytics today DB2 Administration tool DB2 Administration toolkit SAPEdition DB2 Automation Tool DB2 Automation Tookit SAP Edition DB2 Bind Manager DB2 Buffer Pool Analyzer DB2 Change Accumulation Tool DB2 Cloning Tool Db2 High Performance Unload DB2 Log Analysis Tool Db2 Object Comparison Tool DB2 Object Restore DB2 Path Checker DB2 Perform Toolkit SAP Edition QMF z/os DB2 Query Monitor DB2 Query Workload Tuner DB2 Recovery ExpertDB2 SortDB2 SQL Performance AnalyzerDB2 Table EditorDB2 Utilities Enhancement ToolDB2 Utilities SuiteDB2 Query Workload TunerInfosphere Classic FederationInfosphere Classic Replication ServerInfosphere family for data governanceIS Data ArchitectIS Information ServerIS Optim ArchiveIS Optim Test Data ManagementIS Data ReplicationTivoli Omegamon XE for DB2 PM/PEDB2 Analytics AcceleratorDB2 Analytics Accelerator Loader.and more !4 2016 IBM Corporation

DB2 Tools Delivered Q32015 – Q12016DB2 Administration Tool/Object Comparison Tool v11.2– More support for IBM DB2 Analytics Accelerator– Enhanced Change Management and usability featuresDB2 Cloning Tool v3.2– Ability to clone a subsystem at the data set level instead of the volume level,reducing the footprint of the clone– Clone table spaces from image copies – NO DOWNTIME!No fast-replication storage requirement– Consistent cloning across z/OS LPARS allows you to easily clonelarge applications to a new LPAR at a consistent point-in-timeDB2 Recovery Expert v3.2– Mass application recovery: Prioritize mass recoverybased on application priorities (no fast-replication storage requirement)– Redirected recovery can backup object from source (table, table space,database)and restore to different target (database, LPAR– NO DOWNTIME IMPACT to SOURCE!!!– Backout errant transactions to ensure errors are removed from applications5 2016 IBM Corporation

IBM DB2 z/OS Solution Packs TodayDB2 Utilities Solution PackDB2 Performance Solution PackDB2 Automation ToolDB2 High Performance UnloadDB2 SortDB2 Utilities Enhancement ToolDB2 SQL Performance AnalyzerTivoli Omegamon XE for DB2 PEOptim Query Workload TunerDB2 Query MonitorAutomate and enrich the IBM utilitiesMaster the performance lifecycleComplete solutions for all needsDB2 Tools Solution PacksSimplified install and maintenanceBuild intelligence into when and howactions are performedOptimize performance and resourceutilization associated with DBAactivities to reduce TCODB2 Fast Copy Solution PackDB2 Cloning ToolDB2 Recovery ExpertSuperior avail & cost for copy,backup & recovery operations6DB2 Administration Solution PackDB2 Administration ToolDB2 Object Comparison ToolDB2 Table EditorOptim Configuration ManagerManage objects & schema

Directions for DB2 Tools7 2016 IBM Corporation

Strategic Directions for DB2 ToolsSelf-managing DB2 systems– Extensive set of alert thresholds & recommended actions– Advanced action resolution (including calendaring)– Integration of performance tools for true intelligent decision making– Move from automation to true autonomic capabilitySimplification & Modernization of Administration– Autonomics– Advanced graphical interfaces– Convergence of toolsCloud & Database Provisioning– Install, migrate, decommission subsystems– Simple, automatic provisioning of tools in support of new systems– Exploit z/OSMF support for workflows– RESTful APIs to support marketplacesAnalytics (including Enterprise Spark)– Enable access to all z data for the purpose of analytics– Identify opportunities to support Data Scientist & other use cases– Leverage streams & Spark for improved efficiency (e.g. continuous distribution statistics –RUNSTATS elimination)8 2016 IBM Corporation

DB2 Tools Portfolio Focus in 2016All– DB2 12 Toleration & Exploitation!– Portfolio refresh coincident with DB2 VNEXT (Versions, Releases, PTFs)Administration Solution– Change Management enhancements (CM Batch, Multi-target Change)– Improved Accelerator support– Integration of provisioning capability into Administration Pack– Expose Admin functions via REST APIs (for UCD or DSM integration)Utilities Solution– New version of HPU for performance improvements and resource optimization– DB2 Sort: Support for variable length block level data exchange– Extend autonomics (additional data sources, REORG management, statistics management)Analytics Solution– Extend Loader data sources based on client demand– QMF analytics capabilities enhancementsTCz– GUI enhancements including workflows to facilitate configurationPerformance Solution– Populate autonomics model with performance data (OMPE & QM)– Populate IDAA with performance data to facilitate analytics (QM)– New DSM-based QWT and other products9 2016 IBM Corporation

IBM Data Server Manager10 2016 IBM Corporation

IBM Data Server ManagerDeliver aSimplified UserExperience 117Single installer andintegrated repositoryCommon integratedweb console Provides enterprise viewof your environment Guided workflow andanalysisDeliver familiarcapabilitiesfrom theDatabase Tools Database Administration,Query Tuning,ConfigurationManagement 2016 IBM Corporation

Key development directions mapping Key clientsrequirementsStreamline tools, installs,repositoriesMake it easier to use; provideworkflow, diagnostic smarts,actionable recommendationsProvide better performance and scalabilityManage databases across theenterpriseone stop shop tooling12 2016 IBM Corporation

Data Server Manager: Overall TimelineMar 30, 2015Jun 30, 2015Dec 11, 2015eGA: IBM Data ServerManager V1.1.0IBM Data ServerManager V1.1.1IBM Data ServerManager V1.1.2Next Generation CoreTooling for DB2 (OPM,OQWT, OCM, Data Studio)Extending the value of NextGeneration ToolingExpanding the value of NextGeneration of DB2 Tooling to z- Initial DB2 for LUW – PerformanceManagement and DB AdministrationBigInsights - Simplicity in upand running for BigSQL - Initial BigInsights – BigSQLAdministrationa and Management-Good customer reception- Second drop for DB2 for LUW,closed most gaps in differencesbetween old tools and new toolsBigInsights expandingplatforms - Updates for Linux Platforms-Third drop with support for DB2 forz/OS-DB2 Query Workload Tuner for z/OSV5.1 based on DSM – standalone or inPerformance Solutions Pack for z/OS-Admin Solutions Packs for z/OSupdates with DB2 ConfigurationManager for z/OS V4.1 based on DSM-- Platform adds for DB2 LUW

Data Server Manager: Overall TimelineMarch 30 2016IBM Data ServerManager V1.1.2.1DB2 Configuration Manager z/OS:DB2 Query Tuner for z/OS–Migration from Eclipsebased OQWT to DSMBased Query Tuner–Capture Queryenvironment–Integration – QueryMonitor. PerformanceSolution PackParticipating in DB2 Cypress for z/OSESPBeta OMPE V540 OPM based EIManagement Console functions forDB2 for z/OS (including UtilitySolution Pack Autonomics) integratedinto DSM2Q 20163Q 20164Q 2016

Where can you find Data Server Manager todayData ServerManagerBaseEditionDB2PerformanceSolutionPack for z/OSDB2 UtilitySolutionPack for z/OSDB2 AdminSolutionPack for z/OSFREE!- Use to upsell topaid products15 2016 IBM Corporation

How can I get DB2 Configuration Manager? OCM 3.1.x is part of previous releases of the z Admin SolutionPack Integrated with z Admin Tool OCM 3.1.x is part of DB2 Connect Unlimited Advanced Editionfor z/OS DSM 3.1.x makes DB2 Connect management easy Great value for our customers16 2016 IBM Corporation

Features of the Data Server Manager Based ToolsAt A GlanceData Server Manager Base 17Connect to DB2 for z/OS V10 and V11.Database object navigation, viewingobject detail, and linking to relatedobjects.Database object dependency display.Data browsing and editing.Basic database object operations, suchas creation of tables, indexes,constraints, and tablespaces; dropping oftables, indexes and constraints; alteringtables.Showing system privilege from theperspective of Group/User, Role, or SQLobject. Choose:-"Group/User" to see the role and therelative object privilege for a useraccount;- "Role" to see the role a user accountbelongs to and its relative objectprivilege;- "SQL object" to see a specific objectand users or roles that have the relativeauthority.DB2 Query Workload Tuner forz/OS V5.1 (standalone, DB2 Perf SolnDB2 Configuration Manager forz/OS V5.1 (inside DB2 Admin Soln PackPack)only ) Launching of visual explain and tunequery on the SQL editorA new tuning wizard to capture SQLstatements from multiple sourcesSeveral Tuning advisors providerecommendations for: Statistics Advisor Index Advisor IDAA AdvisorUnderstand problem query or workload Access plan graph Query formatting and annotation Tuning Report Test Candidate Index Access Plan Comparison Index Impact Analysis Track Configuration ChangesConfigure zParmCompare and Clone ConfigurationsManage Application ProfileManage aliasManage clientsDB2 Utility Solution Pack V2.2 Customizable profiles for identifyingobject situations and generating resolvingJCL (reorg, copy, runstats, etc)Automatic prioritization of objectsituations and their resolving actionsAbility to define maintenance windows forenabling active autonomics, allowingDB2 to self manage utility runsNotification support (email, text, WTO) onselected autonomic eventsGraphical trend analysis of historical RTSCapture of utility history, recording utilityoutput, time, duration, etc. 2016 IBM Corporation

Introducing Data Server Manager Supporting DB2 for z/OSEasy Database Administration Quick and simple way to navigate database objects, viewdata and edit, write SQL and get results backSimplify Best Practices Configuration Easier to manage database clients connecting to DB2 forz/OS systems, and manage what they are doing Increase value of the DB2z Administration Pack18Modernize Query Tuning / Increase IDAA value Improve performance of applications leveraging DB2z Simplified, modern look and feel to database query tuningand also carried through in the DB2z Performance Pack Get more value out of your IDAA investments throughevaluating IDAA eligible workloads New features such as index impact analysisIntegrated Tool for DB2 for z/OS Management Console and DSM Integration Common strategy for DB2 for z/OS customers who wantsimple access - one tool - to manage the health of theenterprise in a modernized fashion via web browser 2016 IBM Corporation

Data Server Manager - Key Features19 2016 IBM Corporation

3-step setup20 2016 IBM Corporation

Basic Navigation : New Tuning WizardAbstractHelp users navigate between panels more convenientlyLaunch pointOptimize - Query TuningSelect “New tochoose theDatabase for Tuning21 2016 IBM Corporation

Manage Databases Using the Database ExplorerManage database objectsExplore the catalogExplore databaseobject properties22 2016 IBM Corporation

Develop and Run SQL Scripts Manage scripts Explain SQL Tune SQLValidate SQL23 Customize and filter resultoutput Save execution results 2016 IBM Corporation

Create and schedule jobsSchedule a Create Tablejob from ExploreDatabasesScheduled commandsfrom Explore DatabasesAlternatively, create ascript and schedule thejob242 2016 IBM Corporation

AnalyticsQuery workload tuner for DB2 for z/OS overviewWeb-based user interfaceDSM common infrastructureQuery Tools and AdvisorsWorkload Tools and AdvisorsOverviewPageAccess orIndexAdvisorFormatterAnnotationService orIndexAdvisorStatisticsAdvisorAccess PlanComparisonAccess Plan ComparisonConfiguration dDB2DB2forforz/OSz/OSDB2 for z/OS 2016 IBM Corporation

Expert-Enabled Query Tuning Out of the BoxIdentify query candidates from numeroussources– DB2 catalog– Dynamic statement cache– User defined repositoryFacilitate analysis– Query formatting– Query annotation– Access path visualization andannotationGet expert tuning advice– Improve statistics quality– Improve database design– Improve accelerated queries–

Tune SQL or WorkloadReview access plan graph and annotationsAdvisors streamline tuning process– Single Query Statistics Advisor– Workload Statistics advisor– Index advisor– IDAA advisorNew!Use what-if analysis to determine what impactindexes have on a workloadUse what-if analysis to determine what statements areeligible for accelerationRun scripts to implementadvise or share resultsusing tuning report27 2016 IBM Corporation

AnalyticsAccess Plan GraphRecommendation Integration 2016 IBM Corporation

AnalyticsWorkload Index impact analysisAbstract– Analyze the impact of the recommended indexes to other SQL statementsLaunch point Select a tuning job - View Results - Index script - Test Candidate Indexes 2016 IBM Corporation

AnalyticsWorkload Index impact analysis cont.Analysis scope– Statement Cache– Packages– Existing jobs 2016 IBM Corporation

AnalyticsIndex impact analysis cont. 2016 IBM Corporation

Migrating workloads from QWT 4.1.x to DSM32 2016 IBM Corporation

Data Server Manager : Useful web .com/watch?v x17hpPi9OkE&list PLKMYHhPjiLIB2cuz7I f-VWa9EAPn-vF33 2016 IBM Corporation

IBM InfoSphere System z Connector for Hadoop34 2016 IBM Corporation

Whether you run Hadoop on z or off z z/OS Data is importantTransaction and LogData are consistentlyPART OF the analysis!Evidence of the value ofcombining traditional datawith Hadoop analyticsSource : Gartner Inc, Research Note G00263798- SurveyAnalysis: Big Data Investment Grows butDeployments Remain Scarce in 2014 . NickHeudecker, Lisa Kart,Published 09/09/2014

Big Data Integration Is Critical For Success With HadoopMost Hadoopinitiatives involve- collecting,“By most- moving,accounts- transforming,- cleansing,- integrating,- exploring, and- analysing and onlyvolumes ofdisparate datasources and types.80%of the development effortin a big data project goesinto data integration20%goes towardsdata analysis.”Extract, Transform, and Load Big Data With Apache Hadoop - f

For most, Big Data Integration Must Reach Backto z Systems 80% of transaction data is stored or originates on IBM z SystemsMuch of this is because of the scale of the 3,000 mainframe systems Average annual downtime for System z applications is 5 minutes! Latest System z hardware capable of running 8,000 virtual linux images EAL5 encryption & cryptographic hardware securing data in motion & at restEmpowering System z Data Integration with Hadoop & Big Data Initiatives37Bring together data from both realmsProvide up-to-date informationPreserve core transactional data securityMinimize overhead and TCO 2016 IBM Corporation

InfoSphere System z Connector for Hadoop (Passport PID 5725S33)Big DataZ SystemsIBM IOP & BigInsightsNoSQLDB2VSAMLogsIMSIDMSDatacomLinux filesystem &ETLProcessesOpen SystemsDB2OracleSQL ServerTeradataIBM System zConnector forHadoopNetworkendpointCloud

IBM InfoSphere System z Connector for HadoopKey FactsEasy to Setup and Use:– Installs from Linux– Drag-and-drop interface – no programming required– COBOL/PL1 copybook parsing & presentation to automate data reformatting– Automated Code Page ConversionStellar Performance:– Expect to see dramatic z/OS MIPS usage reduction over home grown & ETL– Users report three and four times throughput increasesBroadening Support:– Multiple Hadoop distributions – on and off the mainframe– Multiple sources: DB2, VSAM/QSAM, IMS, IDMS, Datacom, JDBC, Log files– Multiple destinations: Hadoop, Linux File Systems, MongoDB, streaming endpointsFeature Rich:– Define multiple data transfer profiles– Filter and translate data & columns on the fly, on the target– Secure pipe (SSL) – RACF and TopSecret integration– Preserves metadata, landing mainframe data into targets39 2016 IBM Corporation

Behind the Scenes – Real life client flow semplification10 TB g Mainframe processing(storage on disc and tape)Unacceptable delay with data volume increaseExceed MIPS consumption & batch windowSecurity Challenges (unnecessary copies)Outsourcing labor costTransactionaldataAfterSystem zConnectorMainframe Point & Click Solution (no programming required)Integrate with RACF mainframe security & schedulingMIPS consumption 1/10 vs previous/traditionalapproach 4X Speed increase moving detailed data for analysis No staging cost saving on storage & software40

InfoSphere System z Connector for HadoopKey Benefits41 Simplicity, Speed of deployment (no z/OS install), Instant valuethrough point and click. Security (RACF & Top Secret access method so no duplicated profiles), End to end data movement & conversion with no staging. The productdoes not require multiple steps to make z/OS data available for analytics(vs. traditional ETL or manual processes). No Cobol, no SQL and noassociated cost to maintain those file transfer jobs Minimal z/OS CPU usage (MIPS) as all data conversions are happeningon Linux processors Integration with existing infrastructure (schedulers,.)41 2016 IBM Corporation

THANKYOU42 2016 IBM Corporation

What's new and exciting in Tools for DB2 for z/OS Elisabetta Curci [email protected] . Launching of visual explain and tune query on the SQL editor A new tuning wizard to capture SQL . Understand problem query or workload Access plan graph Query formatting