db2mt run load fail - update

[shkang-kr]

Please, check below issue again. Last time, there was mistake and misunderstanding in my test.

'db2mt run load' fails and I tried to narrow down the scope of the problem.

1. I finished previous steps successfully.

2. db2mt run load failed . I guarantee data and ddl was uploaded in previous step, on S3. Check the load log.

cat load_thread1.log

Refer to /home/db2inst1/db2mtdir/logs/current/load_thread1_success.lst for the list of tables successfully loaded.
Refer to /home/db2inst1/db2mtdir/logs/current/load_thread1_failed.lst for the list of tables failed to be loaded.

============================ T1 START ============================
2024-05-03.08:14:13
call sysproc.admin_cmd('load from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/T1_export.ixf" of ixf
lobs from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/" xml from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/"
modified by lobsinfile implicitlyhiddeninclude identityoverride generatedoverride periodoverride transactionidoverride
messages on server
replace into "DB2INST1"."EMPPROJACT"
nonrecoverable
indexing mode incremental allow no access')

Result set 1

ROWS_READ ROWS_SKIPPED ROWS_LOADED ROWS_REJECTED ROWS_DELETED ROWS_COMMITTED ROWS_PARTITIONED NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL MSG_REMOVAL

---

                 -                    -                    -                    -                    -                    -                    -                     - SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('979734585_1850699157_ADMIN')) AS MSG                                                                                                                                                                                                                                                                                                                                                                                                                                      CALL SYSPROC.ADMIN_REMOVE_MSGS('979734585_1850699157_ADMIN')

1 record(s) selected.

Return Status = 0

SQL20397W Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least
one error, "SQL1652", was encountered during the execution. More information
is available. SQLSTATE=01H52

2024-05-03.08:18:46
============================= T1 END =============================
============================ T4 START ============================
2024-05-03.08:18:48
call sysproc.admin_cmd('load from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/T4_export.ixf" of ixf
lobs from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/" xml from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/"
modified by lobsinfile implicitlyhiddeninclude identityoverride generatedoverride periodoverride transactionidoverride
messages on server
replace into "DB2INST1"."SALES"
nonrecoverable
indexing mode incremental allow no access')

Result set 1

ROWS_READ ROWS_SKIPPED ROWS_LOADED ROWS_REJECTED ROWS_DELETED ROWS_COMMITTED ROWS_PARTITIONED NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL MSG_REMOVAL

---

                 -                    -                    -                    -                    -                    -                    -                     - SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('1598879862_1520309334_ADMIN')) AS MSG                                                                                                                                                                                                                                                                                                                                                                                                                                     CALL SYSPROC.ADMIN_REMOVE_MSGS('1598879862_1520309334_ADMIN')

1 record(s) selected.

Return Status = 0

SQL20397W Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least
one error, "SQL1652", was encountered during the execution. More information
is available. SQLSTATE=01H52

2024-05-03.08:23:21
============================= T4 END =============================
============================ T7 START ============================
2024-05-03.08:23:22
call sysproc.admin_cmd('load from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/T7_export.ixf" of ixf
lobs from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/" xml from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/"
modified by lobsinfile implicitlyhiddeninclude identityoverride generatedoverride periodoverride transactionidoverride
messages on server
replace into "DB2INST1"."PROJECT"
nonrecoverable
indexing mode incremental allow no access')

Result set 1

ROWS_READ ROWS_SKIPPED ROWS_LOADED ROWS_REJECTED ROWS_DELETED ROWS_COMMITTED ROWS_PARTITIONED NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL MSG_REMOVAL

---

                 -                    -                    -                    -                    -                    -                    -                     - SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('1605348231_1783044617_ADMIN')) AS MSG                                                                                                                                                                                                                                                                                                                                                                                                                                     CALL SYSPROC.ADMIN_REMOVE_MSGS('1605348231_1783044617_ADMIN')

1 record(s) selected.

Return Status = 0

SQL20397W Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least
one error, "SQL1652", was encountered during the execution. More information
is available. SQLSTATE=01H52

2024-05-03.08:27:55
============================= T7 END =============================
============================ T10 START ============================
2024-05-03.08:27:57
call sysproc.admin_cmd('load from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/T10_export.ixf" of ixf
lobs from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/" xml from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/"
modified by lobsinfile implicitlyhiddeninclude identityoverride generatedoverride periodoverride transactionidoverride
messages on server
replace into "DB2INST1"."EMP_RESUME"
nonrecoverable
indexing mode incremental allow no access')

Result set 1

ROWS_READ ROWS_SKIPPED ROWS_LOADED ROWS_REJECTED ROWS_DELETED ROWS_COMMITTED ROWS_PARTITIONED NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL MSG_REMOVAL

---

                 -                    -                    -                    -                    -                    -                    -                     - SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('1135070747_427923423_ADMIN')) AS MSG                                                                                                                                                                                                                                                                                                                                                                                                                                      CALL SYSPROC.ADMIN_REMOVE_MSGS('1135070747_427923423_ADMIN')

