IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

I need help with SQLCA


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
John Poulakos

Active User


Joined: 13 Jun 2012
Posts: 180
Location: United States

PostPosted: Thu Dec 27, 2018 9:27 pm
Reply with quote

I am working with an old proprietary software product and the damn thing intercepts DB2 and puts out its own useless user abend. From the message it puts out, I can tell it's a DB -310 error, but I can't tell which host variable is causing the error. I'm hoping its in the SQLCA, but I am having trouble interpreting it. This is all the information I have:

********************************* TOP OF DATA **********************************
*** SYSTEM FATAL ERROR:
DATE: 12/26/18 TIME: 22:44:15

***** LAST DBIO CALL:
DBFUNC: INS DBNUM: 0061 DBSTATUS: *DB27 STATUS-2: 0036
BUFFER: |201900011021021 22991021220 OTHER ST GRANTS & REIMBU|
|R 0000000000000000000000000000000000000000000000000000000000|
|0000000000000000000130|

***** LAST DBMS CALL IS DB2:
FUNC : INSERT CURSOR: STATUS: -310
TABLE : RRPT
BUFFER: 201900011021021 22991021220 OTHER ST GRANTS & REIMB
000
0000
SQLCA : *E2D8D3C3C140404000000088FFFFFECA0002F1F3* *SQLCA h 13*
: *4040404040404040404040404040404040404040* * *
: *4040404040404040404040404040404040404040* * *
: *4040404040404040404040404040404040404040* * *
: *4040404040404040C4E2D5E7D9C9C8C2FFFFFF12* * DSNXRIHB *
: *4040404040404040404040F2F2F0F2F3A0286F1C* * 22023 ? *


******************************** BOTTOM OF DATA ********************************
Back to top
View user's profile Send private message
John Poulakos

Active User


Joined: 13 Jun 2012
Posts: 180
Location: United States

PostPosted: Thu Dec 27, 2018 10:34 pm
Reply with quote

I believe the host variable number is the 13 at the end of the row. It is the beginning of errmsg and nothing else would hold this content.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2572
Location: NYC,USA

PostPosted: Thu Dec 27, 2018 10:57 pm
Reply with quote

Quote:
I believe the host variable number is the 13 at the end of the row. It is the beginning of errmsg and nothing else would hold this content.

If that's so then that must be a Decimal field having a nondecimal data and if not then identify all the Decimal fields present and look for the values before INSERT.
Back to top
View user's profile Send private message
John Poulakos

Active User


Joined: 13 Jun 2012
Posts: 180
Location: United States

PostPosted: Fri Dec 28, 2018 12:07 am
Reply with quote

Without knowing which host variable is giving the error, I don't even know if it's a field I think is numeric. All the fields that are numeric according to the file definition that feeds this program are zeros (as shown in the buffer). But, I don't have the actual source code and I can only see the SQL that is shown in the DBRM, which doesn't show host variables. Knowing which host variable is causing the error would at least offer some clue, which is more than I have now.

This is a 20+ year old legacy system that is going away... just trying to get it to work one last time.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2572
Location: NYC,USA

PostPosted: Fri Dec 28, 2018 12:36 am
Reply with quote

Quote:
Without knowing which host variable is giving the error, I don't even know if it's a field I think is numeric. All the fields that are numeric according to the file definition that feeds this program are zeros (as shown in the buffer).
Do you not know what you are passing to this called module? did you get to know what is the bad record from the input data set? As far as I can tell its a bad data for this record , how about comparing this record with good ones?
Back to top
View user's profile Send private message
John Poulakos

Active User


Joined: 13 Jun 2012
Posts: 180
Location: United States

PostPosted: Fri Dec 28, 2018 1:29 am
Reply with quote

The program fails on the first record. If I remove that, it fails on the next record, etc. etc.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2572
Location: NYC,USA

PostPosted: Fri Dec 28, 2018 1:34 am
Reply with quote

Quote:
The program fails on the first record. If I remove that, it fails on the next record, etc. etc.
That means the Data Set has all bad records. If this program executes in production without this issue then best way is to compare this Data Set to Production one and ask for corrections accordingly to the source system. If its a production issue then compare this Data Set to last Successful run in Production and see the differences and fix it.
Back to top
View user's profile Send private message
John Poulakos

Active User


Joined: 13 Jun 2012
Posts: 180
Location: United States

PostPosted: Fri Dec 28, 2018 11:38 pm
Reply with quote

If it were that simple, they wouldn't have given it to me. I only get the really ugly problems. This is an annual program that was overlooked when this legacy system was migrated to a new mainframe in January. It has never been run on this mainframe. This software was originally written for some database other than DB2. Rather than rewrite programs for DB2 they created an interface that translates database operations into DB2 SQL and performs data mapping functions. It's very complex, ugly and a good part of it is a "black box".

I only posted this topic to see if somebody could say for sure that the host variable number is in the first 2 bytes of the SQLCA message area when a DB2 -310 is issued. That would help me find things in a rather large dump, because fortunately the "black box" died on a S0C4 after completing the DB2 rollback.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2572
Location: NYC,USA

PostPosted: Sat Dec 29, 2018 1:52 am
Reply with quote

Quote:
I only posted this topic to see if somebody could say for sure that the host variable number is in the first 2 bytes of the SQLCA message area when a DB2 -310 is issued. That would help me find things in a rather large dump, because fortunately the "black box" died on a S0C4 after completing the DB2 rollback.
No. It is not as per me. You can see SQLCA copybook and when sqlcode -310 then in the Error it self it gives the position # than in SQLCA.
However, I still wonder why you can't display the host variables (used for this black box module call )before the call in the calling program to determine what is going wrong? Without this I don't think of any other way to troubleshoot the issue, since error displays that you posed are so customized and needs extra efforts to translate it and one could surely misinterpret them.
Quote:
DBFUNC: INS DBNUM: 0061 DBSTATUS: *DB27 STATUS-2: 0036
Worth paying attention to this 61 if not then 36 position# if that's the variable having non decimal value. Did you check that already?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Confusion regarding length of SQLCA v... DB2 4
No new posts Access SQLCA fields inside a stored p... DB2 6
No new posts DB2 error "SQLCA" WAS NOT D... ABENDS & Debugging 6
No new posts Error code -911 SQLCA:SQLCA :40001 00... ABENDS & Debugging 4
No new posts SQLCA location DB2 4
Search our Forums:

Back to Top