2021-08 ICI – Db2 12 Update

This month, I would like to review the ICIs that we have had for a few releases plus those that have recently appeared, and then the trouble with twelve …

It began …

Db2 development realised that something had gone horribly wrong when a bunch of Db2 users suddenly found that the output from their queries was no longer what it should be … After a bit of digging, the CHAR format rewrite was found to be the root cause and the fix was hastily created – BIF_COMPATIBILITY ZPARM.

What’s in a name?

Well, then VARCHAR happened and along came a very unpleasent problem with JAVA timestamps and, as I have documented in earlier BLOGs, it all started getting silly with one ZPARM being used for multiple format problems.

Along came the ICI (Incompatible Change Indicator)

So in Db2 10 we got a new IFCID, the 366, which was spat out at *every* prepare (bind) of any SQL that, possibly, contained an ICI. Now we started off pretty small with just three ICI’s: the first two being the reformatted output of CHAR and VARCHAR and the third being the TIMESTAMP format problem.

Db2 10 updates for Db2 11

Here they brought out numbers four to nine to handle all the little changes in Db2 11 so that you got the alert in Db2 10 before it bit you in Db2 11 – all well and good.

Db2 11 updates

The big change, was the brand new IFCID 376 – which is the evil twin of the 366. The only difference being that Db2 cached the entries, so you basically got a rolled up 366 – apart from one tiny little detail. The Execution count was missing. For the 366 it is 1:1, but for the 376 it is 1:nnnnn which could be any positive integer. They then added the 11nn range, going all the way up to 1111, and then they brought in 1112 for empty XML tags. Now all of these have been discussed in my earlier blogs.

What’s new in the ICI World?

Db2 12 of course! They brought out 1201 very early on due to POWER causing a problem. The output on overflow changed from a negative to a positive SQLCODE, which can of course cause „problems“… Why did this change even happen? IBM rewrote the code from using LE 32 bit assembler math calls to using C, and so the function „knew“ if it overflowed and could return a warning saying so, whereas the 31 bit assembler just died a death and you got a negative SQLCODE.

Naming Convention?

Then it went quiet for a while until something weird happened: 1215031 and 1215032 appeared. Now, at first, I liked the idea of putting the FL into the ICI, but then I realised it was actually pretty pointless and just made it more confusing !

1215031 is issued when you could qualify a row with NULL in the DATA CHANGE OPERATION column using the FOR SYSTEM_TIME FROM/BETWEEN predicate on a system period temporal table with AUDITING.

1215032 is issued when you attempt to call stored procedure SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY as this is no longer supported for data replication calls.

1204 (note the FL has gone…) is issued when you use CURRENT_SERVER or CURRENT_TIMEZONE as a column or variable name.

In the Docu it says 1202, but you actually get a 1215031 – and there’s no mention of 1215032 and 1204!

More new ones

Meanwhile, not (nothing?) to do with Db2 12, IBM also brought out

11 for using SELECT INTO syntax with a UNION

[12 was thankfully skipped!]

13 for INSERT/UPDATE/DELETE using an attribute WITH UR

Both of these were more parsing bugs than anything evil, but both require code changes if they appear!

Utility Time

Now IBM have enhanced the LOAD utility with LOAD FORMAT DELIMITED for correct packed numeric data support when one or more virtual decimal digits exist. This has caused another problem, as the data loaded could be viewed (as it was by one of our customers), as inconsistent. IBM then created another APAR to roll back the change and introduce a new ZPARM LOAD_DEL_IMPLICIT_SCALE to control how these numbers should be loaded. Default is NO, like it used to be, with an implied decimal point at the far right of the data. At the same time, it will now alert users that they could have an incompatibility with the LOAD by changing IFCID 25 to set a new bit. This warns you that you have done a LOAD into a table where there is packed decimal data with one or more digits after the virtual decimal point. If YES, then load interprets the Scale setting in the LOAD statement. For details please see APARs PH28104 and PH36908.

Pain Point for you?

