list all the user tables
The poster asked how to list only the user tables in a database from systables, noting that user tables seem to have tabid >= 100. Jonathan Leffler (IBM) confirmed that filtering on tabid >= 100 is reasonably safe and has been since 1986, and that he knew of no cleaner method. The only caveat raised: with Enterprise Replication, internal cdr_deltab... tables also get tabids above 99, and opinions differed on whether those count as system or user tables (they're ER-created conflict-resolution tables best left alone). So the tabid >= 100 test stands, with that exception.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi there, I want to list all the user tables for a particular database. If I select the values from systables for that database, the only column values that looks different from the system tables and the user tables are TABID and VERSION. I noticed that TABIDs are always >= 100 for user tables. Can I use them to list the user tables?? Is there anyother clean way to do this? Sashi -- Sashi Parthasarathy Velara Software (352) 334-7235 sashi@velara.com
tabid >= 100 is fairly safe -- and has been since 1986. There isn't a cleaner way to do it that I know of. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management Solutions 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "Sashi Parth...."| | | <sashi@velara.com| | | > | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 02/06/2003 11:27 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: list all the user tables [277] | | | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| Hi there, I want to list all the user tables for a particular database. If I select the values from systables for that database, the only column values that looks different from the system tables and the user tables are TABID and VERSION. I noticed that TABIDs are always >= 100 for user tables. Can I use them to list the user tables?? Is there anyother clean way to do this? Sashi -- Sashi Parthasarathy Velara Software (352) 334-7235 sashi@velara.com
> Jonathan Leffler wrote > > tabid >= 100 is fairly safe -- and has been since 1986. > There isn't a cleaner way to do it that I know of. > If you are using ER, you sometimes get tables named cdr_deltab... with tabid > 99 ( at least you do in IDS 7.31 ) >> Sashi asked > I want to list all the user tables for a particular database. If I select > the values from systables for that database, the only column values that > looks different from the system tables and the user tables are TABID and > VERSION. I noticed that TABIDs are always >= 100 for user tables. > > Can I use them to list the user tables?? Is there anyother clean way to do > this? > > Colin Bull c.bull@VideoNetworks.com
Apart from Lotus being utterly confused - it claims that I sent the message sent by Colin... It is a moot point whether cdr_deltab... tables are system tables or not. I'd defer to Madison on that. I'd personally regard them as special user tables, rather as I did syscolval and syscolatt (UPSCOL and I4GL). But opinions could legitimately vary on that. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management Solutions 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "Colin Bull" | | | <c.bull@videonetw| | | orks.com> | | | Sent by: Jonathan| | | Leffler/Menlo | | | Park/IBM@IBMUS | | | | | | | | | 02/07/2003 12:31 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: RE: list all the user tables [291] | | | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| > Jonathan Leffler wrote > > tabid >= 100 is fairly safe -- and has been since 1986. > There isn't a cleaner way to do it that I know of. > If you are using ER, you sometimes get tables named cdr_deltab... with tabid > 99 ( at least you do in IDS 7.31 ) >> Sashi asked > I want to list all the user tables for a particular database. If I select > the values from systables for that database, the only column values that > looks different from the system tables and the user tables are TABID and > VERSION. I noticed that TABIDs are always >= 100 for user tables. > > Can I use them to list the user tables?? Is there anyother clean way to do > this? > > Colin Bull c.bull@VideoNetworks.com
I'd consider the delete tables (i.e. cdr_deltab....) as 'quasi-user' description. I guess that we would consider them similar to the tables in sysutils. The cdr_deltab tables could also be considered as extension of the user tables that are being replicated. M.P. Jonathan Leffler/Menlo To: ids@iiug.org Park/IBM@IBMUS cc: Sent by: Subject: RE: list all the user tables [298] forum.subscriber@ iiug.org 02/07/2003 12:03 PM Apart from Lotus being utterly confused - it claims that I sent the message sent by Colin... It is a moot point whether cdr_deltab... tables are system tables or not. I'd defer to Madison on that. I'd personally regard them as special user tables, rather as I did syscolval and syscolatt (UPSCOL and I4GL). But opinions could legitimately vary on that. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management Solutions 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "Colin Bull" | | | <c.bull@videonetw| | | orks.com> | | | Sent by: Jonathan| | | Leffler/Menlo | | | Park/IBM@IBMUS | | | | | | | | | 02/07/2003 12:31 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: RE: list all the user tables [291] | | | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| > Jonathan Leffler wrote > > tabid >= 100 is fairly safe -- and has been since 1986. > There isn't a cleaner way to do it that I know of. > If you are using ER, you sometimes get tables named cdr_deltab... with tabid > 99 ( at least you do in IDS 7.31 ) >> Sashi asked > I want to list all the user tables for a particular database. If I select > the values from systables for that database, the only column values that > looks different from the system tables and the user tables are TABID and > VERSION. I noticed that TABIDs are always >= 100 for user tables. > > Can I use them to list the user tables?? Is there anyother clean way to do > this? > > Colin Bull c.bull@VideoNetworks.com
Are these tables that can be readily unloaded, dropped, recreated, and reloaded? > -----Original Message----- > From: Jonathan Le.... [mailto:jleffler@us.ibm.com] > Sent: Friday, February 07, 2003 1:03 PM > To: ids@iiug.org > Subject: RE: list all the user tables [298] > > > > > > > Apart from Lotus being utterly confused - it claims that I > sent the message > sent by Colin... > > It is a moot point whether cdr_deltab... tables are system > tables or not. > I'd defer to Madison on that. I'd personally regard them as > special user > tables, rather as I did syscolval and syscolatt (UPSCOL and > I4GL). But > opinions could legitimately vary on that. > > -- > Jonathan Leffler (jleffler@us.ibm.com) > STSM, Informix Database Engineering, IBM Data Management Solutions > 4100 Bohannon Drive, Menlo Park, CA 94025 > Tel: +1 650-926-6921 Tie-Line: 630-6921 > "I don't suffer from insanity; I enjoy every minute of it!" > > > |---------+----------------------------> > | | "Colin Bull" | > | | <c.bull@videonetw| > | | orks.com> | > | | Sent by: Jonathan| > | | Leffler/Menlo | > | | Park/IBM@IBMUS | > | | | > | | | > | | 02/07/2003 12:31 | > | | AM | > | | | > |---------+----------------------------> > > >------------------------------------------------------------- > -------------------------------------------------------------- > ------------------| > | > > | > | To: ids@iiug.org > > | > | cc: > > | > | Subject: RE: list all the user tables [291] > > | > | > > | > | > > | > > >------------------------------------------------------------- > -------------------------------------------------------------- > ------------------| > > > > > > > Jonathan Leffler wrote > > > > tabid >= 100 is fairly safe -- and has been since 1986. > > There isn't a cleaner way to do it that I know of. > > > > If you are using ER, you sometimes get tables named cdr_deltab... > with tabid > 99 ( at least you do in IDS 7.31 ) > > >> Sashi asked > > I want to list all the user tables for a particular > database. If I select > > the values from systables for that database, the only > column values that > > looks different from the system tables and the user tables > are TABID and > > VERSION. I noticed that TABIDs are always >= 100 for user tables. > > > > Can I use them to list the user tables?? Is there anyother > clean way to > do > > this? > > > > > > > Colin Bull > c.bull@VideoNetworks.com > > > > > > "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."
John Carlson > Are these tables that can be readily unloaded, dropped, recreated, and > reloaded? > These are tables created by Enterprise replication. The data is used for conflict resolution. I would say that they are system tables because they are created by the system and the data in them is not available to the user. We just pretend they are not there most of the time and let them get on with their own business. If we manually hack ER, we will drop them if they get left behind but this is probably not a good idea on production systems ! > > > > > It is a moot point whether cdr_deltab... tables are system > > tables or not. > > I'd defer to Madison on that. I'd personally regard them as > > special user > > tables, rather as I did syscolval and syscolatt (UPSCOL and > > I4GL). But > > opinions could legitimately vary on that. > > > Jonathan Leffler wrote > > > > > > tabid >= 100 is fairly safe -- and has been since 1986. > > > There isn't a cleaner way to do it that I know of. > > > > > > > If you are using ER, you sometimes get tables named cdr_deltab... > > with tabid > 99 ( at least you do in IDS 7.31 ) > > > > >> Sashi asked > > > I want to list all the user tables for a particular > > database. If I select > > > the values from systables for that database, the only > > column values that > > > looks different from the system tables and the user tables > > are TABID and > > > VERSION. I noticed that TABIDs are always >= 100 for user tables. > > > > > > Can I use them to list the user tables?? Is there anyother > > clean way to > > do > > > this? > > > Colin Bull c.bull@VideoNetworks.com