Intermittent query errors with JDBC drivers to DB2

gunkldunk · gunkldunk

This is driving me stark-raving mad - can anyone help?

We are using ColdFusion MX 6.1 and are getting intermittent query errors,
actually they are happening more frequently each day it seems...

Everything will be working fine and then all of a sudden certain pages won't
work, and we'll see error messages like this:

"Error Executing Database Query.[Macromedia][DB2 JDBC
Driver]Transliteration failed, reason: invalid UTF8 data"

or

"For input string: ""?$52"" The specific sequence of files included or
processed is:"
(the string in quotes is always some different random gibberish)

or

"Timestamp must be in the format..." etc.

The thing is that the code has not changed, and the data is not actually
corrupted (we know because we can read and do same queries from other DB2
clients like QuestCentral)

But if I go in to the ColdFusion administrator panel and "re-submit" the data
source, then the errors go away!!!
(sometimes it requires 2 or 3 times to click the "submit" button)

Can anyone help explain what is going wrong? Lately it has started to happen
multiple times per day - so that is multiple times per day we have to go in and
manually "submit" the data source within the ColdFusion administration panel.

If it wasn't for this one bug, everything else has been working great!
Please, anyone, help shed some light on this if you can!

BKBK · BKBK

No, not a pleasant experience. I would look at the encoding, content-type
or character-set in the communication between your pages and the database.

gunkldunk · gunkldunk

Originally posted by: BKBK
No, not a pleasant experience. I would look at the encoding, content-type
or character-set in the communication between your pages and the database.

Not sure what you mean by this... could you elaborate?

gunkldunk · gunkldunk

Originally posted by: BKBK
Could you give code from one of the problem pages?

Sure, this is one that keeps failing intermittently as I said above:

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">


<cfif not (isUserInRole("AD") OR isUserInRole("MG"))>
<cflocation URL="index.cfm" addtoken="no">
</cfif>

<cfquery name="get_job" datasource="frsdb2">
SELECT *
FROM
#schema#FF_JOB_STATUS
WHERE
#schema#FF_JOB_STATUS.Job_number = #Job_number#
</cfquery>

<cfquery name="getStore_number" datasource="frsdb2">
SELECT
Store_number
FROM
#schema#FF_JOB_STATUS
WHERE
#schema#FF_JOB_STATUS.Job_number = #Job_number#
</cfquery>

<cfquery name="getEmergency" datasource="frsdb2">
SELECT
Emergency
FROM
#schema#FF_JOB_STATUS
WHERE
#schema#FF_JOB_STATUS.Job_number = #Job_number#
</cfquery>

<CFQUERY NAME="getTechnician" DATASOURCE="frsdb2">
SELECT
ASSOC_ID,
RTRIM(FIRST_NAME) concat ' ' concat RTRIM(LAST_NAME) as TECH_FULLNAME
FROM
#schema#FF_USER
WHERE
#schema#FF_USER.POSITION_NO != 1
</CFQUERY>

<cfquery name="getCategory" datasource="frsdb2">
SELECT
Job_category_name
FROM
#schema#FF_JOB_CATEGORY
GROUP BY
Job_category_name
ORDER BY
Job_category_name
</cfquery>

<CFQUERY NAME="user_search_opened" DATASOURCE="frsdb2">
SELECT
#schema#FF_JOB_STATUS.Job_number AS Job_number,
#schema#FF_JOB_STATUS.Store_number AS Store_number,
#schema#FF_JOB_STATUS.Job_category_name AS Job_category_name,
#schema#FF_JOB_STATUS.Date_opened AS Date_opened,
#schema#FF_JOB_STATUS.Asset AS Asset,
#schema#FF_JOB_STATUS.Description_of_problem AS Description_of_problem,
#schema#FF_JOB_STATUS.Location_of_problem AS Location_of_problem,
#schema#FF_JOB_STATUS.ASSOC_ID_CREATE,
#schema#FF_JOB_STATUS.Emergency AS Emergency_hidden,
#schema#FF_JOB_STATUS.Comments AS Comments,
#schema#FF_STORE_ADDRESS.STORE AS Store
FROM
#schema#FF_JOB_STATUS,
#schema#FF_STORE_ADDRESS,
#schema#FF_JOB_CATEGORY
WHERE
#schema#FF_JOB_STATUS.Store_number = #schema#FF_STORE_ADDRESS.STORE_NUMBER AND
#schema#FF_JOB_STATUS.Job_category_name =
#schema#FF_JOB_CATEGORY.Job_category_name AND
#schema#FF_JOB_STATUS.job_number = #job_number#
ORDER BY
#schema#FF_JOB_STATUS.Date_opened DESC
</CFQUERY>

<STYLE TYPE="text/css">
A:link { color:Blue; }
A:visited { color:Purple; }
A:hover { color:Crimson; }
</STYLE>

<HTML>

<HEAD>
<TITLE><cfoutput>#Application.title#</cfoutput> - Reassign</TITLE>

