Re: onstat -g ses
} 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.
Assuming that you have one or more temporary dbspaces defined and that DBSPACETEMP onconfig
parameter points to them, you should be able to watch the sorting of the key/location
pairs. Run "onstat -t | more", looking for tablespaces that have a physaddr appropriate
for your temporary dbspace(s). These are the tables Online is using to perform the sort.
It starts off with a whole bunch of them, then goes through multiple merge phases until
only two temporary tables remain. The final merge then builds the index. Given that you
have multiple index fragments, you may end up with two temporary tables for each fragment,
rather than two for the whole index. You should be able to see the index being built as
well, by watching npdata or nprows increasing on the tablespace that identifies an index
fragment.
} 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
---------------End of Original Message-----------------
Mark Collins
mcollins@us.dhl.com
The problem lies in how easily and dangerously we forget that
manipulating things is not the same as understanding them.