MAINFRAME How CADetector for zOS solved my Db
MAINFRAME How CA-Detector for z/OS solved my Db 2 BIF compatibility worries Chris Hoelscher – choelscher@humana. com Database Architect Humana, Inc. MFT 34 T
For Informational Purposes Only Terms of this Presentation Must use [WE WILL DELETE THIS NOTE BEFORE SUBMITTING] © 2017 CA. All rights reserved. All trademarks referenced herein belong to their respective companies. The content provided in this CA World 2017 presentation is intended for informational purposes only and does not form any type of warranty. The information provided by a CA partner and/or CA customer has not been reviewed for accuracy by CA. 2 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
Abstract Chris Hoelscher Humana Inc. Database Architect 3 #CAWORLD #NOBARRIERS Starting in Db 2 10, Db 2 changed the way certain built-in Functions (BIFs) behave and the way timestamps must be formatted, amongst other changes. While we could identify (through IFCID traces) The date/time/user of the distributed packages at risk, we could not report the corresponding SQL text - until we exploited CA-Detector. We are now able to deliver the needed information to allow developers to quickly make the required changes COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
Agenda 4 1 THE PROBLEM? 2 THE EFFECT? 3 THE SOLUTION(S)? 4 WARNINGS/SUGGESTIONS? 5 PONDERABLES? 6 QUESTIONS? #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Problem? 5 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Problem? § SQL language (ca. 1970) is simply an suggested concept; many vendor implementations of that concept exist § Organizations (ANSI and ISO ) exist to (among other things) define and maintain a “vendor-neutral” definition of the SQL (relational) concept § IBM Db 2 (and other SQL implementations) vary (a little or a lot) from this “standard” definition 6 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Problem (continued)? § Starting with Db 2 10, and subsequent releases, IBM has attempted to conform its Db 2 SQL behavior (in some areas) to ANSI/ISO SQL standards. Unfortunately, this action changed the way certain built-in functions (BIFs) relating to numeric to character string conversion return data to the calling program. § See Notes for details § A ZPARM exists to simulate Db 2 9 behavior 7 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Effect? 8 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Effect? § Unanticipated (incorrect) results may/will be returned to the calling application (if changes are not made) when a version of Db 2 no longer allows these functions to operate in “V 9 simulation mode” or has the setting turned off. § Only manual investigation can determine if an identified function call is affected by this change – or assume the worst and change ALL char/varchar calls. 9 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Solution(s)? 10 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Solution(s)? § Start IFCID 366 or 376 in Db 2 subsystems … 11 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Solution(s) (continued)? § Prepare CA-Detector collection profiles 12 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Solution(s) (continued)? § Prepare CA-Detector Start Collection Command 13 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Solution(s) (continued)? § Each Day (see complete process in Notes) – Capture IFCIDs from previous day – Extract CA-Detector data from previous day – for each SSID 14 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Solution(s) (continued)? § Each Day (continued) – Match IFCID-detected threads to CA-Detector Output – Drop threads not containing “CHAR“ – Drop duplicate queries (from different intervals) 15 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Solution(s) (continued)? § Each Week – Accumulate Daily output(s) – Match authid of thread to manager – Email weekly report 16 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Solution(s) (continued)? 17 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
The Solution(s) (continued)? § Change all CHAR() to CHAR 9() § Change all VARCHAR() to VARCHAR 9() 18 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
Warnings/Suggestions? 19 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
Warnings/Suggestions? § Detector releases prior to 19 may not be able to handle 24 x 7 dynamic SQL capture processing § Be current on ALL Detector fixes § Datastore sizes may increase up to 8 -fold 20 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
Warnings/Suggestions (continued)? § Application code changes must be in specific order – Timestamp format change must completed in applcompat V 10 – unmodified timestamps will receive -180 in applcompat V 11 – BIF changes must be completed in applcompat V 11 – new functions not accessible in applcompat V 10 – Testing for both may be accomplished dynamically (see notes): 21 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
Ponderables? 22 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
Ponderables? § Is all this grabbing the sql text really necessary? § How long will the BIF zparm override be available? § How do we “entice” development co-operation? § All environments? Test? Prod? § Answers (at least for us) in Notes 23 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
Questions? 24 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
Thank you. Stay connected at communities. ca. com 25 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
Mainframe For more information on Mainframe, please visit: http: //cainc. to/CAW 17 -Mainframe 26 #CAWORLD #NOBARRIERS COPYRIGHT © 2017 CA. ALL RIGHTS RESERVED
- Slides: 26