App Connect

Hi Folks,
Trying to call a stored procedure which is defined in a database utils library. However, the schema changes between environments e.g.

CREATE PROCEDURE updateDB()
LANGUAGE DATABASE
EXTERNAL NAME SomeStoredProcedure

which allows me to call the database using...

DECLARE DBSchemaName EXTERNAL CHARACTER;
CALL UpdateDB() IN Database.{DBSchemaName}

I can then create a UDP called DBSchemaName and override it per environment.

However, this requires that every time someone uses updateDB in a compute node that they have to DECLARE the DBSchemaName variable, create a UDP and override it in *EVERY* bar file that uses the database library. This is very tedious and prone to errors so I'm trying to find a way of keeping the UDP with the database library so that I can just deploy the library once with a "policy"/UDP per environment - but I can't think of a way. Here's what I've tried (and failed with)...

Option 1:
Define the schema dynamically in the Database module e.g can I do something like:

CREATE PROCEDURE updateDB()
LANGUAGE DATABASE
EXTERNAL NAME {DBSchema}.SomeStoredProcedure

I've tried every combination I can think of and it doesn't work for me - it will only allow a hard-coded name for the stored procedure's name.

Option 2:

Create a library that wrappers the Stored Procedure and keep this with the stored procedures e.g.

DECLARE DBSchema EXTERNAL CHARACTER;
CREATE FUNCTION updateDB() BEGIN
CALL ExportShipmentsImpl() IN Database.{DBSchema};
END;

However, I can't find a way to override the DBSchema variable as it's not promotable ?

Am I doing something wrong or is this simply not possible to do and I'm going to have to cope with the pain of the original way of doing it (which is the IBM recommended way from what I can see btw). I' ve started to look at a user-defined policy but can't imagine that this would work any better in terms of being able to get it to work - as unless DBSchema is promotable nothing will work???

Any thoughts gurus ?

thanks,

john.

Hi Folks,
Trying to call a stored procedure which is defined in a database utils library. However, the schema changes between environments e.g.

CREATE PROCEDURE updateDB()
LANGUAGE DATABASE
EXTERNAL NAME SomeStoredProcedure

which allows me to call the database using...

DECLARE DBSchemaName EXTERNAL CHARACTER;
CALL UpdateDB() IN Database.{DBSchemaName}

I can then create a UDP called DBSchemaName and override it per environment.

However, this requires that every time someone uses updateDB in a compute node that they have to DECLARE the DBSchemaName variable, create a UDP and override it in *EVERY* bar file that uses the database library. This is very tedious and prone to errors so I'm trying to find a way of keeping the UDP with the database library so that I can just deploy the library once with a "policy"/UDP per environment - but I can't think of a way. Here's what I've tried (and failed with)...

Option 1:
Define the schema dynamically in the Database module e.g can I do something like:

CREATE PROCEDURE updateDB()
LANGUAGE DATABASE
EXTERNAL NAME {DBSchema}.SomeStoredProcedure

I've tried every combination I can think of and it doesn't work for me - it will only allow a hard-coded name for the stored procedure's name.

Option 2:

Create a library that wrappers the Stored Procedure and keep this with the stored procedures e.g.

DECLARE DBSchema EXTERNAL CHARACTER;
CREATE FUNCTION updateDB() BEGIN
CALL ExportShipmentsImpl() IN Database.{DBSchema};
END;

However, I can't find a way to override the DBSchema variable as it's not promotable ?

Am I doing something wrong or is this simply not possible to do and I'm going to have to cope with the pain of the original way of doing it (which is the IBM recommended way from what I can see btw). I' ve started to look at a user-defined policy but can't imagine that this would work any better in terms of being able to get it to work - as unless DBSchema is promotable nothing will work???

Any thoughts gurus ?

thanks,

john.

Hi Folks,
Trying to call a stored procedure which is defined in a database utils library. However, the schema changes between environments e.g.

CREATE PROCEDURE updateDB()
LANGUAGE DATABASE
EXTERNAL NAME SomeStoredProcedure

which allows me to call the database using...

DECLARE DBSchemaName EXTERNAL CHARACTER;
CALL UpdateDB() IN Database.{DBSchemaName}