The above mentioned new notification is a bit strange (pardon the pun), as there is already the IFCID 376 for incompatibilities. Now you must also start the IFCID 25 and go checking bits. So my question to you all is: Have you got this problem and, if so, do you think it is worth it to integrate IFCID 25 bit checking into SOFTWARE ENGINEERINGs/SEGUSs current ICI/BIF Use Case in our WorkLoad Expert and/or our BIF/ICI Freeware software?

The future is bright

As far as ICIs are concerned they just keep on rolling!

As always if you have any comments, especially with regard to IFCID 25, please feel free to e-mail!

TTFN

Roy Boxwell

Tridex September 2016

SEGUS & SOFTWARE ENGINEERING präsentieren

TRIDEX – am Dienstag den 27. September 2016

Compliance with compliments! Viable DB2 z/OS workload tracking.

Audit and Compliance is a need that many companies want and have to fulfill.

There’s different ways and tools that promise to be able to do it, but what can they really do and what are the associated costs? This presentation introduces DB2 10/11 technology exploitation that delivers any DML, DDL, DCL being executed in a DB2 environment along with identification details. Learn how you can run Audit analytics against a long‐term repository, pinpointing who executed a query, when and from where. Analyze your entire workload to understand access patterns and abnormalities.

1-Audit needs and musts
Take a journey to GLB HIPAA PCI‐DSS Basel III Sarbanes‐Oxley CA SB1386 Federal Information Security Management Act “ed Flag”Rules (FRCA)5.
2-Solution overview and their Pros/Cons
Get an overview about the existing solutions and understand how they work.
3-The viable way – let DB2 do the magic!
Learn about DB2 enhancements in DB2 10/11 that deliver the DB2 workload being processed and understand why it’s so efficient.
4-Customer results from the banking industry
Receive some experience from a large banking company and how they successfully replaced their DB2 Audit feature based reporting by a modern SQL tracking and analytics process.

 

The Delaware Valley DB2 Users Group

SEGUS & SOFTWARE ENGINEERING präsentieren

DVDUG – am Mittwoch den 28. September 2016

Compliance with compliments! Viable DB2 z/OS workload tracking.

Audit and Compliance is a need that many companies want and have to fulfill.

There’s different ways and tools that promise to be able to do it, but what can they really do and what are the associated costs? This presentation introduces DB2 10/11 technology exploitation that delivers any DML, DDL, DCL being executed in a DB2 environment along with identification details. Learn how you can run Audit analytics against a long‐term repository, pinpointing who executed a query, when and from where. Analyze your entire workload to understand access patterns and abnormalities.

1-Audit needs and musts
Take a journey to GLB HIPAA PCI‐DSS Basel III Sarbanes‐Oxley CA SB1386 Federal Information Security Management Act “ed Flag”Rules (FRCA)5.
2-Solution overview and their Pros/Cons
Get an overview about the existing solutions and understand how they work.
3-The viable way – let DB2 do the magic!
Learn about DB2 enhancements in DB2 10/11 that deliver the DB2 workload being processed and understand why it’s so efficient.
4-Customer results from the banking industry
Receive some experience from a large banking company and how they successfully replaced their DB2 Audit feature based reporting by a modern SQL tracking and analytics process.

 

Wisconsin DB2 Users Group September 2016

SEGUS & SOFTWARE ENGINEERING präsentieren

WDUG – am Dienstag den 14. September 2016

Don’t let ICIs put your DB2 application in the ICU!

How to discover incompatible changes in your DB2 System

