Re: RUNSQLSTM drop/create alias error

["Frances Denoncourt" <Frances.Denoncourt@xxxxxxxxxxxxxx>]

Vern, Elvis, Chuck
I tried several different variations including this last one. Tried deleting the file as suggested. [dltf trsfdd/impactb - not found]
This is over a copy of the original file. Tried it with ERRLVL(30) and (31). Same results.
Went to the site suggested. Still not working.
 
 

drop alias trsfdd/IMPACTB;                               
Create alias trsfdd/IMPACTB  FOR TRsfdd/TRIMPCPNEW;      
 UPDATE  trsfdd/IMPACTB                                   
 set  PCPROC = ' ';                                       
 
RUNSQLSTM SRCFILE(TRSFDD/SQLSRC) SRCMBR(UPDATEIMP) COMMIT(*NONE) ERRLVL(31)  
                                                                     
IMPACTB in TRSFDD type *FILE not found.                                   
Message ID . . . . . . :   SQL0204       Severity . . . . . . . :   30        
 Message type . . . . . :   Diagnostic                                         
 Date sent  . . . . . . :   05/12/08      Time sent  . . . . . . :   08:46:01  
                                                                               
 Message . . . . :   IMPACTB in TRSFDD type *FILE not found.                   
Alternative name for file IMPACTB not allowed.  
Message ID . . . . . . :   CPF327E       Severity . . . . . . . :   40      
 Message type . . . . . :   Diagnostic                                       
 Date sent  . . . . . . :   05/12/08      Time sent  . . . . . . :   08:46:01
                                                                             
 Message . . . . :   Alternative name for file IMPACTB not allowed.          
 Cause . . . . . :   An attempt was made to create, move, or change database 
   file IMPACTB with alternative name *N into library TRSFDD.  However, the  
   operation was not done for reason code 2.  The reason codes are:          
     1 - The alternative name is not a valid database alternative name.      
     2 - The alternative name is a duplicate of the alternative name for     
   database file IMPACTB that already exists in library TRSFDD.                                        
File IMPACTB not created in library TRSFDD.                               
IMPACTB in TRSFDD type *FILE already exists.                           
Message ID . . . . . . :   SQL0601       Severity . . . . . . . :   30        
 Message type . . . . . :   Diagnostic                                         
 Date sent  . . . . . . :   05/12/08      Time sent  . . . . . . :   08:46:01  
                                                                               
 Message . . . . :   IMPACTB in TRSFDD type *FILE already exists.              
 Cause . . . . . :   An attempt was made to create IMPACTB in TRSFDD or to     
   rename a table, view, alias, or index to IMPACTB, but IMPACTB already       
   exists.  All tables, views, aliases, indexes, SQL packages, constraints,    
   triggers, and user-defined types in the same schema must have unique names. 
     -- If IMPACTB is a temporary table, it cannot be replaced unless the WITH 
   REPLACE clause is specified.                                                
     -- If the schema name is *N, this is a CREATE SCHEMA statement.  If this  
   is a CREATE TABLE or ALTER TABLE statement and the type is *N, IMPACTB is a 
   constraint.                                                                    
Object IMPACTB in TRSFDD type *FILE not found.                            
IMPACTB in TRSFDD type *FILE not found.                                   
                               
 
 
 
 
 
 
 
 
 
 

Fran Denoncourt
Sr. Programmer/Analyst
Pinal County Treasurer's Office
Florence, AZ 85232
(520) 866-6404

Receipt of this message does not grant you permission to send me 
Unsolicited Commercial Email
 
> > > CRPence <CRPbottle@xxxxxxxxx> 05/10/2008 1:36 PM >>>
   CPF327E will mention RCLSTG, but not for RC2 [return code 2] which is 
_expected_ prior to a SQL0601.  Either the DROP should remain in the 
script [what is given shows it is commented with '--'] and ERRLVL(30) be 
used, or the CREATE ALIAS should also be commented\removed from the 
script.  The RC2 suggests to delete the file IMPACT in library TRSFDD 
[or choose a unique name; reiterated in the SQL0601], if the CREATE is 
intended to ignore the error.  Or ERRLVL(31) could be used to ignore the 
-601 condition, but that would likely be ignoring too much in a script.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyexecutestmt.htm 

   If RC3 for CPF327E, do one of the following for recovery, but best 
only *after* doc collection to assist determining origin [open a PMR 
with IBM]:

     RCLSTG SELECT(*DBXREF) /* restricted state required */
     RCLDBXREF /* prompt for parms; not on older releases */
     CALL QDBXRCTX /* I do not recall parms; search internet */

Regards, Chuck

Frances Denoncourt wrote:
> I had a sql stmt that ran just fine until this afternoon. It worked
> all morning just fine. I think it started aborting when I deleted the
> file, made mods, recreated it.
>
> Found something about RCLSTG - not something I'll be doing or asking
> OPS. I tried using a different alias. Didn't work.
>
> How do you even view a list of aliases for a file? We don't have a
> SQL - cannot STRSQL, but can RUNSQLSTM using a source file.
>
> Spent a couple of hours trying to track this down.  Your help would
> be greatly appreciated. Thanks, Fran
>
>
> RUNSQLSTM SRCFILE(TRSFDD/SQLSRC) SRCMBR(UPDATEIMP) COMMIT(*NONE) 
>  Alternative name for file IMPACT not allowed.
>  File IMPACT not created in library TRSFDD.
>  IMPACT in TRSFDD type *FILE already exists. 
>  RUNSQLSTM command failed. RUNSQLSTM command failed.
>
> After the 'already exists' I tried this:
>
>  *************** Beginning of data ***************
>  --drop alias trsfdd/impact;
>  Create alias trsfdd/Impact   FOR TRLIBDTA/TRIMPCP ;
>  UPDATE  trsfdd/Impact set  PCPROC = ' ';