Exemple : Déchargement de données avec génération d'une commande de création de table externe et d'une commande de chargement associée vers un stockage d'objets compatible avec l' S3

[i1156@lat111 ~]$ db2hpu -i i1156 -f sysin
INZM031I Optim High Performance Unload for Db2 06.05.00.003(230126) 
         64 bits 01/31/2023 (Linux lat111 3.10.0-862.14.4.el7.x86_64 #1 SMP Fri Sep 21 09:07:21 UTC 2018 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 EMPLOYEE;
000004 OUTFILE("outfile")
000005 DDLFILE("ddlfile")
000006 LOADDEST(OBJECT_STORAGE AWS_S3 "S3_EXTERNAL")
000007 LOADFILE("loadfile")
000008 INTO TABLE(I1156.TB_EXTERNAL)
000009 FORMAT EXTERNAL TEXT USING (QUOTEDVALUE SINGLE REQUIREQUOTES TRUE DECIMALDELIM '.' NOLOG TRUE NULLVALUE "null" S3 "S3_EXTERNAL");

INZU462I HPU control step start: 01/31/2023 08:00:11.382.
INZU463I HPU control step end  : 01/31/2023 08:00:13.723.
INZU464I HPU run step start    : 01/31/2023 08:00:13.961.
INZU410I HPU utility has unloaded 42 rows on lat111 host for I1156.EMPLOYEE in outfile.
INZU622I HPU utility has generated the DDL file ddlfile.
INZU684I HPU utility has generated an upload command for the AMAZON S3 destination in the loadfile file.
INZU465I HPU run step end      : 01/31/2023 08:00:14.004.
INZI441I HPU successfully ended: Real time -> 0m2.622535s
User time -> 0m0.029573s : Parent -> 0m0.029573s, Children -> 0m0.000000s
Syst time -> 0m0.028517s : Parent -> 0m0.028517s, Children -> 0m0.000000s

[AWS_S3]
alias=S3_EXTERNAL
bucket=BUCKET
accesskey=ACCESSKEY_5YU2XNMDHQA
region=eu-west-1

[S3_EXTERNAL]
type=aws_s3
password=FAE6B57CC297B86F268F651F0018393187F92C0B87573C6B6B3F3BCE2E098BDCD83F46B84008EC83...

[i1156@lat111 ~]$ cat outfile
'000010'|'CHRISTINE'|'I'|'HAAS'|'A00'|'3978'|19950101|'PRES    '|18|'F'|19630824|+0152750.00|+0001000.00|+0004220.00
...
'200340'|'ROY'|'R'|'ALONZO'|'E21'|'5698'|19970705|'FIELDREP'|16|'M'|19560517|+0031840.00|+0000500.00|+0001907.00

[i1156@lat111 ~]$ cat loadfile
#!/bin/sh
echo Start uploading ...
aws s3 cp --content-type "text/plain; charset=UTF-8" "outfile" s3://BUCKET > "TB_EXTERNAL.msg" >2&1
RC=$?
if [ $RC -ne 0 ]
then
    echo "An error occurred while processing the 'outfile' file. The 'TB_EXTERNAL.msg' file contains the associated execution report."
else
    echo "The 'outfile' file has been processed successfully. The 'TB_EXTERNAL.msg' file contains the associated execution report."
fi
exit $RC

[i1156@lat111 ~]$ cat ddlfile
CREATE EXTERNAL TABLE "I1156"."TB_EXTERNAL"  (
                "EMPNO" CHAR(6 OCTETS) NOT NULL,
                "FIRSTNME" VARCHAR(12 OCTETS) NOT NULL,
                "MIDINIT" CHAR(1 OCTETS),
                "LASTNAME" VARCHAR(15 OCTETS) NOT NULL,
                "WORKDEPT" CHAR(3 OCTETS),
                "PHONENO" CHAR(4 OCTETS),
                "HIREDATE" DATE,
                "JOB" CHAR(8 OCTETS),
                "EDLEVEL" SMALLINT NOT NULL,
                "SEX" CHAR(1 OCTETS),
                "BIRTHDATE" DATE,
                "SALARY" DECIMAL(9,2),
                "BONUS" DECIMAL(9,2),
                "COMM" DECIMAL(9,2) )
USING ( FORMAT TEXT
        DELIMITER '|'
        QUOTEDVALUE SINGLE
        REQUIREQUOTES TRUE
        CCSID 1208
        DECIMALDELIM '.'
        NOLOG TRUE
        NULLVALUE 'null'
        DATE_FORMAT 'YYYYMMDD'
        S3 ('BUCKET.s3.eu-west-1.amazonaws.com', 'ACCESSKEY_5YU2XNMDHQA', 'SECRETKEY_B+yXklvtqW86gQmIgMuPMuyuzKuYUk', 'BUCKET')
        DATAOBJECT 'outfile') ;