0

I am updating a table from DataStage job with write mode "Update". The jobs running for long time, taking appx. 10 hours to complete. I have 4 694 233 records in table and trying to load appx 15k records from Dataset.

Not sure why it is taking that long time. How to tune and reduce the load time?

James Z
  • 12,209
  • 10
  • 24
  • 44
Gagan Dutta
  • 1
  • Hi - I’m afraid you haven’t provided anything like enough information for someone to be able to help you. How is your Datastage job configured, what does the Snowflake history show as being run, are there any log files containing relevant information, etc? – NickW Aug 28 '21 at 09:59
  • 1
    If it's taking 10 hours for 15k rows, DataStage is almost certainly affecting one row per statement. You can check the Snowflake history tab to confirm this. It will need to run statements that perform operations on sets of rows. The best option is to PUT files to a Snowflake stage, use COPY INTO to a staging table, and MERGE into the target table. The next best option is to INSERT sets of rows at a time (as many as will get through in a single statement) to a staging table and MERGE from there. Singleton operations should be avoided except for small tests, writing a log entry, etc. – Greg Pavlik Aug 28 '21 at 13:39
  • Greg, you are correct. Its executing one update statement for each row. Do we have options in DataStage to perform update for set of rows. – Gagan Dutta Aug 29 '21 at 15:27
  • The Datastage documentation explains how to bulk load into Snowflake, is this what you are currently doing? https://developer.ibm.com/recipes/tutorials/how-to-use-ibm-infosphere-datastage-snowflake-connector-to-load-data-into-snowflake-datawarehouse/ – NickW Aug 29 '21 at 18:18

1 Answers1

0

Load the data into flat files and do a bulk import into SNowflake.

ashwin
  • 332
  • 2
  • 16