Heads up!
Warning: old tools with new version (original version here)
English version
In the post about the release of version 15 I've mentioned an heads up about the risk of using the new version of the engine with current versions of the tools (4GL/ISQL). I hope I can show the issue more clearly here while we start to grasp on the expanded capacity of version 15.
Version 15 has two "modes" of operation. The "compatibility mode" or "Infrastructure Version: 0" and the new version "Infrastructure Version: 1". Currently I have no idea if future changes will create new modes.
In "Compatibility Mode" the engine cannot create "LARGE TABLEs". If you try to use the "CREATE LARGE TABLE tabname...." you'll get:
261: Cannot create table (informix.test_large).
21568: V1 infrastructure features have not yet been enabled.
The mode is shown for example if you run "onstat -":
IBM Informix Dynamic Server Version 15.0.0.0AEE -- On-Line -- Up 00:21:11 -- 222740 Kbytes
2024-11-24 02:28:09 -- Infrastructure Version: 0 (Compatibility mode)
There is a process to "convert" an instance from Infrastructure Version 0 to 1, but I won't cover that in this post. Let's now see what happens in an instance created from scratch in version 15. By default all tables will be in "LARGE TABLE" mode. But we have the option to create "SMALL TABLEs". A way to check if a table is in LARGE or SMALL mode is by running oncheck:
asterix@myhost.onlinedomus.local-> oncheck -pt stores:customer | head -10
TBLspace Report for stores:informix.customer
Physical Address 8:566
Creation date 11/19/2024 15:31:46
TBLspace Flags 400000802 Row Locking
TBLspace uses 4 bit bit-maps
TBLspace uses large rowids
Maximum row size 134
asterix@myhost.onlinedomus.local->Note the "TBLspace uses large rowids" section. Let's create a small table and check it:
asterix@myhost.onlinedomus.local-> dbaccess -e stores small_test.sql
Database selected.
DROP TABLE IF EXISTS customer_small;
Table dropped.
CREATE SMALL TABLE customer_small
(
customer_num SERIAL NOT NULL ,
fname CHAR(15),
lname CHAR(15),
company CHAR(20),
address1 CHAR(20),
address2 CHAR(20),
city CHAR(15),
state CHAR(2),
zipcode CHAR(5),
phone CHAR(18),
PRIMARY KEY (customer_num)
) LOCK MODE ROW;
Table created.
INSERT INTO customer_small SELECT * FROM customer;
28 row(s) inserted.
Database closed.
asterix@myhost.onlinedomus.local->So now if we check the customer_small table:
asterix@myhost.onlinedomus.local-> oncheck -pt stores:customer_small | head -10
TBLspace Report for stores:informix.customer_small
Physical Address 8:662
Creation date 11/24/2024 02:47:29
TBLspace Flags 802 Row Locking
TBLspace uses 4 bit bit-maps
Maximum row size 134
Number of special columns 0
asterix@myhost.onlinedomus.local->There is no indication of "large rowids" so it's a "small" table. Now let's see what happens if we try to use ISQL on these two tables. I'm using 7.51.FC3, currently the latest available version of the tools.
asterix@myhost.onlinedomus.loca-> finderr -21569 Message number -21569 not found. asterix@myhost.onlinedomus.loca:informix->
The error is unknown. Not a surprise as the error didn't exist when this version of the tools was created. So let's check it in the engine environment:
So, it makes sense. The engine understands the application is expecting small rowids and raises an error if the table was created with LARGE mode.asterix@myhost.onlinedomus.local:informix-> . setinfx 5 Welcome to CDC Instance (ADM) Asterix asterix@myhost.onlinedomus.local:informix-> finderr -21569 -21569 This client cannot create or open tables that use large rowids. You are attempting to create or open a large table, but your client program is not compatible with large tables. Set the TABLE_SIZE session environment variable to "SMALL" or rebuild your client using the latest client libraries. asterix@myhost.onlinedomus.local:informix->
This is the risk you face if you try to use the engine in version 15 before a new version of the tools are released. Note that this will affect any application that relies or handles rowids. The problem will not manifest if you upgrade an instance and avoid changing it to the new Infrastructure Mode, or in other words, while you keep in Compatibility Mode.
At the time of this writing I am unaware of any method to force the Infrastructure Version 0 on a newly created instance. This would mean that any environment that depends on rowids would require a tools refresh before any upgrade is considered. Don't take this lightly or you'll risk serious issues on your applications. An alternative would be (in a newly created instance) to create all the tables as SMLL
Let it be clear that there is an environment option to change the default type of tables we wish to create (when we don't specify the SMALL nor the LARGE option). That option is set with the command:
SET ENVIRONMENT TABLE_SIZE <SMALL|LARGE>
Note that if you try to set it to "LARGE" in an instance running in compatibility mode you'll get the error:
mouraria@psygnosis.onlinedomus.local:informix-> finderr -26041
-26041 Invalid values specified for the %s environment variable.
An invalid value was provided for the environment variable. Please check
your user documentation, and provide the correct value, and try again.Versão Portuguesa
No artigo sobre a disponibilização da versão 15, mencionei um aviso sobre o risco de usar a nova versão do motor com as versões actuais das ferramentas (4GL/ISQL). Espero agora poder mostrar aqui de forma mais clara este tema, enquanto começamos a entrar mais na capacidade estendida da versão 15
A versão 15 tem dois "modos" de operação. O modo de compatibilidade ou "Infrastructure Version 0" e o novo modo ou "Infrastructure Version 1". De momento não faço ideia se futuras alterações poderão dar origem a outros modos.
Em modo de compatibilidade o motor não pode criar "LARGE TABLEs". Se tentarmos usar "CREATE LARGE TABLE nom_tabela...." obteremos:
261: Cannot create table (informix.test_large).
21568: V1 infrastructure features have not yet been enabled.
O modo pode ser mostrado quando executamos "onstat -":
IBM Informix Dynamic Server Version 15.0.0.0AEE -- On-Line -- Up 00:21:11 -- 222740 Kbytes
2024-11-24 02:28:09 -- Infrastructure Version: 0 (Compatibility mode)
Existe um processo para "converter" uma instância do modo "Infrastructure Version 0" para "Infrastructure Version 1", mas não vou cobrir isso neste artigo. Vejamos agora o que acontece numa instância criada de raiz na versão 15. Por omissão todas as tabelas serão "LARGE TABLES". Mas temos a opção de criar "SMALL TABLEs". Uma forma de verificar o tipo de tabela é usar o comando oncheck:
asterix@myhost.onlinedomus.local-> oncheck -pt stores:customer | head -10
TBLspace Report for stores:informix.customer
Physical Address 8:566
Creation date 11/19/2024 15:31:46
TBLspace Flags 400000802 Row Locking
TBLspace uses 4 bit bit-maps
TBLspace uses large rowids
Maximum row size 134
asterix@myhost.onlinedomus.local->Repare-se na secção "TBLspace uses large rowids". Vamos criar uma SMALL TABLE e verificar:
asterix@myhost.onlinedomus.local-> dbaccess -e stores small_test.sql
Database selected.
DROP TABLE IF EXISTS customer_small;
Table dropped.
CREATE SMALL TABLE customer_small
(
customer_num SERIAL NOT NULL ,
fname CHAR(15),
lname CHAR(15),
company CHAR(20),
address1 CHAR(20),
address2 CHAR(20),
city CHAR(15),
state CHAR(2),
zipcode CHAR(5),
phone CHAR(18),
PRIMARY KEY (customer_num)
) LOCK MODE ROW;
Table created.
INSERT INTO customer_small SELECT * FROM customer;
28 row(s) inserted.
Database closed.
asterix@myhost.onlinedomus.local->Verifiquemos então a tabela customer_small:
asterix@myhost.onlinedomus.local-> oncheck -pt stores:customer_small | head -10
TBLspace Report for stores:informix.customer_small
Physical Address 8:662
Creation date 11/24/2024 02:47:29
TBLspace Flags 802 Row Locking
TBLspace uses 4 bit bit-maps
Maximum row size 134
Number of special columns 0
asterix@myhost.onlinedomus.local->Não há referência a "large rowids" portanto é uma "SMALL TABLE". Agora vejamos o que acontece se tentarmos usar o ISQL com ambas as tabelas. Vou usar a versão 7.51.FC3, actualmente a última versão disponível das ferramentas.
asterix@myhost.onlinedomus.loca-> finderr -21569 Message number -21569 not found. asterix@myhost.onlinedomus.loca:informix->
O erro é desconhecido. Não é uma surpresa visto que o erro não existia quando esta versão das ferramentas foi criada. Verifiquemos então no ambiente do motor:
Portanto tudo faz sentido. O motor entende que a aplicação está à espera de rowids "curtos" e cria um erro se a tabela em causa foi criada como LARGE TABLE.asterix@myhost.onlinedomus.local:informix-> . setinfx 5 Welcome to CDC Instance (ADM) Asterix asterix@myhost.onlinedomus.local:informix-> finderr -21569 -21569 This client cannot create or open tables that use large rowids. You are attempting to create or open a large table, but your client program is not compatible with large tables. Set the TABLE_SIZE session environment variable to "SMALL" or rebuild your client using the latest client libraries. asterix@myhost.onlinedomus.local:informix->
à data de escrita deste artigo desconheço se há alguma forma de forçar o modo de compatibilidade numa instância criada de raiz na versão 15. Isto significa que qualquer upgrade para a versão 15 com recriação de instância requererá uma nova versão das tools. Não menospreze este tema pois haverá sérios riscos de as aplicações deixarem de funcionar. Uma alternativa seria (numa instância criada de raiz) criar todas as tabelas como SMALL.
Note-se que há uma opção de ambiente para mudar o tipo de tabela a criar por omissão (quando não se usa o SMALL ou o LARGE no CREATE TABLE).
Essa opção é usar o comando:
SET ENVIRONMENT TABLE_SIZE <SMALL|LARGE> Refira-se ainda que, uma tentativa de estabelecer esta opção como LARGE, numa instância que se encontre em modo de compatibilidade gerará um erro:
mouraria@psygnosis.onlinedomus.local:informix-> finderr -26041
-26041 Invalid values specified for the %s environment variable.
An invalid value was provided for the environment variable. Please check
your user documentation, and provide the correct value, and try again.
Posts
