SQL0901 on a file rename
Last Post 07 Feb 2013 07:49 AM by mygoodname. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
mygoodname
New Member
New Member
Posts:75

--
23 Jan 2013 12:16 PM

We recently updated one of our testing systems to V7R1 and are now encoutering an odd error.

We have an in-house database update process that basically does this:

  1. Rename the old physical file.
  2. Create a duplicate object of the new file from a specified source library.
  3. Use DSPDBR to ID dependent files attached to the old physical and delete them.
  4. Use DSPDBR on the copy of the new file in the specified source library to ID dependent files attached to the new one and use CRTDUPOBJ to install them.

We have a physical file that has 10 logical files and 1 SQL view attached to it.  Now when we try to rename the physical file (step 1), I get SQL0901 followed shortly by iseries CPF323F.

Message ID . . . . . . :     CPF323F         Severity . . . . . . . :     40          
Message type . . . . . :     Escape                                              
Date sent  . . . . . . :   01/23/13      Time sent  . . . . . . :   11:54:39  
                                                                              
Message . . . . :   Move or rename of file XXXXXXXX in library ICIAJ5DTA not  
  complete.                                                                   
Cause . . . . . :   The move or rename of file XXXXXXXX in library ICIAJ5DTA  
  to file Z_XXXXXXXX in library ABCDEFGHIJ was not completed.  This file is one
  with dependent Structured Query Language (SQL) view files.  The error       
  occurred while attempting to change information associated with these       
  dependent files.                                                            
Recovery  . . . :   See the previously listed messages and correct any errors.
   Move or rename file Z_XXXXXXXX in library ABCDEFGHIJ back to file XXXXXXXX  
  in library ABCDEFGHIJ and then move or rename file XXXXXXXX in library       
  ABCDEFGHIJ again to file Z_XXXXXXXX in library ABCDEFGHIJ.

I get no other messages before or after that point to a cause.

Has anyone else run into something like this?

Brian Rusch
Advanced Member
Advanced Member
Posts:549

--
23 Jan 2013 12:25 PM
I have not run into this, but could the problem be solved by running step 3 as the first step?
mygoodname
New Member
New Member
Posts:75

--
23 Jan 2013 12:32 PM

I think so and I currently have the team testing that out, but this process will need to be run over 100+ sets of libraries and the overall process is a bit more complicated than I can go into.  Mainly we capture information on changes as they are promoted and build lists of what has to be to upgrade a given version of the database.

If this is going to be an ongoing issue, we'll need to make some detailed programmatic changes, so I need to know if this is either fixable or permanent.  If it's permanent, I should be able to justify the effort.  Otherwise....

Tommy Holden
Senior Member
Senior Member
Posts:2833
Avatar

--
23 Jan 2013 02:19 PM

sounds really convoluted to me.  unless there's something major you haven't mentioned, why not just do a CPYF OLDFILE NEWFILE CRTFILE(*YES) then CLRPFM on the original file?  or if you want SQL then CREATE TABLE NEWFILE AS (SELECT * FROM OLDFILE) WITH DATA and then do a DELETE FROM OLDFILE.

 

then you can simply not worry about the dependent LFs & views

Rocky Marquiss
Senior Member
Senior Member
Posts:2905

--
24 Jan 2013 09:03 AM
Tom,
I think the purpose behind the process was to not only create a new PF but also create the associated LF's as well.
mygoodname
New Member
New Member
Posts:75

--
04 Feb 2013 05:42 AM

Rocky is correct.  It's all part of a process to update a client database.

The issue was resolved after IBM delivered a PTF.  If IT let's me know what the specific PTF is, I will come back and list it.  In case they don't, this is the clue that led us to bring the issue to IBM.

This problem occurred when we tried to rename a physical file that had an SQL materialized query attached to it and we saw the problem when we ran DSPFD against the materialized query before and after the rename.

References to the physical file before the rename included something like this:  "FROM LIBRARY/FILE".

After the physical was renamed, the description looked like this:  "FROMLIBRARY/NEWFILE".

The net result was that the physical name was changed but not all of the attached dependent files were re-associated correctly since the new SQL syntax being generated by the OS was invalid.

 

mygoodname
New Member
New Member
Posts:75

--
07 Feb 2013 07:49 AM
IT has informed me that PT SI48586 solved the problem.
You are not authorized to post a reply.

Acceptable Use Policy