| View previous topic :: View next topic |
| Author |
Message |
satyenderd
Beginner
Joined: 26 Aug 2005
Posts: 144
Topics: 73
|
 Posted: Thu Nov 20, 2008 3:40 pm Post subject: RENAME the table |
 |
|
Hi all,
I wanna rename the existing table to New table name.
I ran the query, and it went fine.
| Code: |
********************************* Top of Data ****************
---------+---------+---------+---------+---------+---------+--
SET CURRENT SQLID = 'T0J0AJ7';
---------+---------+---------+---------+---------+---------+--
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+--
RENAME TABLE T0J0AJ7.TEMP4 TO EMPL;
---------+---------+---------+---------+---------+---------+--
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
|
But, when I tried to browse the table with the new name I could find that one.
| Code: |
File-AID for DB2 ------------- Object List Utility ------- Object(s) not found
COMMAND ===> SCROLL ===> CSR
SSID ===> TDB2
Enter both of the parameters below:
Object Name ===> TEMPL (* for all)
Object Type ===> (*=All or use S to select from list)
S Table _ View _ Synonym _ Alias
_ Index _ Tablespace _ Plan _ Package
_ Temporary Table _ Auxiliary Table _ Trigger _ User Defined Function
_ Distinct Type _ Stored Procedure _ MQT
Specify Selection Criteria:
Object Creator ===> * (For all except Tbsp,Pkg,Trigger & SP)
Database Name ===> * (For Tablespace Only)
Collection ID ===> (For Package only)
Schema Name ===> * (For Triggers,SP,UDF & Distinct type)
|
Could anyone please thow some light on this.
Thanks in advance to all.
_________________
Satya |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12382
Topics: 75
Location: San Jose
|
| Posted: Thu Nov 20, 2008 3:51 pm Post subject: |
|
|
satyenderd,
If you ran your Spufi commands via Spufi make sure that AUTOCOMMIT option is set to YES on the spufi panel
| Code: |
SPUFI SSID: DB2T
===>
Enter the input data set name: (Can be sequential or partitioned)
1 DATA SET NAME ... ===> SPUFI.INPUT(xxxx)
2 VOLUME SERIAL ... ===> (Enter if not cataloged)
3 DATA SET PASSWORD ===> (Enter if password protected)
Enter the output data set name: (Must be a sequential data set)
4 DATA SET NAME ... ===> OUTPUT
Specify processing options:
5 CHANGE DEFAULTS ===> YES (Y/N - Display SPUFI defaults panel?)
6 EDIT INPUT ...... ===> YES (Y/N - Enter SQL statements?)
7 EXECUTE ......... ===> YES (Y/N - Execute SQL statements?)
8 AUTOCOMMIT ...... ===> YES (Y/N - Commit after successful run?)
9 BROWSE OUTPUT ... ===> YES (Y/N - Browse output data set?)
For remote SQL processing:
10 CONNECT LOCATION ===>
PRESS: ENTER to process END to exit HELP for more information
|
Kolusu |
|
| Back to top |
|
|
satyenderd
Beginner
Joined: 26 Aug 2005
Posts: 144
Topics: 73
|
| Posted: Thu Nov 20, 2008 3:59 pm Post subject: |
|
|
Kolusu, Thanks for your response.
Yes, Its already set.
herein is the screenshot:
| Code: |
SPUFI SSID: TDB2
===>
Enter the input data set name: (Can be sequential or partitioned)
1 DATA SET NAME ... ===> 'T0J0AJ7.SPUFI.INPUT(COND2)'
2 VOLUME SERIAL ... ===> (Enter if not cataloged)
3 DATA SET PASSWORD ===> (Enter if password protected)
Enter the output data set name: (Must be a sequential data set)
4 DATA SET NAME ... ===> 'T0J0AJ7.SPUFI.OUTPUT'
Specify processing options:
5 CHANGE DEFAULTS ===> YES (Y/N - Display SPUFI defaults panel?)
6 EDIT INPUT ...... ===> YES (Y/N - Enter SQL statements?)
7 EXECUTE ......... ===> YES (Y/N - Execute SQL statements?)
8 AUTOCOMMIT ...... ===> YES (Y/N - Commit after successful run?)
9 BROWSE OUTPUT ... ===> YES (Y/N - Browse output data set?)
For remote SQL processing:
10 CONNECT LOCATION ===>
PRESS: ENTER to process END to exit HELP for more information |
_________________
Satya |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12382
Topics: 75
Location: San Jose
|
| Posted: Thu Nov 20, 2008 4:13 pm Post subject: |
|
|
satyenderd,
After the Rename command, check the sysibm.systables catalog table to see the entry for the new name. btw I notice that spufi is renaming the table to EMPL and your file-aid DB2 menu is looking for table TEMPL. Is that a typo?
Kolusu |
|
| Back to top |
|
|
satyenderd
Beginner
Joined: 26 Aug 2005
Posts: 144
Topics: 73
|
| Posted: Thu Nov 20, 2008 5:02 pm Post subject: |
|
|
Kolusu,
No its not a typo.
Herein I have queried on SYSIBM.SYSTABLES, i could'nt find the new table name.
| Code: |
SELECT NAME FROM
SYSIBM.SYSTABLES
WHERE NAME='TEMPL';
---------+---------+---------+---------+---------+---------+----
NAME
---------+---------+---------+---------+---------+---------+----
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---- |
_________________
Satya |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12382
Topics: 75
Location: San Jose
|
| Posted: Thu Nov 20, 2008 5:40 pm Post subject: |
|
|
try this query and see if you get any results
| Code: |
SELECT SUBSTR(NAME,1,8)
,SUBSTR(CREATOR,1,8)
,TYPE
,DBNAME
,TSNAME
FROM SYSIBM.SYSTABLES
WHERE NAME LIKE '%TEMPL%'
|
Kolusu |
|
| Back to top |
|
|
satyenderd
Beginner
Joined: 26 Aug 2005
Posts: 144
Topics: 73
|
| Posted: Thu Nov 20, 2008 6:55 pm Post subject: |
|
|
Hi Kolusu,
Herein is the output.
I ran the query using R/C query.
No table is there.
| Code: |
PTSQL ------------ RC/SQL - Browse Select Results ------------ 11-20-08 15:5
COMMAND ===> SCROLL ===> PAGE
204 ROWS RETRIEVED
#1 #2 TYPE DBNAME TSNAME
UTTEMPLA DSNACC T CC390 UTTEMPL
PS_MC_TE HRTEST A DSNDB06 SYSDBAUT
PS_CNT_T HRTEST A DSNDB06 SYSDBAUT
PS_CNT_T HRTEST A DSNDB06 SYSDBAUT
PS_MC_TE HRDEV A DSNDB06 SYSDBAUT
PS_CNT_T HRDEV A DSNDB06 SYSDBAUT
PS_CNT_T HRDEV A DSNDB06 SYSDBAUT
PS_MC_TE HRCONV A DSNDB06 SYSDBAUT
PS_CNT_T HRCONV A DSNDB06 SYSDBAUT
PS_CNT_T HRCONV A DSNDB06 SYSDBAUT
PS_AE_TE PSF8CONV V PSF8CNV AERUN07
PS_AE_TE PSF8CONV V PSF8CNV AERUN07
PS_MC_TE PSF8CONV T PSF8CNV PT0101
PS_MC_TE PSF8CONV V PSF8CNV PT0101 |
_________________
Satya |
|
| Back to top |
|
|
satyenderd
Beginner
Joined: 26 Aug 2005
Posts: 144
Topics: 73
|
| Posted: Thu Nov 20, 2008 7:33 pm Post subject: |
|
|
Hi Kolusu,
Herein is another try and see the results:
In the SYSTABLE the entry is there. But when I tried to find in the FILE-AID, it failed. Could you please help me in this regard.
Thanks once again for your help and response.
| Code: |
********************************* Top of Data ***********************
---------+---------+---------+---------+---------+---------+---------+----
SET CURRENT SQLID = 'T0J0AJ7';
---------+---------+---------+---------+---------+---------+---------+----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+----
RENAME TABLE T0J0AJ7.TEMP4 TO TABEMPL;
---------+---------+---------+---------+---------+---------+---------+----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+----
SELECT NAME FROM
SYSIBM.SYSTABLES
WHERE NAME='TABEMPL';
---------+---------+---------+---------+---------+---------+---------+----
NAME
---------+---------+---------+---------+---------+---------+---------+----
TABEMPL
---------+---------+---------+---------+---------+---------+----
SELECT * FROM TABEMPL;
---------+---------+---------+---------+---------+---------+----
ENO ENAME
---------+---------+---------+---------+---------+---------+----
1 SATYENDER
2 RAJENDER
3 MAHENDER
4 SURENDER
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+----
|
FILE-AID SCREENSHOT:
| Code: |
File-AID for DB2 ------------- Object List Utility ------- Object(s) not found
COMMAND ===> SCROLL ===> CSR
SSID ===> TDB2
Enter both of the parameters below:
Object Name ===> TABEMPL (* for all)
Object Type ===> (*=All or use S to select from list)
S Table _ View _ Synonym _ Alias
_ Index _ Tablespace _ Plan _ Package
_ Temporary Table _ Auxiliary Table _ Trigger _ User Defined Function
_ Distinct Type _ Stored Procedure _ MQT
Specify Selection Criteria:
Object Creator ===> * (For all except Tbsp,Pkg,Trigger & SP)
Database Name ===> * (For Tablespace Only)
Collection ID ===> (For Package only)
Schema Name ===> * (For Triggers,SP,UDF & Distinct type)
|
FILE-AID OUTPUT: it is displaying the old table name (T0J0AJ7.TEMP4 )
| Code: |
-----------------------------------------------------------
File-AID for DB2 Browse T0J0AJ7.TEMP4
COMMAND ===>
ENO ENAME
SMALLINT CHAR(10)
IDENTITY ----------
****** **************************** TOP OF DATA *********
000001 1 SATYENDER
000002 2 RAJENDER
000003 3 MAHENDER
000004 4 SURENDER
****** ************************** BOTTOM OF DATA ******** |
_________________
Satya |
|
| Back to top |
|
|
satyenderd
Beginner
Joined: 26 Aug 2005
Posts: 144
Topics: 73
|
| Posted: Thu Nov 20, 2008 7:42 pm Post subject: |
|
|
Kolusu,
sorry to bother you.
Now when I am trying to query on the table, i am getting the error.
| Code: |
********************************* Top of Data ************************
---------+---------+---------+---------+---------+---------+---------+
SET CURRENT SQLID = 'T0J0AJ7';
---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM TABEMPL;
---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -204, ERROR: T0J0AJ7.TABEMPL IS AN UNDEFINED NAME
DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -500 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFE0C' X'00000000' X'00000000' X'FFFFFFF
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 |
_________________
Satya |
|
| Back to top |
|
|
satyenderd
Beginner
Joined: 26 Aug 2005
Posts: 144
Topics: 73
|
| Posted: Fri Dec 05, 2008 2:21 pm Post subject: |
|
|
Hi Kolusu,
I found the solution with a doubt, why the AUTOCOMMIT is not working(????):
I have edited the AUTOCOMMIT (YES/NO) option on the default panel to NO.
7 EXECUTE ......... ===> YES
8 AUTOCOMMIT ...... ===> NO
9 BROWSE OUTPUT ... ===> YES
Then in the SPUFI, I gave:
SET CURRENT SQLID = 'CDSADM';
RENAME TABLE T0J0AJ7.TEMP4 TO TABEMPL;
COMMIT;;
The command got executed and the result:
SELECT * FROM T0J0AJ7.TABEMPL;
---------+---------+---------+---------+---------+---------+-
ENO ENAME EAGE EPHONE
---------+---------+---------+---------+---------+---------+-
1 SATYENDER 25 10
2 RAJENDER 27 11
3 MAHENDER 26 12
4 SURENDER 25 13
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-
_________________
Satya |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
