-420 encountered after DB2 v11 upgrade

william giannelli

-420 encountered after DB2 v11 upgrade

Hello,

We have a developer that claims that after our DB2 v11 upgrade, they are now getting a -420 sqlcode after compiling in v11. In v10 they did not get a -420. They say they have made no code changes.

Does anyone know of any reason migrating to DB2 v11 would then cause a -420?

thanks

Bill

Philip Sevetson

-420 encountered after DB2 v11 upgrade
(in response to william giannelli)
**please note my email address change**
Bill G.,

I don’t offhand know of anything which would cause this. However: “they have made no code changes” means one thing if you’re dealing with bound/static SQL, another thing entirely if you’re using JDBC (or any other dynamic process) to handle SQL. I’m sure there are other examples, but the core of the problem is that unless you’re using static SQL, _everything_, from one end to the other, has to stay the same, in order for there not to be any risk.

Our DB2V11 CM upgrade broke our Websphere… we discovered that they were using a JDBC Type 3 call configuration, which is supported by DB2V10 but obsoleted by DB2V11 (and most of the rest of the world). The customer, of course, hadn’t made any changes… We solved that by upgrading the Websphere drivers using a DB2 Connect fixpack level for Type 4 and fully up to date. I don’t think that this is your problem, mind you; the example is illustrative.

