1. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

1. For make our life easier we have to create the function RandInt:

Code:
```drop function RandInt;

CREATE FUNCTION RandInt (X integer)
RETURNS integer
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
not DETERMINISTIC
RETURN int(X * Rand() + .5);```
2. After our function was created we can play heads or tails game without dime, using the query:

Code:
```select case when randint(test) = 0 then 'Tail'
end "Game With Dime"
from
(select 1 test from sysibm.sysdummy1) tst```
3. Average of distribution (in our example for 10000), you'll find out how it close to 5000 = 10000 / 2:

Code:
```with prob (k, maxK, total) as
(select 0, 10000, 0 from sysibm.sysdummy1
union all
select k + 1, maxK, total + randint(1)
from prob
where k + 1 <= maxK
)
select maxK, total from prob
where k = maxK```
Lenny
Last edited by Lenny77; 10-07-10 at 17:54.

2. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

## Average Of Distribution

You can find Average Of Distribution without function, if you don't have the authority to create function:

Code:
```with prob (k, maxK, total) as
(select 0, 10000, 0 from sysibm.sysdummy1
union all
select k + 1, maxK, total + int(rand() + .5)
from prob
where k + 1 <= maxK
)
select maxK, total from prob
where k = maxK```
Lenny

3. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

## Calculating Standard Deviation

VARIANCE The formula used to calculate the biased variance is logically equivalent to:
VARIANCE = SUM(X**2)/COUNT(X) - (SUM(X)/COUNT(X))**2

Standard Deviation:

STDDEV = SQRT(VARIANCE).

Now I'm using the RandInt function, created the test example, to try, how it's working in a real life:

Code:
```with prob (k, maxK, prb, total, totsqr) as
(select 0, 100, 1, 0, 0
from sysibm.sysdummy1
union all
select pb.k + 1, maxK, prb, total + rnd,
totsqr + power(rnd, 2)
from prob pb,
table
(select randint(prb) rnd, pb.k
from sysibm.sysdummy1 ) rr
where pb.k + 1 <= maxK
)
select maxK, strip(char(total)) || ' to ' || char(maxK * prb) chance_to_win,
sqrt(totsqr * 1. / maxK - power((total * 1. / maxK), 2))
as Standard_Deviation
from prob
where k = maxK;```
Typical result:

MAXK...... CHANCE_TO_WIN.......... STANDARD_DEVIATION
10000........... 497 to 1000.......... 4.99995589980552E-001

Lenny
Last edited by Lenny77; 10-08-10 at 16:57.

4. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

## Permutation of characters in the word

Let see how it working
if we make from the input word to the new word with the same characters.

Code:
```with Input (word) as
(select '1234567890ABCDEF' from sysibm.sysdummy1
)
,
RandCharOrder (randword, remword, k) as
(select varchar('', 1000), strip(word), length(word)
from Input
union all
select randword || substr(remword, pos, 1),
case when pos = 1 and length(remword) > 1
then substr(remword, pos + 1)
when length(remword) = 1
then ''
else substr(remword, 1, pos - 1) || substr(remword, pos + 1)
end, ch.k - 1
from RandCharOrder ch, Input,
table
(select randint(length(remword) - 1) + 1 pos
from sysibm.sysdummy1 ) rr

Where length(remword) > 0
)
select word, randword from RandCharOrder, Input
where k = 0;```
Lenny
Last edited by Lenny77; 10-13-10 at 18:52. Reason: change query

5. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

## Change RandInt function, override RandInt

The function RandInt(integer) is working now in interval:
[0, X] when X > 0
[X, 0] when X < 0
[0, 1] when X in (0, 1)

Code:
```drop function RandInt(integer);

CREATE FUNCTION RandInt (X integer)
RETURNS integer
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
not DETERMINISTIC
RETURN
case when X = 0
then int(1 * Rand() + .5)
else int(X * Rand() + 0.5 * sign(X))
end
;```
The function RandInt(integer, integer) is working in interval:
[start#, end#] when end# >= start#
[end#, start#] when end# <= start#

Code:
```CREATE FUNCTION RandInt (start# integer, end# integer)
RETURNS integer
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
not DETERMINISTIC
RETURN RandInt(end# - start#) + start#;```
What could be also interesting to us:
Create statement of RandInt(integer, integer) function consists call to
parent function RandInt(integer).

Example of using new functions:

Code:
```select
randint(500) int1, randint(1000) int2, randint(500, 1000) int3
from sysibm.sysdummy1;```
Lenny

6. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

## Dice game / two players / 3 dices

We will use the RandInt(m, n) function, because of dice has numbers (in random sequence)
from 1 to 6, so it will be RandInt(1, 6) for each dice:

Code:
```select
player1.dice1, player1.dice2, player1.dice3,
player2.dice1, player2.dice2, player2.dice3,
(player1.dice1 + player1.dice2 + player1.dice3) player1,
(player2.dice1 + player2.dice2 + player2.dice3) player2,
case when (player1.dice1 + player1.dice2 + player1.dice3) >
(player2.dice1 + player2.dice2 + player2.dice3)
then 'Player1  WIN !'
when (player1.dice1 + player1.dice2 + player1.dice3) <
(player2.dice1 + player2.dice2 + player2.dice3)
then 'Player2  WIN !'
else 'Tie (draw)'
end result

from
(select randint(1, 6) dice1, randint(1, 6) dice2, randint(1, 6) dice3
from sysibm.sysdummy1) player1
,
(select randint(1, 6) dice1, randint(1, 6) dice2, randint(1, 6) dice3
from sysibm.sysdummy1) player2```
You can TRY.

7. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
Have you ever considered learning Java or C?

8. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Originally Posted by Marcus_A
Have you ever considered learning Java or C?
Looks like CLASS CONSTRUCTOR ?

9. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
Originally Posted by Lenny77
Looks like CLASS CONSTRUCTOR ?
Becasue these types of calcuations seem more appropriate in a programming language than in a DBMS.

10. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Originally Posted by Marcus_A
Becasue these types of calcuations seem more appropriate in a programming language than in a DBMS.
That's what I am exactly want: Using DBMS like any other programming languages.

11. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
Originally Posted by Lenny77
That's what I am exactly want: Using DBMS like any other programming languages.
A DBMS is not a programming language, it is a dababase managment system.

12. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Originally Posted by Marcus_A
A DBMS is not a programming language, it is a dababase managment system.
How you can see, I can use DB2 functions and statements to solve different programming problems. Is it not good ?

13. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
One other question: are you retired?

14. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Originally Posted by Marcus_A
One other question: are you retired?
No, I am not. I am SPA and VP of very huge financial company.

But this question actually not a legal.

15. Registered User
Join Date
Nov 2004
Posts
54
Originally Posted by Lenny77
No, I am not. I am SPA and VP of very huge financial company.

But this question actually not a legal.

I *LOVE* the things you come up with Lenny. Keep it up!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•