MVSFORUMS.com A Community of and for MVS Professionals
View previous topic :: View next topic
Author
Message
satya_reddy Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3
Posted: Mon Nov 29, 2004 10:54 pm Post subject: DB2 Performance improvement
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.
Back to top
satya_reddy Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3
Posted: Mon Nov 29, 2004 10:55 pm Post subject:
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
Back to top
satya_reddy Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3
Posted: Mon Nov 29, 2004 10:56 pm Post subject:
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
Back to top
satya_reddy Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3
Posted: Mon Nov 29, 2004 10:56 pm Post subject:
Few options were tried today as listed below
Back to top
satya_reddy Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3
Posted: Mon Nov 29, 2004 10:58 pm Post subject:
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.
Back to top
satya_reddy Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3
Posted: Mon Nov 29, 2004 11:00 pm Post subject:
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.
Back to top
satya_reddy Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3
Posted: Mon Nov 29, 2004 11:01 pm Post subject:
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.
Back to top
satya_reddy Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3
Posted: Mon Nov 29, 2004 11:03 pm Post subject:
Can you advise or provide any suggestions for this performance resolution.
Back to top
kolusu Site Admin Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Back to top
satya_reddy Beginner Joined: 21 Sep 2004 Posts: 16 Topics: 3
Posted: Wed Dec 01, 2004 11:14 pm Post subject:
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..
Back to top
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