SYSKEY problem in 7.6.06.03

[thomasschulz2]

Hello list,

after the update from 7.6.03.15 to 7.6.06.03 (on linux) we've noticed a problem, when using the SYSKEY column of MaxDB (internal unique key created implicitly on tables without a key).

The problem occurs, when we read some rows including their syskeys and afterwards try to read or update these rows through their syskeys. Every time, when the syskey ends on "...00", the corresponding row isn't found. A comparison with 7.6.03.15 shows, that in the older version no syskeys with "...00" are created.

How to reproduce the problem:

- create a table without a key

- insert more than 256 rows

- read the rows including their syskeys

- run a select on the syskeys (select ... where syskey = x'<syskey>')

I've tested with perl scripts (decoding the syskey) and with the MaxDB Database Studio. I know, that there are better mechanisms like using a unique database field, but the affected scripts are old, have grown during the time and are distributed over various applications. Therefore it is difficult for us to change the structure.

Are there any "simple" solutions, which allow us to continue using the syskeys in our scripts (like other query syntax, or db parameter change)?

Regards,

Thomas

[Former Member · Accepted solution]

Hi,

there is some help at hand using a different SQL.

Ok, it looks a little bit let's say : strange.

But it works.

It is not so good for performance as your old qualification, but not too bad.

But please check what it will mean in your case.

You may change

select ... where syskey = x''

to

select ... where WHERE SYSKEY >= X....'' AND SYSKEY < EXPAND(X'.....',8) || X'01'

or, if you are using parameter for the syskey-value

WHERE SYSKEY >= :A AND SYSKEY < GREATEST(X'00', EXPAND(:A,8) || X'01')

As I promised: it looks strange.

And you can be assured: I did not manage it with the first try.

As Lars wrote; we do not know, when to fix the bug of creating syskeys ending with x'00'.

But as you do have them in your system, even a new kernel will not help for the current (wrong) syskeys,

as the check will not change.

Therefore you have to use some workaround until (with a new kernel) you will unload/drop your current tables

and re-insert your data with new syskeys.

We are very sorry for the inconveniences.

Elke

[thomasschulz2 · Accepted solution]

Hi Elke,

> Hi,

>

> there is some help at hand using a different SQL.

>

> Ok, it looks a little bit let's say : strange.

>

> But it works.

> It is not so good for performance as your old qualification, but not too bad.

> But please check what it will mean in your case.

>

> You may change

>

> select ... where syskey = x''

> to

> select ... where WHERE SYSKEY >= X....'' AND SYSKEY < EXPAND(X'.....',8) || X'01'

>

> or, if you are using parameter for the syskey-value

>

> WHERE SYSKEY >= :A AND SYSKEY < GREATEST(X'00', EXPAND(:A,8) || X'01')

>

>

> As I promised: it looks strange.

> And you can be assured: I did not manage it with the first try.

that's what we are looking for. I changed your query to:

SELECT ... WHERE SYSKEY >= X'...' AND SYSKEY < X'...' || X'01'

and it works. With EXPAND function we get the error: "Invalid unsigned integer".

> As Lars wrote; we do not know, when to fix the bug of creating syskeys ending with x'00'.

> But as you do have them in your system, even a new kernel will not help for the current (wrong) syskeys,

> as the check will not change.

> Therefore you have to use some workaround until (with a new kernel) you will unload/drop your current tables

> and re-insert your data with new syskeys.

This will be probably no problem for us, because we delete the rows after processing or mark them as processed.

Thanks a lot for your help and investigations,

Thomas

[Former Member]

Hi,

do NOT leave out the EXPAND !!!

if your syskey does have less than seven bytes, then YOUR select will return more than one row.

And that is not what you expect.

And, yes, sorry, 7.6.6. does have a problem (fixed in newer releases):

if the integer given for expand-length is equal to the length of the column/literal given as first parameter, then the error mentioned is returned.

--> please take my version (including expand) , but use 9 instead of 8.

And do I understand correctly, that you always include the syskey as string literal?????

You do not prepare your statement once and just execute it together with the current data???

Oh, oh, I hope, Lars will skip this mail and not tell you something about misbehaviour, bad performance, and the like.

I would like to tell you the same...

...but I will not.

Good luck,

Elke

[thomasschulz2]

>