<SCRIPT LANGUAGE="JavaScript">

</script>

</HEAD>

<BODY background="img/background2MAY.gif" bgcolor="White" >

<p>

<table>
<TR>
<TD><b><FONT COLOR="##000099" FACE="Verdana,Tahoma,Arial,Helvetica">Reassign
Repair Requests</B></FONT></TD>
<TD></TD>
</TR>
</TABLE>

<hr align="left" width="750">

<table width="750" border="0" cellspacing="2" cellpadding="2">
<tr><td colspan="8" bgcolor="##003399"><font
face="Verdana,Geneva,Arial,Helvetica,sans-serif" size="-1"
color="White"><b>  Select the new Technician, Category or Status and
then click the "Update job" button.</b></font><br></td></tr>
<tr>
<th align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">Store</font></th>
<th align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">Date Opened</font></th>
<th align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">Reassign?</font></th>
<th align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">Job #</font></th>
<th align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">Category</font></th>
<th align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">Asset</font></th>
<th align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">Assignee</font></th>
<th align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">Category</font></th>
</tr>
<form name="reassign_job_form" action="reassign_comment.cfm" method="POST"
enctype="multipart/form-data" onsubmit="return validateForm(this);">
<input type="hidden" name="Store_number" value="<cfoutput
query="user_search_opened">#Store_number#</cfoutput>">
<input type="hidden" name="Job_category_name" value="<cfoutput
query="user_search_opened">#Job_category_name#</cfoutput>">
<input type="hidden" name="Date_opened" value="<cfoutput
query="user_search_opened">#Date_opened#</cfoutput>">
<input type="hidden" name="Asset" value="<cfoutput
query="user_search_opened">#Asset#</cfoutput>">
<input type="hidden" name="Description_of_problem" value="<cfoutput
query="user_search_opened">#Description_of_problem #</cfoutput>">
<input type="hidden" name="Location_of_problem" value="<cfoutput
query="user_search_opened">#Location_of_problem#</cfoutput>">
<input type="hidden" name="ASSOC_ID_CREATE" value="<cfoutput
query="user_search_opened">#ASSOC_ID_CREATE#</cfoutput>">
<input type="hidden" name="Emergency_hidden" value="<cfoutput
query="user_search_opened">#Emergency_hidden#</cfoutput>">
<input type="hidden" name="Date_closed" value="<cfoutput>#now()#</cfoutput>">
<input type="hidden" name="Comments" value="<cfoutput
query="user_search_opened">#Comments#</cfoutput>">

<cfif IsDefined("job_number")>
<cfoutput query="user_search_opened" maxrows="1">
<tr bgcolor="###iif(currentrow MOD 2,DE('efefef'),DE('ffffff'))#">
<td align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">#Store#</font></td>
<td align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">#DateFormat(Date_opened, "mm/dd/yy")#</font></td>
<td align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2"><input type="radio" name="Job_number" value="#Job_number#"
checked></font></td>
<td align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2"><a
href="job_search.cfm?Job_number=#Job_number#">#Job _number#</a></font></td>
<td align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">#Job_category_name#</font></td>
<td align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">#Asset#</font></td>
</cfoutput>
<td align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">
<select name="New_Technician">
<option>choose assignee</option>
<cfoutput query="getTechnician">
<cfif #ASSOC_ID# EQ #get_job.ASSOC_ID_TECH#>
<option value="#ASSOC_ID#" selected>#TECH_FULLNAME#</option>
<cfelse>
<option value="#ASSOC_ID#">#TECH_FULLNAME#</option>
</cfif>
</cfoutput>
</select></font>
</td>

<td align="center"><font face="Verdana,Geneva,Arial,Helvetica,sans-serif"
size="-2">
<select name="New_Category">
<option>choose category</option>
<cfoutput query="getCategory">
<cfif #Job_category_name# EQ #get_job.JOB_CATEGORY_NAME#>
<option value="#Job_category_name#" selected>#Job_category_name#</option>
<cfelse>
<option value="#Job_category_name#">#Job_category_name#</option>
</cfif>
</cfoutput>
</select></font>
</td>
</tr>
<tr>
<cfoutput query="user_search_opened">
<td colspan="8" align="center" valign="top"><font
face="Verdana,Geneva,Arial,Helvetica,sans-serif" size="-2" color="Red">Is this
job an emergency?</font> <font
face="Verdana,Geneva,Arial,Helvetica,sans-serif" size="-2">Yes:
<cfif #Emergency_hidden# GTE 1>
<input type="radio" name="Emergency" value=1 checked> No:<input
type="radio" name="Emergency" value=0></td>
<cfelse>
<input type="radio" name="Emergency" value=1> No:<input type="radio"
name="Emergency" value=0 checked></td>
</cfif>
</cfoutput>
</tr>
</cfif>
<tr><td colspan="8" align="center"><input type="submit" name="Submit"
value="Update job"> <INPUT TYPE="button" VALUE="Back"
onClick="history.go(-1)"></td></tr>
</form>
</table>

