Product Roadmap for SQLBase

forum.wishlist (1998-2005) & forum.gupta.wishlist (2005-2010)
David Drager

Product Roadmap for SQLBase

Post by David Drager » 10 Sep 2007, 23:28

 Posted by:  David Drager 

We are now working on the Product Roadmap for SQLBase. Some core features
for the future versions of SQLBase have been identified. But your feedback
may help us identify other features. In particular, I am interested in the
following issues (but feel free to give me your thoughts on your own
issues):

1. What is the maximum number of users you support per server? Do you
have a requirement to support more users? If so how many? Are there any
features you need to help you support these users?

2. Do you support a number of SQLBase installations? What features
would help you install, maintain & support SQLBase in the field?

If you would like to discuss the future of Team Developer or SQLBase with
me, please send me a private email.

David Drager
VP Product Management
Unify Corporation

Tony

Product Roadmap for SQLBase

Post by Tony » 11 Sep 2007, 11:24

 Posted by:  Tony 

1. A free version of SQLBase!

Mirko
Italy
Posts: 983
Joined: 04 Apr 2017, 08:56
Location: Geneva

Product Roadmap for SQLBase

Post by Mirko » 11 Sep 2007, 11:48

 Posted by:  Mirko BONANNO 

>

* More details with SqlErrors messages ; for example the offending column
name for an unmached foreign key

* Profile is what I miss since the beginning ; The possibility to define
rights for a profile (user group) and then include/exclude a user (with one
or more profiles). Of course updating a profile would update all users
related to that profile :-).

* The possibility of granting rights with extended conditions !
For example :

Grant select on TABLENAME to USERNAME where COLNAME between X and Y ;
or
Grant select on TABLENAME to USERNAME where MyStoredProcedureName( COLNAME,
COLNAME) ;

HTH
Mirko

HL

Product Roadmap for SQLBase

Post by HL » 11 Sep 2007, 12:18

 Posted by:  HL 


Did you already consider to use a view for this?

create view VIEWNAME ( ... ) as select ... from TABLENAME where COLNAME
between X and Y;
grant select on VIEWNAME to USERNAME;

Regards
HL

Mirko
Italy
Posts: 983
Joined: 04 Apr 2017, 08:56
Location: Geneva

Product Roadmap for SQLBase

Post by Mirko » 11 Sep 2007, 14:20

 Posted by:  Mirko BONANNO 

Yes I did consider that !

But doing so you have to query the right view depending on the user ! I was
thinking (dreaming) about a single (same) query returning a different
result-set depending on the user who is executing it. Of course it should be
the same for an update/insert/delete statement ; I'm granted to update that
row but you are not and we are both executing the very same statement :
Update mytable where rowid = :sROWID

ciao
Mirko

FRBhote
India
Posts: 1230
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Product Roadmap for SQLBase

Post by FRBhote » 11 Sep 2007, 15:47

 Posted by:  F R Bhote 

The biggest help would be improving concurrency - I have an instllation with
about 75 users, and I am attaching today's (they have been complaining
excessively today) audit. Amazing number of timeouts. Most of them are
caused by a select causing a lock, and most others are an insert or update
of a single row.

We support about 400 installations, and find that the easiest way is to
unload the database and get it from the remote location here and analyse
it - nothing more than that that can be done.

It's mainly the 702/703 which is an irritant and difficult to explain to the
customer. I'll attach a sample FAIL.SQL in the next mail because it is very
big and you can get the idea.

F R Bhote

Attachment: ABC7.zip

Rainer Ebert

Product Roadmap for SQLBase

Post by Rainer Ebert » 11 Sep 2007, 15:58

 Posted by:  Rainer Ebert 

Most important for us would be performance (bad index usage) and improved
concurrency (deadlocks, locks, ...). We have a large database (~ 20GB) and
up to 25 users (growing) and a 7*24h TD service applications working on the
database (9.0.1PTF2 TE). Many times a day we get deadlocks on selects which
are a join between 2 tables or contain a union. The join takes 2 seconds to
prepare, but often it fails because of a deadlock.

And I would like to see all bugs fixed. Especially the bug, where SQLBase
often stops processing if running as service on a multiprocessor or
hyperthreading system with interaction with desktop disabled (without
visible SQLBase service window).

And a possibility to define a user with the same rights as SYSADM.

And a possibility to tell SQLBase which index(es) to use for a specific
query.

Speed up check database and update statistics.

Rainer

David Drager

Product Roadmap for SQLBase

Post by David Drager » 11 Sep 2007, 20:17

 Posted by:  David Drager 

B.R. thanks for your input. Some questions for you:

