-710: Table <table> has been dropped, altered, or renamed.

During a recent meet-up with some IBM developers, we brought up the topic of error -710: Table <table> has been dropped, altered, or renamed. This error usually occurs when a prepared SQL statement has been invalidated by a schema change of some kind and can make code deployment or adding indices difficult in a live environment while applications are running.

IBM told us they have made significant progress with this problem. Firstly, in version 11.10, the onconfig parameter AUTO_REPREPARE was added which facilitated the automatic re-preparing of an already prepared SQL statement internally by the Informix engine if it had been invalidated by a schema change. Unfortunately this fix didn’t cover all scenarios, something IBM were open about at the time and there is an article in the IDS Experts blog on developerWorks covering the progress that was made then.

However, IBM now tell us they have made further progress on this problem to the point now where the -710 error should occur less frequently, if at all.

The developerWorks article describes six scenarios where a -710 error could occur in Informix 10.00 or earlier and the first three of these scenarios are fixed in Informix 11.10 when AUTO_REPREPARE is switched on. I thought it would be useful to create a test case for each of these and see if the problem has indeed been resolved.

The article gives more information but the six scenarios are:

  1. An index created on a table where a select statement has already been prepared and executed will cause that select statement to fail when it is executed again.
  2. A similar scenario where the select statement has not been previously used, a cursor is used and the select uses select *.
  3. A more complex example involving two tables, one with a trigger, and two procedures for which some code is supplied.
  4. Changing the number and type of columns in a select statement.
  5. Executing a prepared DDL statement, presumably where other DDL has been run on the table.
  6. An unspecified race condition.

All of this, apart from the last item, the race condition, is fairly easy to script up and test, although one slight caveat is that I prefer Perl-DBI and know little about languages like ESQL/C so I don’t have explicit control of cursors.

Thanks to a chum, I got hold of a copy of Informix 10.00.FC10 and ran through tests one to five:

sandbox_shm thompsonb@informix1:~ > ./710_test_cases.pl all

Running test 1: $prepare s1 from "select c1, c2 from t710";$execute s1;$create index i1 on t710(c1);$execute s1; -------> -710 error

Dropping existing table 't710':
done.
Creating table 't710':
done.
Inserting some data into table 't710':
done.
Selecting values:

c1 c2
10 10
20 20
30 30
56 56

Creating index:
done.
Re-using prepared statement to select values:

c1 c2
DBD::Informix::st execute failed: SQL: -710: Table (thompsonb.t710) has been dropped, altered or renamed. at ./710_test_cases.pl line 80.
Closing statement handle:
done.

Running test 2: $prepare s1 from "select * from t710 where c1 = 10";$declare curs1 cursor for s1;$create index i1 on t710(c1);$open curs1; -------> -710 error

Dropping existing table 't710':
done.
Creating table 't710':
done.
Inserting some data into table 't710':
done.
Preparing select statement:
done.
Creating index:
done.
Using prepared statement to select values:

c1 c2
DBD::Informix::st execute failed: SQL: -710: Table (thompsonb.t710) has been dropped, altered or renamed. at ./710_test_cases.pl line 113.
Closing statement handle:
done.

