MVSFORUMS.com

satya_reddy · Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3

Current SQL Query in production is

EXEC SQL DECLARE NFOPEN1 CURSOR FOR
SELECT
CDBKXX_SERNUM
, CDBKXX_TRANQUAL
, CDBKXX_SEQNUM
, ORGN_PLAN_CD
, DEST_PLAN_CD
, TYPE_REQ
, TRANSM_MODE_CD
, RSN_CD
, SRC_CD

, ACT_CD
, CLERK_NUM
, REL_NUM
, CLM_TYPE
, CREATE_DT
, CREATE_DT_X
, ORGN_PLAN_STA_CD
, DEST_PLAN_STA_CD
, CASH_REFND_IND
, CASH_REFND_AMT
, CASH_REFND_AMT_X
, ADJ_SF_IND
, XREF_SCCF_NUM
, PREV_XREF_SCCF_NUM
, TOT_CHRG
, TOT_CHRG_X
, OPN_CLO_IND
, FMT_DB_POST_DT
, FMT_DB_POST_DT_X
, RESUB_SF_IND
, RESUB_DF_IND
, HIGH_SEQ_NUM
, HIGH_SEQ_NUM_X
, STAT_CD
, TRANS_IND
, DISP_CD
, ERR_CD_1
, ERR_CD_2
, ERR_CD_3
, ERR_CD_4
, ERR_CD_5
, PLAN_OWNER_IND
, RECYC_CTR
, RECYC_CTR_X
, EXCLUDE_PER_USER
, EXCLUDE_PER_BLUECD
, PRODUCT_CD
, PROV_NETWORK_ID
, PROV_NUMBER_SFX
, PCP_EFFECT_DATE
, PCP_EFFECT_DATE_X
, COV_START_DATE
, COV_START_DATE_X
, COV_END_DATE
, COV_END_DATE_X
, ACTIVITY_IND
, ACTIVITY_DATE
, ACTIVITY_DATE_X
, MEF_STAT_CD
, MEF_DISC_LVL_IND
, MEF_DISC_CD_1
, MEF_DISC_CD_2
, MEF_DISC_CD_3
, MEF_DISC_CD_4
, MEF_DISC_CD_5
, ECR_IND
, MEF_RCPT_DT
, MEF_RCPT_DT_X
FROM IPTFM81A
WHERE CDBKXX_SERNUM > :WS-SPACE2 AND
CDBKXX_SEQNUM > :WS-SPACE2 AND
CDBKXX_TRANQUAL > :WS-SPACE2 AND
FMT_DB_POST_DT >= :WS-DB2-CURRENTDT OR
OPN_CLO_IND = '1'
ORDER BY 1,3,2
END-EXEC.

satya_reddy · Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3

Performance Stats : 422K Excp Counts & 08.23 mins.
The problem was 26 buffers were needed thus depleting db2 buffer resources for other jobs, hence DBA

satya_reddy · Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3

This change did not yield results, as per the following mail
The program ITSSD988 is not picking up the new index created because SELECT
clause has all the columns and DB2 think that it will be double work to read
the index and then table space.

To solve this problem program ITSSD988 can code first SQL with key fields
only in the SELECT clause and then after getting the key fields use it to make
one more call which will be direct read.

We can test this solution to see the performance/get pages in DB2T else we
will go back to the original solution of using unload file to get the NF with
full key and then access the table

satya_reddy · Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3

Few options were tried today as listed below

satya_reddy · Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3

Option 1:

EXEC SQL DECLARE NFSET1 INSENSITIVE SCROLL CURSOR FOR
SELECT
A.CDBKXX_SERNUM
, A.CDBKXX_TRANQUAL
, A.CDBKXX_SEQNUM
FROM IPTFM81A A
WHERE A.CDBKXX_SERNUM > :WS-SPACE2 AND
A.CDBKXX_SEQNUM > :WS-SPACE2 AND
A.CDBKXX_TRANQUAL > :WS-SPACE2 AND
A.FMT_DB_POST_DT >= :WS-DB2-CURRENTDT OR
A.OPN_CLO_IND = '1'
ORDER BY 1,3,2
END-EXEC.

when SQLCODE of above fetch was zero, the lower query is executed.

