예: CLPPlus 도구 사용을 기반으로 Db2 Warehouse 대상에 대한 업로드 명령을 생성하는 데이터 언로드

실행 보고서:

[i1058@lat111 ~]$ db2hpu -i i1058 -f sysin
INZM031I Optim High Performance Unload for Db2 06.01.00.001(170224) 
         64 bits 02/27/2017 (Linux lat111 3.10.0-327.36.1.el7.x86_64 #1 SMP Wed Aug 17 03:02:37 EDT 2016 x86_64)
INZI473I Memory limitations: 'unlimited' for virtual memory and 'unlimited' for data segment
       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+
000001 GLOBAL CONNECT TO SAMPLE;
000002 UNLOAD TABLESPACE
000003 SELECT * FROM I1058.EMPLOYEE;
000004 INTO TABLE (MYUSER.EMPLOYEE)
000005 OUTFILE("outfile")
000006 LOADFILE("loadfile")
000007 LOADDEST(DB2 WAREHOUSE WITH STANDARD AUTH)
000008 DATE DATE_C
000009 FORMAT DELIMITED;

INZU462I HPU control step start: 02/27/2017 17:01:01.438.
INZU463I HPU control step end  : 02/27/2017 17:01:01.867.
INZU464I HPU run step start    : 02/27/2017 17:01:01.956.
INZU410I HPU utility has unloaded 42 rows on lat111 host for MYUSER.EMPLOYEE in outfile.
INZU684I HPU utility has generated an upload command for the DB2 WAREHOUSE destination in the loadfile file.
INZU465I HPU run step end      : 02/27/2017 17:01:02.958.
INZI441I HPU successfully ended: Real time -> 0m1.520652s
User time -> 0m0.249260s : Parent -> 0m0.243942s, Children -> 0m0.005318s
Syst time -> 0m0.054006s : Parent -> 0m0.046561s, Children -> 0m0.007445s

db2hpu.dest 파일의 연관된 Db2® Warehouse 섹션:

[Warehouse]
user=bluadmin
dbname=BLUDB
url=DB2WAREHOUSE_IP
port=50000

생성된 출력 파일에서 발췌한 내용:

[i1058@lat111 ~]$ cat outfile
"000010","CHRISTINE","I","HAAS","A00","3978",1995-01-01,"PRES    ",18,"F",1963-08-24,+0152750.00,+0001000.00,+0004220.00
...
"200340","ROY","R","ALONZO","E21","5698",1997-07-05,"FIELDREP",16,"M",1956-05-17,+0031840.00,+0000500.00,+0001907.00

생성된 업로드 명령:

[i1058@lat111 ~]$ cat loadfile
#!/bin/sh
echo "Enter password for user 'bluadmin':"
read -s password

echo Start uploading ...
echo "WHENEVER SQLERROR EXIT FAILURE;"> "EMPLOYEE.msg.tmp"
echo "SET DELIMITER ',';"> "EMPLOYEE.msg.tmp"
echo "CONNECT bluadmin/'$password'@DB2WAREHOUSE_IP:50000/BLUDB;">> "EMPLOYEE.msg.tmp"
echo "IMPORT FROM 'outfile' INSERT INTO MYUSER.EMPLOYEE;">> "EMPLOYEE.msg.tmp"
echo "DISCONNECT;">> "EMPLOYEE.msg.tmp"
echo "EXIT;">> "EMPLOYEE.msg.tmp"
clpplus -nw < "EMPLOYEE.msg.tmp" > "EMPLOYEE.msg" 2>&1
RC=$?
rm EMPLOYEE.msg.tmp
if [ $RC -ne 0 ]
then
    echo "An error occurred while processing the 'outfile' file. The 'EMPLOYEE.msg' file contains the associated execution report."
else
    echo "The 'outfile' file has been processed successfully. The 'EMPLOYEE.msg' file contains the associated execution report."
fi
exit $RC