Informix

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb

Sebastien:

I have tested this with Jonathan Leffler's sqlcmd compiled with CSDK 4.50 and with CSDK 15.0. Verified:

$ ~/bin/sqlcmd -d art 
-B
SQL[6]: drop table tab1;
2024-12-13 06:45:40
+ drop table tab1;
SQL -214: Cannot remove file for table (art.tab1).
ISAM -21569: This client cannot create or open tables with V1 infrastructure features.
SQLSTATE: IX000 at /dev/stdin:1
Time: 0.001997
SQL[7]:  

$ ./sqlcmd -d art -B  
SQL[7]: drop table tab1;
2024-12-13 06:46:00
+ drop table tab1;
Time: 0.018983
SQL[8]:

Art

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb

Yes, this is quite expected:

• any table you create in v15, if not in compatibility mode and not forcing SMALL table, will be a LARGE (big-rowid) table
• any pre-v15 client will not be able to access such LARGE table
• if looking closely, or if using e.g. a 14.10 dbaccess for the drop table, you'll see ISAM error 21569 associated with SQL 214
• ISAM 21569, as Art already pointed out, indicates above mentioned inability - it only still has to be made known to (the next release) of 14.10/4.50

For now this is a categorical no for old clients trying to access v15 LARGE tables, in whichever way or for whatever purpose.
In theory one might argue that things like DROP TABLE, or maybe other DML, could be allowed, but would anyone see any value in this?

BR,

 Andreas

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb

Yes, this is quite expected:

• any table you create in v15, if not in compatibility mode and not forcing SMALL table, will be a LARGE (big-rowid) table
• any pre-v15 client will not be able to access such LARGE table
• if looking closely, or if using e.g. a 14.10 dbaccess for the drop table, you'll see ISAM error 21569 associated with SQL 214
• ISAM 21569, as Art already pointed out, indicates above mentioned inability - it only still has to be made known to (the next release) of 14.10/4.50

For now this is a categorical no for old clients trying to access v15 LARGE tables, in whichever way or for whatever purpose.
In theory one might argue that things like DROP TABLE, or maybe other DML, could be allowed, but would anyone see any value in this?

BR,

 Andreas

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb

Getting back to your original questions Sebastien:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Yes and yes. Just like in previous versions, old clients connecting to the new server isn't a problem. Nor is new clients connecting to older servers. Distributed queries between V15 and older versions -- all that should be working. The problem you ran into is entirely about large tables vs. small tables. And it should be solvable.

As we discussed earlier in the week, V15 supports both 4-byte and 8-byte rowids. Legacy tables all use 4-byte rowids and are called small tables. Tables that use the new 8-byte rowids have millions of times the capacity and are therefore called large tables. A large rowid may very well refer to page 1,000,000,000,000 in the table.

To an old client that's crazy talk.

Both old and new clients do need to handle rowids in various scenarios. So if we allowed old clients to access large tables they would work only to a point, and that seemed untenable to us. We decided to disallow all access to large tables from old clients.

That said, no one is forced to create or use large tables in V15. The DBA has a lot of control over whether a table is created small or large.

Unless they attempt to explicitly create a large table, as in CREATE LARGE TABLE <table name>...,  old clients will not receive an error about infrastructure incompatibilities when creating a table. Tables they create will always be small.

New clients will create large tables by default, but that behavior can be changed using several methods. For example they can use the 'small' keyword in the create table statement:

CREATE SMALL TABLE <table name>...

They can also set a session-level environment variable like so:

SET ENVIRONMENT TABLE_SIZE 'SMALL';

CREATE TABLE <table name>...

The bottom line is that you can use only small tables forever if you want. You'll still get chunks up to 8 exabytes and page sizes up to 256k and all the other V15 features, but thanks to the 4-byte rowid, small tables remain subject to two limits:

1. An individual table fragment can have a maximum of 16,777,215 pages.
2. Each data page in the table can store a maximum of 255 rows.

