飞舞的幻想

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 飞舞的幻想 阅读(262) | 评论 (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 飞舞的幻想 阅读(1669) | 评论 (0) | 编辑 收藏