How to grow sports2000 database?

Posted by Valeriy Bashkatov on 09-Nov-2015 04:40

Hello,

Has anyone had experience of growing database "Sports/Sports2000" to arbitrary sizes, for example, up to 10GB or more with full support relationships between his tables?

Regards,
Valeriy

OpenEdge RDBMS - Forum

Posted by Richard Shulman on 09-Nov-2015 07:28

This code does not grow to a specific size but you can define numcustomers value and it will create random amounts of orders and orderlines for each customer.

DEFINE VARIABLE mylinenum AS INT64 NO-UNDO INIT 0.

DEFINE VARIABLE linemax AS INT64 NO-UNDO INIT 0.

DEFINE VARIABLE itmnum AS INT64 NO-UNDO INIT 0.

DEFINE VARIABLE itmcount AS INT64 NO-UNDO INIT 0.

DEFINE VARIABLE shipstate AS CHARACTER NO-UNDO INIT "Ordered,Back Ordered,Shipped" .

DEFINE VARIABLE numcustomers AS INT64 NO-UNDO INIT 10000.

DEFINE VARIABLE numorders AS INT64 NO-UNDO INIT 6.

DEFINE VARIABLE loop AS INT64 NO-UNDO.

DEFINE VARIABLE nextcustnum AS INT64 NO-UNDO.

DEFINE VARIABLE currordnum AS INT64 NO-UNDO.

DEF VAR list AS CHAR INIT "HXM,DKP,RDR,SLS,JAL,DOS,BBB" NO-UNDO.

DEF VAR counter AS INT NO-UNDO.

DEF VAR counter2 AS INT NO-UNDO.

DEF VAR counter3 AS INT NO-UNDO.

DEF VAR custbuff AS HANDLE NO-UNDO.

DEFINE VARIABLE creditcards AS CHARACTER NO-UNDO INIT "Visa,MasterCard,Discover,American Express".

DEFINE BUFFER custbuff FOR database1.customer.

FIND FIRST custbuff NO-LOCK.

REPEAT loop = 1 TO numcustomers.

nextcustnum = NEXT-VALUE(nextcustnum).

IF counter MOD num-entries(list) = 0 THEN counter2 = counter MOD num-entries(list) + 1.

ELSE IF counter MOD num-entries(list) > 7 THEN counter2 = 7.

ELSE counter2 = counter MOD num-entries(list).

BUFFER-COPY custbuff TO customer ASSIGN custnum = nextcustnum salesrep=ENTRY(counter2,list).

numorders = RANDOM(1,25).

REPEAT counter2 = 1 TO numorders.

currordnum = NEXT-VALUE(nextordnum).

FIND FIRST order WHERE ordernum = currordnum NO-ERROR.

IF NOT(AVAIL(order)) THEN

DO:

CREATE order.

VIEW-AS ALERT-BOX INFO BUTTONS OK.*/

END.

ELSE

NEXT.

ASSIGN ordernum = currordnum

custnum = nextcustnum

orderdate = TODAY

shipdate = TODAY + RANDOM(1,20)

promisedate = TODAY + RANDOM(20,30)

carrier = "Fedex"

PO="PO" + string(RANDOM(1000,17000))

warehousenum = RANDOM(1,14)

creditcard = entry(RANDOM(1,4),creditcards).

linemax = RANDOM(1,5).

mylinenum = 1.

REPEAT counter3 = 1 TO linemax.

CREATE orderline.

VIEW-AS ALERT-BOX INFO BUTTONS OK.*/

ASSIGN ordernum = currordnum

linenum = mylinenum.

itmnum = RANDOM(1,30).

FIND FIRST ITEM WHERE item.itemnum = itmnum.

ASSIGN orderline.itemnum = itmnum

orderline.price = ITEM.price

discount = RANDOM(0,5)

orderlinestatus=entry(RANDOM(1,3),shipstate).

ASSIGN mylinenum = mylinenum + 1.

END.

All Replies

Posted by Mike Fechner on 09-Nov-2015 04:46

I can offer a 1GB version. 200,000 customers.

Posted by James Palmer on 09-Nov-2015 04:48

Good question Valeriy. Would be nice of Progress to provide better options of sample databases!

Posted by Richard Shulman on 09-Nov-2015 07:28

This code does not grow to a specific size but you can define numcustomers value and it will create random amounts of orders and orderlines for each customer.

DEFINE VARIABLE mylinenum AS INT64 NO-UNDO INIT 0.

DEFINE VARIABLE linemax AS INT64 NO-UNDO INIT 0.

DEFINE VARIABLE itmnum AS INT64 NO-UNDO INIT 0.

DEFINE VARIABLE itmcount AS INT64 NO-UNDO INIT 0.

DEFINE VARIABLE shipstate AS CHARACTER NO-UNDO INIT "Ordered,Back Ordered,Shipped" .

DEFINE VARIABLE numcustomers AS INT64 NO-UNDO INIT 10000.

DEFINE VARIABLE numorders AS INT64 NO-UNDO INIT 6.

DEFINE VARIABLE loop AS INT64 NO-UNDO.

DEFINE VARIABLE nextcustnum AS INT64 NO-UNDO.

DEFINE VARIABLE currordnum AS INT64 NO-UNDO.

DEF VAR list AS CHAR INIT "HXM,DKP,RDR,SLS,JAL,DOS,BBB" NO-UNDO.

DEF VAR counter AS INT NO-UNDO.

DEF VAR counter2 AS INT NO-UNDO.

DEF VAR counter3 AS INT NO-UNDO.

DEF VAR custbuff AS HANDLE NO-UNDO.

DEFINE VARIABLE creditcards AS CHARACTER NO-UNDO INIT "Visa,MasterCard,Discover,American Express".

