Recursive SQL to generate test data

Raymond Bell

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: [login to unmask email]<mailto:[login to unmask email]>
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

Philip Sevetson

Recursive SQL to generate test data
(in response to Raymond Bell)
**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:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 6:36 AM
To: [login to unmask email]
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: [login to unmask email]<mailto:[login to unmask email]>
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.**
Attachments

  • image001.png (3.3k)

Raymond Bell

Recursive SQL to generate test data
(in response to Philip Sevetson)
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:[login to unmask email]
Sent: 28 July 2017 12:32
To: '[login to unmask email]'
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:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 6:36 AM
To: [login to unmask email]<mailto:[login to unmask email]>
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: [login to unmask email]<mailto:[login to unmask email]>
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
Attachments

  • image001.png (3.3k)

Phil Grainger

Recursive SQL to generate test data
(in response to Raymond Bell)
In that session, the examples I reused from Suresh Sane DID include an insert statement wrapped around the recursive select

It generates data alarmingly quickly!

Sent using OWA for iPad
________________________________
From: Bell, Raymond (IT Operations, Technology) <[login to unmask email]>
Sent: 28 July 2017 12:40:24
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Recursive SQL to generate test data

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:[login to unmask email]
Sent: 28 July 2017 12:32
To: '[login to unmask email]'
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:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 6:36 AM
To: [login to unmask email]<mailto:[login to unmask email]>
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: [login to unmask email]<mailto:[login to unmask email]>
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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
________________________________
Attachment Links: image001.png (3 k) https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D8538&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=YArH8MVbtPtqSX8HQ6UbYbXIsiF-lw1dzjYGrkE-aNQ&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_st_-3Fpost-3D182330-26anc-3Dp182330-23p182330&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=vHVeEzJGAoJriVynXFw1pgIaYWvvIRsZRukvmcLY6rc&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=PLzhQw8B-e-ZvZpWvTzMAoijFsW2zb9nzAZM1Nkya7s&e= Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_us_to_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=uBgceF7cx-Ba23qJ1Ig6gzm4KQV72nrm8SLuSVOvSvY&e=

This email has been sent to: [login to unmask email]

Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ESAIGroup.com_idug&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=K9Wg9whNtOfVFF9fU0GNSBHNz0qNU32SzZM1NdNSZu4&e=

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=7XX2wXiW0YYg9RTaISPdWY92XSZxPHCFsQBYc97XFDY&e=

________________________________
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

Marcus Davage

Recursive SQL to generate test data
(in response to Phil Grainger)
Classification: Public

Phil,

I used your method to generate 100s of millions of rows of test data for an application, including partitioning key data. Shaved months off the testing schedule. As soon as testing was over, the database and application were promptly decommissioned.

Marcus
From: Grainger, Phil [mailto:[login to unmask email]
Sent: 28 July 2017 13:44
To: [login to unmask email]
Subject: [DB2-L] - RE: Recursive SQL to generate test data

-- This email has reached the Bank via an external source --
In that session, the examples I reused from Suresh Sane DID include an insert statement wrapped around the recursive select

It generates data alarmingly quickly!

Sent using OWA for iPad
________________________________
From: Bell, Raymond (IT Operations, Technology) <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 28 July 2017 12:40:24
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Recursive SQL to generate test data

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:[login to unmask email]
Sent: 28 July 2017 12:32
To: '[login to unmask email]'
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:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 6:36 AM
To: [login to unmask email]<mailto:[login to unmask email]>
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: [login to unmask email]<mailto:[login to unmask email]>
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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----


Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555. Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500. Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801. Cheltenham & Gloucester plc. Registered Office: Barnett Way, Gloucester GL4 3RL. Registered in England and Wales 2299428. Telephone: 0345 603 1637

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Cheltenham & Gloucester plc is authorised and regulated by the Financial Conduct Authority.

Halifax is a division of Bank of Scotland plc. Cheltenham & Gloucester Savings is a division of Lloyds Bank plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.

Philip Sevetson

Recursive SQL to generate test data
(in response to Phil Grainger)
**please note my email address change**
... and I was able to reproduce the results which Phil G. is reporting. Thanks for the tip.

Raymond, here is the sample SQL which produces the correct result:

DECLARE GLOBAL TEMPORARY TABLE TEMP_SAMPLE_VALS
(SAMPLE_VAL INT NOT NULL,
SV_COUNT INT NOT NULL)
;
INSERT INTO SESSION.TEMP_SAMPLE_VALS
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
;
SELECT * FROM SESSION.TEMP_SAMPLE_VALS
;

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:[login to unmask email]

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 8:44 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Recursive SQL to generate test data

In that session, the examples I reused from Suresh Sane DID include an insert statement wrapped around the recursive select

It generates data alarmingly quickly!

Sent using OWA for iPad
________________________________
From: Bell, Raymond (IT Operations, Technology) <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 28 July 2017 12:40:24
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Recursive SQL to generate test data

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:[login to unmask email]
Sent: 28 July 2017 12:32
To: '[login to unmask email]'
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:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 6:36 AM
To: [login to unmask email]<mailto:[login to unmask email]>
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: [login to unmask email]<mailto:[login to unmask email]>
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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----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.**
Attachments

  • image001.png (3.3k)

Raymond Bell

Recursive SQL to generate test data
(in response to Philip Sevetson)
Ah, so it does, PG. Didn't see that; the young whippersnapper took a look and went, 'Ow, my brain hurts'. But we've knocked together something that does the buz', including generating some random character field values using a combination of SUBSTR, RAND (to start the substring line from) and 1.

He's happy. Should keep him out of my hair for a while... ;o)

And PS, yes, we got something similar to work quite well. :o)

