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
 

 

Seeing the full text of SQLERRM
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Appu

New User


Joined: 26 Apr 2010
Posts: 74
Location: India

PostPosted: Mon Mar 10, 2014 11:12 am    Post subject: Seeing the full text of SQLERRM
Reply with quote

Hello Team,

Please note that I am trying to run an XSLTRANSFORM procedure for DB2 from a PL1 program. But I am getting sqlcode -443 ; but the message in SQLERRM is not displayed fully. This is because the SQLCA in PL1 contains only 70 characters. How can I get the full error message seen.
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Mon Mar 10, 2014 3:52 pm    Post subject:
Reply with quote

DSNTIAR.
Back to top
View user's profile Send private message
Appu

New User


Joined: 26 Apr 2010
Posts: 74
Location: India

PostPosted: Mon Mar 10, 2014 5:21 pm    Post subject:
Reply with quote

Hello,

Thanks for the information. I have tried DSNTIAR as well; but I am still not able to get the truncated message from SQLERRM. The usage of DSNTIAR gave me the same info as SQLCA.

Eg:-
DSNT408I SQLCODE = -443, ERROR: ROUTINE SYSFUN.XSLTRANSFORM (SPECIFIC NAME
SYSFUN.XSLTRANSFORM) HAS RETURNED AN ERROR SQLSTATE WITH DIAGNOSTIC
TEXT SQLCODE: -16280 Content is not
DSNT418I SQLSTATE = 38000 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNX9CAC SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'

What I really wanted was the truncated message contained in TEXT SQLCODE.

Please share your thoughts.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Mar 11, 2014 3:15 am    Post subject:
Reply with quote

Quote:
but the message in SQLERRM is not displayed fully. This is because the SQLCA in PL1 contains only 70 characters. How can I get the full error message seen.


This will be 70 only everywhere, are you expecting it to be more?

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_descriptionofsqlcafields.htm

Also even if you are using DSNTIAR program , it will give you what is there in SQLCA the only added benefit is you can give your own error message to be displyed along with the SQLCODE as sample below,


https://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.apsg%2Fsrc%2Ftpc%2Fdb2z_definemessageoutputarea.htm
Back to top
View user's profile Send private message
Appu

New User


Joined: 26 Apr 2010
Posts: 74
Location: India

PostPosted: Tue Mar 11, 2014 9:00 am    Post subject:
Reply with quote

Hello,

This means that there is no other way i can see the rest of the truncated message ? Any other tool or option available ?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Mar 11, 2014 9:35 am    Post subject:
Reply with quote

What more message are you expecting in TEXT SQLCODE ?
Back to top
View user's profile Send private message
Appu

New User


Joined: 26 Apr 2010
Posts: 74
Location: India

PostPosted: Tue Mar 11, 2014 9:55 am    Post subject:
Reply with quote

Hello,

As you could see above, the message gets truncated :

TEXT SQLCODE: -16280 Content is not

I want to see the rest of the message icon_smile.gif . I understand this is not possible with the current declaration of SQLCA .. so checking if there is any other way ..
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Mar 11, 2014 9:57 am    Post subject:
Reply with quote

Did you try displaying SQLERRM explicitly via program to assure there is something more also present ? And why do you think its not possible to have it within 70 bytes ?
Back to top
View user's profile Send private message
Appu

New User


Joined: 26 Apr 2010
Posts: 74
Location: India

PostPosted: Tue Mar 11, 2014 10:01 am    Post subject:
Reply with quote

You mean changing the SQLCA structure ?
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Tue Mar 11, 2014 1:57 pm    Post subject:
Reply with quote

Read this :

https://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_descriptionofsqlcafields.htm
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Mar 11, 2014 2:05 pm    Post subject:
Reply with quote

It seems TS does not pay attention to read the links shared..
Back to top
View user's profile Send private message
Appu

New User


Joined: 26 Apr 2010
Posts: 74
Location: India

PostPosted: Tue Mar 11, 2014 2:18 pm    Post subject:
Reply with quote

First of all I am really doubted whether you all understood the issue.

I have already read the SQLCA strcuture and I know the message can hold a maximum of 70 chars. But from the message I got, i could see that it is getting truncated after 70 chars .. but contains some valid messages and I want to see if any option available by which i could see the truncated message .

