AS400如何透過DTS轉入SQL Server2000

剛好同事問有沒有文件，想說順便寫一下設定步驟好囉！

1.請確定在你執行的那台server上已裝好Client Access囉，如果你想在Client端也能用，那Client端也要裝!
2-1.裝好後我通常是先設定ODBC啦，再[開始][執行]那邊輸入odbcad32，這邊之後再設也行

2-2. 然後在[ODBC資料來源管理員]畫面的[系統資料來名稱]頁籤，按下[新增]
2-3.在[建立新資料來源]畫面，選取[iSeries Access ODBC Driver]，按下[完成 ]

2-4.在[iSeries Access for Windows ODBC設定]畫面的[一般]頁籤，設定[資料來源名稱]，名稱看你想設定什都可，與[系統]的ip位址，也就是AS400的位置2-5. 切換到[伺服器]頁籤，設定[SQL的預設檔案庫]，也就是AS400的Library囉，如果有多個的話，是設在下面的[檔案庫清單]，然後按下[確定]，ODBC的設定就完成囉!
3.這邊我就假設你已在DTS裡拉好一個[其他連線］ ，然後你就在[連線屬性]的設定畫面裡，在下方的[使用者/系統DSN]那各下拉選單，選取你剛設好的資料來源，然後再設定與AS400連線的使用者名稱與密碼，就可以按下[確定]囉，我剛在第2步也說，ODBC也是可以這時候再設的，按下[新增]就可以在設定其他的囉，步驟與第2步差不多
4.若你之前的設定都正確的話，當你拉一個[轉換資料工作]時，在[來源]的屬性設定頁籤裡，[資料表/檢視表]下拉選單是可以看到那些在AS400的table的喔

批次檔的執行條件處理IF

IF在批次檔中大概有3種格式，我配合我用過的例子作說明，如下：

1. IF [NOT] 參數==字串 command
參數如果等於指定的字串，則條件成立，就會運行命令
例子1：我有一個批次檔，每天排程執行，當遇到週日我想執行不一樣的命令， 內容要怎麼寫？

::取日期與星期幾
for /f "tokens=1,2" %%i in ('date /T') do set vard=%%i&& set vare=%%j
::判斷是否週日，如果是週日跳到另一段落
if %vare%==星期日 goto addsun
::下面執行非週日的命令
echo 執行非週日命令
exit
::下面執行週日的另令
:addsun
echo 執行週日命令

例子2：各位都知道windows排定的工作是可以細到每分鐘的執行一次的，那如果我排定一個批次檔是每天的九點開始執行，每小時執行一次，直到下午五點，批次檔會寫記錄到Log中，這樣排定，批次檔每隔一小時就執行一次，但是我想在這批次檔中作到第一次執行時，先把Log檔清空，怎麼作呢？

if %time:~0,2% EQU 9 echo. >c:\test.log

答案是利用時間囉，因為排程是時間是9點開始，那我判斷時間是9點才執行清檔就可以，簡單吧，系統時間%time%是20:29:11.04囉

2. IF [NOT] EXIST filename command
如果有指定的檔，則條件成立，就運行命令，通常用來測試檔案是否存在
例子：我把一個註冊表的機碼匯到暫存區的一個檔，那我希望在批次檔中判斷當此檔存在時，就無聲執行它，怎麼作？

if exist %temp%\addreg.reg regedit /s %temp%\addreg.reg

3. IF [NOT] ERRORLEVEL number command
DOS程式執行完都會傳回一個數字，稱為錯誤碼errorlevel或回傳碼，如果回傳碼等於指定的數字，則條件成立，就運行命令囉
例子：如果檔案複製成功，我希望LOG.txt就會顯示"成功複製檔案"，否則就會顯示"檔案複製失敗"

XCOPY C:\AUTOEXEC.BAT D:\
IF ERRORLEVEL 1 ECHO 成功複製檔案 >c:\LOG.txt
IF ERRORLEVEL 0 ECHO 檔案複製失敗 >c:\LOG.txt

IF ERRORLEVEL 是用來測試它的上一個DOS命令的回傳值的，注意只是上一個命令的回傳值喔，而且回傳值必須依照從大到小次序順序判斷，否則會出錯喔。

自動刪除七日前的檔案

最近剛好需要，上網有找到例子，可是沒法直接用，會出錯，只好再參考指令碼存放庫的sample，改了後終於可以用了，分享給大家囉！

下面這個例子是會刪除在c:\Downloads下7天前所建立，然後副檔名為txt文字檔囉，請複製後存成vbs的檔案就可以了，也可以用在DTS裡的ActiveX Script喔

執行sp_helpdb發生錯誤

最近遇到一個問題，就是在Query Analyzer執行sp_helpdb出現下列錯誤

伺服器: 消息 515，級別 16，狀態 2，過程 sp_helpdb，行 53
無法將 NULL 值插入列 'owner'，表 'tempdb.dbo.#spdbdesc___________________________________________________________________________________________________________00010000001F'；該列不允許空值。INSERT 失敗。

然後執行第二次時又出現

伺服器: 消息 515，級別 16，狀態 2，過程 sp_helpdb，行 53
無法將 NULL 值插入列 ''，表 ''；該列不允許空值。INSERT 失敗。

當時想說，這不是系統內建的嗎?怎麼會出錯呢?
怪的是，若我用"sp_helpdb 資料庫名字"又沒問題
只好上網Goggle一下囉
原來是某個資料庫的擁有者遺失或不正確所造成

一一去查看資料庫的擁有者，還真的有資料庫擁有者那邊是空白的耶，真是見鬼了

解決辦法：執行sp_changedbowner 'sa'去指定擁有者囉

使用[查閱]轉換的注意事項

SQL Server 2005線上叢書說："依預設，查閱轉換會將整個參考資料表放在記憶體的快取中，以提供最佳的效能。如果您未選取 [進階] 索引標籤上的任何選項，查閱轉換就會使用此預設行為。如果有記憶體限制，[進階] 索引標籤上的選項可讓您只設定部份快取。"

[OLE DB來源]請使用"SQL 命令"來取得資料

最近在參考一些SSIS效能調教的文章，有提到說在使用[OLE DB來源]時，資料存取模式使用[SQL 命令]的效能會比使用[資料表或檢視]來的好

[SQL server目的地]的逾時時間

這好像是個bug，逾時預設是30秒，如果不更改為0，也就是不限制時間的話，有時執行會遇到莫名的錯誤說，我更改為0後，就正常很多了，給各位參考一下

奇怪的CHECKSUM函數

最近剛好在研究SSIS，想說如何做到當資料有異動時去更新資料，上網查到一篇文章Using a checksum to determine if a row has changed，我才知道原來有CHECKSUM這個函數啊

這個函數有趣的地方是，它會針對你給的資料清單，清單可以是資料行，然後產生一組湊雜(hash)值，線上叢書這麼稱呼的啦，比如說
SELECT CHECKSUM('a1','adalf')
--256163607
SELECT CHECKSUM('al','adalf')
--1620787439
看到沒，如果給的值不相同，產生的湊雜值也不同
用這個值當索引，就可以當湊雜索引囉
用這個值當作比較，也可以用來判斷資料是否不一樣，不一樣就需要更新
那我就想到也許可以拿來當作unique index啊
如果原本索引欄位很長，透過這個轉變索引成數值欄位，效能應該會更好
問題是線上叢書竟然說
如果變更了運算式清單中的其中一個值，清單的總和檢查碼通常也會改變。不過，總和檢查碼也有可能不會變更。
哇哩勒，意思就是不保證唯一啦，還可能會重複，連當作比較都有問題
那這樣還可以拿來作什啊，真是不好用

AS400要怎樣才能轉入SQL Server 2005呢?(二)

先參考AS400要怎樣才能轉入SQL Server 2005呢?(一)，了解一下，然後確認已經在Server上裝好Client Access 囉，我這邊指講資料來源是AS400時要怎麼設定喔，只針對SSIS的部分，如果你用的是SSMS的匯入匯出精靈，來源端的設定也大同小異啦，只是畫面長的不一樣而已囉

1.在[連接管理員]下方的空白處按右鍵，選擇[新增OLE DB連接]


2.在[設定OLE DB連接管理員]的設定畫面，按下[新增]


3.在[連接管理員]的設定畫面，在[提供者]旁的下拉選單

選擇IBMDA400的那個囉，然後按下[確定]

4. 然後在[伺服器名稱或檔名]那邊輸入AS400的ip，在使用者名稱及密碼那邊輸入連線到AS400用的帳號密碼囉，通常[允許儲存密碼]我會打勾，[初始資料庫目錄]不需設定，留空即可，很多人會誤以為這要填LIBRARY，LIBRARY是在後面才設定的，這跟設ODBC是不一樣的地方，然後按下[測試連接]

5.出現[連接測試成功]就表示OK了，然後按下[確定]


6. 確認[資料連接]出現新的設定，然後該設定的[資料連接屬性]是你剛設的資訊，再按下[確定]吧
7.然後[連接管理員]就會多一個你剛設的，在[控制流程]頁籤先拉一個[資料流程工作]，在[資料流程]頁籤再拉一個[OLE DB來源]，在[OLE DB來源]上滑鼠快點兩下

8.在[OLE DB來源編輯器]裡，[OLE DB連接管理員]選剛設好的那個， [資料存取模式]選[SQL 命令]，[SQL命令文字]請輸入SQL陳述式囉，注意此處的SQL陳述式，需指定LIBRARY，而且要用LIBRARY.TABLE，不是LIBRARY/TABLE喔，像下圖那樣喔，"SELECT * FROM LIBRARY.TABLE"

8.如果SQL命令文字不正確，按下[預覽]或左邊的[資料行]，是會跳出錯誤的視窗的
還好我有用過AS400兩年，像 "SQL0204: TEST in LBNFIL type *FILE not found."的錯誤，就是告訴你，在LBNFIL下並沒有TEST這個table囉，請檢查是LIBRARY打錯還是TABLE打錯囉

9.如果SQL命令文字正確，直接按下[資料行]，就可以看到實際的資料行囉，此時就可以按下[確定]了，來源設定完成！

OLE DB命令似乎只能處理簡單的SQL陳述式

最近在使用OLE DB命令，這個元件的用處就是能夠以來源內的每筆資料列內容當作參數，對資料表執行Insert，Update，Delete的語法，有點像Cursor一樣可作單筆處理，但使用時發現似乎只能使用簡單的SQL陳述式，inner join 的我不知怎麼下，因為在SSMS裡執行正常啊，子查詢好像確定是不能用在這囉

比如說，我使用inner join

update CUACCP5_Master_fset FLINE = 30from
dbo.CUACCP5_Master_f as f1 inner join dbo.CUACCP5_Master_p as p1on (f1.idt_key =
p1.idt_key ) where p1.Key1= ?

就出現錯誤

描述：無法繫結多重部分(Multi-Part)識別碼"p1.Key1"

然後我改成子查詢

update CUACCP5_Master_f set FLINE = 30from CUACCP5_Master_f where
CUACCP5_Master_f.idt_key in(select idt_key from CUACCP5_Master_p where Key1 =
?)

就出現錯誤

描述：無法從具有 sub-select 查詢的 SQL 陳述式衍生參數資訊。

去微軟技術論壇發問，也只有一個前輩回答我，但也得不到解決辦法，後來我想到一個辦法了，就是我把那個inner join的寫到預存程序裡，然後在OLE DB命令去執行那個預存程序囉，好麻煩啊

AS400要怎樣才能轉入SQL Server 2005呢?(一)

最近接到個任務剛好有這個需要，在SQL 2000時代，需透過Client Access(IBM iSeries Access for Windows)去轉的，然後還要設定odbc，是很麻煩的，在SQL 2005呢？原本是藉由資料通轉入SQL Server的，因為用Client Access我們沒人會啊，後來問我們400的管理人員，才知道原來資料通也是透過Client Access去轉的，那表示我應該可以直接用Client Access去轉啊，不用透過資料通，效能應該會好點，因為原本透過資料通，光轉一百五十萬筆就得花1小時了，那我這次任務，得轉四千多萬筆，那不是得轉好多天啊!所以我得去研究這個我們目前沒人會的東東，皇天不付苦心人，上網搜尋好多天，終於實作出來了，四千多萬筆花不到6小時後，已經快很多吧!不過此篇我不先介紹步驟，下篇再介紹，我先介紹Client Access裡面所提供的三個OLE DB providers的差別，這我也找很久說，最後從IBM Redbooks裡面找到的，我直接把我認為的重點轉貼出來好了，如有人認為不妥，我再刪掉好囉

The V5R3 release of iSeries Access for Windows includes three OLE DB providers:

1. New SQL-only provider (IBMDASQL) that supports SQL Commitment Control and MTS.



2. New RLA-only provider (IBMDARLA) that supports true record blocking and forward-only cursors for record level access.



3. The IBMDA400 provider, which supports SQL statements, record level access, remote command and program call, and data queues. It does not support Commitment Control for SQL commands, and does not support MTS.
   
   那紅皮書裡面還有講一些限制喔，各位可以參考參考

　　另外也許有人會問IBMDA400與IBMDASQL使用上速度有何差別？我個人反覆實測傳輸223,167筆資料下，兩個速度差異不大，只差數秒間，差一秒有差嗎？我個人覺得沒差囉

自動重開機

一般情況要自動重開機很簡單，很多小工具可以達成，但是若server的桌面是在"鎖定"的狀態下，直接排程重開機的指令是沒有作用的喔，最好的方好是先排程"登出"的指令，然後再排程"重開機"的指令囉