Thank All for the info! The problem is resolved :-)
I like to share what I found eventually and how the root cause was traced
.
Step 1: It has a suspicious lock,
[informix@maggie ~]$ onstat -k
IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 03:02:05
-- 11778640 Kbytes
Locks
address wtlist owner lklist
type tblsnum rowid key#/bsiz
44657028 0 4fa14de8 0
HDR+S 100002 204 0
446572d0 0 4fa12228 0
S 100002 204 0
46fd8028 0 4fa15f68 0
S 100002 204 0
48498828 0 4fa13c68 0
S 100002 204 0
484988b0 0 4fa13c68 48498828
HDR+S 100002 201 0
484989c0 0 4fa110a8 0
S 100002 201 0
49959028 0 4fa14528 0
HDR+S 100002 205 0
7 active, 800000 total, 131072 hash buckets, 0 lock table overflows
Step 2: with tips from Lester, Art and Andreas, the following strange
Thread name "ds_head_sha" was caught,
************************ HERE is guy !!!
[informix@maggie ~]$ onstat -g ath | grep 4fa14528
75 510190b8 4fa14528 1 sleeping secs: 789
10cpu ds_head_sha
[informix@maggie ~]$ onstat -u | grep 4fa14528
4fa14528 ---P--- 36 informix - 0 0
1 0 0
[informix@maggie ~]$ onstat -g ses 36
IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 03:02:57
-- 11778640 Kbytes
[informix@maggie ~]$
Step 3: Who is guy ??
A shard collection name !!! ---- WOW
[informix@maggie info]$ cdr list shardCollection
Shard Collection:ds_head_sha Version:0 type:expression key:datatype_name
Version Column:version
Table:noaa:informix.ds_head_shard
g_nsofops datatype_name IN ('CORGNSS','CORDLYOBS','GOME')
g_ncdcops datatype_name IN ('ASCAT','GVAR_IMG','IASI')
g_ngdcops datatype_name IN ('NUCAPS_CCR', 'NUCAPS_EDR')
Last year, we used cdr utility and created the above shard collection
"ds_head_sha" .
Surprised the shard collection name was actually a live thread which had
been running there forever .... and holding lock for ever...
After I run the following command to delete the shard collection,
[informix@maggie info]$ cdr delete shardCollection ds_head_sha
The ds_head_sha thread is gone, and the dbexport job is able to
complete successfully .
Thanks
Frank
On Wed, Aug 3, 2016 at 2:46 PM, Andreas Legner <andreas.legner@de.ibm.com>
wrote:
> select hex(rowid), name from sysmaster:sysdatabases;
>
> Then match with those (0x)201, 204 and 205 rowids to be sure about the=20
> lock you want to examine.
>
> onstat -g ath | grep <lock=5Fowner> -> is it an sqlexec?
> onstat -u | grep <lock=5Fowner> -> session id
> onstat -g ses <session=5Fid>
>
> HTH
> Andreas
>
> From: "FRANK" <yunyaoqu@gmail.com>
> To: ids@iiug.org
> Date: 03.08.2016 19:49
> Subject: Re: dbexport : -425 - Database is currently op.... [37530]
> Sent by: ids-bounces@iiug.org
>
> fresh bounce again ....=20
>
> [informix@maggie ~]$ onstat -k=20
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up=20
> 00:00:57=20
> -- 11778640 Kbytes=20
> Locks=20
> address wtlist owner lklist=20
> type tblsnum rowid key#/bsiz=20
> 44657028 0 4fa14de8 0=20
> HDR+S 100002 204 0=20
> 446572d0 0 4fa12228 0=20
> S 100002 204 0=20
> 46fd8028 0 4fa15f68 0=20
> S 100002 204 0=20
> 48498828 0 4fa13c68 0=20
> S 100002 204 0=20
> 484988b0 0 4fa13c68 48498828=20
> HDR+S 100002 201 0=20
> 49959028 0 4fa14528 0=20
> HDR+S 100002 205 0=20
> 6 active, 800000 total, 131072 hash buckets, 0 lock table overflows=20
>
> [informix@maggie ~]$ dbexport noaa -ss=20
> -425 - Database is currently opened by another user.=20
> -107 - ISAM error: record is locked.=20
>
> On Wed, Aug 3, 2016 at 1:43 PM, FRANK <yunyaoqu@gmail.com> wrote:=20
>
> >=20
> >=20
> > Hi, Art,=20
> >=20
> >=20
> >=20
> > [informix@maggie ~]$ onstat -k=20
> > IBM Informix Dynamic Server Version 12.10.FC4W1XU -- Single-User -- Up=20
> > 02:08:16 -- 11778640 Kbytes=20
> > Locks=20
> > address wtlist owner lklist=20
> > type tblsnum rowid key#/bsiz=20
> > 49959028 0 4fa14528 0=20
> > HDR+S 100002 205 0=20
> > 1 active, 800000 total, 131072 hash buckets, 0 lock table overflows=20
> >=20
> >=20
> > [informix@maggie ~]$ dbexport noaa -ss=20
> > -425 - Database is currently opened by another user.=20
> > -107 - ISAM error: record is locked.=20
> >=20
> >=20
> >=20
> > Yes, bounced many times ... No change.... Very funny ....=20
> >=20
> >=20
> > Thanks=20
> > Frank=20
> >=20
> >=20
> > On Wed, Aug 3, 2016 at 1:32 PM, Art Kagel <art.kagel@gmail.com>
> wrote:=20
> >=20
> >> Does onstat -k show the lock? Did you try bouncing the instance?=20
> >>=20
> >> Art=20
> >>=20
> >> Art S. Kagel, President and Principal Consultant=20
> >> ASK Database Management=20
> >> www.askdbmgt.com=20
> >>=20
> >> Blog: http://informix-myview.blogspot.com/=20
> >>=20
> >> Disclaimer: Please keep in mind that my own opinions are my own=20
> opinions=20
> >> and do not reflect on the IIUG, nor any other organization with which I
> =
>
> am=20
> >> associated either explicitly, implicitly, or by inference. Neither do=20
> >> those opinions reflect those of other individuals affiliated with any=20
> >> entity with which I am affiliated nor those of the entities themselves.
> =
>
> >>=20
> >> On Wed, Aug 3, 2016 at 1:26 PM, FRANK <yunyaoqu@gmail.com> wrote:=20
> >>=20
> >> > Guys,=20
> >> >=20
> >> > Did numerous times of dbexport . in past 50 years, NO problem :-) :-)
> =
>
> >> >=20
> >> > But, now it blocked the job...=20
> >> >=20
> >> > It is sure , no application , no ER, no HDR, NO nothing running there
> =
>
> (=20
> >> > I even stopped scheduler ) ...=20
> >> >=20
> >> > Any tip to trace the " another user"?=20
> >> >=20
> >> > By the way, it is fine to do the same job on another database of
> the=20
> >> same=20
> >> > instance .=20
> >> >=20
> >> > Thanks=20
> >> > Frank=20
> >> >=20
> >> > [informix@maggie ~]$ dbexport noaa -ss=20
> >> > -425 - Database is currently opened by another user.=20
> >> > -107 - ISAM error: record is locked.=20
> >> > [informix@maggie ~]$ onstat -u=20
> >> > IBM Informix Dynamic Server Version 12.10.FC4W1XU -- Single-User --=20
> Up=20
> >> > 01:50:41 -- 11778640 Kbytes=20
> >> > Userthreads=20
> >> > address flags sessid user tty wait tout=20
> >> > locks nreads nwrites=20
> >> > 4fa05028 ---P--D 1 informix - 0 0=20
> >> > 0 1273 502=20
> >> > 4fa058e8 ---P--F 0 informix - 0 0=20
> >> > 0 0 328=20
> >> > 4fa061a8 ---P--F 0 informix - 0 0=20
> >> > 0 0 38=20
> >> > 4fa06a68 ---P--F 0 informix - 0 0=20
> >> > 0 0 35=20
> >> > 4fa07328 ---P--F 0 informix - 0 0=20
> >> > 0 0 36=20
> >> > 4fa07be8 ---P--F 0 informix - 0 0=20
> >> > 0 0 87=20
> >> > 4fa084a8 ---P--F 0 informix - 0 0=20
> >> > 0 0 11=20
> >> > 4fa08d68 ---P--F 0 informix - 0 0=20
> >> > 0 0 9=20
> >> > 4fa09628 ---P--F 0 informix - 0 0=20
> >> > 0 0 70=20
> >> > 4fa09ee8 ---P--F 0 informix - 0 0=20
> >> > 0 0 8=20
> >> > 4fa0a7a8 ---P--F 0 informix - 0 0=20
> >> > 0 0 4=20
> >> > 4fa0b068 ---P--F 0 informix - 0 0=20
> >> > 0 0 3=20
> >> > 4fa0b928 ---P--F 0 informix - 0 0=20
> >> > 0 0 3=20
> >> > 4fa0c1e8 ---P--F 0 informix - 0 0=20
> >> > 0 0 94=20
> >> > 4fa0caa8 ---P--F 0 informix - 0 0=20
> >> > 0 0 1=20
> >> > 4fa0d368 ---P--F 0 informix - 0 0=20
> >> > 0 0 1=20
> >> > 4fa0dc28 ---P--F 0 informix - 0 0=20
> >> > 0 0 1=20
> >> > 4fa0e4e8 ---P--- 16 informix - 0 0=20
> >> > 0 0 0=20
> >> > 4fa0eda8 ---P--B 17 informix - 0 0=20
> >> > 0 0 0=20
> >> > 4fa0f668 Y--P--B 18 informix - 50b84e18 0=20
> >> > 0 0 0=20
> >> > 4fa0ff28 Y--P--D 19 informix - 50e39de8 0=20
> >> > 0 9994 0=20
> >> > 4fa107e8 ---P--D 20 informix - 0 0=20
> >> > 0 0 0=20
> >> > 4fa11968 ---P--D 24 informix - 0 0=20
> >> > 0 0 0=20
> >> > 4fa12ae8 ---P--D 25 informix - 0 0=20
> >> > 0 3 0=20
> >> > 4fa133a8 ---P--D 26 informix - 0 0=20
> >> > 0 0 0=20
> >> > 4fa14528 ---P--- 36 informix - 0 0=20
> >> > 1 0 0=20
> >> > 4fa156a8 Y--P--D 35 informix - 44249c30 0=20
> >> > 0 0 0=20
> >> > 27 active, 128 total, 61 maximum concurrent=20
> >> >=20
> >> > [informix@maggie ~]$ onstat -g ses=20
> >> > IBM Informix Dynamic Server Version 12.10.FC4W1XU -- Single-User --=20
> Up=20
> >> > 01:51:48 -- 11778640 Kbytes=20
> >> > session #RSAM total used=20
> >> > dynamic=20
> >> > id user tty pid hostname threads memory memory=20
> >> > explain=20
> >> > 280 informix - 0 - 0 16384 12440=20
> >> > off=20
> >> > 264 informix - 0 - 0 16384 12440=20
> >> > off=20
> >> > 15 informix - 0 - 0 16384 12440=20
> >> > off=20
> >> > 13 informix - 0 - 0 16384 14032=20
> >> > off=20
> >> > 12 informix - 0 - 0 16384 14032=20
> >> > off=20
> >> > 11 informix - 0 - 0 16384 12440=20
> >> > off=20
> >> > 10 informix - 0 - 0 16384 12440=20
> >> > off=20
> >> > 8 informix - 0 - 0 16384 14032=20
> >> > off=20
> >> > 7 informix - 0 - 0 16384 14032=20
> >> > off=20
> >> > 6 informix - 0 - 0 16384 12440=20
> >> > off=20
> >> > 5 informix - 0 - 0 16384 12440=20
> >> > off=20
> >> > 4 informix - 0 - 0 16384 12440=20
> >> > off=20
> >> > 3 informix - 0 - 0 16384 12440=20
> >> > off=20
> >> > 2 informix - 0 - 0 16384 12440=20
> >> > off=20
> >> >=20
> >> > [informix@maggie ~]$ onstat -g ath=20
> >> > IBM Informix Dynamic Server Version 12.10.FC4W1XU -- Single-User --=20
> Up=20
> >> > 01:52:11 -- 11778640 Kbytes=20
> >> > Threads:=20
> >> > tid tcb rstcb prty status=20
> >> > vp-class name=20
> >> > 2 506d6028 0 1 IO Idle=20
> >> > 3lio* lio vp 0=20
> >> > 3 506ee368 0 1 IO Idle=20
> >> > 4pio* pio vp 0=20
> >> > 4 5070f368 0 1 IO Idle=20
> >> > 5aio* aio vp 0=20
> >> > 5 50730368 1cf9680 1 IO Idle=20
> >> > 6msc* msc vp 0=20
> >> > 6 50761368 0 1 IO Idle=20
> >> > 7fifo* fifo vp 0=20
> >> > 7 507a5a90 0 1 IO Idle=20
> >> > 13aio* aio vp 1=20
> >> > 8 507c6368 0 1 IO Idle=20
> >> > 14aio* aio vp 2=20
> >> > 9 507e7368 0 1 IO Idle=20
> >> > 15aio* aio vp 3=20
> >> > 10 50824608 4fa05028 3 sleeping secs: 1=20
> >> > 11cpu main=5Floop()=20
> >> > 11 5087e028 0 1 running=20
> >> > 20soc* soctcppoll=20
> >> > 12 5087e6b0 0 1 running=20
> >> > 21soc* soctcppoll=20
> >> > 13 508bb028 0 1 running=20
> >> > 22soc* soctcppoll=20
> >> > 14 508bb6b0 0 1 running=20
> >> > 23soc* soctcppoll=20
> >> > 15 508f8028 0 1 running=20
> >> > 1cpu* sm=5Fpoll=20
> >> > 16 50915958 0 2 sleeping forever=20
> >> > 1cpu* soctcplst=20
> >> > 17 50948370 0 2 sleeping forever=20
> >> > 1cpu sm=5Flisten=20
> >> > 18 50981028 0 1 sleeping secs: 1=20
> >> > 9cpu sm=5Fdiscon=20
> >> > 19 50996418 0 2 sleeping forever=20
> >> > 9cpu* soctcplst=20
> >> > 20 509aad00 0 2 sleeping forever=20
> >> > 10cpu* soctcplst=20
> >> > 21 509c47f0 4fa058e8 1 sleeping secs: 1=20
> >> > 11cpu flush=5Fsub(0)=20
> >> > 22 509c4b30 4fa061a8 1 sleeping secs: 1=20
> >> > 11cpu flush=5Fsub(1)=20
> >> > 23 509fe028 4fa06a68 1 sleeping secs: 1=20
> >> > 11cpu flush=5Fsub(2)=20
> >> > 24 509fe368 4fa07328 1 sleeping secs: 1=20
> >> > 9cpu flush=5Fsub(3)=20
> >> > 25 509fe6a8 4fa07be8 1 sleeping secs: 1=20
> >> > 9cpu flush=5Fsub(4)=20
> >> > 26 509fe9e8 4fa084a8 1 sleeping secs: 1=20
> >> > 11cpu flush=5Fsub(5)=20
> >> > 27 509fed28 4fa08d68 1 sleeping secs: 1=20
> >> > 11cpu flush=5Fsub(6)=20
> >> > 28 50a90028 4fa09628 1 sleeping secs: 1=20
> >> > 11cpu flush=5Fsub(7)=20
> >> > 29 50a90368 4fa09ee8 1 sleeping secs: 1=20
> >> > 9cpu flush=5Fsub(8)=20
> >> > 30 50a906a8 4fa0a7a8 1 sleeping secs: 1=20
> >> > 11cpu flush=5Fsub(9)=20
> >> > 31 50a909e8 4fa0b068 1 sleeping secs: 1=20
> >> > 9cpu flush=5Fsub(10)=20
> >> > 32 50a90d28 4fa0b928 1 sleeping secs: 1=20
> >> > 9cpu flush=5Fsub(11)=20
> >> > 33 50b2c028 4fa0c1e8 1 sleeping secs: 1=20
> >> > 9cpu flush=5Fsub(12)=20
> >> > 34 50b2c368 4fa0caa8 1 sleeping secs: 1=20
> >> > 11cpu flush=5Fsub(13)=20
> >> > 35 50b2c6a8 4fa0d368 1 sleeping secs: 1=20
> >> > 1cpu flush=5Fsub(14)=20
> >> > 36 50b2c9e8 4fa0dc28 1 sleeping secs: 1=20
> >> > 11cpu flush=5Fsub(15)=20
> >> > 37 50b2cd28 0 3 IO Idle=20
> >> > 10cpu* kaio=20
> >> > 38 50c45370 0 3 IO Idle=20
> >> > 9cpu* kaio=20
> >> > 39 50cf3b68 4fa0e4e8 2 sleeping secs: 1=20
> >> > 9cpu aslogflush=20
> >> > 40 50d9baa8 4fa0eda8 1 sleeping secs: 2=20
> >> > 11cpu btscanner=5F0=20
> >> > 41 50e39a00 4fa0f668 1 cond wait btc=5Fsort=20
> >> > 1cpu btscanner=5F1=20
> >> > 42 50e56aa8 4fa0ff28 3 cond wait ReadAhead=20
> >> > 10cpu readahead=5F0=20
> >> > 43 50e73bf8 4fa107e8 3 sleeping secs: 1=20
> >> > 9cpu auto=5Ftune=20
> >> > 59 511cc908 0 3 IO Idle=20
> >> > 1cpu* kaio=20
> >> > 60 511ccc48 0 3 IO Idle=20
> >> > 12cpu* kaio=20
> >> > 62 510ae380 4fa11968 3 sleeping secs: 1=20
> >> > 1cpu* onmode=5Fmon=20
> >> > 63 5119f380 4fa12ae8 3 sleeping secs: 1=20
> >> > 1cpu periodic=20
> >> > 64 5113e2c8 4fa133a8 3 sleeping forever=20
> >> > 9cpu* memory=20
> >> > 69 51106b08 0 3 IO Idle=20
> >> > 11cpu* kaio=20
> >> > 74 50f7f6b8 4fa156a8 1 cond wait bp=5Fcond=20
> >> > 9cpu bf=5Fpriosweep()=20
> >> > 75 51033b28 4fa14528 1 sleeping secs: 496=20
> >> > 1cpu ds=5Fhead=5Fsha=20
> >> >=20
> >> > --94eb2c04269cccaeac05392e2171=20
> >> >=20
> >> >=20
> >> >=20
> >> >=20
> >>=20
> >>=20
>
> ***************************************************************************=
> ****=20
>
> >> > Forum Note: Use "Reply" to post a response in the discussion forum.=20
> >> >=20
> >> >=20
> >>=20
> >> --001a113f8aae8442f305392e3821=20
> >>=20
> >>=20
> >>=20
> >>=20
>
> ***************************************************************************=
> ****=20
>
> >> Forum Note: Use "Reply" to post a response in the discussion forum.=20
> >>=20
> >>=20
> >=20
>
> --001a1144831edce8b005392e6a21=20
>
>
> ***************************************************************************=
> ****=20
>
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a114a7a6ccc8e710539326f9c
