Sql_Trace File Identifier

我們設(shè)置了sql_trace為true之后，trace file會dump到udump中，但是該目錄下也許已經(jīng)有很多trace file了，或者其他session也設(shè)置了sql_trace為true，也在產(chǎn)生trace file。如何能迅速找到你的session產(chǎn)生的trace file呢？我們可以通過如下語句設(shè)置Sql_Trace File Identifier：

這樣你的session產(chǎn)生的trace file就會以設(shè)置的identifier為后綴。

 

然后我們可以看到udump下生成的trace file文件名為：test_ora_4168_test_identifier.trc

Sample Block

抽樣表掃描（sample table scan）只讀取一個(gè)表的部分?jǐn)?shù)據(jù)。其實(shí)我們只選擇表的部分?jǐn)?shù)據(jù)的話，可以用rownum來限制也很方便。但是考慮如下情況：
A表和B表join，我只想A表的部分?jǐn)?shù)據(jù)去連接B表，此時(shí)用sample就方便的多。如果用rownum就需要用一個(gè)subquery。

抽樣表掃描有兩種形式，一種是抽樣表中行數(shù)的一個(gè)百分比，一個(gè)是抽樣表中塊數(shù)的一個(gè)百分比。注意都是百分比。語法如下：

也可以在視圖上使用SAMPLE。

Kimball在他的ETL Toolkit一書中，一直強(qiáng)調(diào)：對于有外鍵約束的表，為了提高load的速度，可以先將外鍵約束disable，加載完成后再enable（當(dāng)然另外一個(gè)原因是進(jìn)入數(shù)據(jù)倉庫的數(shù)據(jù)都是規(guī)范的，甚至可以考慮不使用外鍵約束，即違反約束的數(shù)據(jù)在Transformation部分就應(yīng)該解決掉，當(dāng)然這是設(shè)計(jì)問題了）。ps:另外兩個(gè)提高load速度的方法分別為使用sql loader和insert、update分離。當(dāng)時(shí)還不是明白這個(gè)過程怎么具體實(shí)現(xiàn)，今天看《oracle concept》看到下面話終于明白了一些：
Flexibility for Data Loads and Identification of Integrity Violations
You can disable integrity constraints temporarily so that large amounts of data can be loaded without the overhead of constraint checking. When the data load is complete,you can easily enable the integrity constraints, and you can automatically report any new rows that violate integrity constraints to a separate exceptions table.
看來oracle這點(diǎn)上做的比較完善，還可以將違反完整性約束的數(shù)據(jù)自動記錄到一個(gè)exception表中。

今天google分析函數(shù)row_number()的時(shí)候發(fā)現(xiàn)的，覺得很好：

表demo是重復(fù)拷貝自dba_objects，有88萬左右，不重復(fù)的是27323，沒有索引
方法一：delete from demo a where a.rowid <> (select max(rowid) from demo b where
b.object_id=a.object_id);
耗時(shí)：幾個(gè)小時(shí)以上
方法二： delete from demo where rowid in
(select rid from
(select rowid rid,row_number() over(partition by object_id order by rowid) rn
from demo)
where rn <> 1 );
耗時(shí)：30秒
方法三： create table demo2 as
select object_id,owner... from
(select demo.*,row_number() over(partition by object_id order by rowid) rn from demo)
where rn = 1;
truncate table demo; insert into demo select * from demo2; drop table demo2;
共耗時(shí)： 10秒，適合大數(shù)據(jù)量的情況，產(chǎn)生更少回滾量；

學(xué)到了分析函數(shù)row_number(),對于object_id和rowid也有了一些認(rèn)識。oracle要學(xué)的東西太多了，什么時(shí)候是個(gè)頭啊。上面的方法不是很難理解，但也還沒有完全理解，有機(jī)會實(shí)際試試。

今天在論壇看到的方法，先記錄下來：
方法一：
在sequential file stage中有個(gè)屬性可以ignore第一行。對于footer，如果footer使用了與正文不同的格式，可以用使用transformer stage和 @INROWNUM stage來將footer去掉。
方法二：
在sequential file的filter option中使用sed -e '$d' -e '1d'來講header和footer去掉。
方法三：
寫個(gè)before job subroutine，可以先對這個(gè)文件做各種處理，不過我想也是subroutine中調(diào)用DSExecute sed -e '$d' -e '1d'。當(dāng)然Basic中也有專門對sequential file處理的函數(shù)。

Orchadmin is a command line utility provided by datastage to research on data sets.
The general callable format is : $orchadmin [options] [descriptor file]

1. Before using orchadmin, you should make sure that either the working directory or the $APT_ORCHHOME/etc contains the file “config.apt” OR

The environment variable $APT_CONFIG_FILE should be defined for your session.
Orchadmin commands

The various commands available with orchadmin are
1. CHECK: $orchadmin check

Validates the configuration file contents like , accesibility of all nodes defined in the configuration file, scratch disk definitions and accesibility of all the nodes etc. Throws an error when config file is not found or not defined properly

