SELECT RTRIM(ORD_MM.DEAL_NUMBER,' '),ORD_MM.AREA, ORD_MM.TRADE_DATE,
'),RTRIM(ORD_CS.CUST_NUMBER,' '),RTRIM(ORD_CS.NAME_FULL,' '),
DB2_DRCRLD.WSS_GDP_SITE, DB2_DRCRLD.WSS_COA_DATE, DB2_DRCRLD.WSS_TID,
DB2_DRCRLD.STATUS, DB2_DRCRLD.SEQUENCE_NO, DB2_DRCRLD.KEY_AMOUNT,
ORD_MM.WSS_TID, ORD_MM.EXT_TRAN_NO, ORD_MM.ORIG_NO, ORD_MM.REMARK_THREE,
ORDMM_ALIAS.DEAL_NUMBER FROM DB2_DRCRLD,ORD_AREA,ORD_MM,ORD_SI,ORD_CS,
ORD_NOSTRO, ORD_MM ORDMM_ALIAS WHERE DB2_DRCRLD.PRODUCT_CAT='MM' AND
ORD_AREA.AREA_CODE='DBGL' AND DB2_DRCRLD.DEAL_NUMBER = ORD_MM.DEAL_NUMBER
AND ORD_MM.AREA_INT_NO = DB2_DRCRLD.BOOKING_UNIT AND
ORD_MM.FSI_TID=ORD_SI.WSS_TID AND ORD_MM.PRODUCT IN
DB2_DRCRLD.WSS_COA_DATE=ORD_AREA.AREA_DATE AND FILE_NAME IS NULL
The script above runs every 15mins in our production enviroment. This works OK for what it is intended for. Every morning about 7:30am the database is shutdown for backup. After the database has been brought up this script is takes about 3 hours to return any data. After that thing are OK until the next shutdown.
I am hoping you db gurus will point me in the right direction to sort this out. Any suggestions welcomed
12-04-2002, 06:38 AM
You mean only the first time it takes 3 hrs and from next every 15 minutes its normal?
12-04-2002, 06:45 AM
No, I mean that the script is scheduled to run every 15 mins. The first time that the script runs is at 8.10am after a cold backup of the database has being taken. The fisrt run is taking a very long time to return any data. This morning it took 3hrs. After the first run, all the other runs return data very quickly ie between 3-10mins.
12-04-2002, 06:47 AM
Also how much data it has to process ?
12-04-2002, 06:52 AM
I have already looked at that. My question is why it does take so long after the database has been re-started?. It seems to be OK after some time.
I am thinking that it is taking too long for the database to come to optimum performance hence maybe some init.ora parameter needs tuning
12-04-2002, 06:53 AM
Are there any other scripts run at the same time in the morning but not during the day that could have an affect on this query?
Is the data content similar at each runtime?
12-04-2002, 06:56 AM
well database needs sort of warm up after a reboot thatīs why hot backups are recommended these days