Running test 3: create procedure p1(c_a int, c_b int) returning integer;insert into A values(1001, 1001);update A set b=c_b where A.a=c_a; ==>(You have an update trigger defined on A which inserts into table B return 0;end procedure; create procedure p2() returning integer;define i integer;let i=p1(56, 56);create index i1 on B(b);return p1(56, 56); -- > -710 error when p1 is executedend procedure;

Dropping existing table 'a':
done.
Creating table 'a':
done.
Inserting some data into table 'a':
done.
Dropping existing table 'b':
done.
Creating table 'b':
done.
Inserting some data into table 'b':
done.
Dropping existing procedure 'p1':
done.
Creating procedure 'p1':
done.
Dropping existing procedure 'p2':
done.
Creating procedure 'p2':
done.
Creating update trigger on table 'a' which inserts into table 'b':
done.
Preparing statement to execute procedure 'p2':
done.
Executing procedure 'p2':
DBD::Informix::st fetch failed: SQL: -710: Table (thompsonb.b) has been dropped, altered or renamed. at ./710_test_cases.pl line 167.
done.
Closing statement handle:
done.

Running test 4: The number and type of columns in your SELECT list have changed.

Dropping existing table 't710':
done.
Creating table 't710':
done.
Inserting some data into table 't710':
done.
Preparing select statement from table 't710':
done.
Adding extra column to table 't710':
done.
Selecting from table 't710' using prepared statement:

c1 c2
DBD::Informix::st execute failed: SQL: -710: Table (thompsonb.t710) has been dropped, altered or renamed. at ./710_test_cases.pl line 195.
done.
Closing statement handle:
done.

Running test 5: If you are executing a prepared DDL statement, you might get -710 errors.

Dropping existing table 't710':
done.
Creating table 't710':
done.
Inserting some data into table 't710':
done.
Prepare a statement to add column 'c3' to table 't710':
done.
Add a column 'c4' to table t710:
done.
Execute the prepared statement to add column 'c3' to table 't710':
DBD::Informix::st execute failed: SQL: -710: Table (thompsonb.t710) has been dropped, altered or renamed. at ./710_test_cases.pl line 227.
done.
Closing statement handle:
done.

Exiting cleanly.

Great (in a way): all the test cases give a -710 error in Informix 10.00! I’ve put my other results in a table; a fail indicates that I got the -710 error:

Test Informix 10.00.FC10 Informix 11.50.FC9W2 without AUTO_REPREPARE Informix 11.50.FC9W2 with AUTO_REPREPARE Informix 11.70.FC5W1 without AUTO_REPREPARE Informix 11.70.FC5W1 with AUTO_REPREPARE
1 Fail Fail Pass Fail Pass
2 Fail Fail Pass Fail Pass
3 Fail Fail Pass Fail Pass
4 Fail Fail Pass Fail Pass
5 Fail Fail Fail Fail Fail

Unfortunately I do not have Informix 11.10 to test with and it is no longer available for download. It would be interesting to know whether it passes test 4 or not with AUTO_REPREPARE set.

Is the failure of test 5 by the latest releases important? I would say not. Preparing a DDL statement, then preparing and executing another on the same table before executing the first prepared statement is certainly not something I’ve seen done before in a real environment.

If anyone would like to have a go at reproducing my results or has any comments on my implementation of the test cases, I have posted the code here.


Huge pages

When upgrading to version 11.70, it’s good to review the list of new features you can take advantage of. IBM has helpfully produced a technical white paper for this purpose. One of these features is huge (or large) pages on Linux, of benefit if your system has a large amount of memory allocated to Informix. The primary advantage is a reduction in the size of the (separate) pages tables used by processes and the system to map to physical memory.

In Linux huge pages were first supported by the 2.6 kernel and this feature was later back-ported to 2.4.21, although the implementation is not the same. This blog post mostly concerns itself with how huge pages work in x86_64 Linux 2.6 kernels although I’ll try and point out where any differences may lie in other implementations.

You can have a look at how much space is allocated to page tables in your system by looking in /proc/meminfo at parameter PageTables. For example I have an 40 Gb Informix instance running on a server with 128 Gb of memory and page table entries totalling 1004 Mb (nearly 1 Gb) are needed to support the system.

Standard pages are 4 kb and point to a block of physical memory. In fact each page has a separate entry in a process page table, which then maps to separate system page table which in turn maps to physical memory. These page tables can contain a mix of standard and huge pages. By using huge pages, the block size increases to 2 Mb on x64_64 (16 Mb on Power PC, 256 Mb on Itanium and 1 Mb on System z). My 40 Gb instance would need 10,485,760 page table entries to support it using standard pages but just 20,480 entries using huge pages. A page table entry can be up to 64 bytes.

In fact the gains are even better than this because modern CPUs use a Translation Lookaside Buffer (TLB) to cache the page tables and these are of a fixed size, typically able to hold a few thousand entries. There is a good Wiki article that explains this in more detail. Page tables containing lots of standard pages therefore lead to more TLB misses where the operating system has to fetch other parts of the page table from system memory.

Huge pages are not used system-wide. Your system administrator must allocate an area of memory to huge pages as follows:

sysctl -w vm.nr_hugepages=<no. of huge pages>

Note that <no. of huge pages> is the number of pages and not in Mb. On 2.4 kernels the parameter is vm.hugetlb_pool.

It may be necessary to reboot your server so that Linux can allocate the memory.

For an Informix instance or set of instances running on a server, a sensible size would be the total memory footprint of all the instances. This can be easily obtained by running onstat –. You might also want to allocate some space for dynamic memory allocations too, although these can use standard pages if no huge page are available.

On Linux (and Solaris) Informix will automatically use huge pages if enough huge pages have been allocated and where the RESIDENT flag is set in onconfig to -1 or to a high enough value to cover your segments. You can control this with the IFX_LARGE_PAGES environment variable.

It’s important to understand that huge pages can only be used by processes that support them and cannot be swapped out so you need to leave normal pages for the operating system and any other processes.

On start-up, the server will put a message in the online log to show that huge pages are being used:

10:00:00 IBM Informix Dynamic Server Started.
10:00:00 Shared memory segment will use huge pages.
10:00:00 Segment locked: addr=0x44000000, size=39520829440
10:00:00 Shared memory segment will use huge pages.
10:00:00 Segment locked: addr=0x977a00000, size=42949672960

You might expect that onstat -g seg would then show you that huge pages are in use for a given segment but this is not the case.

What happens if the server needs to allocate an extra virtual segment? As usual the value of SHMADD will determine the size of the segment and Informix will check to see if there are sufficient huge pages available for it. If not, it will use normal pages and a message like the below will appear in the online log:

10:30:00 Warning: Server is unable to lock huge pages in memory.
Switching to normal pages.
10:30:00 Dynamically allocated new virtual shared memory segment (size 1048576KB)
10:30:00 Memory sizes:resident:38594560 KB, virtual:43013440 KB, no SHMTOTAL limit
10:30:00 Segment locked: addr=0x1378f50000, size=1073741824

You can monitor huge page use, again using /proc/meminfo:

> cat /proc/meminfo | grep HugePages
HugePages_Total: 40960
HugePages_Free:   2518
HugePages_Rsvd:    883

Comparing this with the output from onstat -g seg I have:

Segment Summary:
id         key        addr             size             ovhd     class blkused  blkfree 
38404103   52bb4801   44000000         39520829440      463568504 R*    9648205  435     
38436872   52bb4802   977a00000        42949672960      503318520 V*    8793768  1691992 
Total:     -          -                82470502400      -        -     18441973 1692427

No obvious relationship? I know from the online log that both the virtual and resident segments are using huge pages. If we take the total huge pages, subtract the free and add the reserved, we get (40960 - 2518 + 883) = 39325 pages. If we convert that into bytes: (39325 * 2048 * 1024), we get 82470502400 which is the total size of the two segments.