When do my stored procedure execution plans get updated?

For the sake of brevity in this article I am going to group procedures, functions and routines together as stored procedures and ignore any differences between them.

What does the SQL command UPDATE STATISTICS FOR PROCEDURE/FUNCTION/ROUTINE does and perhaps, more pertinently, as a DBA do I need to run this regularly to ensure my systems are working efficiently? For those wanting an immediate answer I think it is “never” or “almost never“, the reasons for which I hope to explain clearly in this article.

The command itself is straightforward: calling it causes Informix to parse a stored procedure’s code and produce a query plan for all of it based on the current statistics and data distributions (if the procedure references any tables). It then writes the query plan to the sysprocplan table which is, unless you have an unlogged database, a logged operation written to the logical log. Used with no parameters it does this for all stored procedures in the system.

As long as the query plan in sysprocplan is reasonably efficient there is probably no need to (ever) proactively update it but there may be cases when you’d want to do so, for example, if a very small or empty table has grown into a large one. However if you were to do this your new plan would be based on the current table statistics and data distributions and if these haven’t been updated yet you may get the same, now inefficient, plan.

The manual states:

The sysprocplan system catalog table stores execution plans for SPL routines. Two actions can update the sysprocplan system catalog table:

  • Execution of an SPL routine that uses a modified table
  • The UPDATE STATISTICS FOR ROUTINE, FUNCTION, or PROCEDURE statement.

There is a created column in the sysprocplan table but it’s a date and not a date/time which makes it much harder to match plan updates to other events.

So what is a modified table? Quite simply it is one where the version number has been incremented. You can see the version number with an SQL query like:

select version from systables where owner='myowner' and tabname='mytable';

I think the only reference to this in manual is in section about the systables view where it simply says:

version
INTEGER
Number that changes when table is altered

How the engine works out the dependencies a stored procedure has on different tables falls into the category of system internals, which IBM chooses not to publicly document, but I think it’s safe to say that if a table is referenced anywhere in a procedure it is dependent on it.

There are many ways a table can be “altered”, some more obvious than others:

Method Version number incremented by
GRANT SELECT 1
GRANT UPDATE 1
GRANT SELECT 1
GRANT SELECT, UPDATE 1
UPDATE STATISTICS HIGH 2
UPDATE STATISTICS MEDIUM 2
UPDATE STATISTICS [LOW] 2
RENAME COLUMN 65536
CREATE INDEX 65536
ADD column 131072
DROP column 131072
GRANT CONNECT 0
GRANT RESOURCE 0
GRANT DBA 1

I am not sure why some operations increment the value by large numbers, all powers of 2, as any increment has a similar effect, at least as far as the scope of this article is concerned.

The table is not a complete list because there are many possible DDL operations but this does already illustrate or suggest that:

  • On most systems it’s likely that UPDATE STATISTICS commands will be the main trigger for stored query plans to be updated. If you run LOW, HIGH and MEDIUM modes for a table like you will if you use AUS or dostats, you’ll trigger at least three updates for dependent stored procedures (if they are called).
  • If we want to grant multiple privileges on the same table, it’s best to do it in a single statement because if a dependent stored procedure is being called in between running commands by an application, its stored execution plan will be updated only once.
  • GRANT DBA is not a table level operation yet it has an effect.

Further testing shows that both the GRANT DBA and REVOKE DBA statements increment the version number on all tables in the same database where the tabid is 100 or greater, that is all user tables. From the manual above it follows that the stored query plans for all stored procedures or functions dependent on a table will be updated the next time they are executed.

On our systems we see a large amount of writes to sysprocplan after granting or revoking the DBA privilege to anyone. When graphed we see a sharp peak and a long exponential tail off as less commonly used procedures get called.

Therefore if you grant DBA to a user on a busy live system, it can affect concurrency more than you might expect. On an idle system you may want to run UPDATE STATISTICS FOR PROCEDURE immediately afterwards to update the stored query plans in an orderly way and save the first session to call any given procedure from this overhead.

I think running the command offline to avoid the overhead for user or application sessions is possibly the only true use case for this command.

Advertisements

Intermittent “CSM: authentication error” with JDBC

This article will only concern you if:

  • you connect using JDBC.
  • you use simple password encryption.

Simple password encryption just does one thing: it encrypts the password sent to the database server in transit preventing it from being obtainable by network packet sniffing.

At the client end configuring simple password encryption can be done simply by adding
";SECURITY=PASSWORD"
to your JDBC connection string.

At the server end set up a DBSERVERALIAS and add
csm=(SPWDCSM)
to the fifth field in sqlhosts and set environment variable INFORMIXCONCSMCFG before starting the instance to point to a file containing something like:
SPWDCSM("/opt/informix/lib/csm/libixspw.so", "", "p=1")