Perhaps some customers will migrate a database in-place and continue to use an existing application with its legacy small tables indefinitely, while developing new applications to work with large tables. Or they will recompile some applications specifically to take advantage of large tables. We're hoping the design is flexible enough to keep everyone happy while also offering much greater capacity. But we have our ears to the ground. If we can make the experience of moving to v15 smoother we will definitely try.

We'll also review our docs to see where we need to add more of this kind of info.

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb

Hi,

There are two things I noticed from Carlton's presentation.

-  All In-place alters HAVE to be resolved before migration

- ALl secondaries are lost on upgrade and have to rebuilt

Will these limitations be removed in a modpack/fixpack?

Regards,

David.

Getting back to your original questions Sebastien:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Yes and yes. Just like in previous versions, old clients connecting to the new server isn't a problem. Nor is new clients connecting to older servers. Distributed queries between V15 and older versions -- all that should be working. The problem you ran into is entirely about large tables vs. small tables. And it should be solvable.

As we discussed earlier in the week, V15 supports both 4-byte and 8-byte rowids. Legacy tables all use 4-byte rowids and are called small tables. Tables that use the new 8-byte rowids have millions of times the capacity and are therefore called large tables. A large rowid may very well refer to page 1,000,000,000,000 in the table.

To an old client that's crazy talk.

Both old and new clients do need to handle rowids in various scenarios. So if we allowed old clients to access large tables they would work only to a point, and that seemed untenable to us. We decided to disallow all access to large tables from old clients.

That said, no one is forced to create or use large tables in V15. The DBA has a lot of control over whether a table is created small or large.

Unless they attempt to explicitly create a large table, as in CREATE LARGE TABLE <table name>...,  old clients will not receive an error about infrastructure incompatibilities when creating a table. Tables they create will always be small.

New clients will create large tables by default, but that behavior can be changed using several methods. For example they can use the 'small' keyword in the create table statement:

CREATE SMALL TABLE <table name>...

They can also set a session-level environment variable like so:

SET ENVIRONMENT TABLE_SIZE 'SMALL';

CREATE TABLE <table name>...

The bottom line is that you can use only small tables forever if you want. You'll still get chunks up to 8 exabytes and page sizes up to 256k and all the other V15 features, but thanks to the 4-byte rowid, small tables remain subject to two limits:

1. An individual table fragment can have a maximum of 16,777,215 pages.
2. Each data page in the table can store a maximum of 255 rows.

Perhaps some customers will migrate a database in-place and continue to use an existing application with its legacy small tables indefinitely, while developing new applications to work with large tables. Or they will recompile some applications specifically to take advantage of large tables. We're hoping the design is flexible enough to keep everyone happy while also offering much greater capacity. But we have our ears to the ground. If we can make the experience of moving to v15 smoother we will definitely try.

We'll also review our docs to see where we need to add more of this kind of info.

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb

Getting back to your original questions Sebastien:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Yes and yes. Just like in previous versions, old clients connecting to the new server isn't a problem. Nor is new clients connecting to older servers. Distributed queries between V15 and older versions -- all that should be working. The problem you ran into is entirely about large tables vs. small tables. And it should be solvable.

As we discussed earlier in the week, V15 supports both 4-byte and 8-byte rowids. Legacy tables all use 4-byte rowids and are called small tables. Tables that use the new 8-byte rowids have millions of times the capacity and are therefore called large tables. A large rowid may very well refer to page 1,000,000,000,000 in the table.

To an old client that's crazy talk.

Both old and new clients do need to handle rowids in various scenarios. So if we allowed old clients to access large tables they would work only to a point, and that seemed untenable to us. We decided to disallow all access to large tables from old clients.

That said, no one is forced to create or use large tables in V15. The DBA has a lot of control over whether a table is created small or large.

Unless they attempt to explicitly create a large table, as in CREATE LARGE TABLE <table name>...,  old clients will not receive an error about infrastructure incompatibilities when creating a table. Tables they create will always be small.

New clients will create large tables by default, but that behavior can be changed using several methods. For example they can use the 'small' keyword in the create table statement:

CREATE SMALL TABLE <table name>...

They can also set a session-level environment variable like so:

SET ENVIRONMENT TABLE_SIZE 'SMALL';

