Getting Top N To N Rows From Db2
I need to split a huge table in to chunks. Fetching data from DB2 and processing in SSIS iteration 1 : Get first 10 rows and process it iteration 2 : Get next 10 rows(11-20) and
Solution 1:
db2 "select row_number() over(order by tabschema, tabname)
, tabschema::char(10), tabname::char(30)
from syscat.tables
order by tabschema, tabname
offset 10 rows
fetch first 10 rows only"
123-------------------- ---------- ------------------------------11 SYSCAT COLCHECKS
12 SYSCAT COLDIST
13 SYSCAT COLGROUPCOLS
14 SYSCAT COLGROUPDIST
15 SYSCAT COLGROUPDISTCOUNTS
16 SYSCAT COLGROUPS
17 SYSCAT COLIDENTATTRIBUTES
18 SYSCAT COLLATIONS
19 SYSCAT COLOPTIONS
20 SYSCAT COLUMNS
10 record(s) selected.
Solution 2:
Use OFFSET x FETCH FIRST y ROWS ONLY option to read data by chunk
To benefit from this method in SSIS, you should follow these steps:
- Add Execute SQL Task to get the rows count and store it into a variable.
- Add a Forloop container to loop over a range of numbers until reaching the row count
- Inside the Forloop container, add a Data Flow Task that contains an OLEDB Source or ODBC and OLEDB Destination
In OLEDB/ODBC Source, Set the Access mode to SQL Command and define an epxression similar to:
"SELECT * FROM MYTABLE ORDER BY ID_COLUMN OFFSET " + (DT_WSTR,50)@[User::IncrementValue] + " FETCH FIRST " + (DT_WSTR,50)@[User::IncrementValue] + " ROWS ONLY"
The following answer is a step by step guide to load data by chunks from SQLite, you can follow it just change the SQL Command syntax as mentioned:
Post a Comment for "Getting Top N To N Rows From Db2"