Informix

Solaris 10  1/13

Informix 12.10.FC14

I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints.  It was generated by Art's myexport utility.  I copied the intended database (a production database) to another instance for testing, and the script works perfectly.

So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file.  After a few hundred constraints, it produced a 106 error (non-exclusive access).  The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.

I found the command line in the script that it was attempting to execute.  That command line was the first command to reference that table.  (There were several additional ALTER TABLE statements for that table further down in the command script.)

What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script?  (Recall it ran successfully to completion on another instance.)

Here is an excerpt from stderr when the SQL command file was running:

.

.

.

Table altered.

Table altered.

  242: Could not open database table (informix.ofndr_loc_hist).

  106: ISAM error:  non-exclusive access.
Error in line 269
Near character position 35

Table altered.

Table altered.

.

.

.

Thank you for any thoughts.

Regards,

David Grove

------------------------------
David Grove
------------------------------

David:

What was the specific ALTER command that failed? What release of myschema were you running to create the script from myexport (myschema -V)?

Art

Solaris 10  1/13

Informix 12.10.FC14

I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints.  It was generated by Art's myexport utility.  I copied the intended database (a production database) to another instance for testing, and the script works perfectly.

So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file.  After a few hundred constraints, it produced a 106 error (non-exclusive access).  The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.

I found the command line in the script that it was attempting to execute.  That command line was the first command to reference that table.  (There were several additional ALTER TABLE statements for that table further down in the command script.)

What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script?  (Recall it ran successfully to completion on another instance.)

Here is an excerpt from stderr when the SQL command file was running:

.

.

.

Table altered.

Table altered.

  242: Could not open database table (informix.ofndr_loc_hist).

  106: ISAM error:  non-exclusive access.
Error in line 269
Near character position 35

Table altered.

Table altered.

.

.

.

Thank you for any thoughts.

Regards,

David Grove

------------------------------
David Grove
------------------------------

Art,

I mentioned your name to attach credibility to the script-- not to suggest that it is in any way a cause or contributor to the problem.  I believe the problem is due to something I did (or failed to do).  I always appreciate your work and assistance.

In any event, this is the command at which it failed:

ALTER TABLE "informix".ofndr_loc_hist ADD CONSTRAINT CHECK (assgn_dt <= end_dt )

                CONSTRAINT c103_assgn_dt NOVALIDATE;

That same command file ran successfully on another instance, so there must be something about the instance or environment that caused the problem-- not the script, itself.

I'm not sure what version that script is from.  It is over a year old, and I have used it several times.  (I saved the index and constraint command files from a "myexport" run, and used them when I wanted to move all the indexes to a new dbspace.  I would DROP the constraints and indexes, then use your script to regenerate the indexes [in the desired dbspace] and then regenerate the constraints.)  It has worked beautifully several times.  I just hadn't tried it on production before.

DG

David:

What was the specific ALTER command that failed? What release of myschema were you running to create the script from myexport (myschema -V)?

Art

Solaris 10  1/13

Informix 12.10.FC14

I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints.  It was generated by Art's myexport utility.  I copied the intended database (a production database) to another instance for testing, and the script works perfectly.

So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file.  After a few hundred constraints, it produced a 106 error (non-exclusive access).  The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.

I found the command line in the script that it was attempting to execute.  That command line was the first command to reference that table.  (There were several additional ALTER TABLE statements for that table further down in the command script.)

What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script?  (Recall it ran successfully to completion on another instance.)

Here is an excerpt from stderr when the SQL command file was running:

.

.

.

Table altered.

Table altered.

  242: Could not open database table (informix.ofndr_loc_hist).

  106: ISAM error:  non-exclusive access.
Error in line 269
Near character position 35

Table altered.

Table altered.

.

.

.

Thank you for any thoughts.

Regards,

David Grove

------------------------------
David Grove
------------------------------

Solaris 10  1/13

Informix 12.10.FC14

I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints.  It was generated by Art's myexport utility.  I copied the intended database (a production database) to another instance for testing, and the script works perfectly.

So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file.  After a few hundred constraints, it produced a 106 error (non-exclusive access).  The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.

I found the command line in the script that it was attempting to execute.  That command line was the first command to reference that table.  (There were several additional ALTER TABLE statements for that table further down in the command script.)