Marcus, I nearly asked you as I was pretty sure you'd done just that. But didn't want to distract you from what you were dealing with this morning. ;o)

Cheers,


Raymond


Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: 28 July 2017 13:58
To: '[login to unmask email]'
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**
... and I was able to reproduce the results which Phil G. is reporting. Thanks for the tip.

Raymond, here is the sample SQL which produces the correct result:

DECLARE GLOBAL TEMPORARY TABLE TEMP_SAMPLE_VALS
(SAMPLE_VAL INT NOT NULL,
SV_COUNT INT NOT NULL)
;
INSERT INTO SESSION.TEMP_SAMPLE_VALS
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
;
SELECT * FROM SESSION.TEMP_SAMPLE_VALS
;

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:[login to unmask email]

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 8:44 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Recursive SQL to generate test data

In that session, the examples I reused from Suresh Sane DID include an insert statement wrapped around the recursive select

It generates data alarmingly quickly!

Sent using OWA for iPad
________________________________
From: Bell, Raymond (IT Operations, Technology) <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 28 July 2017 12:40:24
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Recursive SQL to generate test data

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:[login to unmask email]
Sent: 28 July 2017 12:32
To: '[login to unmask email]'
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:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 6:36 AM
To: [login to unmask email]<mailto:[login to unmask email]>
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: [login to unmask email]<mailto:[login to unmask email]>
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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----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
Attachments

  • image001.png (3.3k)

Marcus Davage

Recursive SQL to generate test data
(in response to Raymond Bell)
Classification: Public

Thanks mate… Thinking of raising an RFE for a new REBIND parameter… REBIND(NEVER)

Regards,
Marcus Davage CEng CITP MBCS
IBM Certified DB2 Database Administrator
LinkedIn http://uk.linkedin.com/in/marcusdavage | IDUG http://www.idug.org/p/us/sn/uid=24500 | Twitter http://twitter.com/spufidoo | About.me http://about.me/spufidoo
From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: 28 July 2017 14:07
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Recursive SQL to generate test data

-- This email has reached the Bank via an external source --

Ah, so it does, PG. Didn’t see that; the young whippersnapper took a look and went, ‘Ow, my brain hurts’. But we’ve knocked together something that does the buz’, including generating some random character field values using a combination of SUBSTR, RAND (to start the substring line from) and 1.

He’s happy. Should keep him out of my hair for a while… ;o)

And PS, yes, we got something similar to work quite well. :o)

Marcus, I nearly asked you as I was pretty sure you’d done just that. But didn’t want to distract you from what you were dealing with this morning. ;o)

Cheers,


Raymond


Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: 28 July 2017 13:58
To: '[login to unmask email]'
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**
… and I was able to reproduce the results which Phil G. is reporting. Thanks for the tip.

Raymond, here is the sample SQL which produces the correct result:

DECLARE GLOBAL TEMPORARY TABLE TEMP_SAMPLE_VALS
(SAMPLE_VAL INT NOT NULL,
SV_COUNT INT NOT NULL)
;
INSERT INTO SESSION.TEMP_SAMPLE_VALS
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
;
SELECT * FROM SESSION.TEMP_SAMPLE_VALS
;

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:[login to unmask email]

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 8:44 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Recursive SQL to generate test data

In that session, the examples I reused from Suresh Sane DID include an insert statement wrapped around the recursive select

It generates data alarmingly quickly!

