Este artigo est� escrito em Ingl�s e Portugu�s
English version:
Introduction
The topic of in place ALTERed tables has always been present in Informix. We say a table was ALTERed in place when an ALTER TABLE instruction resulted in a new table schema, but the table was not physically changed. This is very helpful on large and busy tables. It means you can do the ALTER TABLE quickly (instantaneously) and with minimum immediate impact on system resources. Internally Informix does a very simple thing from the user point of view, but that can be complex from the engine point of view: A new version of the table definition is created, and from there on, any instruction (DML) affecting the data will use the new version. The table's existing pages are left on the older version but each time we SELECT them, the engine converts the row(s) to the new format. Additionally if we update a row, the page where it is stored will be written in the new format.
For example, if we have a table with lots of data pages, and we add a column, this will do an in place ALTER. Certain types of ALTERs don't allow in place ALTERs. For example, if we change a CHAR(5) column to a SMALLINT, this will be a slow ALTER. This usually happens when the system cannot immediately guarantee that the existing data can be stored in the new representation.
When the engine decides that it can do an in place ALTER, we don't have the option to inhibit it. Meaning the ALTER table will be done as an in place ALTER, and if we want to force the physical changes, we must do what we usually call a dummy UPDATE: UPDATE tabname SET column = column;
Impacts of in place ALTERs
There are several impacts on having tables with in place ALTERs. The immediate one is that your updates will be slightly slower, since the whole page will be converted and written. Note that this does not necessarily means higher I/O load, since the page is the smallest unit written by the engine. In other words, even if your table does not have more than one version of it's definition, when we change a single row of data, a whole page (at least) will be written. But the other rows of the page don't need to be changed. And changing an older version can mean your data will not fit on one page after conversion. So there can be more I/O.
Another potential issue is that like with any other part of the engine, the mechanism of in place ALTER can have issues. This is an overrated aspect, but it's true we've seen problems that only affect tables with in place ALTERs.
But the real issue with in place ALTERs refers to upgrades and downgrades. Through versions there have been great controversy regarding the real impact of doing in-place upgrades (conversions) with tables with pending in-place ALTERs. I've searched through the migration guides for several versions (7.3, 9.2, 9.4, 10, 11.1, 11.5 and 11.7) and they're almost completely consistent: You can upgrade with pending in-place ALTERed tables, but you cannot revert. The only exception I've found was the manual for 9.4 which states that you must remove the pending in-place ALTERs to run the conversion (upgrade) successfully.
The reason for allowing upgrades but not downgrades is pretty simple and acceptable: Informix guarantees that version N+1 can handle all previous situations of in-place ALTERs done in version N or older. But, since each version may have added new situations where in-place ALTERs can be done, we can't risk porting a pending ALTER to an older version that may not be able to handle it. Let me remind you that an in-place ALTER requires the ability to convert from one row version to a newer one (which can have more columns, or different data types etc.)
At the time of this writing I could not verify if the exception in the migration guide of version 9.4 was justified or simply a documentation problem. But the fact is that most people assume they must complete the ALTER tables that were done in-place before converting. By this I mean that they must eliminate all the pages in older versions. A valid reason to do that is that in case you need to revert you don't have to waste time running the dummy updates. Typically, if you have any issue in the new version and need to revert, you'll want to do it as soon as possible. As such, eliminating the pending inplace ALTERs before you upgrade can save you precious time if you really need to revert. In any case, the migration guide for version 11.7 clearly states that you only need to remove the pending inplace ALTERs, if they were caused after the conversion to 11.7. Any previous one (which already exists in the old version) would not need to be eliminated.
As a side note, let me state that the only time I've done reversions was during a customer and partner workshop where we were demoing this functionality... I never had to do it on a real customer situation. In any case the other limitations for reversion can be real challenges, so the pending in-place ALTERs wouldn't be your biggest concern.
Finding in-place alters
Now that we've seen in which situations we should remove pending inplace ALTERs (tables with pages in older versions), we come to another issue that is frequently asked, and to which there are several answers. Most of them are controversial or badly explained which again raises a lot of confusion. The issue is: How do we find tables with pending inplace ALTERs?
There was a discussion in the IIUG forum near the end of 2010 that focused on this issue. As usual, there were three answers to this:
- A quick way (based on SQL and SMI that tells us which tables suffered an inplace ALTER, but doesn't show which tables have pending inplace ALTERs. This means that unless you completely rebuild the table, just running dummy UPDATEs will not prevent the table from always appearing in the list generated by this method
- A slow way that's based in the oncheck -pT output (this effectively tells you the number of pages in each existing version This method will give you just the tables with pending inplace ALTERs
- A technical support tool that searches the tables metadata and can provide the answer pretty quickly. Only problem is that it's not generally available
And this was my motivation to do some research on this issue. After some information exchange with Andreas Legner from IBM technical support in Germany, I was able to create an SQL script that can find the tables with pending inplace alters. The script can give us the details about the number of pages in each version, it returns the database and table name, the version and number of pages it contains.
The great thing about this script is that it's fast (from a few seconds to a few minutes for very large databases) and it really shows you the current status. Contrary to what the option 1) above does, if you do the dummy updates on one table, that table will not show up if you run it again. One warning: If you test this, after the dummy update you need to force a checkpoint. The script goes through the partition headers (because the required info is stored there), and after the dummy updates the partition headers are written to disk only at checkpoint time.
The script comes in the form of a stored procedure and is based only on the sysmaster views. The script was tested with all the versions I could find (7.31, 9.3, 9.4, 10, 11.1, 11.5 and 11.70) and it worked without issues in all of them. So if you're upgrading an old system and want to make sure you clear any pending inplace altered tables this can be a great help.
The SQL containing the script is at the bottom of this article and I will not dig into it with great detail. The challenges I got in developing it were mainly to understand how the needed data was already present in sysmaster views and also on interpreting this data (the representation is different depending on the "endianess" of your platform. Again, for the first one the help from Andreas Legner was precious and for the second one a special thank you goes to Art Kagel. Both of them helped me to review the script and to fix some nasty bugs I had in my first attempts.
Usage
In order to use this procedure, you'll need to copy the script code below, paste it into dbaccess and run it against any of your instance databases. It will create a function called get_pending_ipa() that will return the following fields:
- Database
- Table name
- Partition name
- Object type (can be table, partition or partition main)
- Partition number
- Partition lockid (the partnum of the main partition for fragmented tables)
- Table structure version
- Number of pages remaining in this version
To execute just run
execute function get_pending_ipa();
or
execute procedure get_pending_ipa();
Disclaimer
Although the script was tested as much as I could, please understand that is comes with no guarantee. Use at your one risk. Neither me nor my employer can be considered liable for any harm done by it (difficult to happen since it only SELECTs), or more important for bad decisions taken based on it's output. This is just the usual disclaimer. Naturally I've done my best to make sure it works. If you find any error in the script or if you have any suggestion, feel free to contact me.
Vers�o Portuguesa:
Introdu��o
O assunto das tabelas com inplace ALTERs (optei por n�o traduzir o termo) tem estado sempre presente no Informix. Dizemos que uma tabela tem um inplace ALTER quando uma instru��o ALTER TABLE deu origem a uma nova defini��o (schema) de tabela, mas a mesma n�o foi fisicamente alterada. Isto � muito �til em tabelas grandes e/ou com muitos acessos. Permite que se fa�a um ALTER TABLE muito r�pido (instant�neo) e com um impacto reduzido no consumo de recursos do sistema. Internamente o Informix faz algo muito simples do ponto de vista do utilizador mas que pode ser bastante complexo se visto pelo �ngulo do motor: � criada uma nova defini��o da estrutura da tabela, e a partir desse momento qualquer instru��o (DML) que afecte os dados usar� essa nova vers�o. As p�ginas j� existentes da tabela mant�m-se na vers�o antiga, mas sempre que fa�amos um SELECT o motor converte a linha(s) para o novo formato. Adicionalmente, se fizermos um UPDATE a p�gina onde estiver guardado o registo(s) ser� convertida pelo motor para o novo formato.
Por exemplo, se tivermos uma tabela com muitas p�ginas de dados, e adicionar-mos uma coluna, isto ser� feito com um inplace ALTER. Mas alguns tipos de ALTER TABLE n�o permitem um inplace ALTER. Caso mudemos uma coluna de CHAR(5) para SMALLINT, isto ser� um slow ALTER. Habitualmente isto acontece se o sistema n�o puder garantir imediatamente que os dados existentes t�m representa��o ou podem ser guardados no novo tipo de dados (no caso anterior o CHAR(5) pode ter caracteres n�o num�ricos). Se o motor decide que pode fazer um inplace ALTER n�o temos forma de o inibir. Ou seja, a altera��o ser� for�osamente feita com inplace ALTER e se desejarmos for�ar a mudan�a f�sica temos de fazer o que normalmente se designa de dummy UPDATE: UPDATE tabela SET coluna = coluna;
Impactos dos in place ALTERs
Existem v�rios impactos em ter tabelas com inplace ALTERs. O mais imediato � que os UPDATEs ser�o ligeiramente mais lentos, pois toda a p�gina tem de ser convertida e escrita. Note-se que isto n�o implica maior carga de I/O, pois a p�gina � a unidade de escrita mais pequena do motor. Por outras palavras, mesmo que a sua tabela n�o tenha mais que uma vers�o da sua defini��o, quando mudamos uma linha contida numa p�gina, toda a p�gina (pelo menos) ser� escrita. Mas as outras linhas da mesma p�gina n�o s�o alteradas. E mudar de uma vers�o anterior da defini��o para a atual pode implicar que nem todas as linhas caibam na p�gina depois de convertidas. Isso sim, pode implicar mais I/O.
Outro potencial problema � que como em qualquer outra �rea de c�digo do motor, o mecanismo de inplace ALTER pode ter problemas ou erros. Este aspeto � muitas vezes sobrevalorizado, mas � um facto que j� tivemos problemas que s� aconteciam em tabelas com inplace ALTERs.
Mas o verdadeiro problema habitualmente associado com os inplace ALTERs diz respeito aos upgrades e downgrades. Atrav�s das vers�es tem existido grande controv�rsia relativamente �s verdadeiras implica��es de se efetuarem convers�es (ou upgrades inplace) de vers�o existindo tabelas com inplace ALTERs pendentes (por pendentes quer-se dizer que t�m efetivamente p�ginas com mais que uma vers�o de defini��o ou schema da tabela). Procurei pelos guias de migra��o de v�rias vers�es (7.3, 9.2, 9.4, 10, 11.1, 11.5 e 11.7) e s�o quase absolutamente consistentes: Pode fazer-se o upgrade com inplace ALTERs pendentes mas n�o se pode fazer o inverso (regress�o). A �nica exce��o a esta regra est� no manual da vers�o 9.4 que refere que os mesmos t�m de ser removidos antes de se efectuar a convers�o.
A raz�o para permitir convers�es, mas n�o regredir � bastante simples e compreens�vel: O Informix garante que a vers�o N+1 consegue lidar com toas as possibilidades de inplace ALTERs da vers�o N ou anteriores. Mas dado que em cada vers�o podem ser adicionadas novas situa��es onde o motor consegue fazer um inplace ALTER, n�o podemos correr o risco de portar um inplace ALTER pendente para uma vers�o anterior que n�o sabe como lidar com ele. Deixe-me lembrar que um inplace ALTER obriga a que o motor consiga mapear os dados de um formato para outro (com mais colunas, ou tipos de dados diferentes etc.)
No momento da escrita deste artigo n�o consegui verificar se a exce��o no guia de migra��o da vers�o 9.4 se pode justificar com um erro de documenta��o ou se tem outro fundamento. Mas o facto � que a maioria dos utilizadores assumem que t�m de completar (ou eliminar) os inplace ALTERs pendentes antes das convers�es (upgrades). Uma raz�o v�lida para este racioc�nio � que caso seja necess�rio regredir para a vers�o original n�o se querer� perder tempo a executar os dummy updates. Ou seja, eliminar os inplace ALTERs pendentes, antes da convers�o, pode poupar tempo precioso caso se verifique a necessidade de regredir. O manual da vers�o 11.7 vai um pouco mais longe e refere que s� � necess�rio remover os inplace ALTERs pendentes, se os mesmos foram gerados j� na vers�o 11.7
Qualquer um anterior (que j� existisse na vers�o original) n�o necessitar� de ser eliminado.
Como um aparte, permita-me que diga que a �nica vez que fiz regress�es foi num workshop para parceiros e clientes com o objetivo de demonstrar a funcionalidade. Nunca tive a necessidade de efectuar isto numa situa��o real em clientes. Em qualquer caso existem v�rias limita��es �s regress�es que podem constituir verdadeiros desafios, pelo que os inplace ALTERs n�o deveriam ser a maior preocupa��o.
Identificar inplace ALTERs pendentes
Agora que vimos em que situa��es devemos remover os inplace ALTERs pendentes, chegamos a outro t�pico que � alvo frequente de perguntas e discuss�es. A quest�o �: Como identificamos as tabelas que possuem p�ginas de dados com v�rias vers�es da sua defini��o? H� v�rias respostas e regra geral s�o controv�rsias ou mal explicadas o que levanta enormes confus�es. Decorreu uma discuss�o sobre este tema mais uma vez em finais de 2010. Como � h�bito foram dadas tr�s respostas para o problema:
- Uma forma r�pida (baseada em SQL e tabelas SMI) que nos diz as tabelas que sofreram inplace ALTERs mas n�o permite saber se ainda est�o pendentes (existem ainda p�ginas de dados com formato antigo). Isto significa que a menos que se refa�a completamente a tabela, a mera execu��o dos dummy UPDATEs n�o impedir� a tabela de voltar a aparecer na lista gerada por este m�todo.
- Uma forma lenta, baseada no resultado do oncheck -pT. Isto efetivamente diz-nos quantas p�ginas de dados existem para cada formato da tabela. Este m�todo permite realmente identificar os inplace ALTERs pendentes.
- Uma ferramenta do suporte t�cnico que procura na metadata das tabelas e pode fornecer a resposta de forma r�pida. O �nico problema � que n�o est� dispon�vel para os utilizadores em geral
E isto foi a minha motiva��o para efetuar alguma pesquisa sobre este tema. Ap�s alguma troca de informa��o com o Andreas Legner do suporte t�cnico da IBM na Alemanha, consegui criar um script SQL que pode reportar as tabelas com inplace ALTERs pendentes. Este script consegue fornecer o n�mero de p�ginas existentes em cada vers�o da defini��o da tabela. Retorna a base de dados, a tabela, a vers�o(�es) e quantas p�ginas cont�m.
O bom deste script � que � r�pido (de uns segundos a poucos minutos para bases de dados muito grandes), e mostra a situa��o actual. Contrariamente � op��o 1) acima, depois de fazermos os dummy UPDATEs numa tabela, essa mesma tabela n�o volta a aparecer no output do script. Apenas um aviso relativamente a isto: O script percorre o que chamamos de partition headers e estes s� s�o escritos em disco durante um checkpoint. Assim, depois de correr os dummy UPDATEs devem for�ar-se um checkpoint (ou esperar que ocorra um) antes de correr novamente o script.
O script traduz-se num procedimento SPL e baseia-se em informa��o dispon�vel nas views da base de dados sysmaster. O script foi testeado em todas as vers�es que consegui encontrar (7.31, 9.3, 9.4, 10, 11.1, 11.5 e 11.7) e correru em todas sem problemas. Assim, se estiver a fazer uma convers�o de um sistema antigo e quiser limpar todos os inplace ALTERs pendentes nessa inst�ncia, isto pode ser uma grande ajuda.
O script SQL contendo o procedimento est� dispon�vel no final deste artigo e n�o vou fazer uma explica��o exaustiva do mesmo. Os desafios que enfrentei durante o desenvolvimento do procedimento foram principalmente entender se os os dados necess�rios estavam representados na base de dados sysmaster e tamb�m na interpreta��o desses dados (a representa��o dos dados � diferente conforme o "endianess" da plataforma). Mais uma vez, na primeira quest�o a ajuda do Andreas Legner foi preciosa e para a segunda quest�o tive a ajuda do Art Kagel a quem enviou um sincero agradecimento. Ambos me ajudaram a rever o procedimento e identificaram alguns bugs feios que tinha nas primeiras tentativas.
Utiliza��o
Para usar esta fun��o ter� de copiar o c�digo do script que se encontra no final do artigo, col�-lo num dbaccess (ou outra ferramenta) e execut�-lo numa das bases de dados da sua inst�ncia. O script ir� criar uma fun��o chamada get_pending_ipa() que ir� retornar os seguintes valores:
- Nome da base de dados
- Nome da tabela
- Nome da parti��o
- Tipo de objecto (pode ser table, partition ou partition main)
- N�mero da parti��o
- lockid da parti��o (o n�mero da parti��o principal para tabelas fragmentadas)
- Vers�o da estrutura da tabela
- N�mero de p�ginas ainda existentes nesta vers�o
Para executar basta dar a instru��o:
execute function get_pending_ipa();
ou
execute procedure get_pending_ipa();
Exclus�o de garantia
Apesar de o script ter sido testado tanto quanto pude, por favor assuma que o mesmo n�o � fornecido com qualquer tipo de garantia. Utilize-o por sua conta e risco. Nem eu nem o meu empregador poder�o ser considerados respons�veis por qualquer mal ou preju�zo derivado do seu uso (dif�cil dado que apenas faz SELECTs), ou mais importante, por m�s decis�es baseadas no seu output. Isto � apenas o normal termo de des-responsabiliza��o. Naturalmente fiz o meu melhor para assegurar que o procedimento funciona bem e retorna resultados corretos. Qualquer problema que identifique no script ou sugest�o de melhoria por favor contacte-me.
SQL script:
CREATE FUNCTION get_pending_ipa() RETURNING
VARCHAR(128) as database, VARCHAR(128) as table, VARCHAR(128) as partition, VARCHAR(9) as obj_type,
INTEGER as partnum, INTEGER as lockid, SMALLINT as version, INTEGER as npages
-- For version 7.x use this header instead:
--CREATE PROCEDURE get_pending_ipa() RETURNING VARCHAR(128), VARCHAR(128), VARCHAR(128), VARCHAR(9), INTEGER, INTEGER, SMALLINT, INTEGER;
-- Name: $RCSfile: get_pending_ipa.sql,v $
-- CVS file: $Source: /usr/local/cvs/stable/informix/queries/get_pending_ipa.sql,v $
-- CVS id: $Header: /usr/local/cvs/stable/informix/queries/get_pending_ipa.sql,v 1.5 2011/09/09 20:57:31 fnunes Exp $
-- Revision: $Revision: 1.5 $
-- Revised on: $Date: 2011/09/09 20:57:31 $
-- Revised by: $Author: fnunes $
-- Support: Fernando Nunes - domusonline@gmail.com
-- Licence: This script is licensed as GPL ( http://www.gnu.org/licenses/old-licenses/lgpl-2.0.html )
-- Variables holding the database,tabnames and partnum
DEFINE v_dbsname, v_old_dbsname LIKE sysmaster:systabnames.dbsname;
DEFINE v_tabname, v_partname, v_old_tabname LIKE sysmaster:systabnames.tabname;
DEFINE v_partnum, v_old_partnum LIKE sysmaster:syspaghdr.pg_partnum;
DEFINE v_lockid, v_old_lockid LIKE sysmaster:sysptnhdr.lockid;
DEFINE v_pg_next INTEGER;
DEFINE v_pg_partnum INTEGER;
DEFINE v_obj_type VARCHAR(9);
-- Variables holding the various table versions and respective number of pages pending to migrate
DEFINE v_version SMALLINT;
DEFINE v_pages INTEGER;
-- Hexadecimal representation of version and pending number of pages
DEFINE v_char_version CHAR(6);
DEFINE v_char_pages CHAR(10);
DEFINE v_aux_char CHAR(8);
-- Hexadecimal representation of the slot 6 data. Each 16 bytes will appear as a record that needs to be concatenated
DEFINE v_hexdata VARCHAR(128);
-- Variable to hold the sysmaster:syssltdat hexadecimal representation of each 16 bytes of the slot data
DEFINE v_slot_hexdata CHAR(40);
DEFINE v_aux VARCHAR(128);
DEFINE v_endian CHAR(6);
DEFINE v_offset SMALLINT;
DEFINE v_slotoff SMALLINT;
DEFINE v_dummy INTEGER;
-- In case we need to trace the function... Uncomment the following two lines
--SET DEBUG FILE TO "/tmp/get_pending_ipa.dbg";
--TRACE ON;
-- Now lets find out the Endianess ( http://en.wikipedia.org/wiki/Endianness ) of this platform
-- The data in sysmaster:syssltdat will be different because of possible byte swap
-- Read the first slot of the rootdbs TBLSpace tblspace (0x00100001)
-- The first 4 bytes hold the partition number (0x00100001)
SELECT
s.hexdata[1,8]
INTO
v_hexdata
FROM
sysmaster:syssltdat s
WHERE
s.partnum = '0x100001' AND
s.pagenum = 1 AND
s.slotnum = 1 AND
s.slotoff = 0;
IF v_hexdata = '01001000'
THEN
-- Byte swap order, so we're little Endian (Intel, Tru64....)
LET v_endian = 'LITTLE';
ELSE
IF v_hexdata = '00100001'
THEN
-- Just as we write it (no byte swap), so we're big Endian (Sparc, Power, Itanium...)
LET v_endian = 'BIG';
ELSE
-- Just in case something weird (like a bug(!) or physical modification) happened
RAISE EXCEPTION -746, 0, 'Invalid Endianess calculation... Check procedure code!!!';
END IF
END IF
-- Flags to mark the beginning
LET v_hexdata = "-";
LET v_old_dbsname = "-";
LET v_old_tabname = "-";
-- The information we want for each version description will occupy this number of characters
-- in the sysmaster:syssltdat.hexdata notation (after removing spaces). The size depends on the engine version.
LET v_offset=DBINFO('version','major');
IF v_offset >= 10
THEN
LET v_offset = 48;
ELSE
LET v_offset = 40;
END IF
LET v_old_lockid = -1;
FOREACH
-- This query will browse through all the instance partitions, excluding sysmaster database, and will look for
-- any extended partition header (where partition header "next" field is not 0)
-- the ABS(...) is just a trick to make partnums that are equal to lock id appear at the end
SELECT
t.dbsname, t.tabname, t1.tabname, t.partnum, p.pg_partnum, p.pg_next, h.lockid, ABS(h.lockid - h.partnum)
INTO
v_dbsname, v_partname ,v_tabname, v_partnum, v_pg_partnum, v_pg_next, v_lockid, v_dummy
FROM
sysmaster:systabnames t,
sysmaster:syspaghdr p,
sysmaster:sysptnhdr h,
sysmaster:systabnames t1
WHERE
p.pg_partnum = sysmaster:partaddr(sysmaster:partdbsnum(t.partnum),1) AND
p.pg_pagenum = sysmaster:partpagenum(t.partnum) AND
t.dbsname NOT IN ('sysmaster') AND
h.partnum = t.partnum AND
t1.partnum = h.lockid AND
p.pg_next != 0
ORDER BY
t.dbsname, t.tabname, 8 DESC, t.partnum
IF v_lockid = v_partnum
THEN
IF v_lockid = v_old_lockid
THEN
LET v_obj_type = "Part Main";
ELSE
LET v_obj_type = "Table";
END IF
ELSE
LET v_obj_type = "Part";
END IF
LET v_old_lockid = v_lockid;
WHILE v_pg_next != 0
-- Find if we're dealing with a fragmented table or not...
-- While this extended partition page points to another one...
-- Get all the slot 6 data (where the version metadata is stored - version, number of pages, descriptor page etc.
FOREACH
SELECT
REPLACE(s.hexdata, ' '), s.slotoff, p.pg_next
INTO
v_slot_hexdata, v_slotoff, v_pg_next
FROM
sysmaster:syspaghdr p,
sysmaster:syssltdat s
WHERE
s.partnum = p.pg_partnum AND
s.pagenum = p.pg_pagenum AND
s.slotnum = 6 AND
p.pg_partnum = v_pg_partnum AND
p.pg_pagenum = v_pg_next
IF ( v_dbsname != v_old_dbsname OR v_tabname != v_old_tabname OR v_partnum != v_old_partnum)
THEN
LET v_old_dbsname = v_dbsname;
LET v_old_tabname = v_tabname;
LET v_old_partnum = v_partnum;
-- First iteraction for each table
LET v_hexdata = v_slot_hexdata;
ELSE
-- Next iteractions for each table
LET v_hexdata = TRIM(v_hexdata) || v_slot_hexdata;
IF LENGTH(v_hexdata) >= v_offset
THEN
-- We already have enough data for a version within a table
-- Note that we probably have part of the next version description in v_hexdata
-- So we need to copy part of it, and keep the rest for next iteractions
LET v_aux=v_hexdata;
LET v_hexdata=SUBSTR(v_aux,v_offset+1,LENGTH(v_aux)-v_offset);
-- Split the version and number of pending pages part...
LET v_char_version = v_aux[1,4];
LET v_char_pages = v_aux[9,16];
-- Create a usable hex number. Prefix it with '0x' and convert due to little endian if that's the case
IF v_endian = "BIG"
THEN
LET v_char_version = '0x'||v_char_version;
LET v_char_pages = '0x'||v_char_pages;
ELSE
LET v_aux_char = v_char_version;
LET v_char_version[5]=v_aux_char[1];
LET v_char_version[6]=v_aux_char[2];
LET v_char_version[4]=v_aux_char[4];
LET v_char_version[3]=v_aux_char[3];
LET v_char_version[2]='x';
LET v_char_version[1]='0';
LET v_aux_char = v_char_pages;
LET v_char_pages[9]=v_aux_char[1];
LET v_char_pages[10]=v_aux_char[2];
LET v_char_pages[7]=v_aux_char[3];
LET v_char_pages[8]=v_aux_char[4];
LET v_char_pages[6]=v_aux_char[6];
LET v_char_pages[5]=v_aux_char[5];
LET v_char_pages[3]=v_aux_char[7];
LET v_char_pages[4]=v_aux_char[8];
LET v_char_pages[2]='x';
LET v_char_pages[1]='0';
END IF
-- HEX into DEC (integer)
LET v_version = TRUNC(v_char_version + 0);
LET v_pages = TRUNC(v_char_pages + 0);
IF v_pages > 0
THEN
-- This version has pending pages so show it...
RETURN TRIM(v_dbsname), TRIM(v_tabname), TRIM(v_partname), TRIM(v_obj_type), v_partnum, v_lockid, v_version, v_pages WITH RESUME;
END IF
END IF
END IF
END FOREACH
IF LENGTH(v_hexdata) >= v_offset
THEN
-- If we still have data to process...
LET v_aux=v_hexdata;
LET v_char_version = v_aux[1,4];
LET v_char_pages = v_aux[9,16];
IF v_endian = "BIG"
THEN
LET v_char_version = '0x'||v_char_version;
LET v_char_pages = '0x'||v_char_pages;
ELSE
LET v_aux_char = v_char_version;
LET v_char_version[5]=v_aux_char[1];
LET v_char_version[6]=v_aux_char[2];
LET v_char_version[4]=v_aux_char[4];
LET v_char_version[3]=v_aux_char[3];
LET v_char_version[2]='x';
LET v_char_version[1]='0';
LET v_aux_char = v_char_pages;
LET v_char_pages[9]=v_aux_char[1];
LET v_char_pages[10]=v_aux_char[2];
LET v_char_pages[7]=v_aux_char[3];
LET v_char_pages[8]=v_aux_char[4];
LET v_char_pages[6]=v_aux_char[6];
LET v_char_pages[5]=v_aux_char[5];
LET v_char_pages[3]=v_aux_char[7];
LET v_char_pages[4]=v_aux_char[8];
LET v_char_pages[2]='x';
LET v_char_pages[1]='0';
END IF
-- HEX into DEC (integer)
LET v_version = TRUNC(v_char_version + 0);
LET v_pages = TRUNC(v_char_pages + 0);
IF v_pages > 0
THEN
-- This version has pending pages so show it...
RETURN TRIM(v_dbsname), TRIM(v_tabname), TRIM(v_partname), TRIM(v_obj_type), v_partnum, v_lockid, v_version, v_pages WITH RESUME;
END IF
END IF
END WHILE
END FOREACH;
END FUNCTION;
-- For version 7.x use this close statement instead:
--END PROCEDURE;
