MVSFORUMS.com :: View topic - Fetch cursor with OPTIMIZE FOR 1 ROW

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Quick Manuals

Profile

Fetch cursor with OPTIMIZE FOR 1 ROW

MVSFORUMS.com Forum Index -> Database

View previous topic :: View next topic

Author

Message

Lean
Beginner

Joined: 07 Aug 2012
Posts: 37
Topics: 11

Posted: Wed Jan 07, 2015 7:15 am Post subject: Fetch cursor with OPTIMIZE FOR 1 ROW

hi All
I have declare one cursor like below, but it select the second records when excute the fetch func. can pls help share some advice, how it works.
and one more question, it will only return one record when exec the fetch func, right?

Code:

SELECT
CHRG_NRM_PRC_CODE
FROM
VC_CHARGES_SETUP
WHERE
PARTITION_KEY = 15
AND INV_SYSTEM_ID = 'SB'
AND CTRL_1 = 88
AND CTRL_2 = '001'
AND CTRL_3 = '000'
AND CTRL_4 = '0000'
AND
INV_SYSTEM_ID = 'SB' AND
(CTRL_1 = 88 OR
CTRL_1 = 0 )
AND
(CHANNEL = 'ALL' OR
CHANNEL = ' ' )
AND
TXN_TYPE = 'QQ'
AND
(SUB_TXN_TYPE = 'NORM' OR
SUB_TXN_TYPE = ' ' )
AND
(STOCK_MARKET = ' ' OR
STOCK_MARKET = 'SEHK' )
AND
(ISSUER_CODE = ' ' OR
ISSUER_CODE = ' ' )
AND
(BROKER_CODE = ' ' OR
BROKER_CODE = ' ' )
AND
(ASSET_CLASS = ' ' OR
ASSET_CLASS = ' ' )
AND
(ASSET_CATEGORY = ' ' OR
ASSET_CATEGORY = ' ' )
AND
SECURITY_TYPE = 'E'
AND
(SECURITY_SUB_TYPE = ' ' OR
SECURITY_SUB_TYPE = ' ' )
AND
(SECURITY_CODE = ' ' OR
SECURITY_CODE = ' ' )
AND
(CURRENCY_CODE = ' ' OR
CURRENCY_CODE = ' ' )
AND
(CUST_CATEGORY = 'ALL' OR
CUST_CATEGORY = 'C13')
AND
(ACCOUNT_NBR = 'ALL' OR
ACCOUNT_NBR = ' ' OR
ACCOUNT_NBR = '0026486393' )
AND
((SECURITY_CODE_FROM = ' ' AND
SECURITY_CODE_TO = ' ' ) OR
(SECURITY_CODE_FROM = ' '
AND
SECURITY_CODE_TO = ' ' ))
AND
((ASSET_CLASS_FROM = ' ' AND
ASSET_CLASS_TO = ' ' ) OR
(ASSET_CLASS_FROM = ' '
AND
ASSET_CLASS_TO = ' ' ))
AND
((ASSET_CAT_FROM = ' ' AND
ASSET_CAT_TO = ' ' ) OR
(ASSET_CAT_FROM = ' '
AND
ASSET_CAT_TO = ' ' ))
AND
EFFECTIVE_DATE <= '2015-01-05'
AND
(AUM_TIV_IND = ' ' OR
AUM_TIV_IND = ' ')
AND
(SECURITY_CLASS = ' ' OR
SECURITY_CLASS = ' ')
AND
(PRODUCT_CODE = 'SBC' OR
PRODUCT_CODE = ' ' )
AND
LEVEL_1 <= ' '
AND
LEVEL_2 <= ' '
AND
STATUS = 'A'
AND
(PARENT_ISSUER = 'ALL' OR
PARENT_ISSUER = ' ' OR
PARENT_ISSUER = ' ' )
AND
(TAX_DOM = 'ALL' OR
TAX_DOM = ' ' OR
TAX_DOM = 'HK' )
ORDER BY
PRIORITY DESC,
SUB_TXN_TYPE DESC,
CHANNEL DESC,
EFFECTIVE_DATE DESC,
CTRL_1 DESC
OPTIMIZE FOR 1 ROW