In your situation, I’d recommend identifying the broken SQL by any means necessary, and testing it in a different process (SPUFI/DSNTEP2/QMF/what-have-you) to try to reproduce the error. If you can, but can’t see it, put it up on this List and some of us will see if we can torture some answers out of it.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: william giannelli [mailto:[login to unmask email]
Sent: Thursday, October 19, 2017 9:29 AM
To: [login to unmask email]
Subject: [DB2-L] - -420 encountered after DB2 v11 upgrade


Hello,

We have a developer that claims that after our DB2 v11 upgrade, they are now getting a -420 sqlcode after compiling in v11. In v10 they did not get a -420. They say they have made no code changes.

Does anyone know of any reason migrating to DB2 v11 would then cause a -420?

thanks

Bill

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Jim Tonchick

-420 encountered after DB2 v11 upgrade
(in response to Philip Sevetson)
If the program is the same, the DB2 release and mode is the same i.e.(NFM) and the table structure is the same, then it could be a data content issue. You could have bad data the the table where you receive the -420.


-----Original Message-----
From: Sevetson, Phil <[login to unmask email]>
To: '[login to unmask email]' <[login to unmask email]>
Sent: Fri, Oct 20, 2017 07:14 AM
Subject: [DB2-L] - RE: -420 encountered after DB2 v11 upgrade



<div id="AOLMsgPart_1.2_320e8d92-3d73-44be-ba8f-c7e3c81c2097">
<style scoped="">#AOLMsgPart_1.2_320e8d92-3d73-44be-ba8f-c7e3c81c2097 td{color: black;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}@font-face {font-family:Tahoma; panose-1:2 11 6 4 3 5 4 4 2 4;}.aolReplacedBody p.aolmail_MsoNormal,.aolReplacedBody li.aolmail_MsoNormal,.aolReplacedBody div.aolmail_MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:12.0pt; font-family:"Times New Roman","serif";}.aolReplacedBody a:link,.aolReplacedBody span.aolmail_MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;}.aolReplacedBody a:visited,.aolReplacedBody span.aolmail_MsoHyperlinkFollowed {mso-style-priority:99; color:purple; text-decoration:underline;}.aolReplacedBody p {mso-style-priority:99; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:12.0pt; font-family:"Times New Roman","serif";}.aolReplacedBody p.aolmail_MsoAcetate,.aolReplacedBody li.aolmail_MsoAcetate,.aolReplacedBody div.aolmail_MsoAcetate {mso-style-priority:99; mso-style-link:"Balloon Text Char"; margin:0in; margin-bottom:.0001pt; font-size:8.0pt; font-family:"Tahoma","sans-serif";}.aolReplacedBody span.aolmail_EmailStyle18 {mso-style-type:personal-reply; font-family:"Calibri","sans-serif"; color:#1F497D;}.aolReplacedBody span.aolmail_BalloonTextChar {mso-style-name:"Balloon Text Char"; mso-style-priority:99; mso-style-link:"Balloon Text"; font-family:"Tahoma","sans-serif";}.aolReplacedBody .aolmail_MsoChpDefault {mso-style-type:export-only; font-family:"Calibri","sans-serif";}@page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;}.aolReplacedBody div.aolmail_WordSection1 {page:WordSection1;}</style><div lang="EN-US" class="aolReplacedBody">
**please note my email address change**


<div class="aolmail_WordSection1">
<p class="aolmail_MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Bill G.,</span></p>
<p class="aolmail_MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span></p>
<p class="aolmail_MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I don’t offhand know of anything which would cause this. However: “they have made no code changes” means one thing if you’re dealing with bound/static SQL,
another thing entirely if you’re using JDBC (or any other dynamic process) to handle SQL. I’m sure there are other examples, but the core of the problem is that unless you’re using static SQL, _<i>everything</i>_, from one end to the other, has to stay the
same, in order for there not to be any risk.



Our DB2V11 CM upgrade broke our Websphere… we discovered that they were using a JDBC Type 3 call configuration, which is supported by DB2V10 but obsoleted by DB2V11 (and most of the rest of the world). The customer, of course, hadn’t made any changes… We solved
that by upgrading the Websphere drivers using a DB2 Connect fixpack level for Type 4 and fully up to date. I don’t think that this is your problem, mind you; the example is illustrative.
</span></p>
<p class="aolmail_MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span></p>
<p class="aolmail_MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">In your situation, I’d recommend identifying the broken SQL by any means necessary, and testing it in a different process (SPUFI/DSNTEP2/QMF/what-have-you)
to try to reproduce the error. If you can, but can’t see it, put it up on this List and some of us will see if we can torture some answers out of it.</span></p>
<p class="aolmail_MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span></p>
<p class="aolmail_MsoNormal"><span style="font-family:"Calibri","sans-serif";color:#1F497D">Philip Sevetson</span><span style="font-family:"Calibri","sans-serif";color:#1F497D"></span></p>
<p class="aolmail_MsoNormal"><span style="font-family:"Calibri","sans-serif";color:#1F497D">Computer Systems Manager</span></p>
<p class="aolmail_MsoNormal"><span style="font-family:"Calibri","sans-serif";color:#1F497D">5 Manhattan West (33<sup>rd</sup> St at 10<sup>th</sup> Ave)</span></p>
<p class="aolmail_MsoNormal"><span style="font-family:"Calibri","sans-serif";color:#1F497D">New York, NY 10001-2632</span></p>
<p class="aolmail_MsoNormal"><span style="font-family:"Calibri","sans-serif";color:#1F497D">212-857-1688 w</span></p>
<p class="aolmail_MsoNormal"><span style="font-family:"Calibri","sans-serif";color:#1F497D">917-991-7052 c</span></p>
<p class="aolmail_MsoNormal"><span style="font-family:"Calibri","sans-serif";color:#1F497D">212-857-1659 f</span></p>
<p class="aolmail_MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><img width="107" height="28" id="aolmail_Picture_x0020_1" alt="cid:[login to unmask email]"></span><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"></span></p>
<p class="aolmail_MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span></p>
<p class="aolmail_MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> william giannelli [<a href="mailto:[login to unmask email]?">mailto:[login to unmask email]</a>]


<b>Sent:</b> Thursday, October 19, 2017 9:29 AM

<b>To:</b> DB2-<a href="mailto:[login to unmask email]">[login to unmask email]</a>

<b>Subject:</b> [DB2-L] - -420 encountered after DB2 v11 upgrade</span></p>
<p class="aolmail_MsoNormal"> </p>
<p>Hello,</p>
<p>We have a developer that claims that after our DB2 v11 upgrade, they are now getting a -420 sqlcode after compiling in v11. In v10 they did not get a -420. They say they have made no code changes.</p>
<p>Does anyone know of any reason migrating to DB2 v11 would then cause a -420?</p>
<p>thanks</p>
<p>Bill</p>
<p class="aolmail_MsoNormal"> </p>
<div class="aolmail_MsoNormal" align="center" style="text-align:center">
<div align="left">-----End Original Message-----
</div>
</div>
</div>
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate,
copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

<hr size="1" style="color:#ccc"><div id="aolmail_socfooter" style="font-size:80%"><span style="font-weight:bold">Attachment Links: </span>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/fo/do/?download=1&fid=8851">image001.png (3 k)</a>


<span style="font-weight:bold">Site Links: </span>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/fo/st/?post=183413&anc=p183413#p183413">View post online</a>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/fo/si/?topic=19">View mailing list online</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">Start new thread via email</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]?Subject=Unsubscribe">Unsubscribe from this mailing list</a>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/us/to/">Manage your subscription</a>


This email has been sent to: <a href="mailto:[login to unmask email]">[login to unmask email]</a>
<p>
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU

ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See

<a target="_blank" rel="noopener noreferrer" href="http://www.ESAIGroup.com/idug">http://www.ESAIGroup.com/idug </a>

</p>
<p>
Use of this email content is governed by the terms of service at:
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/cm/ld/fid=2">http://www.idug.org/p/cm/ld/fid=2</a></p>

</div><hr size="1" style="color:#ccc">

</div>
</div>

James Campbell

-420 encountered after DB2 v11 upgrade
(in response to william giannelli)
Did you change BIF_COMPATIBILITY?

James Campbell

On 19 Oct 2017 at 6:29, william giannelli wrote:

>
> Hello,
> We have a developer that claims that after our DB2 v11 upgrade, they are now getting a -420
> sqlcode after compiling in v11. In v10 they did not get a -420. They say they have made no code
> changes.
> Does anyone know of any reason migrating to DB2 v11 would then cause a -420?
> thanks
> Bill
>
>

Myron Miller

-420 encountered after DB2 v11 upgrade
(in response to william giannelli)
We got it as well during the V11 Conversion. We're using a table function to return data. And it's two fold issue. First off is table contains both char and numeric data. Then the access path drastically changed so that instead of the filtering being done on the table first so that it ensures the numeric data is only returned, the access path for the function is split over into the full access path of the sql.


We have in the SQL something like tablea.numfield = casst(FUNCT.cola as numeric) which retrieves data from a table that has both char and numeric in it. But the function filters it such that only numeric is retrieved. But when the function SQL is spread out over the entire SQL, it then returns the char data to the numfield test and of course that fails.


Making the function deterministic was one fix. Changing the access path to ensure all the numeric fields were only returned was another. IBM also provided a fix that helped with this. No clue of remember what that PTF was.


Thanks Myron W. Miller


________________________________
From: william giannelli <[login to unmask email]>
Sent: Thursday, October 19, 2017 9:29 AM
To: [login to unmask email]
Subject: [DB2-L] - -420 encountered after DB2 v11 upgrade


Hello,

We have a developer that claims that after our DB2 v11 upgrade, they are now getting a -420 sqlcode after compiling in v11. In v10 they did not get a -420. They say they have made no code changes.

Does anyone know of any reason migrating to DB2 v11 would then cause a -420?

thanks

Bill

-----End Original Message-----

william giannelli

RE: -420 encountered after DB2 v11 upgrade
(in response to Myron Miller)

any way you can point me to the PTF? I would very much appreciate it!

thanks

Bill

william giannelli

RE: -420 encountered after DB2 v11 upgrade
(in response to James Campbell)

No we did not change our BIF_COMPATIBILITY

thanks

Bill

Dave Nance

-420 encountered after DB2 v11 upgrade
(in response to James Campbell)
Did the ordering of predicates change due to new access path?
IBM Query fails with SQL0420 error - United States 

|
| |
IBM Query fails with SQL0420 error - United States
This document provides troubleshooting information for when your WHERE clause predicate order is reversed and yo... | |

|


David Nance


From: James Campbell <[login to unmask email]>
To: [login to unmask email]
Sent: Sunday, October 22, 2017 8:36 PM
Subject: [DB2-L] - RE: -420 encountered after DB2 v11 upgrade

Did you change BIF_COMPATIBILITY?

James Campbell

On 19 Oct 2017 at 6:29, william giannelli wrote:

>
> Hello,
> We have a developer that claims that after our DB2 v11 upgrade, they are now getting a -420
> sqlcode after compiling in v11. In v10 they did not get a -420. They say they have made no code
> changes.
> Does anyone know of any reason migrating to DB2 v11 would then cause a -420?
> thanks
> Bill
>
>
   
-----End Original Message-----   



Myron Miller

-420 encountered after DB2 v11 upgrade
(in response to Dave Nance)
In our case, yes.


Thanks Myron W. Miller


-----End Original Message-----

Patrick Bossman

RE: -420 encountered after DB2 v11 upgrade
(in response to Dave Nance)

Db2 can change the order predicates are processed.  A few of the scenarios we've seen this occur the access path change has been a view/table expression is now merged instead of materialized (where the materialization used to filter all data that would have driven -420, now with a merge, Db2 has more join sequences that do not filter the data that would drive -420 first).

In other cases, likely join sequence change can result in this occurring.  Although it is also possible index choice could drive it (ie. using index 1, we would filter out the -420 driving data, but if we use index 2, the predicate sees the data and trips the -420...)

Best regards,

Pat Bossman


In Reply to Dave Nance:

Did the ordering of predicates change due to new access path?
IBM Query fails with SQL0420 error - United States 

|
| |
IBM Query fails with SQL0420 error - United States
This document provides troubleshooting information for when your WHERE clause predicate order is reversed and yo... | |

|


David Nance


From: James Campbell <[login to unmask email]>
To: [login to unmask email]
Sent: Sunday, October 22, 2017 8:36 PM
Subject: [DB2-L] - RE: -420 encountered after DB2 v11 upgrade

Did you change BIF_COMPATIBILITY?

James Campbell

On 19 Oct 2017 at 6:29, william giannelli wrote:

>
> Hello,
> We have a developer that claims that after our DB2 v11 upgrade, they are now getting a -420
> sqlcode after compiling in v11. In v10 they did not get a -420. They say they have made no code
> changes.
> Does anyone know of any reason migrating to DB2 v11 would then cause a -420?
> thanks
> Bill
>
>
   
-----End Original Message-----