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.

 

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.

 

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.

 

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.

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.

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.

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

GIVE and TAKE Programme 1, 2, 3


Give and Take 2020

Information on the Give and Take Programs 4,5,6,7


 

Previous Give & Take

We have „GIVEn“ various free-of-charge Use Cases from our SQL WorkloadExpert for Db2 z/OS like:

  1  Index Maintenance Costs

  2  EXPLAIN Suppression

  3  BIF Usage

Limited free-of-Charge Db2 Application

This Program started in Europe, during our 30th anniversary was such a success, that it is now being Extended for the benefit of North American Db2 z/OS sites.

SQL WorkloadExpert for Db2 z/OS (WLX) contains several “Use Cases”. We provided three of them, free of charge, for one month to different sites.

In return, we received their results. We’d like to share this inspiring experiences with you now.


Inspiring experiences

We TAKE the anonymized results for research

and will communicate with the local User Groups for discussions

Kundenmeinungen

3BIF USAGE
News
Lesen Sie die Kundenmeinungen aus unterschiedlichen Industrie Sektoren     [Customer Comments]

 

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

Erste Ergebnisse von Db2 z/OS Kunden

1Index Mantenance CostsNearly all the data we got back showed a positive result for created Indexes…
2EXPLAIN SuppressionAbout 10% of SQLs are actually “left over”…
3BIF Usage When migrating to a new Db2 version, the BIFs are not always compatible and an extreme amount of data is produced.

 

The difficulty of near-time analysis to track down BIFs within dynamic SQL have been solved with the BIF Usage Use Case…

[Ergebnisse von Db2 z/OS Kunden (engl)]

Program 3 – BIF Usage   

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

BIF Usage Video

2015-01 BIFCIDS – Where’s the BIF?

How will you deal with loop-hole usage in production code?

 

 

The IFCIDs 366 and 376

DB2 provides many and varied IFCIDs. But for today, I’m most interested in the 366 and 376. The 366 is available in DB2 10 and the 376 in DB2 11. Now I like to call these “BIFCIDs” because they are triggered whenever a BIF is used that will behave differently than it is currently used when moving to the next release of DB2. (It’s also triggered when changing Application Compatibility settings in DB2 11 and higher).

 

So where’s the BIF?

BIF Usage Video (11min:)       Presentation

Well, a BIF is a Built-In Function such as CHAR, DECIMAL, etc. There are hundreds of them these days. In the last few DB2 releases, IBM has changed a few to make DB2 more compatible with SQL standards. They have actually closed a couple of loop-holes, where “bad” data could be accepted and processed.

 

Loop-hole user?

What happens is: someone somewhere found this loop-hole and used it in production code. Now when you upgrade your DB2, this code will either fail or give erroneous results – which is never good. Hence IBM created the IFCID 366. This is output every time an SQL statement is PREPARED, or executed, that contains a candidate BIF. There were so many of these, that IBM introduced a sort of condensed version so it only triggered one for the first execution, or prepare, but sadly that IFCID—376—is only for DB2 11.

Where can, or will, this really hurt?

 

Looking into the documentation for these IFCIDs you will see a long list of when they are written:

***********************************************************************
**  IFCID 0366 is a serviceability trace.                            **
**  It can be used to identify applications that are affected        **
**  by incompatible changes.                                         **
**  The QW0366FN field indicates the type of incompatible Change:    **
**                                                                   **                                                      
**  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 = 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 = 10                                                    ** 
**  RTRIM, LTRIM or STRIP version 9 being used with mixed data       **
**                                                                   **
**  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 = 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. *                                 **
***********************************************************************

 

Useful stuff indeed!

Phew! Not a bad list, huh? Now you see why these IFCIDs are so useful. It could well be, that you have none of these “alive” in your system today. Or, of course, it could be that you get millions of the things! Somehow you will have to work out a way to save the data, analyse it to get to the root cause, and then, finally, fix the problem(s).

 

Saved by APPLCOMPAT?

You could argue that the new DB2 11 parameter Application Compatibility will save you, but this is really a false economy. All it enables is the guarantee that the code will still “run”. However, in two more DB2 releases the code will fail and, in two more releases – so about six years – who will even know *how* to change which piece of source code and, perhaps even, where is that source code?

 

Saved by BIFCIDs

Personally, what I would do, is : to run our SQL WorkloadExpert tool to trap all the required [B]IFCIDs for a few hours (at first!).Then I would analyse the results, fix the code where it needs fixing – and repeat! I would keep doing this until no IFCID records are coming out and I would be set!

BIF Usage Video (11min:)       Presentation

What is even better, is that our SQL WorkloadExpert will work correctly even when any new QW0366FN values appear – so when IBM decides to add another code (Like the new values 9 and 10 above for example) this BIF Usage still works correctly.

 

Of course, you may have another tool that you use at your site.

Can it see “Where’s the BIF?”

How will you deal with loop-hole usage in production code?

 

As usual, any question or comments gladly welcome!

 

TTFN

Roy Boxwell