DEFINE BUFFER custbuff FOR database1.customer.

FIND FIRST custbuff NO-LOCK.

REPEAT loop = 1 TO numcustomers.

nextcustnum = NEXT-VALUE(nextcustnum).

IF counter MOD num-entries(list) = 0 THEN counter2 = counter MOD num-entries(list) + 1.

ELSE IF counter MOD num-entries(list) > 7 THEN counter2 = 7.

ELSE counter2 = counter MOD num-entries(list).

BUFFER-COPY custbuff TO customer ASSIGN custnum = nextcustnum salesrep=ENTRY(counter2,list).

numorders = RANDOM(1,25).

REPEAT counter2 = 1 TO numorders.

currordnum = NEXT-VALUE(nextordnum).

FIND FIRST order WHERE ordernum = currordnum NO-ERROR.

IF NOT(AVAIL(order)) THEN

DO:

CREATE order.

VIEW-AS ALERT-BOX INFO BUTTONS OK.*/

END.

ELSE

NEXT.

ASSIGN ordernum = currordnum

custnum = nextcustnum

orderdate = TODAY

shipdate = TODAY + RANDOM(1,20)

promisedate = TODAY + RANDOM(20,30)

carrier = "Fedex"

PO="PO" + string(RANDOM(1000,17000))

warehousenum = RANDOM(1,14)

creditcard = entry(RANDOM(1,4),creditcards).

linemax = RANDOM(1,5).

mylinenum = 1.

REPEAT counter3 = 1 TO linemax.

CREATE orderline.

VIEW-AS ALERT-BOX INFO BUTTONS OK.*/

ASSIGN ordernum = currordnum

linenum = mylinenum.

itmnum = RANDOM(1,30).

FIND FIRST ITEM WHERE item.itemnum = itmnum.

ASSIGN orderline.itemnum = itmnum

orderline.price = ITEM.price

discount = RANDOM(0,5)

orderlinestatus=entry(RANDOM(1,3),shipstate).

ASSIGN mylinenum = mylinenum + 1.

END.

Posted by Valeriy Bashkatov on 09-Nov-2015 08:38

[quote user="Richard Shulman"]

This code does not grow to a specific size but you can define numcustomers value and it will create random amounts of orders and orderlines for each customer.

DEFINE VARIABLE mylinenum AS INT64 NO-UNDO INIT 0.

DEFINE VARIABLE linemax AS INT64 NO-UNDO INIT 0.

DEFINE VARIABLE itmnum AS INT64 NO-UNDO INIT 0.

DEFINE VARIABLE itmcount AS INT64 NO-UNDO INIT 0.

DEFINE VARIABLE shipstate AS CHARACTER NO-UNDO INIT "Ordered,Back Ordered,Shipped" .

DEFINE VARIABLE numcustomers AS INT64 NO-UNDO INIT 10000.

DEFINE VARIABLE numorders AS INT64 NO-UNDO INIT 6.

DEFINE VARIABLE loop AS INT64 NO-UNDO.

DEFINE VARIABLE nextcustnum AS INT64 NO-UNDO.

DEFINE VARIABLE currordnum AS INT64 NO-UNDO.

DEF VAR list AS CHAR INIT "HXM,DKP,RDR,SLS,JAL,DOS,BBB" NO-UNDO.

DEF VAR counter AS INT NO-UNDO.

DEF VAR counter2 AS INT NO-UNDO.

DEF VAR counter3 AS INT NO-UNDO.

DEF VAR custbuff AS HANDLE NO-UNDO.

DEFINE VARIABLE creditcards AS CHARACTER NO-UNDO INIT "Visa,MasterCard,Discover,American Express".

DEFINE BUFFER custbuff FOR database1.customer.

FIND FIRST custbuff NO-LOCK.

REPEAT loop = 1 TO numcustomers.

nextcustnum = NEXT-VALUE(nextcustnum).

IF counter MOD num-entries(list) = 0 THEN counter2 = counter MOD num-entries(list) + 1.

ELSE IF counter MOD num-entries(list) > 7 THEN counter2 = 7.

ELSE counter2 = counter MOD num-entries(list).

BUFFER-COPY custbuff TO customer ASSIGN custnum = nextcustnum salesrep=ENTRY(counter2,list).

numorders = RANDOM(1,25).

REPEAT counter2 = 1 TO numorders.

currordnum = NEXT-VALUE(nextordnum).

FIND FIRST order WHERE ordernum = currordnum NO-ERROR.

IF NOT(AVAIL(order)) THEN

DO:

CREATE order.

VIEW-AS ALERT-BOX INFO BUTTONS OK.*/

END.

ELSE

NEXT.

ASSIGN ordernum = currordnum

custnum = nextcustnum

orderdate = TODAY

shipdate = TODAY + RANDOM(1,20)

promisedate = TODAY + RANDOM(20,30)

carrier = "Fedex"

PO="PO" + string(RANDOM(1000,17000))

warehousenum = RANDOM(1,14)

creditcard = entry(RANDOM(1,4),creditcards).

linemax = RANDOM(1,5).

mylinenum = 1.

REPEAT counter3 = 1 TO linemax.

CREATE orderline.

VIEW-AS ALERT-BOX INFO BUTTONS OK.*/

ASSIGN ordernum = currordnum

linenum = mylinenum.

itmnum = RANDOM(1,30).

FIND FIRST ITEM WHERE item.itemnum = itmnum.

ASSIGN orderline.itemnum = itmnum

orderline.price = ITEM.price

discount = RANDOM(0,5)

orderlinestatus=entry(RANDOM(1,3),shipstate).

ASSIGN mylinenum = mylinenum + 1.

END.

[/quote]

Thank you very much, Richard!

This thread is closed