I am not saying this from air, but I have tried the program and put a display of my SQLCA structure.
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Tue Mar 11, 2014 2:25 pm    Post subject:
Reply with quote

Did you read about the msg length field, and the message eventually containg x'FF'? Could that be reason for your problem?
Back to top
View user's profile Send private message
Appu

New User


Joined: 26 Apr 2010
Posts: 74
Location: India

PostPosted: Tue Mar 11, 2014 2:50 pm    Post subject:
Reply with quote

Hi PeterHolland,

Yes the tokens are separated by X'FF' and I have already pasted the whole 70 char message . So there is no way we can know what message has been truncated ?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Mar 12, 2014 5:40 am    Post subject:
Reply with quote

There is a confusion for me here ,
1) How are you getting "TEXT SQLCODE: -16280 Content is not" message?
2) Are you saying that the above message is truncated then I see it is still <70 to count on?
3) Did you try checking WLM logs?
4) What happens when you used DSNTIAR and moved SQLERRM to a variable which is declared more than 70?
5) You may also wants to check,

Code:
SQL0443
Message Text: Trigger program or external routine detected an error.
Cause Text: Either a trigger program, external procedure, or external function detected and returned an error to SQL. If the error occurred in a trigger program, the trigger was on table &4 in schema &5. If the error occurred in an external procedure or function, the external name is &4 in schema &5. The associated text is &6. If the error occurred in a trigger program, the associated text is the type of trigger program. If the error occurred in an external function, the associated text is the text of the error message returned from the external function.
Recovery Text: Refer to the joblog for more information regarding the detected error. Correct the error and try the request again.
SQLCODE or SQLCODEs: -443
SQLSTATE or SQLSTATEs: 38xxx, 38501
Back to top
View user's profile Send private message
Appu

New User


Joined: 26 Apr 2010
Posts: 74
Location: India

PostPosted: Wed Mar 12, 2014 9:03 am    Post subject:
Reply with quote

1) By simply putting a PUT SKIP of SQLCA structure.
2)
Code:

SQLCA.SQLERRM='SYSFUN.XSLTRANSFORM.SYSFUN.XSLTRANSFORM.SQLCODE: -16280 Content i
EDDCC4EDDCDDD77EEECED4EEDEDCDECDDDFEEECED4EEDEDCDECDDDFEDDCDCC746FFFFF4C99A89A48
28331B2835994ED282645B723391526694F282645B723391526694F2833645A00162800365355309

As you could see , the message doesnt starts from 'TEXT SQLCODE' but the tokens are separated by X'FF' ( as correctly suggested by PeterHolland).
3) how to do that in a pl/1 pgm
4) the same as you have replied above :
Quote:

Also even if you are using DSNTIAR program , it will give you what is there in SQLCA the only added benefit is you can give your own error message to be displyed along with the SQLCODE as sample below,
. Also nothing much happens even if we move a 70 char field to a big length field
5) could you please tell me wht is specified as &4, &5 ?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Mar 12, 2014 2:35 pm    Post subject:
Reply with quote

I think this is exactly what you looking for,

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0022027.html
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Wed Mar 12, 2014 3:57 pm    Post subject:
Reply with quote

@Rohit, and where did the TS state he is using LUW DB2?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Mar 13, 2014 11:29 am    Post subject:
Reply with quote

PeterHolland, Right. I would later saw it was for LUW so that may no more be useful for him.
Back to top
View user's profile Send private message
Appu

New User


Joined: 26 Apr 2010
Posts: 74
Location: India

PostPosted: Thu Mar 13, 2014 1:34 pm    Post subject:
Reply with quote

Finally with the help of DBA i could see the full length message.
There was an STC running for the Db2 from which I could get the full message .

Thanks all for ur suggestions.
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
This topic is locked: you cannot edit posts or make replies. Sort card to replace text using cross... Susanta SYNCSORT 32 Tue Sep 13, 2016 4:17 pm
No new posts Only text from varchar Seppe DB2 8 Thu Apr 21, 2016 10:42 pm
No new posts How to send email with Rich Text usin... santhosh5983 JCL & VSAM 4 Fri Apr 08, 2016 2:30 am
This topic is locked: you cannot edit posts or make replies. Need a JCL for FTP the text file to D... Hareesh Verma JCL & VSAM 7 Fri Mar 25, 2016 12:11 pm


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