<hr align="left" width="750">

<cfinclude template="footerlegend.cfm">

</BODY>
</HTML>

BKBK · BKBK

Well, Gunkldunk, this is a surprise. I only see select-statements. I had
expected the errors would be caused by SQL that writes to the database,
e.g. insert or update. Also, nothing stands out of your code, as a likely
cause of the errors.

By encoding, content-type or character-set, I meant there could be
a discrepancy between the encoding of your database and the encoding of
the data that your code is sending to the database. My assumption comes
from clues like invalid UTF8 data, the gibberish input string:
""?$52"" and, from the error message itself, transliteration.

If that indeed is the problem, the best and lasting solution is to use
the Unicode encoding (e.g. UTF-8). Do that for your database, if possible,
and certainly for all your Coldfusion pages. For example, place

<meta http-equiv="content-type" content="text/html; charset=utf-8">

within the <head> tag of each of the problem pages, and see if it helps.
(Oh, and, talking about the head-tag, just something incidental. The
<style> tag, too, should be within the <head>)

To me, the effect of re-submitting the datasource in the Coldfusion
Administrator makes the matter more complex. I don't know why it
happens. But I would expect the CFAdmin to react like that only when
data was being inserted or updated. For example, I know from personal
experience that errors may occur when your Coldfusion page has latin
encoding, and writes to an NText column of a database that supports
Unicode.

There is another possible scenario. If, for some reason, Coldfusion is
interrupted while it is sending a large amount of character data to
the database, that interruption itself might be sent along, as a
distortion of the data.

jorgepino · jorgepino

CF 6.1 has an update that deal with characther set Problems, Make sure you have the latest update installed

gunkldunk · gunkldunk

We may finally be on to a solution... the databases we are having problems with
have "code set"/"code page" of IBM-1252, but the AIX operating system for the
server is 819/ISO8859-1... a mismatch, you could say.

The one database that is not giving us the error has "code set"/"code page" of
819/ISO8859-1 (i.e. it matches with the operating system)

The DB2 manager looked up some info on-line that says those that those code
sets are supposed to be compatbile - he has a PMR open with IBM now to see what
they have to say... he says it's not as simple as just changing an attribute,
that they would probably have to export all data, drop databases, recreate,
then reload data... I think they are hoping IBM will have an easier solution.
I'll post more reply as the situation develops...

gunkldunk · gunkldunk

Here is the latest advice from IBM as far as trying to troubleshoot our problem:

"you will need to enable jcc trace in Coldfusion application or you will need
to translate your coldfusion into jcc java application and take jcc trace from
there."

Can anybody explain to me what this might mean? I don't have a clue,
unfortunately.

gunkldunk · gunkldunk

More from IBM support:
(can anyone help me understand what I need to do?)

I know that Coldfusion application is using Db2 Universal JDBC type 4 (jcc
driver). For this type of driver, you need to modify your java application to
get a trace (jcc trace). In your case, your application is Coldfusion, I don't
know how to enable trace in this type of application. You might need to ask
Coldfusion Support Team.
Other choice is to port the SQL statement that is causing the problem in
Coldfusion application into JDBC application and enable the trace in JDBC
application. Hopefully, this way can reproduce the problem and fix the issue.
Please first ask Coldfusion Support Team whether there is an option to embed
Java codes in Coldfusion application or an option to turn on trace in
Coldfusion Application.
Here is the link for enabling trace in JDBC application using Db2 Universal
JDBC type 4:

[url]http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.ud[/url]
b.doc/ad/cjvjctrc.htm
Hope it helps.

sdupre · sdupre

The 3.5 drivers are available on the /support/coldfusion page. There were a
lot of fixes in DB2.

I would try them and report any issues. There is a build-in trace utility
with that set of drivers.

Stephen Dupre
Macromedia QA

Intermittent query errors with JDBC drivers to DB2

Thread Tools

Display

Intermittent query errors with JDBC drivers to DB2

Similar Questions and Discussions

Microsoft JDBC Drivers

Using Oracle JDBC drivers

Oracle 10g JDBC Thin Drivers and Time

Mysql Driver . What's the best between macromediaDrivers or JDBC Drivers ?

Any JDBC drivers for informix anywhere (ifxjdbc.jar?)

Re: Intermittent query errors with JDBC drivers to DB2

Re: Intermittent query errors with JDBC drivers to DB2

Re: Intermittent query errors with JDBC drivers to DB2

Re: Intermittent query errors with JDBC drivers to DB2

Re: Intermittent query errors with JDBC drivers to DB2

Re: Intermittent query errors with JDBC drivers to DB2

Re: Intermittent query errors with JDBC drivers to DB2

Re: Intermittent query errors with JDBC drivers to DB2

Re: Intermittent query errors with JDBC drivers to DB2

Posting Permissions