2. COPY : $orchadmin copy
Makes a complete copy of the datasets of source with new destination descriptor file name. Please not that

a. You cannot use UNIX cp command as it justs copies the config file to a new name. The data is not copied.

b. The new datasets will be arranged in the form of the config file that is in use but not according to the old confing file that was in use with the source.

3. DELETE : $orchadmin <> [-f -x] descriptorfiles….
The unix rm utility cannot be used to delete the datasets. The orchadmin delete or rm command should be used to delete one or more persistent data sets.
-f options makes a force delete. If some nodes are not accesible then -f forces to delete the dataset partitions from accessible nodes and leave the other partitions in inaccesible nodes as orphans.
-x forces to use the current config file to be used while deleting than the one stored in data set.

4. DESCRIBE: $orchadmin describe [options] descriptorfile.ds
This is the single most important command.
1. Without any option lists the no.of.partitions, no.of.segments, valid segments, and preserve partitioning flag details of the persistent dataset.
-c : Print the configuration file that is written in the dataset if any
-p: Lists down the partition level information.
-f: Lists down the file level information in each partition
-e: List down the segment level information .
-s: List down the meta-data schema of the information.
-v: Lists all segemnts , valid or otherwise
-l : Long listing. Equivalent to -f -p -s -v -e

5. DUMP: $orchadmin dump [options] descriptorfile.ds
The dump command is used to dump(extract) the records from the dataset.
Without any options the dump command lists down all the records starting from first record from first partition till last record in last partition.
-delim ‘’ : Uses the given string as delimtor for fields instead of space.
-field : Lists only the given field instead of all fields.
-name : List all the values preceded by field name and a colon
-n numrecs : List only the given number of records per partition.
-p period(N) : Lists every Nth record from each partition starting from first record.
-skip N: Skip the first N records from each partition.
-x : Use the current system configuration file rather than the one stored in dataset.

6. TRUNCATE: $orchadmin truncate [options] descriptorfile.ds
Without options deletes all the data(ie Segments) from the dataset.
-f: Uses force truncate. Truncate accessible segments and leave the inaccesible ones.
-x: Uses current system config file rather than the default one stored in the dataset.
-n N: Leaves the first N segments in each partition and truncates the remaining.

7. HELP: $orchadmin -help OR $orchadmin -help
Help manual about the usage of orchadmin or orchadmin commands

當(dāng)一條記錄過來，某個(gè)字段為空，而在transformer中又對該字段trim了，由于該字段為空，不能trim，所以DataStage會認(rèn)為這是個(gè)錯(cuò)誤，從而把這個(gè)記錄drop了，這與實(shí)際業(yè)務(wù)不符合，非主鍵字段為空并不能就把這條記錄drop了。我們可以通過寫一個(gè)判斷來解決該問題：if LK_1.EMAIL <> '' then trim(LK_1.EMAIL) else LK_1.EMAIL

1 before/after job和before/after stage,可以執(zhí)行shell，命令行以及一些DataStage API，如DSJobReport
2 job sequence中有個(gè)stage，可以在里面寫控制job調(diào)度的地方
3 每個(gè)job的properties中有個(gè)job control tab
4 命令行可以執(zhí)行、導(dǎo)入job等，它通過dsjob實(shí)現(xiàn)，在DS Manager的文檔中還講了有專門的命令行來導(dǎo)入job
5 可以編寫shell來控制job，對dsjob命令做封裝，可以傳入?yún)?shù)等。
對于上面這些概念還是很模糊，先記錄在此。

今天又碰到新問題，在transformer中，進(jìn)去是有很多record，出來之后為0條record，用peek，看了一下，進(jìn)去的數(shù)據(jù)的某個(gè)字段數(shù)據(jù)全為null，而在transformer中對此字段trim了，改為不trim就可以了。

今天遇到一個(gè)問題，job不停的在那里運(yùn)行，然后link上的數(shù)據(jù)顯示各個(gè)環(huán)節(jié)都是正確的，包括最后插入數(shù)據(jù)庫的link上也顯示了數(shù)據(jù)，但是最后數(shù)據(jù)庫里并沒有數(shù)據(jù)。在Director的log中，日志在從兩個(gè)源文件把所有數(shù)據(jù)load出來完之后，日志就死在那里了。
以前這個(gè)job是正確的，昨天由于重新load其中一個(gè)源文件的元數(shù)據(jù)，所以出現(xiàn)了上述問題。所以先前以為是由于load的新的源數(shù)據(jù)有問題，就從此處來找問題的原因，并且認(rèn)為可能是改了元數(shù)據(jù)，在其他地方映射的時(shí)候有位置不對的地方，所以整了很久。因?yàn)橐郧笆呛煤玫模缓笥忠詾槭欠?wù)器的問題。

這都是定勢思維的錯(cuò)誤，然后又一急，所以浪費(fèi)了很多時(shí)間，其實(shí)很多時(shí)候都是這樣，出了問題我們不能理性的好好思考。