I can then create a UDP called DBSchemaName and override it per environment.

However, this requires that every time someone uses updateDB in a compute node that they have to DECLARE the DBSchemaName variable, create a UDP and override it in *EVERY* bar file that uses the database library. This is very tedious and prone to errors so I'm trying to find a way of keeping the UDP with the database library so that I can just deploy the library once with a "policy"/UDP per environment - but I can't think of a way. Here's what I've tried (and failed with)...

Option 1:
Define the schema dynamically in the Database module e.g can I do something like:

CREATE PROCEDURE updateDB()
LANGUAGE DATABASE
EXTERNAL NAME {DBSchema}.SomeStoredProcedure

I've tried every combination I can think of and it doesn't work for me - it will only allow a hard-coded name for the stored procedure's name.

Option 2:

Create a library that wrappers the Stored Procedure and keep this with the stored procedures e.g.

DECLARE DBSchema EXTERNAL CHARACTER;
CREATE FUNCTION updateDB() BEGIN
CALL ExportShipmentsImpl() IN Database.{DBSchema};
END;

However, I can't find a way to override the DBSchema variable as it's not promotable ?

Am I doing something wrong or is this simply not possible to do and I'm going to have to cope with the pain of the original way of doing it (which is the IBM recommended way from what I can see btw). I' ve started to look at a user-defined policy but can't imagine that this would work any better in terms of being able to get it to work - as unless DBSchema is promotable nothing will work???

Any thoughts gurus ?

thanks,

john.

Hi Folks,
Trying to call a stored procedure which is defined in a database utils library. However, the schema changes between environments e.g.

CREATE PROCEDURE updateDB()
LANGUAGE DATABASE
EXTERNAL NAME SomeStoredProcedure

which allows me to call the database using...

DECLARE DBSchemaName EXTERNAL CHARACTER;
CALL UpdateDB() IN Database.{DBSchemaName}

I can then create a UDP called DBSchemaName and override it per environment.

However, this requires that every time someone uses updateDB in a compute node that they have to DECLARE the DBSchemaName variable, create a UDP and override it in *EVERY* bar file that uses the database library. This is very tedious and prone to errors so I'm trying to find a way of keeping the UDP with the database library so that I can just deploy the library once with a "policy"/UDP per environment - but I can't think of a way. Here's what I've tried (and failed with)...

Option 1:
Define the schema dynamically in the Database module e.g can I do something like:

CREATE PROCEDURE updateDB()
LANGUAGE DATABASE
EXTERNAL NAME {DBSchema}.SomeStoredProcedure

I've tried every combination I can think of and it doesn't work for me - it will only allow a hard-coded name for the stored procedure's name.

Option 2:

Create a library that wrappers the Stored Procedure and keep this with the stored procedures e.g.

DECLARE DBSchema EXTERNAL CHARACTER;
CREATE FUNCTION updateDB() BEGIN
CALL ExportShipmentsImpl() IN Database.{DBSchema};
END;

However, I can't find a way to override the DBSchema variable as it's not promotable ?

Am I doing something wrong or is this simply not possible to do and I'm going to have to cope with the pain of the original way of doing it (which is the IBM recommended way from what I can see btw). I' ve started to look at a user-defined policy but can't imagine that this would work any better in terms of being able to get it to work - as unless DBSchema is promotable nothing will work???

Any thoughts gurus ?

thanks,

john.

Hi Folks,
Trying to call a stored procedure which is defined in a database utils library. However, the schema changes between environments e.g.

CREATE PROCEDURE updateDB()
LANGUAGE DATABASE
EXTERNAL NAME SomeStoredProcedure

which allows me to call the database using...

DECLARE DBSchemaName EXTERNAL CHARACTER;
CALL UpdateDB() IN Database.{DBSchemaName}

I can then create a UDP called DBSchemaName and override it per environment.

However, this requires that every time someone uses updateDB in a compute node that they have to DECLARE the DBSchemaName variable, create a UDP and override it in *EVERY* bar file that uses the database library. This is very tedious and prone to errors so I'm trying to find a way of keeping the UDP with the database library so that I can just deploy the library once with a "policy"/UDP per environment - but I can't think of a way. Here's what I've tried (and failed with)...

