DataStage批量导入导出脚本

本文遵循BY-SA版权协议，转载请附上原文出处链接。


本文作者: 黑伴白

本文链接: http://heibanbai.com.cn/posts/3579bfdd/

DataStage批量导入导出

在实际开发中，进行DataStage作业的批量导入导出，可以减少很多重复工作，提供工作效率

通过istool工具，我们就可以实现ds作业的批量导入导出功能，具体如下:

作业导出命令:

1
istool.sh export -domain $ip:$port -u $user -p $passwd  -ar ${dsnm}.isx -ds '"'${dsfile}'"'

作业导入命令:

1
istool.sh import -domain $ip:port  -u $user -p $passwd  -ar ${dsnm}.isx -replace  -ds '"'/$pjtnm'"'

作业编译命令:

1
dscc /h $dns /u $user /p $passwd $projectnm /j $dsname

脚本源码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
#!/bin/bash

################################################################################################################################
#desc   : This program is used for exporting or importing Datastage jobs in lunix environment.
#name   : ds_eximport.sh
#version: 7.1
#Mod 7.1: Export Jobs use the JIRA.no (select job from dasdb where job_short_desc in JIRA.no), one job one isx
#Mod 7.2: 增加同步导入导出159环境DataStage
################################################################################################################################

cd ~
source .profile

clear

ipaddress=`hostname -i`
hostname=`hostname`
exportId=`date +"%m%d%H%M%S"`
mdate=`date "+%Y%m%d"`




