DB2 - L

Back to discussions
Expand all | Collapse all

DB2 for z/os 12: BIF_COMPATIBILITY

  • 1.  DB2 for z/os 12: BIF_COMPATIBILITY

    Posted Jan 21, 2021 04:53 AM

    I (and everyone else at our site) would like our developers/maintainers to change code only when necessary.

    When looking at the occurences of CHAR() and VARCHAR(), I want to have them test under both

    BIF_COMPATIBILITY=V9_DECIMAL_VARCHAR And BIF_COMPATIBILITY=CURRENT to see if the output is different, thus requiring a change of (var)char to (var)char9 – but NOT if the output does NOT change.

    But can I do that? Even if I wanted to change the zparm dynamically, it is SSID-wide and would affect many active  processes currently not being tested

    Is there a session variable I can set to test both ways (as I can for applcompat) ? Or should the developers bite the bullet and change all (var)char to (var)char9 ?

     

    Thank you

    Chris Hoelscher

    Lead Sys DBA

    IBM Global Technical Services on assignmemt to Humana Inc.

    T 502.476.2538  or 502.407.7266

     

    The information transmitted is intended only for the person or entity to which it is addressed
    and may contain CONFIDENTIAL material. If you receive this material/information in error,
    please contact the sender and delete or destroy the material/information.

    Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
    do not discriminate on the basis of race, color, national origin, ancestry, age, disability, sex,
    marital status, gender, sexual orientation, gender identity, or religion. Humana Inc. and its subsidiaries do not
    exclude people or treat them differently because of race, color, national origin, ancestry, age,
    disability, sex, marital status, gender, sexual orientation, gender identity, or religion.

    English: ATTENTION: If you do not speak English, language assistance services, free
    of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

    Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
    gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

    繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
    服務。請致電 1‐877‐320‐1235 (TTY: 711)。

    Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
    pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

    Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
    pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

    한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
    이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.


  • 2.  RE: DB2 for z/os 12: BIF_COMPATIBILITY

    Posted Jan 21, 2021 06:55 AM
    If you decide to do this remember that changing the BIF_COMPATIBILITY field does not affect any in cache SQL. So you either have to RUNSTATS it out or bounce the sub-system.

    What I have done is a bit different but it worked...
    Create a new CLONE subsystem but with BIF_COMPATIBILTY set to CURRENT and APPLCOMPAT set to maximum level you have
    Capture as much workload as you can from original Subsystem using a tool of your choice both static and dynamic SQL and batch workload as well.
    Replay that complete workload on the CLONE
    Capture all outputs and see if the differences are ok or not

    Not simple, not straightforward and requires a lot of non Db2 knowledge for the batch workload but once done you can get a very good feeling that you can adjust the ZPARMs in the real world and then throw away the clone!
    The icing on the cake is to, at the same time, EXPLAIN all the dynamic SQL and see if your access paths change!

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich


    Original Message


  • 3.  RE: DB2 for z/os 12: BIF_COMPATIBILITY

    Posted Jan 21, 2021 09:28 AM
    Chris,

    one other item to review, if you have an accelerator (IDAA), check if CHAR9 / VARCHAR9 will disable selection of the SQL for acceleration. Its been a couple of years since I last reviewed BIF_COMPAYIBILITY, but I recall a least one solution I reviewed could be an issue here.

    Regards
    Jack

    ------------------------------
    Jack Campbell
    Saxon Consulting, Inc.
    ------------------------------

    Original Message


  • 4.  RE: DB2 for z/os 12: BIF_COMPATIBILITY

    Posted Jan 21, 2021 10:27 AM

    Well I have no IDEA (or IDAA for that matter)

     

    Thanks,

    Chris Hoelscher

    Lead Sys DBA

    IBM Global Technical Services on assignmemt to Humana Inc.

    T 502.476.2538  or 502.407.7266

     




    Original Message


  • 5.  RE: DB2 for z/os 12: BIF_COMPATIBILITY

    Posted Jan 21, 2021 10:30 AM

    Very droll, Chris.  Take the rest of the half-hour off.

     

    Cheers,

     

     

    Raymond

     




    Original Message


  • 6.  RE: DB2 for z/os 12: BIF_COMPATIBILITY

    Posted Jan 21, 2021 06:33 PM
    Chris,

    Not every CHAR or VARCHAR function is a problem.  Only when the argument is a decimal data type I think.   So if argument was Integer, Smallint ,Date,Time etc. no problem. So I don't think we want to change them all to CHAR9 and VARCHAR9.
    You have potentially both Static and Dynamic SQL to worry about.  
    So IBM provided that trace to actually detect them.
    If I analyse all Static SQLs, I can detect CHAR and VARCHAR functions quite easily, but determining the datatype of the argument is tricky.
    If the argument is just a Hostvar or Column, we can detect that being decimal.
    If its an expression, that maybe trickier although certain expressions are more obviously DATE/TIME related.
    So I believe its possible to scan all static SQLs and look for possible cases, rather than definite cases.
    Note that many packages found could even be obsolete.  

    So starting a few years back, sites might have been detecting them and gradually changing. them.
    However I wonder if CHAR9 has been used widely, since it was not available in a timely manner originally.
    While (VAR)CHAR9 was the simple solution, other solutions are possible.
    If one wanted leading zeros in the CHAR representation one could have used DIGITS although that does not include the sign.
    Many pieces of code did not actually need a sign character, but have to analyse the individual SQLs on a case by case basis.


    ------------------------------
    Michael Hannan
    DB2 SQL and Performance Specialist
    ------------------------------

    Original Message


  • 7.  RE: DB2 for z/os 12: BIF_COMPATIBILITY

    Posted Jan 21, 2021 09:20 PM
    Capture IFCID 376. You'll get a record when your code depends on
    BIF_COMPATIBILITY=V9 / V9_DECIMAL_VARCHAR

    General recommendation is to run it for a day, fix anything you find. Rerun for a longer
    period, repeat.

    James Campbell


    On 21 Jan 2021 at 9:53, Chris Hoelscher via Internati wrote:

    > I (and everyone else at our site) would like our developers/maintainers to change code only when necessary.
    > When looking at the occurences of CHAR() and VARCHAR(), I want to have them test under both
    > BIF_COMPATIBILITY=V9_DECIMAL_VARCHAR And BIF_COMPATIBILITY=CURRENT to see if the output is different, thus requiring a change of (var)char to (var)char9 - but NOT if the output does NOT change.
    > But can I do that? Even if I wanted to change the zparm dynamically, it is SSID-wide and would affect many active processes currently not being tested
    > Is there a session variable I can set to test both ways (as I can for applcompat) ? Or should the developers bite the bullet and change all (var)char to (var)char9 ?
    >
    > Thank you
    > Chris Hoelscher
    > Lead Sys DBA
    > IBM Global Technical Services on assignmemt to Humana Inc.
    > T 502.476.2538 or 502.407.7266
    >

    --
    This email has been checked for viruses by AVG.
    https://www.avg.com


    Original Message


  • 8.  RE: DB2 for z/os 12: BIF_COMPATIBILITY

    Posted Jan 21, 2021 10:55 PM

    Thanks to all – I have been running IFCID 376 for 4+ years now – I produce some very nice reports for host-based transactions showing the subsystem, coll/pkg, the cics or jobname, which ifcode reason, and the date of the most recent hit

     

    For distributed threads, I capture all SQL thru CA-detector and then match the attributes of the SQL to the attributes of the IFCID 376 entry.

     

    My reason for asking all this is I can't seem to make anybody in development do anything (for 4+ years) (*I* am not allowed to touch code) – so I am looking for ways to make this conversion as easy as possible – while I try to get management to back this initiative to get this taken care of  

     

    Chris Hoelscher

    Lead Sys DBA

    IBM Global Technical Services on assignmemt to Humana Inc.

    T 502.476.2538  or 502.407.7266

     




    Original Message


  • 9.  RE: DB2 for z/os 12: BIF_COMPATIBILITY

    Posted Jan 21, 2021 11:15 PM
    Edited by Michael Hannan Jan 21, 2021 11:17 PM
    Just an added thought to show that conversion to old format is possible without the CHAR9 function, although quite cumbersome.
    I think the functions I use here are IDAA supported. 
    Select DEC15C2
          ,char(substr('- ',sign(dec15C2)+2, 1) concat
             lpad(cast(abs(dec15C2)
             as VARCHAR(17)), 17, '0'), 18) pad
          ,cast(DEC15C2 as CHAR(17)) cast1
          ,char9(dec15c2) concat '<'
    from dec
    fetch first 10 rows only
    with ur;
      ---------+---------+---------+---------+---------+---------+---------+-------
            DEC15C2  PAD                 CAST1
      ---------+---------+---------+---------+---------+---------+---------+-------
         1194452.56   00000001194452.56  1194452.56      0000001194452.56<
         1104923.20   00000001104923.20  1104923.20      0000001104923.20<
          162898.53   00000000162898.53  162898.53       0000000162898.53<
          -43901.10  -00000000043901.10  -43901.10      -0000000043901.10<
         -138783.51  -00000000138783.51  -138783.51     -0000000138783.51<
           -5979.06  -00000000005979.06  -5979.06       -0000000005979.06<
          403250.14   00000000403250.14  403250.14       0000000403250.14<
      2032524238.12   00002032524238.12  2032524238.12   0002032524238.12<
          907238.94   00000000907238.94  907238.94       0000000907238.94<
              32.76   00000000000332.76  332.76          0000000000332.76<


    ------------------------------
    Michael Hannan
    DB2 SQL and Performance Specialist
    ------------------------------

    Original Message


  • 10.  RE: DB2 for z/os 12: BIF_COMPATIBILITY

    Posted Jan 23, 2021 02:40 AM
    Based on past experience I think that at some time IBM will pull the plug on
    BIF_COMPATIBILITY. And then Humana will have to start thinking about how they will
    handle the situation.

    Now - they have time to plan. Then - maybe not.

    James Campbell


    On 22 Jan 2021 at 3:54, Chris Hoelscher via Internati wrote:

    > Thanks to all - I have been running IFCID 376 for 4+ years now - I produce some very nice reports for host-based transactions showing the subsystem, coll/pkg, the cics or jobname, which ifcode reason, and the date of the most recent hit
    >
    > For distributed threads, I capture all SQL thru CA-detector and then match the attributes of the SQL to the attributes of the IFCID 376 entry.
    >
    > My reason for asking all this is I can't seem to make anybody in development do anything (for 4+ years) (*I* am not allowed to touch code) - so I am looking for ways to make this conversion as easy as possible - while I try to get management to back this initiative to get this taken care of
    >
    > Chris Hoelscher
    > Lead Sys DBA
    > IBM Global Technical Services on assignmemt to Humana Inc.
    > T 502.476.2538 or 502.407.7266
    >
    >
    >
    > -------------------------------------------
    > Original Message:
    > Sent: 1/21/2021 9:20:00 PM
    > From: James Campbell
    > Subject: RE: DB2 for z/os 12: BIF_COMPATIBILITY
    >
    > Capture IFCID 376. You'll get a record when your code depends on
    > BIF_COMPATIBILITY=V9 / V9_DECIMAL_VARCHAR
    >
    > General recommendation is to run it for a day, fix anything you find. Rerun for a longer
    > period, repeat.
    >
    > James Campbell
    >
    >
    > On 21 Jan 2021 at 9:53, Chris Hoelscher via Internati wrote:
    >
    > > I (and everyone else at our site) would like our developers/maintainers to change code only when necessary.
    > > When looking at the occurences of CHAR() and VARCHAR(), I want to have them test under both
    > > BIF_COMPATIBILITY=V9_DECIMAL_VARCHAR And BIF_COMPATIBILITY=CURRENT to see if the output is different, thus requiring a change of (var)char to (var)char9 - but NOT if the output does NOT change.
    > > But can I do that? Even if I wanted to change the zparm dynamically, it is SSID-wide and would affect many active processes currently not being tested
    > > Is there a session variable I can set to test both ways (as I can for applcompat) ? Or should the developers bite the bullet and change all (var)char to (var)char9 ?
    > >
    > > Thank you
    > > Chris Hoelscher
    > > Lead Sys DBA
    > > IBM Global Technical Services on assignmemt to Humana Inc.
    > > T 502.476.2538 or 502.407.7266
    > >
    >
    > --
    > This email has been checked for viruses by AVG.
    > https://www.avg.com
    >
    > -------------------------------------------
    > Original Message:
    > Sent: 1/21/2021 4:53:00 AM
    > From: Chris Hoelscher
    > Subject: DB2 for z/os 12: BIF_COMPATIBILITY
    >
    > I (and everyone else at our site) would like our developers/maintainers to change code only when necessary.
    > When looking at the occurences of CHAR() and VARCHAR(), I want to have them test under both
    > BIF_COMPATIBILITY=V9_DECIMAL_VARCHAR And BIF_COMPATIBILITY=CURRENT to see if the output is different, thus requiring a change of (var)char to (var)char9 - but NOT if the output does NOT change.
    > But can I do that? Even if I wanted to change the zparm dynamically, it is SSID-wide and would affect many active processes currently not being tested
    > Is there a session variable I can set to test both ways (as I can for applcompat) ? Or should the developers bite the bullet and change all (var)char to (var)char9 ?
    >
    > Thank you
    > Chris Hoelscher
    > Lead Sys DBA
    > IBM Global Technical Services on assignmemt to Humana Inc.
    > T 502.476.2538 or 502.407.7266
    >


    --
    This email has been checked for viruses by AVG.
    https://www.avg.com


    Original Message