About Concurrency:

1. What isolation levels are you using?

2. How do you generate ID #'s?

3. What scenarios do you have most of your lockings issues:

i.e. Reporting against a Transactional Database,

4. If you try TD 11, I would be interested in finding out its impact on
your concurrency issues.

About your 400 installations:

1. How many of these are multi-user servers?

2. What remote management functions would be most useful to you?

702/703 Problem:

1. Do you have a case number or any other code I could use to look this
up?

Regards,

David Drager

Jim McNamara

Product Roadmap for SQLBase

Post by Jim McNamara » 11 Sep 2007, 21:50

 Posted by:  Jim McNamara 

Hi Dave - wanted to throw in my 2 cents on the locking issue because we have
been forced to move to Oracle for the production database for our largest
operation. We still use SQLBase for some smaller applications and the
migration to Oracle won't be completed until next month.

Our principle business has 170 users on a 50 location WAN. Performance
through version 8.5 forced us to divide the database among 4 servers with
12-14 locations with a max of 40 users on each server.

Biggest over all problem > users abnormally ending their app or losing their
internet connection while posting a transaction left locks on the database
that usually result in having to shut down and restart the service as
SQLConsole is not much help for finding and killing the offending process.
Not much fun at 5:30 Sat afternoon when you're trying to finish up a round
of golf.

Biggest performance problem > End of day processing when users are selecting
their aggregate business for day end accounting processing comes to a halt
if more that a half dozen stores start their closing routine at roughly the
same time. I guess you'd call that reporting against a transactional
database.

SQLBase served us very well for many years - until we grew from 12 stores
with 20 users in 1998 to 25 stores with 50 users in 2003 when we had to
start dividing out the processing. When we got to 40 stores and 120 users
and had divided the database into 3 fragments (2 years ago) we had a tough
decision to make. Fragmenting the databases was creating more problems than
it was solving.

Thanks and Good Luck,

Jim McNamara

aspurga
Lithuania
Posts: 37
Joined: 06 Mar 2017, 08:54
Location: Lithuania

Product Roadmap for SQLBase

Post by aspurga » 12 Sep 2007, 01:59

 Posted by:  Arunas 

The full text indexing engine.

Arunas Spurga

FRBhote
India
Posts: 1230
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Product Roadmap for SQLBase

Post by FRBhote » 12 Sep 2007, 07:58

 Posted by:  F R Bhote 

The isolation level is always RL. It's all over the audit.
---------------
To generate IDs, we use the method described by Lalwani:

!!CB!! 174
Set sSelectId = 'SELECT S_ID_NO INTO :nNo FROM SYSADM.S_ID WHERE S_ID_DATE
= :dfDate'
Set sUpdateId = 'UPDATE SYSADM.S_ID SET S_ID_NO = S_ID_NO + 1 WHERE
S_ID_DATE = :dfDate'

!!CB!! 129
If NOT ( SqlExecute( hSqlUpdId ) AND fnExists( hSqlSelId ) AND SqlExecute(
hSql ) )
Call SqlPrepareAndExecute( hSqlUpdId, sgRollback )
Call ErrorBox( sgSelIdErrMess )
Return FALSE

where hSql is the Insert statement.

CREATE TABLE SYSADM.S_ID (
S_ID_DATE DATE,
S_ID_NO DOUBLE PRECISION,
S_ID_DUP SMALLINT,
S_ID_FIRM SMALLINT)
PCTFREE 99
/
CREATE INDEX SYSADM.XS_ID_DATE ON SYSADM.S_ID(
S_ID_DATE)
PCTFREE 10
/
I wonder if that index PCTFREE should be 99...
--------------

I would love to try SB 11, but after the (bad) experience I had with 9.0.1
PTF2, (the audit file was about 10 times the size) I would really like to
know if it is backward compatible without an unload. The database is up 24x7
and it takes about 5 hours to load. I'm still looking for a customer to flog
the unlimited user license to .
---------------

About 100 of our installations are multi-user and as Jim says, the End Task
by users is a major problem. We have given some of our users the dbconnects
program from the newsgroup to kill user connexions, but it's quite a
guessing game.

