飛舞的幻想
          

          

          


          
	
		

          常用鏈接
          

		

          統計
          
	
		
		
		
          Google Adsense
          
		
				
			
	
		
          Zanox  FWHX
          
		
				
			
	
		
          花個明白
          
		
				
			
	

		

          最新評論
	
          

          
	
			
		

          
		
	

          

          
	
					

          
	2009年5月13日		  
	#

          


		
          
			
          DB2 時間轉換
          
			
			create function ts_fmt(TS timestamp, fmt varchar(20)) 
returns varchar(50) 
return 
with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as 
( 
 select 
 substr( digits (day(TS)),9), 
 substr( digits (month(TS)),9) , 
 rtrim(char(year(TS))) , 
 substr( digits (hour(TS)),9), 
 substr( digits (minute(TS)),9), 
 substr( digits (second(TS)),9), 
 rtrim(char(microsecond(TS))) 
 from sysibm.sysdummy1 
 ) 
select 
case fmt 
 when 'yyyymmdd' 
 then yyyy || mm || dd 
 when 'mm/dd/yyyy' 
 then mm || '/' || dd || '/' || yyyy 
 when 'yyyy/dd/mm hh:mi:ss' 
 then yyyy || '/' || mm || '/' || dd || ' ' || 
 hh || ':' || mi || ':' || ss 
 when 'nnnnnn' 
 then nnnnnn 
 else 
 'date format ' || coalesce(fmt,'  ') || 
 ' not recognized.' 
 end 
from tmp 
使用例句：
update t_attachment as a set a.attach_path=(select '/'||rtrim(char(apply_com_id))||'/'||rtrim(char(ts_fmt(apply_date,'yyyymmdd')))||'/' from t_rmbs_claim as c
where a.claim_id=c.claim_id)||rtrim(a.attach_path) where exists (select 1 from t_rmbs_claim as tc where tc.claim_id=a.claim_id);



			
			
          		
				posted @ 2010-03-05 18:18 飛舞的幻想 閱讀(272) | 評論 (0) | 編輯 收藏
			
          
		
          
	
		
          
			
          DB2 restore Redirect
          
			
			(1)db2 restore db EFINANCE incremental from /db2/eFinance091228  taken
at 200912282115 on /db2/eFinanceWJ28 into EFINAN28 logtarget
/db2/eFinanceWJ28 redirect generate script  restore.clp
          
說明：EFINANCE 為備份前的數據庫名稱  EFINAN28 為回復后的數據庫名稱 /db2/eFinance091228備份文件存放的路徑  /db2/eFinanceWJ28為還原后日志存放路徑以及還原后數據庫文件路徑。
          

          
(2)db2 -tvf  restore.clp
          
如果需要重新指定表空間的路徑請修改該文件
          
該文件內容如下：
          
-- *****************************************************************************
          
-- ** automatically created redirect restore script
          
-- *****************************************************************************
          
UPDATE COMMAND OPTIONS USING S ON Z ON EFINANCE_NODE0000.out V ON;
          
SET CLIENT ATTACH_DBPARTITIONNUM  0;
          
SET CLIENT CONNECT_DBPARTITIONNUM 0;
          
-- *****************************************************************************
          
-- ** automatically created redirect restore script
          
-- *****************************************************************************
          
RESTORE DATABASE EFINANCE
          
-- USER  <username>
          
-- USING '<password>'
          
FROM '/db2/eFinance091228'
          
TAKEN AT 20091228211504
          
ON '/db2/eFinanceWJ28'
          
DBPATH ON '/db2/eFinanceWJ28'
          
INTO EFINAN28
          
LOGTARGET '/db2/eFinanceWJ28/'
          
-- NEWLOGPATH '/ef/appinst/appinst/NODE0000/SQL00002/SQLOGDIR/'
          
-- WITH <num-buff> BUFFERS
          
-- BUFFER <buffer-size>
          
-- REPLACE HISTORY FILE
          
-- REPLACE EXISTING
          
REDIRECT
          
-- PARALLELISM <n>
          
-- WITHOUT ROLLING FORWARD
          
-- WITHOUT PROMPTING
          
;
          
-- *****************************************************************************
          
-- ** table space definition
          
-- *****************************************************************************
          
-- *****************************************************************************
          
