|
View previous topic :: View next topic
|
| Author |
Message |
ramangouda patil
New User
Joined: 09 Apr 2008
Posts: 39
Location: India
|
|
|
|
Hi All,
I am trying to run a COBOL DB2 program which has cursors defined in them. In the program the cursor is opened but thereafter the program abends.
SQL RETURN CODE: 919
Any chance anyone else in this forum came across a similar situation ?
Appreciate any help! |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
At the top of the page is a link to "IBM Manuals". On that page select one of the DB2 Messages and Codes. Search for -919 and read about the return code. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| I believe, it might have been -919 (notice the "-", bold did not make it that obvious, phew!), as Dick has rightly indicated. |
|
| Back to top |
|
|
ramangouda patil
New User
Joined: 09 Apr 2008
Posts: 39
Location: India
|
|
|
|
| The return code I am receiving is + 919 and not -919. I tried searching for +919 but could not find much on the internet. Any help please ? |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| ramangouda patil wrote: |
| The return code I am receiving is + 919 and not -919. I tried searching for +919 but could not find much on the internet. Any help please ? |
How are you sure about it?
I hope you know what do positive-SQLCODEs mean, actually? Do yo uhave SQLSTATE handy with you? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
show us the code and data definitions that you are using to display the sqlcode.
and then we will explain to you why you are wrong, it is -919.
there is no +919 sqlcode on z/OS DB2.
thus you will find nothing on the internet for a +919 |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
| What is the SQLSTATE at the time of abend? |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| ramangouda patil wrote: |
| The return code I am receiving is + 919 and not -919. I tried searching for +919 but could not find much on the internet. Any help please ? |
Given that there is no +919 SQLCODE listed in the DB2 10 for z/OS Troubleshooting Guide, I think that I may be forgiven my doubts as to the accuracy and veracity of your story. Would you like to try again? |
|
| Back to top |
|
|
ramangouda patil
New User
Joined: 09 Apr 2008
Posts: 39
Location: India
|
|
|
|
Hi All,
Posting the code and error message. I asked for help with the system administrator, but could not get any lead on the 919 code. Thus trying other options.
| Code: |
[* SQL RETURN CODE: 919 * ]
[ WS-SQL-CODE PIC ZZZZ9-.]
[EXEC SQL
DECLARE E5100CR CURSOR FOR
SELECT
TLENTYID ,
CKVAL ,
CKVAL ,
NVCDISTLINENBR ,
NVCDISTSEQ ,
MTNBR ,
VENTCD ,
UBEVENTCD ,
CTVYCD ,
AXAUTHCD ,
CKVAL ,
EAMT ,
SERRSRVREF ,
CCTEFFDT
FROM COBGNX.TCE5100
FOR FETCH ONLY
END-EXEC. ]
In Procedure division:
[ EXEC SQL
OPEN E5100CR
END-EXEC. ]
[IF SQLCODE = 0
NEXT SENTENCE
ELSE
MOVE SQLCODE TO WS-SQL-CODE
DISPLAY '* SQL RETURN CODE: ' WS-SQL-CODE ' *'
GO TO EOJ
END-IF. ] |
|
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
What is the Cobol PIC for WS-SQL-CODE?
What is the valuse of SQLSTATE (I will not ask again). |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
batch or cics?
you mentioned that your db2 program had cursors.
how many other cursors where open at the time you opened this cursor?
| Code: |
| WS-SQL-CODE PIC ZZZZ9-. |
| Code: |
| * SQL RETURN CODE: 919 * |
| Code: |
| DISPLAY '* SQL RETURN CODE: ' WS-SQL-CODE ' *' |
by the way you are BSing us.
if your pic clause was truely as you said, and your code was truely as you said,
this
| Code: |
| * SQL RETURN CODE: 919 * |
would be this:
| Code: |
| * SQL RETURN CODE: 919 * |
|
|
| Back to top |
|
|
ramangouda patil
New User
Joined: 09 Apr 2008
Posts: 39
Location: India
|
|
|
|
Hello,
| Code: |
The PIC clause is
[WS-SQL-CODE PIC ZZZZ9-. ]
The SQLCODE and SQLSTATE is
[* SQL RETURN CODE: 919 *]
[* SQL STATE CODE: 0 * ]
using the DISPLAY statements in the program :
[DISPLAY '* SQL RETURN CODE: ' WS-SQL-CODE ' *']
[DISPLAY '* SQL STATE CODE: ' WS-SQL-STATE' *']
|
This is as is copied from the program and the spool output. I am stating the issue as is and this is not made up in anyway. Kindly let me know if I can provide more details to be clearer with the issue.
Thanks! |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
rarely does an OPEN Cursor generate a -919.
a +919 does not exist in z/OS DB2 (as of vsn10, which is the latest)
i would venture to say, that SQLSTATE = 0 never occurs on an SQLCODE <> 0
ok.
1. what kind of machine are you running on?
2. what version of db2 are you running?
3. Batch or CICS?
4. I asked about other cursors in your program.
5. i don't like excerpted code, what is the flow of your program? |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| Just a guess: is your COBOL program meant to fetch data from some DWH (Data Ware House) tables, possibly, involving Informatica? |
|
| Back to top |
|
|
cmsmoon
New User
Joined: 17 Jun 2010
Posts: 75
Location: Chennai
|
|
|
|
-919 A ROLLBACK OPERATION IS REQUIRED
Explanation:
The unit of work was placed in a state where a rollback operation is
required. This can happen for the following reasons:
o An SQL statement updated a distributed database server, but the
database server can be used only for read-only operations. Either
updates are currently restricted to servers that support distributed
two-phase commit and this application server does not support
distributed two-phase commit, or updates are restricted to a single
server that does not support distributed two-phase commit and this
application server is not that server. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| Actually, so far, we're still advocating on if it's +919 or -919. Though, OP, ramangouda, has not returned yet. |
|
| Back to top |
|
|
ramangouda patil
New User
Joined: 09 Apr 2008
Posts: 39
Location: India
|
|
|
|
Sorry All,
Was not able to reply earlier. The SQL code is definately 919 (dont see any negative sign in the SQLCODE output).
The DB2 version we have is 8.1
The machine is z/OS version 1.9
We are running the program through batch.
There are other cursors defined, but this issue is happening on the first cursor that we are trying to open.
The program flow is basically displaying a few statements to spool(which the program is doing) and then trying to open the cursor, read through the cursor and move the read values into working variables. What is happening is after trying to open the cursor, we are catching the SQLCode and then going into error handling it to display appropriate messages and then end of program.
Kindly let me know if I need to provide more details.
Again thanks for the assistance. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
The SQL code is definately 919 |
Most of us are doubtful that DB2 is returning a "919" as it is not documented anywhere in the DB2 material. . .
Suggest you copy this program and remove everything except this problem bit of code and run it. You would only keep the definitions for the cursor and whatever is needed to fetch rows.
Make sure the sqlcode is set to zero before issuing any sql statements. |
|
| Back to top |
|
|
ramangouda patil
New User
Joined: 09 Apr 2008
Posts: 39
Location: India
|
|
|
|
Hi All,
Below is snippet form the spool output for couple of cursors. the second and third gave an error of -991 but the first shows 919.
| Code: |
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* SQL RETURN CODE: 919 *
* ERROR OPENING CRS3E00 CSR *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* SQL RETURN CODE: 991- *
* ERROR CLOSING CRS3E00 CSR *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* SQL RETURN CODE: 991- *
* ERROR CLOSING CRS028 CSR *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* |
|
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
what does the jcl for the job step look like?
What do the other cursors do?
since this is batch,
and you have pretty long cursors,
do you ever commit? |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| ramangouda patil wrote: |
Hi All,
Below is snippet form the spool output for couple of cursors. the second and third gave an error of -991 but the first shows 919.
[*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* SQL RETURN CODE: 919 *
* ERROR OPENING CRS3E00 CSR *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* SQL RETURN CODE: 991- *
* ERROR CLOSING CRS3E00 CSR *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* SQL RETURN CODE: 991- *
* ERROR CLOSING CRS028 CSR *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* ][/code] |
How do you relate "DECLARE E5100CR CURSOR" to "CRS3E00" in the above message extract?
Since 919 does not exist in DB2, you didn't get a 919 from an SQL statement. This is supported by the SQLSTATE being zero, rather than some value which would aid problem determination.
Did you try Dick Scherrer's suggestion?
If you want anything further here, you're going to have to cough-up relevant unedited code. |
|
| Back to top |
|
|
ramangouda patil
New User
Joined: 09 Apr 2008
Posts: 39
Location: India
|
|
|
|
Hello All,
We discarded the cursor approach and went with downloading of table data. The reason was this was a one time run requirement and a download was able to cater the need. Also on the cursor name question, I just changed the name of the cursor while posting it in this forum.The program had the same cursor name in the definition and while opening the cursor.
Thanks for all the help and apologies if I created a whole lot of confusion. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Good to hear it is resolved - thank you for letting us know 
d |
|
| Back to top |
|
|
|
|