其實(shí)問題很簡單：
如果我們按照正常邏輯來分析的話，既然不能讀入數(shù)據(jù)庫，肯定是數(shù)據(jù)不符合數(shù)據(jù)庫對數(shù)據(jù)的約束，包括主鍵啊，非空啊，本問題就是由于在stage的不斷流轉(zhuǎn)中產(chǎn)生了很多空格，使得最后待插入的數(shù)據(jù)長度遠(yuǎn)遠(yuǎn)大于數(shù)據(jù)庫中定義的字段長度。由于在那里不斷reject，所以影響了速度，job一直在那里運(yùn)行。最后用APT_STRING_PADDER,將其設(shè)為0x0（用null代替空格）搞定。
ps：在插入數(shù)據(jù)庫時(shí)使用一個(gè)reject文件對查錯(cuò)有好處，這樣能看到reject是些什么數(shù)據(jù)，然后就能知道為什么被reject了。
同時(shí)我們可以得出如果最后插入數(shù)據(jù)庫時(shí)很多數(shù)據(jù)被reject，但是你并沒有用一個(gè)reject文件來接收這些reject掉的數(shù)據(jù)，將使得job基本處于停滯狀態(tài)。

當(dāng)一個(gè)Job跑到一半終止了要還原，在DS Director中主要有三種方法來還原：
1 Job-----Reset
2 Job-----Clear Status File
3 Job-----Cleanup Resource
具體細(xì)節(jié)也不懂，有時(shí)間慢慢研究

Datastage Desiner的快捷鍵
1 關(guān)閉某個(gè)job <Ctrl+F4>
2 <Ctrl+F6>同時(shí)打開了兩個(gè)以上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不能刪除，如果試著編譯又出現(xiàn)如下錯(cuò)誤：
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 首先運(yùn)行命令：DELETE FROM DS_JOBS WHERE NAME = 'JobName'
2 再運(yùn)行DS.CHECKER ，注意要運(yùn)行DS.CHECKER必須要有exclusive access到project
，通過如下命令可以看到有誰還連接到了服務(wù)器：netstat -a |grep dsrpc

                                                            一 BASIC介紹

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

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

使用BASIC命令來編譯你程序，這將創(chuàng)建一個(gè)包含目標(biāo)代碼的文件。目標(biāo)文件的名字不重要，因?yàn)榭偸峭ㄟ^源文件名來引用程序。

可以用RUN命令來執(zhí)行你的程序，使用RAID命令來調(diào)試你的程序。

                                                                二、基礎(chǔ)知識
數(shù)據(jù)類型、變量和操作符
所有數(shù)據(jù)在內(nèi)部都是以字符串存儲，在運(yùn)行時(shí)轉(zhuǎn)換為具體類型。
主要有三種數(shù)據(jù)類型：字符串、數(shù)字型、和unknown（null）

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

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

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

字符串的比較
字符串的比較是一個(gè)個(gè)的比較每個(gè)字符的ASCII的值。如果兩個(gè)字符串可以轉(zhuǎn)換為數(shù)字，總是比較兩個(gè)數(shù)字的數(shù)值大小。任何值與null相比較的結(jié)果都是unknown。空串不是簡單的等于0。空格看做小于0。

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

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

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

事務(wù)
事務(wù)可以嵌套
子事務(wù)在父事務(wù)提交或回滾之前開始的話，子事務(wù)將變成活動事務(wù)，而父事務(wù)將繼續(xù)存在但是是非活動的。當(dāng)子事務(wù)提交或回滾之后，父事務(wù)將再次變?yōu)榛顒拥摹?br>在最高層的事務(wù)提交之前，將緩存所有子事務(wù)的數(shù)據(jù)庫操作。當(dāng)進(jìn)行一個(gè)讀取操作時(shí)，首先會去讀取緩存的數(shù)據(jù)，如果找不到才會從數(shù)據(jù)庫文件中讀取。

論壇上見到的，記錄一下，以備後用
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來調(diào)用，可以傳參數(shù)，還可以將整個(gè)job本身（最終的輸入和輸出鏈接）作為參數(shù)傳遞或者返回到調(diào)用的客戶端。

通過右鍵添加link
鼠標(biāo)右鍵點(diǎn)擊起始stage，按住右鍵移動鼠標(biāo)到目標(biāo)stage。還有一種方法就是把鼠標(biāo)放在起始stage的邊緣等到變?yōu)橐粋€(gè)小圓圈里面有一個(gè)叉的時(shí)候拖動鼠標(biāo)到目標(biāo)stage。

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

使用Schema file來指定meta data
1 可以通過列定義和schema file兩種方式來指定meta data，值得注意的一點(diǎn)是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來完成轉(zhuǎn)碼。
 
通過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中創(chuàng)建一個(gè)server shared container，添加需要的Server job stage，再把server shared container添加到parallel job并連接到其他parallel stage。
 
幾個(gè)環(huán)境變量的具體路徑
%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時(shí)，盡量避免使用Iconv and Oconv來映射日期，因?yàn)榭赡墚a(chǎn)生一些意想不到的結(jié)果。