Phil, that’s beautiful, and exactly what I was thinking of/hoping for. I think that’s more than enough rope for my young colleague to be hanging himself with.
We found an old UK DB2 User Group presentation by Phil Grainger on the subject (I was in the audience at the time and remember bits of my brain falling out…although evidently not those bits related to memory) but didn’t look too closely at it. Your working example is perfect. He’ll just have to modify the Select to conjure up whatever data he’s after (about 20 different tables, millions of rows), slap it out to a file via TIAUL and try to load it up. Should be fun.
I have a vague recollection of successfully trying some limited recursive SQL shortly after the presentation but that bit of memory, although not technically lost, has been migrated off to ML2 and I think the tape silo’s poked. :o(
Cheers,
Raymond
Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS
From: Sevetson, Phil [
mailto:db2-l@lists.idug.org]Sent: 28 July 2017 12:32
To: '
db2-l@lists.idug.org'
Subject: [DB2-L] - RE: Recursive SQL to generate test data
*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************
**please note my email address change**
Raymond,
Basic recursion is a simple mechanism. After that, randomizing the contents is a matter of using whatever mechanism you wish. Here’s the first part:
WITH UNIFORM_DISTRIBUTION (LEVEL, SAMPLE_VAL)
AS
(SELECT 1, 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT LEVEL + 1, INTEGER(RAND() * 10)
FROM UNIFORM_DISTRIBUTION
WHERE LEVEL < 100
)
SELECT SAMPLE_VAL, COUNT(*)
FROM UNIFORM_DISTRIBUTION
GROUP BY SAMPLE_VAL
;
This is just a quick select to generate a few random numbers and then report on their distribution. You’d have to modify the randomizing functions to create the data types which you need, and then set LEVEL < n, where n is the number of rows you want to generate.
Also, you can’t just INSERT this stuff. CTE’s (the WITH/AS part of the SQL) don’t like INSERT as the final piece. What I wound up doing was putting this statement in a DSNTIAUL step, which contained the output of the final SELECT.
Hit me up with more details about what you want to produce, if this doesn’t give you enough information to do what your colleague needs.
Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:
image001.png@01D261E4.BE68E970]
From: Bell, Raymond (IT Operations, Technology) [
mailto:db2-l@lists.idug.org]Sent: Friday, July 28, 2017 6:36 AM
To:
db2-l@lists.idug.org<
mailto:db2-l@lists.idug.org>Subject: [DB2-L] - Recursive SQL to generate test data
All,
I seem to remember some posts here a while ago about using recursive SQL to generate test data. A colleague here is facing the awkward task of estimating the time some massive reorgs will take, and the best way is to semi-simulate it with Prod-sized garbage test data. Copying from Prod is obviously verboten but I seem to recall some recursive SQL that can knock up Ks of random data rows in an instant.
Anyone care to share?
Cheers,
Raymond
Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS
RBS Gobarburn | House E 2nd Floor | Edinburgh EH12 1HQ | Depot Code 045
Email:
raymond.bell@rbs.co.uk<
mailto:raymond.bell@rbs.co.uk>The content of this email is INTERNAL unless stated otherwise
The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.
National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.
The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.
This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.
Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.
Visit our website at
www.rbs.com http://www.rbs.com-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.
National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.
The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.
This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.
Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.
Visit our website at
www.rbs.com http://www.rbs.com