bcp using SQL Server2000

/* create a batch file export4backup.bat 內容如下 */

d:
isql -U sa -P sa -d ysbknewbak -i exp4bk.sql -o exp4bk.bat -n -h-1 -w500 -b -s
call exp4bk.bat
exit


/* exp4bk.sql 使用 isql執行,exp4bk.sql內容如下*/

SET NOCOUNT ON
SELECT 'd:'
SELECT 'BCP ' + DBNAME + '.dbo.' + TABLENAME + ' OUT ' +
'd:\' + RTRIM(TABLENAME) + '.bcp -c -U intech -P intech -S ' +
RTRIM(SERVERNAME)
FROM N_BACKUP_TABLE
WHERE STATUS = 'Y'
SET NOCOUNT OFF


Data movement from SQL Server to DB2


c:\>BCP Northwind.dbo.D_N_USER OUT D_N_USER.bcp -c -t, -q -U sa -P exim999 -S IBM-63AB93AA2BF


/*using DB2 CLP*/

connect to db2

C:\>db2 load client from D_N_USER.bcp of del modified by nochardel keepblanks insert into D_N_USER


Dynamic SQL in procedure using SQL Server 2000

/* interact with dos using xp_cmdshell */

exec master..xp_cmdshell 'echo hello > c:\file.txt'
exec master..xp_cmdshell 'echo appended data >> c:\file.txt'
exec master..xp_cmdshell 'echo more data >> c:\file.txt'
master..xp_cmdshell 'bcp master..sysobjects out c:\file.bcp -S -U -P -c '

/* using dynamic sql */

declare @cmd varchar(1000)
select @cmd = 'osql -U -P -S -Q"select * from master..sysobjects" -o"c:\osqloutput.txt" -w500'
exec master..xp_cmdshell @cmd

if exists(select 1 from sysobjects
where name = 'sp_reindex' and type = 'P' )
drop procedure sp_reindexgo
create procedure sp_reindexasbegin
declare @cmd varchar(255)
declare ic insensitive cursor for
select 'dbcc dbreindex (' + name + ')'
from sysobjects
where type = 'U'
open ic
fetch next from ic into @cmd
while @@fetch_status = 0
begin exec (@cmd)
fetch next from ic into @cmd end
close ic
deallocate ic
end


Data movement in different codepage in DB2 UDB8.2

/*
　SOURCE DATABASE WEBDB CODEPAGE 950 CHARACTERSET BIG5
　TARGET DATABASE WEBDB2 CODEPAGE 1208 CHARACTERSET UTF8
　字元長度要注意, UTF8 一個中文字長度=3
　ex: TABLENAME => ORION
*/

DB2 CONNECT TO WEBDB USER UID USING PWD

DB2 EXPORT TO ORION.DEL OF DEL SELECT * FROM ORION

DB2 TERMINATE

DB2 CONNECT TO WEBDB2 USER UID USING PWD

DB2 LOAD FROM ORION.DEL OF DEL MODIFIED BY CODEPAGE=950 INSERT INTO ORION

DB2 TERMINATE


IDENTITY column RESTART in DB2 UDB8.2

/*
使用輸銀風險管理stagging tables 為例, 隨著倉儲資料越來越大, 經過幾次資料搬移, 幾個identity的欄位將爆掉. 將identity restart的程序如下:
*/

-- EXPORT TABLE WITH IDENTITY

EXPORT TABLE F_N_CNTDETAIL.DEL OF DEL SELECT * FROM F_N_CNTDETAIL ORDER BY SEQ

--清空 table

ALTER TABLE F_N_CNTDETAIL ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

--RESTART IDENTITY

ALTER TABLE F_N_CNTDETAIL ALTER COLUMN SEQ RESTART WITH 1;

--LOAD

LOAD FROM F_N_CNTDETAIL.DEL OF DEL MODIFIED BY IDENTITYIGNORE
INSERT INTO F_N_CNTDETAIL