Posted: Tue Dec 23, 2003 11:08 pm Post subject: SQL queries(DB2) on MVS
I have some queries regarding sql syntax usage on DB2 on MVS.
The following statements are working fine on AIX/UDB
1)
Code:
SELECT distinct TCI_BP_ID,
TCI_SCA_ID,
TCI_BLNG_SCA_ID,
TCI_CHRG_ITEM,
TCI_CHRG_ITEM_CD,
SEC_SEC_TYP,
SEC_SEC_TYP_CD,
LCD_SUBCSTDN_ID,
SEC_PAR_VAL,
SEC_ISSUE_CRNC,
CSP_SEC_ID,
(select sum(COALESCE(e.CSP_QTY_OF_SEC,0) )
FROM CSP_CSTDY_POS e
WHERE
e.branchid = 'IE' AND
a.branchid = 'IE' AND
c.branchid = 'IE' AND
e.CSP_SCA_ID = a.TCI_SCA_ID
AND e.CSP_SEC_ID = b.SEC_SEC_ID
AND e.CSP_QTY_OF_SEC > 0
AND e.CSP_LOC = c.LCD_LOC
GROUP BY e.CSP_SCA_ID,
e.CSP_SEC_ID,e.CSP_LOC) ,
TCI_BLNG_BP_ID,
0,
SEC_CNTRY_OF_ORIG,
SEC_SEC_CTGRY ,
SEC_SCRPLS_IND,
CSP_LOC,
a.branchid
,(CASE SEC_LSTNG_IND when 0 then 1
WHEN 1 then 0
ELSE NULL
END)
FROM TCI_TMP_CHRGS a, SEC_SEC b,CSP_CSTDY_POS
d,LCD_LOCATION_DTLS c
WHERE
a.branchid = 'IE' AND
d.branchid = 'IE' AND
c.branchid = 'IE' AND
a.TCI_SCA_ID = d.CSP_SCA_ID
AND SEC_SEC_ID = d.CSP_SEC_ID
AND d.CSP_QTY_OF_SEC > 0
AND c.LCD_LOC = CSP_LOC ;
2)
Code:
EXEC SQL UPDATE TPT_CNTRY_PORT_VAL a
set TPT_CHRG_AMT = :h_chrg_amt * (( select sum(TCD_MRKT_VAL)
from TCD_TMP_CHRGS_DTLS b
where TCD_SCA_ID = :h_blng_sca_id and
TCD_SCA_ID = TPT_SCA_ID and
TCD_CHRG_ITEM = :h_chrg_item and
TCD_CHRG_ITEM = TPT_CHRG_ITM and
TCD_LOC = TPT_LOC and
TPT_CHRG_PCKG in (1,4) and
TCD_SEC_ID = TPT_SEC_ID and
/*TPT_BLNG_BP_ID = :h_blng_bp_id and*/
TCD_BLNG_BP_ID = :h_blng_bp_id and
TCD_BLNG_BP_ID = TPT_BLNG_BP_ID and
TCD_PRCS_IND = 1 and
TPT_STRT_DT = :h_strt_dt and
a.branchid = :h_brnch_id and
b.branchid = :h_brnch_id
group by TPT_LOC
)/:h_mrkt_val)
where exists
(select 1 from
TCD_TMP_CHRGS_DTLS b
where TCD_SCA_ID = :h_blng_sca_id and
TCD_SCA_ID = TPT_SCA_ID and
TCD_CHRG_ITEM = :h_chrg_item and
TCD_CHRG_ITEM = TPT_CHRG_ITM and
TCD_LOC = TPT_LOC and
TPT_CHRG_PCKG in (1,4) and
TCD_SEC_ID = TPT_SEC_ID and
/*TPT_BLNG_BP_ID = :h_blng_bp_id and*/
TCD_BLNG_BP_ID = :h_blng_bp_id and
TCD_BLNG_BP_ID = TPT_BLNG_BP_ID and
TCD_PRCS_IND = 1 and
TPT_STRT_DT = :h_strt_dt and
a.branchid = :h_brnch_id and
b.branchid = :h_brnch_id
group by TPT_LOC
) ;
These two statements are working fine on AIX/UDB,
The problem comes when they are compiled on MVS , the sqlcode returned by the precompiler is a meaningless(SQLCODE= -84). This disappears when I remove the Group By clause from these queries. The dm's get compiled correctly after the removal.But they start giving runtime errors. Removing the group by clause results in functional mistakes from the first query and a scalar fullselect condition from the second query.
Now both these queries are designed to be executed on a mass scale, the first one is a mass insert and the other one is a mass update. Can you suggest any alternative way of executing these queries without modifying the functional logic?
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Wed Dec 24, 2003 7:16 am Post subject:
nivedita,
Both of the SQL statements are not supported by DB2. Sqlcode of -84 is not meaningless.check this link which explains in detail about the error code -84
Now both these queries are designed to be executed on a mass scale, the first one is a mass insert and the other one is a mass update. Can you suggest any alternative way of executing these queries without modifying the functional logic?
Joined: 29 Oct 2003 Posts: 10 Topics: 0 Location: Indore, India
Posted: Tue Dec 30, 2003 4:15 am Post subject:
Hi...
In MVS, "groupby" has certain restrictions...so does "orderby",..and hey nivedita...if you really want to do any mass insert/update , use triggers instead of type I/II queries ...I personally wud use stored procedures, but considering performance bottlenecks in MVS, triggers wud do the job for you...
Let me know if you have any problem with triggers..
Regards _________________ Regards,
Naveen Srinivasan
Computer Sciences Corporation
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