below is the result when run above SQL independently

Code:

CHRG
SUB NRM LAST
TXN TXN STOCK SECURITY CUST PRC MAKER AUTH MODIFIED
CHANNEL TYPE TYPE MARKET TYPE CATEGORY PRIORITY CODE TS TS TS
------- ----- ----- ------ ------------------ --------- ----- -------------------------- -------------------------- --------------------------
ALL QQ NORM SEHK E C13 100010110 QQHK1 2013-09-01-10.50.46.519009 2013-09-01-11.07.25.521435 2013-09-01-11.07.25.521435
ALL QQ NORM SEHK E ALL 10110 QQHK2 2013-09-01-10.50.07.573918 2013-09-01-11.03.43.371876 2013-09-01-11.03.43.371876

then we have another similar cursor, it return the expected result.

Code:

SELECT CHRG_NRM_PRC_CODE
FROM VC_CHARGES_SETUP
WHERE
PARTITION_KEY = 15
AND INV_SYSTEM_ID = 'SB'
AND
(CTRL_1 = 88 OR
CTRL_1 = 0 )
AND
(CTRL_2 = '001' OR
CTRL_2 = '000' OR
CTRL_2 = ' ' )
AND
(CHANNEL = 'ALL' OR
CHANNEL = 'ALL' )
AND
TXN_TYPE = 'QQ'
AND
(SUB_TXN_TYPE = 'NORM' OR
SUB_TXN_TYPE = ' ' )
AND
(COUNTRY_CODE = 'HK' OR
COUNTRY_CODE = 'ALL' OR
COUNTRY_CODE = ' ' )
AND
(STOCK_MARKET = ' ' OR
STOCK_MARKET = 'SEHK' )
AND
(ISSUER_CODE = ' ' OR
ISSUER_CODE = ' ' )
AND
(BROKER_CODE = ' ' OR
BROKER_CODE = ' ' )
AND
(ASSET_CLASS = ' ' OR
ASSET_CLASS = ' ' )
AND
(ASSET_CATEGORY = ' ' OR
ASSET_CATEGORY = ' ' )
AND
SECURITY_TYPE = 'E'
AND
(SECURITY_SUB_TYPE = ' ' OR
SECURITY_SUB_TYPE = ' ' )
AND
(SECURITY_CODE = ' ' OR
SECURITY_CODE = ' ' )
AND
(CURRENCY_CODE = ' ' OR
CURRENCY_CODE = '344' )
AND
(CUST_CATEGORY = 'ALL' OR
CUST_CATEGORY = 'A' OR
CUST_CATEGORY = 'C13' )
AND
(ACCOUNT_NBR = ' ' OR
ACCOUNT_NBR = '0026486393' )
AND
(PRODUCT_CODE = ' ' OR
PRODUCT_CODE = 'SBC' )
AND
((SECURITY_CODE_FROM = ' ' AND
SECURITY_CODE_TO = ' ' ) OR
(SECURITY_CODE_FROM = ' '
AND
SECURITY_CODE_TO = ' ' ))
AND
((ASSET_CLASS_FROM = ' ' AND
ASSET_CLASS_TO = ' ' ) OR
(ASSET_CLASS_FROM = ' '
AND
ASSET_CLASS_TO = ' ' ))
AND
((ASSET_CAT_FROM = ' ' AND
ASSET_CAT_TO = ' ' ) OR
(ASSET_CAT_FROM <= ' '
AND
ASSET_CAT_TO >= ' ' ) )
AND
EFFECTIVE_DATE <= '2015-01-05'
AND
(AUM_TIV_IND = ' ' OR
AUM_TIV_IND = ' ')
AND
(SECURITY_CLASS = ' ' OR
SECURITY_CLASS = 'AL' OR
SECURITY_CLASS = ' ')
AND
LEVEL_1 <= ' '
AND
LEVEL_2 <= ' '
AND
STATUS = 'A'
AND
(PARENT_ISSUER = 'ALL' OR
PARENT_ISSUER = ' ' OR
PARENT_ISSUER = ' ' )
AND
(TAX_DOM = 'ALL' OR
TAX_DOM = ' ' OR
TAX_DOM = 'HK' )
ORDER BY
PRIORITY DESC,
SUB_TXN_TYPE DESC,
CHANNEL DESC,
EFFECTIVE_DATE DESC,
CTRL_1 DESC,
CTRL_2 DESC
OPTIMIZE FOR 1 ROW

