|
View previous topic :: View next topic
|
| Author |
Message |
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
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 |
|
 |
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Possibly, there is an error in the code that is "walking on" something. . . |
|
| Back to top |
|
|
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
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 |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
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 |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
| Back to top |
|
|
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| 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 |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
|
|
| 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 |
|
|
|
|
