WIXLIB

IBM Data Virtualization Manager for z/OSSession Number: 22621John CaseySenior Solutions AdvisorRocket Softwarejcasey@rocketsoftware.com1 2018 IBM Corporation 1

Disclaimer IBM Corporation 2018. All Rights Reserved. Performance is based on measurements and projections using standard IBM benchmarks in a controlledenvironment. The actual throughput or performance that any user will experience will vary depending uponmany factors, including considerations such as the amount of multiprogramming in the user’s job stream, theI/O configuration, the storage configuration, and the workload processed. Therefore, no assurance can be IBM, the IBM logo, DB2, z/OS, CICS, IMS, MQ and QMF are trademarks of International Business MachinesCorporation in the United States, other countries, or both. Java and all Java-based trademarks and logos are trademarks or registered trademarks of Oracle and/or itsaffiliates. Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both. Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both. Other company, product, or service names may be trademarks or service marks of others.2 2018 IBM Corporation

Critical Business IssuesData resides in silos and unfamiliar formatsSpeed of business and newdigital requirements forcingneed for real-time informationThere is simply too much data for olderarchitectures3 2018 IBM Corporation

Traditional Data Integration Risk to data securityChallenges of moving data(Traditional ETL) Data inconsistency Rigid, limits business agility High cost and latency4 2018 IBM Corporation

IBM Data Virtualization Manager for z/OS IBM Data Virtualization Manager for z/OS IBM Data Virtualization Manager for z/OS allows organizations tovirtualize z/OS data with other enterprise data sources in real-time toprovide comprehensive information that is readily consumable by analytics,digital applications and users.Data Virtualization Managerfor z/OS Value Proposition: IBM Data Virtualization Manager for z/OS enhances IBM's premier z/OS data integration solution with newcapabilities for creating real-time, virtual views of enterprise data - mainframe and non-mainframe.With Data Virtualization Manager, data remains securely in place, ready when needed in the right format,without the cost and complexity associated with moving data. Data Virtualization Manager unlocks thevalue of your mainframe data for real-time customer and business insights. Key Features: Keeps data secure and in-place with real-time data virtualization on IBM Z Support for a broad range of mainframe and non-mainframe data sources Breadth of APIs and interface support for SQL, JSON, REST, SOAP and HTTP z/OS resident optimization for improved performance and TCO Abstraction layer to improve productivity and reduce reliance on mainframe skills5 2018 IBM Corporation

Data Virtualization Use Cases6Faster, easier delivery ofmodern systems ofengagementNeed for immediate insightinto your customer orbusinessReduce thecost/complexity ofaccessing zation 2018 IBM Corporation

Data Virtualization Technology Accelerates Real Time AnalyticsUniversal Data AccessData Virtualization for all enterprise data on and off mainframeBI Solutionwith Access, Virtualization and Visualizationand Data PreparationData VirtualizationManager for z/OSDB2 AnalyticsAcceleratorLoaderQMF for z/OSCommon metadata mapsto share and reuseQuery AccelerationHigh-speed processing for complex Db2 queries7Machine Learningfor z/OSOpen Data Analyticsfor z/OSPredictive AnalyticsDiscovering patterns/meaning in data 2018 IBM Corporation

IBM Data Virtualization Manager for z/OSVirtualize z/OS data with other enterprise data sources in real-time to provide comprehensiveinformation that is readily consumable by analytics, digital applications and users 8zIIP eligible / avoid MLC costsOptimized for z14, z13, EC12Supports relational sourcesRules engine Support any API:SQL, NoSQL, REST, SOAP, HTTP Python support Automated discovery though IBM ADDI Ships with industry mappingsExploits 64-bit storageBuilt-in MapReduceBuilt-in parallel I/O 2018 IBM Corporation