Sent using OWA for iPad
________________________________
From: Bell, Raymond (IT Operations, Technology) <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 28 July 2017 12:40:24
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Recursive SQL to generate test data

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:[login to unmask email]
Sent: 28 July 2017 12:32
To: '[login to unmask email]'
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:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 6:36 AM
To: [login to unmask email]<mailto:[login to unmask email]>
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: [login to unmask email]<mailto:[login to unmask email]>
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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----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
-----End Original Message-----


Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555. Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500. Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801. Cheltenham & Gloucester plc. Registered Office: Barnett Way, Gloucester GL4 3RL. Registered in England and Wales 2299428. Telephone: 0345 603 1637

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Cheltenham & Gloucester plc is authorised and regulated by the Financial Conduct Authority.

Halifax is a division of Bank of Scotland plc. Cheltenham & Gloucester Savings is a division of Lloyds Bank plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.
Attachments

  • image001.png (3.3k)

Phil Grainger

Recursive SQL to generate test data
(in response to Marcus Davage)
Nice of you to remember me, but strictly it's not "my" method

Sent using OWA for iPad
________________________________
From: Davage, Marcus (ITS Database Services - DB2) <[login to unmask email]>
Sent: 28 July 2017 13:54:42
To: [login to unmask email]
Subject: [DB2-L] - RE: Recursive SQL to generate test data

Classification: Public

Phil,

I used your method to generate 100s of millions of rows of test data for an application, including partitioning key data. Shaved months off the testing schedule. As soon as testing was over, the database and application were promptly decommissioned.

