Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    8

    Red face Unanswered: delete with NLI fail.

    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?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Mar 2007
    Posts
    8
    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.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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).
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Mar 2007
    Posts
    8
    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)

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by Wong Tseng
    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

  8. #8
    Join Date
    Mar 2007
    Posts
    8
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •