473,689 Members | 3,013 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to convert a stored-proc with one result-set into a table udf

Hi,

I don't have much experience writting UDFs, so I don't know if this is
possible (and simple). I am basically looking to write a wrapper table
UDF that encapsulates reorgchk_tb_sta ts and reorgchk_ix_sta ts
procedures, so I can easily manipulate the result set using SQL.

TIA

P. Adhia

Jun 1 '06 #1
7 3839
Hi.

You have to use external udf (c, java for example) to do this.

Sincerely,
Mark B.
Hi,

I don't have much experience writting UDFs, so I don't know if this is
possible (and simple). I am basically looking to write a wrapper table
UDF that encapsulates reorgchk_tb_sta ts and reorgchk_ix_sta ts
procedures, so I can easily manipulate the result set using SQL.

TIA

P. Adhia


Jun 2 '06 #2
4.****@mail.ru wrote:
You have to use external udf (c, java for example) to do this.


Thanks for the response. It isn't as easy as I thought it would be.

I guess my next best option is to write queries against catalog tables.
Just so that I don't reinvent the wheel, has anyone attempted to create
views (or set of queries) on catalog tables that mimic the result-sets
returned by reorgchk_tb_sta ts and reorgchk_ix_sta ts?

Thanks again

P Adhia

Jun 2 '06 #3
Try something like this:
CREATE FUNCTION FUNC_REORGCHCK ()
RETURNS TABLE (
TABLE_SCHEMA VARCHAR(128)
,TABLE_NAME VARCHAR(128)
,CARD INTEGER
,OVERFLOW INTEGER
,NPAGES INTEGER
,FPAGES INTEGER
,ACTIVE_BLOCKS INTEGER
,TSIZE INTEGER
,F1 INTEGER
,F2 INTEGER
,F3 INTEGER
,REORG INTEGER
)
SPECIFIC DB2INST1.FUNC_R EORGCHK
F1: BEGIN ATOMIC
-- LOOP variables
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;

-- DCGT to hold output of call to store proc
DECLARE GLOBAL TEMPORARY TABLE TB_REORGCHK
(
TABLE_SCHEMA VARCHAR(128)
,TABLE_NAME VARCHAR(128)
,CARD INTEGER
,OVERFLOW INTEGER
,NPAGES INTEGER
,FPAGES INTEGER
,ACTIVE_BLOCKS INTEGER
,TSIZE INTEGER
,F1 INTEGER
,F2 INTEGER
,F3 INTEGER
,REORG INTEGER
)

CALL reorgchk_tb_sta ts('T','ALL');
ASSOCIATE RESULT SET LOCATORS (LOC1)
WITH PROCEDURE reorgch_tb_stat s;
ALLOCATE c1 CURSOR FOR RESULT SET loc1;

fetch_loop:
REPEAT
FETCH rsCur INTO
TABLE_SCHEMA
,TABLE_NAME
,CARD
,OVERFLOW
,NPAGES
,FPAGES
,ACTIVE_BLOCKS
,TSIZE
,F1
,F2
,F3
,REORG;

INSERT INTO SESSION.TB_REOR GCHK VALUES (
TABLE_SCHEMA
,TABLE_NAME
,CARD
,OVERFLOW
,NPAGES
,FPAGES
,ACTIVE_BLOCKS
,TSIZE
,F1
,F2
,F3
,REORG)
UNTIL at_end > 0
END REPEAT fetch_loop;
RETURN
SELECT *
FROM SESSION.TB_RERO GCHK

END
@


Norm
P. Adhia wrote:
4.****@mail.ru wrote:
You have to use external udf (c, java for example) to do this.


Thanks for the response. It isn't as easy as I thought it would be.

I guess my next best option is to write queries against catalog tables.
Just so that I don't reinvent the wheel, has anyone attempted to create
views (or set of queries) on catalog tables that mimic the result-sets
returned by reorgchk_tb_sta ts and reorgchk_ix_sta ts?

Thanks again

P Adhia


Jun 2 '06 #4
Norm wrote:
fetch_loop:
REPEAT
FETCH rsCur INTO
TABLE_SCHEMA
,TABLE_NAME
,CARD
,OVERFLOW
,NPAGES
,FPAGES
,ACTIVE_BLOCKS
,TSIZE
,F1
,F2
,F3
,REORG;

INSERT INTO SESSION.TB_REOR GCHK VALUES (
TABLE_SCHEMA
,TABLE_NAME
,CARD
,OVERFLOW
,NPAGES
,FPAGES
,ACTIVE_BLOCKS
,TSIZE
,F1
,F2
,F3
,REORG)
UNTIL at_end > 0
END REPEAT fetch_loop;