This is covered in more detail elsewhere and I haven’t covered using CSDK but for JDBC connections it’s all there is to know.

Unfortunately there is a bug in JDBC 3.70.JC8W1 and JDBC 4.10.JC7 and below where every 100th connection attempt or so will fail randomly with this stack:

java.sql.SQLException: CSM: authentication error.
at com.informix.jdbc.IfxSqliConnect.(IfxSqliConnect.java:1337)
at sun.reflect.GeneratedConstructorAccessor3.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.informix.jdbc.IfxDriver.connect(IfxDriver.java:243)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:270)
at Connect.main(Connect.java:30)
Caused by: com.informix.asf.IfxASFRemoteException:
at com.informix.asf.Connection.recvBindResponse(Connection.java:1363)
at com.informix.asf.Connection.establishConnection(Connection.java:1619)
at com.informix.asf.Connection.(Connection.java:392)
at com.informix.jdbc.IfxSqliConnect.(IfxSqliConnect.java:1232)
... 7 more

You can see if you’re vulnerable by compiling this app and running it until it fails or you’re reasonably confident you don’t have a problem:

public class Connect
{
public static void main(String[] args)
{
Connection conn = null;
String url = "jdbc:informix-sqli://hostname:port/dbname:INFORMIXSERVER=informixserver;user=user;password=password;SECURITY=PASSWORD";
System.out.println(url);

try
{
Class.forName("com.informix.jdbc.IfxDriver");
}
catch (Exception e)
{
System.out.println("FAILED to load Informix JDBC driver.");
e.printStackTrace();
return;
}

int i=0;
while (true) {
i++;
try
{
conn = DriverManager.getConnection(url);
}
catch (SQLException e)
{
System.out.println("FAILED to connect! "+e);
e.printStackTrace();
}
System.out.println("Connected " + i);
if (conn != null) {
try {
conn.close();
}
catch (SQLException e) {
System.out.println("FAILED to disconnect! "+e);
e.printStackTrace();
}
}
}
}
}

If your application handles fails connections and retries automatically you might not have noticed this error or perhaps it was lost in the noise but for more simple applications it can be a pain.

Fortunately this is fixed in JDBC 4.10.JC8 and the fix works with 11.70 and 12.10 versions of the servers.

Interestingly the JDBC release notes for 4.10.JC8 are coy about this, showing just one fix.


-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.


Constrained by constraints

Spending most of my day, as I do, working for the beast (Oracle), it’s not so often I get chance to do any decent Informix work, let alone a bit of coding. So today I spotted an opportunity to brush up on my SPL skills after coming across this error:

892: Cannot disable object (informix.mytableuk01) due to other active objects using it.

As the system was in single user mode at the time because I was doing some maintenance work, this seemed rather puzzling.

The offending code was:

begin;
set contraints for mytable disabled;

This command should have disabled all constraints on table mytable and also any referencing constraints. For some reason this did not work.

I began by looking at the output from dbschema and worked my way through but pretty soon I realised that there were an awful lot of dependencies on this table.

So I came up with this function which finds them in no time and may prove useful for someone else:

create function check_fk_dependencies (in_tabname varchar(254), in_level smallint default 0)
returning varchar(254)

define p_referenced varchar(254);
define p_output varchar(254);
define p_loop_check varchar(254);
define p_level smallint;
define p_temp_check varchar(254);

on exception in (-206) end exception; -- ignore error on drop of temporary table if already present in session

let p_referenced = NULL;
let p_level = in_level + 1;

if in_level = 0 then
  drop table fk_loop_check;
  create temp table fk_loop_check (
    tabname varchar(254),
    parent varchar(254)
  ) with no log;
end if;

foreach
select p.tabname
  into p_referenced
  from sysreferences s, systables r, sysconstraints c, systables p
  where p.tabid = c.tabid and s.ptabid = r.tabid and s.constrid = c.constrid and p.tabid != r.tabid and r.tabname = in_tabname

  select tabname
    into p_loop_check
    from fk_loop_check
    where tabname = p_referenced and parent = in_tabname;

  if p_loop_check is null then
    insert into fk_loop_check values (p_referenced, in_tabname);
    foreach execute procedure check_fk_dependencies (p_referenced, p_level) into p_output
      return in_tabname || ' -> ' || p_output with resume;
    end foreach;
  end if;

end foreach;

if p_referenced is null then
  return in_tabname;
end if;

end function;

And there we have it! A function that will show all the dependencies and on which tables you need to disable constraints. It can cope with circular dependencies – that is what the temporary table is used for.