Transcription
Streamline Your ModernizationStrategies With Open Sourceand SQL With Db2 for iB Scott Forstie on how establishing a virtual layercomprised of SQL views fits into databasemodernization strategiesB Jesse Gorzinski on open-source modernizationtrends and advantages
FROM THE EDITORcontents39 Using Power10'sSuperfast AI WithEvent Streaming10 Open SourceReaches theMainstream152 Achieving DatabaseModernization UsingSQL With Db2 for iAIX 7.3 LeveragesPower10 Processorto Support Customers’Hybrid Multicloud andAI EndeavorsMainstream ModernizationStrategiesModernization strategies come in many forms—each with a unique blend of cost, risk and value.But some strategies are better than others,depending on your organizational needs.However, some organizations still have hesitationsabout adopting the most effective modernizationstrategies—such as establishing a virtual layer of SQL views withinphysical data models or leveraging open-source technologies.In this e-book, Scott Forstie directly addresses some of the mostcommon “blockers” to database modernization, and explains howestablishing a virtual layer comprised of SQL views fits into databasemodernization strategies. Jesse Gorzinski also weighs in on commonopen-source misconceptions, and highlights why open source hasbecome a mainstream modernization strategy.Keelia Estrada Moeller, Senior EditorStreamline Your Modernization Strategies With Open Source and SQL With Db2 for i
SPONSORED BYLOGO55 WORD INTRO dolor sit amet, consectetuer adipiscing elit, sed diamAchievingerat volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tationDatabaseullamcorper suscipit lobortis nisl ut aliquip ex ea commodo consequat. DuisTheheadlinegoesautem vel eumiriure dolor in hendrerit insuscipit lobortis nisl ut aliquip ex.ModernizationUsinghereSQLin two linesWithDb2fordeki dek dekDek dekdek deknonummy nibh euismod tincidunt ut laoreet dolore magna aliquamS100 WORDS ipsum dolor sit amet, consectetueradipiscing elit, sed diam nonummy nibh euismodtincidunt ut laoreet dolore magna aliquam eratvolutpat. Ut wisi enim ad minim veniam, quis nostrudexerci tation ullamcorper suscipit lobortis nisl ut aliquip exea commodo consequat. Duis autem vel eum iriure dolorin hendrerit in vulputate velit esse molestie consequat,vel illum dolore eu feugiat nulla facilisis at vero eros etaccumsan et iusto odio dignissim qui blandit praesentluptatum zzril delenit augue duis dolore te feugait nullaBY SCOT T FO RSTI Efacilisi.Epsum factorial non deposit quid pro quo hicescorol. Olypian quarrels et gorilla congolium sic adScott Forstie on howestablishing a virtual layercomprised of SQL views fitsinto database modernizationstrategiesDatabase modernization comes in many forms,100 WORDS ipsumeach withdolorasituniqueamet,blendconsectetuerof cost, riskadipiscingand mostelit, sed diam nonummyimportantly,nibhvalue.euismodThis articletinciduntfocusesut laoreeton a verydolorespecificmagna aliquamidea—establishingerat volutpat.a virtualUt wisilayerenimofadSQLminimviewsveniam,withinquis nostrudthe physicalexercidatatationmodel.ullamcorperBy the end,suscipityou’ll see thatlobortisthisnislcanut aliquipbe doneexwithea commodonext to zeroconsequat.risk and nextDuisto zeroautemcost,vel eumleavingiriurethedolordecisionin hendreritpoint within vulputateyou as tovelitthe value.esse molestie consequat, vel illum dolore eu feugiat nullafacilisis at vero eros et accumsan et iusto odio dignissimqui blandit praesent luptatum zzril delenit augue duisdolore te feugait nulla facilisi.Epsum factorial non depositquid pro quo hic escorol. Olypian quarrels et gorillacongoliumHoldsictheadphone.nauseum.This sounds too good to be true. We don’thave a database engineer on our team. We don’t havedeep SQL skills. Lastly, we don’t have time to gain therequisite skills to tackle database modernization!Common DatabaseModernization Blockers
Let’s address some of the most common blockers todatabase modernization:Blocker:Counter:Blocker:Counter:SQL views are dangerous.SQL views are performance neutral. SQLviews take minimal storage, they don’t growin size and they’re safe to use.Blocker:We don’t have staffing. Managementwon’t buy in!Gain the awareness, trust and supportof management by delivering unfundedmodernization to help managementunderstand that a balanced approach ispossible, quantifiable and valuable.Counter:Blocker:Counter:4Our physical data model doesn’t evenuse SQL!SQL views can be built over DDS createddatabase physical files, SQL tables and otherSQL views.We don’t have time!This idea will take minutes to complete. Ithink you can squeeze that in. Further, asmall amount of time spent on this topicwill gradually yield value and return on yourinvestment.How Open SourceStreamlineYour ModernizationBolsters PowerStrategiesSystemsWithModernizationOpen SourceandandHybridSQLCloudWith Db2Strategiesfor i
RedirectingProgramsand Users toan SQL ViewFor many IBM i clients, the physicaldata model is comprised solelyof database physical and keyedlogical files. By forcing the users andapplications to directly consumethe lowest level of the physical datamodel, there is a significant barrier tochange. When a non-SQL languageis used to consume a database file,the format of the file is hardboundto the consuming program. Later, ifthe business needs or wants to adda column to the file, a correspondingrecreate of all the consumingprograms must be orchestrated.A closer-to-modern approach forthe data model would be to redirectprograms and users to an SQLview, even if the view is simply areprojection of the same columnsfound in the underlying table. Why?Flexibility. When the applications are5Free, Expert-Led Education;Live or On-DemandWant to keep up on the latest IT trends? Wondering what yourpeers are doing? Need a solution for a business challenge?With many in-person conferences and expos on hold, there’s nobetter way than complimentary, expert-led TechChannel webinarsto get the latest on all things tech. Attend now and interact with thetechnologists; ask the questions that matter most to you.View all live andon-demand webinarsHow Open SourceStreamlineYour ModernizationBolsters PowerStrategiesSystemsWithModernizationOpen SourceandandHybridSQLCloudWith Db2Strategiesfor i
refocused on a virtual layer of SQL views, the door opens toadding columns to the underlying physical file without thehistoric concern of coordination, negotiation or otherwisedoing the heavy lifting necessary to ripple the changesthroughout the data center.Instead of using the remainder of this article to wax poeticon theoretical matters, I offer you a working solution.Before I take you through the details, let me make one pointclear: There are many independent software vendor (ISV)products that can help you with database modernization. Iencourage you to take a close look at these options, as theyare powerful and should be considered.Figure 1. The Db2 for i SQL TutorPhoto by David BowmanA Working ExampleSQL on Db2 for i can do so many amazing things. In thefollowing example, I will show you how SQL can discoverdatabase physical files, create the SQL statement text toestablish SQL views over those files, and finally, executethe SQL statements. This working example has beenpublished on the Db2 for i SQL Tutor, a site focused onsharing SQL solutions to IBM i challenges.As seen in Figure 1, the welcome page of the SQL Tutorhas different avenues into the examples, and there is evenan aggregated list of the IBM iSee video blogs, where mycolleague Tim Rowe and I visually show how-to topics onthe IBM i, frequently with a companion SQL script. From6How Open SourceStreamlineYour ModernizationBolsters PowerStrategiesSystemsWithModernizationOpen SourceandandHybridSQLCloudWith Db2Strategiesfor ithe SQL Tutor, if you drill into the Database EngineeringTopics, scroll down a bit and you’ll see the SQL examplesand IBM iSee videos as depicted in Figure 2. If you clickon the Virtually done.sql link, you should see the SQLsource code and sample invocations of the build viewstatement() SQL scalar function and the build viewsover physicals() SQL procedure. To use this exampleas is, execute the CREATE FUNCTION and CREATEPROCEDURE SQL statements one time on your IBM i.The build view statement() function is passed in theschema name and table name of your existing databasephysical file. The name of the SQL view that you would
like to have created is the third parameter. The exampleuses a naming scheme where the character “V” is tackedonto the end of the table name. You could adopt whatevernaming convention you’d like to utilize. I prefer to havethe beginning portion of the view name use the samecharacter sequence as the table name, so that it’s easierto remember the view name and so that the objectsappear next to each other in tools that list databaseobjects. If you have tables that use 10-character names,the view will end up with a system generated file name.The users will use the nice, long name and if you want toadjust the system name, do so via Access Client Solutions(ACS).The fourth and final input parameter is an optional systemname for the view, which only needs to be considered ifyou’re using long names for your physical files, which isdoubtful.Figure 2. Database engineering topics include working SQL andhow-to videosFigure 3 shows how easy it is to call the build viewstatement() function for a specific file, confirming that theconstruction of the CREATE VIEW statement is working.The build views over physicals() procedure drives theshow. It utilizes a recently added Db2 for i catalog (QSYS2/SYSFILES) to discover database physical files within theuser supplied schema name.7How Open SourceStreamlineYour ModernizationBolsters PowerStrategiesSystemsWithModernizationOpen SourceandandHybridSQLCloudWith Db2Strategiesfor iFigure 3. The build view statement() scalar function constructsa CREATE VIEW SQL statement
For each file, the function does the following:1. Invoke the coolstuff.build view statement() function tohave the CREATE VIEW statement text generated2. Execute the CREATE VIEW statement3. Transfer the ownership of the view to match the ownerof the physical file4. Grant the view the same authorizations as thephysical file5. Log the details of the operations in a Declared GlobalTemporary File (DGTF) named session.views created.SQL DGTF’s exist within the current job’s version ofQTEMP. If you want the results of the procedure callto be saved, use an INSERT with Subselect, queryingsession.views created.Once all database physical files, in the given schema, havebeen processed, the overall results of the function arereturned as a result set, as shown in Figure 4.That’s all there is to it! See how easy it is to establish avirtual layer, comprised of SQL views?My advice is this: Approach database modernizationusing modern tools, with an eye on cost, value and risk.8Streamline Your Modernization Strategies With Open Source and SQL With Db2 for iFigure 4. The build views over physicals() table function in actionIn some cases, SQL with Db2 for i is the tool to use tomodernize your IBM i.SCOTT FORSTIE is a senior technical staff member within IBM idevelopment and spends most of his time working as the Db2 for iArchitect. He has worked on IBM OS development since joining IBMin 1989. Scott is a frequently published author, speaker at industryevents around the globe, and can be found @Forstie IBMi andforstie@us.ibm.com. Scott is an avid runner and enjoys cooking.
Using Power10's Superfast AI With Event StreamingBY J E S SE G O R ZI NSKIThe latest generation of IBM's Power processors is noincremental update. It comes with built-in accelerationfor artificial intelligence (AI)! This is done by integratingmatrix math assist (MMA) units right into the chip.If you haven't already, now is the time to explore AI. IBM iis a natural fit for this purpose. In order to be effective, AIalgorithms need "ground truth" data. Simply put, the more"ground truth" is available, the better the intelligence.And guess what houses tons of data: Db2 for i! Somecompanies have decades of historical data, and someprocess mind-numbing amounts of transactions everyminute. It's a treasure trove for cognitive computing.There are a number of ways for an IBM i company toexploit this revolutionary processor technology.In some scenarios, you may wish to analyze transactionaldata in real time. A logical approach for this is to decouplethe transaction processing from the analytics. An eventstreaming model can be used, whereby transactions arepublished in real time over some medium for anothercomponent to examine as needed.This event streaming approach, like others, can leveragereal-time information to provide the best insights. It’sunique in that it takes an offline or out-of-band method.This could be a suitable end goal for a variety of AIapplications. In this case, the event-streaming modeloffers complete flexibility. Ultimately, there are three maincomponents to this approach: a data source, a streamingplatform and the AI.Learn more about Power10 in the full article9Streamline Your Modernization Strategies With Open Source and SQL With Db2 for i
SPONSORED BYOpen Source Reachesthe MainstreamLOGOIn a few short years, IBM i clients have embracedopen-source technology. Jesse Gorzinski weighs in onopen-source trends and advantages here.BY N EI L TAR DYDeknedekdek dek dek dek dekThe reality is that open source is fully mainstream now, andway to gauge the value of open-source solutionsOon the IBM i platform is to consider what actual IBM iclients are accomplishing with the technology. But totruly comprehend the impact of open source, it’s necessaryto take a step back—perhaps even a literal step back to lookaround your home.Open source underpins technology that allows you to checkyour bank account balance on your phone or start your carfrom your kitchen. Beyond that, many prominent companiesrely on open source in their internal operations—not justtech giants like IBM and Microsoft, not just GitLab (whichwent public at 77 a share in October), but businesses you’dnever expect.10Streamline Your Modernization Strategies With Open Source and SQL With Db2 for ithat prominence and prevalence makes it all the more compelling to IBM i clients looking to modernize applications.“In the past five years, open source has become the forefrontclassification of technology—for IBM i and the industry asa whole,” says IBM’s Jesse Gorzinski, business architect foropen source on IBM i. “We have smart cars and smart refrigerators and all these things. I can start my Hyundai from myphone because my Hyundai has a bunch of code that talks tothe internet and talks to message brokers to get the signalthrough. That’s powered by open source. Open source is justhow we do this stuff now.”
Fighting FUDIt can be argued that open source has existed since theearliest days of computing. In the 1950s, the SHAREuser group worked with IBM to publish applications andutilities as source code for use on IBM mainframes.Certainly if you consider the Zend Server or support forthe Node.js JavaScript runtime or Python programminglanguage, IBM i clients have benefited from open sourcefor years. However, only recently has open sourcereally taken off on the platform—early 2018 is a logicalmilestone.This could explain why common misconceptions aboutopen source persist within the IBM i world. For instance,is open-source software secure?“There’s this large misconception that open source issomehow inherently not secure, because the bad guyscan see the code,” Gorzinski says. “But who do you doyour personal banking with? Chase? Citigroup? Because Ican pull up their web portals and show you where they’reusing open source to secure your information. These arejust a couple of the prominent stakeholders who trustopen-source security.”He goes on to point out initiatives like the InternetBug Bounty and the Open Source Security Foundation(OpenSSF)—cross-industry collaboratives that strive11How Open SourceStreamlineYour ModernizationBolsters PowerStrategiesSystemsWithModernizationOpen SourceandandHybridSQLCloudWith Db2Strategiesfor i
to improve the security of open-source software byidentifying vulnerabilities and establishing and promotingbest practices.open-source software and implementing open-sourcesolutions. The point is there’s no inherent reason to fearthe “openness” of open source.Of course none of this is to suggest that the technologyis invulnerable and security isn’t a concern when using“There is fear, uncertainty and doubt—or FUD—aroundmodernizing applications and changing them to doBest-in-ClassSPONSOR ADSMBSolutions:HEADLINEHEREStart HereLorem ipsum dolor sit amet, consectetueradipiscing elit, sed tincidunt ut laoreet doloremagna aliquam erat volutpat. Ut wisi enimThe TechChannel 2021 Solutions Directory is the SMB users’ guide tothe latest in innovative, best-in-class Power Systems, IBM i, AIX, Linux onPOWER and storage solutions from ISVs and partners worldwide. Searchby OS, company or by 22 product categories for the latest informationurl.com1-800-555-6666and expert perspectivesavailable on hardware, accessories, softwareproducts, education and services.CALL TO ACTIONStart your research now12How Open SourceStreamlineYour ModernizationBolsters PowerStrategiesSystemsWithModernizationOpen SourceandandHybridSQLCloudWith Db2Strategiesfor i
something new,” Gorzinski adds. “And it's perfectlyreasonable to stop and think, ‘Am I opening up anattack vector for malicious players?’ You should askthat question. But the good news is that open sourcebrings industry standard security solutions backedby a large user community and freely availableinformation. You’ll find all kinds of resources at placeslike Stack Overflow and more reputable sources.So you can start implementing security with a highdegree of confidence that you're doing it right.”IBM i installations should also be confident in thesupport structure backing open-source development.In that prior sentence, the word “support” takeson dual meaning. First, there’s actual softwaresupport, which IBM recommends and provides forcommercial open-source packages it makes availablefor download. Then again, there’s the open-sourcesupport emanating from the world at large.“A lot of the Fortune 500 tech companies investin open source security. It’s a wide community,”Gorzinski says. “For instance, Google ‘Airbnb opensource.’ Airbnb is actually one of the most ethical,contributive companies in the open-source spaceoutside of the big tech firms. People will go to aJavaScript conference, see an Airbnb booth andbe like, ‘What? Are they trying to sell us cheapapartments to rent in New York?’ No, they're actuallythere to talk about all the great things they're doing13Streamline Your Modernization Strategies With Open Source and SQL With Db2 for i
“ All over the industry people worry about finding skilled people whounderstand the core business. But open source helps because open-sourcetechnology stacks can be used to augment today's business applications ina non-disruptive manner. ”—Jesse Gorzinski, business architect for open source on IBM i, IBMwith open source because that’s what they’re running ontheir stack. They understand that supporting the opensource community is good business.”The IT skills shortage is a final factor that causes someto hesitate when considering open source. Sure youcan post an ad on a job site and hear from two dozenPHP programmers within a day, but how can a PHPprogrammer possibly comprehend, much less manage,core business applications running RPG code writtenback in the heyday of hair bands?“That’s probably the thing you hear most: It’s hard tofind people with the right skills,” Gorzinski says. “It’sespecially true with IBM i, but this concern is not uniqueto IBM i clientele. All over the industry people worryabout finding skilled people who understand the corebusiness. But open source helps because open-sourcetechnology stacks can be used to augment today'sbusiness applications in a non-disruptive manner.14Streamline Your Modernization Strategies With Open Source and SQL With Db2 for iYou don't necessarily have to rewrite anything to builda new website, expose APIs, or implement DevOps. Youjust need to find folks with the skills that you need for thetechnology you’re about to embrace.”Modernization ToolsFor those about to embark on a new project, or simply getstarted with open-source development, Gorzinski offersthese recommendations:Use current programming languages: Python, Ruby,PHP and other common languages afford users accessto millions of modules in community repositories. TheNode.js npm repository alone is said to contain more thana million open-source packages. “That means if you’relooking to do [Internet of things] (IoT) or microservices orwhatever, the heavy lifting has likely already been done foryou,” Gorzinski says.
Check out these tools: Node-RED is a browser-basedlow-code programming tool for integrating IoT deviceswith applications. Even by techie standards, ApacheCamel is tough to sum up. But it allows users to integratedisparate systems using the same API. This capability isobviously valuable in any modernization scenario.AIX 7.3 LeveragesPower10 Processor toSupport Customers’Hybrid Multicloud andAI EndeavorsBY SCOT T M CKI N N E YOf course, numerous IBM i customers are already doingimpressive things with open source. To name just two,there’s Wijnen Van Maele and Cras Woodgroup. Moreexamples can be found in the IT Infrastructure sectionof ibm.com.Gorzinski describes the recent growth of open sourceon the platform as transformative. “Certainly adoptionrates continue to rise, but with IBM i in particular, we'veseen an incredible transformation,” he says. “IBM i shopsare doing cutting edge stuff with [artificial intelligence](AI), IoT, microservices and quantum computing. Evenmore importantly, they’re running it in production. That’sthe MO, the modus operandi, now.”The new AIX 7.3 release brings customersnew ways to integrate their missioncritical infrastructure with new and enhancedcapabilities. IBM designed AIX 7.3 in responseto feedback from customers, particularlyaround extracting business value from data andsupporting their hybrid multicloud strategies.Its new features take advantage of the uniquecapabilities of the Power10 processor.The innovations in AIX 7.3 focus on furtherimproving AIX’s core strengths customersdepend on and exploits new Power10 hardwarefeatures, while evolving AIX to better takeadvantage of new and emerging technologies.15Streamline Your Modernization Strategies With Open Source and SQL With Db2 for i
“As we continue to advance the OS, we have anextremely strong commitment to investment protection,”says Carl Burnett, Distinguished Engineer, AIX, IBM. “Theinvestments our customers have made in AIX remainthere in a compatible way, so they can get additionalvalue out of the hardware without having to change codein their applications.”The new features center around:›Streamlined business insights (enterprise artificialintelligence (AI))›Enhanced support for continuous computing›Increased security from core to cloud›Integration into existing environments›Support for hybrid multicloudThe combination of AIX 7.3 and Power10 helps clientssupport two broad AI use cases:1. In-transaction workloads: You can perform real-timeinferencing on an arriving transaction quickly to leveragepredictive capabilities versus having to call another AIplatform. This includes use cases like risk and frauddetection, or loan and claim pre-approvals—all on thesame Power system, close to the data.2. Operational analytics: AIX 7.3 combined with MMA allowsfor more sophisticated AI processing on fresh data as itarrives, without having to move it to a different platformand losing the value of insights from newly arriving data.Two examples of this are dynamic price optimization andanomaly detection in a batch of sales orders.In both cases, AIX 7.3 and Power10 help to meet SLAsaround the speed of transactions and response timewindow with maximum security where you have to make adetection or decision.Learn more about AIX 7.3 in the full article16Streamline Your Modernization Strategies With Open Source and SQL With Db2 for i
This e-book was published by953 Westgate Drive, Suite 107, St. Paul, MN 55114 // (612) 339-7571staff listSenior Content Strategist: Claire O'LearySenior Editor: Keelia Estrada MoellerArt Director: Kelsey HanscomProject Manager: Noelle HeaslipAudience Development Director: Linda HolmPublishing Director: Mari Adamson-Bray(612) 336-9241 // mbray@techchannel.comAccount Executive: Darryl Rowell(612) 313-1781 // drowell@techchannel.comSr. Strategic Account Manager: Lisa Kilwein(480) 428-9780 // lkilwein@techchannel.com Copyright 2021 by MSPC, a division of MSP Communications. This e-book could contain technical inaccuracies or typographical errors. Also,illustrations shown herein may show prototype equipment. Your system configuration may vary slightly. This e-book may contain small programs thatare furnished by MSPC as simple examples to provide an illustration. These examples have not been tested under all conditions. MSPC, therefore,cannot guarantee or imply reliability, serviceability or function of these programs. All programs contained herein are provided to you “AS IS.” IMPLIEDWARRANTIES OF MERCHANTABILITY, NON-INFRINGEMENT AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED.All customer examples cited represent the results achieved by some customers. Actual environmental costs and performance characteristics willvary depending on individual customer configurations and conditions.The articles in this e-book represent the views of the authors and are not necessarily those of MSPC or TechChannel.17Streamline Your Modernization Strategies With Open Source and SQL With Db2 for i
TechChannel.com is home to a variety of content tohelp you get started on your security journey.Learn more about modernization
In this e-book, Scott Forstie directly addresses some of the most common “blockers” to database modernization, and explains how establishing a virtual layer comprised of SQL views fits into database . Streamline Your Moderniz
