IBM Db2 Warehouse client container db_migrate error

Question

Short Description: Error on db_migrate IBM Db2 Warehouse client container

I have successfully installed the client container v2.9.0 for DB2 Warehouse from docker. I am trying to run the db_migrate command to copy data from our "old" PDA servers to our own DB2 Warehouse.

docker run -itd --net=host -v db2wh_vol:/mnt/clientdir --name=client store/ibmcorp/db2wh_ce:v2.9.0-db2wh_client-linux

I am issuing the following command in the client container (passwords are hidden for security reasons):

db_migrate -sdb BACC_DEV_STGPWRPT_SDFDM  -tdb BLUDB_24 -shost dstbld-pda02.bld.dst.ibm.com -suser h16720 -tuser bluadmin -spassword password -tpassword password  -sschema POTINA -tables "PO_FACT" "PRODUCT_DIMENSION" "CUSTOMER_DIMENSION"

The load job start and when I inspect the log for the error that throws it is the following:

=======================================================================================

db_migrate started on 2018-07-27 07:44:20

Parameters:   -logdir /mnt/clientdir/clienthome/db2inst1/logs/db_migrate -sdb BACC_DEV_STGPWRPT_SDFDM -tdb BLUDB_24 -shost dstbld-pda02.bld.dst.ibm.com -suser h16720 -tuser bluadmin -spassword ****** -tpassword ****** -sschema POTINA -tables PO_FACT PRODUCT_DIMENSION CUSTOMER_DIMENSION

Migrating dstbld-pda02.bld.dst.ibm.com:BACC_DEV_STGPWRPT_SDFDM --> localhost:BLUDB_24 (dashdb-txn-flex-yp-dal09-734.services.dal.bluemix.net/50001/BLUDB SSL)

Source Full Schema Enabled: TRUE
Source System Lettercase  : UPPERCASE

Data Format   : ascii
Log Directory : /mnt/clientdir/clienthome/db2inst1/logs/db_migrate/db_migrate.20180727_074340.7133
Log File      : /mnt/clientdir/clienthome/db2inst1/logs/db_migrate/db_migrate.20180727_074340.7133/db_migrate.output

Initiated From:  linuxkit-025000000001
Top Level PID : 7133

SOURCE Version: NPS 7.2       Unloaded Via: exttab    # of Dataslices: 240
TARGET Version: v11.1.3.3       Loaded Via: exttab    # of Dataslices: 1
Script version: 1.0.0.1008.[20180418_132854] [CHANGES: BUG355]

=======================================================================================


Migrating the table: "POTINA"."PO_FACT"

.....processing table 1 of 3
.....using target table "POTINA"."PO_FACT"
.....migration process                              started at  2018-07-27 07:44:38
.....estimated # of records                                     5,096,960
.....load starting               ( thread 1 of 4 )
.....waiting on load             ( thread 2 of 4 )
.....load starting               ( thread 2 of 4 )
.....load starting               ( thread 3 of 4 )
.....load starting               ( thread 4 of 4 )
.....unloading data              ( thread 1 of 4 )
.....unloading data              ( thread 2 of 4 )
.....unloading data              ( thread 3 of 4 )
.....unloading data              ( thread 4 of 4 )
ERROR:  The load operation error
ERROR:  The load operation error
ERROR:  See logs: /mnt/clientdir/clienthome/db2inst1/logs/db_migrate/db_migrate.20180727_074340.7133/POTINA.PO_FACT_3.load
ERROR:  The load operation error
ERROR:  See logs: /mnt/clientdir/clienthome/db2inst1/logs/db_migrate/db_migrate.20180727_074340.7133/POTINA.PO_FACT_4.load
ERROR:  The load operation error
ERROR:  See logs: /mnt/clientdir/clienthome/db2inst1/logs/db_migrate/db_migrate.20180727_074340.7133/POTINA.PO_FACT_1.load
ERROR:  See logs: /mnt/clientdir/clienthome/db2inst1/logs/db_migrate/db_migrate.20180727_074340.7133/POTINA.PO_FACT_2.load
ERROR:  /opt/ibm/migration_tools/db_toolkit/db_extTab.sh issue, see logs: POTINA.PO_FACT_3.unload
ERROR:  /opt/ibm/migration_tools/db_toolkit/db_extTab.sh issue, see logs: POTINA.PO_FACT_4.unload
ERROR:  /opt/ibm/migration_tools/db_toolkit/db_extTab.sh issue, see logs: POTINA.PO_FACT_2.unload
ERROR:  /opt/ibm/migration_tools/db_toolkit/db_extTab.sh issue, see logs: POTINA.PO_FACT_1.unload