> Hi,
> 
> do NOT leave out the EXPAND !!!
> 
> if your syskey does have less than seven bytes, then YOUR select will return more than one row.
> And that is not what you expect.
> 
> And, yes, sorry, 7.6.6. does have a problem (fixed in newer releases):
> if the integer given for expand-length is equal to the length of the column/literal given as first parameter, then the error mentioned is returned.
> 
> --> please take my version (including expand) , but use 9 instead of 8.
> 

OK, I have corrected it.

> And do I understand correctly, that you always include the syskey as string literal?????

>

> You do not prepare your statement once and just execute it together with the current data???

>

> Oh, oh, I hope, Lars will skip this mail and not tell you something about misbehaviour, bad performance, and the like.

>

> I would like to tell you the same...

>

> ...but I will not.

>

> Good luck,

> Elke

Yes, I know, but with these old scripts it is like with old database versions: you fix the serious errors, but don't optimize it anymore as long as they are working and fulfill their purpose. But I take your advice up and check our scripts for optimization at this point.

Regards and thank you again,

Thomas

[lbreddemann]

Hi there,

I was able to reproduce this behaviour with 7.6.06 but not with the current 7.7.06.

So upgrading to the 7.7 version may be a feasible workaround.

Apart from that, it's just a design bug to reference your data by the syskey.

What do you do with duplicates of your application data?

regards,

Lars

[thomasschulz2]

Hi Lars,

used as queues the duplicates are processed again (updating existing data or transferring data again) or contain data, which are in combination unique (a possible solution for using an other unique key).

I agree, using the syskeys is no good design, but as already told, this are old grown mechanisms, which have to be redesigned step by step. Actual we take a look at the known processes searching for alternative solutions.

Because we are very satisfied with 7.6, an upgrade to 7.7 actual is no alternative for us. Due to our experiences with the last two 7.6 updates, this seems to be a much bigger step, which we will go only after extensive tests (not planned now).

Regards,

Thomas

[lbreddemann]

> I agree, using the syskeys is no good design, but as already told, this are old grown mechanisms, which have to be redesigned step by step. Actual we take a look at the known processes searching for alternative solutions.

> Because we are very satisfied with 7.6, an upgrade to 7.7 actual is no alternative for us. Due to our experiences with the last two 7.6 updates, this seems to be a much bigger step, which we will go only after extensive tests (not planned now).

Well... what do you think will happen now?

7.6 is OLD STUFF.

It's supported and maintained for SAP customers to provide them with some stability in their landscape.

It's surely not the version the development is working on.

Since this issue cannot occur in SAP environments (where every table has a defined primary key) I doubt that this bug will catch much attention or a high priority.

Especially since there is a valid workaround: use the current version of MaxDB.

Therefore: either you fix your bugs (no primary keys) or you have to wait probably pretty long until we fix this bug.

regards,

Lars

[thomasschulz2]

>

> Well... what do you think will happen now?
> 7.6 is OLD STUFF.
> It's supported and maintained for SAP customers to provide them with some stability in their landscape.
> It's surely not the version the development is working on.
OK - this wasn't so clear to me from outside the SAP circle. If I take a look at the various public MaxDB Websites (e.g. download, availability matrix), I don't see such a clear statement about the state of 7.6. Maybe it will be a good idea, to add a similiarly information there.
> 
> Since this issue cannot occur in SAP environments (where every table has a defined primary key) I doubt that this bug will catch much attention or a high priority.
> Especially since there is a valid workaround: use the current version of MaxDB.
> 
> Therefore: either you fix your bugs (no primary keys) or you have to wait probably pretty long until we fix this bug.
> 
> regards,
> Lars

We try to adapt our structures and scripts to avoid the usage of syskeys.

Regards,

Thomas

[lbreddemann]

>

> >

> > Well... what do you think will happen now?

> > 7.6 is OLD STUFF.

> > It's supported and maintained for SAP customers to provide them with some stability in their landscape.

> > It's surely not the version the development is working on.

>

> OK - this wasn't so clear to me from outside the SAP circle. If I take a look at the various public MaxDB Websites (e.g. download, availability matrix), I don't see such a clear statement about the state of 7.6. Maybe it will be a good idea, to add a similiarly information there.

It's stated that the software is in maintenance.

Of course bugs customers run into will be fixed if necessary, no question about that.

But everything gets a priority these days and so will this bug get one.

regards,

Lars