Topic
  • 5 replies
  • Latest Post - ‏2011-06-14T13:13:18Z by SystemAdmin
SystemAdmin
SystemAdmin
15496 Posts

Pinned topic Cognos report Error - 'UDA-SQL-0114' - 'sqlOpenResult' status='-28'.

‏2011-06-13T10:26:08Z |
Hi Phil / Wilson,
I am getting the attached error when I run the report. The version is cognos 8.4.1 and database is Db2. But when I schedule the report, the scheduling is succeeded. Prior to this, it was running perfectly without any error. The report is list and it invloved only 4 joins. Can you please suggest what the issue is and how to resolve it.

Thanks!!
Updated on 2011-06-14T13:13:18Z at 2011-06-14T13:13:18Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos report Error - 'UDA-SQL-0114' - 'sqlOpenResult' status='-28'.

    ‏2011-06-13T13:37:08Z  
    David,
    Try to change the

    changed the "Use Local Cache" to "No".

    Winson.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos report Error - 'UDA-SQL-0114' - 'sqlOpenResult' status='-28'.

    ‏2011-06-13T23:08:58Z  
    David,
    Try to change the

    changed the "Use Local Cache" to "No".

    Winson.
    UDA-SOR-0005 usually indicates a sorting operation issue. This can occur when local processing is involved and the Cognos server is attempting to sort large amounts of data.

    When you execute a report interactively the query will not normally be the same as when you execute the report as a scheduled item or in the background. The reason for this is that Cognos will reformat the interactive query to get the first set of rows back more quickly so that it can render the first page of output faster. For a scheduled report this step is not necessary and the query is optimized to retrieve all the records rather than just the first few.

    I would look at how the query is being generated to see if local processing is a factor here. If you can push more of the query to the database you will likely not have any issues with local sorting files.

    Another thing that you should look into is contacting IBM support. It is possible that there is an issue here that should be raised to the attention of IBM development and support can help you with this process.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos report Error - 'UDA-SQL-0114' - 'sqlOpenResult' status='-28'.

    ‏2011-06-14T05:34:42Z  
    UDA-SOR-0005 usually indicates a sorting operation issue. This can occur when local processing is involved and the Cognos server is attempting to sort large amounts of data.

    When you execute a report interactively the query will not normally be the same as when you execute the report as a scheduled item or in the background. The reason for this is that Cognos will reformat the interactive query to get the first set of rows back more quickly so that it can render the first page of output faster. For a scheduled report this step is not necessary and the query is optimized to retrieve all the records rather than just the first few.

    I would look at how the query is being generated to see if local processing is a factor here. If you can push more of the query to the database you will likely not have any issues with local sorting files.

    Another thing that you should look into is contacting IBM support. It is possible that there is an issue here that should be raised to the attention of IBM development and support can help you with this process.
    Winson,

    I have changed the Local cache to 'No' and to my surprise it was working fine. Thanks for the suggestion!! But what could be the specific reason that for only this report I require the setting. I dont have good idea on this,can you tell me.

    Phil, Yes the report had previously set with the Local processing and I dont remember why I kept at that time. I have removed it and it is working as usual.
    But what do you mean by "If you can push more of the query to the database you will likely not have any issues with local sorting files"
    Thanks in Advance!!
    David
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos report Error - 'UDA-SQL-0114' - 'sqlOpenResult' status='-28'.

    ‏2011-06-14T06:02:23Z  
    Winson,

    I have changed the Local cache to 'No' and to my surprise it was working fine. Thanks for the suggestion!! But what could be the specific reason that for only this report I require the setting. I dont have good idea on this,can you tell me.

    Phil, Yes the report had previously set with the Local processing and I dont remember why I kept at that time. I have removed it and it is working as usual.
    But what do you mean by "If you can push more of the query to the database you will likely not have any issues with local sorting files"
    Thanks in Advance!!
    David
    If you have simply changed the processing to the default/blank selection then this will inherit the governor setting from your FM model. The default FM model governors are actually set to use limited local processing so your change may not have done anything. Try setting the property in your report queries explicitly to Database Only. If the report is still running fine then this is an indication that local processing is not involved. In which case my suggestions are not going to be useful here. However, if you start getting an error about local processing being required then follow my previous recommendation. What I meant by the quoted text is that if you can send the entire query to the database then the sorting operations may be possible as part of this single database SQL statement so the Cognos server will not have to do any sorting after retrieving the data.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos report Error - 'UDA-SQL-0114' - 'sqlOpenResult' status='-28'.

    ‏2011-06-14T13:13:18Z  
    David,

    We had the same issue but we were trying so many different option to solve it. finally this trick works.I never dig into far to see why yet.

    Winson.