Total Noob to Progress - Need help with tools

[TomBascom]

I haven't personally touched EFI but it is a "known application" -- one with a high enough profile that people like me are are aware of it anyhow

The client statement cache can be activated and viewed in PROMON. Go to the R&D menu, option 1 (status), [HASHTAG]#18[/HASHTAG] Client Database-Request Statement Cache ...

Unlike your description of the SQL profiler it does not collect history -- it isn't a "trace" sort of feature.

 

[Mark2457]

Also, if you're going to be using the SQL-92 query engine in the Progress database you should get to know about updating statistics (table, index, and column meta-data).

How to generate SQL-92 script to execute UPDATE STATISTICS for all user tables of a database using 4GL?
http://knowledgebase.progress.com/articles/Article/P115266/p

SQL-92: 'Update Statistics' Explained
http://knowledgebase.progress.com/articles/Article/20992/p

Hints to improve the performance of SQL queries?
http://knowledgebase.progress.com/articles/Article/P117623/p

There are lots of other articles that may be of interest when you search the KB for "UPDATE STATISTICS".

Thanks.

In the Progress database meta-schema, each field has a defined value called MAX-WIDTH (aka SQL WIDTH). This is the maximum length of the data in that field that can be retrieved by a SQL client. This value can be modified.

There is a command-line tool called dbtool that can be run against a database to read field values, find their widths, and set MAX-WIDTH values appropriate for the data so you don't get SQL client errors. Search for dbtool in the KB.

What is DBTOOL?
http://knowledgebase.progress.com/articles/Article/P24496/p

Thanks a lot!

 

[Mark2457]

OK, so I'm trying to enable statement logging and I found this:

Steps to enable the SQL statement logging:

1. Start the database.
2. Connect with any JDBC or ODBC tool.
3. Run the following statement to enable SQL statement logging:

SET PRO_SERVER LOG ON with (statement);

4. Run the application.

Up until now, I've been using ODBC and a linked server in SQL to execute my queries. Works OK (other than the overstuffing issue)

However, I can't run SET PRO_SERVER LOG ON against a linked server. I guess I need some kind of IDE to connect and run these commands. Tried googling it, but didn't find anything useful. Anyone got any suggestions?

Thanks

Mark

 

[Cringer]

Are you running a Windows Server? I'm assuming yes. In order to enable statement logging you need to run PROMON on the database. The easiest way to do that is to run Proenv from the menus. Give the first command as PROMPT, so you can see where you are. Navigate using DOS commands to the physical location of the database and then type PROMON <dbname>. Then follow Tom's instructions above to get to the statement logging menu.

 

[Rob Fitzpatrick]

I guess I need some kind of IDE to connect and run these commands. Tried googling it, but didn't find anything useful. Anyone got any suggestions?

OpenEdge includes a basic command-line SQL client called SQL Explorer (sqlexp). Search for info on that in the SQL Reference or SQL Development manual; I can't remember which at the moment.

Syntax will be something like:
sqlexp dbname -H <hostname> -S <port no. or service> -user <sql_user> -pass <sql_user's password>

If you connect and authenticate you can enter SQL commands and get output.

[root @ db-sandbox] /u/db/sports/ [36](1): sqlexp sports -H localhost -S 12345 -user foo -pass bar
OpenEdge Release 10.2B08 as of Tue Nov 12 19:07:41 EST 2013
Connecting user "foo" to URL "jdbc:datadirect:openedge://localhost:12345;databaseName=sports"... (8920)
SQLExplorer>SET PRO_SERVER LOG ON with (statement);
SQLExplorer>exit

 

[Mark2457]

Are you running a Windows Server? I'm assuming yes. In order to enable statement logging you need to run PROMON on the database. The easiest way to do that is to run Proenv from the menus. Give the first command as PROMPT, so you can see where you are. Navigate using DOS commands to the physical location of the database and then type PROMON <dbname>. Then follow Tom's instructions above to get to the statement logging menu.

Thanks Cringer

Tried it , but getting error:

D:\Hsi\data>promon gams1
OpenEdge Release 10.2B06 as of Mon Mar 19 19:14:30 EDT 2012
Unable to open or create gams1.lg, error 13. (2257)
dbDelUsrctl: SemLockLog ret = -1

any ideas?

TIA

Mark

 

[Rob Fitzpatrick]

The number in brackets (2257) is a Progress message number. You can look up messages in the ProKB application I linked to, or in the KB, or in a Progress client (Help | Messages):

┌──────────────────────────────────Messages───────────────────────────────────┐
│                                                                             │
│ Message Number: 2257                                                        │
│                                                                             │
│ ┌─────────────────────────────────────────────────────────────────────────┐ │
│ │Unable to open or create <filename>, error <number>. (2257)                │
│ │                                                                           │
│ │PROGRESS was unable to create the temporary file <filename>. The reason    │
│ │for the error is indicated by the error <number>. The most common          │
│ │problems are:   2 - A directory in the path does not exist.  13 - You do   │
│ │not have write permission in the directory.  24 - You have too many open   │
│ │files.  28 - The disk is full Other error numbers can be found in          │
│ │/usr/include/errno.h.                                                      │
│ │D                                                                          │
│ └─────────────────────────────────────────────────────────────────────────┘ │
│                                                                             │
│ <OK>   < View Message >                                                     │
└─────────────────────────────────────────────────────────────────────────────┘