What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script?  (Recall it ran successfully to completion on another instance.)

Here is an excerpt from stderr when the SQL command file was running:

.

.

.

Table altered.

Table altered.

  242: Could not open database table (informix.ofndr_loc_hist).

  106: ISAM error:  non-exclusive access.
Error in line 269
Near character position 35

Table altered.

Table altered.

.

.

.

Thank you for any thoughts.

Regards,

David Grove

P.S.  I propose to run it again during a future maintenance window, and SET LOCK MODE TO WAIT 10;

------------------------------
David Grove
------------------------------

Solaris 10  1/13

Informix 12.10.FC14

I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints.  It was generated by Art's myexport utility.  I copied the intended database (a production database) to another instance for testing, and the script works perfectly.

So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file.  After a few hundred constraints, it produced a 106 error (non-exclusive access).  The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.

I found the command line in the script that it was attempting to execute.  That command line was the first command to reference that table.  (There were several additional ALTER TABLE statements for that table further down in the command script.)

What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script?  (Recall it ran successfully to completion on another instance.)

Here is an excerpt from stderr when the SQL command file was running:

.

.

.

Table altered.

Table altered.

  242: Could not open database table (informix.ofndr_loc_hist).

  106: ISAM error:  non-exclusive access.
Error in line 269
Near character position 35

Table altered.

Table altered.

.

.

.

Thank you for any thoughts.

Regards,

David Grove

P.S.  I propose to run it again during a future maintenance window, and SET LOCK MODE TO WAIT 10;

------------------------------
David Grove
------------------------------

No HDR, but RSS.  However, I did an 'onmode -d dstandard' on the RS before starting the script on the primary.  IOW, so far as I know, I turned it back to a plain-vanilla server.

I also did an 'EXECUTE FUNCTION task("scheduler shutdown") before running the script.

DG

Solaris 10  1/13

Informix 12.10.FC14

I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints.  It was generated by Art's myexport utility.  I copied the intended database (a production database) to another instance for testing, and the script works perfectly.

So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file.  After a few hundred constraints, it produced a 106 error (non-exclusive access).  The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.

I found the command line in the script that it was attempting to execute.  That command line was the first command to reference that table.  (There were several additional ALTER TABLE statements for that table further down in the command script.)

What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script?  (Recall it ran successfully to completion on another instance.)

Here is an excerpt from stderr when the SQL command file was running:

.

.

.

Table altered.

Table altered.

  242: Could not open database table (informix.ofndr_loc_hist).

  106: ISAM error:  non-exclusive access.
Error in line 269
Near character position 35

Table altered.

Table altered.

.

.

.

Thank you for any thoughts.

Regards,

David Grove

P.S.  I propose to run it again during a future maintenance window, and SET LOCK MODE TO WAIT 10;

------------------------------
David Grove
------------------------------

Solaris 10  1/13

Informix 12.10.FC14

I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints.  It was generated by Art's myexport utility.  I copied the intended database (a production database) to another instance for testing, and the script works perfectly.

So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file.  After a few hundred constraints, it produced a 106 error (non-exclusive access).  The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.

I found the command line in the script that it was attempting to execute.  That command line was the first command to reference that table.  (There were several additional ALTER TABLE statements for that table further down in the command script.)

What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script?  (Recall it ran successfully to completion on another instance.)

Here is an excerpt from stderr when the SQL command file was running:

.

.

.

Table altered.

Table altered.

  242: Could not open database table (informix.ofndr_loc_hist).

  106: ISAM error:  non-exclusive access.
Error in line 269
Near character position 35

Table altered.

Table altered.

.

.

.

Thank you for any thoughts.

Regards,

David Grove

P.S.  I propose to run it again during a future maintenance window, and SET LOCK MODE TO WAIT 10;

------------------------------
David Grove
------------------------------

"Was the database opened in exclusive Mode?"

Would opening in exclusive mode by user 'informix' be different from setting mode to single user (i.e., 'onmode -j')?

DG

Solaris 10  1/13

Informix 12.10.FC14

I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints.  It was generated by Art's myexport utility.  I copied the intended database (a production database) to another instance for testing, and the script works perfectly.

So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file.  After a few hundred constraints, it produced a 106 error (non-exclusive access).  The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.