-- ** Tablespace name                            = SYSCATSPACE
          
-- **   Tablespace ID                            = 0
          
-- **   Tablespace Type                          = Database managed space                      
          
-- **   Tablespace Content Type                  = All permanent data. Regular table space.    
          
-- **   Tablespace Page size (bytes)             = 4096
          
-- **   Tablespace Extent size (pages)           = 4
          
-- **   Using automatic storage                  = Yes     
          
-- **   Auto-resize enabled                      = Yes     
          
-- **   Total number of pages                    = 24576
          
-- **   Number of usable pages                   = 24572
          
-- **   High water mark (pages)                  = 21080
          
-- *****************************************************************************
          
-- *****************************************************************************
          
-- ** Tablespace name                            = TEMPSPACE1
          
-- **   Tablespace ID                            = 1
          
-- **   Tablespace Type                          = System managed space                        
          
-- **   Tablespace Content Type                  = System Temporary data                       
          
-- **   Tablespace Page size (bytes)             = 4096
          
-- **   Tablespace Extent size (pages)           = 32
          
-- **   Using automatic storage                  = Yes     
          
-- **   Total number of pages                    = 1
          
-- *****************************************************************************
          
-- *****************************************************************************
          
-- ** Tablespace name                            = USERSPACE1
          
-- **   Tablespace ID                            = 2
          
-- **   Tablespace Type                          = Database managed space                      
          
-- **   Tablespace Content Type                  = All permanent data. Large table space.      
          
-- **   Tablespace Page size (bytes)             = 16384
          
-- **   Tablespace Extent size (pages)           = 32
          
-- **   Using automatic storage                  = No      
          
-- **   Auto-resize enabled                      = No      
          
-- **   Total number of pages                    = 1310720
          
-- **   Number of usable pages                   = 1310688
          
-- **   High water mark (pages)                  = 886976
          
-- *****************************************************************************
          
SET TABLESPACE CONTAINERS FOR 2
          
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
          
USING (
          
FILE   '/db2/eFinanTS28/eFinance/USERSPACE1'                                 1310720
          
);
          
-- *****************************************************************************
          
-- ** Tablespace name                            = TBS_DATA32K
          
-- **   Tablespace ID                            = 3
          
-- **   Tablespace Type                          = Database managed space                      
          
-- **   Tablespace Content Type                  = All permanent data. Large table space.      
          
-- **   Tablespace Page size (bytes)             = 32768
          
-- **   Tablespace Extent size (pages)           = 32
          
-- **   Using automatic storage                  = No      
          
-- **   Auto-resize enabled                      = No      
          
-- **   Total number of pages                    = 327680
          
-- **   Number of usable pages                   = 327648
          
-- **   High water mark (pages)                  = 9856
          
-- *****************************************************************************
          
SET TABLESPACE CONTAINERS FOR 3
          
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
          
USING (
          
FILE   '/db2/eFinanTS28/eFinance/TBS_DATA32K'                                 327680
          
);
          
-- *****************************************************************************
          
-- ** Tablespace name                            = TBS_DATA4K
          
-- **   Tablespace ID                            = 4
          
-- **   Tablespace Type                          = Database managed space                      
          
-- **   Tablespace Content Type                  = All permanent data. Large table space.      
          
-- **   Tablespace Page size (bytes)             = 4096
          
-- **   Tablespace Extent size (pages)           = 32
          
-- **   Using automatic storage                  = No      
          
-- **   Auto-resize enabled                      = No      
          
-- **   Total number of pages                    = 2621440
          
-- **   Number of usable pages                   = 2621408
          
-- **   High water mark (pages)                  = 25440
          
-- *****************************************************************************
          
SET TABLESPACE CONTAINERS FOR 4
          
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
          
USING (
          
FILE   '/db2/eFinanTS28/eFinance/TBS_DATA4K'                                 2621440
          
);
          
-- *****************************************************************************
          
-- ** Tablespace name                            = TBS_IDX4K
          
-- **   Tablespace ID                            = 5
          
-- **   Tablespace Type                          = Database managed space                      
          
-- **   Tablespace Content Type                  = All permanent data. Large table space.      
          
-- **   Tablespace Page size (bytes)             = 4096
          
-- **   Tablespace Extent size (pages)           = 32
          
-- **   Using automatic storage                  = No      
          