----------------
(While I type this, I have this on Instant Messenger:
raneventura1: hi
raneventura1: gm
frbhyper: hi
raneventura1: hyper not working properly
raneventura1: very slow
raneventura1: have you checked y-day abc file ?)
----------------
Regarding the scenarios, let's take one from the audit I posted:
One guy is doing this:
008,11-SEP-07 12:51:45.14,LOCK
HOLDER,VSL07,HARSHALA,Win32User,RL,300,2805742,1402,Temp X-Lock,
INSERT INTO SYSADM.PARTY( P_CODE, P_NAME, P_DOB, P_ADDR1, P_ADDR2, P_ADDR3,
P_ADDR4,......P_CAT,.....
and another wants to select:
008,11-SEP-07 12:51:45.14,LOCK
HOLDER,VSL07,MALEKAR,Win32User,RL,300,2805742,1429,Temp S-Lock,
SELECT P_CODE FROM SYSADM.PARTY WHERE P_CAT = 'DZ'
the result of which is:
P_CODE
===============
BSE
NSE
2 ROWS SELECTED
IN 0.02 SECONDS
--------------------
Another scenario:
008,11-SEP-07 14:04:30.15,LOCK
HOLDER,VSL07,AMISH2,Win32User,RL,300,6899917,332506,X-Lock,
INSERT INTO SYSADM.SAUDA
(S_DATE, S_NO, ...
where S_DATE is today's and S_NO goes from 1 upto something like 100,000.
The other guy is doing:
008,11-SEP-07 14:04:30.15,LOCK
HOLDER,VSL07,SANJAY1,Win32User,RL,300,6899917,38455,Temp S-Lock,
SELECT ... SUM( @DECODE( S_BILL_TYPE, 'd', S_RATE * S_QTY ) ), ...
FROM SYSADM.PARTY, SYSADM.SAUDA, ...
WHERE .... AND S_DATE BETWEEN :1 AND :2 AND...
where S_DATE is yesterday's or the day before's.
------------
Regarding the 702/703, I've sent you a mail with a FAIL.SQL which is 3 MB
and too large to post on this group.
Case 927844 - Error 702 problems
------------
While we are at it, there is another case regarding the ODBC driver "Case
927275 - Win CE And ODBC" which I do not think is solved as yet.
------------
We all out here are very fond of SqlBase and don't see anyone having issues
on the management side. If you notice, all these years there have been 3
main things which everyone wants:
1. Concurrency (row-level locking),
2. Multi-processor support, and
3. Replication.

Regards,

F R Bhote

PS: SB8.5.0 PTF 4

Didier Tourneur

Product Roadmap for SQLBase

Post by Didier Tourneur » 12 Sep 2007, 08:32

 Posted by:  Didier Tourneur 

A better execution plan,not parsed with spaces, tab .
but like excel.
And more explcation about the plan.
didier.

Yoda

Product Roadmap for SQLBase

Post by Yoda » 12 Sep 2007, 18:34

 Posted by:  Yoda 

I wonder if that index PCTFREE should be 99...>>>>

If you set it to 99%, your Inserts will slow down.
However, i have noticed 15%-20% free space often means faster inserts
and updates as long as you are using the primary key. There is an
IceTeaGroup article i wrote long back that deals with this.

However, there is one thing you can do which will (almost) certainly
help you. I assume that S_ID table is a small table with a few rows
right? In that case, set the PCTFREE of the table to 99. That will
force SQLBase to allocate 1 row per page.

NExt, in the query below(sSelectId), try fetching the ROWID and use
the rowid you fetched to Update the table.

Thanks Everyone
Master Yoda

>

David Drager

Product Roadmap for SQLBase

Post by David Drager » 12 Sep 2007, 21:12

 Posted by:  David Drager 

Yoda,

You are right about PCTFREE, it is a good practice if you have a very small
table with contention to use a high PCTFREE. But you don't want to do a
select before the update when you are generating ID numbers. Always perform
the update first and then do the select and then commit right away.
Otherwise you will get the world's most efficient deadlock generator.

Committing immediately after the Select decreases concurrency problems, but
if a transaction is rolled back you may have ID number that skipped over.

Also keep in mind that with SQLBase 11 life is easy. SQLBase can generate
your ID numbers without you having to worry about concurrency issues.

Regards,

David Drager

FRBhote
India
Posts: 1230
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Product Roadmap for SQLBase

Post by FRBhote » 13 Sep 2007, 07:39

 Posted by:  F R Bhote 

sUpdateId may be defined after sSelectId, but the execution is sUpdateId and
then sSelectId. Therefore the question of rowid does not arise.

However, the second query on the PARTY table appears to be causing most of
the problems. Almost all queries use this table, and there are Temp X-Locks
and Temp S-Locks are causing the problem.

Regarding :
We need to keep compatibility with earlier versions of SqlBase - so it will
mean making the APT compatible with both - it's a big job. And as I said the
PARTY table is the main hitch. Wonder if unloading and loading it every day
will help. Let me try it today.

Return to “gupta.wishlist”

Who is online

Users browsing this forum: [Ccbot] and 0 guests