Marcus
From: Grainger, Phil [mailto:[login to unmask email]
Sent: 28 July 2017 13:44
To: [login to unmask email]
Subject: [DB2-L] - RE: Recursive SQL to generate test data

-- This email has reached the Bank via an external source --
In that session, the examples I reused from Suresh Sane DID include an insert statement wrapped around the recursive select

It generates data alarmingly quickly!

Sent using OWA for iPad
________________________________
From: Bell, Raymond (IT Operations, Technology) <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 28 July 2017 12:40:24
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Recursive SQL to generate test data

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:[login to unmask email]
Sent: 28 July 2017 12:32
To: '[login to unmask email]'
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:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 6:36 AM
To: [login to unmask email]<mailto:[login to unmask email]>
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: [login to unmask email]<mailto:[login to unmask email]>
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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----


Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555. Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500. Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801. Cheltenham & Gloucester plc. Registered Office: Barnett Way, Gloucester GL4 3RL. Registered in England and Wales 2299428. Telephone: 0345 603 1637

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Cheltenham & Gloucester plc is authorised and regulated by the Financial Conduct Authority.

Halifax is a division of Bank of Scotland plc. Cheltenham & Gloucester Savings is a division of Lloyds Bank plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

Philip Sevetson

Recursive SQL to generate test data
(in response to Phil Grainger)
**please note my email address change**
As Phil G. notes, and as I should have mentioned earlier, the definitive work on this subject was published on this list (and presumably other places) a few years ago by Suresh Sane and I think we're all in his debt for this.

If he had previous primary sources, I don't remember them, although this stuff can presumably be found in the SQL Reference manual, with the appropriate (large) amounts of digging and skull sweat.

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:[login to unmask email]

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 9:55 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Recursive SQL to generate test data

Nice of you to remember me, but strictly it's not "my" method

Sent using OWA for iPad
________________________________
From: Davage, Marcus (ITS Database Services - DB2) <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 28 July 2017 13:54:42
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Recursive SQL to generate test data

Classification: Public


Phil,

I used your method to generate 100s of millions of rows of test data for an application, including partitioning key data. Shaved months off the testing schedule. As soon as testing was over, the database and application were promptly decommissioned.

Marcus
From: Grainger, Phil [mailto:[login to unmask email]
Sent: 28 July 2017 13:44
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Recursive SQL to generate test data

-- This email has reached the Bank via an external source --
In that session, the examples I reused from Suresh Sane DID include an insert statement wrapped around the recursive select

It generates data alarmingly quickly!

Sent using OWA for iPad
________________________________
From: Bell, Raymond (IT Operations, Technology) <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 28 July 2017 12:40:24
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Recursive SQL to generate test data

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:[login to unmask email]
Sent: 28 July 2017 12:32
To: '[login to unmask email]'
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:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 6:36 AM
To: [login to unmask email]<mailto:[login to unmask email]>
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: [login to unmask email]<mailto:[login to unmask email]>
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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----


Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555. Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500. Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801. Cheltenham & Gloucester plc. Registered Office: Barnett Way, Gloucester GL4 3RL. Registered in England and Wales 2299428. Telephone: 0345 603 1637

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Cheltenham & Gloucester plc is authorised and regulated by the Financial Conduct Authority.

Halifax is a division of Bank of Scotland plc. Cheltenham & Gloucester Savings is a division of Lloyds Bank plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----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.**
Attachments

  • image001.png (3.3k)

Suresh Sane

Recursive SQL to generate test data
(in response to Philip Sevetson)
Good to be remembered....-:)


Here is the full SQL I published about 9 years ago. Example includes smallint, char, dec and date with the specified rages below.


You can find it on IDUG website many places in one of my presentations.


Thx

Suresh


EMPID SMALLINT 1 thru 10000

FNAME CHAR(20) 3-7 chars

LNAME CHAR(20) 3-10 chars

SALARY DEC(7,2) 1000 to 5000

HIREDATE DATE 21 years thru 1 year



INSERT INTO RECASE06

WITH NUMBERS (LEVEL, NEXTONE) AS

(SELECT 1, 1

FROM SYSIBM.SYSDUMMY1

UNION ALL

SELECT LEVEL + 1, LEVEL + 1

FROM NUMBERS

WHERE LEVEL < 10 )

SELECT INTEGER(ROUND(RAND()*9999,0)) + 1

, LEFT(SUBSTR('BCDFGHJKLMNPRSTVWZ',

INTEGER(ROUND(RAND()*17,0))+1, 1)

CONCAT

SUBSTR('AEIOUY', INTEGER(ROUND(RAND()*5,0))+1, 1)

<<< REPEAT 5 TIMES>>>

, INTEGER(ROUND(RAND()*4,0)) + 3)

, LEFT(SUBSTR('BCDFGHJKLMNPRSTVWZ',

INTEGER(ROUND(RAND()*17,0))+1, 1)

CONCAT SUBSTR('AEIOUY', INTEGER(ROUND(RAND()*5,0))+1, 1)

<<< REPEAT 5 TIMES>>>

, INTEGER(ROUND(RAND()*7,0)) + 3)

, DECIMAL((1000.00 + RAND()*4000),7,2)

, CURRENT DATE - 1 YEAR

- INTEGER(20*365*RAND()) DAYS

FROM NUMBERS



________________________________
From: Sevetson, Phil <[login to unmask email]>
Sent: Friday, July 28, 2017 2:44 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Recursive SQL to generate test data

**please note my email address change**

As Phil G. notes, and as I should have mentioned earlier, the definitive work on this subject was published on this list (and presumably other places) a few years ago by Suresh Sane and I think we’re all in his debt for this.



If he had previous primary sources, I don’t remember them, although this stuff can presumably be found in the SQL Reference manual, with the appropriate (large) amounts of digging and skull sweat.



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:[login to unmask email]



From: Grainger, Phil [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 9:55 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Recursive SQL to generate test data



Nice of you to remember me, but strictly it's not "my" method

Sent using OWA for iPad

________________________________

From: Davage, Marcus (ITS Database Services - DB2) <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 28 July 2017 13:54:42
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Recursive SQL to generate test data



Classification: Public



Phil,



I used your method to generate 100s of millions of rows of test data for an application, including partitioning key data. Shaved months off the testing schedule. As soon as testing was over, the database and application were promptly decommissioned.



Marcus

From: Grainger, Phil [mailto:[login to unmask email]
Sent: 28 July 2017 13:44
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Recursive SQL to generate test data



-- This email has reached the Bank via an external source --
In that session, the examples I reused from Suresh Sane DID include an insert statement wrapped around the recursive select

It generates data alarmingly quickly!

Sent using OWA for iPad

________________________________

From: Bell, Raymond (IT Operations, Technology) <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 28 July 2017 12:40:24
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Recursive SQL to generate test data



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:[login to unmask email]
Sent: 28 July 2017 12:32
To: '[login to unmask email]'
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:[login to unmask email]



From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Friday, July 28, 2017 6:36 AM
To: [login to unmask email]<mailto:[login to unmask email]>
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: [login to unmask email]<mailto:[login to unmask email]>
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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=

-----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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=W9Uk-5pBeWSQkoYNMSLPUr3-bUfVO_cWAxM2AbTBuG8&s=EfzPr019Eiu8GUSX9HUF9-jYUOfEozIqADzHJp-j3Q8&e=

-----End Original Message-----

BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

-----End Original Message-----



Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555. Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500. Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801. Cheltenham & Gloucester plc. Registered Office: Barnett Way, Gloucester GL4 3RL. Registered in England and Wales 2299428. Telephone: 0345 603 1637

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Cheltenham & Gloucester plc is authorised and regulated by the Financial Conduct Authority.

Halifax is a division of Bank of Scotland plc. Cheltenham & Gloucester Savings is a division of Lloyds Bank plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.



-----End Original Message-----

BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

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

J&#248;rn Thyssen

RE: Recursive SQL to generate test data
(in response to Raymond Bell)

Hi Raymond,

Why not make a simple native SP looping over (multi row) INSERTs? Code will be readable and modifiable by normal people unlike recursive SQL, and you could even add some commit logic to make the DBA (you?!) happy.

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Suresh Sane

Recursive SQL to generate test data
(in response to Jørn Thyssen)
Jorn,


Your point about commit logic is very important.


However, I don't necessarily buy the "normal people" argument and I am reminded of a quote from Prof Dijkstra:

“Don't blame me for the fact that competent programming, … will be too difficult for 'the average programmer', you must not fall into the trap of rejecting a surgical technique because it is beyond the capabilities of the barber in his shop around the corner.”


Everyone on the list is a "surgeon" and can handle it..-:)


Thx

Suresh


-----End Original Message-----

Nadir Doctor

Recursive SQL to generate test data
(in response to Suresh Sane)
Well said, Suresh but Prof Dijkstra also emphasized code simplicity [blend
of which is an art] - here are some of his other enlightening and amusing
quotes:

https://en.wikiquote.org/wiki/Edsger_W._Dijkstra


Best Regards,
Nadir



On Sun, Jul 30, 2017 at 9:04 AM, suresh sane <[login to unmask email]> wrote:

> Jorn,
>
>
> Your point about commit logic is very important.
>
>
> However, I don't necessarily buy the "normal people" argument and I am
> reminded of a quote from Prof Dijkstra:
>
> “Don't blame me for the fact that competent programming, … will be too
> difficult for 'the average programmer', you must not fall into the trap of
> rejecting a surgical technique because it is beyond the capabilities of the
> barber in his shop around the corner.”
>
>
> Everyone on the list is a "surgeon" and can handle it..-:)
>
>
> Thx
>
> Suresh
>
> ------------------------------
> *From:* Jørn Thyssen <[login to unmask email]>
> *Sent:* Friday, July 28, 2017 9:17 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: Recursive SQL to generate test data
>
>
> Hi Raymond,
>
> Why not make a simple native SP looping over (multi row) INSERTs? Code
> will be readable and modifiable by normal people unlike recursive SQL, and
> you could even add some commit logic to make the DBA (you?!) happy.
>
>
>
> Best regards,
>
> Jørn Thyssen
>
> Rocket Software
> 77 Fourth Avenue • Waltham, MA • 02451 • USA
> E: [login to unmask email] • W: www.rocketsoftware.com
>
> Views are personal.
>
> -----End Original Message-----
>
> -----End Original Message-----
>

J&#248;rn Thyssen

RE: Recursive SQL to generate test data
(in response to Suresh Sane)

Hi Suresh,

Point taken! And of course I did not intend to insult the "not normal" people on the list ;)

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Raymond Bell