Does your user account have write permission in the database directory?

 

[Mark2457]

The number in brackets (2257) is a Progress message number. You can look up messages in the ProKB application I linked to, or in the KB, or in a Progress client (Help | Messages):

┌──────────────────────────────────Messages───────────────────────────────────┐
│                                                                             │
│ Message Number: 2257                                                        │
│                                                                             │
│ ┌─────────────────────────────────────────────────────────────────────────┐ │
│ │Unable to open or create <filename>, error <number>. (2257)                │
│ │                                                                           │
│ │PROGRESS was unable to create the temporary file <filename>. The reason    │
│ │for the error is indicated by the error <number>. The most common          │
│ │problems are:   2 - A directory in the path does not exist.  13 - You do   │
│ │not have write permission in the directory.  24 - You have too many open   │
│ │files.  28 - The disk is full Other error numbers can be found in          │
│ │/usr/include/errno.h.                                                      │
│ │D                                                                          │
│ └─────────────────────────────────────────────────────────────────────────┘ │
│                                                                             │
│ <OK>   < View Message >                                                     │
└─────────────────────────────────────────────────────────────────────────────┘

Does your user account have write permission in the database directory?

I'm on windows and am a domain admin. I created a file in the same dir OK

 

[TheMadDBA]

You are running this on the actual DB server right? It requires a shared memory connection and cannot be run remotely.

Also check the security group policies on all of the database files, make sure there isn't anything funny looking there.

 

[Mark2457]

You are running this on the actual DB server right? It requires a shared memory connection and cannot be run remotely.

Also check the security group policies on all of the database files, make sure there isn't anything funny looking there.

Yes, I'm on the DB server.

 

[Mark2457]

Anyone know a resource in Chicago area where I could buy a day's training on the basics?

 

[Cringer]

Tom Bascom has a suitcase and will travel...

 

[TomBascom]

SQL logging will only log SQL queries. It gives you zero insight into what the 4gl side is doing.

 

[Mark2457]

SQL logging will only log SQL queries. It gives you zero insight into what the 4gl side is doing.

Thanks Tom. I've been working with a Linked server (in MS SQL) for now to find the data I want. It works, but obviously doesn't have access to statistics so it's slow.

 

[Rob Fitzpatrick]

What do you need to do with the data? If you just want to browse data you could connect a SQL client directly to the database, which should perform well. You could use Squirrel, probably most any BI tool, or even Excel.

 

[Mark2457]

What do you need to do with the data? If you just want to browse data you could connect a SQL client directly to the database, which should perform well. You could use Squirrel, probably most any BI tool, or even Excel.

Ultimately, it will be sent to Great Plains (hosted on MS SQL Server). We normally use Scribe Insight: we write stored procedures in SQL server for the source queries.

 

[TheMadDBA]

Another alternative would be to purchase the MSSQL Dataserver for Progress. It allows you to connect directly from the 4GL to MSSQL. You can run stored procedures, send across raw SQL queries or query/update the SQL tables directly from the 4GL. I just got through setting up a near real time feed from a Progress database to a SQL database.

Training would be a good idea since the Progress system is going to be around for a while (probably much longer than they think) and somebody needs to know how to keep it running. Hopefully you have a test/dev system you can play around with? If not... get one even if it is smaller than production.

 

[Mark2457]

Another alternative would be to purchase the MSSQL Dataserver for Progress. It allows you to connect directly from the 4GL to MSSQL. You can run stored procedures, send across raw SQL queries or query/update the SQL tables directly from the 4GL. I just got through setting up a near real time feed from a Progress database to a SQL database.

Training would be a good idea since the Progress system is going to be around for a while (probably much longer than they think) and somebody needs to know how to keep it running. Hopefully you have a test/dev system you can play around with? If not... get one even if it is smaller than production.

Thanks. Was just looking for our ODBC license and came across a license for MSSQL Data Server, so it seems we have it. How big of a learning curve is 4GL if you know SQL well (just need to write a handful of Stored Procedures to query 5 or 6 related tables in each?

Regards

Mark

 

[Cringer]

It's a massive learning curve. First of all there's no stored procedures... There are some other quite high level differences to get your head round too. Progress queries are record based (in that you write a query that iterates round and returns you a record at a time), SQL is more record set oriented. So there's that to come to terms with. And then you've got that the whole syntax is very different, and how to join is different too.
In a very basic case though
FOR EACH <table> WHERE <whereclause>:
END.
Should be pretty self-explanatory.

 

[Mark2457]

It's a massive learning curve. First of all there's no stored procedures... There are some other quite high level differences to get your head round too. Progress queries are record based (in that you write a query that iterates round and returns you a record at a time), SQL is more record set oriented. So there's that to come to terms with. And then you've got that the whole syntax is very different, and how to join is different too.
In a very basic case though
FOR EACH <table> WHERE <whereclause>:
END.
Should be pretty self-explanatory.

Hmmm... and the hits just keep on coming