Stored procedures Netezza

dataservices administration
1

Hi,

I was asked to test a stored procedure(from NETEZZA) to be imported into BODI and see whether if it is working.
How can I import a stored procedure into BODI created in NZ appliance ?
The stored procedure is a replacement for table comparision transform in BODI .

Thanks in advance.

jbodi (BOB member since 2011-09-07)

2

You go to your NETEZZA Data store, and then expand the DS, you will see ‘function’ library, then you import by name.

Just specify name of the function and then click OK, if everything goes right, you should see that SP under function library!

Hope this helps!

data_guy :us: (BOB member since 2006-08-19)

3

I have the SP in Aginity Netezza Workbench.

I have a new repository and no data stores created . So do i need to create a datastore and call the function.

jbodi (BOB member since 2011-09-07)

4

Just like before you can drag tables into your DF, you need to IMPORT them, same goes for DB SP in case you want to use them in DI jobs.

So looks like, you need to create new DS just to import the SP.

data_guy :us: (BOB member since 2006-08-19)

5

I have created Data store with Db as Netezza.

But in Data store which I created, it expands only for tables and templates but not Functions !

Generally we see functions,tables,templates right. And these functions can be used in scripts as per business logic.

I want to know if I missed something .

jbodi (BOB member since 2011-09-07)

6

Seems like Neteeza is one of the databases we do not support functions. Most of the ODBC based implementations we do not.
So your only option is to call them via a sql() function in a script.

Using a sql() function inside a dataflow is no option due to performance.

Werner Daehn :de: (BOB member since 2004-12-17)

7

If I have to call stored procedures from Netezza I cannot do it now. So Can you help me for calling for stored procedures developed from Aginity Netezza workbench.

Is SQL transform is only option or do we have any.

Thanks,

JBODI

jbodi (BOB member since 2011-09-07)

8

It is not SQL Transform

Werner meant the SQL() Function

You could probably call like this

SQL(‘NETEZZA_DATASTORE_NAME’,‘STORED_PROCEDURE_NAME()’);

ganeshxp :us: (BOB member since 2008-07-17)

9

I did not get that.

In a script at different level of job this can called like sql(…) ?

jbodi (BOB member since 2011-09-07)

10

Yes on the script level.

ganeshxp :us: (BOB member since 2008-07-17)

11

Ok thanks

I was wondering if i have to work on developing jobs and test them at same time then do i need to migrate each and every component of BODI to NZ Data stores as We dont hav access to production data ( using this only for testing purposes)

help me what i can do !!

Thanks

jbodi (BOB member since 2011-09-07)