CODEWITHSUNDEEP
db2db2-luwobject-storage
user3789961
user3789961

Reputation: 145

Load data from IBM Object Storage file to Cloud DB2

I am using below SQL statement to load data from object storage file to cloud db2.

CALL SYSPROC.ADMIN_CMD('load from "S3::s3.jp-tok.objectstorage.softlayer.net::<s3-access-key-id>:
:<s3-secret-access-key>::nlu-test::practice_nlu.csv" of DEL modified by codepage=1208 
coldel0x7c WARNINGCOUNT 1000 MESSAGES ON SERVER INSERT into DASH12811.NLU_TEMP_2');

it failed with error :

Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least one error, "SQL3016", was encountered during the execution. More information is available.. SQLCODE=20397, SQLSTATE=01H52, DRIVER=4.24.92

Please note, i want to load this data to DB2 not DB2 warehouse.

Upvotes: 1

Views: 1218

Answers (2)

Paul Vernon
Paul Vernon

Reputation: 3901

Error SQL3016 is

$ db2 "? SQL3016"


SQL3016N  An unexpected keyword "<keyword>" was found in the filetmod
      parameter for the filetype.

Explanation:

A keyword that does not apply to the filetype for the utility was found
in the filetype modifier (the filetmod parameter or the phrase following
MODIFIED BY in a CLP command).

The utility stops processing.

User response:

Remove the filetype modifier or specify at least one valid keyword in
the filetype modifier. See the Command Reference for more information on
filetype modifiers.

Which indicates that one of your filetype modifiers is not valid. Although they do appear valid, ti would be interesting to test if the the load without the codepage=1208 or coldel0x7c modifiers works or gives a different error for you

Upvotes: 1

Paul Vernon
Paul Vernon

Reputation: 3901

When using LOAD via ADMIN_CMD the 1st result set from the stored procedure call contains a SQL statment that you can use to see the messages back from the LOAD command.

Here is an example of this from the CLP.

$ db2 "call admin_cmd('load from ''S3::s3.jp-tok.objectstorage.softlayer.net::<s3-access-key-id>::<s3-secret-access-key>::nlu-test::practice_nlu.csv'' of del modified by codepage=1208 coldel0x7c WARNINGCOUNT 1000 MESSAGES ON SERVER INSERT into t')"
 `  


  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('1618991872_2012791051_DB2INST1')) AS MSG   CALL SYSPROC.ADMIN_REMOVE_MSGS('1618991872_2012791051_DB2INST1')     

  1 record(s) selected.

  Return Status = 0

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

Now run the SQL from the result set MSG_RETRIEVAL column to see the detailed messages from LOAD 

$ db2 "SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('1618991872_2012791051_DB2INST1')) AS MSG"

SQLCODE   MSG                                                                  
--------- ---------------------------------------------------------------------
SQL3501W  The table space(s) in which the table resides will not be placed in  
backup pending state since forward recovery is disabled for the database.      
SQL3109N  The utility is beginning to load data from file "S3".                
SQL3500W  The utility is beginning the "ANALYZE" phase at time "10/01/2019     
18:27:29.090523".                                                              
SQL2018N  The utility encountered an error "" while attempting to verify the   
user's authorization ID or database authorizations.                            
SQL2018N  The utility encountered an error "" while attempting to verify the   
user's authorization ID or database authorizations.                            

  5 record(s) selected.

Looking at the full LOAD messages may help you see what issue you have with your call

Upvotes: 2

Related Questions