#####AUTOMATIC TERMINATION:  Prior problems were encountered.  Aborting the migration of this table.

.....migration aborted                                          TOTAL seconds: 39
.....migration process                              ended at    2018-07-27 07:45:17

I opened the log for the PO_FACT_1.unload:

[Db2wh - Client CLI ~]\>
db_migrate.20180727_074340.7133/POTINA.PO_FACT_1.loade/db2inst1/logs/db_migrate/

==> POTINA.PO_FACT [Thread: 1, attempt: 0]
DEBUG: DB EXTERNAL TABLE OPTIONS:  Encoding 'INTERNAL'  CrInString true CtrlChars true TimeDelim ':'  NULLVALUE 'QNZQ'   Remotesource 'jdbc'  MAXERRORS 1
DEBUG: /opt/ibm/migration_tools/db_toolkit/db_extTab.sh -action load -dbtype DB2 -host dashdb-txn-flex-yp-dal09-734.services.dal.bluemix.net -port 50001 -database BLUDB -schema "POTINA" -table PO_FACT -user bluadmin  -password '***' -pipeline /mnt/clientdir/clienthome/db2inst1/logs/db_migrate/db_migrate.20180727_074340.7133/db_migrate.20180727_074340.7133_1.pipe_10 -logname POTINA.PO_FACT_1.load -logpath /mnt/clientdir/clienthome/db2inst1/logs/db_migrate/db_migrate.20180727_074340.7133 -addinoptions Encoding 'INTERNAL'  CrInString true CtrlChars true TimeDelim ':'  NULLVALUE 'QNZQ'   Remotesource 'jdbc'  MAXERRORS 1   -certFile /mnt/clientdir/clienthome/db2inst1/logs/db_migrate/db_migrate.20180727_074340.7133/dashdb-txn-flex-yp-dal09-734.services.dal.bluemix.net:50001_20180727_074409.pem >> /mnt/clientdir/clienthome/db2inst1/logs/db_migrate/db_migrate.20180727_074340.7133/db_migrate.20180727_074340.7133_1.dbsql
20180727T07:44:38.361 : [1] SQL: INSERT INTO "POTINA"."PO_FACT" SELECT * FROM EXTERNAL '/mnt/clientdir/clienthome/db2inst1/logs/db_migrate/db_migrate.20180727_074340.7133/db_migrate.20180727_074340.7133_1.pipe_10'  USING (    DELIMITER '|'   LOGDIR '/mnt/clientdir/clienthome/db2inst1/logs/db_migrate/db_migrate.20180727_074340.7133'  EscapeChar '\' Encoding 'INTERNAL'  CrInString true CtrlChars true TimeDelim ':'  NULLVALUE 'QNZQ'   Remotesource 'jdbc'  MAXERRORS 1  )
Using user specified non-default DB2 port number: 50001
An unexpected token "EXTERNAL" was found following "FROM".  Expected tokens may include:  "<space>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.23.42
Rows Returned : transfer failed
The /opt/ibm/migration_tools/db_toolkit/db_extTab.sh returned 255

Can you please help on it what I am doing wrong? Thanks in advance!

Answer 1

db_migrate only supports migration from PDA (aka Netezza) to Db2 Warehouse. It does not support "plain" Db2, wheter on premise, or as Db2 on Cloud. I.e. your target server is Db2 11.1.3.3, which does not support EXTERNAL TABLEs. That is why you get the An unexpected token "EXTERNAL" message.

I.e. from Db2 Warehouse

touch /mnt/bludata0/scratch/test
db2 "INSERT INTO test SELECT * FROM EXTERNAL '/mnt/bludata0/scratch/test'"

SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a 
query is an empty table.  SQLSTATE=02000

but from Db2

db2 "INSERT INTO test SELECT * FROM EXTERNAL '/tmp/test'"

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "EXTERNAL" was found following "FROM".  Expected 
tokens may include:  "<space>".  SQLSTATE=42601