lock table on a fragmented table

Hi, I have some fragmented tables (by round robin and by expression) I realized that the sentence: lock table <table> in exclusive mode; locks only one fragment. For example: database sysmaster;select hex(a.ti_partnum) from systabinfo a, systabnames b where a.ti_partnum = b.partnum and b.dbsname = 'dwh' and b.tabname = 'facturacion' and ti_nrows > 0 0x01500002 0x01B00002 0x01C00002 0x01F00002 0x02200002 0x02400002 onstat -k | grep -i 1500002 onstat -k | grep -i 1B00002 (shows a record) onstat -k | grep -i 1C00002 onstat -k | grep -i 1F00002 onstat -k | grep -i 2200002 onstat -k | grep -i 2400002 Anyone know if this is true ? That means if the "lock table" only locks a fragment Thanks in advance. Roger

The partnum of the first fragment of a fragmented table is also the entire table's lockid, so locking this first partition is all that is required to lock the table. You can see that in the sysmaster:sysptnhdr records for all of the table's fragments and indexes, they all have the same lockid value which is also the partnum of the first partition/fragment. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Apr 8, 2013 at 12:23 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote: > Hi, > I have some fragmented tables (by round robin and by expression) > I realized that the sentence: > lock table <table> in exclusive mode; > locks only one fragment. > > For example: > database sysmaster;> select hex(a.ti_partnum) > from systabinfo a, systabnames b > where a.ti_partnum = b.partnum > and b.dbsname = 'dwh' > and b.tabname = 'facturacion' > and ti_nrows > 0 > > 0x01500002 > 0x01B00002 > 0x01C00002 > 0x01F00002 > 0x02200002 > 0x02400002 > > onstat -k | grep -i 1500002 > onstat -k | grep -i 1B00002 (shows a record) > onstat -k | grep -i 1C00002 > onstat -k | grep -i 1F00002 > onstat -k | grep -i 2200002 > onstat -k | grep -i 2400002> > Anyone know if this is true ? That means if the "lock table" only locks a > fragment > > Thanks in advance. > > Roger > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01160dde27cc4804d9dbedde

ids-bounces@iiug.org wrote on 04/08/2013 09:23:45 AM: > From: "ROGER VILCA" <rvilca@luzdelsur.com.pe> > To: ids@iiug.org, > Date: 04/08/2013 09:34 AM > Subject: lock table on a fragmented table [30003] > Sent by: ids-bounces@iiug.org > > Hi, > I have some fragmented tables (by round robin and by expression) > I realized that the sentence: > lock table <table> in exclusive mode; > locks only one fragment. This statement is not true. It only places a lock on a single partnumber (that is true), but the lock on a single partnum does lock the entire table. > > For example: > database sysmaster;> select hex(a.ti_partnum) > from systabinfo a, systabnames b > where a.ti_partnum = b.partnum > and b.dbsname = 'dwh' > and b.tabname = 'facturacion' > and ti_nrows > 0 > > 0x01500002 > 0x01B00002 > 0x01C00002 > 0x01F00002 > 0x02200002 > 0x02400002 > > onstat -k | grep -i 1500002 > onstat -k | grep -i 1B00002 (shows a record) > onstat -k | grep -i 1C00002 > onstat -k | grep -i 1F00002 > onstat -k | grep -i 2200002 > onstat -k | grep -i 2400002> > Anyone know if this is true ? That means if the "lock table" only locks a > fragment > > Thanks in advance. > > Roger > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

On 08/04/13 17:23, ROGER VILCA wrote: > Hi, > I have some fragmented tables (by round robin and by expression) > I realized that the sentence: > lock table <table> in exclusive mode; > locks only one fragment. > > For example: > database sysmaster;> select hex(a.ti_partnum) > from systabinfo a, systabnames b > where a.ti_partnum = b.partnum > and b.dbsname = 'dwh' > and b.tabname = 'facturacion' > and ti_nrows > 0 > > 0x01500002 > 0x01B00002 > 0x01C00002 > 0x01F00002 > 0x02200002 > 0x02400002 > > onstat -k | grep -i 1500002 > onstat -k | grep -i 1B00002 (shows a record) > onstat -k | grep -i 1C00002 > onstat -k | grep -i 1F00002 > onstat -k | grep -i 2200002 > onstat -k | grep -i 2400002> > Anyone know if this is true ? That means if the "lock table" only locks a > fragment > > Thanks in advance. > > Roger Do an oncheck -pt on a table, any table (eg this was from sysmaster:systables, so not the best example) ... Number of rows 245 Partition partnum 1049509 Partition lockid 1049509 ... you will notice that each fragment has it's own partnum, but all will share a partition lockid, whil will probably be the partnum of the first fragment in a table. Now do lock a couple of tables and do an onstat -k. Notice how all the partnums for table locks that you see are always partition lockids. By extrapolation - there is a special partnum for each table which when locked means that the whole table is locked, not just a fragment. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Thanks Art, John, Marco. Regards, Roger