ODBC Read method=Table vs User def SQL

[ds_is_fun]

We need to use the correct optimized method in our ODBC stage.
Our environment-
1. We read from two types of tables - source, ref tables.
Our source table gets loaded with 2k rows everyday and cleaned every 2 months(120k max accumulation in 2months). Our REF tables wouldn't exceed 1k rows.
2. There are two ways to extract from the source table. Which is the best method?
a. Use a table READ method in the ODBC stage and use a filter stage.
b. Write a query with a WHERE clause and use User defined SQL.
Our WHERE clause consists of only one column with a status code =0/1 check.
3. During the lookups again Read Method = Table vs User def SQL.
a. Does a Read method = table cache the table into memory and avoid a lookup against the table row by row?
b. Everytime we lookup against the REF table we do it by row by row? Correct?

All responses are greatly appreciated. Thanks!

[ds_is_fun]

We need to use the correct optimized method in our ODBC stage.
Our environment- 4 CPUs Solaris.

1. We read from two types of tables - source, ref tables.
Our source table gets loaded with 2k rows everyday and cleaned every 2 months(120k max accumulation in 2months). Our REF tables wouldn't exceed 1k rows.


2. There are two ways to extract from the source table. Which is the best method?
a. Use a table READ method in the ODBC stage and use a filter stage.
b. Write a query with a WHERE clause and use User defined SQL.
Our WHERE clause consists of only one column with a status code =0/1 check.


3. During the lookups again Read Method = Table vs User def SQL.
a. Does a Read method = table cache the table into memory and avoid a lookup against the table row by row?
b. Everytime we lookup against the REF table we do it by row by row? Correct?

All responses are greatly appreciated. Thanks!

[gbusson]

hi,

For extracting :you can filter in the database. If there is indexes on the columns, do not hesitate!

read method is about the query, not the lookup. The option is lookup type (Sparse vs normal)

spare means that lookup is made row by row. Normal => table into memory

[bcarlson]

FYI - I haven't used the ODBC stage, but I know that the table read option for DB2 also allows a where clause, you don't have to do a user-def query.

Brad.