ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportExecution group abends while calling DB2 Stored Procedure

Post new topicReply to topic
Execution group abends while calling DB2 Stored Procedure View previous topic :: View next topic
Author Message
JVR
PostPosted: Tue Jun 18, 2019 5:37 am Post subject: Execution group abends while calling DB2 Stored Procedure Reply with quote

Newbie

Joined: 05 Mar 2012
Posts: 8

I'm facing a problem on the broker running on Unix while calling a stored procedure (ODBC call) .
The happy path scenario works perfect. However, the execution group abends, when an IN parameter, with value greater than the specified value is passed to the SP.
In the stored procedure below, the ivsOrderSta is of type CHAR and length 4. When a value > 4 is passed the EG abends
Code:

Stored Procedure:

CALL VZPS1111('XXX6986', '92', '54', 'A', '22220500', returnCode, errorText,errorType)

CREATE PROCEDURE VZPS1111(
IN userId CHAR,
IN factory_cd CHAR,
IN routecd CHAR,
IN locnext CHAR,
IN ivsOrderSta CHAR,
OUT returnCode CHAR,
OUT errorText CHAR,
OUT errorType CHAR
)
LANGUAGE DATABASE
EXTERNAL NAME "YGT.VZPS1111";



Syslog:

Code:

Jun 18 13:04:38 accai111 IIB[10836]: IBM Integration Bus v100010 (UKEAI2M1_DEVBK.Test1) [Thread 10923] (Msg 1/1) BIP2154I: Integration server finished with Configuration message.
Jun 18 13:05:04 accai111 IIB[21637]: IBM Integration Bus v100010 [b](UKEAI2M1_DEVBK) [Thread 21689] (Msg 1/1) BIP2060W: The integration node has detected that the integration server Test1, process ID 10836, has shutdown.[/b]
Jun 18 13:05:04 accai111 IIB[31840]: IBM Integration Bus v100010 (UKEAI2M1_DEVBK.Test1) [Thread 31840] (Msg 1/1) BIP2297I: Integration Server service trace has been enabled due to user-initiated action.
Jun 18 13:05:04 accai111 IIB[31840]: IBM Integration Bus v100010 (UKEAI2M1_DEVBK.Test1) [Thread 31840] (Msg 1/1) BIP2208I: Integration server (64) started: process '31840'; thread '31840'; additional information: integrationNodeName 'UKEAI2M1_DEVBK' (operation mode 'advanced'); integrationServerUUID 'e76ba3c2-f3c2-409f-b161-f5a8568c564d'; integrationServerLabel 'Test1'; queueManagerName 'UKEAI2M1_DEV'; trusted 'false'; userId 'mqbrkr'; migrationNeeded 'false'; integrationNodeUUID 'a23468de-7abe-11e8-9755-a0324c540000'; filePath '/lfs/opt/mqsi/10.0.0.10/server'; workPath '/var/mqsi'; ICU Converter Path ''; ordinality '10'.
Jun 18 13:05:44 accai111 IIB[31840]: IBM Integration Bus v100010 (UKEAI2M1_DEVBK.Test1) [Thread 331] (Msg 1/1) BIP2152I: Configuration message received from integration node.
Jun 18 13:05:44 accai111 IIB[31840]: IBM Integration Bus v100010 (UKEAI2M1_DEVBK.Test1) [Thread 331] (Msg 1/1) BIP2153I: About to 'Start' an integration server.
Jun 18 13:05:44 accai111 IIB[31840]: IBM Integration Bus v100010 (UKEAI2M1_DEVBK.Test1) [Thread 331] (Msg 1/1) BIP2154I: Integration server finished with Configuration message.



Any other exception, invalid datatype, invalid parameters passed to SP - fails and the application terminates successfully.
Only the SP calls with invalid data length results in abend of the EG.

We have stored procedures with 50 or more IN parameters, and a validation check on each field would not be feasible (or part of IIB requirement).
I have enabled the User trace and the ODBC trace and see no failure in both.

The only error in ODBC trace, which I guess is not result of SP call failure.

Code:

[ODBC][10836][2406176512][2019-06-18 13:04:42.093][SQLGetDiagRec.c][776]
      Exit:[SQL_SUCCESS]                   
         SQLState = HY004                   
         Native = 0x7f578f6a9c9c -> -99999                   
         Message Text = [[IBM][CLI Driver] CLI0123E  SQL data type out of range. SQLSTATE=HY004]
[ODBC][10836][2406176512][2019-06-18 13:04:42.093][SQLGetDiagRec.c][739]
      Entry:               
         Statement = 0x7f57a5b23f00               
         Rec Number = 2               
         SQLState = 0x7f578f6a9ca0               
         Native = 0x7f578f6a9c9c               
         Message Text = 0x7f578f6a9f60               
         Buffer Length = 512               
         Text Len Ptr = 0x7f578f6a9c9a
[ODBC][10836][2406176512][2019-06-18 13:04:42.093][SQLGetDiagRec.c][776]
      Exit:[SQL_NO_DATA]
[ODBC][10836][2406176512][2019-06-18 13:04:42.093][SQLFetch.c][158]
      Entry:           
         Statement = 0x7f57a5b23f00
[ODBC][10836][2406176512][2019-06-18 13:04:42.093][SQLFetch.c][175]Error: HY010
      DIAG [HY004] [IBM][CLI Driver] CLI0123E  SQL data type out of range. SQLSTATE=HY004

[ODBC][10836][2406176512][2019-06-18 13:04:42.094][SQLCloseCursor.c][135]
      Entry:           
         Statement = 0x7f57a5b23f00
[ODBC][10836][2406176512][2019-06-18 13:04:42.094][SQLCloseCursor.c][153]Error: 24000
      DIAG [HY004] [IBM][CLI Driver] CLI0123E  SQL data type out of range. SQLSTATE=HY004



Broker Version: 10.0.0.10
Os: Unix
DB2 Version: 12.1

Any inputs is truly appreciated.

Regards,
VJ
Back to top
View user's profile Send private message
Vitor
PostPosted: Tue Jun 18, 2019 5:55 am Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25732
Location: Ohio, USA

I think you're in PMR territory.

It looks a lot like that particular SQL error is being passed back by the ODBC driver to the EG not to your application.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportExecution group abends while calling DB2 Stored Procedure
Jump to:



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.