MQSeries.net :: View topic

kiran · Posted: Fri Feb 14, 2003 12:13 pm Post subject:

Hi ALL,

When i am trying to select 1000 records from a table into to a Environment Variable one by one, i am getting a record duplicated many times after a particular record.Here is my table sql script,

CREATE TABLE "DB2ADMIN"."ACQBATCH" (
"TOKEN" VARCHAR(1

,
"LOANNO" VARCHAR(20) NOT NULL ,
"ACHMSG" BLOB(500) NOT LOGGED NOT COMPACT ,
"MASTERMSG" BLOB(3000) NOT LOGGED NOT COMPACT )
IN "USERSPACE1" ;

Logic what i have written in the compute node is,

1. I will send a token to a SP which will return me a list of all loannos from the table.
2. With each loanno i am calling a another SP and it returns a ACHMSG and MASTERMSG.
3. I will do ACHMSH||MASTERMSG and i will send it out.
4. I am using PROPAGATE to generate each record as a message.
5.Untill the last loanno i will loop the whole process in a WHILE loop.

Here is my ESQL logic which i am using for the above logic,

---------------------------------------------------------------------------------
DECLARE MSGID CHARACTER;
DECLARE ACH_LEN INTEGER;
DECLARE MASTER_LEN INTEGER;
DECLARE LOAN_COUNT INTEGER;
SET LOAN_COUNT = 1;
SET MSGID = TRIM(CAST(InputRoot.MQMD.AccountingToken AS CHARACTER CCSID InputRoot.MQMD.CodedCharSetId ENCODING InputRoot.MQMD.Encoding));
SET Environment.ResultSet.DataLoans[] = PASSTHRU('{call DB2ADMIN.ACQ_GETLOANNOS(?)}',MSGID);
DELETE FROM Database.ACQBATCH AS T WHERE T.TOKEN = MSGID AND T.MASTERMSG IS NULL;
WHILE(LOAN_COUNT <= CARDINALITY(Environment.ResultSet.DataLoans[])) DO
SET OutputRoot = InputRoot;
SET Environment.ResultSet.DataMsgs[] = PASSTHRU('{call DB2ADMIN.ACQ_GETMSG(?,?)}',TRIM(Environment.ResultSet.DataLoans[LOAN_COUNT].LOANNO),MSGID);
IF(Environment.ResultSet.DataMsgs[1].ACHMSG <> '') THEN
SET ACH_LEN = LENGTH(Environment.ResultSet.DataMsgs[1].ACHMSG);
SET MASTER_LEN = LENGTH(Environment.ResultSet.DataMsgs[1].MASTERMSG);
SET OutputRoot."BLOB"."BLOB" = x'4c6fa7949340a58599a2899695407e407ff14bf07f408595839684899587407e407fe4e3c660f87f6f6e'
||SUBSTRING(Environment.ResultSet.DataMsgs[1].MASTERMSG FROM 1 FOR MASTER_LEN-21)
||SUBSTRING(Environment.ResultSet.DataMsgs[1].ACHMSG FROM 21 FOR ACH_LEN);
ELSE
SET OutputRoot."BLOB"."BLOB" = x'4c6fa7949340a58599a2899695407e407ff14bf07f408595839684899587407e407fe4e3c660f87f6f6e' ||Environment.ResultSet.DataMsgs[1].MASTERMSG;
END IF;
PROPAGATE;
SET Environment.ResultSet.DataMsgs[1].MASTERMSG = NULL;
SET Environment.ResultSet.DataMsgs[1].ACHMSG = NULL;
SET LOAN_COUNT = LOAN_COUNT+1;
END WHILE;
--DELETE FROM Database.ACQBATCH AS T WHERE T.TOKEN =MSGID;
SET OutputRoot.MQMD.AccountingToken = InputRoot.MQMD.AccountingToken;
SET OutputRoot.MQMD.Encoding = 273;
SET OutputRoot.MQMD.Format = 'MQSTR';
SET OutputRoot.MQMD.Priority = 0;
SET OutputRoot.MQMD.PutApplName = InputRoot.MQMD.PutApplName;
SET OutputRoot.MQMD.UserIdentifier= InputRoot.MQMD.UserIdentifier;
SET OutputRoot.MQMD.ApplIdentityData = 'CONTROLDELAY';
SET OutputRoot.BLOB.BLOB = X'F9F9F9F9F9F9F9F9F9F9F9F9F9F9F9F9';

-----------------------------------------------------------------------------------

Untill 639 records the data is coming out correctly, after the 639th record data is getting duplicated. If i look into the database everthing is fine no record is duplicated.I am the correct loan to SP, still i will be getting the 639th record.There are 979 records in table and the loanno is the PK , so when i make a call from compute node after 639 records the i keep getting the 639th record data into the Environment Variable.
I think its something to deal with the memory or the DB2 problem calling from MQSI for so many records. If any one can help me on this it will be great help to me.
thanks
kiran