Simple select doesn't work

[Former Member]

Hi,

I don't understand this behavior (seems a bug?).

This query works and returns many rows :

select *
  from lottoevasione l
  join spedizione s on s.id_lotto = l.id
  join spedizione_segnacolli ss on ss.id_spedizione = s.id
  join lottoevasione_ordini lo on lo.id_lottoevasione = l.id
  join ordinecliente oc on oc.id = lo.id_ordinecliente
  join modspedizione ms on oc.id_modsped = ms.id
 where length(ss.segnacollo_ext) < 13 
   and ms.id_corriere = 1

This one returns an empty set (the only change is on the first row) :

select s.id
  from lottoevasione l
  join spedizione s on s.id_lotto = l.id
  join spedizione_segnacolli ss on ss.id_spedizione = s.id
  join lottoevasione_ordini lo on lo.id_lottoevasione = l.id
  join ordinecliente oc on oc.id = lo.id_ordinecliente
  join modspedizione ms on oc.id_modsped = ms.id
 where length(ss.segnacollo_ext) < 13 
  and ms.id_corriere = 1

Any ideas of why it can happen ? Note that whatever is used instead of 's.id' on the first row the second query doesn't work. Only the star '*' works.

Thanks

[thorsten_zielke]

Hi,

this may be a bug that is already fixed - please let me know the following:

1. Which MaxDB version are you using? If there is a newer version available, consider an upgrade if you can afford the downtime.

2. Run a Database Analyzer parameter check (as outlined in SAP note 1111426) to verify, if all database parameters are set as recommended. If not, change them or let me know if you need guidance.

3. If the issue still occurs, repeat the SQL with a 'explain select...' and post the resulting strategy for both SQLs here.

Thorsten

[Former Member]

Hi,

I'm using version 7.8.02.21, I'll try 7.8.02.28 on the test environment and report back.

Thanks !

[Former Member]

I've tried installing 7.8.2.28 as explained here: http://maxdb.sap.com/doc/7_8/44/d962dbd06f3ee2e10000000a114a6b/frameset.htm but I'm having an issue.

I installed the new version using SDBINST and the option to install a 'parallel' version in a different directory. The first problem I had is that SDBINST was looking for sdbconfig program into /opt/sdb/MaxDB/bin but on my installation it's into /opt/sdb/programs/bin. I resolved with a soft link.

The installation went to the end, and the new versions files are into /opt/sdb/7.8.2.28.

Following the procedure on the web page above, I couldn't execute the db_reg -R <instpath> command.

I tried

db_reg -R /opt/sdb/7.8.2.28

to tell the first database to use the newer software version but I got this error

ERR_WRONGINST - DBM server instroot is /opt/sdb/MaxDB

i.e. it tells that instroot is the older version (7.8.2.21) and it seems it can't change it.

Any help is greatly appreciated.

Thanks

[thorsten_zielke]

Are you sure that your database software was indeed using the same installation path?

Please issue a 'xinstinfo <dbname>' for all installed database instances and let me know the result, especially for the 'InstallationPath' output.

Thorsten

[Former Member]

Thanks for your help.

xinstinfo JMS
IndepData           : /home/sdb/data
IndepPrograms       : /opt/sdb/MaxDB
InstallationPath    : /opt/sdb/MaxDB
Kernelversion       : KERNEL    7.8.02   BUILD 021-121-242-175
Rundirectory        : /home/sdb/data/wrk/JMS

There are 5 databases on this server and the output of xinstinfo is the same for all of them (the Rundirectory changes obviously)

[thorsten_zielke]

The problem is that all your 5 databases are sharing the same 'InstallationPath'. The 'InstallationPath' depends on the installed database software.

What you need to do now is give each database its own 'InstallationPath'.

I would recommend to

1. Install a new database software with the same patch level 7.8.02.21 with sdbinst, but use a *different* installation path name.

2. Have a look at the 'dbmrelocate' tool introduced with MaxDB 7.8 at http://wiki.sdn.sap.com/wiki/display/MaxDB/dbmrelocate. This should help you to move a existing database to another software installation path.

Thorsten