Datastage Desiner的快捷鍵
1 關閉某個job <Ctrl+F4>
2 <Ctrl+F6>同時打開了兩個以上job，在不同的job之間切換。
Datastage Director的快捷鍵

1 First you should already have Tortoise installed.
2 Download the plugin here:http://www.tabaquismo.freehosting.net/ignacio/eclipse/tortoise-svn/subversion.html
3 Unzip the plugin to the plugins directory in your Eclipse as u usually do.
4 Now open the Preference page of your Eclipse and you can see Tortoise if everything goes well.
5 click Tortoise and you have three fields to configure,the following is what I configured :
Path:C:\Program Files\TortoiseSVN\bin\TortoiseProc.exe
SVN Server:128.64.181.66
Shared Root Folder:/CARTCCP/CARTCCP/proj_/,your project's parent dir.

Now you can File--Import--Existing Eclipse projects into Workspace,choose your project and import.

Till now you can right-click on any files in your project and you can see shortcut menu Tortoise and here you can do update、commit and so on.

#!/bin/ksh
#######
################################################################################
#######
####### FILE: MasterControl.ksh
#######
####### DESCRIPTION: Starts a DataStage MasterControl type job passing
#######              all runtime parameter values
#######
#######
####### Date       Version   Developer      Description
####### ---------- --------- -------------- ------------------------------------
####### 2002-05-15 1.0       Ken Bland      Initial release
####### 2002-06-27 2.2       Ken Bland      FileSetDate/ProcessDate modifications
####### 2002-07-10 2.2       Steve Boyce    Added ProcessDate as 14th parameter
####### 2002-08-16 2.3       Steve Boyce    Now calls stored procedure
#######                                     GET_NEW_BATCH_NBR in datastage schema
#######                                     instead of deriving it and inserting
#######                                     here.
#######                                     Uses comSQLPlus.ksh and comPLSQL.ksh
#######                                     instead of SQLPlusStub.ksh.ksh
#######

PROG=`basename ${0}`
EXIT_STATUS=0

NOW=`date`
echo "${NOW} ${PROG} Initialization..."
echo

#######
####### CONFIGURATION ##########################################################
#######

