A problem was encountered when trying to use a Serial Data Type with IDS 9.4 and WebSphere V5. The application needed to have a unique number generated for the primary key field as new records were inserted into IDS. However, WebSphere looks for a primary key value of "0" for a new record. To bypass this problem Sequence Objects were used instead of the Serial Data Type.
In the IBM Redbook Using Informix Dynamic Server with WebSphere, SG24-6948, a sample application was developed that uses JSPs, html pages, servlets, entity-driven beans and message-driven beans. The application is used to query, insert, and update data on Informix Dynamic Server (IDS). Data insertion is performed through an entity bean, and, in our case, a container managed persistence bean. That way the data is inserted is through a primary key and the data insertion is performed on the customers table that has customer_num as a primary key. This column was defined as a serial data type, and caused a problem when using the entity bean. For serial data types there are two ways to insert data:
1. Insert value 0, so the system generates the next unique value for the column.
2. Specify a unique number in the column.
The first option is the simplest one since many applications may be accessing the server and inserting data into the table. The problem with this is that the conversion of value 0 to the next unique integer number is performed on the database server. So after inserting the new value the entity bean has to query this row and update the other columns. This did not work for our application because WebSphere was looking for a primary key of value 0 instead of the unique number generated by IDS.
The solution for this problem is to use sequence objects. This database object was introduced with IDS 9.40 and is described in Chapter 2 of the previously mentioned redbook. A sequence object generates unique numbers on the server, so we used this second approach to insert serial values. First we create a sequence object using dbaccess:
create sequence custnum start 150 increment 1
The command above creates a sequence object that starts with value 150 and increments it by 1.
Note: In our case we only used the stores_demo database for the sample applications so the first record inserted has a customer_num of 150.
Now that we have our sequence object we can determine the next unique value. In our application we run the following SQL statement before an insert:
select custnum.nextval from systables where tabid=1
This statement returns the unique value that we can specify during the insertion of a new customer record. By doing this we avoided the problem caused by the serial data type.
This material has not been submitted to any formal IBM test and is published AS IS. It has not been the subject of rigorous review. IBM assumes no responsibility for its accuracy or completeness. The use of this information or the implementation of any of these techniques is a client responsibility and depends upon the client's ability to evaluate and integrate them into the client's operational environment.