Install Genero db and create a database - database configuration/design tasks

  1. Install Genero db on the computer. By default, Genero db is configured to run alone on a networked machine, having client applications hosted on other machines. If you are tasked with installing and configuring the Genero db database, here are some configuration parameters to be examined or changed:
    1. If applications and the database server are co-located on the same machine:
      • By default, Genero db uses nearly all the memory available on the computer. To share the memory with applications, you must change the MEMORY_OVERRIDE parameter in $ANTSHOME/Server/config.txt.
      • If memory is not a concern and if the number of connections is about 50, you may want to try the networking=IPC communication protocol in the data source definition to get better performance. Over 50 simultaneous connections, you should use the default TCP-socket protocol.
    2. For production use, a caching RAID controller on which to do logging is highly recommended. For maximum data integrity, LOG_MODE should be set to DURABLE, and LOGPATH should point to a different disk than the disk containing data. If LOGPATH does not point to a disk managed by a RAID controller, LOG_MODE should be set to OSDURABLE. See the Genero db documentation for more details.
    3. By default, Genero db relies on local area network multicast to enable clients and servers to find each other. If you have a firewall, you must allow UDP connections on port 12345 for the multicast address 255.0.0.37. If you want to disable multicast search: on the server side, set MULTICAST_ENABLED=FALSE in $ANTSHOME/Server/config.txt. On the client side, for UNIX™ platforms, you must set overridebroadcast=yes in the ODBC data source definition. For Windows™ platforms, check the "Override Multicast" option in the ODBC data source configuration (click the "Advanced" button, then click "Connection Method" in the "Networking" section).
    4. If you are running Genero db 3.80 or higher, you must set the server configuration parameter COMPATIBILITY_MODE=INFORMIX. For details about the SQL features affected by this parameter, see the Genero db Migration Guide. For details about disabling the compatibility mode check , see the FGLPROFILE configuration settings described in Prepare the runtime environment - connecting to the database.
  2. Set up an ODBC data source, called mydb. The default ODBC data source settings must be modified:
    1. Set overridebroadcast=yes if you don't need multicast, as described in Step 1.
    2. During the installation of Genero db, the data source might have been created with the login and password of the SYSTEM user. If you leave these default user and password entries, anyone can connect to the database as the user SYSTEM (a superuser). You must clean the user and password ODBC parameters. This will force client programs to specify a login and password to connect to the database.
    3. By default, the Genero db client reconnects automatically to the server if the connection is lost. This is a useful feature as long as the SQL session is stateless. However, if you create temporary tables or if you change session parameters with ALTER SESSION, the session context will be silently lost if a reconnect occurs. To get an SQL error when DB connection is lost, you can deny the client to reconnect by setting automaticfailover=no in the ODBC parameters. The Genero db client does not reconnect automatically if a transaction was started. In such case the program gets and SQL error.
    4. Typical Genero applications connect only once to the database server. By default, the Genero db client uses connection aggregators, which reuse connection resources and server bindings for new connections. This is useful when the same client process opens and closes many connections, but is unnecessary overhead in typical Genero applications. Additionally, using aggregation implies multithreading. UNIX signal handling are not thread safe. Since the runtime system uses the UNIX signal() function, you must disable aggregation by setting noaggregators=0 in the ODBC data source definition. On Windows platforms, the noaggregators property can only be changed with the registry editor, under the ODBC.INI key.
    5. The Genero db client library maintains a statement pool by default. When you FREE a cursor or statement in your programs, the underlying ODBC statement handle structure goes to the statement pool for future reuse. You can disable the ODBC statement pool by setting the disableStmtPool ODBC.INI parameter to Yes. When this parameter is set, fetch buffers are freed as well an the process consumes less memory.
    6. Set the correct database client character set with the characterset parameter. This character set must match the character set used by the locale of the runtime system (LANG/LC_ALL).
    7. You may want to set useinfxenvvars to yes to take environment variables such as DBDATE into account regarding date to/from string conversions.
  3. Create a database user dedicated to your application. You can create database users authenticated by a password or use OS authentication. This example creates a user authenticated by a password. See the Genero db Programmer's Guide for more details about OS authentication.
    $ antscmd -d mydb -u SYSTEM -p SYSTEM
    ...
    mydb> CREATE USER appadmin IDENTIFIED BY password;
  4. Grant database privileges to the user:
    mydb> GRANT CREATE SESSION TO appadmin;
    mydb> GRANT CREATE TABLE TO appadmin;
    mydb> GRANT CREATE VIEW TO appadmin;
    mydb> GRANT CREATE SYNONYM TO appadmin;
    mydb> GRANT CREATE PROCEDURE TO appadmin;
    mydb> GRANT CREATE SEQUENCE TO appadmin;
  5. Create the application tables. Do not forget to convert Informix® data types to Genero db data types. See issue Data type conversion table for more details.

    Check for reserved words in your table and column names. See the Genero db SQL Syntax Guide: Appendix B, Reserved Words