Getting top n to n rows from db2

Question

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 process it

iteration 3 : Get next 10 rows(21-30) and process it

and so on till count(*) of a table

Is it possible to get top n to n rows from db2

im looking for a query like below,

select * from from tablename fetch 10 to 20 rows

Carrefour

What's the DB2 version and platform? – Malherbe

It is weird how you accepted and up-voted an answer that is not related to ssis while you tagged your question with ssis?! At the same time you ignored the ssis answer – Touslesmois

Answer 1

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061832.html

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"

1                    2          3                             
-------------------- ---------- ------------------------------
                  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.

Roentgenogram

Answer 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:

1. Add Execute SQL Task to get the rows count and store it into a variable.
2. Add a Forloop container to loop over a range of numbers until reaching the row count
3. Inside the Forloop container, add a Data Flow Task that contains an OLEDB Source or ODBC and OLEDB Destination

4. 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:

• Reading Huge volume of data from Sqlite to SQL Server fails at pre-execute

References

• SQL Paging With Limit And Offset In DB2 For i
• Using the OFFSET clause with a cursor
• DB2 Using LIMIT and OFFSET
• Paging through result sets using LIMIT and OFFSET or ROWNUM

Pons