Recursive SQL to generate test data
(in response to Jørn Thyssen)
None taken, Jørn. :o) And for what will hopefully be a one-off I’m happy for my young Padawan to be as surgical as he likes. :o)


Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: Jørn Thyssen [mailto:[login to unmask email]
Sent: 30 July 2017 17:04
To: [login to unmask email]
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"
*********************************************


Hi Suresh,

Point taken! And of course I did not intend to insult the "not normal" people on the list ;)



Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email]<mailto:[login to unmask email]> • W: www.rocketsoftware.com http://www.rocketsoftware.com

Views are personal.

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

Philip Sevetson

Recursive SQL to generate test data
(in response to Jørn Thyssen)
**please note my email address change**
Jørn,

My personal experience with the recursion code is that I found it difficult at first, as you seem to have. But a couple of days’ intermittent practice was enough that I was comfortable writing it, and reading what I’d written (and what Suresh has written, that I have read).

I know it’s visually off-putting at first, but that’s a temporary effect.

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:[login to unmask email]

From: Jørn Thyssen [mailto:[login to unmask email]
Sent: Sunday, July 30, 2017 12:04 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Recursive SQL to generate test data


Hi Suresh,

Point taken! And of course I did not intend to insult the "not normal" people on the list ;)



Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email]<mailto:[login to unmask email]> • W: www.rocketsoftware.com http://www.rocketsoftware.com

Views are personal.

-----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.**
Attachments

  • image001.png (3.3k)