-- **   Auto-resize enabled                      = No      
          
-- **   Total number of pages                    = 2621440
          
-- **   Number of usable pages                   = 2621408
          
-- **   High water mark (pages)                  = 96
          
-- *****************************************************************************
          
SET TABLESPACE CONTAINERS FOR 5
          
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
          
USING (
          
FILE   '/db2/eFinanTS28/eFinance/TBS_IDX4K'                                  2621440
          
);
          
-- *****************************************************************************
          
-- ** Tablespace name                            = CMCC_DATA
          
-- **   Tablespace ID                            = 6
          
-- **   Tablespace Type                          = Database managed space                      
          
-- **   Tablespace Content Type                  = All permanent data. Large table space.      
          
-- **   Tablespace Page size (bytes)             = 16384
          
-- **   Tablespace Extent size (pages)           = 32
          
-- **   Using automatic storage                  = No      
          
-- **   Auto-resize enabled                      = No      
          
-- **   Total number of pages                    = 655360
          
-- **   Number of usable pages                   = 655328
          
-- **   High water mark (pages)                  = 32736
          
-- *****************************************************************************
          
SET TABLESPACE CONTAINERS FOR 6
          
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
          
USING (
          
FILE   '/db2/eFinanTS28/eFinance/CMCC_DATA'                                   655360
          
);
          
-- *****************************************************************************
          
-- ** Tablespace name                            = CMCC_DATA32
          
-- **   Tablespace ID                            = 7
          
-- **   Tablespace Type                          = Database managed space                      
          
-- **   Tablespace Content Type                  = All permanent data. Large table space.      
          
-- **   Tablespace Page size (bytes)             = 32768
          
-- **   Tablespace Extent size (pages)           = 32
          
-- **   Using automatic storage                  = No      
          
-- **   Auto-resize enabled                      = No      
          
-- **   Total number of pages                    = 327680
          
-- **   Number of usable pages                   = 327648
          
-- **   High water mark (pages)                  = 22496
          
-- *****************************************************************************
          
SET TABLESPACE CONTAINERS FOR 7
          
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
          
USING (
          
FILE   '/db2/eFinanTS28/eFinance/CMCC_DATA32'                                 327680
          
);
          
-- *****************************************************************************
          
-- ** Tablespace name                            = TBS_IDX01
          
-- **   Tablespace ID                            = 8
          
-- **   Tablespace Type                          = Database managed space                      
          
-- **   Tablespace Content Type                  = All permanent data. Large table space.      
          
-- **   Tablespace Page size (bytes)             = 4096
          
-- **   Tablespace Extent size (pages)           = 32
          
-- **   Using automatic storage                  = No      
          
-- **   Auto-resize enabled                      = No      
          
-- **   Total number of pages                    = 2621440
          
-- **   Number of usable pages                   = 2621408
          
-- **   High water mark (pages)                  = 274176
          
-- *****************************************************************************
          
SET TABLESPACE CONTAINERS FOR 8
          
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
          
USING (
          
FILE   '/db2/eFinanTS28/eFinance/TBS_IDX01'                                  2621440
          
);
          
-- *****************************************************************************
          
-- ** Tablespace name                            = DMS_TMP_32K
          
-- **   Tablespace ID                            = 9
          
-- **   Tablespace Type                          = Database managed space                      
          
-- **   Tablespace Content Type                  = System Temporary data                       
          
-- **   Tablespace Page size (bytes)             = 32768
          
-- **   Tablespace Extent size (pages)           = 32
          
-- **   Using automatic storage                  = No      
          
-- **   Auto-resize enabled                      = No      
          
-- **   Total number of pages                    = 327680
          
-- **   Number of usable pages                   = 327648
          
-- **   High water mark (pages)                  = 64
          
-- *****************************************************************************
          
SET TABLESPACE CONTAINERS FOR 9
          
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
          
USING (
          
FILE   '/db2/eFinanTS28/eFinance/DMS_TMP_32K'                                 327680
          
);
          
-- *****************************************************************************
          
-- ** Tablespace name                            = SYSTOOLSPACE
          
-- **   Tablespace ID                            = 10
          
-- **   Tablespace Type                          = Database managed space                      
          
-- **   Tablespace Content Type                  = All permanent data. Large table space.      
          
