Topic
  • 3 replies
  • Latest Post - ‏2014-02-11T06:42:50Z by B.Hauser
OS/2 Dude
OS/2 Dude
1 Post

Pinned topic RENAME TABLE in QTEMP

‏2013-10-23T19:21:16Z |

I am using a 'RENAME TABLE QTEMP/FILENAME  TO  LONGSQLFILENAME  FOR SYSTEM NAME  FILENAME' statement on an i.

The file name remains the same in QTEMP, but the LONGSQLFILENAME is not found.  If I later create a file named FILENAME in a persistent library, I can query it using LONGSQLFILENAME all day long.

QUESTION:  Can you not use a RENAME TABLE for an object in QTEMP?

Thanks

Updated on 2013-10-23T19:22:01Z at 2013-10-23T19:22:01Z by OS/2 Dude
  • krmilligan
    krmilligan
    450 Posts

    Re: RENAME TABLE in QTEMP

    ‏2013-10-24T14:31:07Z  

    Pretty sure that RENAME doesn't support QTEMP objects.

    Why are you creating a table in QTEMP?  Are you sure that you can't logically define this data set with a view or common table expression instead of wasting system resources by making a temporary copy of your data? 

  • CRPence@vnet.ibm.com
    53 Posts

    Re: RENAME TABLE in QTEMP

    ‏2014-02-10T19:42:37Z  

      My recollection is that the SQL supported long names in QTEMP, by itself; necessarily [without an enhancement to the *DBXREF], outside the realm of the SQL Catalogs VIEW files based-on the System Database Cross-Reference (DBXREF) tables.  Perhaps I mis-remember what the SQL feature did in that regard.  But what I do recall for sure had functioned, was to create an ALIAS with a long name, and that can refer to the system-name of the file.  The SQL seems to be able to find and thus allow querying by the long-name of an ALIAS created in the QTEMP library FOR QTEMP/system-file-name; i.e. not limited to permanent\persistent libraries.  Even if the SQL neither tracks nor finds a database *FILE in the QTEMP library via its "Alternative name", allowing the assigning of both the system-name [short name] and the long-name and allowing the  RENAME of the file object in QTEMP does enable native operations such as the CL Move Object (MOVOBJ) and Save Object (SAVOBJ) commands to have the object properly named prior to [attempt to effect] placement in the target library.

      In support of my recollection that the SQL tracks\supports long names in the QTEMP library, after some attempts to perform some rename and then some failed SELECT attempts referring to the long-name, I later tried a CREATE TABLE with the long name that I had used previously, and the SQL failed with SQL0601 "LONG_NAME_FOR_XX in QTEMP type *FILE already exists."  That effect [on v5r3] is clearly in contradiction to the actual situation, whereby the library QTEMP no longer had any objects.  Yet that also provides [what I infer to be] clear evidence that the SQL made an attempt to track the long names.  For reference, the actual failing script:

    > create table qtemp/xx (a char)                       
      Table XX created in QTEMP.                           
    > rename table qtemp/xx to long_name_for_xx            
      RENAME for XX in QTEMP completed.                    
    > rename table qtemp/long_name_for_xx to system name xx
      LONG_NAME_FOR_XX in QTEMP type *FILE not found.      
    > select * from qtemp/long_name_for_xx                 
      LONG_NAME_FOR_XX in QTEMP type *FILE not found.      
    > drop table    qtemp/LONG_00001                       
      Drop of LONG_00001 in QTEMP complete.                
    > create table qtemp/long_name_for_xx (a char)         
      Table LONG_NAME_FOR_XX created in QTEMP.             
    > rename table qtemp/long_name_for_xx to system name xx
      RENAME for LONG_00001 in QTEMP completed.            
    > select * from qtemp/long_name_for_xx                 
      LONG_NAME_FOR_XX in QTEMP type *FILE not found.      
    > select * from qtemp/xx                               
      SELECT statement run complete.                       
    > create alias qtemp/long_name_for_xx for qtemp/xx     
      Alias LONG_NAME_FOR_XX created in QTEMP.             
    > select * from qtemp/long_name_for_xx                 
      SELECT statement run complete.                       
    > drop alias qtemp/long_name_for_xx                    
      Drop of LONG_NAME_FOR_XX in QTEMP complete.          
    > select * from qtemp/long_name_for_xx                 
      LONG_NAME_FOR_XX in QTEMP type *FILE not found.      
    > drop table qtemp/xx                                  
      Drop of XX in QTEMP complete.                        
    > create table qtemp/long_name_for_xx (a char)         
      LONG_NAME_FOR_XX in QTEMP type *FILE already exists.
    



     

  • B.Hauser
    B.Hauser
    280 Posts

    Re: RENAME TABLE in QTEMP

    ‏2014-02-11T06:42:50Z  

    Pretty sure that RENAME doesn't support QTEMP objects.

    Why are you creating a table in QTEMP?  Are you sure that you can't logically define this data set with a view or common table expression instead of wasting system resources by making a temporary copy of your data? 

    If you really need a temporary table and cannot use as Kent already proposed a view and also want this tempoary table created with a long table name, you may use the CREATE GLOBAL TEMPORARY TABLE command instead of the CREATE TABLE and RENAME command:

    The following command will create a table within the QTEMP library

    Declare global temporary table my_long_table_Name
    (Col1 VarChar(10),
     Col2 Integer,
    ....);

    This table may be created with the following system name.

    MY_LO00001

    The following SELECT statements will work correctly:

    Select * from qtemp.my_long_table_Name;
    Select * from qtemp.my_lo00001;

    Select * from my_long_table_Name;
    Select * from my_lo00001;

    Birgitta