if [ $# -eq 2 ];then
    keyword=$1
    keyword=`echo $keyword|tr "[a-z]" "[A-Z]"`
    file_name=$2
    file_dir=DS-${mdate}
elif [ $# -eq 3 ];then
    sysname=$1
    keyword=$2
    keyword=`echo $keyword|tr "[a-z]" "[A-Z]"`
    file_name=$3
    file_dir=${sysname}-${mdate}
else
    echo "Please input the right parameter ..."
    echo "Eg:"
    echo "sh ds_eximport.sh export/import JIRA.list"
    echo "or"
    echo "sh ds_eximport.sh DS export/import JIRA.list"
    exit -1
fi

#log dir
logpath="/home/DS/file/log/shelllog/ds_eximport/${mdate}/"
#logFile
logfile="${logpath}/${keyword}_${hostname}_${exportId}.log"


#Log function
Log()
{
    createTime=`date  +"%Y-%m-%d %H:%M:%S"`
    echo "[${createTime}] $*" |tee -a ${logfile} 2>/dev/null 
}

#Check dir function
CheckDir()
{
    if [ ! -d $1 ]; then
        mkdir_log=` mkdir -p -m 775 $1 `
        if [ $? -ne 0 ]; then
           echo "[$1] create fail!"
           echo "[$1] does not exist!!please check..."
           exit 1
        fi    
    fi
}
CheckDir "$logpath"

#开发、UAT、版本环境信息  #DSJ2PJQ-开发  #DS1PSD、DSNUAT-UAT  #DSPJQ-版本
if [ ${hostname} == "DSJ2PJQ" -o ${hostname} == "DS1PSD" -o ${hostname} == "DSNUAT" -o ${hostname} == "DSPJQ" ];then
    eximportpath="/home/DS/file/version/"
    listDir="/home/DS/file/version/"
    dsuser=$DSUSER
    dspassword=`java -jar /home/DSsbin/DSSHELL/java/jiemi.jar $DSPWD`
    #DS资料库的用户密码
    dbuser=$DSDBUSER
    dbpass=`java -jar /home/DSsbin/DSSHELL/java/jiemi.jar $DSDBPWD`
#准生产环境信息 目录相关和生产保持一致
elif [ ${hostname} == "DSJPSD" ];then
    eximportpath="/home/DS/file/tmp/version/${file_dir}/SQL_DS/"
    listDir="/home/DS/file/tmp/version/${file_dir}/"
    dsuser=$DSUSER
    dspassword=`java -jar /home/DSsbin/DSSHELL/java/jiemi.jar $DSPWD`
    #DS资料库的用户密码
    dbuser=$DSDBUSER
    dbpass=`java -jar /home/DSsbin/DSSHELL/java/jiemi.jar $DSDBPWD`
#生产环境信息 4个DS集群
elif [ ${hostname} == "MDSDS01" -o ${hostname} == "MDSDS04" -o ${hostname} == "MDSDS07" -o ${hostname} == "MDSDS10" ];then
    eximportpath="/home/DS/file/tmp/version/${file_dir}/SQL_DS/"
    listDir="/home/DS/file/tmp/version/${file_dir}/"
    dsuser=D_user
    dspassword=`java -jar /home/DSsbin/DSSHELL/java/jiemi.jar "3MwCAjIwMTkwMzIwMTQ1MjMyMDA1NhAf+TgHO77olFnvI0WgAz72EAAAAEKqkrMyPOhc9UYm/QRK1lA="`
    #DS资料库的用户密码
    dbuser=dasuser
    dbpass=`java -jar /home/DSsbin/DSSHELL/java/jiemi.jar "3MwCAjIwMTkwMzIwMTQ1NDQ0MDU5MxBiOaucB9ihtBI65YJwX5Z4EAAAAK4Svtuu97iwUN/IL6wmBPw="`
else
    echo "No this mathine[${hostname}:${ipaddress}], please check..."
    echo "No this mathine[${hostname}:${ipaddress}], please check...">>${logfile}
    exit -1
fi

#各环境的DS资料库编目数据库名称不一致，需区分定义
if [ ${hostname} == "DSJ2PJQ" ];then
    dbname=DASDB
elif [ ${hostname} == "DS1PSD" ];then
    dbname=DASDB
elif [ ${hostname} == "DSNUAT" ];then
    dbname=DASDB1
elif [ ${hostname} == "DSPJQ" ];then
    dbname=DASDB
elif [ ${hostname} == "DSJPSD" ];then
    dbname=DASDB
elif [ ${hostname} == "MDSDS01" ];then
    dbname=DSDB1
elif [ ${hostname} == "MDSDS04" ];then
    dbname=DSDB2
elif [ ${hostname} == "MDSDS07" ];then
    dbname=DSDB3
elif [ ${hostname} == "MDSDS10" ];then
    dbname=DSDB4
fi

#Jira编号清单的绝对路径
list="${listDir}/${file_name}"
if [ ! -f $list ];then
    Log "The list file [$list] not exist, please check..."
    exit -1
fi

Log "--------------------------------------Datastage export & import Tools-------------------------------------------------------"
#Check sys env
if [ ! -f /home/dsinst/InformationServer/Clients/istools/cli/istool.sh ]
then
    Log "This machine `hostname -i` does not support istool command."
    exit -1
fi

#Export jobs which short_desc in the listfile
if [ "$keyword" == "EXPORT" ];then
    for line in `cat $list`
    do
        jiraInfo=$line
        line=`echo $line|awk -F'|' '{print $1}'`
        CheckDir "${eximportpath}/tmp/${line}_${exportId}/"
        if [ -d ${eximportpath}/${line}/ ];then
            ls ${eximportpath}/${line}/|grep "\.isx"$>/dev/null
            if [ $? -eq 0 ];then
                mv ${eximportpath}/${line}/*.isx ${eximportpath}/tmp/${line}_${exportId}/
            fi
        fi
        CheckDir "${eximportpath}/${line}/"
        jobNum=0
        Log "Begin to export all the jobs which jira.no is ['${line}'], please wait..."
        jobcountsql="SELECT count(*) FROM DATASTAGEX_DSJOBDEF WHERE locate(':', DSNAMESPACE_XMETA ) <> 0 and SHORTDESCRIPTION_XMETA='${line}'"
        #connect to datastage database
        db2 connect to $dbname user $dbuser using $dbpass 1>/dev/null;
        if [ $? -ne 0 ];then
            Log "Connect to $dbname user $dbuser, please check..."
            exit -1
        fi
        jobcountres=`db2 -nox "${jobcountsql}";`
        joballnum=`echo $jobcountres|tr -d ' '`
        if [ ${joballnum} -eq 0 ];then
            Log "This Jira.no [$line] there is no DS job to export."
            continue
        else
            >${eximportpath}/${line}/${keyword}_${line}.list
            jobsql="SELECT substr(DSNAMESPACE_XMETA,locate(':', DSNAMESPACE_XMETA )+1 ) as PROJECTNAME,NAME_XMETA as JOBNAME,replace(CATEGORY_XMETA,'\\\','/') as JOBPATH FROM DATASTAGEX_DSJOBDEF WHERE locate(':', DSNAMESPACE_XMETA ) <> 0 and SHORTDESCRIPTION_XMETA='${line}'"
            joblist=`db2 -nox "${jobsql}";`
            #disconnect datastage database
            db2 disconnect "$dbname">/dev/null;
            echo "$joblist" |
            while read PROJECTNAME JOBNAME JOBPATH
            do
                jobNum=$((jobNum+1))
                jobNameLine=`echo ${hostname}/${PROJECTNAME}/${JOBPATH}/${JOBNAME}.pjb`
                /home/dsinst/InformationServer/Clients/istools/cli/istool.sh export -domain $ipaddress:9446 -u $dsuser -p $dspassword  -ar ${eximportpath}/${line}/${PROJECTNAME}.${JOBNAME}.isx -ds '-incexec "'${jobNameLine}'"'>/dev/null 2>&1
                if [ ! -f ${eximportpath}/${line}/${PROJECTNAME}.${JOBNAME}.isx ];then
                    Log "${jobNum}/${joballnum}|${PROJECTNAME}|${JOBNAME}|${line}  Export Failed!"
                else
                    Log "${jobNum}/${joballnum}|${PROJECTNAME}|${JOBNAME}|${line}  Export Succed!"
                fi
                echo "${jobNum}/${joballnum}|${PROJECTNAME}|${JOBNAME}|${line}|${JOBPATH}"|tee -a ${eximportpath}/${line}/${keyword}_${line}.list>/dev/null 2>&1
            done
            jobAll=0
            jobSucc=0
            jobFail=0
            for JobIsx in `cat ${eximportpath}/${line}/${keyword}_${line}.list`
            do
                jobAll=$((jobAll+1))
                PROJECTNAME=`echo "${JobIsx}"|awk -F"|" '{print $2}'`
                JOBNAME=`echo "${JobIsx}"|awk -F"|" '{printf $3}'`
                JIRANO=`echo "${JobIsx}"|awk -F"|" '{printf $4}'`
                if [ -f ${eximportpath}/${JIRANO}/${PROJECTNAME}.${JOBNAME}.isx ];then
                    jobSucc=$((jobSucc+1))
                else
                    jobFail=$((jobFail+1))
                fi
            done
            Log "LogFile: [${logfile}]"
            Log "Jira.no [$line] DS Job export over, all [${jobAll}], succ [${jobSucc}], fail [${jobFail}]!"
            Log "Job list: [${eximportpath}/${line}/EXPORT_${line}.list]"
            Log "#######################################################################"
        fi
    done
elif [ "$keyword" == "IMPORT" ];then

    #新增159DS服务器，仅在导入时调用，同步导入，导出时还是从136.4导出
    Log "Remote excute 199.188.166.110 server import shell [/home/DSsbin/DSSHELL/EVA/ds_eximport.sh_V159]..."
    if [ $# -eq 2 ];then
        nohup ssh moiase@199.188.166.110 "sh /home/DSsbin/DSSHELL/EVA/ds_eximport.sh_V159 IMPORT ${file_name} ${logfile}" &
    elif [ $# -eq 3 ];then
        nohup ssh moiase@199.188.166.110 "sh /home/DSsbin/DSSHELL/EVA/ds_eximport.sh_V159 ${sysname} IMPORT ${file_name} ${logfile}" &
    fi
    

    Log "Begin to import all the jobs of Jira.no in the jiraList [${list}], please wait..."
    for line in `cat ${list}|sed 's/ //g'`
    do
        jiraInfo=$line
        line=`echo $line|awk -F'|' '{print $1}'`
        if [ ! -f ${eximportpath}/${line}/EXPORT_${line}.list ];then
            Log "This Jira.no [$line] there is no job to import."
            continue
        else
            jobAll=`wc -l ${eximportpath}/${line}/EXPORT_${line}.list|awk -F' ' '{print $1}'|tr -d ' '`
            jobNum=0
            jobFail=0
            jobSucc=0
            for jobIsx in `cat ${eximportpath}/${line}/EXPORT_${line}.list|sed 's/ //g'`
            do
                jobNum=$((jobNum+1))
                PROJECTNAME=`echo "${jobIsx}"|awk -F"|" '{print $2}'`
                JOBNAME=`echo "${jobIsx}"|awk -F"|" '{printf $3}'`
                JIRANO=`echo "${jobIsx}"|awk -F"|" '{printf $4}'`
                #begin to import the job
                if [ -f ${eximportpath}/${JIRANO}/${PROJECTNAME}.${JOBNAME}.isx ];then
                    /home/dsinst/InformationServer/Clients/istools/cli/istool.sh import -domain $ipaddress:9446 -u $dsuser -p $dspassword -ar ${eximportpath}/${JIRANO}/${PROJECTNAME}.${JOBNAME}.isx -replace  -ds '"'$hostname/$PROJECTNAME'"'>/dev/null 2>&1
                    checksql="SELECT count(*) FROM DATASTAGEX_DSJOBDEF WHERE locate(':', DSNAMESPACE_XMETA ) <> 0 and SHORTDESCRIPTION_XMETA='${JIRANO}' and substr(DSNAMESPACE_XMETA,locate(':', DSNAMESPACE_XMETA )+1 )='${PROJECTNAME}' and NAME_XMETA='${JOBNAME}'"
                    checkres=`db2 connect to $dbname user $dbuser using $dbpass 1>/dev/null;
                            db2 -nox "${checksql}";
                            db2 disconnect "$dbname">/dev/null`;
                    if [ ${checkres} -eq 1 ];then
                        jobSucc=$((jobSucc+1))
                        Log "${jobNum}/${jobAll}|[${hostname}]-${PROJECTNAME}|${JOBNAME}|${JIRANO}  Import Succed!"
                    else
                        jobFail=$((jobFail+1))
                        Log "${jobNum}/${jobAll}|[${hostname}]-${PROJECTNAME}|${JOBNAME}|${JIRANO}  Import Failed!"
                    fi
                else
                    jobFail=$((jobFail+1))
                    Log "${jobNum}/${jobAll}|[${hostname}]-${PROJECTNAME}|${JOBNAME}|${JIRANO} Import Failed, isx not exist!"
                fi
            done
        fi
    done
    Log "${hostname}|${line}:import over,all:${jobAll},succ:${jobSucc},fail:${jobFail}"
    Log "[${hostname}]LogFile: [${logfile}]"
    Log "#######################################################################"
    
    #因为199.188.166.110的导入脚本是远程后台nohup执行，需判断是否完成
    Log "Judge 199.188.166.110 shell over or not..."    
    while true
    do
        ps_num=`ps -ef|grep "ds_eximport.sh_V159 IMPORT ${file_name}"|grep -v grep|wc -l`
        if [ ${ps_num} -ne 0 ];then
            Log "199.188.166.110 is running, wait 5 seconds..."
            sleep 5
        else
            Log "199.188.166.110 run over!"
            #159执行完成后，日志也同时写在了197.3.136.4的日志中，但因为是后台写入并未打印在4的屏幕上，数据平台无法判断，单独打印在屏幕中
            echo "Imort log in 159:"
            grep "DSNUAT" ${logfile}
            break
        fi
    done
    
    #数据平台中判断"import complete"关键字，是否导入完成，取每一条作业最后的"Import Succed"关键字判断是否成功，累计数和"all["中的数字一样则成功
    #因为是导入两台ds服务器，为适配数据平台，将总数乘以2
    jobAll=`expr $jobAll \* 2`
    #获取159成功和失败的作业数
    jobSucc2=`grep "${hostname}|${line}:import over" ${logfile}|awk -F',' '{print $3}'|awk -F':' '{print $2}'`
    jobFail2=`grep "${hostname}|${line}:import over" ${logfile}|awk -F',' '{print $4}'|awk -F':' '{print $2}'`
    
    jobSucc=`expr $jobSucc + $jobSucc2`
    jobFail=`expr $jobFail + $jobFail2`
    
    Log "[${line}] import complete, all [${jobAll}], succ [${jobSucc}], fail [${jobFail}]]!"
    
else
    Log "The first param must be export/EXPORT/import/IMPORT, please check..."
    exit -1
fi

蚂蚁再小也是肉🥩！

ETL > DataStage
#ETL #DataStage
DataStage批量导入导出脚本
http://heibanbai.com.cn/posts/3579bfdd/
作者
黑伴白
发布于
2022年10月12日
许可协议

“您的支持，我的动力！觉得不错的话，给点打赏吧 ୧(๑•̀⌄•́๑)૭”

微信二维码

微信支付

支付宝二维码

支付宝支付