New Feature: MQ Queues and Messages Ability to perform SQL-basedanalytics directly against MQqueues/messagesSQLJSON treats MQ queues/messages as a newdata source ability to filter on individual messages toinclude in result setIBM Data Virtualization Manager for z/OS Join any DVM data sources withMQ messages to access asvirtual table Support for MQGET (reads) andMQPUT (inserts) Non-invasive, virtual access Eliminates the need to extract data fromqueueEnable access/update todata in MQ via multiple APIsServicesMQGETMQPUTQueue ManagerNon-relational DataAdabasIDMSIMSVSAMSMFz/OSJoin data in queues andmessages with nonrelational data9 2018 IBM Corporation

Virtual Parallel Data Allows multiple independent SQL requests to share a single result set Front ends IDAA Loader IzODA - Spark Scala/Spark Java/Python DB API QMF JDBC/ODBC Studio Batch/DSSPUFI10 Back ends Sequential/Tape VSAM/IAM Adabas zFS Logstreams MQ Series IMS IDMS 2018 IBM Corporation

Virtual Parallel Data VPD requests are joined in a named group 11Each request specifies the name of the groupThe first request to arrive creates the group; I/O begins immediately.Requests arriving within a time interval are placed into the same group.The group is closed when the last request arrives, or the timeout is reached.Once the group is closed, the buffer can be wrapped/reused.The back-end I/O is done once, and buffered in one or more large, wrapping, 64-bit memory objects.All group members share the buffered results.Each VPD request can specify its own degree of parallelism using MRC. 2018 IBM Corporation

Recent PoT EnvironmentObjective: Replace multi-step ETL process to replicate IDMS tables to OracleAVZQDataStageORCLAVZ9DVM ODBCz13- 768GB- 7 GPP- 6 zIIP12Windows VM- 2 processors- 8 GBLinux VM- 2 processors- 8GB 2018 IBM Corporation

ODBC Map Reduce1313 2018 IBM Corporation

How does IBM Data Virtualization Manager perform?Tests performed at the IBM Systems Benchmark Center, Poughkeepsie, NY in Nov. 2017 running on IBM z13using 800GB of financial data - flat files, with a multitude of fieldszIIP exploitation 99% of data virtualization runs on zIIPzIIP engine exploitationRowLabelsSum of CPUTimeDVM 1Parallelism 14Test Case 4 and 2 have sameconfigurationWith degree of parallelism at 8elapsed time is reduced from 98.68minutes to 17 minutesFurthermore, by adding 3 zIIPs, testcase 8 shows even greaterimprovement bringing the elapsedtime down to 13.83 minutes.With enough zIIPs it will not unusualfor us to see 1000% improvementfor elapsed timesSum of zIIPTime7099.03Sum of IIPCPTime5609.551389.58Sum of zIIPNTime5609.55%zIIPeligible98.59%Parallelism impact on elapsed timeTestCaseGPP'sNumber of zIIPenginesDegree ofparallelismElapse timein 81611.722SMT 2018 IBM Corporation

Data Virtualization for Mainframe API Enablementz/OS ConnectEnterprise Editionz/OS Connect EE v3Security layerGateway / DataPowerREST APIConsumersIntelligent Content routingLoad balancerCICSDiscoveryServicesIMSWASTrusted zoneMQDMZOuter APIsInner APIsREST ServicesDB2 z/OS Connect Enterprise Edition V3.0 enablesthe exposure of existing backendservices/applications as RESTful inner APIs15 15 2018 IBM Corporation

Data Virtualization for Mainframe API Enablementz/OS ConnectEnterprise EditionDMZSecurity layerGateway / DataPowerREST APIConsumersIntelligent Content routingLoad balancerz/OS Connect EE v3 and Data Virtualization ManagerDiscoveryServicesCICSAdabasIMSIMS DBSQLTrusted zoneOuter APIsWASIDMSMQSMFDB2VSAMDVMNon z/OSInner APIsREST Services z/OS Connect Enterprise Edition V3.0 and DataVirtualization Manager enable the exposure ofexisting backend services/applications and dataas RESTful inner APIs16 16Data VirtualizationManager for z/OS 2018 IBM Corporation

IBM Data Virtualization Manager for z/OSInstallable components DVM Server (SMP/E) Studio (Eclipse-based) Drivers (ODBC/JDBC)17 2018 IBM Corporation

