6 Replies Latest reply: May 29, 2012 5:33 PM by Thorsten Zielke RSS

Simple select doesn't work

Andrea Vettori
Currently Being Moderated

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

  • Re: Simple select doesn't work
    Thorsten Zielke
    Currently Being Moderated

    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

    • Re: Simple select doesn't work
      Andrea Vettori
      Currently Being Moderated

      Hi,

       

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

      Thanks !

    • Re: Simple select doesn't work
      Andrea Vettori
      Currently Being Moderated

      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

      • Re: Simple select doesn't work
        Thorsten Zielke
        Currently Being Moderated

        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

        • Re: Simple select doesn't work
          Andrea Vettori
          Currently Being Moderated

          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)

          • Re: Simple select doesn't work
            Thorsten Zielke
            Currently Being Moderated

            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

Actions