EXEC SQL DECLARE NFOPEN1 CURSOR FOR
SELECT
B.CDBKXX_SERNUM
, B.CDBKXX_TRANQUAL
, B.CDBKXX_SEQNUM
, B.ORGN_PLAN_CD
, B.DEST_PLAN_CD
, B.TYPE_REQ
, B.TRANSM_MODE_CD
, B.RSN_CD
, B.SRC_CD
, B.ACT_CD
, B.CLERK_NUM
, B.REL_NUM
, B.CLM_TYPE
, B.CREATE_DT
, B.CREATE_DT_X
, B.ORGN_PLAN_STA_CD
, B.DEST_PLAN_STA_CD
, B.CASH_REFND_IND
, B.CASH_REFND_AMT
, B.CASH_REFND_AMT_X
, B.ADJ_SF_IND
, B.XREF_SCCF_NUM
, B.PREV_XREF_SCCF_NUM
, B.TOT_CHRG
, B.TOT_CHRG_X
, B.OPN_CLO_IND
, B.FMT_DB_POST_DT
, B.FMT_DB_POST_DT_X
, B.RESUB_SF_IND
, B.RESUB_DF_IND
, B.HIGH_SEQ_NUM
, B.HIGH_SEQ_NUM_X
, B.STAT_CD
, B.TRANS_IND
, B.DISP_CD
, B.ERR_CD_1
, B.ERR_CD_2
, B.ERR_CD_3
, B.ERR_CD_4
, B.ERR_CD_5
, B.PLAN_OWNER_IND
, B.RECYC_CTR
, B.RECYC_CTR_X
, B.EXCLUDE_PER_USER
, B.EXCLUDE_PER_BLUECD
, B.PRODUCT_CD
, B.PROV_NETWORK_ID
, B.PROV_NUMBER_SFX
, B.PCP_EFFECT_DATE
, B.PCP_EFFECT_DATE_X
, B.COV_START_DATE
, B.COV_START_DATE_X
, B.COV_END_DATE
, B.COV_END_DATE_X
, B.ACTIVITY_IND
, B.ACTIVITY_DATE
, B.ACTIVITY_DATE_X
, B.MEF_STAT_CD
, B.MEF_DISC_LVL_IND
, B.MEF_DISC_CD_1
, B.MEF_DISC_CD_2
, B.MEF_DISC_CD_3
, B.MEF_DISC_CD_4
, B.MEF_DISC_CD_5
, B.ECR_IND
, B.MEF_RCPT_DT
, B.MEF_RCPT_DT_X
FROM IPTFM81A B
WHERE B.CDBKXX_SERNUM = :CDBKXX-SERNUM-A AND
B.CDBKXX_TRANQUAL = :CDBKXX-TRANQUAL-A AND
B.CDBKXX_SEQNUM = :CDBKXX-SEQNUM-A
FETCH FIRST 1 ROW ONLY
END-EXEC.

Performance Stats : 678K Excp Counts & 14.33 mins.

satya_reddy · Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3

Option 2:

EXEC SQL DECLARE NFSET1 CURSOR FOR
SELECT
A.CDBKXX_SERNUM
, A.CDBKXX_TRANQUAL
, A.CDBKXX_SEQNUM
FROM IPTFM81A A
WHERE A.CDBKXX_SERNUM > :WS-SPACE2 AND
A.CDBKXX_SEQNUM > :WS-SPACE2 AND
A.CDBKXX_TRANQUAL > :WS-SPACE2 AND
A.FMT_DB_POST_DT >= :WS-DB2-CURRENTDT OR
A.OPN_CLO_IND = '1'
ORDER BY 1,3,2
END-EXEC.

when SQLCODE of above fetch was zero, the lower query is executed.

EXEC SQL DECLARE NFOPEN1 CURSOR FOR
SELECT
B.CDBKXX_SERNUM
, B.CDBKXX_TRANQUAL
, B.CDBKXX_SEQNUM
, B.ORGN_PLAN_CD
, B.DEST_PLAN_CD
, B.TYPE_REQ
, B.TRANSM_MODE_CD
, B.RSN_CD
, B.SRC_CD
, B.ACT_CD
, B.CLERK_NUM
, B.REL_NUM
, B.CLM_TYPE
, B.CREATE_DT
, B.CREATE_DT_X
, B.ORGN_PLAN_STA_CD
, B.DEST_PLAN_STA_CD
, B.CASH_REFND_IND
, B.CASH_REFND_AMT
, B.CASH_REFND_AMT_X
, B.ADJ_SF_IND
, B.XREF_SCCF_NUM
, B.PREV_XREF_SCCF_NUM
, B.TOT_CHRG
, B.TOT_CHRG_X
, B.OPN_CLO_IND
, B.FMT_DB_POST_DT
, B.FMT_DB_POST_DT_X
, B.RESUB_SF_IND
, B.RESUB_DF_IND
, B.HIGH_SEQ_NUM
, B.HIGH_SEQ_NUM_X
, B.STAT_CD
, B.TRANS_IND
, B.DISP_CD
, B.ERR_CD_1
, B.ERR_CD_2
, B.ERR_CD_3
, B.ERR_CD_4
, B.ERR_CD_5
, B.PLAN_OWNER_IND
, B.RECYC_CTR
, B.RECYC_CTR_X
, B.EXCLUDE_PER_USER
, B.EXCLUDE_PER_BLUECD
, B.PRODUCT_CD
, B.PROV_NETWORK_ID
, B.PROV_NUMBER_SFX
, B.PCP_EFFECT_DATE
, B.PCP_EFFECT_DATE_X
, B.COV_START_DATE
, B.COV_START_DATE_X
, B.COV_END_DATE
, B.COV_END_DATE_X
, B.ACTIVITY_IND
, B.ACTIVITY_DATE
, B.ACTIVITY_DATE_X
, B.MEF_STAT_CD
, B.MEF_DISC_LVL_IND
, B.MEF_DISC_CD_1
, B.MEF_DISC_CD_2
, B.MEF_DISC_CD_3
, B.MEF_DISC_CD_4
, B.MEF_DISC_CD_5
, B.ECR_IND
, B.MEF_RCPT_DT
, B.MEF_RCPT_DT_X
FROM IPTFM81A B
WHERE B.CDBKXX_SERNUM = :CDBKXX-SERNUM-A AND
B.CDBKXX_TRANQUAL = :CDBKXX-TRANQUAL-A AND
B.CDBKXX_SEQNUM = :CDBKXX-SEQNUM-A
FETCH FIRST 1 ROW ONLY
END-EXEC.