below is the result when run above SQL independently

Code:

CHRG
SUB NRM LAST
TXN TXN STOCK SECURITY CUST PRC MAKER AUTH MODIFIED
CHANNEL TYPE TYPE MARKET TYPE CATEGORY PRIORITY CODE TS TS TS
------- ----- ----- ------ ---------- -------- --------- ----- -------------------------- -------------------------- --------------------------
ALL QQ NORM SEHK E C13 100010110 QQHK1 2013-09-01-10.50.46.519009 2013-09-01-11.07.25.521435 2013-09-01-11.07.25.521435
ALL QQ NORM SEHK E ALL 10110 QQHK2 2013-09-01-10.50.07.573918 2013-09-01-11.03.43.371876 2013-09-01-11.03.43.371876

below is all the index fields for this table

Code:

COLNAME
------------------
PARTITION_KEY
CTRL_2
CHANNEL
TXN_TYPE
SUB_TXN_TYPE
STOCK_MARKET
ISSUER_CODE
BROKER_CODE
ASSET_CLASS
ASSET_CATEGORY
SECURITY_TYPE
SECURITY_SUB_TYPE
SECURITY_CODE
CURRENCY_CODE
CUST_CATEGORY
ACCOUNT_NBR
SECURITY_CODE_FROM
SECURITY_CODE_TO
ASSET_CLASS_FROM
ASSET_CLASS_TO
ASSET_CAT_FROM
ASSET_CAT_TO
EFFECTIVE_DATE
SECURITY_CLASS
LEVEL_1
LEVEL_2
ACCOUNT_TYPE
COUNTRY_CODE
PRODUCT_CODE
PARENT_ISSUER
TAX_DOM

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12359
Topics: 75
Location: San Jose

Posted: Wed Jan 07, 2015 11:40 am Post subject: Re: Fetch cursor with OPTIMIZE FOR 1 ROW

Lean wrote:

Lean,

Your understanding of "OPTIMIZE FOR 1 ROW" is inaccurate. You need to understand that the OPTIMIZE FOR clause does not limit the number of rows that can be fetched or affect the result in any way other than performance. OPTIMIZE FOR clause is used to coerce DB2 into avoiding list prefetch in an access path. List prefetch causes DB2 to accumulate the record IDs (RIDs) of each row to be returned. The RIDs are then sorted so that DB2 can avoid re-reading pages. If the RIDs are sorted, then DB2 will read all records that exist on a single page at the same time. Without list prefetch the first page/screen usually will appear much quicker (even though DB2 may have to re-read pages to process the entire SQL result set)

Bottom line is that if you are retrieving only a few rows, you can use OPTIMIZE FOR 1 ROW to influence the access path that DB2 selects.

If your intention is to just get ONLY 1 record then you need to use FETCH FIRST 1 ROW ONLY
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu

Lean
Beginner

Joined: 07 Aug 2012
Posts: 37
Topics: 11

Posted: Wed Jan 07, 2015 9:27 pm Post subject:

ok, thanks kolusu

Display posts from previous:

	MVSFORUMS.com Forum Index -> Database	All times are GMT - 5 Hours
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