Re: rowids and logical logs and fragmented tables
On Feb 15, 10:53 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > the_duke_of_hazzard wrote: > > I searched for this in the mailing list with no joy. > > > BACKGROUND > > ============ > > > We have a script which parses logical logs and produces a text output > > of the transactions in parallel, so that transaction behaviour can be > > viewed synchronically for debugging purposes. Handy for long-held > > transactions blocking others, deadlocks etc.. > > > The information logged includes the table name (grokked from the > > systable info) and the rowid as reported by the logs when relevant, eg > > for HUPDATE here: > > >http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.ad... > > > We then use the rowid to track down the row we are interested in. > > > PROBLEM > > ========= > > > The problem we are having is that the rowids reported by the logs > > obviously don't exist in fragmented tables (that have no rowids) - > > fine. > > > So how do we map the "rowids" reported by the logical log to the rows > > in the db? I've not been able to find a way. > > > TIA, > > > Ian > > I'm writing without searching... Not even in the FM (fabulous...). > But I would say that what you get from the logs is a partition ID and a row ID. > A fragmented table per si has no rowids, but it is built of several partitions > and each one has a rowid (and different partitions will have different table > rows with the same rowid, which is why the table itself has no rowids). > To obtain the table partnum you can see the sysmaster:syspthdr.lockid field. > > Please check carefully before use! :) > > By the way... what you're doing is not supported (in the sense that logical > logs structure is subject to change without notice etc. bla.. bla.. bla..) > > In 11.50.xC3 IBM introduced a little thing called Change Data Capture, > something that allows us to interface with Data Mirror (one of the companies > bought by IBM). It allows you to extract info from the logical logs using a > published API. The feature is new, and hopefully will be further enhanced in a > near future. Keep an eye on it... > > Regards. > > Regards. > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... Thanks for this. Our scripts already extract the partnum (which is how we get the tablename from th LL's) - I just wondered if there was a way to map the "rowid" to the row. It seems not... so we have to rely on other circumstantial info to figure out what might have been affected. Regards, Ian
On Feb 18, 3:04 pm, the_duke_of_hazzard <ian.mi...@gmail.com> wrote: > On Feb 15, 10:53 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > > > > > the_duke_of_hazzard wrote: > > > I searched for this in the mailing list with no joy. > > > > BACKGROUND > > > ============ > > > > We have a script which parses logical logs and produces a text output > > > of the transactions in parallel, so that transaction behaviour can be > > > viewed synchronically for debugging purposes. Handy for long-held > > > transactions blocking others, deadlocks etc.. > > > > The information logged includes the table name (grokked from the > > > systable info) and the rowid as reported by the logs when relevant, eg > > > for HUPDATE here: > > > >http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.ad... > > > > We then use the rowid to track down the row we are interested in. > > > > PROBLEM > > > ========= > > > > The problem we are having is that the rowids reported by the logs > > > obviously don't exist in fragmented tables (that have no rowids) - > > > fine. > > > > So how do we map the "rowids" reported by the logical log to the rows > > > in the db? I've not been able to find a way. > > > > TIA, > > > > Ian > > > I'm writing without searching... Not even in the FM (fabulous...). > > But I would say that what you get from the logs is a partition ID and a row ID. > > A fragmented table per si has no rowids, but it is built of several partitions > > and each one has a rowid (and different partitions will have different table > > rows with the same rowid, which is why the table itself has no rowids). > > To obtain the table partnum you can see the sysmaster:syspthdr.lockid field. > > > Please check carefully before use! :) > > > By the way... what you're doing is not supported (in the sense that logical > > logs structure is subject to change without notice etc. bla.. bla.. bla..) > > > In 11.50.xC3 IBM introduced a little thing called Change Data Capture, > > something that allows us to interface with Data Mirror (one of the companies > > bought by IBM). It allows you to extract info from the logical logs using a > > published API. The feature is new, and hopefully will be further enhanced in a > > near future. Keep an eye on it... > > > Regards. > > > Regards. > > > -- > > Fernando Nunes > > Portugal > > >http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > Thanks for this. Our scripts already extract the partnum (which is how > we get the tablename from th LL's) - I just wondered if there was a > way to map the "rowid" to the row. > It seems not... so we have to rely on other circumstantial info to > figure out what might have been affected. > Regards, > Ian Fernando is right...if the data is fragmented, we need to have a unique "pointer" to get there from an index. It's the combination of "fragment id | rowid" that will point to the row on the data page. Fragmented tables do have rowids associated with them - in the format of 0xLLLLLSS, where L = logical page in the partition, and S = slot number on the page (starting with 1, the engine takes slot 0 for the pg-trailing timestamp). But - since each fragment is it's own partition under the covers, you would have duplicate rowids if the fragment ID (same as partnum for the fragment) was added to the rowid to form the 8-byte unique "address." Tables have never "had" rowids - a common misconception. The rowid only physically exists in one or more indexes for the table, if it exists at all. (OK - you CAN do "select *,rowid from skippy" and magically the rowid value comes with. It's just being derived from the slot table on the page.) Ironically - when you "add rowids" to a fragmented table, nothing changes with the "fragment ID | rowid" combination - we just add a serial value to the front of the row. Ah - do I ever miss IDS internals stuff...! HTH - Mark Scranton Xtivia Inc.
On Feb 18, 2:49 pm, "Mark Scranton (Xtivia Inc.)" <mark.scran...@gmail.com> wrote: > On Feb 18, 3:04 pm, the_duke_of_hazzard <ian.mi...@gmail.com> wrote: > > > > > > > On Feb 15, 10:53 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > > > > the_duke_of_hazzard wrote: > > > > I searched for this in the mailing list with no joy. > > > > > BACKGROUND > > > > ============ > > > > > We have a script which parses logical logs and produces a text output > > > > of the transactions in parallel, so that transaction behaviour can be > > > > viewed synchronically for debugging purposes. Handy for long-held > > > > transactions blocking others, deadlocks etc.. > > > > > The information logged includes the table name (grokked from the > > > > systable info) and the rowid as reported by the logs when relevant, eg > > > > for HUPDATE here: > > > > >http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.ad... > > > > > We then use the rowid to track down the row we are interested in. > > > > > PROBLEM > > > > ========= > > > > > The problem we are having is that the rowids reported by the logs > > > > obviously don't exist in fragmented tables (that have no rowids) - > > > > fine. > > > > > So how do we map the "rowids" reported by the logical log to the rows > > > > in the db? I've not been able to find a way. > > > > > TIA, > > > > > Ian > > > > I'm writing without searching... Not even in the FM (fabulous...). > > > But I would say that what you get from the logs is a partition ID and a row ID. > > > A fragmented table per si has no rowids, but it is built of several partitions > > > and each one has a rowid (and different partitions will have different table > > > rows with the same rowid, which is why the table itself has no rowids). > > > To obtain the table partnum you can see the sysmaster:syspthdr.lockid field. > > > > Please check carefully before use! :) > > > > By the way... what you're doing is not supported (in the sense that logical > > > logs structure is subject to change without notice etc. bla.. bla.. bla..) > > > > In 11.50.xC3 IBM introduced a little thing called Change Data Capture, > > > something that allows us to interface with Data Mirror (one of the companies > > > bought by IBM). It allows you to extract info from the logical logs using a > > > published API. The feature is new, and hopefully will be further enhanced in a > > > near future. Keep an eye on it... > > > > Regards. > > > > Regards. > > > > -- > > > Fernando Nunes > > > Portugal > > > >http://informix-technology.blogspot.com > > > My email works... but I don't check it frequently... > > > Thanks for this. Our scripts already extract the partnum (which is how > > we get the tablename from th LL's) - I just wondered if there was a > > way to map the "rowid" to the row. > > It seems not... so we have to rely on other circumstantial info to > > figure out what might have been affected. > > Regards, > > Ian > > Fernando is right...if the data is fragmented, we need to have a > unique "pointer" to get there from an index. It's the combination of > "fragment id | rowid" that will point to the row on the data page. > Fragmented tables do have rowids associated with them - in the format > of 0xLLLLLSS, where L = logical page in the partition, and S = slot > number on the page (starting with 1, the engine takes slot 0 for the > pg-trailing timestamp). But - since each fragment is it's own > partition under the covers, you would have duplicate rowids if the > fragment ID (same as partnum for the fragment) was added to the rowid > to form the 8-byte unique "address." > > Tables have never "had" rowids - a common misconception. The rowid > only physically exists in one or more indexes for the table, if it > exists at all. (OK - you CAN do "select *,rowid from skippy" and > magically the rowid value comes with. It's just being derived from the > slot table on the page.) > > Ironically - when you "add rowids" to a fragmented table, nothing > changes with the "fragment ID | rowid" combination - we just add a > serial value to the front of the row. > > Ah - do I ever miss IDS internals stuff...! > HTH - > Mark Scranton > Xtivia Inc.- Hide quoted text - > > - Show quoted text - Oh it's nice to see "Skippy" make a return..... ;-)
On Feb 18, 7:43 pm, John <jgleip...@gmail.com> wrote: > On Feb 18, 2:49 pm, "Mark Scranton (Xtivia Inc.)" > > > > <mark.scran...@gmail.com> wrote: > > On Feb 18, 3:04 pm, the_duke_of_hazzard <ian.mi...@gmail.com> wrote: > > > > On Feb 15, 10:53 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > > > > > the_duke_of_hazzard wrote: > > > > > I searched for this in the mailing list with no joy. > > > > > > BACKGROUND > > > > > ============ > > > > > > We have a script which parses logical logs and produces a text output > > > > > of the transactions in parallel, so that transaction behaviour can be > > > > > viewed synchronically for debugging purposes. Handy for long-held > > > > > transactions blocking others, deadlocks etc.. > > > > > > The information logged includes the table name (grokked from the > > > > > systable info) and the rowid as reported by the logs when relevant, eg > > > > > for HUPDATE here: > > > > > >http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.ad... > > > > > > We then use the rowid to track down the row we are interested in. > > > > > > PROBLEM > > > > > ========= > > > > > > The problem we are having is that the rowids reported by the logs > > > > > obviously don't exist in fragmented tables (that have no rowids) - > > > > > fine. > > > > > > So how do we map the "rowids" reported by the logical log to the rows > > > > > in the db? I've not been able to find a way. > > > > > > TIA, > > > > > > Ian > > > > > I'm writing without searching... Not even in the FM (fabulous...). > > > > But I would say that what you get from the logs is a partition ID and a row ID. > > > > A fragmented table per si has no rowids, but it is built of several partitions > > > > and each one has a rowid (and different partitions will have different table > > > > rows with the same rowid, which is why the table itself has no rowids). > > > > To obtain the table partnum you can see the sysmaster:syspthdr.lockid field. > > > > > Please check carefully before use! :) > > > > > By the way... what you're doing is not supported (in the sense that logical > > > > logs structure is subject to change without notice etc. bla.. bla.. bla..) > > > > > In 11.50.xC3 IBM introduced a little thing called Change Data Capture, > > > > something that allows us to interface with Data Mirror (one of the companies > > > > bought by IBM). It allows you to extract info from the logical logs using a > > > > published API. The feature is new, and hopefully will be further enhanced in a > > > > near future. Keep an eye on it... > > > > > Regards. > > > > > Regards. > > > > > -- > > > > Fernando Nunes > > > > Portugal > > > > >http://informix-technology.blogspot.com > > > > My email works... but I don't check it frequently... > > > > Thanks for this. Our scripts already extract the partnum (which is how > > > we get the tablename from th LL's) - I just wondered if there was a > > > way to map the "rowid" to the row. > > > It seems not... so we have to rely on other circumstantial info to > > > figure out what might have been affected. > > > Regards, > > > Ian > > > Fernando is right...if the data is fragmented, we need to have a > > unique "pointer" to get there from an index. It's the combination of > > "fragment id | rowid" that will point to the row on the data page. > > Fragmented tables do have rowids associated with them - in the format > > of 0xLLLLLSS, where L = logical page in the partition, and S = slot > > number on the page (starting with 1, the engine takes slot 0 for the > > pg-trailing timestamp). But - since each fragment is it's own > > partition under the covers, you would have duplicate rowids if the > > fragment ID (same as partnum for the fragment) was added to the rowid > > to form the 8-byte unique "address." > > > Tables have never "had" rowids - a common misconception. The rowid > > only physically exists in one or more indexes for the table, if it > > exists at all. (OK - you CAN do "select *,rowid from skippy" and > > magically the rowid value comes with. It's just being derived from the > > slot table on the page.) > > > Ironically - when you "add rowids" to a fragmented table, nothing > > changes with the "fragment ID | rowid" combination - we just add a > > serial value to the front of the row. > > > Ah - do I ever miss IDS internals stuff...! > > HTH - > > Mark Scranton > > Xtivia Inc.- Hide quoted text - > > > - Show quoted text - > > Oh it's nice to see "Skippy" make a return..... ;-) Well yes....he's been hidden away in the barn in Indiana for some time...