unexpected records deletion

[Guest.Visitor]

Md. Shahid wrote: > I've included effected PF under journaling and I found QZDASOINIT as > the program name. You don't list a question here, I'll assume you'd like some opinions. QZDASOINIT is the job used by ODBC requests. So, it looks like you have a PC running data transfers that is deleting your data. Bill

[Guest.Visitor]

Thanks Bill, In fact, any opinion will be great. BTW, down the line I am pasting the Job log partial contents :- SQL0199 Diagnostic 30 11/16/07 10:27:56.995288 QSQRUN1 QSYS *STMT QSQRUN1 QSYS *STMT From user . . . . . . . . . : TESTER From module . . . . . . . . : QSQPREP From procedure . . . . . . : CLEANUP Statement . . . . . . . . . : 11033 To module . . . . . . . . . : QSQPREP To procedure . . . . . . . : CLEANUP Statement . . . . . . . . . : 11033 Message . . . . : Keyword DELETE not expected. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE. Cause . . . . . : The keyword DELETE was not expected here. A syntax error was detected at keyword DELETE. The partial list of valid tokens is FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires 5722SS1 V5R4M0 060210 Job Log DBLI5 11/16/07 09:28:58 Page 3 Job name . . . . . . . . . . : QZDASOINIT User . . . . . . : QUSER Number . . . . . . . . . . . : 031734 Job description . . . . . . : QDFTSVR Library . . . . . : QGPL MSGID TYPE SEV DATE TIME FROM PGM LIBRARY INST TO PGM LIBRARY INST reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again. SQL0104 Diagnostic 30 11/16/07 10:28:05.323632 QSQXCUTE QSYS *STMT QSQXCUTE QSYS *STMT From user . . . . . . . . . : TESTER From module . . . . . . . . : QSQXCUTE From procedure . . . . . . : CLEANUP Statement . . . . . . . . . : 25729 To module . . . . . . . . . : QSQXCUTE To procedure . . . . . . . : CLEANUP Statement . . . . . . . . . : 25729 Message . . . . : Token * was not valid. Valid tokens: FROM. Cause . . . . . : A syntax error was detected at token *. Token * is not a valid token. A partial list of valid tokens is FROM. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token *. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is , correct the SQL statement because it does not end with a valid clause.

[Guest.Visitor]

Looks like somebody logged on as TESTER is trying to run a DELETE statement over an ODBC connection. There are errors while they try to write a correct SQL statement. The joblog might be more readable if you posted it in the Raw Code section of the message.

[Guest.Visitor]

Yup...You can see the last statement "TESTER" tried to run was: "DELETE * FROM Sometable" when the syntax checker encountered the '*' and suggested the only valid token was FROM. I'd start with CHGUSRPRF USRPRF(TESTER) STATUS(*DISABLED)...Then post a global eamil in your organization to find out "Who wants to learn SQL?"...TESTER should be first in line. hoo-hoo :-)

[Guest.Visitor]

It's done like this TESTER: DELETE FROM SOMEFILE WHERE 1 = 1. No pesky "Are you sure?" prompts that way. Sorry Md Shahid, at least you've got that PF journaled...TESTER is trying to delete all the records in your file.

[Guest.Visitor]

Thanks Ken, you are correct. I am investigating that how and when these statements are getting executed and what's their origin. As such I don't have any stored procedure or any external routine in use by TESTER.

[Guest.Visitor]

Thanks for these comments,, but what's the origin for these statements like "DELETE FROM ..." TESTER is not calling any stored procedure or other routine. No SQL use I am still investigating.

[Guest.Visitor]

Hi, I've been noticing that the PF records are being deleted by QZDASOINIT job. I've included effected PF under journaling and I found QZDASOINIT as the program name. Although, job log is not much helpfull. Md. Shahid

[Guest.Visitor]

Even Client Access Navigator can give SQL access via TCP/IP and ODBC and automatically connect to the 400's default database. Usually *LOCAL unless you have other databases. The interactive session can execute SQL statements directly against the database. TESTER probably thinks: "Hmmmm, Select * from sometable listed all the records. Maybe Delete * from sometable will do something else?" Pure speculation.