The what and why of ICIs
If you’ve been paying close attention, you will have noticed that over the course of the past couple releases of DB2 for z/OS, IBM has been making changes that can modify the behavior of your DB2 application programs. The number of incompatible changes being introduced by IBM started off slowly in DB2 9 but has grown to a significant number today. So much so that there are ways to trace incompatible changes (using IFCIDs and ICIs, or Incompatible Change Indicators), as well as methods to repress the changes, even if only for a period of time.
Ways to deal with incompatibilities
This session will discuss the incompatible changes, their potential impact on your applications, as well as provide guidance on how to tackle the whole experience. And you’ll also see how SEG’s Workload Expert technology makes it easier to manage and control these incompatible changes. With all of this information at your disposal, you can make sure that your DB2 applications do not wind up in the ICU (intensive care unit)!
Customer experiences
Now that we all know everything about ICI’s, let us have a look at how the separatly licensable BIF/ICI Use Case of  WorkloadExpert can help you find where the “bad guys” are and how to continuously check that everything is ok. This presentation will show how you can find out ICI Details for static and dynamic SQL, and then we will show real customer results from a “BiF hunt”.

Speaker’s biography

Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in DB2 recovery issues and database maintenance, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under real‐world recovery pressure. His activities cover EMEA, as well as North America through SE’ U.S. subsidiary, SEGUS Inc. He’s involved in the development of SE’ maintenance and recovery Solutions.

Midwest Database User Group September 2016

SEGUS & SOFTWARE ENGINEERING präsentieren

MWDUG – am Mittwoch den 15. September 2016

Don’t let ICIs put your DB2 application in the ICU!

How to discover incompatible changes in your DB2 System

The what and why of ICIs
If you’ve been paying close attention, you will have noticed that over the course of the past couple releases of DB2 for z/OS, IBM has been making changes that can modify the behavior of your DB2 application programs. The number of incompatible changes being introduced by IBM started off slowly in DB2 9 but has grown to a significant number today. So much so that there are ways to trace incompatible changes (using IFCIDs and ICIs, or Incompatible Change Indicators), as well as methods to repress the changes, even if only for a period of time.
Ways to deal with incompatibilities
This session will discuss the incompatible changes, their potential impact on your applications, as well as provide guidance on how to tackle the whole experience. And you’ll also see how SEG’s Workload Expert technology makes it easier to manage and control these incompatible changes. With all of this information at your disposal, you can make sure that your DB2 applications do not wind up in the ICU (intensive care unit)!
Customer experiences
Now that we all know everything about ICI’s, let us have a look at how the separatly licensable BIF/ICI Use Case of  WorkloadExpert can help you find where the “bad guys” are and how to continuously check that everything is ok. This presentation will show how you can find out ICI Details for static and dynamic SQL, and then we will show real customer results from a “BiF hunt”.

Speaker’s biography

Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in DB2 recovery issues and database maintenance, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under real‐world recovery pressure. His activities cover EMEA, as well as North America through SE’ U.S. subsidiary, SEGUS Inc. He’s involved in the development of SE’ maintenance and recovery Solutions.

2016-05 What’s in an Acronym? ICI

Do you know the difference between BIF & ICI?

Release migration: Do you know which role ICIs are playing in the Db2 compatibility?

Just over a year ago I did a little newsletter all about IFICDs 366 and 376, what I call BIFCIDs, that report usage of a function that has changed or a function that works now, but will either fail or work differently, at some point in the future, e.g.: after a Db2 release migration.

When is a BiF not a BiF?

Now, back then I labelled these guys “BIFCIDs” and asked: “Where’s the BiF?” I see now that the name I chose was actually pretty inaccurate! If you take the time to study the macro versions of 366 and 376 (all described in the SDSNMACS library member DSNDQW05) you see that from the 22 IFCIDs only six are actually for a Built-in Function “problem” – all the rest are really ICIs.

What’s an ICI? It’s a TLA (another nice acronym), from Db2 development and stands for “Incompatible Change Indicator”. A really catchy name for a really nasty problem! So let us review the IFCID details, but now from the perspective of “Are you a BIFCID or are you an ICI?”

 

The details for BIFCIDs – die BIFs der IFCIDS list

***********************************************************************
**  QW0366FN = 1                                                      *
**    Indicates that the pre Version 10 CHAR built-in function has    *
**    been invoked. There is an incompatible change to the output of  *
**    the CHAR function for some decimal data. The zparm              *
**    BIF_COMPATIBILITY and/or the SYSCOMPAT_V9 schema have been used *
**    by this application to get the old behavior. Please make the    *
**    appropriate changes and rebind with the SYSCURRENT schema to    *
**    use the Version 10 CHAR(decimal) built-in function.             *
**    (PM29124 V10 only, usermod V8/V9)                               *
**  QW0366FN = 2                                                      *
**    Indicates that the pre Version 10 VARCHAR built-in function or  *
**    CAST(decimal AS CHAR or VARCHAR) has been invoked.              *
**  QW0366FN = 10                                                     *
**   RTRIM, LTRIM or STRIP version 9 being used with mixed data       *
**  QW0366FN = 1109                                                   *
**    Indicates that CAST(string AS TIMESTAMP) was processed for the  *
**    input string of length 8 and input was treated as a store clock *
**    value (or input string was of length 13 and was treated as a    *
**    GENERATE_UNIQUE value). This behavior is incorrect for a CAST   *
**    and is valid for TIMESTAMP built-in function only. This behavior*
**    is being corrected in Db2 11 so that input to CAST is not       *
**    treated as a store clock value nor GENERATE_UNIQUE.             *
**  QW0366FN = 1110                                                   *
**    Indicates the integer argument of SPACE function is greater     *
**    than 32764.                                                     *
**  QW0366FN = 1111                                                   *
**    Indicates the optional integer argument of VARCHAR function     *
**    has a value greater than 32764.                                 *
***********************************************************************

 

IFCIDS list follow: the details for ICIs

***********************************************************************
**  QW0366FN = 3                                                      *
**    Indicates that an unsupported character representation of a     *
**    timestamp string was used. PM48741 V10 only.                    *
**  QW0366FN = 4                                                      *
**   A QW0366FN 4 record indicates that the statement uses the        *
**    word ARRAY_EXISTS as an unqualified user-defined function name  *
**    in a context that may be incompatible with Version 11.          *
**  QW0366FN = 5                                                      *
**    A QW0366FN 5 record indicates that the statement uses the       *
**    word CUBE as an unqualified user-defined function name          *
**    in a context that may be incompatible with Version 11.          *
**  QW0366FN = 6                                                      *
**   A QW0366FN 6 record indicates that the statement uses the        *
**    word ROLLUP as an unqualified user-defined function name        *
**    in a context that may be incompatible with Version 11.          *
**  QW0366FN = 7                                                      *
**   A QW0366FN 7 record indicates that Db2 for z/OS server issued    *
**   a SQLCODE -301 for incompatible data type conversion from        *
**   string data type (e.g. CHAR, VARCHAR, GRAPHIC, VARGRAPHIC        *
**   etc.) to numeric data type in V10 CM mode when implicit          *
**   cast is not supported or V10 NFM mode when DDF_COMPATIBILITY     *
**   zparm is set to DISABLE_IMPCAST_NJV or SP_PARMS_NJV to           *
**   disable implicit cast, and the client is CLI driver              *
**   or v11 NFM mode & APPLCOMPAT = V10R1 when DDF_COMPATIBILITY      *
**   is set to SP_PARMS_NJV or DISABLE_IMPCAST_NJV to disable         *
**   implicit cast either from string data type to numeric or         *
**   from numeric data type to string data type.                      *
**  QW0366FN = 8                                                      *
**   A QW0366FN 8 record indicates that Db2 for z/OS server           *
**   returned output data match the data types of the                 *
**   corresponding CALL statement arguments when DDF_COMPATIBILITY    *
**   zparm is set to SP_PARMS_NJV.                                    *
**                                                                    *
**  QW0366FN = 9                                                      *
**    A QW0366FN 9 record indicates a data type conversion from       *
**    a TIMESTAMP WITH TIME ZONE input to a TIMESTAMP data            *
**    during input host variable bind-in process on server when       *
**    DDF_COMPATIBILITY zparm is set to IGNORE_TZ to ignore the       *
**    time zone information sent by Java IBM Data Server Driver.      *
**  QW0366FN = 1101                                                   *
**    Indicates that the INSERT statement that inserts into an XML    *
**    column without XMLDOCUMENT function has been processed (which   *
**    should result in SQLCODE -20345 when run on Db2 release prior   *
**    to V11). Starting with V11, SQL error will no longer be issued. *
**    Application will no longer recieve SQLCODE for this statement.  *
**  QW0366FN = 1102                                                   *
**    Indicates that V10 XPath evaluation behavior was in effect which*
**    resulted in an error. For instance, a data type conversion error*
**    could have occured for a predicate that would otherwise be      *
**    evaluated to false. Starting from V11, such "irrelevant" errors *
**    might be suppressed so an application might no longer recieve   *
**    the SQLCODE for this statement.                                 *
**  QW0366FN = 1103                                                   *
**    Indicates that a dynamic SQL uses the ASUTime limit that has    *
**    been set for the entire thread for RLF reactive governing.      *
**    For instance, when a dynamic SQL is processed from package A,   *
**    if the ASUTime limit is already set during other dynamic SQL    *
**    processing from package B in the same thread, the SQL from      *
**    package A will use the ASUTime limit set during the SQL         *
**    processing from package B. Stating with v11, dynamic SQLs from  *
**    multiple packages will use the ASUTime limit that is set        *
**    considering its own package information.                        *
**  QW0366FN = 1104, 1105, 1106, 1107                                 *
**    Indicates that CLIENT special register (CLIENT_USERID,          *
**    CLIENT_WRKSTNNAME, CLIENT_APPLNAME, CLIENT_ACCTNG) has been set *
**    to a value that is longer than what is supported prior to V11.  *
**    A shorter value has been used instead.                          *
**  QW0366FN = 1108                                                   *
**    Indicates that CLIENT special register (CLIENT_USERID,          *
**    CLIENT_WRKSTNNAME, CLIENT_APPLNAME, CLIENT_ACCTNG) has been set *
**    to a value that is longer than what is supported prior to V11.  *
**    Truncated values upto the supported lengths prior to v11 have   *
**    been used for RLF table search instead.                         *
**  QW0366FN = 1112                                                   *
**   Indicates the empty XML element is serialized to <X></X>         *
**   instead of <X/>.                                                 *
***********************************************************************