I found the command line in the script that it was attempting to execute.  That command line was the first command to reference that table.  (There were several additional ALTER TABLE statements for that table further down in the command script.)

What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script?  (Recall it ran successfully to completion on another instance.)

Here is an excerpt from stderr when the SQL command file was running:

.

.

.

Table altered.

Table altered.

  242: Could not open database table (informix.ofndr_loc_hist).

  106: ISAM error:  non-exclusive access.
Error in line 269
Near character position 35

Table altered.

Table altered.

.

.

.

Thank you for any thoughts.

Regards,

David Grove

P.S.  I propose to run it again during a future maintenance window, and SET LOCK MODE TO WAIT 10;

------------------------------
David Grove
------------------------------

Solaris 10  1/13

Informix 12.10.FC14

I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints.  It was generated by Art's myexport utility.  I copied the intended database (a production database) to another instance for testing, and the script works perfectly.

So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file.  After a few hundred constraints, it produced a 106 error (non-exclusive access).  The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.

I found the command line in the script that it was attempting to execute.  That command line was the first command to reference that table.  (There were several additional ALTER TABLE statements for that table further down in the command script.)

What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script?  (Recall it ran successfully to completion on another instance.)

Here is an excerpt from stderr when the SQL command file was running:

.

.

.

Table altered.

Table altered.

  242: Could not open database table (informix.ofndr_loc_hist).

  106: ISAM error:  non-exclusive access.
Error in line 269
Near character position 35

Table altered.

Table altered.

.

.

.

Thank you for any thoughts.

Regards,

David Grove

P.S.  I propose to run it again during a future maintenance window, and SET LOCK MODE TO WAIT 10;

------------------------------
David Grove
------------------------------

Hi David,

onstat -g opn is your friend here, esp. if you know the affected table:

find the table in the output (in 12.10 you might have to go by its partnum (systables) or fragids (sysfragments)), then see which thread(s) is/are having the table open and find their associated session info.

HTH,
 Andreas

Solaris 10  1/13

Informix 12.10.FC14

I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints.  It was generated by Art's myexport utility.  I copied the intended database (a production database) to another instance for testing, and the script works perfectly.

So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file.  After a few hundred constraints, it produced a 106 error (non-exclusive access).  The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.

I found the command line in the script that it was attempting to execute.  That command line was the first command to reference that table.  (There were several additional ALTER TABLE statements for that table further down in the command script.)

What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script?  (Recall it ran successfully to completion on another instance.)

Here is an excerpt from stderr when the SQL command file was running:

.

.

.

Table altered.

Table altered.

  242: Could not open database table (informix.ofndr_loc_hist).

  106: ISAM error:  non-exclusive access.
Error in line 269
Near character position 35

Table altered.

Table altered.

.

.

.

Thank you for any thoughts.

Regards,

David Grove

P.S.  I propose to run it again during a future maintenance window, and SET LOCK MODE TO WAIT 10;

------------------------------
David Grove
------------------------------

Thank you, Andreas.

I'm not sure how to actually apply your suggestion.  I would need to "catch it in the act" so to speak, right?

The script has 4,015 ALTER TABLE statements.  I can execute it using dbaccess (with stdout and stderr redirected to files to catch the output).  When the script encounters an error, it just continues with then next ALTER TABLE statement.  So, I can read the stderr.out file, occasionally, while the script is running.  But, then I only see that an error has occurred after the script has already continued on to subsequent commands in the script.

I suppose I could just let it run to completion, and then manually go through it, checking each error, and count the position within the stderr.out, and then find the corresponding position within the SQL script (to see what ALTER TABLE command failed), and then manually execute the failed commands.  That would probably fix things, but would take time to do this.  Since I interrupted the process after I detected any errors, I don't really know how many there are.  If only a handful, a manual rerun of the affected tables could work.  But, if there are too many, a manual fix could exceed the time available in the maintenance window.

When I test on a duplicate of the production database (created by restoring an ontape level 0 archive to another instance on another machine) there script runs without error.  So, I can't duplicate the problem outside of the production system.

At this time, the problem is not a critical issue, and production is running fine.  I just figure it might run "more fine" by detaching the indexes and putting them in their own "wider" dbspace.

So, unless it becomes an actual performance problem, I think I'll just "let sleeping dogs lie".

Thank you for your suggestion.

DG

Hi David,

onstat -g opn is your friend here, esp. if you know the affected table:

