RE: Is this a BUG? my unicode-enabled database have some problem

shaksuper wrote:
> 
> hello!
> 
> I have created a unicode-enabled database by set the parameter 
> _UNICODE to YES and the parameter DEFAULT_CODE to UNICODE.
> Then,I input some data from sqlserver2000 to the database successful.
> when I try to run some query statement,the error occur:
> 
> >Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
> >General error;-2009 POS(1) Join columns too long.
> >select em.STATUS AS status,    em.EMPLOYEE_ID AS employeeId,  
> >em.EMPLOYEE_NAME AS employeeName,a.TIME_CONCUME as timeConcume,  
> >a.END_DATE AS endDate,  p.PROJECT_TITLE_SHORTEN AS 
> >projectName,ot.OPERATION_TYPE_TITLE AS operationType from  
> >manhaur_temp AS a , empolyee as em, project as p,  operation_type as 
> >ot,  operation as o where      em.EMPLOYEE_ID=a.EMPLOYEE_ID and  
> >a.OPERATION_ID=o.EVENT_ID  and      
> >o.OPERATION_TYPE_ID=ot.OPERATION_TYPE_ID and    
> >o.EVENT_ID=p.PROJECT_ID
> 
> I tested several times,I found that if the statement include 3 tables 
> it will run fine,more than 3 tables it will generate the General 
> error;-2009 POS(1) Join columns too long.

ith the current versions of MaxDB there is a limit of 1020 bytes (bytes,
not character) for those join-columns used for joining the next table.
If you have tables a,b,c,d (assuming, they are handled in this sequence)
and join-predicates like
a.c1 = c.c1 and
b.c2 = c.c2 and
c.c3 = d.c3

Then (after having joined a and b) the join-columns for table c are c.c1 and c.c2,
not c.c3. Therefore the sum of lengths of c.c1 and c.c2 is not allowed this limit.
With other sequences of table-handling (if a,b,d,c is the best sequence, for example)
those columns which are needed to join c may be different.

> 
> Besides,I sometimes fall across the General error;-904 POS(1) Space 
> for result tables exhausted,when I execute sql statement both at sql 
> studio and according JDBC driver.

In the archive of this list, there are several therads concerning error -904.
It means, that your database is too small to hold the intermediate results
and the final result of some selects.
This may be caused by 
- a really too small database (nearly filled with primary data)
- a select with huge size of intermediate results, for example
  for joins with OR-terms for join-conditions
- wrong (too old) optimizer statistics causing bad join-sequences
- missing indizes causing bad join-sequences.

We do not know those selects, the size of the database, the explain-output
of those selects,... therefore these are just hints where to look to.

> 
> I have read the document carefully,but still confused.
> At last,I also setup a non unicode-enabled database and try the same 
> example,and it's run fine.
> Is this a sapdb BUG?

No, this is no bug, but unicode-character needs twice the size of
ascii-character--> increasing the size of primary data, intermediate 
and final results, row length, join-column-length.
Do you really NEED all columns to be of unicode-type 
or would it be a good idea to define a mixture
of ascii character columns and unicode-character-columns depending
on the columns needs?


Elke
SAP Labs Berlin
> 
> _______________________________________________
> sapdb.general mailing list
> [email protected]
> http://listserv.sap.com/mailman/listinfo/sapdb.general
>