Hello, We have several large tables.But when we have large number of rows to update to final table the proc append using UNIQUESAVE=REP takes abnormal time. Below are the details of your questions. Can you tell me what is wrong in environment?Do we need to tune some SPDS options ? Here are the below answer How large is the target table? Ans : table size is 15 GB. for example for one table. Got a proper index, unique? Ans : Yes we have unique index in the table No of transaction rows? Ans : todays transaction rows 1422835,total rows in the table :233799286 Is the overall performance well in the environment? Ans: we are facing abnormal real time when we have large number of rows to update. HW sizing, memory/core parameter settings in SPDS... Ans : HW information : We have 2 application server named bumblee and domino having below config: Below is the system configuration for the servers Bumblebee and Domino. For Bumblebee : System Model: IBM,8205-E6B Machine Serial Number: 1079CBP Processor Type: PowerPC_POWER7 Processor Implementation Mode: POWER 7 Processor Version: PV_7_Compat Number Of Processors: 16 Processor Clock Speed: 3550 MHz CPU Type: 64-bit Kernel Type: 64-bit LPAR Info: 3 Bumblebee PROD Memory Size: 98304 MB Good Memory Size: 98304 MB Platform Firmware level: AL720_090 Firmware Version: IBM,AL720_090 Console Login: enable Auto Restart: true Full Core: false Paging Space Information Total Paging Space: 98304MB Percent Used: 1% Volume Groups Information ============================================================================== Active VGs ============================================================================== utillocvg: PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION hdisk5 active 2047 0 00..00..00..00..00 hdisk6 active 399 0 00..00..00..00..00 hdisk7 active 4095 0 00..00..00..00..00 ============================================================================== sasworkvg: PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION For Domino : System Model: IBM,8205-E6B Machine Serial Number: 1079C9P Processor Type: PowerPC_POWER7 Processor Implementation Mode: POWER 7 Processor Version: PV_7_Compat Number Of Processors: 16 Processor Clock Speed: 3550 MHz CPU Type: 64-bit Kernel Type: 64-bit LPAR Info: 4 Domino PROD Memory Size: 98304 MB Good Memory Size: 98304 MB Platform Firmware level: AL720_090 Firmware Version: IBM,AL720_090 Console Login: enable Auto Restart: true Full Core: false Paging Space Information Total Paging Space: 98304MB Percent Used: 1% Volume Groups Information ============================================================================== Inactive VGs ============================================================================== altinst_rootvg ============================================================================== Active VGs ============================================================================== sasworkvg: PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION hdisk1 active 4095 0 00..00..00..00..00 hdisk2 active 399 0 00..00..00..00..00 hdisk3 active 4095 0 00..00..00..00..00 SPDS core paramenter setting : NOREVNUMUPDATE; SORTSIZE=4096M; STARSIZE=4096M; INDEX_SORTSIZE=2048M; GRPBYROWCACHE=4096M; BINBUFSIZE=128K; WORKPATH="/local/temp/spdswork/eip/work"; NOCOREFILE; SEQIOBUFMIN=512K; RANIOBUFMIN=4K; MAXWHTHREADS=64; MAXSEGRATIO=75; WHERECOSTING; RANDOMPLACEDPF; MINPARTSIZE=512M; TMPDOMAIN=SPDTEMP; NLSTRANSCODE; MINPORTNO=21100; MAXPORTNO=21999; WHEREAUDIT; WHAUDLEN=4096; SQLAUDLEN=4096; SQLOPTS="reset no_qrwenable printlog plljoin nostarjoin=0 magic=103 buffersize=67108864 sqlhimem _method details='stj$dfrpdw$'" ; IDLE_TIMEOUT=36000; FMTDOMAIN=FORMATS; FMTNAMENODE=localhost; FMTNAMEPORT=5180;
... View more