This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português
English version:
I suppose this article should be written in Spanish but unfortunately I'm too limited for that. I recently noticed the appearance of the Spanish Informix User Group site. Accordingly to the information online it's recent and it will be launched in this Autumn with the presence of several Informix specialists.
If your country is Spain, or your native language is Spanish, and you use Informix (or just want to learn more about it) I think and hope that this will be a new reference. I've seen some signs of interest in Informix in my neighbor country and this is another one.
I wish all the best to the founders and I hope they'll be able to attract the users and serve the community. And for the Portuguese community, this can also be interesting. There's a large number of people here to whom the language will not be a big issue, so maybe we can also enjoy and take advantage of this initiative (even due to the geographic proximity)
Versão Portuguesa:
Suponho que este artigo deveria ser também escrito em Espanhol/Castelhano, mas infelizmente sou demasiado limitado para isso. Notei recentemente o aparecimento do site do Grupo Espanhol de Utilizadores Informix. Segundo a informação lá colocada é recente e será lançado neste Outono com a presença de vários especialistas Informix.
Se o seu país é a Espanha ou se o Espanhol é uma língua que domina, e usa Informix (ou apenas deseje aprender mais sobre ele) penso e espero que isto venha a ser uma nova referência. Tenho visto alguns sinais de interesse no Informix no meu país vizinho e este é mais um.
Desejo as maiores felicidades aos fundadores e espero que consigam atrair os utilizadores e servir a comunidade. Também para a comunidade Portuguesa isto pode ser interessante. Existe por cá muita gente para quem a língua não será um obstáculo e portanto poderão disfrutar e tirar partido desta iniciativa (até pela proximidade geográfica)
Tuesday, September 13, 2011
Friday, September 9, 2011
Visual Summary Goes Live on WikiSummarizer
Get pending In Place ALTERs / Obter as tabelas com InPlace ALTERs pendentes
This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português
http://www.tonkinnews.com
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:
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:
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:
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:
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;
Este artigo está escrito em Inglês e Português
http://www.tonkinnews.com
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;
Thursday, September 8, 2011
New Releases: Mindomo 6 & MindMapper 12
Although I haven't had time to completely review some of the new releases this week, I wanted to let you know that the folks at Mindomo have released their web based mind mapping application Mindomo 6 which is a major release and rewrite of the application. Mindomo 6 is a very attractive application that at times makes you forget that you are using a web based application running through the browser. I will have more to say about the program but wanted to let you know that Mindomo 6 has been released and if you can give it a test drive. I am including an embedded Mindomo 6 mind map to give you an idea of what is possible. One of my favorite features of online mind mapping applications is the ability to embed your map online by pasting a bit of code into the blog post as you will see.
SimTech this week also announced the release of MindMapper 12 a full featured desktop mind mapping application for Windows. MindMapper 12 now sports the Fluid Interface Ribbon common in many Windows applications making it easier to use. MindMapper 12 now enjoys many more user panes to make the program easier to navigate and use. MindMapper 12 offers users some very sophisticated mapping structures to accomplish brainstorming, project management, presentation of ideas, as well as root cause analysis. MindMapper112 incorporates project management features as well as Gantt Charts for those professionals who need to track ongoing projects. Once I have a chance to sit down with MindMapper 12, I will share with you some of my thoughts.
It is exciting to be able to cover this arena and to see all of the choices we have in the tools that we use for our business and professional work. Stay tuned for more thoughts about these applications. In the meantime- if you have a chance go to SimTech's and Mindomo's Website and trial each of the applications and let me know how you make out.
SimTech this week also announced the release of MindMapper 12 a full featured desktop mind mapping application for Windows. MindMapper 12 now sports the Fluid Interface Ribbon common in many Windows applications making it easier to use. MindMapper 12 now enjoys many more user panes to make the program easier to navigate and use. MindMapper 12 offers users some very sophisticated mapping structures to accomplish brainstorming, project management, presentation of ideas, as well as root cause analysis. MindMapper112 incorporates project management features as well as Gantt Charts for those professionals who need to track ongoing projects. Once I have a chance to sit down with MindMapper 12, I will share with you some of my thoughts.
It is exciting to be able to cover this arena and to see all of the choices we have in the tools that we use for our business and professional work. Stay tuned for more thoughts about these applications. In the meantime- if you have a chance go to SimTech's and Mindomo's Website and trial each of the applications and let me know how you make out.
Tuesday, September 6, 2011
Bookstream™: New Cloud-Based Library Delivers eBooks to Students' iPads, Smartphones, and Laptops
| For Immediate Release | ||
| Contact: Valerie C. Chernek | 410-871-2670 | vchernek@donjohnston.com |
Bookstream™: New Cloud-Based Library Delivers eBooks to
Students' iPads, Smartphones, and Laptops
Students' iPads, Smartphones, and Laptops
September 6, 2011- Volo, IL – This school year, educators will have a new option to deliver their books. In addition to handing out textbooks in class, they will deliver eBooks through the cloud to students' iPads, smartphones, and laptops with a new online service called Bookstream.
Bookstream, developed by Don Johnston Incorporated, makes it easy for educators to hop on the Internet, upload eBooks and share them with students for anytime reading. It was designed to help educators comply with IDEA 2004 (Individuals with Disabilities Education Act) that require schools to deliver core curriculum in accessible formats to support students with disabilities.
"We work with assistive technology specialists who drive hundreds of miles each week to deliver eBooks on flash drives," said Ben Johnston, Director of Marketing. "It's ironic that eBooks are delivered by car, but as one special educator explained, there aren't many alternatives—book files are often too big and unwieldy to email or download. She was also concerned about violating copyright laws and wanted an easier way to manage digital content. She inspired us to do something about it, and now we are withBookstream."
For districts that are just beginning to build an accessible curriculum, Bookstream includes access to thousands of eBooks and fully supports Bookshare's more than 110,000 digital files. It will read any book in ePub format without DRM restrictions. Teachers can also upload and share their own quizzes and worksheets with students.
Consumers are quickly embracing eBooks, but there are additional challenges for school districts to do the same," said Kevin Johnston, Director of Product Design. "For schools, eBooks come with different rights for different students, the books need to be accessible on school computers and on personal devices, and some electronic textbooks can be over two gigabytes each! With Bookstream, we solve these challenges for educators, making the whole process easier. We also built in tracking tools so educators can see how much time their students spend reading and which books they read."
Your Accessible Library Could Look Like This |
 |
