DB2

DB2 is IBM's offering to the highend database market. The latest version of DB2 (Universal Database) is ideal for OLTP, Data Warehousing, Decision Support and everything in between. It's well priced, extremely scalable and runs on virtually every platform out there from handhelds to mainframes.

Create PROCEDURE using output "TABLE LIKE"


I'm attempting to return a result set as an out parameter in a stored procedure using: CREATE PROCEDURE GetReconnectJobs ( OUT o_data TABLE LIKE GETRECONNECTJOBS_VIEW AS LOCATOR ) BEGIN SELECT fields1..n INTO o_data FROM cibjjo.GetReconnectJobs_VIEW; END; But am getting error: SQL0408N A value is not compatible with the data type of its assignment target. Target name is "O_DATA". Any ideas? Oracle has a ref_cursor, and I'm assuming this is the DB2 equivalent of that???
Originally Posted by Jim313
I'm assuming this is the DB2 equivalent of that???
Your assumption is incorrect.
Example: Returning a REF CURSOR from a procedure (PL/SQL) - IBM DB2 9.7 for Linux, UNIX, and Windows
Is there a way to return a results set in DB2 v9.1? The link you provided is for pl/sql using a ref_cursor.
Returning result sets from SQL procedures
I have it compiling ok and will be testing it, but this seemed to work. Thank you!
I can open a cursor and return the results from the SELECT, but I need to update a status flag before I retrieve the records and updated it again after the SELECT. Adding either update statement to the stored procedure causes it to not compile. I tried adding begin-end blocks around each of the Updates, but that didn't seem to matter. Any suggestions on how I can resolve this?
CREATE PROCEDURE GetReconnectJobs ( )
LANGUAGE SQL
CALLED ON NULL INPUT
MODIFIES SQL DATA
OPTHINT ' '
REOPT ONCE
DYNAMIC RESULT SETS 1
begin-- ADDING THIS UPDATE STATEMENT BREAKS THE PROCEDURE AND CAUSES IT TO NOT COMPILE
-- reserve records for select below...
UPDATE T14TOPS.CI006v
SET RP_SURV_FLPAL_FLG = 'X' -- in transit
where RP_SURV_FLPAL_FLG = 'N';
end;
Re: BEGIN
DECLARE c_ReonnRecs CURSOR WITH RETURN FOR
SELECT
CUST_NBR,
PREM_NBR,
FO_NBR,
MTR_NES_ID,
RECN_RCPT_CK_AMT,
FO_INIT_CMT_TXT,
ORDPEN_EFF_DAT,
FO_TYPE_CDE,
AS_CALL_NBR,
FO_RSN_ISS_CDE,
FO_ORG_DAT,
FO_ORG_TIM,
ORD_STAT_ID,
CUST_NAM,
CUST_PHN_NBR,
HSE_NBR,
ST_NAM,
APT_ID,
ZIP_CDE,
MTR_CONSTA_EXIS_ID,
XFMR_POLEPAD_NBR,
MTR_LOC_CDE,
AMI_RD_FLG -- AMI = 'Y'
FROM GetReconnectJobs_VIEW
where RP_SURV_FLPAL_FLG = 'X';
end Re;
begin -- ADDING THIS UPDATE STATEMENT BREAKS THE PROCEDURE AND CAUSES IT TO NOT COMPILE
-- un-reserve records, and set status to In-process
UPDATE T14TOPS.CI006v
SET RP_SURV_FLPAL_FLG = 'I' -- In-process
where RP_SURV_FLPAL_FLG = 'X';
end;
OPEN c_ReonnRecs;
END
;
Originally Posted by Jim313
Adding this breaks the proceedure.
This isn't very informative. I guess we'll just wait for mindreaders to help diagnose the problem.

Related Links

Stupid question
DB2 Backup with TSM failing with SQL2062N and Reason Code 610
Task Center - User Interface Scheduling
Full tablescan
ON DELETE RESTRICT vs ON DELETE NO ACTION
DB2 scheduled backup
DB2 Performance problem
Fetch in DB2 stored procedure
how can I start "db2cc"!
query optimizer runs astray....
Migration AS/400 DB2 to Windows DB2 8.1
Creating a new database with the existing database with same structure
load error SQL0302N
load error SQL0302N
Db2 Udb Unix Aix
Dropping and recreating a primary key which has already unique index

Categories

DataBase Dev
MongoDB
Adabas
DB2
Informix
Microsoft SQL Server
MySQL
Oracle
Pervasive.SQL
PostgreSQL
Sybase
Other
ASP
Crystal Reports
Delphi, C etc
JAVA
Perl and the DBI
PHP
ANSI SQL
Unix Shell Scripts
Visual Basic
Brilliant Database
Corel Paradox
FileMaker
Microsoft Access
Microsoft Excel
Other PC Databases
New Members & Introduc...
Applications & Tools
Database Concepts & De...
Chit Chat
Marketplace
Job Opportunities
Suggestions & Feedback

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App