OpenEdge12.0 server side join

[Jelbin]

Hi Team,

I am trying to execute the new functionality of progress 12(server side join). I installed Openedge 12.0 trail version in my system.
I enabled the qrylog and executed my script in the procedure editor . Updated the DB start up script also.
But the qrylog file does not contain any information about server side join.(In the log file, Server-side join appears next to the query type for server-side join queries:
Type: FOR Statement, Server-side join). I cannot able to see this information in my log file.

Can anyone please let me know weather server side join will enable in the Openedge 12.0 trail version. If its works with trail version, can you please guide me where I am wrong in my execution.

Regards,
Jelbin

 

[TomBascom]

What code did you run which you expected would use server-side joins?

 

[Jelbin]

Hi Tom,

Below is the code .

etime(yes).
OUTPUT TO "custout.prn".
for each customer no-lock,
each order no-lock
where order.custnum = customer.custnum,
each orderline no-lock
where orderline.ordernum = order.ordernum:
put customer.custnum format ">>>>>9" customer.name skip.
end.
OUTPUT CLOSE.
display etime.

Below is the Parameter added in the DB Startup script.
-threadedServer 1
-ssj 1
-clientlog Clientlog.log
-logentrytypes QryInfo
-logginglevel 3

Regrds,
Jelbin

 

[TomBascom]

For what it is worth -- I tried it with a normal oe12 install (not classroom edition) and a shared memory connection, and got very similar results.

One difference is that in my sports2000 db there are 1117 customer records rather than the 49011 that your sports2000 seems to have.

The other big difference that I see is that, in my case, the "DB Reads" data is available whereas in your case it says "UNAVAILABLE".

I also noticed that your .pf file mixes client and server parameters. So I shutdown the db and tried the query again but in single-user mode. When I run it that way I also get "UNAVAILABLE" so I am thinking that you do not have a db server running. In which case server side joins obviously are not going to happen.

Restarting my server, with -S this time, running the client and connecting with -S results in:

[19/08/21@10:30:01.294-0400] P-007723 T-007723 1 4GL -- Logging level set to = 3
[19/08/21@10:30:01.294-0400] P-007723 T-007723 1 4GL -- Log entry types activated: QryInfo
[19/08/21@10:30:01.298-0400] P-007723 T-007723 2 4GL QRYINFO        Query Plan:  ./join.p line 5
[19/08/21@10:30:01.298-0400] P-007723 T-007723 2 4GL QRYINFO        QueryId: 139996926681416
[19/08/21@10:30:01.299-0400] P-007723 T-007723 2 4GL QRYINFO        Type: FOR Statement, Server-side join
[19/08/21@10:30:01.299-0400] P-007723 T-007723 2 4GL QRYINFO        Client Sort: N
[19/08/21@10:30:01.299-0400] P-007723 T-007723 2 4GL QRYINFO        Scrolling: N
[19/08/21@10:30:01.299-0400] P-007723 T-007723 2 4GL QRYINFO        Table: s2k.Customer
[19/08/21@10:30:01.299-0400] P-007723 T-007723 2 4GL QRYINFO          Indexes: CustNum
[19/08/21@10:30:01.299-0400] P-007723 T-007723 2 4GL QRYINFO        Table: s2k.Order    
[19/08/21@10:30:01.299-0400] P-007723 T-007723 2 4GL QRYINFO          Indexes: CustOrder
[19/08/21@10:30:01.299-0400] P-007723 T-007723 2 4GL QRYINFO        Table: s2k.OrderLine
[19/08/21@10:30:01.299-0400] P-007723 T-007723 2 4GL QRYINFO          Indexes: orderline
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO        Query Statistics:  ./join.p line 5
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO        QueryId: 139996926681416
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO        DB Blocks accessed:
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         s2k : 48431
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO        DB Reads:
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         Table: s2k.Customer : 1117
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         Index: Customer.CustNum : 1118
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         Table: s2k.Order : 2836
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         Index: Order.CustOrder : 3953
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         Table: s2k.OrderLine : 0
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         Index: OrderLine.orderline : 0
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO        s2k.Customer Table:
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         4GL Records: 1117
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         Records from server: 1117
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO          Useful: 1117
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO          Failed: 0
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         Select By Client: N
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO        s2k.Order Table:
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         4GL Records: 3953  
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         Records from server: 3953
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO          Useful: 3953
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO          Failed: 0
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         Select By Client: N
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO        s2k.OrderLine Table:
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         4GL Records: 13970 
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         Records from server: 13970
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO          Useful: 13970
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO          Failed: 0
[19/08/21@10:30:06.241-0400] P-007723 T-007723 2 4GL QRYINFO         Select By Client: N

 

[Jelbin]

Hi Tom,

Thanks for your reply. Yes, you are correct. My DB is running in locally not in Server.
Will it work in class room edition.?

Regards,
Jelbin.

 

[Rob Fitzpatrick]

What is your full client command line? Does it include -S?

 

[TomBascom]

I see that the "personal database" is what is supported so I suspect that the "classroom edition" does not support running a server.

But it is not a product that I have ever worked with so I could be wrong.

 

[Rob Fitzpatrick]

I see that the "personal database" is what is supported so I suspect that the "classroom edition" does not support running a server.

But it is not a product that I have ever worked with so I could be wrong.

The personal database (e.g. the RDBMS product you have if you only install a development product like PASOE or 4GL Dev System) does support servers and TCP clients, provided they connect from localhost.

 

[Rob Fitzpatrick]

In this case you will see BROKER 0: (4393) This server is licensed for local logins only. in the log file.

 

[TomBascom]

Does that mean shared memory only?

 

[Rob Fitzpatrick]

Does that mean shared memory only?

No. It means shared memory or non-remote (localhost) TCP.

 

[TomBascom]

Ok, so starting a server and seeing server side joins should be possible.

 

[Rob Fitzpatrick]

I asked about -S because when I tested this in 12.0, in my client log I saw Type: FOR Statement, as in Jelbin's client log, when connected via shared memory. I only saw Type: FOR Statement, Server-side join when using -S.

 

[TomBascom]

Unless it is an "enterprise only" type of feature.

 

[TomBascom]

Yes, exactly. That is also what I saw.

 

[TomBascom]

His provided startup parameters do not list -S and he has said that he did not start a server. So that's problem #1.

 

[Jelbin]

Ho Tom and Rob,
Thanks for you valuable suggestions . I am wrong in multiple directions.
Rob, can you please share me the sample start up script which you used with -S parameter(connected via shared memory).
I never used that in my script. In-fact I doesn't know how to use that.

Regards,
Jelbin.

 

[Rob Fitzpatrick]

Rob, can you please share me the sample start up script which you used with -S parameter(connected via shared memory).
I never used that in my script. In-fact I doesn't know how to use that.

There are two aspects that you need to change: how the database is opened (via proserve command) and how the client is connected to the database.

First, let's look at your parameter list:

-threadedServer 1
-ssj 1
-clientlog Clientlog.log
-logentrytypes QryInfo
-logginglevel 3

This is incorrect as you have a mix of database startup parameters (-threadedServer and -ssj) and client startup parameters (the other three). You want to have two separate commands: one to start the database in multi-user mode, i.e. with a broker, and one to start a client and connect it to that database.

A correction: an ABL client can connect to a database in one of two ways: via shared memory (without -S) or via a TCP network connection (with -S). When a client specifies -S <port number/service name> in its startup parameters, then the database must have been opened already in multi-user mode (proserve) and it must also specify -S in its startup parameters, with the same value as the client.

From your client log, it seems your database is sports2000 located in C:\Users\751190\Desktop\Test; I'll assume that to be the case.

Do the following from a proenv session (Start menu | Progress | proenv):
(Note: this is not a script! Do not type the comments. Type the three commands.)

// change to the database directory
cd /d C:\Users\751190\Desktop\Test

// start a broker to open the database in multi-user mode
// note that threadedServer and SSJ are enabled by default
// it will listen for client connections on TCP port 12345
proserve sports2000 -S 12345

// start a client, connect it to a database called sports2000
// that is listening on TCP port 12345 on localhost
mpro sports2000 -H localhost -S 12345 -clientlog Clientlog.log -logentrytypes QryInfo -logginglevel 3

// paste your code into the editor and run it

 

[Rob Fitzpatrick]

Unless it is an "enterprise only" type of feature.

I tested this in AEE. I don't have a non-Enterprise 12.x environment at my disposal at the moment, but in the conference materials and the docs there is no mention of SSJ or threadedServer being Enterprise-only.

 

[Jelbin]

There are two aspects that you need to change: how the database is opened (via proserve command) and how the client is connected to the database.

First, let's look at your parameter list:


This is incorrect as you have a mix of database startup parameters (-threadedServer and -ssj) and client startup parameters (the other three). You want to have two separate commands: one to start the database in multi-user mode, i.e. with a broker, and one to start a client and connect it to that database.

A correction: an ABL client can connect to a database in one of two ways: via shared memory (without -S) or via a TCP network connection (with -S). When a client specifies -S <port number/service name> in its startup parameters, then the database must have been opened already in multi-user mode (proserve) and it must also specify -S in its startup parameters, with the same value as the client.

From your client log, it seems your database is sports2000 located in C:\Users\751190\Desktop\Test; I'll assume that to be the case.

Do the following from a proenv session (Start menu | Progress | proenv):
(Note: this is not a script! Do not type the comments. Type the three commands.)

// change to the database directory
cd /d C:\Users\751190\Desktop\Test

// start a broker to open the database in multi-user mode
// note that threadedServer and SSJ are enabled by default
// it will listen for client connections on TCP port 12345
proserve sports2000 -S 12345

// start a client, connect it to a database called sports2000
// that is listening on TCP port 12345 on localhost
mpro sports2000 -H localhost -S 12345 -clientlog Clientlog.log -logentrytypes QryInfo -logginglevel 3

// paste your code into the editor and run it

There are two aspects that you need to change: how the database is opened (via proserve command) and how the client is connected to the database.

First, let's look at your parameter list:


This is incorrect as you have a mix of database startup parameters (-threadedServer and -ssj) and client startup parameters (the other three). You want to have two separate commands: one to start the database in multi-user mode, i.e. with a broker, and one to start a client and connect it to that database.

A correction: an ABL client can connect to a database in one of two ways: via shared memory (without -S) or via a TCP network connection (with -S). When a client specifies -S <port number/service name> in its startup parameters, then the database must have been opened already in multi-user mode (proserve) and it must also specify -S in its startup parameters, with the same value as the client.

From your client log, it seems your database is sports2000 located in C:\Users\751190\Desktop\Test; I'll assume that to be the case.

Do the following from a proenv session (Start menu | Progress | proenv):
(Note: this is not a script! Do not type the comments. Type the three commands.)

// change to the database directory
cd /d C:\Users\751190\Desktop\Test

// start a broker to open the database in multi-user mode
// note that threadedServer and SSJ are enabled by default
// it will listen for client connections on TCP port 12345
proserve sports2000 -S 12345

// start a client, connect it to a database called sports2000
// that is listening on TCP port 12345 on localhost
mpro sports2000 -H localhost -S 12345 -clientlog Clientlog.log -logentrytypes QryInfo -logginglevel 3

// paste your code into the editor and run it

Thanks Rob, This is great.Let me test the same and I will let you know my result.
Thanks again.

Regards,
jelbin.