| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Execution group abends while calling DB2 Stored Procedure |
« View previous topic :: View next topic » |
| Author |
Message
|
| JVR |
 Posted: Tue Jun 18, 2019 5:37 am Post subject: Execution group abends while calling DB2 Stored Procedure |
 |
|
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 |
|
 |
| Vitor |
| Posted: Tue Jun 18, 2019 5:55 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, 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 |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