-- **   Tablespace Page size (bytes)             = 4096
          
-- **   Tablespace Extent size (pages)           = 4
          
-- **   Using automatic storage                  = Yes     
          
-- **   Auto-resize enabled                      = Yes     
          
-- **   Total number of pages                    = 8192
          
-- **   Number of usable pages                   = 8188
          
-- **   High water mark (pages)                  = 1512
          
-- *****************************************************************************
          
-- *****************************************************************************
          
-- ** Tablespace name                            = SYSTOOLSTMPSPACE
          
-- **   Tablespace ID                            = 11
          
-- **   Tablespace Type                          = System managed space                        
          
-- **   Tablespace Content Type                  = User Temporary data                         
          
-- **   Tablespace Page size (bytes)             = 4096
          
-- **   Tablespace Extent size (pages)           = 4
          
-- **   Using automatic storage                  = Yes     
          
-- **   Total number of pages                    = 1
          
-- *****************************************************************************
          
-- *****************************************************************************
          
-- ** Tablespace name                            = EF_ENGINE_16K
          
-- **   Tablespace ID                            = 12
          
-- **   Tablespace Type                          = Database managed space                      
          
-- **   Tablespace Content Type                  = All permanent data. Large table space.      
          
-- **   Tablespace Page size (bytes)             = 16384
          
-- **   Tablespace Extent size (pages)           = 32
          
-- **   Using automatic storage                  = No      
          
-- **   Auto-resize enabled                      = No      
          
-- **   Total number of pages                    = 655360
          
-- **   Number of usable pages                   = 655328
          
-- **   High water mark (pages)                  = 96
          
-- *****************************************************************************
          
SET TABLESPACE CONTAINERS FOR 12
          
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
          
USING (
          
FILE   '/db2/eFinanTS28/eFinance/EF_ENGINE_16K'                               655360
          
);
          
-- *****************************************************************************
          
-- ** Tablespace name                            = TEMPSPACE16k
          
-- **   Tablespace ID                            = 13
          
-- **   Tablespace Type                          = System managed space                        
          
-- **   Tablespace Content Type                  = System Temporary data                       
          
-- **   Tablespace Page size (bytes)             = 16384
          
-- **   Tablespace Extent size (pages)           = 32
          
-- **   Using automatic storage                  = No      
          
-- **   Total number of pages                    = 1
          
-- *****************************************************************************
          
SET TABLESPACE CONTAINERS FOR 13
          
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
          
USING (
          
PATH   '/db2/eFinanTS28/eFinance/C0000016k.TMP'
          
);
          
-- *****************************************************************************
          
-- ** start redirected restore
          
-- *****************************************************************************
          
RESTORE DATABASE EFINANCE CONTINUE;
          
-- *****************************************************************************
          
-- ** end of file
          
-- *****************************************************************************
          

          

          

          
(3)db2 rollforward database EFINAN28 to end of logs and complete  overflow log path '(/db2/eFinanceWJ28)'
          
說明：EFINAN28 為還原的數據庫名稱 /db2/eFinanceWJ28為日志存放路徑 注意 日志一定要全 即要包括回復的當天的全部log文件。

			
			
          		
				posted @ 2010-01-29 14:05 飛舞的幻想 閱讀(1680) | 評論 (0) | 編輯 收藏
			
          
		
          
	
		
          
			
          DB2學習的好地方 推薦
          
			
			http://www.db2china.net/club/?fromuid=328

			
			
          		
				posted @ 2009-05-13 00:21 飛舞的幻想 閱讀(173) | 評論 (0) | 編輯 收藏
			
          
		
          
	


				

          




    
          
        
	




主站蜘蛛池模板：
清镇市|
浮梁县|
乌海市|
紫云|
宁强县|
裕民县|
宣威市|
辽中县|
中江县|
吉林市|
佳木斯市|
南溪县|
洛川县|
德令哈市|
木里|
龙陵县|
呼图壁县|
巧家县|
金山区|
平山县|
钟祥市|
社旗县|
汤原县|
滦平县|
贡觉县|
同江市|
盐边县|
育儿|
鹰潭市|
年辖：市辖区|
正定县|
文成县|
华宁县|
抚宁县|
乌审旗|
怀仁县|
出国|
和田县|
阿合奇县|
鹿泉市|
民勤县|