MQSeries.net :: View topic

isolomatin · Posted: Wed Jun 13, 2007 8:52 am Post subject:

Hi all,

one of the requirements for software we are building is to return unprocessed messages to a database. This works by placing a message with a uuid onto a queue, reading it using a flow, getting the message from a storage db by using uuid then inserting it into another db.

The storage db is DB2 8.1.0.14 on AIX. Its codepage is 1208. The db that the message has to be inserted into is DB2/400. Its codepage is 1025 (data is kept as 1025 but the label on the db says 37, business requirement, can't change this).

Now the problem. There is a field called MSGBDY on DB2/400. It's a varchar with maximum size 16386 bytes. The error I am getting when inserting into the database is:

Text = [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001

Here is the code:

DECLARE msgID CHAR;
DECLARE b BLOB;
DECLARE hdrLen INT;
DECLARE msgHdr CHAR;
DECLARE bdyLen INT;
DECLARE msgBdy char;

SET b = X'000000000000' || SUBSTRING(InputRoot.BLOB.BLOB FROM 17 FOR 2);
SET hdrLen = CAST(b AS INTEGER);
SET msgHdr = CAST(SUBSTRING(InputRoot.BLOB.BLOB FROM 19 FOR hdrLen) AS CHAR CCSID 1025);

SET b = X'000000000000' || SUBSTRING(InputRoot.BLOB.BLOB FROM 4115 FOR 2);
SET bdyLen = CAST(b AS INTEGER);
SET msgBdy = CAST(SUBSTRING(InputRoot.BLOB.BLOB FROM 4117 FOR bdyLen) AS CHAR CCSID 1025);

SET msgID = cast(SUBSTRING(InputRoot.BLOB.BLOB FROM 1 FOR 16) as char ccsid 1025);

declare len1 int;
set len1 = length(msgBdy);
declare len2 int;
set len2 = length(msgID);
declare len3 int;
set len3 = length(msgHdr);

declare msgTms char;
set msgTms = cast(substring(InputRoot.BLOB.BLOB from 20501 for 26) as char ccsid 1025);

INSERT INTO Database.{DB_SCHEMA}.{DB_TABLE} (MSGID, MSGHDR, MSGBDY, MSGSTM)
VALUES (msgID, msgHdr, msgBdy, msgTms);

Here is the exception list:

ExceptionList
RecoverableException
File = F:\\build\\S600_P\\src\\DataFlowEngine\\ImbDataFlowNode.cpp
Line = 616
Function = ImbDataFlowNode::createExceptionList
Type = ComIbmMQInputNode
Name = EMRS/Handlers/ReturnToMSC#FCMComposite_1_4
Label = EMRS.Handlers.ReturnToMSC.GetMessageForMSC
Catalog = BIPv600
Severity = 3
Number = 2230
Text = Node throwing exception
RecoverableException
File = F:\\build\\S600_P\\src\\DataFlowEngine\\ImbComputeNode.cpp
Line = 464
Function = ImbComputeNode::evaluate
Type = ComIbmComputeNode
Name = EMRS/Handlers/ReturnToMSC#FCMComposite_1_5
Label = EMRS.Handlers.ReturnToMSC.InsertIntoDB
Catalog = BIPv600
Severity = 3
Number = 2230
Text = Caught exception and rethrowing
RecoverableException
File = F:\\build\\S600_P\\src\\DataFlowEngine\\ImbRdl\\ImbRdlStatementGroup.cpp
Line = 573
Function = SqlStatementGroup::execute
Type = ComIbmComputeNode
Name = EMRS/Handlers/ReturnToMSC#FCMComposite_1_5
Label = EMRS.Handlers.ReturnToMSC.InsertIntoDB
Catalog = BIPv600
Severity = 3
Number = 2488
Text = Error detected, rethrowing
Insert
Type = 5
Text = EMRS.Handlers.ReturnToMSC_InsertIntoDB.main
Insert
Type = 5
Text = 38.3
Insert
Type = 5
Text = INSERT INTO Database.{RUMSCIN_SCHEMA}.{RUMSCIN_TABLE} ( MSGID, MSGHDR, MSGBDY, MSGSTM ) VALUES (msgID, msgHdr, msgBdy, msgTms )
DatabaseException
File = F:\\build\\S600_P\\src\\DataFlowEngine\\ImbOdbc.cpp
Line = 227
Function = ImbOdbcHandle::checkRcInner
Type =
Name =
Label =
Catalog = BIPv600
Severity = 3
Number = 2321
Text = Root SQL exception
Insert
Type = 2
Text = -1
DatabaseException
File = F:\\build\\S600_P\\src\\DataFlowEngine\\ImbOdbc.cpp
Line = 355
Function = ImbOdbcHandle::checkRcInner
Type =
Name =
Label =
Catalog = BIPv600
Severity = 3
Number = 2322
Text = Child SQL exception
Insert
Type = 5
Text = 22001
Insert
Type = 2
Text = -99999
Insert
Type = 5
Text = [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001

Debug info:

msgID = MI10002980
b = 0000000000003f1a
hdrLen = 141
msgHdr = :IO:I\n:FROM:NAME1234 \n:TO:PIN=123456\n:PRI:N\n:CRN:UNID(UNID:UNID:UNID:UNID:UNID:UNID:UNID:UN)\n:FORM:Memo\nTYPE: 940\nNKS : 12345678912345678900
bdyLen = 16154
msgBdy = <16154 bytes, looks like msgHdr>
len1 = 16154
len2 = 16
len3 = 141
msgTms = 2007-06-10-04.27.48.448000

You can see that the length is correct but why wouldn't it insert it into the column? The column size permits the insertion.
I tried a lot of things but that field just keeps outgrowing the column size.

Can anyone help?

Thanks!