10 Replies Latest reply: Jan 22, 2013 6:52 AM by 915739 RSS

    OBIEE Reportting error

    915739
      Hi Gurus,

      When i am tring to run a report i am getting following error.I am using Obiee 10g .Could you please suggest me here. What needs to be done to see the data.
      I am not able to see data.after running a lot time report is throwing the following error.

      Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
      State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46017] Sort has no work space in the work directory. [nQSError: 46103] Sorter failed to compress result. The compressed result is more than four gigabytes, please refine the query (HY000)


      Kindly help me here.





      Regards,
      SK
        • 1. Re: OBIEE Reportting error
          Satya Ranki Reddy
          Hi SK,

          46017
          Sort has no work space in the work directory.

          Cause - The work directory has no available space. The disk may be full, or the directory may have reached its maximum size.

          Response - Make space available in the directory(path - WORK_DIRECTORY_PATHS in NQSConfig.INI file ), or add another work directory to the NQSConfig.ini file by adding a path to the WORK_DIRECTORY_PATHS value.

          Go through the link for WORK_DIRECTORY_PATHS under same heading,it gives good idea...http://obiee101.blogspot.com/2010/01/obiee-performance-tuning.html


          Hope this help's

          Thanks
          Satya
          • 2. Re: OBIEE Reportting error
            user248025
            Hi,

            Its not DB it's analysis tool, so try to put some more filter and then test it.

            Note: your error message say's your reporting query more than 4gb and exeeded config. bi part.

            Thanks
            Deva
            • 3. Re: OBIEE Reportting error
              915739
              Hi Satya,


              The below link which is provided is not working .Could you please explain me bit detail.

              http://obiee101.blogspot.com/2010/01/obiee-performance-tuning.html






              Regards,
              SK
              • 4. Re: OBIEE Reportting error
                915739
                Hi ,

                Thanks in advance.This problem is not resolved.Still i am facing issue.
                Could you please explain me bit detail.

                Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
                State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46017] Sort has no work space in the work directory. [nQSError: 46103] Sorter failed to compress result. The compressed result is more than four gigabytes, please refine the query (HY000)
                • 5. Re: OBIEE Reportting error
                  Christian Berg
                  Satya gave you the response:>
                  46017
                  Sort has no work space in the work directory.

                  Cause - The work directory has no available space. The disk may be full, or the directory may have reached its maximum size.

                  Response - Make space available in the directory(path - WORK_DIRECTORY_PATHS in NQSConfig.INI file ), or add another work directory to the NQSConfig.ini file by adding a path to the WORK_DIRECTORY_PATHS value.>

                  Here's what an unmodified OBIEE 11.1.1.6 NQSConfig.INIstates:
                  # Work directory paths can be left blank and we will default
                  # to the process instance's temp directory.  For example,
                  # $(ORACLE_INSTANCE)/temp/OracleBIServerComponent/<instance_name>.
                  # If relative, it will be relative to this path.  If absolute,
                  # we take the absolute path at runtime.
                  WORK_DIRECTORY_PATHS = "";  
                  VIRTUAL_TABLE_PAGE_SIZE = 128 KB;  
                  Edited by: Christian Berg on Jan 22, 2013 10:50 AM
                  • 6. Re: OBIEE Reportting error
                    915739
                    Hi ,

                    Thanks for your response.I am using OBIEE 10G Version.
                    Could you please suggest me how should i change or add space in work directory

                    Kindly guide me please






                    Regards,
                    SK
                    • 7. Re: OBIEE Reportting error
                      944874
                      Plug in a USB stick, assign it drive letter X and change your NQSConfig.ini like this: WORK_DIRECTORY_PATHS = "X:\";
                      • 8. Re: OBIEE Reportting error
                        915739
                        Hi,

                        Actually It is a server and dont have access to add one more drive or stick.
                        Could you please suggest how to Increase space.
                        Can i add more path with in the same drive in Config.xml itself?

                        Kindly guide me Please.


                        Regards,
                        SK
                        • 9. Re: OBIEE Reportting error
                          Satya Ranki Reddy
                          Hi Sk,

                          Sorry for delay response.

                          Note: Before doing this modification please take backup Nqsconfig.ini file.

                          Default :

                          WORK_DIRECTORY_PATHS = "C:\OracleBIData\tmp";
                          SORT_MEMORY_SIZE = 4 MB ;
                          SORT_BUFFER_INCREMENT_SIZE = 256 KB ;
                          VIRTUAL_TABLE_PAGE_SIZE = 128 KB ;

                          After modification:

                          WORK_DIRECTORY_PATHS = "D:\OracleBIData\tmp";
                          SORT_MEMORY_SIZE = 8MB ;
                          SORT_BUFFER_INCREMENT_SIZE = 512KB ;
                          VIRTUAL_TABLE_PAGE_SIZE = 128 KB ;

                          Ask to admin person to modify the nqsconfig file as mention above. Please modify as mention above and see wether this will work or not..

                          The problem will occured two times.

                          1. sort memory size full
                          2. if you pull the columns in report from multiple sources.

                          Hope this help's

                          Thanks
                          Satya
                          • 10. Re: OBIEE Reportting error
                            915739
                            Thanks a lot Satya for your clear explanation and response.
                            I will try this and test .



                            Regards
                            SK