CREATE TABLE <table name>...

The bottom line is that you can use only small tables forever if you want. You'll still get chunks up to 8 exabytes and page sizes up to 256k and all the other V15 features, but thanks to the 4-byte rowid, small tables remain subject to two limits:

1. An individual table fragment can have a maximum of 16,777,215 pages.
2. Each data page in the table can store a maximum of 255 rows.

Perhaps some customers will migrate a database in-place and continue to use an existing application with its legacy small tables indefinitely, while developing new applications to work with large tables. Or they will recompile some applications specifically to take advantage of large tables. We're hoping the design is flexible enough to keep everyone happy while also offering much greater capacity. But we have our ears to the ground. If we can make the experience of moving to v15 smoother we will definitely try.

We'll also review our docs to see where we need to add more of this kind of info.

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb

Hello John,

Unfortunately, the lock on the large tables was somewhat exaggerated. The following select also produces this error, although it only queries the API. For example:
execute function task('print partition full', '<partnum of a big table>');
I don't think it makes sense to prohibit this. Support wants to discuss it. That's why there is a feature request: https://ideas.ibm.com/ideas/INFX-I-653
Please vote if you are of the same opinion.

Regards

Andreas

Getting back to your original questions Sebastien:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Yes and yes. Just like in previous versions, old clients connecting to the new server isn't a problem. Nor is new clients connecting to older servers. Distributed queries between V15 and older versions -- all that should be working. The problem you ran into is entirely about large tables vs. small tables. And it should be solvable.

As we discussed earlier in the week, V15 supports both 4-byte and 8-byte rowids. Legacy tables all use 4-byte rowids and are called small tables. Tables that use the new 8-byte rowids have millions of times the capacity and are therefore called large tables. A large rowid may very well refer to page 1,000,000,000,000 in the table.

To an old client that's crazy talk.

Both old and new clients do need to handle rowids in various scenarios. So if we allowed old clients to access large tables they would work only to a point, and that seemed untenable to us. We decided to disallow all access to large tables from old clients.

That said, no one is forced to create or use large tables in V15. The DBA has a lot of control over whether a table is created small or large.

Unless they attempt to explicitly create a large table, as in CREATE LARGE TABLE <table name>...,  old clients will not receive an error about infrastructure incompatibilities when creating a table. Tables they create will always be small.

New clients will create large tables by default, but that behavior can be changed using several methods. For example they can use the 'small' keyword in the create table statement:

CREATE SMALL TABLE <table name>...

They can also set a session-level environment variable like so:

SET ENVIRONMENT TABLE_SIZE 'SMALL';

CREATE TABLE <table name>...

The bottom line is that you can use only small tables forever if you want. You'll still get chunks up to 8 exabytes and page sizes up to 256k and all the other V15 features, but thanks to the 4-byte rowid, small tables remain subject to two limits:

1. An individual table fragment can have a maximum of 16,777,215 pages.
2. Each data page in the table can store a maximum of 255 rows.

Perhaps some customers will migrate a database in-place and continue to use an existing application with its legacy small tables indefinitely, while developing new applications to work with large tables. Or they will recompile some applications specifically to take advantage of large tables. We're hoping the design is flexible enough to keep everyone happy while also offering much greater capacity. But we have our ears to the ground. If we can make the experience of moving to v15 smoother we will definitely try.

We'll also review our docs to see where we need to add more of this kind of info.

Hello,

Simple questions:

• Can I use a CSDK 4.50.* to connect to an Informix 15 server?
• Can I use a CSDK 15 to connect to an Informix 14 server?

Is there some compatibility matrix available?

I have just face the following:

1) With CSDK 15 connected to Informix 15 server:
CREATE TABLE tab1 ( pkey BIGSERIAL(5000000000) NOT NULL, name VARCHAR(50) )
INSERT ...
INSERT ...
UPDATE ...
SELECT ...

2) The using CSDK 4.50 connected to Informix 15 server:

> drop table tab1;

  214: Cannot remove file for table (sf.tab1).

Any idea why this happens?

Are such restrictions documented somewhere?

Seb