if [ ${#} -ne 14 ]; then
   echo "${NOW} ${PROG} : Invalid parameter list."
   echo "${NOW} ${PROG} : The script needs 14 parameters:"
   echo "${NOW} ${PROG} :    JobName"
   echo "${NOW} ${PROG} :    ParameterFile"
   echo "${NOW} ${PROG} :    FileSetDate (YYYY-MM-DD)"
   echo "${NOW} ${PROG} :    BatchNumber"
   echo "${NOW} ${PROG} :    JobHierarchyFile"
   echo "${NOW} ${PROG} :    SourceSystemList"
   echo "${NOW} ${PROG} :    SubjectAreaList"
   echo "${NOW} ${PROG} :    ClearWorkArea"
   echo "${NOW} ${PROG} :    StartingMilestone"
   echo "${NOW} ${PROG} :    EndingMilestone"
   echo "${NOW} ${PROG} :    DebugMode"
   echo "${NOW} ${PROG} :    JobLinkStatisticChecksFile"
   echo "${NOW} ${PROG} :    ResurrectLogFile"
   echo "${NOW} ${PROG} :    ProcessDate (NULL|YYYY-MM-DD H24:MI:SS)"
   exit 99
fi

JobName="${1}"
ParameterFile="${2}"
FileSetDate="${3}"
BatchNumber="${4}"
JobHierarchyFile="${5}"
SourceSystemList="${6}"
SubjectAreaList="${7}"
ClearWorkArea="${8}"
StartingMilestone="${9}"
EndingMilestone="${10}"
DebugMode="${11}"
JobLinkStatisticChecksFile="${12}"
ResurrectLogFile="${13}"
ProcessDate="${14}"

echo "${NOW} ${PROG} JobName ${JobName}"
echo "${NOW} ${PROG} ParameterFile ${ParameterFile}"
echo "${NOW} ${PROG} FileSetDate ${FileSetDate}"
echo "${NOW} ${PROG} BatchNumber ${BatchNumber}"
echo "${NOW} ${PROG} JobHierarchyFile ${JobHierarchyFile}"
echo "${NOW} ${PROG} SourceSystemList ${SourceSystemList}"
echo "${NOW} ${PROG} SubjectAreaList ${SubjectAreaList}"
echo "${NOW} ${PROG} ClearWorkArea ${ClearWorkArea}"
echo "${NOW} ${PROG} StartingMilestone ${StartingMilestone}"
echo "${NOW} ${PROG} EndingMilestone ${EndingMilestone}"
echo "${NOW} ${PROG} DebugMode ${DebugMode}"
echo "${NOW} ${PROG} JobLinkStatisticChecksFile ${JobLinkStatisticChecksFile}"
echo "${NOW} ${PROG} ResurrectLogFile ${ResurrectLogFile}"
echo "${NOW} ${PROG} ProcessDate ${ProcessDate}"
echo

# Below will look in the parameters.ini file to determine the directory path each.
UserID=`whoami`
BinFileDirectory=`cat /.dshome`/bin
LogFileDirectory=`grep -w LogFileDirectory ${ParameterFile}|cut -d "=" -f2`
TempFileDirectory=`grep -w TempFileDirectory ${ParameterFile}|cut -d "=" -f2`
CommonScriptFileDirectory=`grep -w CommonScriptFileDirectory ${ParameterFile}|cut -d "=" -f2`
CommonLogFileDirectory=`grep -w CommonLogFileDirectory ${ParameterFile}|cut -d "=" -f2`
LogFileName=${CommonLogFileDirectory}/${PROG}_${JobName}.log
TEMPBATCHNBRLOG=${TempFileDirectory}/${PROG}_${JobName}_start.log
DATASTAGEPROJECT=`grep -w DATASTAGEPROJECT ${ParameterFile}|cut -d "=" -f2`
DSSERVER=`grep -w DSSERVER ${ParameterFile}|cut -d "=" -f2`
DSUSERID=`grep -w DSUSERID ${ParameterFile}|cut -d "=" -f2`
DSPASSWORD=`grep -w DSPASSWORD ${ParameterFile}|cut -d "=" -f2`

NOW=`date`
echo "${NOW} ${PROG} UserID ${UserID}"
echo "${NOW} ${PROG} BinFileDirectory ${BinFileDirectory}"
echo "${NOW} ${PROG} LogFileDirectory ${LogFileDirectory}"
echo "${NOW} ${PROG} TempFileDirectory ${TempFileDirectory}"
echo "${NOW} ${PROG} CommonScriptFileDirectory ${CommonScriptFileDirectory}"
echo "${NOW} ${PROG} CommonLogFileDirectory ${CommonLogFileDirectory}"
echo "${NOW} ${PROG} LogFileName ${LogFileName}"
echo "${NOW} ${PROG} TEMPBATCHNBRLOG ${TEMPBATCHNBRLOG}"
echo "${NOW} ${PROG} DATASTAGEPROJECT ${DATASTAGEPROJECT}"
echo "${NOW} ${PROG} DSSERVER ${DSSERVER}"
echo "${NOW} ${PROG} DSUSERID ${DSUSERID}"
echo "${NOW} ${PROG} DSPASSWORD *Protected*"
echo

#######
####### PARAMETER BUILD Without batch number ##################################
#######

if [ "${ProcessDate}" = "NULL" ]; then
   StartTimestamp=`date '+%Y-%m-%d %H:%M:%S'`
else
   StartTimestamp="${ProcessDate}"
fi
ParamList=" -param ParameterFile=${ParameterFile}"
ParamList="${ParamList} -param ProcessDate=\"${StartTimestamp}\""
ParamList="${ParamList} -param FileSetDate=${FileSetDate}"
ParamList="${ParamList} -param JobHierarchyFile=${JobHierarchyFile}"
ParamList="${ParamList} -param SourceSystemList=${SourceSystemList}"
ParamList="${ParamList} -param SubjectAreaList=${SubjectAreaList}"
ParamList="${ParamList} -param ClearWorkArea=${ClearWorkArea}"
ParamList="${ParamList} -param StartingMilestone=${StartingMilestone}"
ParamList="${ParamList} -param EndingMilestone=${EndingMilestone}"
ParamList="${ParamList} -param DebugMode=${DebugMode}"
ParamList="${ParamList} -param JobLinkStatisticChecksFile=${JobLinkStatisticChecksFile}"
ParamList="${ParamList} -param ResurrectLogFile=${ResurrectLogFile}"

#######
####### Get Batch Number and create ETL_BATCH_AUDIT record #####################
#######

echo "${NOW} ${PROG} About to get new BATCH_NBR and insert it into ETL_BATCH_AUDIT..."
${CommonScriptFileDirectory}/comPLSQL.ksh ${ParameterFile} "IRDSN" "IRUserID" \
                                           GET_NEW_BATCH_NBR \
                                              "${JobName}" \
                                              "${StartTimestamp}" \
                                              "${UserID}" \
                                              "${SourceSystemList}" \
                                              "${SubjectAreaList}" \
                                              "${ParamList}" \
                                              "${FileSetDate}" > ${TEMPBATCHNBRLOG}
SQL_EXIT_STATUS=$?
cat ${TEMPBATCHNBRLOG}
if [ "${SQL_EXIT_STATUS}" != 0 ]; then
   NOW=`date`
   echo "${NOW} ${PROG} Failure to connect/insert into ETL_Batch_Audit table!"
   exit ${SQL_EXIT_STATUS}
fi

#######
####### Get BATCH_NBR from batch number log file ##############################
#######
BatchNumber=`grep -w BATCH_NBR ${TEMPBATCHNBRLOG}|cut -d "=" -f2`
if [ -z "${BatchNumber}" ]; then
   NOW=`date`
   echo "${NOW} ${PROG} Failure to retrieve BATCH_NBR from ${TEMPBATCHNBRLOG}"
   exit ${SQL_EXIT_STATUS}
fi

#######
####### Add batch number to list of parameters #################################
#######
ParamList="${ParamList} -param BatchNumber=${BatchNumber}"

NOW=`date`
echo
echo ${NOW} ${PROG} Parameter list: ${ParamList}
echo

#######
####### DataStage EXECUTION ####################################################
#######

NOW=`date`
echo "${NOW} ${PROG} Executing DataStage dsjob program..."

echo ${BinFileDirectory}/dsjob -server ${DSSERVER} -user ${DSUSERID} -password ${DSPASSWORD} -run -wait ${ParamList} ${DATASTAGEPROJECT} ${JobName} 2>&1 > ${LogFileName}
echo
echo "${BinFileDirectory}/dsjob -server ${DSSERVER} -user ${DSUSERID} -password ${DSPASSWORD} -run -wait ${ParamList} ${DATASTAGEPROJECT} ${JobName} 2>&1 > ${LogFileName}"
echo

eval ${BinFileDirectory}/dsjob -server ${DSSERVER} -user ${DSUSERID} -password ${DSPASSWORD} -run -wait ${ParamList} ${DATASTAGEPROJECT} ${JobName} 2>&1 >> ${LogFileName}

jobwaiting=`grep "Waiting for job..." ${LogFileName}`
if [ "${jobwaiting}" != "Waiting for job..." ]; then
   NOW=`date`
   echo ${NOW} ${PROG} "DataStage failed to start the job"
   failedstart=1
else
   NOW=`date`
   echo ${NOW} ${PROG} "DataStage successfully started the job"
   failedstart=0
fi
NOW=`date`
echo ${NOW} ${PROG} "Retrieving job information"

${BinFileDirectory}/dsjob -server ${DSSERVER} -user ${DSUSERID} -password ${DSPASSWORD} -jobinfo ${DATASTAGEPROJECT} ${JobName} >> ${LogFileName}

#######
####### CHECK STATUS ###########################################################
#######

ERROR=`grep "Job Status" ${LogFileName}`
ERROR=${ERROR##*\(}
ERROR=${ERROR%%\)*}

if [ "${failedstart}" != 0 ]; then
   NOW=`date`
   echo ${NOW} ${PROG} "The job failed to start"
   AuditStatus="FAILURE"
   Comments="MasterControl aborted"
   EXIT_STATUS=1
else
   if [ "${ERROR}" = 1 -o "${ERROR}" = 2 ]; then
      NOW=`date`
      echo ${NOW} ${PROG} "The job completed successfully"
      AuditStatus="SUCCESS"
      Comments=""
      EXIT_STATUS=0
   else
      NOW=`date`
      echo ${NOW} ${PROG} "The job aborted"
      AuditStatus="FAILURE"
      Comments="MasterControl aborted"
      EXIT_STATUS=1
   fi
fi

FailedJobCount=`grep -i FAILED ${LogFileDirectory}/${JobName}.log|wc -l|cut -b1-9`
FailedJobCount=`expr ${FailedJobCount} + 0`
echo ${NOW} ${PROG} The number of failed jobs is [${FailedJobCount}]

if [ "${FailedJobCount}" != 0 ]; then
   NOW=`date`
   echo ${NOW} ${PROG} "The job had failed processes"
   AuditStatus="FAILURE"
   Comments="MasterControl had ${FailedJobCount} failed processes"
   EXIT_STATUS=1
fi
StoppedJobStreamCount=`grep "JOB STREAM STOPPED" ${LogFileDirectory}/${JobName}.his|wc -l|cut -b1-9`
StoppedJobStreamCount=`expr ${StoppedJobStreamCount} + 0`
if [ "${StoppedJobStreamCount}" != 0 ]; then
   NOW=`date`
   echo ${NOW} ${PROG} "The job stream was STOPped or KILLed"
   AuditStatus="FAILURE"
   Comments="MasterControl job stream was STOPped or KILLed"
   EXIT_STATUS=1
fi

#######
####### AUDIT ##################################################################
#######

echo
echo "${NOW} ${PROG} About to update ETL_BATCH_AUDIT with status information..."
EndTimestamp=`date '+%Y-%m-%d %H:%M:%S'`

SQLstring="UPDATE ETL_BATCH_AUDIT A \
              SET A.END_TIMESTAMP = TO_DATE('${EndTimestamp}','YYYY-MM-DD HH24:MI:SS'), \
                         A.STATUS = '${AuditStatus}', \
                       A.COMMENTS = '${Comments}', \
                A.RUNTIMESETTINGS = '${ParamList}' \
            WHERE (A.BATCH_NBR = ${BatchNumber});"

NOW=`date`
echo ${NOW} ${PROG} Audit SQL ${SQLstring}
SQLScriptFileName=${TempFileDirectory}/${PROG}_${JobName}_end.sql
echo ${SQLstring} > ${SQLScriptFileName}

${CommonScriptFileDirectory}/comSQLPlus.ksh ${ParameterFile} IRDSN IRUserID ${SQLScriptFileName}
SQL_EXIT_STATUS=$?
if [ "${SQL_EXIT_STATUS}" != 0 ]; then
   NOW=`date`
   echo ${NOW} ${PROG} Failure to connect/update into ETL_Batch_Audit table!
   exit ${SQL_EXIT_STATUS}
fi

#######
####### EXIT ###################################################################
#######

NOW=`date`
echo ${NOW} ${PROG} Complete, exiting with status [${EXIT_STATUS}]
exit ${EXIT_STATUS}


好東西，先收著，再研究。

在DataStage中job不能刪除，如果試著編譯又出現如下錯誤：
Error calling subroutine: DSR_EXECJOB (Action=1); check DataStage is set up correctly in project ETUDES
(Subroutine failed to complete successfully (30107))

+

(40503) A call to an OLE server has failed, or a runtime error occured within the ole itself.

解決辦法：
1 首先運行命令：DELETE FROM DS_JOBS WHERE NAME = 'JobName'
2 再運行DS.CHECKER ，注意要運行DS.CHECKER必須要有exclusive access到project
，通過如下命令可以看到有誰還連接到了服務器：netstat -a |grep dsrpc

                                                            一 BASIC介紹

1 一般的BASIC程序是如下一個格式
[ label ] statement [ ; statement … ] <Return>
2 關于subroutine
a 如果嵌入到了程序中用GOSUB調用，如果在外部，使用CALL
b 使用return返回到主程序，如果在return之前subroutine結束了，自動return
c 如果subroutine中有ABORT STOP CHAIN則永遠回不到主程序。
3 注釋
以REM * ! $^* 開頭的為注釋
4 創建一個BASIC 程序的步驟
使用CREATE.FILE命令創建一個type 1或type 19 DataStage file來保存BASIC程序的源碼。(在DSEngine目錄下運行CREATE.FILE命令，它將提示你輸入文件名等參數)

使用DataStage Editor或其他編輯器創建BASIC程序源碼，這個源碼成為你剛才創建的文件的一條記錄。

使用BASIC命令來編譯你程序，這將創建一個包含目標代碼的文件。目標文件的名字不重要，因為總是通過源文件名來引用程序。

可以用RUN命令來執行你的程序，使用RAID命令來調試你的程序。

                                                                二、基礎知識
數據類型、變量和操作符
所有數據在內部都是以字符串存儲，在運行時轉換為具體類型。
主要有三種數據類型：字符串、數字型、和unknown（null）

字符串
在BASIC源碼中，字符串產量是ASCII字符的序列，所以不能有0（null）和10（linefeed）字符，而是要使用CHAR(0)和CHAR(10)
數字型
范圍從10(-307)到10(307)，最多15位小數
unknown
null在內部是由單一字節CHAR(128)代表的，是一個string，可以用系統變量@NULL.STR來引用它。在BASIC程序中可以使用系統變量@NULL來引用null值。
一個字符串與null連接結果為null

數組
dimensioned array
在向量（一維數組）中，第一個元素的index為1
向量可以看做第二位為1的二維數組（矩陣）。
矩陣和向量的第一個元素分別用vector.name(0)和matrix.name(0,0)表示。它可以在MATPARSE、MATREAD和MATWRITE語句中用來存儲不合格的字段。

動態數組
動態數組的最高層是字段，由(F)分割。字段包含有(V)分割的值組成。每個值又可以包含由(S)分割的subvalue。

字符串的比較
字符串的比較是一個個的比較每個字符的ASCII的值。如果兩個字符串可以轉換為數字，總是比較兩個數字的數值大小。任何值與null相比較的結果都是unknown。空串不是簡單的等于0?？崭窨醋鲂∮?。

字符串匹配
X代表任何字符 A代表所有字母 N代表所有數字 前面的數字代表重復次數。如果前面的數字為0，代表后面的字符重復0到N次的任意次數都可以。

0是false 空串是false null非true非false

鎖 事務和隔離級別
鎖
鎖有兩種粒度：細粒度（記錄鎖定）和粗粒度（文件鎖定）
DataStage支持以下幾種鎖：
記錄共享鎖
記錄更新鎖
文件共享鎖
文件意向（intent）鎖 介于共享與排他之間
文件排他鎖

事務
事務可以嵌套
子事務在父事務提交或回滾之前開始的話，子事務將變成活動事務，而父事務將繼續存在但是是非活動的。當子事務提交或回滾之后，父事務將再次變為活動的。
在最高層的事務提交之前，將緩存所有子事務的數據庫操作。當進行一個讀取操作時，首先會去讀取緩存的數據，如果找不到才會從數據庫文件中讀取。

論壇上見到的，記錄一下，以備後用
Process p = Runtime.getRuntime().exec( MProperties.DS_EXE_PATH + "/dsjob -run -wait -param lowDate=" + lowDate + " -param highDate=" + highDatePlusOne + " -param dsOutDir=" + MProperties.DS_OUT_DIR + " -param IDB_user=" +MProperties.IDB_USER + " -param IDB_pass=" +
MProperties.IDB_PASS + " " + MProperties.DS_PROJECT + " " + MProperties.DS_JOB);
p.waitFor();
return p.exitValue() == 0;

另外如果有RTI，更好的方法如下：
The best way for this to be accomplished is by using the RTI (Real Time Integration) capabilities that we added in 7.x. This allows you to fully expose a DataStage job as a service, to be then invoked via several different mechanisms, depending on your needs....

.....the job can be exposed as a classic web service, using SOAP over HTTP...we generate and publish the WSDL for this within RTI...

....the job can be exposed via our EJB binding and called appropriately

....the job can be exposed for contact via JMS, and either passed a text message or SOAP envelope....

Not only can you simply pass job parameters via this architecture, you can actually have the entire transformation job itself (columns on the ultimate input and output links) passed and returned to your calling client.

Additionally, the entire job control API, which you may already be familiar with in its API or command line implementations, has been exposed as a set of methods available via Web Services.
主要就是通過將job包裝為web service或EJB或JMS來調用，可以傳參數，還可以將整個job本身（最終的輸入和輸出鏈接）作為參數傳遞或者返回到調用的客戶端。

通過右鍵添加link
鼠標右鍵點擊起始stage，按住右鍵移動鼠標到目標stage。還有一種方法就是把鼠標放在起始stage的邊緣等到變為一個小圓圈里面有一個叉的時候拖動鼠標到目標stage。

DataStage中默認和隱式類型轉換時注意的問題
當從源向目標映射數據時，如果類型不一致，對于有些類型我們需要在modify或transfomer stage中通過函數進行轉換，對于有些系統會自動完成類型轉換，在類型轉換過程中，注意以下幾點：
1 在變長到固定長度字符串的默認轉換中，parallel jobs用空格（ASCII 20）字符來填充剩余長度。聯系到前面做的一個parallel job，當目標是變長時（當時源也是變長，但是好像源的精度要小些），但是字符串實際的長度沒有指定的變長那么長的話，系統會自動用空格（ASCII 20）來填充（具體在哪個stage填充的不知），而且環境變量APT_STRING_PADCHAR的默認值也是空格（ASCII 20）。
2 通過環境變量APT_STRING_PADCHAR可以改變默認的填充字符null（ASCII 0），同時一般還要加上trim就能解決空格問題，此問題非常普遍。
3 有個PadString 函數可以用來用指定的字符來填充一個變長的字符串到指定的長度。這個函數的參數不能使固定長度字符串，如果是固定長度的先轉化為變長。
 
Copy Stage作為占位符
在job開發中，當你暫時不知道該使用哪個stage時，可以使用copy stage作為占位符，注意不要把Force屬性設為True，在不把Force設為True時，復制將在運行時進行優化。

使用Schema file來指定meta data
1 可以通過列定義和schema file兩種方式來指定meta data，值得注意的一點是Note that, if you use a schema file, you should ensure that runtime column propagation is turned on. Otherwise the column definitions specified in the stage editor will always override any schema file.即，如果想通過schema file來指定meta data，必須勾上runtime column propagation，否則總是使用列定義來作為meta data。
2 If you are using a schema file on an NLS system, the schema file needs to be in UTF-8 format.如果你的datastage使用了NLS system，那么schema file必須是UTF-8格式的。文檔中說可以使用Sequential stage來完成轉碼。
 
通過Server Shared Container在Parallel Job中添加Server Job Stage
You create a new shared container in the DataStage Designer, add Server job stages as required, and then add the Server Shared Container to your Parallel job and connect it to the Parallel stages.在Designer中創建一個server shared container，添加需要的Server job stage，再把server shared container添加到parallel job并連接到其他parallel stage。
 
幾個環境變量的具體路徑
%SERVERENGINE% – DataStage engine account directory(normally C:\Ascential\DataStage\Engine on Windows and <dsadminhome>\Ascential\DataStage\DSEngine on UNIX).
%PROJECT% – Current project directory.
%SYSTEM% – System directory on Windows NT or /usr/lib on UNIX.

一句不太懂的話，先做記錄
If NLS is enabled, avoid using the built-in Iconv and Oconv functions to map data unless you fully understand the consequences of your actions.在使用了NLS時，盡量避免使用Iconv and Oconv來映射日期，因為可能產生一些意想不到的結果。

I installed Chinese version of Eclipse.When I want to switch to English version,I use -nl en_us option and everything is just ok.

One day,I installed tortoise svn plug-in.In order to configure the svn plug-in,I first use -nl en_us option to start my Eclipse (just as I often do),then I open preference,hoping to see the tortoise in the preference and configure it.The problem is that I can not see tortoise in the preference as supposed.After trying many means to figure out the problem,I get the right answer:do not use -nl en_us option to start Eclipse and you can see tortoise in the preference.You need only to do this once,then you can use -nl en_us option to start your Eclipse as you usually do and you can still see tortoise in the preference.

???? 現在你在各大網站、論壇、博客都能看到SOA的身影，有很多人都在懷疑SOA是不是另一個buzzword。不過J-SOFA 對這種看法做出了一些回擊，它給出了一些很實際的東西讓我們感受SOA的力量。你可以去http://www5f.biglobe.ne.jp/~webtest/jsofa/具體了解一下。
下面是它的首頁對它的介紹：
J-SOFA是編排服務組件的一個輕量級框架，這些服務組件包括JSF中的Backing Bean 和Struts中的Action，等等。它使得我們可以把業務流程從動作中分離出來并且它還幫助我們將業務邏輯實現為服務。這讓你的應用成為實現了SOA的應用。