SQL creating an unwanted duplicate of file

8200 pts.
Tags:
IBM DB2
SQL statements
The other day a colleague ran an SQL statement to update a field in a file to a constant where 2 other fields were equal to constants (so no joining was needed, etc.) When finished, there was a duplicate copy of the file in the first library in the library list. (Which is higher than the library of the real file) and so users that started using the file after this were working with the dupe and the others were using the real one. We can't figure out why that would have happened and never saw it before. Anyone seen anything like this happening?

Software/Hardware used:
SQL, V6R1M0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 17  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    no .. I've never seen an Sql UPDATE create a copy of a file. However, I wouldn't run an update without creating a copy of the file....
    49,950 pointsBadges:
    report
  • BigKat
    we tend to do select statements to verify the where criteria is for the records we want and any calculated result will be what we want, then run the update... and it makes no sense that the update would do it, but job logs show he didn't create it any other "reasonable" method.
    8,200 pointsBadges:
    report
  • philpl1jb
    And the copy couldn't have existed before the SQL's, perhaps long before ??? Cause your program are acting screwy with two copies of the data??? Whats the create date on the inappropriate one?
    49,950 pointsBadges:
    report
  • philpl1jb
    Half of my discussion responses are now disappearing!!! Sounds like you're certain that the problem started at the time of the SQL. I'm sure you checked the creation date on both versions of the file.
    49,950 pointsBadges:
    report
  • BigKat
    it wasn't discovered for an hour, but looking back as far as we can see the only thing that was done on that session was the SQL update. It is most vexing!
    8,200 pointsBadges:
    report
  • philpl1jb
    Short of a create table command. http://www.itjungle.com/fhg/fhg032311-story01.html
    49,950 pointsBadges:
    report
  • philpl1jb
    Very unlikely but it's about the only way I can think of .. .. Interactive sql .. change session attributes .. Select output .. 3.. to a file .. system holds these values from previous session. Output file ..must be specified .. library must be specified .. then select will create duplicate file based on selection rules. Phil
    49,950 pointsBadges:
    report
  • BigKat
    yeah, I thought of that, but his session is still set fro the default QGPL/QSQLSELECT and that output redirection only works for select statements; updates, etc run as they are supposed to :( thanks for any suggestions though...
    8,200 pointsBadges:
    report
  • TomLiotta
    The file has create timestamp. Are you auditing for *CREATE events? Does the audit journal help point to the creation program? Is the "higher" library a SQL collection or a normal library? Is it directly related to the user ID? (It doesn't seem like it would be from your description.) Was all work done in STRSQL? -- Tom
    125,585 pointsBadges:
    report
  • BigKat
    yes, the create timestamp was during the time he did the SQL. Unfortunately Object Journaling isn't being done. higher library is a normal non-SQL library (application library not user's). All work was in STRSQL (no create statement, no create responses (i.e from redirected output). Just mysterious and worrisome since we can't attribute the cause. Was hoping somebody else might have seen it too.
    8,200 pointsBadges:
    report
  • ToddN2000
    Did the file to be updated by the SQL statement have the library "qualified"? I'd like to know so I can try a few tests myself.. Scary problem if it can be replicated. Any chance somebody else was running the same update at the same time? I know you checked the file for a create date/time, how about the user ?
    11,190 pointsBadges:
    report
  • BigKat
    The library was not specified in the update. The file shows it was created by the user that ran the update query, and as far as we can tell from the job log, it was right around the time that he was running the update that the file was created. It was run in the STRSQL interface, and no one else was running a query on the file, but the other users may have been running applications using various records (he did not have an exclusive lock on it) We haven't been able to repeat it, but that doesn't exactly give us a "warm fuzzy":(
    8,200 pointsBadges:
    report
  • TomLiotta
    Without an entry in QAUDJRN, it all gets murky. We can't be sure what program actually created the file. Are both the original and the duplicate defined as SQL tables? ... as DDS physicals? ... one as DDS and the other as SQL? (Use DSPFD to see the 'Data Base File Attributes', and look for 'SQL file type' to ensure that SQL created the table or that CRTDUPOBJ was used instead of CPYF.) If the SQL statements didn't explicitly name libraries, part of the question becomes "How was the library chosen?" Is the STRSQL session data still available? -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    So, do you have a level of support where you can ask IBM?
    49,950 pointsBadges:
    report
  • TomLiotta
    Without either a database or audit journal, it's unlikely IBM can help. Using STRSQL against production files is always risky. -- Tom
    125,585 pointsBadges:
    report
  • BigKat
    yeah, thanks for all the ideas, unfortunately, we are at the point where we can't determine anything definitive. We just have this mysterious occurrence.
    8,200 pointsBadges:
    report
  • philpl1jb
    Suffering from "Nattering nabobs of negativism"
    49,950 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following