duplicate tabname in systabnames
A user rebuilt a table (create new, copy rows, rename old, rename new to old name) on IDS 10.00.FC3R1 and then found two rows in sysmaster:systabnames with the same dbsname/owner/tabname but different partnums — one with 22.9M rows and 1 extent, another with 0 rows and 130 extents. Fernando Nunes suspected the zero-row entry was an index partition and asked for the full sysptnhdr columns (nkeys=1, npdata=0) and a lookup of the lockid partnum. The poster confirmed it: one entry was the table, the other an index that happened to share the same name.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I rebuild a table by - create a new table - insert into new table select from old table - rename old table name - rename new table name to old table name then I issue a SQL statemnet : select systabnames.*, nextns num_extents, sysptnhdr.nrows num_rows from sysptnhdr, systabnames where systabnames.partnum = sysptnhdr.partnum and systabnames.tabname = "past_exam_report" and systabnames.dbsname= "hispast" <output> partnum 3145770 dbsname hispast owner informix tabname past_exam_report collate zh_TW.57352 num_extents 1 num_rows 22903571 partnum 3145806 dbsname hispast owner informix tabname past_exam_report collate zh_TW.57352 num_extents 130 num_rows 0 there are two partnum and one identical table-name . Anyone can explain why ?
roger@star2000.com.tw wrote: > I rebuild a table by > - create a new table > - insert into new table select from old table > - rename old table name > - rename new table name to old table name > > then I issue a SQL statemnet : > select > systabnames.*, > nextns num_extents, > sysptnhdr.nrows num_rows > from > sysptnhdr, systabnames > where > systabnames.partnum = sysptnhdr.partnum > and systabnames.tabname = "past_exam_report" > and systabnames.dbsname= "hispast" > <output> > partnum 3145770 > dbsname hispast > owner informix > tabname past_exam_report > collate zh_TW.57352 > num_extents 1 > num_rows 22903571 > > partnum 3145806 > dbsname hispast > owner informix > tabname past_exam_report > collate zh_TW.57352 > num_extents 130 > num_rows 0 > > there are two partnum and one identical table-name . > Anyone can explain why ? > Attached index... num_rows=0... but if you send us the rest of the columns in sysptnhdr it will be helpful. Version 7, 9...? Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
On 3月8日, 上午4時58分, Fernando Nunes <s...@onlinedomus.net> wrote: > ro...@star2000.com.tw wrote: > > I rebuild a table by > > - create a new table > > - insert into new table select from old table > > - rename old table name > > - rename new table name to old table name > > > then I issue a SQL statemnet : > > select > > systabnames.*, > > nextns num_extents, > > sysptnhdr.nrows num_rows > > from > > sysptnhdr, systabnames > > where > > systabnames.partnum = sysptnhdr.partnum > > and systabnames.tabname = "past_exam_report" > > and systabnames.dbsname= "hispast" > > <output> > > partnum 3145770 > > dbsname hispast > > owner informix > > tabname past_exam_report > > collate zh_TW.57352 > > num_extents 1 > > num_rows 22903571 > > > partnum 3145806 > > dbsname hispast > > owner informix > > tabname past_exam_report > > collate zh_TW.57352 > > num_extents 130 > > num_rows 0 > > > there are two partnum and one identical table-name . > > Anyone can explain why ? > > Attached index... num_rows=0... but if you send us the rest of the columns in > sysptnhdr it will be helpful. > Version 7, 9...? > Regards. > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... ids version is 10.00.FC3R1. the rest columns of sysptnhdr list below: select systabnames.*, sysptnhdr.* from sysptnhdr, systabnames where systabnames.partnum = sysptnhdr.partnum and systabnames.tabname = "past_exam_report" and systabnames.dbsname= “hispast” <output> (first row) partnum 3145770 dbsname hispast owner informix tabname past_exam_report collate zh_TW.57352 partnum 3145770 flags 2050 rowsize 144 ncols 0 nkeys 0 nextns 1 pagesize 4096 created 1204697936 serialv 1 fextsiz 401587 nextsiz 401587 nptotal 1204761 npused 848386 npdata 848281 octptnm -1 lockid 3145770 nrows 22903571 (second row) partnum 3145806 dbsname hispast owner informix tabname past_exam_report collate zh_TW.57352 partnum 3145806 flags 2049 rowsize 144 ncols 0 nkeys 1 nextns 130 pagesize 4096 created 1169711208 serialv 1 fextsiz 4 nextsiz 1024 nptotal 75700 npused 75389 npdata 0 octptnm -1 lockid 3145743 nrows 0
roger@star2000.com.tw wrote:
> On 3?8?, ??4?58?, Fernando Nunes <s...@onlinedomus.net> wrote:
>> ro...@star2000.com.tw wrote:
>>> I rebuild a table by
>>> - create a new table
>>> - insert into new table select from old table
>>> - rename old table name
>>> - rename new table name to old table name
>>> then I issue a SQL statemnet :
>>> select
>>> systabnames.*,
>>> nextns num_extents,
>>> sysptnhdr.nrows num_rows
>>> from
>>> sysptnhdr, systabnames
>>> where
>>> systabnames.partnum = sysptnhdr.partnum
>>> and systabnames.tabname = "past_exam_report"
>>> and systabnames.dbsname= "hispast"
>>> <output>
>>> partnum 3145770
>>> dbsname hispast
>>> owner informix
>>> tabname past_exam_report
>>> collate zh_TW.57352
>>> num_extents 1
>>> num_rows 22903571
>>> partnum 3145806
>>> dbsname hispast
>>> owner informix
>>> tabname past_exam_report
>>> collate zh_TW.57352
>>> num_extents 130
>>> num_rows 0
>>> there are two partnum and one identical table-name .
>>> Anyone can explain why ?
>> Attached index... num_rows=0... but if you send us the rest of the columns in
>> sysptnhdr it will be helpful.
>> Version 7, 9...?
>> Regards.
>>
>> --
>> Fernando Nunes
>> Portugal
>>
>> http://informix-technology.blogspot.com
>> My email works... but I don't check it frequently...
>
> ids version is 10.00.FC3R1.
> the rest columns of sysptnhdr list below:
>
> select
> systabnames.*,
> sysptnhdr.*
> from
> sysptnhdr, systabnames
> where
> systabnames.partnum = sysptnhdr.partnum
> and systabnames.tabname = "past_exam_report"
> and systabnames.dbsname= 'hispast'
> <output>
> (first row)
> partnum 3145770
> dbsname hispast
> owner informix
> tabname past_exam_report
> collate zh_TW.57352
> partnum 3145770
> flags 2050
> rowsize 144
> ncols 0
> nkeys 0
> nextns 1
> pagesize 4096
> created 1204697936
> serialv 1
> fextsiz 401587
> nextsiz 401587
> nptotal 1204761
> npused 848386
> npdata 848281
> octptnm -1
> lockid 3145770
> nrows 22903571
> (second row)
> partnum 3145806
> dbsname hispast
> owner informix
> tabname past_exam_report
> collate zh_TW.57352
> partnum 3145806
> flags 2049
> rowsize 144
> ncols 0
> nkeys 1
> nextns 130
> pagesize 4096
> created 1169711208
> serialv 1
> fextsiz 4
> nextsiz 1024
> nptotal 75700
> npused 75389
> npdata 0
> octptnm -1
> lockid 3145743
> nrows 0
>
Can you check if you have an Index called past_exam_report?
And also:
SELECT *
FROM sysmaster:systabnames
WHERE partnum = 3145743;
please...?
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
Dear Fernando , Thank for your help! I found it .. One is index , the other is table !!