1 record(s) selected.

Return Status = 0

SQL20397W Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least
one error, "SQL1652", was encountered during the execution. More information
is available. SQLSTATE=01H52

2024-05-03.08:32:30
============================= T10 END =============================

3. Tried same load syntax again, manually.

db2inst1@ip-12-0-3-86:~/db2mtdir/logs/current$ db2 "call sysproc.admin_cmd('load from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/T1_export.ixf" of ixf lobs from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/" xml from "DB2REMOTE://db2mts3alias/backup-20240224t/db2mtdir/export/"
modified by lobsinfile implicitlyhiddeninclude identityoverride generatedoverride periodoverride transactionidoverride messages on server replace into "DB2INST1"."EMPPROJACT" nonrecoverable indexing mode incremental allow no access')"

Result set 1

ROWS_READ ROWS_SKIPPED ROWS_LOADED ROWS_REJECTED ROWS_DELETED ROWS_COMMITTED ROWS_PARTITIONED NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL MSG_REMOVAL

---

                 -                    -                    -                    -                    -                    -                    -                     - SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('289152709_1844811853_ADMIN')) AS MSG                                                                                                                                                                                                                                                                                                                                                                                                                                      CALL SYSPROC.ADMIN_REMOVE_MSGS('289152709_1844811853_ADMIN')

1 record(s) selected.

Return Status = 0

SQL20397W Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least
one error, "SQL1652", was encountered during the execution. More information
is available. SQLSTATE=01H52

db2inst1@ip-12-0-3-86:~/db2mtdir/logs/current$

4. test for admin_cmd itself with simple option.

db2inst1@ip-12-0-3-86:~/db2mtdir/logs/current$ db2 "call sysproc.admin_cmd('load from "/home/db2inst1/db2mtdir/export/current/uploaded/T1_export.ixf" of ixf replace keepdictionary into db2inst1.empprojact') "
SQL3508N Error in accessing a file or path of type "INPUT DATA FILE" during
load or load query. Reason code: "8". Path:
"/home/db2inst1/db2mtdir/export/current/uploaded/T1_e".

db2inst1@ip-12-0-3-86:~/db2mtdir/logs/current$ db2 "call sysproc.admin_cmd('load from /home/db2inst1/db2mtdir/export/current/uploaded/T1_export.ixf of ixf replace keepdictionary into db2inst1.empprojact') "
SQL3508N Error in accessing a file or path of type "INPUT DATA FILE" during
load or load query. Reason code: "8". Path:
"/home/db2inst1/db2mtdir/export/current/uploaded/T1_e".

5. tried 'load client this time'. This shows that file permission has no problem.

db2inst1@ip-12-0-3-86:~/db2mtdir/logs/current$ db2 "load client from '/home/db2inst1/db2mtdir/export/current/uploaded/T1_export.ixf' of ixf messages db2inst1.empprojact.log replace into db2inst1.empprojact"

Number of rows read = 73
Number of rows skipped = 0
Number of rows loaded = 73
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 73

db2inst1@ip-12-0-3-86:~/db2mtdir/logs/current$

[eunicechung]

SQL20397W Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least
one error, "SQL1652", was encountered during the execution. More information
is available. SQLSTATE=01H52

SQL1652 is a generic IO error. There are a couple of things you can do to further investigate. You can retrieve the actual load message from the server using the command listed in the output (e.g. SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('1598879862_1520309334_ADMIN')) AS MSG). Another place you can investigate is db2diag.log. I believe RDS provides a mechanism for customers to retrieve and look and db2diag.log.

I suspect the problem is related to the remote alias. As mentioned previously, you won't be able to see the alias with db2 list storage access.

However, you can check what has been catalog on the RDS server by running this from the client:

db2 "select varchar(ALIAS, 128) as ALIAS, varchar(VENDOR, 30) as VENDOR, varchar(ENDPOINT, 255) as ENDPOINT, varchar(BUCKET, 255) as BUCKET, varchar(PATH, 1024) as PATH, GRANTEETYPE, varchar(GRANTEE,  255) as GRANTEE, varchar(USAGE, 512) as USAGE from table(SYSIBMADM.STORAGE_ACCESS_ALIAS.LIST())"

You mentioned the command below was the catalog command that has been run. So in order to see the output from the command above, you need to connect to the server with user admin.

db2inst1@ip-12-0-3-xx:~/db2mtdir/logs/current$ db2 -vf /home/db2inst1/db2mtdir/landing/current/catalog_alias.clp
call sysibmadm.storage_access_alias.catalog('db2mts3alias','s3','s3.us-east-1.amazonaws.com','myaccesskey,'access-secretkey','backup-20240224t',NULL,'U','admin')

In addition, since admin was the user granted with the alias access, LOAD must also be run as with user admin. If you use any other users, it'll return sql1652 since it won't have access.

Another potential reason for sql1652 is the access key/secret being expired/no longer valid.

[shkang-kr]

Thanks for help. I will do more check.