Universe Stored Procedures - Netezza

[system]

Hi All,

I am trying to build a stored procedure based on a Netezza database and am having trouble establishing a connection. I am able to open a normal universe without issue, and open a stored procedure universe on Oracle database without issue. Therefore I expect this to be Netezza specific.

In the past I have successfully applied solutions to issues which were used on Sysbase, Postgres andTerradata databases, so hopefully someone can help…?

When attempting to create the universe I get the “This Connection does not allow Stored Procedures Universe” error.

I have found the following threads here at BOBJ on this error which lead me to believe it is an issue with the .SBO file and ODBC connection:

http://www.forumtopics.org/busobj/viewtopic.php?t=176047&sid=3c737b996e5fb9ac8bfe10620f6b5eb4

120×120 10.4 KB

Hi guys! today I was lucky and managed to make stored procedures working in WebI. I’m glad to share this ‘know how’ and say goodby to DeskI Change parameter Force SQLExecute in odbc.sbo to Always Add parameter Force SQLExecute = Always into oledb.sbo Create query in WebI with the same structure your SP returns Change SQL to following set nocount on /*SELECT any_field FROM any_table*/ exec your_stored_procedure @prompt('enter value','A',<other prompt parameters>) you can even use tem…

On the BO server …/dataAccess/connection server there are quite a few .SBO files, so presuming I need to modify the relevent rdbms SBO being Netezza track to …/dataAccess/connection server/odbc/ where I find Netezza.sbo:

<?xml version="1.0" encoding="UTF-8"?><DriverConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../sbo.xsd">
    <Defaults>
        <Parameter Name="Family">Netezza</Parameter>
        <Parameter Name="Driver Name">Netezza*</Parameter>
        <Parameter Name="SQL External File">netezza</Parameter>
        <Parameter Name="SQL Parameter File">netezza</Parameter>
        <Parameter Name="Description File">odbc</Parameter>
        <Parameter Name="Driver Capabilities">Query</Parameter>
        <Parameter Name="Extensions">netezza,odbc</Parameter>
        <Parameter Name="Array Fetch Available">True</Parameter>
        <Parameter Name="Array Bind Available">False</Parameter>
        <Parameter Name="Empty String">EmptyString</Parameter>
        <Parameter Name="CharSet Table">odbc</Parameter>
        <Parameter Name="CharSet Table" Platform="Unix">datadirect</Parameter>
        <Parameter Name="Query TimeOut Available">No</Parameter>
        <Parameter Name="Connection Status Available">False</Parameter>
    </Defaults>
    <DataBases>
        <DataBase Active="Yes" Name="Netezza Server 4">
            <Aliases>
              <Alias>Netezza Server</Alias>
            </Aliases>
            <Libraries>
                <Library Platform="MSWindows">dbd_wodbc3</Library>
                <Library Platform="MSWindows">dbd_odbc3</Library>
                <Library Platform="Unix">dbd_wddodbc3</Library>
                <Library Platform="Unix">dbd_ddodbc3</Library>
            </Libraries>
            <Parameter Name="Extensions">netezza4,netezza,odbc</Parameter>
        </DataBase>
        <DataBase Active="Yes" Name="Netezza Server 5">
            <Libraries>
                <Library Platform="MSWindows">dbd_wodbc3</Library>
                <Library Platform="MSWindows">dbd_odbc3</Library>
                <Library Platform="Unix">dbd_wddodbc3</Library>
                <Library Platform="Unix">dbd_ddodbc3</Library>
            </Libraries>
            <Parameter Name="Extensions">netezza5,netezza,odbc</Parameter>
        </DataBase>
        <DataBase Active="Yes" Name="Netezza Server 6">
            <Libraries>
                <Library Platform="MSWindows">dbd_wodbc35</Library>
                <Library Platform="MSWindows">dbd_odbc35</Library>
                <Library Platform="MSWindows">dbd_wodbc3</Library>
                <Library Platform="MSWindows">dbd_odbc3</Library>
                <Library Platform="Unix">dbd_wddodbc3</Library>
                <Library Platform="Unix">dbd_ddodbc3</Library>
            </Libraries>
            <Parameter Name="SQL Parameter File">netezza6</Parameter>            
            <Parameter Name="Extensions">netezza6,netezza,odbc</Parameter>

        </DataBase>
        <DataBase Active="Yes" Name="Netezza Server 7">
            <Libraries>
                <Library Platform="MSWindows">dbd_wodbc35</Library>
                <Library Platform="MSWindows">dbd_odbc35</Library>
                <Library Platform="MSWindows">dbd_wodbc3</Library>
                <Library Platform="MSWindows">dbd_odbc3</Library>
                <Library Platform="Unix">dbd_wddodbc3</Library>
                <Library Platform="Unix">dbd_ddodbc3</Library>
            </Libraries>
            <!--  using netezza6 PRM File (no changes)  -->
            <Parameter Name="SQL Parameter File">netezza6</Parameter>            
            <Parameter Name="Extensions">netezza7,netezza,odbc</Parameter>
        </DataBase>
    </DataBases>
</DriverConfiguration>

So I have added:

 <Parameter Name="Force SQLExecute">Always</Parameter>

I have also modfied the connection within CMC to enable Adminstrator user group all rights over the connection - ie Relational Connection Use connection for Stored Procedures is enabled.

And worth adding, I made the sbo changes in situ, should I restart perhaps?

However still have the same error…

Does anyone have any suggestions??

Thanks!

---

Thomas Evans (BOB member since 2013-10-17)

[system]

Hi Thomas,

Did you ever resolve this?

Cheers,

Steve

---

steveayres (BOB member since 2006-11-23)