Stored procedures are implemented by ICMEDA-3290 : dramatically improve the performance of configuration expansion operations across slow networks.
A stored procedure is a faster way of executing complex functions. Rather than process many SQL statements between the client and server, the client sends a single command and the configuration is expanded directly on the server for maximum performance.
Please note that stored procedures are compatible with 5.1.56 or higher version of mysql. If you need help upgrading your mysql (on all slaves and masters) please contact email@example.com.
When you upgrade your client from versions earlier than 15700 to a version later than 15700 and run icmpm for the first time you will receive the following warning:
"Missing server-side optimizations, Run makePmTables to create them"
However, before running makePmTables, the server needs to be configured via a GRANT command to create the appropriate permissions:
You must log onto the mysql server as root.
mysql -u root -h <ICM server hostname>
Execute the following GRANT,
mysql> GRANT ALL ON pmWork.* to 'icmAdmin'@'%' identified by '<icmAdmin mysql password>';
<icmAdmin mysql password> here is the "mysql.password" value from your pm configuration file.
The default password is 'icmAdmin'. So, if you are running with the default, the GRANT would be:
mysql> GRANT ALL ON pmWork.* to 'icmAdmin'@'%' identified by 'icmAdmin';
Next, run makePmTables.
If successful, it will create the pmWork database.
Successfully created Version 12.18 tables for icmpm
If it fails, please review the above sequence.
If you have any mysql slaves on your proxy servers, you will want to prevent the pmWork database from replicating to them. In /etc/my.cnf on the proxy server, add the following line in the [mysqld] section:
Then restart mysql on the proxy:
Optional setup for slaves:
If you wish to improve "describe configuration" performance on slaves then please follow the following two steps for each slave:
1) Log in mysql on the slave and grant permissions on pmWork db as in the example above
2) use makePmTables with slave <host> and <port> to create pmWork db on the slave:
>makePmTables -s <host>:<port>
1) If you have mysql version 5.0.45 you must upgrade to 5.1 or 5.5 For instructions please contact firstname.lastname@example.org
2) Add the following statement to /etc/my.cnf file's [mysqld] section:
log_bin_trust_function_creators = 1