Using DBINFO

If I run the following select tabid, tabname, hex(partnum) from systables where tabtype = 'T' order by 1 I get a nice list of all tables and their respective partnum ordered by tabid If I run the same query with , DBINFO('DBSPACE',partnum) added to the list of columns, I get 727: Invalid or NULL TBLspace number given to dbinfo(dbspace). If I do the same on a different DB on the same server it works perfectly Anybody got any clues? Regards Colin Dawson sending to informix-list

works OK here ... tabid 1 tabname systables (expression) 0x00100642 (expression) rootdbs tabid 2 tabname syscolumns (expression) 0x00100643 (expression) rootdbs tabid 3 tabname sysindices (expression) 0x001007C6 (expression) rootdbs .... and so on ....

Colin Dawson wrote: > If I run the following > > select tabid, tabname, hex(partnum) > from systables > where tabtype = 'T' > order by 1> > I get a nice list of all tables and their respective partnum ordered by > tabid > > If I run the same query with , DBINFO('DBSPACE',partnum) added to the list > of columns, > I get 727: Invalid or NULL TBLspace number given to dbinfo(dbspace). > > If I do the same on a different DB on the same server it works perfectly There are some fragmented tables in the database where it does not work. The solution is: select tabid, tabname, hex(partnum), DBINFO('DBSPACE',partnum) AS dbspace from systables where tabtype = 'T' and partnum != 0 UNION select st.tabid, st.tabname, hex(sf.partnum), sf.dbspace from systables st, sysfragments sf where st.tabtype = 'T' and st.partnum = 0 and st.tabid = sf.tabid and sf.fragtype = 'T' order by 1, 4, 3; The first part of the union retrieves the info you need from non-fragmented tables while the second query retrieves the info for the table fragments. Note that you will get multiple records for fragmented tables so I added the dbspace column to the ORDER BY. If you are running in IDS 10.00+ where you can have more than one fragment in the same dbspace you'll want to add partnum to the sort (as I did here). Also for 10.00, if you decide to drop the partnum, you'll want to change the UNION to UNION ALL so that the identical rows for multiple fragments in the same dbspace will not be eliminated by the UNION logic. Art S. Kagel > Anybody got any clues? > > > > Regards > > Colin Dawson > > > sending to informix-list

Colin Dawson wrote: OOPS, bug in my original see the updated second query below: > If I run the following > > select tabid, tabname, hex(partnum) > from systables > where tabtype = 'T' > order by 1> > I get a nice list of all tables and their respective partnum ordered by > tabid > > If I run the same query with , DBINFO('DBSPACE',partnum) added to the list > of columns, > I get 727: Invalid or NULL TBLspace number given to dbinfo(dbspace). > > If I do the same on a different DB on the same server it works perfectly There are some fragmented tables in the database where it does not work. The solution is: select tabid, tabname, hex(partnum), DBINFO('DBSPACE',partnum) AS dbspace from systables where tabtype = 'T' and partnum != 0 UNION select st.tabid, st.tabname, hex(sf.partn), sf.dbspace from systables st, sysfragments sf where st.tabtype = 'T' and st.partnum = 0 and st.tabid = sf.tabid and sf.fragtype = 'T' order by 1, 4, 3; The first part of the union retrieves the info you need from non-fragmented tables while the second query retrieves the info for the table fragments. Note that you will get multiple records for fragmented tables so I added the dbspace column to the ORDER BY. If you are running in IDS 10.00+ where you can have more than one fragment in the same dbspace you'll want to add partnum to the sort (as I did here). Also for 10.00, if you decide to drop the partnum, you'll want to change the UNION to UNION ALL so that the identical rows for multiple fragments in the same dbspace will not be eliminated by the UNION logic. Art S. Kagel > Anybody got any clues? > > > > Regards > > Colin Dawson > > > sending to informix-list