|
View previous topic :: View next topic
|
| Author |
Message |
Jimy Carol
New User
Joined: 31 Aug 2012
Posts: 25
Location: Australia
|
|
|
|
Hello ,
I need to create massive test data for a DB2 table (more than 1 billion) with 5 columns . Is using a recursive SQL is good idea . I heard that it should not used for generating huge data. Could someone guide me as the pros and cons of using recursive SQL to genrate test data , here is the sql i am trying to use :
WITH NUMBERS (LEVEL, NEXTONE) AS
(
SELECT 1, 1
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT LEVEL +1, LEVEL +1
FROM NUMBERS
WHERE LEVEL < 1000000
)
SELECT NEXTONE
FROM NUMBERS
ORDER BY NEXTONE
Regards
carol |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
You've been posting long enough to know to use the Code tags.
Doing anything billions of times requires doing it cpu-efficiently, not source-code-efficiently. Create your data outside DB2 (SORT, IEBDG, a program) and stuff it in (not a technical expression) en masse.
Might cost you an hour of your time doing it, but will save many hours babysitting the process itself. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Problem with recursive SQL or any SQL generating huge temp data is that it consumes memory or DSNDB07 space. not to mention CPU-cycles.
IEBDG + LOAD like Bill suggested
or ...
Its been a while since I tried this, but I believe you can get the same result with an sensitive dynamic cursor :
*UNTESTED*
poulate the table with a 1000 rows.
| Code: |
alter tablespace mytablespace not logged
open mycursor sensitive dynamic cursor for select keycolumn, datacolumn from mytable
fetch into :mykey, :mydata
perform until sqlcode <> 0 or :mykey > 100000
insert into mytable :mykey + 1000, :mydata
fetch into :mykey, :mydata
end-perform
close cursor
alter tablespace mytablespace logged
|
|
|
| Back to top |
|
|
|
|
