alter dbspace fails if it is scripted

[robert_kratschmann]

Hi,

I start the server with dbspawn:

dbspawn dbsrv17 -n Comix_Masked "cims-masked.db" -gf -x tcpip(Port=2640;IPv6=NO) -c 25p -ch40p -ti 360

After the server starts, I open a connection with dbisql and execute:

alter dbspace archive rename 'cims_archive-masked.db'; commit;

The statement works fine. The new name for the dbspace-file can be seen in sysfile system table. If I script dbspawn and dbisql -nogui in a windows batch file:

%SQLANY17%\\bin64\\dbspawn dbsrv17 -n Comix_Masked "cims-masked.db" -gf -x tcpip(Port=2640;IPv6=NO) -c 25p -ch40p -ti 360 >> renamedb.html %SQLANY17%\\bin64\\dbisql -port 2640 -c "UID=DBA;PWD=xxxxxxxxx;Server=Comix_Masked" -nogui -onerror exit -q renamedb.sql

I get the error:

Could not execute statement. Not allowed while 'DBA' is using the database SQLCODE=-211, ODBC 3 State="40001"

What I tried so far: 1) I tested it with dbsrv17 or dbeng17. 2) After dbspawn I run a statement to wait 60 seconds 3) I use -gf (to deactivate the triggers) or not use -gf. 4) I tried dbisqlc. 5) Remove the -onerror exist clause.

But nothing helps. I get allways the error message from above. Any ideas how to script it without the error?

Thanks

Robert

[chris_keating]

I suggest double checking that no other connections exist. In the SQL script, I would add queries from sysdbfile and sa_conn_info(). Here is sql that I used for testing:

parameters new_name; -- supplied arguments to the script

alter dbspace archive rename 'AlterDBS{new_name}.db'; select dbfile_name, file_name from sysdbfile; select Number, UserId from sa_conn_info() order by Number;

I can reproduce the error but only if I have a user with DBA authority connected.

[robert_kratschmann]

There should be no other connection: I have a production copy tested on my PC. The error message also appears when I use dbeng17 instead of dbsrv17. I ran the script as DBA user.

I've tried an almost empty database with just the dbspace and two dummy tables (one on each dbspace). In this situation, the script works.

The production database contains many triggers and events.

[VolkerBarth]

So could an event use the dbspace in question – or generally be running while you try to alter the dbspace? (If so, Chris's suggestion will tell...)

[chris_keating]

Quering sa_conn_info() should show whether other connections are in play. For example, is there an event running?

[robert_kratschmann]

I'll test it. Is there a way to deactivate all events like triggers with -gf?

[robert_kratschmann]

The database was designed by a person who left the firm. I don't know details of the database. The only thing I can do is exporting the source code into an sql file and see if the events are using tables from the dbspace...

[chris_keating]

There is not a command line option to disable events but it would not be difficult to craft a query to generate SQL to disable events and reactivate then once you are done. Have you confirmed that it is an event that is the cause? You can collect the output of sa_conn_info in the script with the ALTER to see what other connections exist. Connection Numbers 1000000000 or higher are connections including internal and event connections.

[robert_kratschmann]

I found a workaround: 1) Start dbeng17 and deactivate all events via script. 2) Stop dbeng17. 3) Restart dbeng17 execute alter dbspace and activate all events. Only with this workaround it is possible to change the dbspace.

I also checked the output from sa_conn_info: It allways shows only one line. This line is the DBA connection which executes the alter dbspace statement.