What does this all mean?

You might well be wondering: “What’s the point?” Well, the point is to try and get the ICI’s acknowledged in your company and to start checking for them now. Why do I say this? Because the APPLCOMPAT is only going to work for two down levels. What that means is that APPLCOMPAT at Db2 10 is good for Db2 11 and Db2 12 but *dies* horribly at Db2 12 +1 which, if IBM stick to their track record of Db2 delivery dates, will be October 2019. Not that far away in the future is it?

 

The BiF problem is fixed – long live the ICI Problem!

OK, you might have traced and tracked your BIFs, but what about the ICIs? At one firm I visited, all the BIFCIDs were clean until this February which, as a leap year, had 29 days. This caused different programs to run and…Tra La! New BIFCIDs came creeping out of the woodwork…along with some new ICIs…

 

Freeware to the rescue

To help you save time, just download and test our little BiF Freeware, which tracks down all ICIs and BiFs for you. Register, download, and then see where your system stands in the ICI rankings. We have also changed our Use Case “BIF usage” in WorkloadExpert to be only for BiFs and have introduced a new Use Case called “BIF/ICI occurrences” which shows them all.

As usual, any question or comments gladly welcome!

 

TTFN

Roy Boxwell

Heart of America DB2 Users Group September 2016

SEGUS & SOFTWARE ENGINEERING präsentieren

HOADUG – am Montag den 12. September 2016

Compliance with compliments! Viable DB2 z/OS workload tracking.

Audit and Compliance is a need that many companies want and have to fulfill.

There’s different ways and tools that promise to be able to do it, but what can they really do and what are the associated costs? This presentation introduces DB2 10/11 technology exploitation that delivers any DML, DDL, DCL being executed in a DB2 environment along with identification details. Learn how you can run Audit analytics against a long‐term repository, pinpointing who executed a query, when and from where. Analyze your entire workload to understand access patterns and abnormalities.

