Re: onstat -g ses
Don't know your specifics, however, we build a unique composite index
on a 55 million row fragmented table. The table resides in 8 dbspaces
and the index in 8 separate dbspaces. Two suggestions. Set
PDQPRIORITY very high when building the index. I actually don't know
if this helps, but it seems to for us. Also, TEMPDBSPACES we found to
crititical to fast index builds. We use 14 - 250 meg temp dbspaces.
When we use to have 8 temp dbspaces of 500 meg each, indexes for this
table took 6-7 hours. With the current configuration, the index for
this table builds in 45 minutes or less.
Hope this helps.
dalink@west.com wrote:
>I have been building an index on an integer field for the 36 hrs on a
>table with 63 million row which is fragmented round robin on 28
>fragments. The index is fragmented by expression on 28 dbspaces by
>expression mod(acct_num,28) = 0....
>I am trying to monitor its progress and have been using onstat -g ses
><sessid> but I can't find any documentation on the thread names and
>status. I see psortpro threads occasionally. I am running Online version
>7.22 UC2 on 8 processor AIX system with 1 gig of RAM. Any and all help is
>appreciated.
>Below is a listing of onstat -g ses:
>INFORMIX-OnLine Version 7.22.UC2 -- On-Line -- Up 1 days 11:30:50 --
>575632 Kbytes
>session #RSAM total used
>id user tty pid hostname threads memory memory
>17 informix - 37910 ibmv 19 598016 497528
>tid name rstcb flags curstk status
>80 sqlexec 6003d03c Y-BP--- 4396 6003d03c cond wait(opened_up)
>2279 xchg_1.0 6003d8ac Y-B---- 1116 6003d8ac cond wait(opened_up)
>2280 xchg_2.0 6003dce4 --B---- 1004 6003dce4 ready
>2281 xchg_3.0 6003e11c --B-R-- 1692 6003e11c running
>2282 xchg_3.1 6004249c --B-R-- 1692 6004249c running
>2283 xchg_3.2 6003e554 --B-R-- 1688 6003e554 ready
>2284 xchg_3.3 60041c2c --B-R-- 1692 60041c2c running
>2285 xchg_3.4 600428d4 --B-R-- 1036 600428d4 ready
>2286 xchg_3.5 60042d0c --B-R-- 1692 60042d0c running
>2287 xchg_3.6 60043144 --B-R-- 1692 60043144 ready
>2288 xchg_3.7 6004357c --B-R-- 1692 6004357c running
>2289 xchg_3.8 6003fea4 --B-R-- 1692 6003fea4 running
>2290 xchg_3.9 600402dc --B-R-- 1692 600402dc running
>2291 xchg_3.1 6003f634 --B-R-- 1692 6003f634 ready
>2292 xchg_3.1 6003edc4 --B-R-- 1692 6003edc4 ready
>2293 xchg_3.1 6003fa6c --B-R-- 1692 6003fa6c ready
>2294 xchg_3.1 6003e98c --B-R-- 1036 6003e98c ready
>2295 xchg_3.1 60044224 --B-R-- 1692 60044224 ready
>2376 psortpro 60040f84 --B---- 1100 60040f84 ready
>Memory pools count 2
>name class addr totalsize freesize #allocfrag #freefrag
>17 V 60432014 376832 49932 993 8
>17_SORT_0 V 604a4014 221184 50408 106 7
>name free used name free used
>overhead 0 216 scb 0 80
>opentable 0 86872 filetable 0 13000
>misc 0 9936 log 0 79420
>temprec 0 14016 ralloc 0 56528
>gentcb 0 16708 ostcb 0 2200
>sort 0 33272 sqscb 0 8152
>srtmembuf 0 134740 rdahead 0 820
>xchg_desc 0 1116 xchg_port 0 628
>xchg_packet 0 688 xchg_group 0 192
>xchg_priv 0 748 scan_desc 0 7244
>sort_desc 0 824 btmrg_desc 0 312
>hashfiletab 0 5244 osenv 0 1356
>sqtcb 0 14180 fragman 0 2472
>light_scan 0 960 lt_scan_rbuff 0 1860
>lt_scan_bufs 0 600 shmblklist 0 2996
>Sess SQL Current Iso Lock SQL ISAM F.E.
>Id Stmt type Database Lvl Mode ERR ERR Vers
>17 CREATE INDEX class CR Not Wait 0 0 7.22
>Current SQL statement : create index "informix".dtv_tran_act_ix1 on
>"informix".dtv_subscrib_trans (acct_num) fragment by expression
>(mod(acct_num , 28 ) = 0 ) in dbindex1 , (mod(acct_num , 28 ) = 1 ) in
>dbindex2 , (mod(acct_num , 28 ) = 2 ) in dbindex3 , (mod(acct_num , 28
>) = 3 ) in dbindex4 , (mod(acct_num , 28 ) = 4 ) in dbindex5 ,
>(mod(acct_num , 28 ) = 5 ) in dbindex6 , (mod(acct_num , 28 ) = 6 ) in
>dbindex7 , (mod(acct_num , 28 ) = 7 ) in dbhot1 , (mod(acct_num , 28 )
>= 8 ) in dbhot2 , (mod(acct_num , 28 ) = 9 ) in dbhot3 , (mod(acct_num
>, 28 ) = 10 ) in dbhot4 , (mod(acct_num , 28 ) = 11 ) in dbhot5 ,
>(mod(acct_num , 28 ) = 12 ) in dbhot6 , (mod(acct_num , 28 ) = 13 ) in
>dbhot7 , (mod(acct_num , 28 ) = 14 ) in dbwarm1 , (mod(acct_num , 28 )
>= 15 ) in dbwarm2 , (mod(acct_num , 28 ) = 16 ) in dbwarm3 ,
>(mod(acct_num , 28 ) = 17 ) in dbwarm4 , (mod(acct_num , 28 ) = 18 ) in
>dbwarm5 , (mod(acct_num , 28 ) = 19 ) in dbwarm6 , (mod(acct_num , 28 )
>= 20 ) in dbwarm7 , (mod(acct_num , 28 ) = 21 ) in dbcold1 ,
>(mod(acct_num , 28 ) = 22 ) in dbcold2 , (mod(acct_num , 28 ) = 23 ) in
>dbcold3 , (mod(acct_num , 28 ) = 24 ) in dbcold4 , (mod(acct_num , 28
>) = 25 ) in dbcold5 , (mod(acct_num , 28 ) = 26 ) in dbcold6 ,
>(mod(acct_num , 28 ) = 27 ) in dbcold7
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Barry Leb
National Linen Service
1420 Peachtree Street
MS #314
Atlanta, GA 30309
(404) 853-6119
(404) 853-6485 fax
e-mail: barryleb@mindspring.com