cancel
Showing results for 
Search instead for 
Did you mean: 

alter dbspace fails if it is scripted

robert_kratschmann
Participant
0 Kudos
451

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
Product and Topic Expert
Product and Topic Expert
0 Kudos

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
Participant
0 Kudos

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
Contributor
0 Kudos

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
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

robert_kratschmann
Participant

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

robert_kratschmann
Participant
0 Kudos

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
Product and Topic Expert
Product and Topic Expert

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
Participant
0 Kudos

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.

Accepted Solutions (0)

Answers (0)