1-Audit needs and musts
Take a journey to GLB HIPAA PCI‐DSS Basel III Sarbanes‐Oxley CA SB1386 Federal Information Security Management Act “ed Flag”Rules (FRCA)5.
2-Solution overview and their Pros/Cons
Get an overview about the existing solutions and understand how they work.
3-The viable way – let DB2 do the magic!
Learn about DB2 enhancements in DB2 10/11 that deliver the DB2 workload being processed and understand why it’s so efficient.
4-Customer results from the banking industry
Receive some experience from a large banking company and how they successfully replaced their DB2 Audit feature based reporting by a modern SQL tracking and analytics process.

Don’t let ICIs put your DB2 application in the ICU!

How to discover incompatible changes in your DB2 System

The what and why of ICIs
If you’ve been paying close attention, you will have noticed that over the course of the past couple releases of DB2 for z/OS, IBM has been making changes that can modify the behavior of your DB2 application programs. The number of incompatible changes being introduced by IBM started off slowly in DB2 9 but has grown to a significant number today. So much so that there are ways to trace incompatible changes (using IFCIDs and ICIs, or Incompatible Change Indicators), as well as methods to repress the changes, even if only for a period of time.
Ways to deal with incompatibilities
This session will discuss the incompatible changes, their potential impact on your applications, as well as provide guidance on how to tackle the whole experience. And you’ll also see how SEG’s Workload Expert technology makes it easier to manage and control these incompatible changes. With all of this information at your disposal, you can make sure that your DB2 applications do not wind up in the ICU (intensive care unit)!
Customer experiences
Now that we all know everything about ICI’s, let us have a look at how the separatly licensable BIF/ICI Use Case of  WorkloadExpert can help you find where the “bad guys” are and how to continuously check that everything is ok. This presentation will show how you can find out ICI Details for static and dynamic SQL, and then we will show real customer results from a “BiF hunt”.

Speaker’s biography

Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in DB2 recovery issues and database maintenance, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under real‐world recovery pressure. His activities cover EMEA, as well as North America through SE’ U.S. subsidiary, SEGUS Inc. He’s involved in the development of SE’ maintenance and recovery Solutions.

IDUG 2016 DB2 European Tech Conference

SEGUS & SOFTWARE ENGINEERING präsentieren


„Access Path Recovery for DB2 11 & 12 using RUNSTATS Rescue“

15. November 2016

What is the number one reason for bad access paths? Bad statistics!

Why do you have bad statistics? Bad timing is the number one reason, followed by manually updated stats and then forgotten about.

RUNSTATS Rescue for DB2 z/OS saves you when these events happen. The basis is a complete copy of all productive required statistical data in the DB2 catalog. This should be regularly executed and the data saved away. The best way to do this is to use a Generation Gengroup which automatically stores “copies” of the data. RUNSTATS Rescue then provides a front end where you can use a variety of inputs to get back you good access paths.

  • Dynamic SQL, Static SQL completely covered
  • Why PLAN Stability doesn’t save you when you need it
  • What’s new in DB2 12 for Dynamic SQL that still will not save you

Audience experience Level

Beginner, Intermediate, Advanced

Speaker Biography

Roy Boxwell has more than 30 years of experience in MVS, OS/390, and z/OS environments – 26 of those in DB2. He specializes in installation, migration, and performance monitoring and tuning. Roy leads the SEG development team responsible for the real time database maintenance solutions. He is also an active participant, speaker and contributor on the IDUG DB2 Listserv and sends out a monthly DB2 z/OS Newsletter.


„Compliance with compliments! Viable DB2 z/OS workload tracking“.

16. November, 2016

Audit and Compliance is a need that many companies want and have to fulfill.

There’s different ways and tools that promise to be able to do it, but what can they really do and what are the associated costs? This presentation introduces DB2 10/11 technology exploitation that delivers any DML, DDL, DCL being executed in a DB2 environment along with identification details. Learn how you can run Audit analytics against a long‐term repository, pinpointing who executed a query, when and from where. Analyze your entire workload to understand access patterns and abnormalities.

