In a recent project, I ran into an issue of the ODBC connection getting dropped when connecting to Amazon RedShift Cloud.
The data volume was about 31M records - we could not trace it to a specific reason as to why the connection was being.
Here's what I did to resolve it.
I first performed a select on the table I was extracting from to get a total count of the number of records:
store the count in a variable.
SQL SELECT count(*)FROM $(DB).$(DBSchema).$(DatabaseTable);
Let vCount =peek('count');
//Loop through the table in extract chunks of 1M records.
For counter = 0 to $(vCount) step 1000000
ODBC CONNECT TO PostgreSQL30
where "row" >= $(counter)+1 and "row" <= $(counter)+1000000;
// "row" (indicating row number) is a field that had to be added to the table, for this fix to work as designed.
This has been working perfectly.