Possible causes for getting an SQLCODE of -804

lagneshp · New User Joined: 15 Aug 2009 Posts: 29 Location: Chennai

What are the possible causes for getting an SQLCODE of -804.

We are executing a Embeded SQL Query repeatedly in one of our program. It was working fine in production for several years, But suddenly abended with SQLCODE -804 .

44225 records got updated and it abends in 4439th record.

When restarted the job it started from 4426th record as commit point is 25 recs and it abends again in 4439th record.

Skipped the problem record and tried, again facing same issue.

The below is the QL Quey used:

EXEC SQL
UPDATE VALTY_ADR
SET RANK_NUM = :WS-RANK-NUM
,UPDT_DT = CURRENT DATE
WHERE ACCT_NUM = :WS-SEARCH-ACCT-NUM
AND REGN_PROCS_CDE = :WS-SEARCH-REGN-PROC-CDE
AND SRCE_SYS_TYPE = :WS-SEARCH-REF-TYPE
AND ORG_CDE = :WS-SEARCH-ORG-CDE
AND SRCE_SYS_REL_CDE = :WS-SEARCH-REL-CODE
AND SRCE_SYS_SUB_NUM = :WS-SEARCH-REF-SUBNUM
END-EXEC.

Checked the I/P file as well data looks good.

Please provide solution to fix this abend.

enrico-sorichetti · Posted: Fri Oct 21, 2011 10:08 pm

lagneshp · New User Joined: 15 Aug 2009 Posts: 29 Location: Chennai

Already Gone through it... it get solution... that is the reason posted in Forum.

Akatsukami · Posted: Fri Oct 21, 2011 10:13 pm

daveporcelan · Posted: Fri Oct 21, 2011 10:16 pm

The manual refers to a reason code.

This reason code points to the possible issue.

You did not provide this information.

lagneshp · New User Joined: 15 Aug 2009 Posts: 29 Location: Chennai

I Got the below Info.. But issue didn't fix..

Please let me know if you have any suggestions.

plz read the explination it may help you
qw -804 ,gives the detailed information of the error code as below :

804 AN ERROR WAS FOUND IN THE APPLICATION PROGRAM INPUT PARAMETERS FOR THE SQL STATEMENT, REASON reason

Explanation: The call parameter list or the SQLDA is invalid.

o The call parameter list, which is created by the precompiler, might be
invalid if the application programmer has modified the output of the
precompiler, used a variable name beginning with 'SQL' in the
application program, or overwritten the call parameter list in some otherway.
o The SQLDA, which is created by the application program, has an invalid
data type or data length.

o The value of SQLDABC is not consistent with the value of SQLD.
01 Open issued for non-cursor.
02 Close issued for non-cursor.
03 Prepare of EXECUTE IMMEDIATE.
04 Statement is not recognized.
05 No statement string present.
06 Bad SQLDA format in parameter list.
07 SQLDA length is invalid.
08 Unrecognized input data type.
09 Invalid length for input variable.
10 Invalid data length for output variable.
11 The value of SQLDABC is not consistent with the value of SQLD.
12 Invalid input data pointer 13 Invalid output data pointer.
14 SQLN has too many items for SQLDABC.
15 Input RDI pointer is invalid.
16 Unrecognized output data type.
17
The value of the 7th byte of SQLDAID is not consistent with the
data types contained in the SQLDA. The SQLDA contains a LOB type host variable, but the 7th byte of SQLDAID is not set to '2' or
greater to indicate that the extended SQLVARs have been
allocated.

System Action: The statement cannot be executed.

System Programmer Response:
Examine the application program for any of the errors noted under the explanation above. In general, the application programmer should not attempt to modify the output of the precompiler.

daveporcelan · Posted: Fri Oct 21, 2011 10:21 pm

So which reason code did you get?

lagneshp · New User Joined: 15 Aug 2009 Posts: 29 Location: Chennai

The Reason Code .. got is 06.

Akatsukami · Posted: Fri Oct 21, 2011 10:54 pm

Well, then, the answer is obvious: one of you software engineers corrupted the output of the precompiler, wittingly or otherwise. Since getting information out of you is like unto pulling teeth, it was probably done as an attempted shortcut to something else, and you and your buddies are now sweating bullets, trying to fix the problem without admitting that its your fault, or even that there is one.

dick scherrer · Posted: Fri Oct 21, 2011 10:57 pm

Hello,

Now that you know the 06 is caused by a bad parameter list, have you looked at the generated code to see if anything is wrong?

If the generated code is correct (and it probably is because it works thousands of times before failing), you probably have your own code that is "walking on" your working storage. Does this program have any arrays?

Just because some code ran for years does not mean that the code did not have errors - they just haven't been discovered.

lagneshp · New User Joined: 15 Aug 2009 Posts: 29 Location: Chennai

Hi .......

Some home we managed to run the Job by modified th I/P data in a such a way that not to execute that Particular query and completed the Job.

But the same Job Ran fine from next day onwards which has the data which will execute that particular query which causes the issue.

This is one of the unknown Mystery i have seen in DB2 which fix by itself..

Bill Woodger · Posted: Thu Oct 27, 2011 8:08 pm

You really have to fix this. There was an error which caused a problem that day which caused an Abend. Maybe every other day it is causing a problem you are not aware of, but just not going far enough to cause an abend.

Did you investigate the suggestions made earlier? If you are "walking on" your code as Dick points out as a possibility, then maybe with that input it just happened to "walk" far enough to cause the Abend, whereas normally it is just a short stroll doing something more subtle.

My advice is to not leave a problem unresolved. You can very easily regret it later, when you discover that you could have saved six months of backout when you eventually find the problem for some other reason.

Get the same set-up in an environment where you can "play" with it. Recreate the error. Then track it down.

If your boss doesn't like this idea, CYA with a memo/e-mail so it is not your nuts on the line sometime in the future. Be diplomatic, don't send it until he's seen it, but CYA.

dick scherrer · Posted: Fri Oct 28, 2011 5:40 am

Hello,