Finding Raw tables in database
Someone migrating a 4TB Informix 10 instance wanted to switch all tables to RAW logging for faster loads, then restore their original types, and asked how to identify which tables were already RAW. The answer: the table type is encoded in systables.flags — bit 0x00000010 indicates RAW on IDS (0x00000008 on XPS), with other table properties also packed into the same flags column. The poster confirmed a HEX(flags) LIKE pattern query against systables (tabid > 99, tabtype = 'T') gave the right results and reported the task working.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all I'm currently working on migrating a 4TB instance of informix 10.00 FC8, are there any system table that contains the type of a table (whether its raw or standard)? As I'm changing all the tables to raw mode for faster data insertions, and need to automate the process of changing them back to the type they were before. Thanks in advance. Regards Tien Cheng
Hi, RAW tables can be found with the flags in systables. The hexflag for RAW ta= bles is 0x00000010. Keep in mind that the other properties of the table might be added to that = flags. Gerd -----Urspr=C3=BCngliche Nachricht----- Von: TIEN-MING CHENG <tien.cheng@reagola.com> Gesendet: Jun 7, 2010 4:41:44 PM An: ids@iiug.org Betreff: Finding Raw tables in database [20300] >Hi all=20 > >I'm currently working on migrating a 4TB instance of informix 10.00 FC8, a= re=20 >there any system table that contains the type of a table (whether its raw = or=20 >standard)? As I'm changing all the tables to raw mode for faster data=20 >insertions, and need to automate the process of changing them back to the = type=20 >they were before. Thanks in advance.=20 > >Regards=20 >Tien Cheng=20 > > >**************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > ___________________________________________________________ WEB.DE DSL ab 19,99 Euro/Monat. Bis zu 150,- Euro Startguthaben und 50,- Euro Geldpr=C3=A4mie inklusive! https://freundschaftswerbung.web.de
It's encoded into the flag column in systables. If (systables.flags & 0x00000008) is TRUE the table is in RAW mode(for IDS, different flag value for XPS servers). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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, Jun 7, 2010 at 10:41 AM, TIEN-MING CHENG <tien.cheng@reagola.com>wrote: > Hi all > > I'm currently working on migrating a 4TB instance of informix 10.00 FC8, > are > there any system table that contains the type of a table (whether its raw > or > standard)? As I'm changing all the tables to raw mode for faster data > insertions, and need to automate the process of changing them back to the > type > they were before. Thanks in advance. > > Regards > Tien Cheng > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd70ed0ad1d980488749ca1
Hi Art and Gert Thanks for the prompt responses, started playing with it this morning again. I found that "select from systables where HEX(flags) like "_x______1_" and tabid > 99 and tabtype = 'T'" seemed to get me the right result. Does the 1 in that specific position in HEX determine that the table is set to raw mode? I just wanted to confirm whether my thoughts on it was correct. Thanks Regards Tien Cheng
Sorry Tien, I was reading my source code wrong. Gert is correct and your testing is correct. The flag for RAW tables in IDS is 0x00000010 and in XPS it is 0x00000008. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Tue, Jun 8, 2010 at 4:14 AM, TIEN-MING CHENG <tien.cheng@reagola.com>wrote: > Hi Art and Gert > > Thanks for the prompt responses, started playing with it this morning > again. I > found that > "select from systables > > where HEX(flags) like "_x______1_" > > and tabid > 99 > > and tabtype = 'T'" > seemed to get me the right result. Does the 1 in that specific position in > HEX > determine that the table is set to raw mode? I just wanted to confirm > whether > my thoughts on it was correct. Thanks > > Regards > Tien Cheng > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd70ed03f954604888309d3
Perfect, all working now, many thanks for your help Art and Gert. Regards Tien Cheng