Stored procedure in database X, executes stored procedure in database Y, wrapped in transaction?
Is it possible to execute a stored procedure in one database, which then
itself executes a stored procedure from another database? We have decide to
split our data into a tree structure (DB1) and data blobs (DB2) (we are
using MSDE and we have a 2gb limit with each DB so we've done it this way
for that reason). I would like to, say, execute a stored procedure in DB1,
passing in the data blob and other details, DB1 will create a tree node in
DB1 and then add the blob record to DB2. DB1 will wrap in a transaction of
course, as will DB2 when it adds the blob. Is this possible?
[poste...Errors in stored procedure in db2
I am writing a Stord proc in db2 in which I am querying(select query) a
table in the database and picking 2 values.These 2 values are inputs to
the next select query and so on.Totally I have to fire 5 queries and
finally all the required fields are populated in a Temporary table.
Please let me know whats the best option to do this.I have tried to
implement it using nested cursors but its giving errors.Please let me
know how to using Loops(Fetch loop,While,For etc) in stored proc and
how to break out of a loop.Also I want to know how to use the IF ELSE
condition in stored proc.
If you have Linux or Unix Versoin of DB2 see
this folder has the SQL stored procedure samples
If you have windows the default path for the samples are C:\program
It has samples for SQL stored procedure.
...DB2 COBOL stored procedure to UDB SQL stored procedure
I have been given the task of taking a 3,200 line COBOL stored
procedure and duplicating the same functionality in UDB 7.2 on the
Windows platform with a procedural SQL stored procedure. I have
fiddled with procedural SQL stored procs on UDB, but mostly
short,trivial ones. Looking for good advice, links, etc. on the best
approach to this. There doesn't seem to be the concept of subroutines
within a stored proc, so I am guessing one main stored proc that may
be calling multiple smaller stored procs? Just found out about this
need late this afternoon and wondering what the best approach is to
doing such a task.
Any and all ideas most welcome.
<PJackson@txfb-ins.com> wrote in message
>I have been given the task of taking a 3,200 line COBOL stored
> procedure and duplicating the same functionality in UDB 7.2 on the
> Windows platform with a procedural SQL stored procedure. I have
> fiddled with procedural SQL stored procs on UDB, but mostly
> short,trivial ones. Looking for good advice, links, etc. on the best
> approach to this. There doesn't seem to be the concept of subroutines
> within a stored proc, so I am guessing one main stored proc that may
> be calling multiple smaller stored procs? Just found out about this
> need late this afternoon and wondering what the best approach is to
> doing such a task.
> Any...Calling Stored Procedure remotely from other stored procedure (DB2 9.7 LUW)
I'm planning an asynchronous application driven data replication.
Therefor I want to call a stored procedure in database "x" on machine
"b" from a stored procedure in database "y" on machine "a".
Any ideas out there how to do it ?
I found nothing appropriate in the manuals or examples.
On Tuesday, September 4, 2012 8:37:33 PM UTC+2, Frank Mickler wrote:
> Hello db2-experts,
> I'm planning an asynchronous application driven data replication.
> Therefor I want to call a stored procedure in database "x" on machine
> "b" from a stored procedure in database "y" on machine "a".
> Any ideas out there how to do it ?
> I found nothing appropriate in the manuals or examples.
Did you consider SQL Replication or Q Replication? They're very appropriate in this scenario(async)...
On Sep 5, 3:56=A0am, Luiz da Silva <luiz...@gmail.com> wrote:
> On Tuesday, September 4, 2012 8:37:33 PM UTC+2, Frank Mickler wrote:
> > Hello db2-experts,
> > I'm planning an asynchronous application driven data replication.
> > Therefor I want to call a stored procedure in database "x" on machine
> > "b" from a stored procedure in database &...SQL Stored Procedures X Java Stored Procedures in DB2 8.2
We are starting a large data warehousing project using DB2 8.2 on AIX.
There is a direction to move any new internal development to Java and a
question was raised: Would it be a good idea to have all stored
procedures that we might need (ETL, additional transformers, etc) for
this project and any other project written in Java as opposed to SQL/P?
Does anyone have any number or experience in terms of performance
Rafael Faria wrote:
> Hi All,
> We are starting a large data warehousing project using DB2 8.2 on AIX.
>...db2 store procedure compile error SQL1086C
DB2 stored procedure compile error
DB2 Version 7.2 + FixPak 6, Solaris 8 + Workshop CC compiler
When I compile a simplest store procedure ( in stored procedure only contain
one create table SQL statment), got the error message:
[IBM][CLI Driver][DB2/SUN] SQL1086C Anoperating system error "
Precompilation/Compilation error " occurred.
Check the log file, it show the following contents:
SQL1086C An operating system error "" occurred.
Where can I check ...COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] SQL30081N communication error on DB2/POrtal on AIX
I have installed WebSphere Portal on AIX and connected to DB2 on a
remote machine, Getting the followin errors when trying to get the
values from database thru applications installed on Portal.
Any Help Appreciated.
Thanks in advance.
IBM Workplace, India
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] SQL30081N A
communication error has been detected. Communication protocol being
used: "TCP/IP". Communication API being used: "SOCKETS". Location
where the error was detected: "18.104.22.168". Communication function
detectin...Stored procedure from stored procedure
Is it possible to create a stored procedure from a stored procedure?
When I attempt this inanity, it doesn't blow up until syntax error at
the first "end procedure" statement of the SP I'm trying to create
within the outer SP. Can this be done?
Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask).
On 24 Sep 2003 11:47:09 -0700, email@example.com (Red Valsen)
>Is it possible to create a stored procedure from a stored procedure?
>When I attempt this inanity, it doesn't blow up until syntax error at
>the first "end procedure" statement of the SP I'm trying to create
>within the outer SP. Can this be done?
>Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask).
Write it out to flatfile and execute the create from command line
John Carlson wrote:
> On 24 Sep 2003 11:47:09 -0700, firstname.lastname@example.org (Red Valsen)
>>Is it possible to create a stored procedure from a stored procedure?
>>When I attempt this inanity, it doesn't blow up until syntax error at
>>the first "end procedure" statement of the SP I'm trying to create
>>within the outer SP. Can this be done?
>>Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask).
> Write it out to flatfile and execute the create from command line
> using dbaccess???
As John said, somewhat obliquely, there is...Cannot run COBOL stored procedure on DB2
I'm trying to run simple stored procedure written in COBOL on DB2.
However I have no idea how to force it to work... :)
Procedure code is as follows (file HELLO.cob):
DISPLAY "Hello, World!".
File has been compiled to executable, static library and shared
library with TinyCOBOL compiler -> results are accordingly HELLO,
All of these files have been put to directory /home/db2inst1/sqllib/
function and their privileges have been changed to 777.
I tried to create stored procedure using these files in several ways
CREATE PROCEDURE PROCEDURE ()
EXTERNAL NAME 'HELLO'
EXTERNAL NAME 'HELLO!HELLO'
EXTERNAL NAME '/home/db2inst/sqllib/function/HELLO.so'
EXTERNAL NAME '/home/db2inst/sqllib/function/HELLO.so!HELLO'
PARAMETER STYLE SQL
But when I try to call procedure from DB2 I always get something like
Routine "PROCEDURE" (specific name "SQL090630090438500") is
implemented with code in library or path ".../sqllib/function/HELLO",
function ".../sqllib/function/HELLO" which cannot be accessed. Reason
code: "4".. SQLCODE=-444, SQLSTATE=42724, DRIVER=4.7.89
Routine "PROCEDURE" (specific name "SQL090630091203800") is
implemented wi...Stored Procedures error SQL0440N on db2 8.1 fp5
All, we are trying to create and execute our stored procs on db2 ver
8.1 fp5. This is a new database that we setup and are having some
trouble. When I try and run the stored proc from the db2 command line,
I get the following error:
SQL0440N No authorized routine named "XXX" of type
"PROCEDURE" having compatible arguments was found. SQLSTATE=42884
When the application (java) tries to execute the sp. we see the
com.ibm.db2.jcc.c.....SqlException: DB2 SQL error: SQLCODE: -444,
SQLSTATE: 42724, SQLERRMC:
We have checked to make sure that:
...SQLSTATE=HY010 Function Sequence Error- Java Stored Procedures
We are using Java Stored Procs to pull data from application DB2
tables and the final output is written to a file.
Stored Procs are structured as mentioned below:
There is a driver table from which records are extracted and then for
each of this record,subsequently, few other transaction tables are
queried and finally the record is written to a text file.
The result from the driver table is fetched into a ResultSet and we
loop through it to fetch corresponding details from other tables.
There are some stored procs where in I have to loop through multiple
for e...Convert SAP Oracle Database to IBM DB2 Database??
I would like to hear from anyone here who has converted their SAP Oracle
database to IBM DB2 database?
Did you realize greater disk saving via DB2 compression?
Did you run the latest version of DB2 rather than allow SAP to keep your
Oracle version back-leveled?
Please, DB2 Bashers need not apply, looking for the business case anyone
used to convert to DB2.
On 6 Feb, 01:52, "Charles Davis" <cdavis10...@comcast.net> wrote:
> I would like to hear from anyone here who has converted their SAP Oracle
> database to IBM DB2 d...calling a stored procedure from a stored procedure
I'm trying to define a (I thought) trivial stored procedure (SP) as
a front-end to the standard SP "dbms_system.set_ev",
which I want to use to generate a 10046 tracing event. I'm trying
to execute the following PL/SQL:
create or replace procedure rob_enable_tracing (
sid in integer,
serial in integer
dbms_system.set_ev(sid, serial, 10046, 4, '');
and getting the error:
PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared
If I provide the schema in which "set_ev" is defined, in this case "SYS",
create or replace procedure rob_enable_tracing (
sid in integer,
serial in integer
sys.dbms_system.set_ev(sid, serial, 10046, 4, '');
PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared
On May 10, 1:25 pm, RU <r...@vakuum.de> wrote:
> Hi Folks,
> I'm trying to define a (I thought) trivial stored procedure (SP) as
> a front-end to the standard SP "dbms_system.set_ev",
> which I want to use to generate a 10046 tracing event. I'm trying
> to execute the following PL/SQL:
> create or replace procedure rob_enable_tracing (
> sid in integer,
> serial in integer
> dbms_system.set_ev(sid, serial, 10046,...Connect to IBM DB2 database without installing the DB2 client.
To connect to the DB2 Driver, from a remote machine. Do we requier DB2
Can't we connect by just having IBM DB2 driver which is just a single
I am using vb application from Windows platform to connect to DB2 on
> To connect to the DB2 Driver, from a remote machine. Do we requier DB2
> Can't we connect by just having IBM DB2 driver which is just a single
> file (db2cli.dll).
> I am using vb application from Windows platform to connect to DB2 on
> Linux machine.
It's imposiblle. The IBM DB2 ODBC Driver is not only db2cli.dll. Try to
install DB2 Run Time Client Lite merge modules with your VB application
under client workstation.
Direct connecting to DB2 databases supported JDBC Type 4 Driver only.
P.S. Sorry for my bad english :(
With best regards, Dmitry.
FYI: On Windows DB2 comes with db2iprune tool.
With this utility you can reduce size of installation binaries, for
example you can reduce your DB2 runtime client to include only the
interfaces you need. The db2iprune tool is located in the
\db2\windows\utilities\db2iprune directory on DB2 product installation
So, if you are planning install DB2 client with/within your application
you can combine the tool with the response file and perform quick
-- Artur Wronski
...Trigger: To fill another Database with using Stored Procedures of the other Database
I face currently a problem where I could need some input for searching
the source of the Problem
System: SQL Server 9.0
I fill from Database A with triggers Database B, everything works
On Database B there is a Stored Procedures that checks the records and
add additional information accordingly, this Stored Procedures is
normally called by the application on "update and insert" in the
When I try to call this Stored Procedures from the Database A, the
trigger does not work anymore, even if I do a try catch over the whole
trigger, he never rea...Storing all stored procedures in one database to minimize different connection strings
In order to minimize the number of connection strings I have to use to
access different databases on the same Sql Server, I was considering
storing all stored procedures in just one database. I want to do this
because connection pooling in my application - ASP.NET is based on
this connection string. So if I need to access 6 different databases
on one sql server & set 6 different connection strings, I end up
creating 6 different connection pools.
Other than it might create more management work for the DBA, are there
any performance implications with implementing this scheme? Do stored
p...ERROR: CLI describe error: [IBM][CLI Driver][DB2/LINUXX8664]
I am kind of stuck to this... Has anyone experienced this
previously.... Following is my code...
CONNECT TO DB2(DB=DOACD011
CREATE TABLE ODS_ALL_RECORDS
SELECT * FROM CONNECTION TO
A.SUTL_RGTR_STS_DT WHEN '0001-01-01' THEN
END AS RSDT
A.STRG_ANN_INCM_DT WHEN '0001-01-01' THEN
A.STRG_NET_WRT...Calling a stored procedure from another stored procedure...
I'm quite new to PostgreSQL, started off with version 8.0 some time ago.
Recently I've begun working with stored procedures and now I've come
accross a little problem / issue I can't seem to figure out.
A quick overview of the situation:
I have one stored procedure with returns a SET OF a new (record) type
function calculate_cost(...): returns set of mytype;
This stored procedure works without a problem. I can just do a &quo...DB2 Stored procedure
I wonder how to run the CLP export/import commands (or any CLP
command) from within a stored procedure in DB2.
I am attempting to programatically import a flatfile into the database
using java without resorting to executing system commands from java.
It would be more convenient to use a stored procedure from within DB2
instead, is it possible? If so, how?
We are running DB2 8.1.
...DB2 stored procedures
How do we run stored procedures in DB2/UDB in a mainframe environment?
what are the steps necessary to do so?
You are asking a very general question ... there are many types of
stored procs. Please be more specific and perhaps someone can help you.
> Hi all,
> How do we run stored procedures in DB2/UDB in a mainframe environment?
> what are the steps necessary to do so?
...Db2 procedure error
Hi Folks ,
I am few days old to the world of DB2 and right now i am stuck into a
problem that seems to be getting difficult for me.
Please have alook into this and provide me some help.
Below is one such procedure that i have written ,
pads@disrs16:/home/pads/temp>db2 -td@ -vf proc2.db2
CREATE PROCEDURE new_procedure1 ()
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H....stored procedure error
I'm trying to call a ms sqlserver stored procedure in vb that takes in
a varchar input parameter.
here's the vb code:
Dim connection As ADODB.connection
Dim command As ADODB.command
Dim user As String
user = "john_smith"
Set connection = New ADODB.connection
Set command = New ADODB.command
Set command.ActiveConnection = connection
command.CommandType = adCmdStoredProc
command.CommandText = "user_save"
comman...Error in store procedure
I've the following SP:
CREATE PROCEDURE spServicios
@resultado int OUTPUT
SET ROWCOUNT 1
UPDATE control SET registro = getdate()
WHERE (fecha >= getdate()
AND fecha < dateadd(mi, @maxdif, getdate())
AND clientes_codigo IN (SELECT
clientes_codigo FROM telefonos WHERE numero = @numero)
SELECT @resultado = @@rowcount
SET ROWCOUNT 0
But when I try to compile I get the message: Incorrect syntax near the
keyword 'S...Errors in stored procedure
I can not get any error from a stored procedure, it seem the execution
was succeful even if there was primary key violations, unknow foreign
key or RAISERROR.
Have you same hints for me? Thanks in advance.
This is a code snippet:
oPars := oCmd:PARAMETERS_
oCmd:CommandText := "myProc"
oCmd:CommandType := AdCmdStoredProc
oPars := oCmd:PARAMETERS_