SQL locks entire table on INSERT?

["Patrick Townsend" <patownsend@xxxxxxxxxxxxxx>]

Here's an odd one. I have a C application on the AS/400 that uses the SQL
CLI interface to insert records into a database. The application uses
commitment control. For performance reasons the application does the
SQLConnect and binds parameters at start up, and then sits on a data queue
waiting for work to do. When there is work to do data is formatted and
INSERTed into the database. The number of records inserted varies, but is
usually not many (less than a dozen or so). From watching the locks on the
files into which records are inserted, and from watching the performance of
the jobs, it looks like only one process can insert data at a time. That is,
it seems like a process has an exclusive lock on the entire table during the
update process. Other processes that may be running simultaneously seem to
go into lock-wait until they can get an exclusive lock. Almost like a
single-user database! I can understand that the records that are inserted
under commitment control would be locked, but I can't understand why the
entire table would be locked. Any thoughts on this would be appreciated.

Patrick