find the table in the output (in 12.10 you might have to go by its partnum (systables) or fragids (sysfragments)), then see which thread(s) is/are having the table open and find their associated session info.

HTH,
 Andreas

Solaris 10  1/13

Informix 12.10.FC14

I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints.  It was generated by Art's myexport utility.  I copied the intended database (a production database) to another instance for testing, and the script works perfectly.

So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file.  After a few hundred constraints, it produced a 106 error (non-exclusive access).  The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.

I found the command line in the script that it was attempting to execute.  That command line was the first command to reference that table.  (There were several additional ALTER TABLE statements for that table further down in the command script.)

What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script?  (Recall it ran successfully to completion on another instance.)

Here is an excerpt from stderr when the SQL command file was running:

.

.

.

Table altered.

Table altered.

  242: Could not open database table (informix.ofndr_loc_hist).

  106: ISAM error:  non-exclusive access.
Error in line 269
Near character position 35

Table altered.

Table altered.

.

.

.

Thank you for any thoughts.

Regards,

David Grove

P.S.  I propose to run it again during a future maintenance window, and SET LOCK MODE TO WAIT 10;

------------------------------
David Grove
------------------------------

David:

Just an FYI: If you include the -e option to dbaccess it will echo the SQL entered to the output along with the error messages. Witness:

$ dbaccess -e testdb -

Database selected.

> select first 2 * from extents;
select first 2 * from extents;


dbsname  sysutils
tabname  sysaggregates
chunk    1
offset   75583
size     8
id       4638

dbsname  sysutils
tabname  sysroleauth
chunk    1
offset   75591
size     8
id       4639

2 row(s) retrieved.

Thank you, Andreas.

I'm not sure how to actually apply your suggestion.  I would need to "catch it in the act" so to speak, right?

The script has 4,015 ALTER TABLE statements.  I can execute it using dbaccess (with stdout and stderr redirected to files to catch the output).  When the script encounters an error, it just continues with then next ALTER TABLE statement.  So, I can read the stderr.out file, occasionally, while the script is running.  But, then I only see that an error has occurred after the script has already continued on to subsequent commands in the script.

I suppose I could just let it run to completion, and then manually go through it, checking each error, and count the position within the stderr.out, and then find the corresponding position within the SQL script (to see what ALTER TABLE command failed), and then manually execute the failed commands.  That would probably fix things, but would take time to do this.  Since I interrupted the process after I detected any errors, I don't really know how many there are.  If only a handful, a manual rerun of the affected tables could work.  But, if there are too many, a manual fix could exceed the time available in the maintenance window.

When I test on a duplicate of the production database (created by restoring an ontape level 0 archive to another instance on another machine) there script runs without error.  So, I can't duplicate the problem outside of the production system.

At this time, the problem is not a critical issue, and production is running fine.  I just figure it might run "more fine" by detaching the indexes and putting them in their own "wider" dbspace.

So, unless it becomes an actual performance problem, I think I'll just "let sleeping dogs lie".

Thank you for your suggestion.

DG

Hi David,

onstat -g opn is your friend here, esp. if you know the affected table:

find the table in the output (in 12.10 you might have to go by its partnum (systables) or fragids (sysfragments)), then see which thread(s) is/are having the table open and find their associated session info.

HTH,
 Andreas

Solaris 10  1/13

Informix 12.10.FC14

I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints.  It was generated by Art's myexport utility.  I copied the intended database (a production database) to another instance for testing, and the script works perfectly.

So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file.  After a few hundred constraints, it produced a 106 error (non-exclusive access).  The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.

I found the command line in the script that it was attempting to execute.  That command line was the first command to reference that table.  (There were several additional ALTER TABLE statements for that table further down in the command script.)

What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script?  (Recall it ran successfully to completion on another instance.)

Here is an excerpt from stderr when the SQL command file was running:

.

.

.

Table altered.

Table altered.

  242: Could not open database table (informix.ofndr_loc_hist).

  106: ISAM error:  non-exclusive access.
Error in line 269
Near character position 35

Table altered.

Table altered.

.

.

.

Thank you for any thoughts.

Regards,

David Grove

P.S.  I propose to run it again during a future maintenance window, and SET LOCK MODE TO WAIT 10;

------------------------------
David Grove
------------------------------