DB2 - L

Expand all | Collapse all

Recursive SQL to generate test data

  • 1.  Recursive SQL to generate test data

    Posted Jul 28, 2017 06:36 AM
    All,

    I seem to remember some posts here a while ago about using recursive SQL to generate test data. A colleague here is facing the awkward task of estimating the time some massive reorgs will take, and the best way is to semi-simulate it with Prod-sized garbage test data. Copying from Prod is obviously verboten but I seem to recall some recursive SQL that can knock up Ks of random data rows in an instant.

    Anyone care to share?

    Cheers,


    Raymond

    Raymond Bell
    DB2 Database Administrator | IT Operations | Technology | RBS
    RBS Gobarburn | House E 2nd Floor | Edinburgh EH12 1HQ | Depot Code 045
    Email: raymond.bell@rbs.co.uk<mailto:raymond.bell@rbs.co.uk>
    The content of this email is INTERNAL unless stated otherwise

    The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

    National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

    The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

    This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

    Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

    Visit our website at www.rbs.com http://www.rbs.com


  • 2.  Recursive SQL to generate test data

    Posted Jul 28, 2017 07:32 AM
    **please note my email address change**
    Raymond,

    Basic recursion is a simple mechanism. After that, randomizing the contents is a matter of using whatever mechanism you wish. Here’s the first part:

    WITH UNIFORM_DISTRIBUTION (LEVEL, SAMPLE_VAL)
    AS
    (SELECT 1, 1 FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT LEVEL + 1, INTEGER(RAND() * 10)
    FROM UNIFORM_DISTRIBUTION
    WHERE LEVEL < 100
    )
    SELECT SAMPLE_VAL, COUNT(*)
    FROM UNIFORM_DISTRIBUTION
    GROUP BY SAMPLE_VAL
    ;

    This is just a quick select to generate a few random numbers and then report on their distribution. You’d have to modify the randomizing functions to create the data types which you need, and then set LEVEL < n, where n is the number of rows you want to generate.

    Also, you can’t just INSERT this stuff. CTE’s (the WITH/AS part of the SQL) don’t like INSERT as the final piece. What I wound up doing was putting this statement in a DSNTIAUL step, which contained the output of the final SELECT.

    Hit me up with more details about what you want to produce, if this doesn’t give you enough information to do what your colleague needs.



    Philip Sevetson
    Computer Systems Manager
    5 Manhattan West (33rd St at 10th Ave)
    New York, NY 10001-2632
    212-857-1688 w
    917-991-7052 c
    212-857-1659 f
    [cid:image001.png@01D261E4.BE68E970]

    From: Bell, Raymond (IT Operations, Technology) [mailto:db2-l@lists.idug.org]
    Sent: Friday, July 28, 2017 6:36 AM
    To: db2-l@lists.idug.org
    Subject: [DB2-L] - Recursive SQL to generate test data

    All,

    I seem to remember some posts here a while ago about using recursive SQL to generate test data. A colleague here is facing the awkward task of estimating the time some massive reorgs will take, and the best way is to semi-simulate it with Prod-sized garbage test data. Copying from Prod is obviously verboten but I seem to recall some recursive SQL that can knock up Ks of random data rows in an instant.

    Anyone care to share?

    Cheers,


    Raymond

    Raymond Bell
    DB2 Database Administrator | IT Operations | Technology | RBS
    RBS Gobarburn | House E 2nd Floor | Edinburgh EH12 1HQ | Depot Code 045
    Email: raymond.bell@rbs.co.uk<mailto:raymond.bell@rbs.co.uk>
    The content of this email is INTERNAL unless stated otherwise

    The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

    National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

    The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

    This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

    Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.


    Visit our website at www.rbs.com http://www.rbs.com
    -----End Original Message-----
    **This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**


  • 3.  Recursive SQL to generate test data

    Posted Jul 28, 2017 07:40 AM
    Phil, that’s beautiful, and exactly what I was thinking of/hoping for. I think that’s more than enough rope for my young colleague to be hanging himself with.

    We found an old UK DB2 User Group presentation by Phil Grainger on the subject (I was in the audience at the time and remember bits of my brain falling out…although evidently not those bits related to memory) but didn’t look too closely at it. Your working example is perfect. He’ll just have to modify the Select to conjure up whatever data he’s after (about 20 different tables, millions of rows), slap it out to a file via TIAUL and try to load it up. Should be fun.

    I have a vague recollection of successfully trying some limited recursive SQL shortly after the presentation but that bit of memory, although not technically lost, has been migrated off to ML2 and I think the tape silo’s poked. :o(

    Cheers,


    Raymond

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

    From: Sevetson, Phil [mailto:db2-l@lists.idug.org]
    Sent: 28 July 2017 12:32
    To: 'db2-l@lists.idug.org'
    Subject: [DB2-L] - RE: Recursive SQL to generate test data


    *********************************************
    " This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
    *********************************************

    **please note my email address change**
    Raymond,

    Basic recursion is a simple mechanism. After that, randomizing the contents is a matter of using whatever mechanism you wish. Here’s the first part:

    WITH UNIFORM_DISTRIBUTION (LEVEL, SAMPLE_VAL)
    AS
    (SELECT 1, 1 FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT LEVEL + 1, INTEGER(RAND() * 10)
    FROM UNIFORM_DISTRIBUTION
    WHERE LEVEL < 100
    )
    SELECT SAMPLE_VAL, COUNT(*)
    FROM UNIFORM_DISTRIBUTION
    GROUP BY SAMPLE_VAL
    ;

    This is just a quick select to generate a few random numbers and then report on their distribution. You’d have to modify the randomizing functions to create the data types which you need, and then set LEVEL < n, where n is the number of rows you want to generate.

    Also, you can’t just INSERT this stuff. CTE’s (the WITH/AS part of the SQL) don’t like INSERT as the final piece. What I wound up doing was putting this statement in a DSNTIAUL step, which contained the output of the final SELECT.

    Hit me up with more details about what you want to produce, if this doesn’t give you enough information to do what your colleague needs.



    Philip Sevetson
    Computer Systems Manager
    5 Manhattan West (33rd St at 10th Ave)
    New York, NY 10001-2632
    212-857-1688 w
    917-991-7052 c
    212-857-1659 f
    [cid:image001.png@01D261E4.BE68E970]

    From: Bell, Raymond (IT Operations, Technology) [mailto:db2-l@lists.idug.org]
    Sent: Friday, July 28, 2017 6:36 AM
    To: db2-l@lists.idug.org<mailto:db2-l@lists.idug.org>
    Subject: [DB2-L] - Recursive SQL to generate test data

    All,

    I seem to remember some posts here a while ago about using recursive SQL to generate test data. A colleague here is facing the awkward task of estimating the time some massive reorgs will take, and the best way is to semi-simulate it with Prod-sized garbage test data. Copying from Prod is obviously verboten but I seem to recall some recursive SQL that can knock up Ks of random data rows in an instant.

    Anyone care to share?

    Cheers,


    Raymond

    Raymond Bell
    DB2 Database Administrator | IT Operations | Technology | RBS
    RBS Gobarburn | House E 2nd Floor | Edinburgh EH12 1HQ | Depot Code 045
    Email: raymond.bell@rbs.co.uk<mailto:raymond.bell@rbs.co.uk>
    The content of this email is INTERNAL unless stated otherwise

    The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

    National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

    The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

    This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

    Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.


    Visit our website at www.rbs.com http://www.rbs.com
    -----End Original Message-----
    **This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
    -----End Original Message-----
    The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

    National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

    The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

    This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

    Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

    Visit our website at www.rbs.com http://www.rbs.com


  • 4.  Recursive SQL to generate test data

    Posted Jul 28, 2017 08:44 AM
    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)


  • 5.  Recursive SQL to generate test data

    Posted Jul 28, 2017 08:55 AM
    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:db2-l@lists.idug.org]
    Sent: 28 July 2017 13:44
    To: db2-l@lists.idug.org
    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)


  • 6.  Recursive SQL to generate test data

    Posted Jul 28, 2017 09:55 AM
    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)


  • 7.  Recursive SQL to generate test data

    Posted Jul 28, 2017 10:45 AM
    **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:image001.png@01D261E4.BE68E970]

    From: Grainger, Phil [mailto:db2-l@lists.idug.org]
    Sent: Friday, July 28, 2017 9:55 AM
    To: db2-l@lists.idug.org
    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)


  • 8.  Recursive SQL to generate test data

    Posted Jul 28, 2017 10:54 AM
    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


  • 9.  Recursive SQL to generate test data

    Posted Jul 28, 2017 08:58 AM
    **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:image001.png@01D261E4.BE68E970]

    From: Grainger, Phil [mailto:db2-l@lists.idug.org]
    Sent: Friday, July 28, 2017 8:44 AM
    To: db2-l@lists.idug.org
    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)


  • 10.  Recursive SQL to generate test data

    Posted Jul 28, 2017 09:07 AM
    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:db2-l@lists.idug.org]
    Sent: 28 July 2017 13:58
    To: 'db2-l@lists.idug.org'
    Subject: [DB2-L] - RE: Recursive SQL to generate test data


    *********************************************
    " This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
    *********************************************

    **please note my email address change**
    ... 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:image001.png@01D261E4.BE68E970]

    From: Grainger, Phil [mailto:db2-l@lists.idug.org]
    Sent: Friday, July 28, 2017 8:44 AM
    To: db2-l@lists.idug.org<mailto:db2-l@lists.idug.org>
    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)


  • 11.  Recursive SQL to generate test data

    Posted Jul 28, 2017 09:38 AM
    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:db2-l@lists.idug.org]
    Sent: 28 July 2017 14:07
    To: 'db2-l@lists.idug.org'
    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:db2-l@lists.idug.org]
    Sent: 28 July 2017 13:58
    To: 'db2-l@lists.idug.org'
    Subject: [DB2-L] - RE: Recursive SQL to generate test data


    *********************************************
    " This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
    *********************************************
    **please note my email address change**
    … 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:image001.png@01D307AE.FEAB00C0]

    From: Grainger, Phil [mailto:db2-l@lists.idug.org]
    Sent: Friday, July 28, 2017 8:44 AM
    To: db2-l@lists.idug.org<mailto:db2-l@lists.idug.org>
    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)


  • 12.  RE: Recursive SQL to generate test data

    Posted Jul 28, 2017 05:18 PM

    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: jthyssen@rocketsoftware.com • W: www.rocketsoftware.com 

    Views are personal. 



  • 13.  Recursive SQL to generate test data

    Posted Jul 30, 2017 10:05 AM
    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-----


  • 14.  Recursive SQL to generate test data

    Posted Jul 30, 2017 10:50 AM
    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


  • 15.  RE: Recursive SQL to generate test data

    Posted Jul 30, 2017 12:04 PM

    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: jthyssen@rocketsoftware.com • W: www.rocketsoftware.com 

    Views are personal. 



  • 16.  Recursive SQL to generate test data

    Posted Jul 31, 2017 03:56 AM
    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:db2-l@lists.idug.org]
    Sent: 30 July 2017 17:04
    To: DB2-L@lists.idug.org
    Subject: [DB2-L] - RE: Recursive SQL to generate test data


    *********************************************
    " This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
    *********************************************


    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: jthyssen@rocketsoftware.com<mailto:jthyssen@rocketsoftware.com> • 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


  • 17.  Recursive SQL to generate test data

    Posted Jul 31, 2017 10:04 AM
    **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:image001.png@01D261E4.BE68E970]

    From: Jørn Thyssen [mailto:db2-l@lists.idug.org]
    Sent: Sunday, July 30, 2017 12:04 PM
    To: DB2-L@lists.idug.org
    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: jthyssen@rocketsoftware.com<mailto:jthyssen@rocketsoftware.com> • 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.**