![]() ![]() One example generates character data by putting the mod() function in the second parameter of the translate() function, and Db2 for i does not allow that (yet). Be aware that the queries may not work on your system. (See the first two links under Related Stories.) They’re not for Db2 for i, but you may find them helpful. I’ve included links to two articles from the IBM Knowledge Center for your benefit. Int(mod(RandName*0.0001,53)+1), 1) as Name,Ĭurrent date + int(rand()*100) days as DueDateĭoes it work? You be the judge. substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz', You can generate more characters by dividing RandName by other powers of ten. You have one random character, which is either a letter or space. Substr() returns one character from the literal. Adding one gives me a valid subscript value to the string. The mod() function divides RandName by 53, the number of characters in the literal, returning a number from zero to 52. You can use RandName to generate subscripts into an array of characters. RandName is a five-digit packed-decimal random number. Substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz', How do you translate those numeric values into character strings? It’s slightly messy, but not difficult. The rand() function only returns numeric values. You need to generate a random Name value. This expression adds up to 100 days to the current date. Use the rand() function to generate a number of days to adjust a reference date, such as the current date. If you want some negative numbers as well, adjust the expression to give you some. If you want a positive number, you can use an expression much like the one for the Type column. The Balance column is a five-digit packed-decimal number. I’ve never seen a random number generator return a zero or a one, only numbers in between them.) The dec() function removes the decimal places, leaving a whole number. (Chances of the number being four are slim to none. Adding one to that puts it into the range one to four. Multiplying that number by three changes it to a random number in the range zero to three. The rand() function generates a random number in the range zero to one. The Type column should have an integer from 1 to 3. Now put random data into the other columns. This generates 25 rows with sequential ID numbers. If so, a recursive common table expression generates the sequence. You might just use a sequential number for ID, the primary key. You need data for these columns (fields). However, since this is an illustration, here’s the statement to create my illustrative table. (That’s not realistic, but it will keep the example small and I can illustrate the techniques.) You already have a copy of your table, and you just need to load data into it. Suppose you wish to test a query against a single table. Obviously we can’t key that much data, so how do we generate it? We use SQL. Running a query against a test dataset of 25 rows (records) can produce unpleasant surprises when it’s installed into production and forced to face a production dataset of thousands or even millions of rows. Suppose this scenario is not mere supposition.īefore an SQL query goes into production, it should be tested against a production-like dataset. Suppose that I needed a way to generate a lot - and I do mean a lot! - of random data. Suppose that, for security reasons, I was not allowed to copy the production version of the table. Suppose I needed to generate a large database table with random data in order to adequately test the performance of an SQL query. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |