Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQLCODE = -804, REASON 03

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Huzefa

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Fri Mar 26, 2010 8:49 pm    Post subject: SQLCODE = -804, REASON 03
Reply with quote

Hi,
I get the below error when I am trying to prepare a dynamic Insert SQL.
The same PREPARE works for 1408 records in the file, but at the 1409th record it gives me the error.

Code:
SQLCODE = -804, ERROR:  AN ERROR WAS FOUND IN THE APPLICATION PROGRAM
INPUT PARAMETERS FOR THE SQL STATEMENT, REASON 03                   
SQLSTATE   = 07002 SQLSTATE RETURN CODE                             
SQLERRP    = DSNXERT2 SQL PROCEDURE DETECTING ERROR                 
SQLERRD    = -325  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION         
SQLERRD    = X'FFFFFEBB'  X'00000000'  X'00000000'  X'FFFFFFFF'     
X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION                 

Can somebody please look into and let me know what the issue is.

The query being prepared is simple as below
INSERT INTO CREATOR.TABLE (COLUMN1,COLUMN2) VALUES (?,?)
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Fri Mar 26, 2010 9:11 pm    Post subject:
Reply with quote

Have you looked up the error?
-804 AN ERROR WAS FOUND IN THE APPLICATION PROGRAM INPUT PARAMETERS FOR THE SQL STATEMENT, REASON reason
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Mar 26, 2010 10:12 pm    Post subject:
Reply with quote

Hello,

Possibly, there is an error in the code that is "walking on" something. . .
Back to top
View user's profile Send private message
Huzefa

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Sat Mar 27, 2010 7:21 pm    Post subject: Reply to: SQLCODE = -804, REASON 03
Reply with quote

I did refer the "DB2 Messages and Codes" but did not find any recommendation there.

Dick to answer you question.
My program is a row insert module. Reads one record from file and loads the record into DB2 and then reads next and loads and goes on.
It executed the same loop and has been running successfully in production.

I tried to load a huge file with around 200000 records with a huge commit quantity that is commit after every 5000 records contrary to what happens in production where it commits after every 250 inserts. That is when I get this error.

Hence I was looking for some pointers as is the program giving a -804 due to huge records in the file or is it that there is a huge difference between the commits. This is the only 2 things I have changed as what is currently running successfully in production.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Sat Mar 27, 2010 11:36 pm    Post subject:
Reply with quote

The 'huge records' can cause a problem if there is any table storage being utilized.
Try adding the SSRANGE option to the compile and see if that changes anything....
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Mar 28, 2010 12:01 am    Post subject:
Reply with quote

Hello,

First - it is important to detemine what is wrong with the code that causes this problem. . .

Second - how is a restart being handled if the process abends after 32,465 inserts are done? How long does the entire process for 200,000 rows take?
Back to top
View user's profile Send private message
Huzefa

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Sun Mar 28, 2010 7:29 pm    Post subject: Reply to: SQLCODE = -804, REASON 03
Reply with quote

Hi Dick..
I tried checking the program but was not successful in finding any issue n it..

Secondarily we handle restart using a table. Its like we insert 5000 records and log an entry in a checkpoint restart table then process next 5000 and increment the count in checpoint restart table. so the entry now would be 10000 = 5000 + 5000 and so on.

So if the program abends at 32,465 as per you the 2,465 records would be rolled back. When we restart the program it would start processing from 30,001 record.

Hope I am clear.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Sun Mar 28, 2010 7:41 pm    Post subject: Re: Reply to: SQLCODE = -804, REASON 03
Reply with quote

Huzefa wrote:
I tried checking the program but was not successful in finding any issue n it..
You may have to try better, the error explanation is pretty specific about the problem....
Explanation: The call parameter list or the SQLDA is invalid.
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 other way.
The SQLDA, which is created by the application program, has an invalid data type or data length.
The value of SQLDABC is not consistent with the value of SQLD.

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.
Back to top
View user's profile Send private message
Huzefa

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Sun Mar 28, 2010 8:26 pm    Post subject: Reply to: SQLCODE = -804, REASON 03
Reply with quote

I did try looking at all the possible errors in the program.

I am not sure how do I put it. The piece of program that exectues is a loop which executes for every record. The program just works when I have less records and also the gap between 2 commits as 250 records. I get -804 only when there are more records and I increase the commit quantity to 5000 records.

Also I get this -804 when I am trying to prepare the query before executing.

Hence I doubt there is an issue with the program. The more I think I feel its something to do with the number of records or the huge gap between 2 commits.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Mar 28, 2010 9:03 pm    Post subject:
Reply with quote

Hello,

Does the program use any subscripting, indexing, or reference modification? Does the code do any multi-row procfessing?

Have you added the SSRANGE option as suggested? If not, do so.

Quote:
I did try looking at all the possible errors in the program.
Most likely the entire program has not been thoroughly reviewed. Review has been concentrated on the code that does the insert and that is not necessarily where the problem exists.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Sun Mar 28, 2010 9:08 pm    Post subject:
Reply with quote

Quote:
Hence I doubt there is an issue with the program. The more I think I feel its something to do with the number of records or the huge gap between 2 commits.
I can't count how many times the programmer says there is no issue with the program when it turns out the program is at fault. Your attitude indicates that your problem almost certainly will not be solved, because the odds that it is not your program are extremely remote. In more than 30 years of professional programming, the times the problem turned out not to be the program can be counted on the fingers of one hand -- and I don't have to use all of them.

If the problem only occurs with larger numbers of records, look in your program at the table(s) defined -- quite possibly you are running past the table boundary, overlaying storage, and hence causing the problem. Or you could recompile with SSRANGE option, then run with CHECK(ON) to see if any table subscripts are getting out of bounds.

But if you are not willing to admit your program could be at fault, none of this will help -- because table overflows are your program's fault.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Sun Mar 28, 2010 9:23 pm    Post subject:
Reply with quote

Quote:
Also I get this -804 when I am trying to prepare the query before executing.
Well, that might make sense since the reason 03 specifies the error occurred during the Prepare of EXECUTE IMMEDIATE.....
Never the less, after looking for an APAR relating to a -804 and finding none, the key phrase from the -804 error explanation still seems to be "application programmer should not attempt to modify the output of the precompiler", whether deliberately or accidentally.....
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQLCODE-000000080N priya91 DB2 1 Mon Feb 01, 2016 4:35 pm
No new posts Need to understand SQLCODE = 12 rohanthengal DB2 3 Thu Nov 05, 2015 11:22 am
No new posts SQLCODE -922 in Batch-Cics program tr... kishpra CICS 2 Thu Sep 10, 2015 12:36 pm
No new posts IGGCSI00 R15 = 8, CSI reason = X'FFFF... steve-myers PL/I & Assembler 6 Thu Aug 06, 2015 10:29 pm
No new posts SQLCODE -450 for stored procedure spoorni DB2 3 Fri Jan 16, 2015 2:45 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us