Performance Stats : 678K Excp Counts & 13.06 mins.

satya_reddy · Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3

Option 3:

EXEC SQL DECLARE NFOPEN1 CURSOR FOR
SELECT
CDBKXX_SERNUM
, CDBKXX_TRANQUAL
, CDBKXX_SEQNUM
, ORGN_PLAN_CD
, DEST_PLAN_CD
, TYPE_REQ
, TRANSM_MODE_CD
, RSN_CD
, SRC_CD
, ACT_CD
, CLERK_NUM
, REL_NUM
, CLM_TYPE
, CREATE_DT
, CREATE_DT_X
, ORGN_PLAN_STA_CD
, DEST_PLAN_STA_CD
, CASH_REFND_IND
, CASH_REFND_AMT
, CASH_REFND_AMT_X
, ADJ_SF_IND
, XREF_SCCF_NUM
, PREV_XREF_SCCF_NUM
, TOT_CHRG
, TOT_CHRG_X
, OPN_CLO_IND
, FMT_DB_POST_DT
, FMT_DB_POST_DT_X
, RESUB_SF_IND
, RESUB_DF_IND
, HIGH_SEQ_NUM
, HIGH_SEQ_NUM_X
, STAT_CD
, TRANS_IND
, DISP_CD
, ERR_CD_1
, ERR_CD_2
, ERR_CD_3
, ERR_CD_4
, ERR_CD_5
, PLAN_OWNER_IND
, RECYC_CTR
, RECYC_CTR_X
, EXCLUDE_PER_USER
, EXCLUDE_PER_BLUECD
, PRODUCT_CD
, PROV_NETWORK_ID
, PROV_NUMBER_SFX
, PCP_EFFECT_DATE
, PCP_EFFECT_DATE_X
, COV_START_DATE
, COV_START_DATE_X
, COV_END_DATE
, COV_END_DATE_X
, ACTIVITY_IND
, ACTIVITY_DATE
, ACTIVITY_DATE_X
, MEF_STAT_CD
, MEF_DISC_LVL_IND
, MEF_DISC_CD_1
, MEF_DISC_CD_2
, MEF_DISC_CD_3
, MEF_DISC_CD_4
, MEF_DISC_CD_5
, ECR_IND
, MEF_RCPT_DT
, MEF_RCPT_DT_X
FROM IPTFM81A
WHERE CDBKXX_SERNUM > :WS-SPACE2 AND
CDBKXX_SEQNUM > :WS-SPACE2 AND
CDBKXX_TRANQUAL > :WS-SPACE2 AND
FMT_DB_POST_DT >= :WS-DB2-CURRENTDT OR
OPN_CLO_IND = '1'
END-EXEC.

Only removed Order by
Performance Stats : 7879 Excp Counts & 0.10 mins.

satya_reddy · Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3

Can you advise or provide any suggestions for this performance resolution.

kolusu · Posted: Tue Nov 30, 2004 5:58 am Post subject:

satya_reddy,

Try coding OPTIMIZE for the cursor declaration and see if it makes any difference.

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/4.4.6.5?SHELF=&DT=20010710165542&CASE=

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

satya_reddy · Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3

Thanks Kosulu,

I did. It did not improve. DBA is on leave this week, and after he returns, I shall put before him the various options, and let him decide.

Regards,
Satyam..