Audience experience Level

Beginner, Intermediate, Advanced

Objectives

 Objective 1Audit needs and musts Take a journey to GLB HIPAA PCI‐DSS Basel III Sarbanes‐Oxley CA SB1386 Federal Information Security Management Act “ed Flag”Rules (FRCA)5.
 Objective 2Solution overview and their Pros/Cons Get an overview about the existing solutions and understand how they work.
 Objective 3 The viable way – let DB2 do the magic! Learn about DB2 enhancements in DB2 10/11 that deliver the DB2 workload being processed and understand why it’s so efficient.
 Objective 4 Customer results from the banking industry Receive some experience from a large banking company and how they successfully replaced their DB2 Audit feature based reporting by a modern SQL tracking and analytics process.


Speaker biography

Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in DB2 recovery issues and database maintenance, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under real‐world recovery pressure. His activities cover EMEA, as well as North America through SE’ U.S. subsidiary, SEGUS Inc. He’ involved in the development of SE’ maintenance andrecovery Solutions.

 

BIF HealthCheck Licensed Freeware for DB2 10

Detect static and dynamic SQL and match to the relevant collection, packages,…

BIF/ICI HealthCheck overview (Built-in Function Checker for DB2 z/OS)

BIF/ICI HealthCheck reports the following BIF incompatibilities in DB2 10

  • Execution of the DB2 9 for z/OS version of SYSIBM.CHAR(DECIMAL-EXPR)
  • Execution of the DB2 9 for z/OS version of SYSIBM.VARCHAR(DECIMAL-EXPR), CAST (DECIMAL AS VARCHAR), OR CAST (DECIMAL AS CHAR)

  • Use of an unsupported character string representation of a TIMESTAMP
  • Use of a USER-DEFINED FUNCTION (UDF) that has the unqualified name ARRAY_EXISTS
  • Use of a USER-DEFINED FUNCTION (UDF) that has the unqualified name CUBE
  • Use of a USER-DEFINED FUNCTION (UDF) that has the unqualified name ROLLUP

  • Execution of a non-Java client that called a Stored Procedure (SP) that is on the DB2 for z/OS Data Server, while subsystem parameter DDF_COMPATIBILITY was set to SP_PARMS_NJV (the Data Server returned output argument values whose data types matched the data types of the call statement arguments).

  • Execution of a SQL statement by a client non-Java application that included an unsupported conversion from a string type to a numeric type, while the DB2 z/OS Data Server environment was one of the following (the Data Server issues SQLCODE -301)
    • In version 10 Conversion Mode (CM)
    • In version 10 New-Function Mode (NFM) and implicit casting was disabled because subsystem parameter DDF_COMPATIBILITY was set to SP_PARMS_NJV, or DISABLE_IMPCAST_NJ

 

BIF/ICI incompatibilities in DB2 11

 

Mehr über BIF

BIF-Usage

Präsentation

BIF KompatibilitätDB2 10 Kompatibilität Mode

Änderungen bei der STRING Formatierung von Decimal Data bei der CHAR und VARCHAR built-in Funktion und bei der CAST Spezifikation mit CHAR und VARCHAR Ergebnis Typen sowie UNSUPPORTED TIMESTAMP STRINGs.