eBooks read through Bookstream are 508 compliant as outlined in the Federal Rehabilitation Act. They are accessible to any assistive technology with a web browser, such as Don Johnston's Read:OutLoud text reader, or the JAWS screen reader for individuals with visual impairments. Bookstream embeds text-to-speech that can be streamed on any Mac or Windows computer.
"Cloud computing has the potential to create the infrastructure our schools need to keep up with the digital world," said Debbie Fogg Ed.S., CCC/SLP, ATP at Naperville School District #203, IL, one of the first school districts to purchase Bookstream. "We're excited to have a platform to deliver electronic text to students' personal and district-owned laptops and mobile devices. We hope to see an increase in academic performance as a result of the anytime reading access."
From now until December 30, 2011, U.S. schools can take advantage of introductory pricing on Bookstream that gives school districts two years of access for the price of one. First year costs are regularly $1749 with an annual maintenance fee of $529 per site. Districts may also qualify for discounts depending on the number of sites they purchase.
"Educators can set up their accessible library and deliver e-Books in less than 30 minutes," adds Ben Johnston. "Our technology is similar to Amazon's Cloud Drive and Apple's iCloud, but we're choosing to focus on education: that's what we're passionate about. Five years ago, it was inconceivable that schools could deliver an accessible curriculum beyond a few students per school, but now through the new cloud technology and digital books, Bookstream makes it all possible and cost-effective."
Resource Links:Learn about Bookstream, Accessible Instructional Materials and all of Don Johnston's reading products.
###
About Don Johnston Incorporated
Don Johnston develops accessible and emerging reading and writing technologies to support K-12 and postsecondary students with cognitive, physical, and learning disabilities. Since 1980, the special education company has partnered with literacy experts, AT specialists, SLPs, OTs, special education teachers, university researchers and DSS coordinators to improve access to learning and to increase writing independence. Tens of thousands of students with dyslexia, autism, dysgraphia, down-syndrome and diverse learning challenges use our software and hardware devices to accommodate their learning needs in schools, the workforce and at home. Find us on Twitter and Facebook.http://www.tonkinnews.com
Don Johnston develops accessible and emerging reading and writing technologies to support K-12 and postsecondary students with cognitive, physical, and learning disabilities. Since 1980, the special education company has partnered with literacy experts, AT specialists, SLPs, OTs, special education teachers, university researchers and DSS coordinators to improve access to learning and to increase writing independence. Tens of thousands of students with dyslexia, autism, dysgraphia, down-syndrome and diverse learning challenges use our software and hardware devices to accommodate their learning needs in schools, the workforce and at home. Find us on Twitter and Facebook.http://www.tonkinnews.com
Sunday, September 4, 2011
Using Comapping for Workshops
As much as I enjoy the power of desktop mind mapping applications and use them all of time in the work that I do, one of the tools that I am relying on more these days is Comapping, an online mind mapping collaboration tool. Comapping can be accessed from any computer and browser that is connected to the Internet and gives me the flexibility to organize my ideas, add hyperlinks as well as attachments. Comapping does a great job allowing you to share your maps when they are done and I have been taking advantage of this feature for years now when I teach my graduate courses. For each class I create an interactive agenda with Comapping and then publish it to my Moodle classroom account for all my students to access.
Recently I have done a number of workshops and needed a quick way for my participants to be able to jump from web site to web site to view the contents. Using Comapping I was able to create the agenda embed the hyperlinks which made the day go so much more smoothly for me. Publishing your Comapping maps to the web is really quite simple and easy to do and when you are done you are given a unique URL that you can then pass out to your students. Because the URL is rather long, I generally log into my Bit.ly account shorten it and customize the link so that when I get to the workshop I just have to give out the shortened URL.
Comapping is also unique in that it allows you to embed your attachments that your participants can access from the map. In the example below you will see that I have embedded a number of Quick Reference Cards for the SOLO training I have coming up. This will make it easy for my participants to access these reference materials on the day of the training as well as when they are back in their classroom. You will also see that there are live links to the Don Johnston Website for additional support videos that can be quickly accessed from the Comapping map. Using this technique not only saves me from printing costs and paper, but models for teachers what is possible as we move from analog to a digital materials. So interact with the Comapping map below and let me know you thoughts about using Comapping in the work that you do. If you are interested in learning how you can do this shoot me an email.
Recently I have done a number of workshops and needed a quick way for my participants to be able to jump from web site to web site to view the contents. Using Comapping I was able to create the agenda embed the hyperlinks which made the day go so much more smoothly for me. Publishing your Comapping maps to the web is really quite simple and easy to do and when you are done you are given a unique URL that you can then pass out to your students. Because the URL is rather long, I generally log into my Bit.ly account shorten it and customize the link so that when I get to the workshop I just have to give out the shortened URL.
Comapping is also unique in that it allows you to embed your attachments that your participants can access from the map. In the example below you will see that I have embedded a number of Quick Reference Cards for the SOLO training I have coming up. This will make it easy for my participants to access these reference materials on the day of the training as well as when they are back in their classroom. You will also see that there are live links to the Don Johnston Website for additional support videos that can be quickly accessed from the Comapping map. Using this technique not only saves me from printing costs and paper, but models for teachers what is possible as we move from analog to a digital materials. So interact with the Comapping map below and let me know you thoughts about using Comapping in the work that you do. If you are interested in learning how you can do this shoot me an email.
Saturday, September 3, 2011
Run Flash on Your iPad with iSwifter
Subscribe to:
Posts (Atom)