| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| NO MORE HANDLES ERROR |
« View previous topic :: View next topic » |
| Author |
Message
|
| sachinramesh |
 Posted: Wed May 21, 2008 10:19 pm Post subject: NO MORE HANDLES ERROR |
 |
|
Disciple
Joined: 20 Feb 2007
Posts: 170
|
Hi
My Scenario is ,i am getting messages from the java application and MB is trying to insert/update the data into the database based on conditions ..
The data contains around 700 records in which each record should be updated 20 times (for each location)..while doing so i am getting an error which says
<Error><ExceptionList><RecoverableException><File>/build/S500_P/src/DataFlowEngine/ImbDataFlowNode.cpp</File><Line>558</Line>
<Function>ImbDataFlowNode::createExceptionList</Function><Type>ComIbmComputeNode</Type><Name>CUP_VIJETHA_PACKS_MF#FCMComposit
e_1_3</Name><Label>CUP_VIJETHA_PACKS_MF.INSERT INTO CUDB</Label><Text>Node throwing exception</Text><Catalog>BIPv500</Catalog
><Severity>3</Severity><Number>2230</Number><RecoverableException><File>/build/S500_P/src/DataFlowEngine/ImbRdl/ImbRdlStateme
ntGroup.cpp</File><Line>158</Line><Function>SqlStatementGroup::execute</Function><Type>ComIbmComputeNode</Type><Name>CUP_VIJE
THA_PACKS_MF#FCMComposite_1_3</Name><Label>CUP_VIJETHA_PACKS_MF.INSERT INTO CUDB</Label><Text>Error detected, rethrowing</Tex
t><Catalog>BIPv500</Catalog><Severity>3</Severity><Number>2488</Number><Insert><Type>5</Type><Text>.CUP_VIJETHA_PACKS_MF_Comp
ute.Main</Text></Insert><Insert><Type>5</Type><Text>156.22</Text></Insert><Insert><Type>5</Type><Text>PASSTHRU(UPDATE_QRY)</T
ext></Insert><RecoverableException><File>/build/S500_P/src/DataFlowEngine/ImbRdl/ImbRdlExternalDb.cpp</File><Line>280</Line><
Function>SqlExternalDbStmt::executeStmt</Function><Type>ComIbmComputeNode</Type><Name>CUP_VIJETHA_PACKS_MF#FCMComposite_1_3</
Name><Label>CUP_VIJETHA_PACKS_MF.INSERT INTO CUDB</Label><Text>The following error occurred during execution of an SQL statem
ent</Text><Catalog>BIPv500</Catalog><Severity>3</Severity><Number>2519</Number><Insert><Type>5</Type><Text>.CUP_VIJETHA_PACKS
_MF_Compute.Main</Text></Insert><Insert><Type>5</Type><Text>156.22</Text></Insert><Insert><Type>5</Type><Text>CUDB</Text></In
sert><Insert><Type>5</Type><Text>UPDATE TBL_CU_VIJETA_INFO_NEW SET ACTIVE='N',TS=CURRENT_TIMESTAMP WHERE ACTIVE=&ap
os;Y' AND (('05/21/2008' BETWEEN '05/21/2008' AND '06/20/2008') OR ('06/20/2008'
BETWEEN '05/21/2008' AND '06/20/2008')) AND ACTIVITY_ID ='ACTVJ84809499'</Text></Insert><Inser
t><Type>5</Type><Text></Text></Insert><DatabaseException><File>/build/S500_P/src/DataFlowEngine/ImbOdbc.cpp</File><Line>213</
Line><Function>ImbOdbcHandle::checkRcInner</Function><Type></Type><Name></Name><Label></Label><Text>Root SQL exception</Text>
<Catalog>BIPv500</Catalog><Severity>3</Severity><Number>2321</Number><Insert><Type>2</Type><Text>-1</Text></Insert><DatabaseE
xception><File>/build/S500_P/src/DataFlowEngine/ImbOdbc.cpp</File><Line>366</Line><Function>ImbOdbcHandle::checkRcInner</Func
tion><Type></Type><Name></Name><Label></Label><Text>Child SQL exception</Text><Catalog>BIPv500</Catalog><Severity>3</Severity
><Number>2322</Number><Insert><Type>5</Type><Text>HY014</Text></Insert><Insert><Type>2</Type><Text>-99999</Text></Insert><Ins
ert><Type>5</Type><Text>[IBM][CLI Driver] CLI0129E No more handles. SQLSTATE=HY014</Text></Insert></DatabaseException></Data
baseException></RecoverableException></RecoverableException></RecoverableException></ExceptionList><ERROR1>2322</ERROR1><Path
>InputExceptionList.*[1].*[LAST].*[LAST].*[LAST].*[LAST].*[LAST].*[LAST].*[LAST]</Path><MessageAtFailure><Properties><Message
Set>L74HFIS002001</MessageSet><MessageType>VIJETHA_ROWS</MessageType><MessageFormat>TDS1</MessageFormat><Encoding>273</Encodi
ng><CodedCharSetId>819</CodedCharSetId><Transactional>TRUE</Transactional><Persistence>TRUE</Persistence><CreationTime>2008-0
5-21 05:31:59.330</CreationTime><ExpirationTime>-1</ExpirationTime><Priority>0</Priority><ReplyIdentifier>0000000000000000000
00000000000000000000000000000</ReplyIdentifier><ReplyProtocol>MQ</ReplyProtocol><Topic></Topic></Properties><MQMD><SourceQueu
e>CUP.VIJETA_PACKS.INPUTQ</SourceQueue><Transactional>TRUE</Transactional><Encoding>273</Encoding><CodedCharSetId>819</CodedC
harSetId><Format> </Format><Version>2</Version><Report>0</Report><MsgType>8</MsgType><Expiry>-1</Expiry><Feedback>0</F
eedback><Priority>0</Priority><Persistence>1</Persistence><MsgId>414d5120554659514d20202020202020482be16b20002201</MsgId><Cor
relId>000000000000000000000000000000000000000000000000</CorrelId><BackoutCount>0</BackoutCount><ReplyToQ>
</ReplyToQ><ReplyToQMgr>UFYQM </ReplyToQMgr><UserIdenti
fier> </UserIdentifier><AccountingToken>0000000000000000000000000000000000000000000000000000000000000000</Accounti
ngToken><ApplIdentityData>
I am getting this error in the code while updating the database table.
There is no wrong with the code bcos it has worked fine before.
Can anybody tell me when do we get such sort of errors ,and is it a performance issue from MB side to update so many records. |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Wed May 21, 2008 10:58 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi sachinramesh,
| Quote: |
| [IBM][CLI Driver] CLI0129E No more handles. SQLSTATE=HY014 |
Your System DSN information, and your ESQL code snippet please.
Regards. |
|
| Back to top |
|
|
| sachinramesh |
| Posted: Wed May 21, 2008 11:33 pm Post subject: |
|
|
Disciple
Joined: 20 Feb 2007
Posts: 170
|
| elvis_gn wrote: |
Hi sachinramesh,
| Quote: |
| [IBM][CLI Driver] CLI0129E No more handles. SQLSTATE=HY014 |
Your System DSN information, and your ESQL code snippet please.
Regards. |
My System is accesing a database "CUDBINST" which has a dsn "CUDBINST".
Is there anything else u mean by system dsn information.
The message is of TDS format separeted by a semicolon.
My record usually contains fields like a;b;c;d; area;branch;channel; fields
in which the values of area/branch/channel will be like this
area=1,2,3,4,5,6,7;
branch =a,b,c,d,e,f;
channel =a1,a2,a3,a4,a5,a6;
My code goes like this
START OF STORING COMMA SEPARATED 'BRANCHS' WHICH ARE COMING FROM MESSAGE INTO ENVIRONMENT VAIABLE
START OF STORING COMMA SEPARATED 'AREA' WHICH ARE COMING FROM MESSAGE INTO ENVIRONMENT VAIABLE
START OF STORING COMMA SEPARATED 'CHANNEL' WHICH ARE COMING FROM MESSAGE INTO ENVIRONMENT VAIABLE
While
Initialize the variables
while
START OF STORING COMMA SEPARATED 'BRANCHS' WHICH ARE COMING FROM MESSAGE INTO ENVIRONMENT VAIABLE
end while
while
START OF STORING COMMA SEPARATED 'AREA' WHICH ARE COMING FROM MESSAGE INTO ENVIRONMENT VAIABLE
end while
while
START OF STORING COMMA SEPARATED 'CHANNEL' WHICH ARE COMING FROM MESSAGE INTO ENVIRONMENT VAIABLE
end while
while
Validate
Update into database
end while
Insert into database
While
set sequencer
insert into different database
end while
end while |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Thu May 22, 2008 12:25 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi sachinramesh,
By the DSN, I was expecting you to tell me the ODBC drivers you are using.
By the ESQL code snippet, I was expecting you to paste the code and not the business logic.
Regards. |
|
| Back to top |
|
|
| sachinramesh |
| Posted: Thu May 22, 2008 12:43 am Post subject: |
|
|
Disciple
Joined: 20 Feb 2007
Posts: 170
|
| elvis_gn wrote: |
Hi sachinramesh,
By the DSN, I was expecting you to tell me the ODBC drivers you are using.
By the ESQL code snippet, I was expecting you to paste the code and not the business logic.
Regards. |
I am not able to paste the code as it contains many semicolons and commas.which says service unavailable when posting
The ODBC driver is IBM DB2 ODBC DRIVER. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Thu May 22, 2008 12:48 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi sachinramesh,
Try to work with a small number of inserts/updates, I think the amount of inserts/updates are the issue...if the smaller msgs work, then you will need to simply alter the flow logic to have intermediate commits.
Regards. |
|
| Back to top |
|
|
| sachinramesh |
| Posted: Thu May 22, 2008 12:49 am Post subject: |
|
|
Disciple
Joined: 20 Feb 2007
Posts: 170
|
| sachinramesh wrote: |
| elvis_gn wrote: |
Hi sachinramesh,
By the DSN, I was expecting you to tell me the ODBC drivers you are using.
By the ESQL code snippet, I was expecting you to paste the code and not the business logic.
Regards. |
I am not able to paste the code as it contains many semicolons and commas.which says service unavailable when posting
The ODBC driver is IBM DB2 ODBC DRIVER. |
Here is the code ...I had replaced all semicolons with + symbol.
CREATE COMPUTE MODULE CUP_VIJETHA_PACKS_MF_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyEntireMessage()+
-------------------------------- Start of Declaration Section ------------------------------------------------
DECLARE CH_AREA CHAR+
DECLARE CH_BRANCH CHAR+
DECLARE CH_CHANNEL CHAR+
DECLARE CH_PACK_DESC CHAR+
DECLARE CH_IVCODE1 CHAR+
DECLARE CH_UNIT CHAR+
DECLARE CH_UOM CHAR+
DECLARE CH_POWER_POINTS CHAR+
DECLARE CH_OUTLET_TIER CHAR+
DECLARE CH_START_DATE CHAR+
DECLARE CH_END_DATE CHAR+
DECLARE CH_FILE_NAME CHAR+
DECLARE IN_COUNTER,IN_VIJETHA_COUNT INTEGER+
DECLARE CH_VIJETHA_AREA,CH_VIJETHA_BRANCH,CH_VIJETHA_CHANNEL CHAR+
DECLARE IN_TEMP_POSTION_I,IN_I,IN_L,IN_K,AREA_CARDINALITY,ACTIVITY_LIST_CARDINALITY INTEGER+
DECLARE IN_TEMP_POSTION_K,IN_TEMP_POSTION_J,IN_J,IN_M INTEGER+
DECLARE BRANCH_CARDINALITY,CHANNEL_CARDINALITY INTEGER+
DECLARE ACTIVITY_STAT,BRANCH_STAT CHAR+
DECLARE VALID_AREALIST,UPDATE_QRY CHAR+
DECLARE IN_TEMP_ID,CH_TEMP_ID, CH_SCOPE_ID CHAR+
DECLARE CH_TEMP_START_DATE,CH_TEMP_END_DATE CHAR+
-------------------------------- End of Declaration Section ------------------------------------------------
-------------------------------- Start of Initialization section --------------------------------------------
SET IN_COUNTER = 1+
SET IN_VIJETHA_COUNT = CARDINALITY ( "InputBody"."VIJETHA_RECORDS"[] ) +
-------------------------------- End of Initialization section --------------------------------------------
SET IN_COUNTER = 1+
WHILE ( IN_COUNTER <= IN_VIJETHA_COUNT ) DO
SET CH_AREA = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."AREA")) +
SET CH_IVCODE1 = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."IV_CODE")) +
SET CH_OUTLET_TIER = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."OUTLET_TIER"))+
SET CH_START_DATE = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."START_DATE")) +
SET CH_END_DATE = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."END_DATE")) +
SET CH_FILE_NAME = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."FILE_NAME")) +
SET CH_BRANCH = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."BRANCH")) +
SET CH_CHANNEL = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."CHANNEL")) +
SET CH_VIJETHA_AREA = CH_AREA+
SET CH_VIJETHA_BRANCH = CH_BRANCH+
SET CH_VIJETHA_CHANNEL = CH_CHANNEL+
SET VALID_AREALIST =''+
--------------------START OF STORING COMMA SEPARATED 'AREAS' WHICH ARE COMING FROM MESSAGE INTO ENVIRONMENT VAIABLE-----------------
SET IN_I = 1+
WHILE ( CH_VIJETHA_AREA <> '' ) DO
-- find out the position of the separator in the 'AREA' field
SET IN_TEMP_POSTION_I = ( POSITION ( ',' IN CH_VIJETHA_AREA ) )+
-- check if start of 'AREA' field
IF (IN_TEMP_POSTION_I <> '0') THEN
SET Environment.Variables.AREA_LIST[IN_I] = TRIM (SUBSTRING ( CH_VIJETHA_AREA FROM 1 FOR ( IN_TEMP_POSTION_I - 1 ) ) ) +
SET CH_VIJETHA_AREA = SUBSTRING ( CH_VIJETHA_AREA FROM ( IN_TEMP_POSTION_I + 1 ) )+
SET IN_I = IN_I + 1+
ELSE
SET Environment.Variables.AREA_LIST[IN_I] = TRIM ( CH_VIJETHA_AREA ) +
SET CH_VIJETHA_AREA = ''+
END IF+
END WHILE+
SET AREA_CARDINALITY = CARDINALITY(Environment.Variables.AREA_LIST[])+
--------------------START OF STORING COMMA SEPARATED 'BRANCHS' WHICH ARE COMING FROM MESSAGE INTO ENVIRONMENT VAIABLE-----------------
SET IN_J = 1+
WHILE ( CH_VIJETHA_BRANCH <> '' ) DO
-- find out the position of the separator in the 'BRANCH' field
SET IN_TEMP_POSTION_J = ( POSITION ( ',' IN CH_VIJETHA_BRANCH ) )+
-- check if start of 'BRANCH' field
IF (IN_TEMP_POSTION_J <> '0') THEN
SET Environment.Variables.BRANCH_LIST[IN_J] = TRIM (SUBSTRING ( CH_VIJETHA_BRANCH FROM 1 FOR ( IN_TEMP_POSTION_J - 1 ) ) ) +
SET CH_VIJETHA_BRANCH = SUBSTRING ( CH_VIJETHA_BRANCH FROM ( IN_TEMP_POSTION_J + 1 ) )+
SET IN_J = IN_J + 1+
ELSE
SET Environment.Variables.BRANCH_LIST[IN_J] = TRIM ( CH_VIJETHA_BRANCH ) +
SET CH_VIJETHA_BRANCH = ''+
END IF+
END WHILE+
SET BRANCH_CARDINALITY = CARDINALITY(Environment.Variables.BRANCH_LIST[])+
--------------------START OF STORING COMMA SEPARATED 'CHANNELS' WHICH ARE COMING FROM MESSAGE INTO ENVIRONMENT VAIABLE-----------------
SET IN_M = 1+
WHILE ( CH_VIJETHA_CHANNEL <> '' ) DO
-- find out the position of the separator in the 'BRANCH' field
SET IN_TEMP_POSTION_K = ( POSITION ( ',' IN CH_VIJETHA_CHANNEL ) )+
-- check if start of 'BRANCH' field
IF (IN_TEMP_POSTION_K <> '0') THEN
SET Environment.Variables.CHANNEL_LIST[IN_M] = TRIM (SUBSTRING ( CH_VIJETHA_CHANNEL FROM 1 FOR ( IN_TEMP_POSTION_K - 1 ) ) ) +
SET CH_VIJETHA_CHANNEL = SUBSTRING ( CH_VIJETHA_CHANNEL FROM ( IN_TEMP_POSTION_K + 1 ) )+
SET IN_M = IN_M + 1+
ELSE
SET Environment.Variables.CHANNEL_LIST[IN_M] = TRIM ( CH_VIJETHA_CHANNEL ) +
SET CH_VIJETHA_CHANNEL = ''+
END IF+
END WHILE+
SET CHANNEL_CARDINALITY = CARDINALITY(Environment.Variables.CHANNEL_LIST[])+
IF((AREA_CARDINALITY <> BRANCH_CARDINALITY) AND (BRANCH_CARDINALITY <> CHANNEL_CARDINALITY)) THEN
THROW USER EXCEPTION VALUES('DATA ERROR! : COUNT OF AREA/BRANCH/CHANNEL ARE NOT EQUAL ')+
END IF+
---------------------------------START OF MAKING AREA LIST AS 'HPCBOM020','HPCBOM027' FROM AREAS OBTAINED FROM MESSAGE-----------------------------
IF (AREA_CARDINALITY > 0 ) THEN
SET IN_L = 1+
WHILE (IN_L<=AREA_CARDINALITY) DO
IF (POSITION(Environment.Variables.AREA_LIST[IN_L] IN VALID_AREALIST)=0) THEN
SET VALID_AREALIST=VALID_AREALIST || '''' ||Environment.Variables.AREA_LIST[IN_L] || ''''+
IF (IN_L<AREA_CARDINALITY) THEN
SET VALID_AREALIST=VALID_AREALIST||','+
END IF+
END IF+
SET IN_L=IN_L+1+
END WHILE+
--Removing the extra comma(,) if it has come in VALIDRSLIST generated above
IF (SUBSTRING(VALID_AREALIST FROM LENGTH(VALID_AREALIST))=',') THEN
SET VALID_AREALIST=SUBSTRING(VALID_AREALIST FROM 1 FOR LENGTH(VALID_AREALIST)-1)+
END IF+
ELSE
SET VALID_AREALIST = VALID_AREALIST|| '''' ||VALID_AREALIST || ''''+
END IF+
---------------------------------END OF MAKING AREA LIST FROM AREAS OBTAINED FROM MESSAGE-----------------------------
-----------------------------------START OF GETTING ACTIVITY LIST FROM DATABASE----------------------------------------
SET ACTIVITY_STAT='(SELECT DISTINCT A.ACTIVITY_ID,A.FROM_DATE,A.TO_DATE FROM TBL_CU_VIJETA_INFO_NEW AS A, TBL_CU_VIJETA_SCOPE AS B WHERE A.ACTIVE=''Y'' AND B.AREA IN (' || VALID_AREALIST ||') AND A.BRAND_VARIANT=' || '''' || CH_IVCODE1 || '''' || ' AND A.OUTLET_TIER=' || '''' || CH_OUTLET_TIER || '''' || ' AND A.ACTIVITY_ID=B.SOURCE_ID)'+
SET Environment.Variables.ACTIVITY_LIST[] =PASSTHRU(ACTIVITY_STAT)+
-----------------------------------END OF GETTING ACTIVITY LIST FROM DATABASE----------------------------------------
SET ACTIVITY_LIST_CARDINALITY = CARDINALITY(Environment.Variables.ACTIVITY_LIST[])+
SET IN_K=1+
WHILE (IN_K<=ACTIVITY_LIST_CARDINALITY) DO
-- Date selected from table is coming as "DATE '2007-12-06' " so format need to be converted
SET CH_TEMP_START_DATE = Environment.Variables.ACTIVITY_LIST[IN_K].FROM_DATE + --YYYY-MM-DD
SET CH_TEMP_START_DATE = SUBSTRING( CH_TEMP_START_DATE FROM 12 FOR 2) || '/' || SUBSTRING( CH_TEMP_START_DATE FROM 15 FOR 2) || '/' || SUBSTRING( CH_TEMP_START_DATE FROM 7 FOR 4)+ --MM/DD/YYYY
SET CH_TEMP_END_DATE = Environment.Variables.ACTIVITY_LIST[IN_K].TO_DATE + --YYYY-MM-DD
SET CH_TEMP_END_DATE = SUBSTRING( CH_TEMP_END_DATE FROM 12 FOR 2) || '/' || SUBSTRING( CH_TEMP_END_DATE FROM 15 FOR 2) || '/' || SUBSTRING( CH_TEMP_END_DATE FROM 7 FOR 4)+ --MM/DD/YYYY
SET UPDATE_QRY='UPDATE TBL_CU_VIJETA_INFO_NEW SET ACTIVE=''N'',TS=CURRENT_TIMESTAMP WHERE ACTIVE=''Y'' AND ((' || '''' || CH_START_DATE || '''' || ' BETWEEN ' || '''' || CH_TEMP_START_DATE || '''' || ' AND ' || '''' || CH_TEMP_END_DATE || '''' || ') OR (' || '''' || CH_END_DATE || '''' || ' BETWEEN ' || '''' || CH_TEMP_START_DATE || '''' || ' AND ' || '''' || CH_TEMP_END_DATE || '''' || ')) AND ACTIVITY_ID =' || '''' || Environment.Variables.ACTIVITY_LIST[IN_K].ACTIVITY_ID || '''' || ''+
--SET UPDATE_QRY='UPDATE TBL_CU_VIJETA_INFO_NEW SET ACTIVE=''N'',TS=CURRENT_TIMESTAMP WHERE ACTIVE=''Y'' AND ACTIVITY_ID =' || '''' || Environment.Variables.ACTIVITY_LIST[IN_K].ACTIVITY_ID || '''' || ''+
PASSTHRU(UPDATE_QRY)+
SET IN_K=IN_K+1+
END WHILE+
INSERT INTO Database.TBL_CU_VIJETA_INFO_NEW
(
ACTIVITY_ID,
PACK_CODE,
PROFIT_CENTER,
DIVISION,
CATEGORY,
CATEGORY_DESCRIPTION,
BRAND,
BRAND_DESCRIPTION,
BRAND_VARIANT,
BRAND_VARIANT_DESCRIPTION,
FROM_DATE,
TO_DATE,
DESCRIPTION,
PROMO_POINTS,
UNITS,
UOM,
OUTLET_TIER,
OUTLET_CATEGORY,
ACTIVE)
VALUES(
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."ACTIVITY_ID")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."PACK_CODE")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."PC")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."DIVISION")),
'NA',
'NA',
'NA',
'NA',
CH_IVCODE1,
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."IV_DESC")),
CH_START_DATE,
CH_END_DATE,
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."PACK_DESC")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."POWER_POINTS")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."UNITS")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."UOM")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."OUTLET_TIER")),
'VIJETHA',
'Y')+
SET IN_I = 1+
WHILE (IN_I <= AREA_CARDINALITY) DO
--SET BRANCH_STAT='(SELECT DISTINCT A.AREA, A.BRANCH, B.CHANNEL_TYPE FROM SALES_HIERARCHY AS A, CUSTOMER_BASE_MASTER AS B WHERE A.CRS = B.RSCODE AND A.AREA=' || '''' || Environment.Variables.AREA_LIST[IN_I] || '''' || ')'+
--SET Environment.Variables.BRANCH_CHANNEL[]=PASSTHRU(BRANCH_STAT)+
SET Environment.Variables.id_val[] = PASSTHRU('SELECT nextVAL FOR id_val FROM sysibm.sysdummy1')+
SET IN_TEMP_ID=CAST(Environment.Variables.id_val[1]."1" AS INTEGER)+
SET CH_TEMP_ID = CAST(IN_TEMP_ID AS CHAR)+
SET CH_SCOPE_ID = 'SCPVJ' || CH_TEMP_ID + -- A unique scope id has to be generated
INSERT INTO Database.TBL_CU_VIJETA_SCOPE(
SCOPE_ID,
SOURCE_ID,
DIVISION,
PROFIT_CENTER,
BRANCH,
CHANNEL,
AREA,
ZONE,
TTY,
CRS,
STATE,
ACTIVE
)
VALUES(
CH_SCOPE_ID,
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."ACTIVITY_ID")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."DIVISION")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."PC")),
Environment.Variables.BRANCH_LIST[IN_I],
Environment.Variables.CHANNEL_LIST[IN_I],
Environment.Variables.AREA_LIST[IN_I],
'ALL',
'ALL',
'ALL',
'NA',
'Y')+
SET IN_I=IN_I+1+
END WHILE+
SET IN_COUNTER = IN_COUNTER + 1 +
END WHILE+
UPDATE Database.TBL_CU_SCHEMES_AUDIT AS T SET STATUS=43, TS= CURRENT_TIMESTAMP WHERE T.USER_ID=CH_FILE_NAME+
SET Environment.Variables.GODB_INSERT_FLAG = 'Y'+
RETURN TRUE+
END+
CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot+
END+
END MODULE+
CREATE COMPUTE MODULE CUP_VIJETHA_PACKS_MF_Compute1
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyEntireMessage()+
-------------------------------- Start of Declaration Section ------------------------------------------------
DECLARE CH_AREA CHAR+
DECLARE CH_BRANCH CHAR+
DECLARE CH_CHANNEL CHAR+
DECLARE CH_PACK_DESC CHAR+
DECLARE CH_IVCODE1 CHAR+
DECLARE CH_UNIT CHAR+
DECLARE CH_UOM CHAR+
DECLARE CH_POWER_POINTS CHAR+
DECLARE CH_OUTLET_TIER CHAR+
DECLARE CH_START_DATE CHAR+
DECLARE CH_END_DATE CHAR+
DECLARE CH_FILE_NAME CHAR+
DECLARE IN_COUNTER,IN_VIJETHA_COUNT INTEGER+
DECLARE CH_VIJETHA_AREA CHAR+
DECLARE CH_VIJETHA_BRANCH CHAR+
DECLARE CH_VIJETHA_CHANNEL CHAR+
DECLARE IN_TEMP_POSTION_I,IN_I,IN_L,IN_K,AREA_CARDINALITY,ACTIVITY_LIST_CARDINALITY INTEGER+
DECLARE IN_TEMP_POSTION_J,IN_TEMP_POSTION_K,IN_J,IN_M INTEGER+
DECLARE BRANCH_CARDINALITY,CHANNEL_CARDINALITY INTEGER+
DECLARE ACTIVITY_STAT,BRANCH_STAT CHAR+
DECLARE VALID_AREALIST,UPDATE_QRY CHAR+
DECLARE IN_TEMP_ID,CH_TEMP_ID, CH_SCOPE_SYNC_ID CHAR+
DECLARE CH_TEMP_START_DATE,CH_TEMP_END_DATE CHAR+
-------------------------------- End of Declaration Section ------------------------------------------------
-------------------------------- Start of Initialization section --------------------------------------------
SET IN_COUNTER = 1+
SET IN_VIJETHA_COUNT = CARDINALITY ( "InputBody"."VIJETHA_RECORDS"[] ) +
-------------------------------- End of Initialization section --------------------------------------------
SET IN_COUNTER = 1+
WHILE ( IN_COUNTER <= IN_VIJETHA_COUNT ) DO
SET CH_AREA = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."AREA")) +
SET CH_IVCODE1 = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."IV_CODE")) +
SET CH_OUTLET_TIER = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."OUTLET_TIER"))+
SET CH_START_DATE = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."START_DATE")) +
SET CH_END_DATE = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."END_DATE")) +
SET CH_FILE_NAME = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."FILE_NAME")) +
SET CH_BRANCH = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."BRANCH")) +
SET CH_CHANNEL = TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."CHANNEL")) +
SET CH_VIJETHA_AREA = CH_AREA+
SET CH_VIJETHA_BRANCH = CH_BRANCH+
SET CH_VIJETHA_CHANNEL = CH_CHANNEL+
SET IN_I = 1+
SET VALID_AREALIST =''+
WHILE ( CH_VIJETHA_AREA <> '' ) DO
-- find out the position of the separator in the 'DIVISION' field
SET IN_TEMP_POSTION_I = ( POSITION ( ',' IN CH_VIJETHA_AREA ) )+
-- check if start of 'DIVISION' field
IF (IN_TEMP_POSTION_I <> '0') THEN
SET Environment.Variables.AREA_LIST[IN_I] = TRIM (SUBSTRING ( CH_VIJETHA_AREA FROM 1 FOR ( IN_TEMP_POSTION_I - 1 ) ) ) +
SET CH_VIJETHA_AREA = SUBSTRING ( CH_VIJETHA_AREA FROM ( IN_TEMP_POSTION_I + 1 ) )+
SET IN_I = IN_I + 1+
ELSE
SET Environment.Variables.AREA_LIST[IN_I] = TRIM ( CH_VIJETHA_AREA ) +
SET CH_VIJETHA_AREA = ''+
END IF+
END WHILE+
SET AREA_CARDINALITY = CARDINALITY(Environment.Variables.AREA_LIST[])+
--------------------START OF STORING COMMA SEPARATED 'BRANCHS' WHICH ARE COMING FROM MESSAGE INTO ENVIRONMENT VAIABLE-----------------
SET IN_J = 1+
WHILE ( CH_VIJETHA_BRANCH <> '' ) DO
-- find out the position of the separator in the 'BRANCH' field
SET IN_TEMP_POSTION_J = ( POSITION ( ',' IN CH_VIJETHA_BRANCH ) )+
-- check if start of 'BRANCH' field
IF (IN_TEMP_POSTION_J <> '0') THEN
SET Environment.Variables.BRANCH_LIST[IN_J] = TRIM (SUBSTRING ( CH_VIJETHA_BRANCH FROM 1 FOR ( IN_TEMP_POSTION_J - 1 ) ) ) +
SET CH_VIJETHA_BRANCH = SUBSTRING ( CH_VIJETHA_BRANCH FROM ( IN_TEMP_POSTION_J + 1 ) )+
SET IN_J = IN_J + 1+
ELSE
SET Environment.Variables.BRANCH_LIST[IN_J] = TRIM ( CH_VIJETHA_BRANCH ) +
SET CH_VIJETHA_BRANCH = ''+
END IF+
END WHILE+
SET BRANCH_CARDINALITY = CARDINALITY(Environment.Variables.BRANCH_LIST[])+
--------------------START OF STORING COMMA SEPARATED 'CHANNELS' WHICH ARE COMING FROM MESSAGE INTO ENVIRONMENT VAIABLE-----------------
SET IN_M = 1+
WHILE ( CH_VIJETHA_CHANNEL <> '' ) DO
-- find out the position of the separator in the 'BRANCH' field
SET IN_TEMP_POSTION_K = ( POSITION ( ',' IN CH_VIJETHA_CHANNEL ) )+
-- check if start of 'BRANCH' field
IF (IN_TEMP_POSTION_K <> '0') THEN
SET Environment.Variables.CHANNEL_LIST[IN_M] = TRIM (SUBSTRING ( CH_VIJETHA_CHANNEL FROM 1 FOR ( IN_TEMP_POSTION_K - 1 ) ) ) +
SET CH_VIJETHA_CHANNEL = SUBSTRING ( CH_VIJETHA_CHANNEL FROM ( IN_TEMP_POSTION_K + 1 ) )+
SET IN_M = IN_M + 1+
ELSE
SET Environment.Variables.CHANNEL_LIST[IN_M] = TRIM ( CH_VIJETHA_CHANNEL ) +
SET CH_VIJETHA_CHANNEL = ''+
END IF+
END WHILE+
SET CHANNEL_CARDINALITY = CARDINALITY(Environment.Variables.CHANNEL_LIST[])+
---------------------------THROWING AN ERROR IF COUNT OF AREA/BRANCH/CHANNEL ARE NOT EQUAL-------------------------------
IF((AREA_CARDINALITY <> BRANCH_CARDINALITY) AND (BRANCH_CARDINALITY <> CHANNEL_CARDINALITY)) THEN
THROW USER EXCEPTION VALUES('DATA ERROR! : COUNT OF AREA/BRANCH/CHANNEL ARE NOT EQUAL ')+
END IF+
---------------------------------START OF MAKING AREA LIST AS 'HPCBOM020','HPCBOM027' FROM AREAS OBTAINED FROM MESSAGE-----------------------------
IF (AREA_CARDINALITY > 0 ) THEN
SET IN_L = 1+
WHILE (IN_L<=AREA_CARDINALITY) DO
IF (POSITION(Environment.Variables.AREA_LIST[IN_L] IN VALID_AREALIST)=0) THEN
SET VALID_AREALIST=VALID_AREALIST || '''' ||Environment.Variables.AREA_LIST[IN_L] || ''''+
IF (IN_L<AREA_CARDINALITY) THEN
SET VALID_AREALIST=VALID_AREALIST||','+
END IF+
END IF+
SET IN_L=IN_L+1+
END WHILE+
--Removing the extra comma(,) if it has come in VALIDRSLIST generated above
IF (SUBSTRING(VALID_AREALIST FROM LENGTH(VALID_AREALIST))=',') THEN
SET VALID_AREALIST=SUBSTRING(VALID_AREALIST FROM 1 FOR LENGTH(VALID_AREALIST)-1)+
END IF+
ELSE
SET VALID_AREALIST = VALID_AREALIST|| '''' ||VALID_AREALIST || ''''+
END IF+
---------------------------------END OF MAKING AREA LIST FROM AREAS OBTAINED FROM MESSAGE-----------------------------
-----------------------------------START OF GETTING ACTIVITY LIST FROM DATABASE----------------------------------------
SET ACTIVITY_STAT='(SELECT DISTINCT A.ACTIVITY_ID,A.FROM_DATE,A.TO_DATE FROM GODB.TBL_CU_VIJETA_INFO_NEW_SYNC AS A, GODB.TBL_CU_SCOPE_SYNC AS B WHERE A.ACTIVE=''Y'' AND B.AREA IN (' || VALID_AREALIST ||') AND A.BRAND_VARIANT=' || '''' || CH_IVCODE1 || '''' || ' AND A.OUTLET_TIER=' || '''' || CH_OUTLET_TIER || '''' || ' AND A.ACTIVITY_ID=B.SOURCE_ID)'+
SET Environment.Variables.ACTIVITY_LIST[] =PASSTHRU(ACTIVITY_STAT)+
-----------------------------------END OF GETTING ACTIVITY LIST FROM DATABASE----------------------------------------
SET ACTIVITY_LIST_CARDINALITY = CARDINALITY(Environment.Variables.ACTIVITY_LIST[])+
SET IN_K=1+
WHILE (IN_K<=ACTIVITY_LIST_CARDINALITY) DO
SET CH_TEMP_START_DATE = Environment.Variables.ACTIVITY_LIST[IN_K].FROM_DATE + --YYYY-MM-DD
SET CH_TEMP_START_DATE = SUBSTRING( CH_TEMP_START_DATE FROM 12 FOR 2) || '/' || SUBSTRING( CH_TEMP_START_DATE FROM 15 FOR 2) || '/' || SUBSTRING( CH_TEMP_START_DATE FROM 7 FOR 4)+ --MM/DD/YYYY
SET CH_TEMP_END_DATE = Environment.Variables.ACTIVITY_LIST[IN_K].TO_DATE + --YYYY-MM-DD
SET CH_TEMP_END_DATE = SUBSTRING( CH_TEMP_END_DATE FROM 12 FOR 2) || '/' || SUBSTRING( CH_TEMP_END_DATE FROM 15 FOR 2) || '/' || SUBSTRING( CH_TEMP_END_DATE FROM 7 FOR 4)+ --MM/DD/YYYY
SET UPDATE_QRY='UPDATE GODB.TBL_CU_VIJETA_INFO_NEW_SYNC SET ACTIVE=''N'',TS=CURRENT_TIMESTAMP WHERE ACTIVE=''Y'' AND ((' || '''' || CH_START_DATE || '''' || ' BETWEEN ' || '''' || CH_TEMP_START_DATE || '''' || ' AND ' || '''' || CH_TEMP_END_DATE || '''' || ') OR (' || '''' || CH_END_DATE || '''' || ' BETWEEN ' || '''' || CH_TEMP_START_DATE || '''' || ' AND ' || '''' || CH_TEMP_END_DATE || '''' || ')) AND ACTIVITY_ID =' || '''' || Environment.Variables.ACTIVITY_LIST[IN_K].ACTIVITY_ID || '''' || ''+
PASSTHRU(UPDATE_QRY)+
SET IN_K=IN_K+1+
END WHILE+
INSERT INTO Database.GODB.TBL_CU_VIJETA_INFO_NEW_SYNC
(
ACTIVITY_ID,
PROFIT_CENTER,
DIVISION,
CATEGORY,
CATEGORY_DESCRIPTION,
BRAND,
BRAND_DESCRIPTION,
BRAND_VARIANT,
BRAND_VARIANT_DESCRIPTION,
FROM_DATE,
TO_DATE,
PROMO_POINTS,
UNITS,
UOM,
OUTLET_TIER,
OUTLET_CATEGORY,
ACTIVE,
DESCRIPTION,
PACK_CODE)
VALUES(
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."ACTIVITY_ID")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."PC")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."DIVISION")),
'NA',
'NA',
'NA',
'NA',
CH_IVCODE1,
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."IV_DESC")),
CH_START_DATE,
CH_END_DATE,
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."POWER_POINTS")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."UNITS")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."UOM")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."OUTLET_TIER")),
'VIJETHA',
'Y',
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."PACK_DESC")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."PACK_CODE")))+
SET IN_I = 1+
WHILE (IN_I <= AREA_CARDINALITY) DO
--SET BRANCH_STAT='(SELECT DISTINCT A.AREA, A.BRANCH, B.CHANNEL_TYPE FROM GODB.SALES_HIERARCHY AS A, GODB.CUSTOMER_BASE_MASTER AS B WHERE A.CRS = B.RSCODE AND A.AREA=' || '''' || Environment.Variables.AREA_LIST[IN_I] || '''' || ')'+
--SET Environment.Variables.BRANCH_CHANNEL[]=PASSTHRU(BRANCH_STAT)+
SET Environment.Variables.GODB_SEQ[] = PASSTHRU('SELECT NEXTVAL FOR godb.godb_seq FROM SYSIBM.SYSDUMMY1')+
SET IN_TEMP_ID=CAST(Environment.Variables.GODB_SEQ[1]."1" AS INTEGER)+
SET CH_TEMP_ID = CAST(IN_TEMP_ID AS CHAR)+
SET CH_SCOPE_SYNC_ID = 'SCPSCAR' || CH_TEMP_ID + -- A unique scope id has to be generated
INSERT INTO Database.GODB.TBL_CU_SCOPE_SYNC(
SCOPE_SYNC_ID,
SOURCE_ID,
PC,
DIVISION,
BRANCH,
CHANNEL,
AREA,
ZONE,
TTY,
CRS,
STATE,
ACTIVE
)
VALUES(
CH_SCOPE_SYNC_ID,
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."ACTIVITY_ID")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."PC")),
TRIM('"' FROM TRIM("InputBody"."VIJETHA_RECORDS"[IN_COUNTER]."DIVISION")),
Environment.Variables.BRANCH_LIST[IN_I],
Environment.Variables.CHANNEL_LIST[IN_I],
Environment.Variables.AREA_LIST[IN_I],
'ALL',
'ALL',
'ALL',
'NA',
'Y')+
SET IN_I=IN_I+1+
END WHILE+
SET IN_COUNTER = IN_COUNTER + 1 +
END WHILE+
RETURN FALSE+
END+
CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot+
END+
END MODULE+ |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu May 22, 2008 1:07 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Looks like you are building your pass through query without parameters. => This will require one handle per passthrough executed... See the post about this from about a week back.
BTW the practice of concatenating your string for the query is dangerous because it paves the way for a SQL injection attack.
Best practice is to have a static query with parameters for the dynamic elements...
Enjoy 
_________________
MQ & Broker admin |
|
| 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
|
|
|
|