Installing the IBM Data Virtualization Manager for z/OS (DVM) Standard SMP/E Installation Program Directory lists the 22Rel files shipped with the product SAVZLOAD, SAVZCLOD andSAVZPRC must be PDSEs18 2018 IBM Corporation

Configuring the DVM Server Installation and CustomizationGuide contains high levelchecklist 7 Required tasks 3 Optional tasks19 2018 IBM Corporation

DVM Studio connections Connection String JDBC Connection string to DVMStarted Task on z/OS JDBC Connection string to DB2SDSF OUTPUT DISPLAY VDBSSTC07224 DSID2 LINE 110COMMAND INPUT 13.33.32 STC07224 VDS1290H VDBSOEPORTNUMBER'1200'20 2018 IBM Corporation

DVM Studio view Virtual table view withinthe studio View existing virtualtables Create new virtualtables Execute sample testqueries21 2018 IBM Corporation

Creating virtual tables Virtual Tables A right-click suppliesvarious virtual tableoptions Select the “create virtualtables” option22 2018 IBM Corporation

Data sources Data sources List of available z/OSdata sources VSAM will be the datasource in thisexample23 2018 IBM Corporation

Virtual table options Virtual table options Supply a name forthe virtual tables,names are nonqualified names Option to createone flat table orparent/child tablesfor reoccurs PDSE formetadata library24 2018 IBM Corporation

Copybooks Copybook options Pull down menu tochoose what copybookdataset to access Members will bedisplayed Select/download theappropriate VSAMcopybook25 2018 IBM Corporation

Copybook - continued Copybook options Visual representation of selectedcopybook Ability to select start/end fields Example – not selecting a “Filler”field26 2018 IBM Corporation

VSAM cluster name Final options Input VSAM cluster nameand click to validate Post/pre exit options Ability to use an alternateindex Finish to create27 2018 IBM Corporation

Data validation Data validation Right-click the virtualtable name Select the option toquery the data A SQL virtual tablequery will be generatedto validate the data atrest28 2018 IBM Corporation

Data validation - continued Result Sets11. All data flattened inone table2. Parent table forVSAM cluster23. Child table forreoccursParent/child to connecttables329 2018 IBM Corporation

Application Access30 2018 IBM Corporation

Access virtual tables from AY 'OPENING CONNECTION TO DVS SERVER: ' DVCB-SSID.MOVE MESSAGE-LENGTH TO DVCB-MESSAGE-LENGTH.MOVE FCT-OPEN TO DVCB-REQUEST-CODE.CALL DSCLIENT USING DVCB SEND-AREA RECEIVE-AREAERROR-AREA SQLDA-AREA.PERFORM **************DISPLAY 'SENDING SQL TO DVS SERVER: ' DVCB-SSID.MOVE FCT-SEND TO DVCB-REQUEST-CODE.MOVE SEND-LENGTH TO DVCB-SEND-LENGTH.CALL DSCLIENT USING DVCB SEND-AREA RECEIVE-AREAERROR-AREA SQLDA-AREA.PERFORM 0146000101470001 Traditional COBOL coding1. Access data in place,VSAM, Sequential,IMS,etc.2. DSCLIENT embedded inCOBOL code to accessvirtual tables 2018 IBM Corporation

DVM code generator Generating Code Virtual table/viewdefinition complete Right-click on table/viewand choose “GenerateCode From SQL”32 2018 IBM Corporation

DVM code generator Generating Code Input and target of code Variables33 2018 IBM Corporation

Choose application type Application code options34 2018 IBM Corporation

Generated Java code Generated code is acomplete workingprogram Samples show theconnection to DVM andthe SQL prepare andexecute35 2018 IBM Corporation

ISPF interface36 2018 IBM Corporation

Trace browsePink – executed on zIIPGreen – executed on GPPTCBADDR:C* - zIIP and GPP samespeedD* - zIIP faster than GPPLine commands:P/PP – printS/SS – print with zoomPrimary commands:DISPLAYPROFILE37 2018 IBM Corporation