Doesn't the last row in the cursor get inserted twice in this fetch
loop?

Jun 2 '06 #5
You can't
declare handlers,
declare session tables,
issue ASSOCIATE LOCATORS (ALLOCATE CURSOR),
issue FETCH
in SQL funcions.

That is the reason to use external UDF.

Sincerely,
Mark B.

Jun 5 '06 #6
Norm wrote:
Try something like this:
CREATE FUNCTION FUNC_REORGCHCK ()


Just wanted to thank you for making the suggestion. As one of the other
poster predicted, I ran into an error that pointed at unsupported
DECLARE HANDLER statement.

I am not sure why reorgchk_* are stored-procs, whereas most other
commands/utilities have a function instead (probably these were created
in pre-function days). Anyways, I at least (hopefully correctly)
learned that it's easier to convert a table function to a stored-proc,
rahter than the other way around. If IBM ever creates UDFs for REORGCHK
command, the existing stored-procedures, could be simple wrappers
around new UDFs.

Thanks

P Adhia

Jun 5 '06 #7

4.****@mail.ru wrote:
You can't
declare handlers,
declare session tables,
issue ASSOCIATE LOCATORS (ALLOCATE CURSOR),
issue FETCH
in SQL funcions.

That is the reason to use external UDF.

Sincerely,
Mark B.


But you can use those features in SQL procedures, right? If so, could
you call such a procedure from within a SQL function?

Regards
JohnO

Jun 8 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
976
by: JMorrell | last post by:
We have a User Function that gets called from Access. We now have a need to use that same User Function in a VB application. Is there a way to convert the User Function to a Stored Procedure? Do I need to convert it? I'm wondering because the Server Explorer toolbar shows Stored Procedures. I'm pretty green about this, so any help is appreciated. tia, -- JMorrell
1
6479
by: Matt | last post by:
I would like to convert a couple informix stored procedures to SQL Server stored procedures. I have no idea how to accomplish this. Here is an example of one of the procedures I need to convert. drop function mnaf_calc_calendar_quarter; CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date) --***************************************************************************** -- Name: mnaf_calc_calendar_quarter -- Description/Notes:
25
4371
by: cory | last post by:
Hi, I have an Access database and am having an ASP.NEt application written for it. It is almost complete. I have a hosting company that I signed up with a month ago but before I did anything I asked them if Access and ASP.NET would work on their servers, they said yes so I bought in. Now they are saying my application wont work on their servers using MSaccess and I can only use SQL or asp 3.0. They are saying Microsoft is trying to...
3
63086
by: rishabhshrivastava | last post by:
Hello All, How can I convert a value to Double in JavaScript??? In vbscript i believe its done as cDbl(Value) I tried lots of way but getting a value of "NaN". Any suggestions/ideas will be truely appreciated.
1
1785
by: nde_plume | last post by:
I have a SQL Server database that is connected to a client server program via ADO. I want this same program to be able to use JET as the database back end (to provide a light alternative that doesn't require a full install of MSDE or SQL Server.) I know that I can't necessarily get a fully automated conversion, but I was wondering what advice group members would have to make this conversion as painless as possible (not to mention keeping...
6
3009
by: tshad | last post by:
Apparently, I can't do: Dim da2 As New OleDb.OleDbDataAdapter("Select PR, Convert(varchar,getchar(),1),F1, F2, F5, Sum(F4) from temp .... I am getting this error. 'undefined function "convert" in expression'
3
8739
by: mrajanikrishna | last post by:
Hi Friends, I am accepting a number from the user entered in a textbox. I want to assign to a variable in my code and assignt this to that variable. double num1 = (double)txtNum1.text; this produced an error
8
3681
by: Cartoper | last post by:
I have an image stored in a HGlobal and would like to load it into a System::Image via the System::Image::FromStream(). How do I convert the HGlobal into a stream?
12
13541
by: Peter | last post by:
Trying to convert string to byte array. the following code returns byte array of {107, 62, 194, 139, 64} how can I convert this string to a byte array of {107, 62, 139, 65} System.Text.UTF8Encoding str = new System.Text.UTF8Encoding(); string s = new string((char)107, 1); s += new string((char)62, 1);
4
3721
by: Tim Kelley | last post by:
Is it possible to store a sql2000 timestamp field to a variable in C#. I am creating a dataset from a stored procedure and one of the fields is a timestamp field. I have created a variable of type binary, but I am not sure how to convert to data in the dataset. Thanks.
0
8528
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9076
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8786
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7621
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6454
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5810
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4321
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4547
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2965
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.