Option 1:
Define the schema dynamically in the Database module e.g can I do something like:

CREATE PROCEDURE updateDB()
LANGUAGE DATABASE
EXTERNAL NAME {DBSchema}.SomeStoredProcedure

I've tried every combination I can think of and it doesn't work for me - it will only allow a hard-coded name for the stored procedure's name.

Option 2:

Create a library that wrappers the Stored Procedure and keep this with the stored procedures e.g.

DECLARE DBSchema EXTERNAL CHARACTER;
CREATE FUNCTION updateDB() BEGIN
CALL ExportShipmentsImpl() IN Database.{DBSchema};
END;

However, I can't find a way to override the DBSchema variable as it's not promotable ?

Am I doing something wrong or is this simply not possible to do and I'm going to have to cope with the pain of the original way of doing it (which is the IBM recommended way from what I can see btw). I' ve started to look at a user-defined policy but can't imagine that this would work any better in terms of being able to get it to work - as unless DBSchema is promotable nothing will work???

Any thoughts gurus ?

thanks,

john.

For completeness.. I eventually did the following:

Wrappered the stored proc in an ESQL proc e.g.

CREATE PROCEDURE ProdNameDB()
LANGUAGE DATABASE DYNAMIC RESULT SETS 1
EXTERNAL NAME ExternalStoredProcedureName;

which is called by...
CREATE PROCEDURE ProcName(INOUT Ref REFERENCE)
BEGIN
CALL ProcNameDB(Ref.Row[]) IN Database.{DBSchema};
END;

I have then put the DBSchema into a user defined policy. The only way you can (currently) get to that policy in esql is to wrapper it with a java class. So I also create that esql method in the same file:

CREATE FUNCTION getDBSchema() RETURNS CHARACTER LANGUAGE JAVA EXTERNAL NAME "com.companyname.DBSchema.getDBSchema";

It's all a pain tbh but it works. 
The only thing left is a problem I'm having with is passing variables. (You'll note that I set the location I need the result ("Row") in the call to the DB proc -
CALL ProcNameDB(Ref.Row[])

I would like for the user to pass in the location e.g. 

CREATE PROCEDURE ProcName(INOUT Ref REFERENCE, IN location CHAR)
CALL ProcNameDB(Ref.{location}[])


but I can't find the correct cantation but that's a different forum question ;-)

Hi Folks,
Trying to call a stored procedure which is defined in a database utils library. However, the schema changes between environments e.g.

CREATE PROCEDURE updateDB()
LANGUAGE DATABASE
EXTERNAL NAME SomeStoredProcedure

which allows me to call the database using...

DECLARE DBSchemaName EXTERNAL CHARACTER;
CALL UpdateDB() IN Database.{DBSchemaName}

I can then create a UDP called DBSchemaName and override it per environment.

However, this requires that every time someone uses updateDB in a compute node that they have to DECLARE the DBSchemaName variable, create a UDP and override it in *EVERY* bar file that uses the database library. This is very tedious and prone to errors so I'm trying to find a way of keeping the UDP with the database library so that I can just deploy the library once with a "policy"/UDP per environment - but I can't think of a way. Here's what I've tried (and failed with)...

Option 1:
Define the schema dynamically in the Database module e.g can I do something like:

CREATE PROCEDURE updateDB()
LANGUAGE DATABASE
EXTERNAL NAME {DBSchema}.SomeStoredProcedure

I've tried every combination I can think of and it doesn't work for me - it will only allow a hard-coded name for the stored procedure's name.

Option 2:

Create a library that wrappers the Stored Procedure and keep this with the stored procedures e.g.

DECLARE DBSchema EXTERNAL CHARACTER;
CREATE FUNCTION updateDB() BEGIN
CALL ExportShipmentsImpl() IN Database.{DBSchema};
END;

However, I can't find a way to override the DBSchema variable as it's not promotable ?

Am I doing something wrong or is this simply not possible to do and I'm going to have to cope with the pain of the original way of doing it (which is the IBM recommended way from what I can see btw). I' ve started to look at a user-defined policy but can't imagine that this would work any better in terms of being able to get it to work - as unless DBSchema is promotable nothing will work???

Any thoughts gurus ?

thanks,

john.