ODBC Connection Problems.

[PJan8724]

Hey all,

We have a problem with out reports taking much longer then they should to run. So the plan is to get rid of the reports and replace then with SQL Views instead and present the views to the users. Anyway I'm trying to set up an ODBC Connection to the our OpenEdge 10.0 database that will allow us to create the views we want to create.

I've set up an ODBC connection to the database but I'm getting an error with the following progress code (7512). I've looked up the error and it say the following for a fix:

"1) Open the SQL Explorer and connect as 'sysprogress' (no password)
2) Grant DBA privilege to the desired user:

GRANT DBA TO 'user';
COMMIT;
The drawback is that anybody can use this user account (or sysprogress) in order to delete/modify any SQL92 objects (tables, indexes, ...) or modify the SQL92 privileges (creating new DBA user with password)."


Following the steps listed above, I'm told that I can't connect to the database using the Administrator account in SQL Explorer because my username and password as incorrect. I'm using the correct username and password for the database, since its the same username and password that I use to start, stop, backup, whatever for the database. Can anyone help me figure out what I'm doing wrong trying to get the ODBC Connection working properly. I'm some what of a NEWBIE when it comes to Progress/OpenEdge 10.0b05. I mean in SQL Server I could have the ODBC Connection, Users and Rights assigned in about 5 mins. But here it just seems like I'm running in circles and not getting anywhere quick. Any help that you can provide would be greatly apprecaited.

Thanks,

Pete J.

 

[tamhas]

To answer your question fully, look up solution 20143 in the knowledge base.

The short version, which might help get you over the hump, is that if your database has _user records, then you must supply a valid user name and matching password to connect *and* that user has to be granted permission. If you don't have _user records in the database, you must use the user id of the user that created the database and *any* password.

But, perhaps you should back up a minute. While it is true that the SQL query engine has some advantages over the ABL query engine (grrrrr) such as the ability to read tables without an index and a query optimizer, it is unlikely that SQL will provide dramatic improvements over *properly written" ABL. And, of course, it is at least as easy to write bad SQL and suffer performance issues as well.

Have you done a COMPILE XREF of the offending code to see what indexes are being used? Is it doing full table scans? Is that necessary?

Or, is it even the code or is it just a question of needing to tune the database? If that is the problem, going to SQL won't help anything.

Now, I happen to approve of the use of SQL and third party reporting products as a way to do reports ... well, Actuate at least ... because I think it allows one to create a higher quality report than can be easily built in ABL. But, I would hate to have you spend a huge effort to try to move to SQL and then find out that isn't the problem after all.

 

[PJan8724]

Tamhas,

Thanks for the info. I'm reading solution 20143 right now. However my database does have a _user table. I've logged into SQL Explorer as myself in order to Grant the user in question rights to query the table that we want to create the view on. The problem is that I'm getting an access denied message when trying to grant the select rights on the table. I tried logging in with the sysprogress user but I get the following error message: java.lang.NoClassDefFoundError

Since my database has a _user table, does this mean that I have to grant the select rights through the application that is tied to the database and not from the SQL Explorer tool?

As for why we want to use SQL query engine instead of ABL query engine, I'm not sure really this is coming down from development. And since I don't know Progress all that well I don't know anything about the query engine. However I do know that the reports in question that are running slow are all written in the 4GL language (again something I don't know). Anyway, like I mentioned above, is it possible that I have to grant the rights to select from with in the application and not from the SQL Explorer tool?

Thanks,

Pete J.

 

[tamhas]

It has been a while since I set up a new database for SQL, but my memory is that I created user records for sysprogress and another generic report user, let's call it "reporter". Then, you can log in using sysprogress to make your changes and grant permissions to reporter. Then, the reports call all log in as reporter.

Is this a large number of reports which all have problems or just a few that are slow. If the latter, then the ABL code is suspect and the programmers should be doing COMPILE XREF to see what indices are being used and posting code here or on PEG to get ideas on how to make it perform better. If it is a lot of reports, then it is more likely the database needs tuning and your DBA should post startup parameters and PROMON stats for advice.

 

[PJan8724]

Tamhas,

I am the DBA, lol.... I just don't have very much experience with Progress in general, SQL Server is my forte. Some time back in late 2005 the company had a Progress Consultant come out and tune the database in order to increase performance and all the reports were running just fine. We recently upgraded our entire database from Progress version 9.1c to OpenEdge 10.0b05 and I'm using the same start-up parameters that we used from our 9.1c database except that I've transfered them over to the Progress Explorer Tool and I'm running the database from that.

Since we upgraded the database the reports are getting progressively slower and slower. When we did initial testing on OE 10 with limited users on the database, all the reports ran faster then they did in our then 9.1c production environment. But once the database went into production, all the reports started running slower until then started running slower then under our old 9.1c environment.

I don't have access to the code for the reports, however I've posted the start-up parameters and the Promon stats below.

Would you be able to take a look at them and let me know what you think?

One thing I can tell you about our database is that the entire database resides in the Default Schema-Area, I wanted to move the table to other schema areas using a dump and load but when I did my initial test moves the dump and load process was taking close to 16 hours for a 23GB database. I told senior management that leaving the database in the default schema-area was not best practices and they told me that the 16 hours of downtime I needed in order to complete the dump and load was not possible since the user couldn't be with out the application for that long. I was given just enough time to backup the database move the backup from the old server to the new server and restore it as a OE10.0 DB.

Anyway would you be able to take a look at the start-up parameters I have below and let me know what you think?

Start-up Parameters taking from the PET.

DefaultConfiguration General Screen
# Blocks - 0
# hash - 0
# lock - 8192
Max users - 100
Max Servers - 5
Other - -S unipc10 -N TCP -L 16000 -B 180000 -Mm 8192 -spin 24000 -bibufs 10 -aibufs 10

Background Writers are set.

Advnced Screen

Spin Locks - 24000
Page Writer Queue delay - 100
queue min - 1
scan - 1
scan delay - 1
max buffers - 25
Nap int - 1
nap max - 1

DefaultServerGroup

service name - unipc10
num servers - 10
message buff - 1024
reporting int - 1

max clients - 10
min clients - 5

max dyn port 5000
min dyn port 3000

Thanks again,

Pete J.

 

[tamhas]

Your image didn't work. You should actually post this info to the DBA forum to get the right attention. You should also include the database block size and a little something about the box. Oh, and maybe the structure file.

Is this an application where you do a lot of adds and deletes? That progressive slowness sounds like it could be from the database getting fragmented. Have you considered a dump and load of individual tables rather than the whole thing?

There have also been a number of discussions on PEG about various ways to speed this up such as parallel processes, direct DB to DB processes, etc. If you post either here on the DB forum or on dba@peg.com, I'm sure that you will get a number of recommendations.

The other reason for progressive slowness would be tables with historical data which is just getting added to and added to and the report is doing a full table scan. Then it is a question of not using the indexing effectively.