White PaperWo sind die BIFs?
Finding BIFs (engl)
Wo sind die BIFs? Und wie können wir in Zukunft problemlos mit BIFs leben?
Wege aus der mangelnden Aufwärtskompatibilität bei der Migration von DB2 Versionen
Newsletter2015-01 – BIFCIDS – Where’s the BIF? (engl.)How will you deal with loop-hole usage in production code?
VideoBIF Usage (engl)(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon
„Give and Take
Program“
Seite
Give and Take
Program
We have „GIVEn“ various free-of-charge Use Cases from SQL Workload Expert for DB2 z/OS like
1  Index Maintenance Costs
2  EXPLAIN Suppression
3  BIF Usage 
BIF HealthCheck – This last one is still available
We TAKE the anonymized results for research
and will communicate with the local User Groups for discussions
BIF Usage

„Give and Take
Program 3“

Kundenmeinungen
Präsentation


Customer Comments
Lesen Sie die Kundenmeinungen aus unterschiedlichen Industrie Sektoren

  • Gesetzliche Krankenversicherung
  • Automobil Industrie
  • IT Provider für Banken
  • Versicherungen

 

 

BIF HealthCheck Licensed Freeware for DB2 11

Detect static and dynamic SQL and match to the relevant collection, packages,…

BIF HealtchCheck overview (Built-in Function Checker for DB2 z/OS)

BIF HealthCheck reports the following BIF incompatibilities in DB2 11

  • Execution of the DB2 9 for z/OS version of SYSIBM.CHAR(DECIMAL-EXPR)
  • Execution of the DB2 9 for z/OS version of SYSIBM.VARCHAR(DECIMAL-EXPR), CAST (DECIMAL AS VARCHAR), OR CAST (DECIMAL AS CHAR)

  • Use of an unsupported character string representation of a TIMESTAMP
  • Use of the DB2 10 for z/OS default SQL path instead of the V11 path, which has more implicit Schemas

  • Execution of a non-Java client that called a Stored Procedure (SP) that is on the DB2 for z/OS Data Server, while subsystem parameter DDF_COMPATIBILITY was set to SP_PARMS_NJV (the Data Server returned output argument values whose data types matched the data types of the call statement arguments).
  • Execution of an insert statement that inserts into an XML column without the XMLDOCUMENT function, which generates SQLCODE -20345 on a DB2 release prior to V11, but does not generate an error starting in V11

  • V10 XPATH evaluation behavior was in effect, which resulted in an error (e.g. a data type conversion error occurred for a predicate that would otherwise be evaluated to false.). Starting in V11, such errors might be suppressed

  • Execution of a SQL statement by a client non-Java, or Java application that included an unsupported conversion from a string type to a numeric type, or from a numeric type to a string type while the DB2 z/OS Data Server environment was one of the following (the Data Server issues SQLCODE -301)

o The Data Server was in version 11 New-Function Mode (NFM)
o APPLICATION COMPATIBILITY was set to V10R1
o Implicit casting was disabled because subsystem parameter DDF_COMPATIBILITY was set to SP_PARMS_NJV, or DISABLE_IMPCAST_NJV

BIF incompatibilities in DB2 10

 

Mehr über BIF

BIF-Usage

Präsentation

BIF KompatibilitätDB2 10 Kompatibilität Mode

Änderungen bei der STRING Formatierung von Decimal Data bei der CHAR und VARCHAR built-in Funktion und bei der CAST Spezifikation mit CHAR und VARCHAR Ergebnis Typen sowie UNSUPPORTED TIMESTAMP STRINGs.

White PaperWo sind die BIFs?
Finding BIFs (engl)
Wo sind die BIFs? Und wie können wir in Zukunft problemlos mit BIFs leben?
Wege aus der mangelnden Aufwärtskompatibilität bei der Migration von DB2 Versionen
Newsletter2015-01 – BIFCIDS – Where’s the BIF? (engl.)How will you deal with loop-hole usage in production code?
VideoBIF Usage (engl)(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon
„Give and Take
Program“
Seite
Give and Take
Program
We have „GIVEn“ various free-of-charge Use Cases from SQL Workload Expert for DB2 z/OS like
1  Index Maintenance Costs
2  EXPLAIN Suppression
3  BIF Usage 
BIF HealtchCheck (Freeeware)– This last one is still available
We TAKE the anonymized results for research
and will communicate with the local User Groups for discussions
BIF Usage

„Give and Take
Program 3“

Kundenmeinungen
Präsentation

Inspiring experiences

[Customer Comments]

Lesen Sie die Kundenmeinungen aus unterschiedlichen Industrie Sektoren

  • Gesetzliche Krankenversicherung
  • Automobil Industrie
  • IT Provider für Banken
  • Versicherungen