commit;
ALTER TABLE MIS.SJSGHSJ ACTIVATE NOT LOGGED INITIALLY;
delete from MIS.SJSGHSJ where BCRQ ='20070516';
commit;

This is my delete statements with NLI. I had put the alter statement and delete statement in the same transaction unit, but the I still got the SQLSTATE=57011

What's wrong with these statements?

My guess is that you are running this from the CLP and autocommit is still on. You need to issue "update command options using c off" at the start, and "update command options using c on" at the end.

If this is not the case, please supply the sqlcode that is being returned, the sqlstate 57011 is returned for several sqlcodes and it is the sqlcode that is more meaningful.

Andy

Sorry for not supplying you the useful infomation. I executed these statements in the Command Editor, and the result only includes the infomation of the sqlstate. So I cannot reply you with the sqlcode.
If I remove the commit statements, there is more infomation returned, including the sql code( sqlcode : -964). But I think that is another situation.
I have used the update commands, but delete still got wrong.

sqlcode -964 is transaction log is full. You would get this if your delete is removing a very large amount of rows and logging is being done. I still suspect that what you are trying to do is not correct. Your commands should look like this:

commit;
update command options using c off;
ALTER TABLE MIS.SJSGHSJ ACTIVATE NOT LOGGED INITIALLY;
delete from MIS.SJSGHSJ where BCRQ ='20070516';
commit;
update command options using c on;

If this is what you are using and you still are having problems, then please post the return values for each line of this script.

Andy

I don't think that CLP parameters ("options using c off") will work in Command Editor. Autocommit must be switched off in the Command Editor properties (under the Tools Settings menu item).

result(in Chinese):

ALTER TABLE MIS.SJSGHSJ ACTIVATE NOT LOGGED INITIALLY
DB20000I SQL 命令成功完成(successful)。

delete from MIS.SJSGHSJ where BCRQ ='20070516'
DB21034E 该命令被当作 SQL
语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：
SQL0964C 数据库的事务日志已满。 SQLSTATE=57011 (It says the transaction log is full)

commit
DB20000I SQL 命令成功完成。(successful)

update command options using c on
DB20000I UPDATE COMMAND OPTIONS 命令成功完成。(successful)

Did you run this in the CLP or the "Command Editor"? If you used the CLP, you forgot the command "update command options using c off;" (I do not see any result for it). If you are using the "Command Editor" you need to turn off the auto-commit option by Tools -> Tool settings, goto the "Command Editor" tab and uncheck the box.

Andy

Hi,Andy
Actually I used the Command Editor, the result for "c off" did exist, but I skipped it when I tried to copy that.
Glad everything is Ok after I turn off the auto-commit option in Tools Menu following your advice. Thank you!