Hi Edwards,
I am getting the following error when I compile the below code.
SQL0526N the requested function does not apply to declared temporary tables.
Please advice.
Thanks & Regards
Mujeeb
-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x59325515.[Email address protected]
Sent: Tuesday, October 30, 2007 4:30 PM
To: LazyDBA Discussion
Subject: SPAM-LOW: RE: RE: HI all Oracle to DB2 conversion problem
Hey Mujeeb,
Did you get your code below to work?
-----Original Message-----
From: Mujeeb
[mailto:db2udbdba-ezmlmshield-x99162102.[Email address protected]
Sent: Monday, October 29, 2007 9:44 AM
To: LazyDBA Discussion
Subject: RE: RE: HI all Oracle to DB2 conversion problem
Sorry Edwards, Please ignore earlier function. This is the one I am
struck
up with.
create function get_childs(id integer)
returns TABLE (id_no integer)
LANGUAGE SQL
READS SQL DATA
begin atomic
DECLARE level INTEGER DEFAULT 1;
declare v_id integer;
set level=1;
insert into session.rownum1
select sec_resid from sec_res_master where sec_resparentid=id;
SET V_ID =(SELECT MIN(ID) FROM session.rownum1) ;
while v_id is not null do
insert into session.rownum1
select sec_resid from sec_res_master where sec_resparentid=v_id and
sec_resid not in (select min(id) from session.rownum1);
SET V_ID =(SELECT MIN(ID) FROM session.rownum1 where id>v_id) ;
end while;
return select id from session.rownum1 ;
end @
Thanks & Regards
Mujeeb
-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x15931951.[Email address protected]
Sent: Monday, October 29, 2007 1:35 AM
To: LazyDBA Discussion
Subject: SPAM-LOW: RE: HI all Oracle to DB2 conversion problem
I. Using the DB2 Control Center
--FIRST, CREATE A USER TEMPORARY TABLESPACE USING THE CONTROL CENTER
CONNECT TO SAMPLE;
CREATE USER TEMPORARY TABLESPACE TEMP1 PAGESIZE 4 K
MANAGED BY SYSTEM
USING ('D:\DB2\TEMP1\temp1\TEMP1.DBF' )
EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16
TRANSFERRATE 0.14 BUFFERPOOL IBMDEFAULTBP;
CONNECT RESET;
--SECOND, CREATE GLOBAL TEMPORARY TABLE IN THE CONTROL CENTER
DECLARE GLOBAL TEMPORARY TABLE ROWNUM1
LIKE EMPLOYEE
ON COMMIT PRESERVE ROWS NOT LOGGED IN TEMP1;
INSERT INTO SESSION.ROWNUM1 SELECT * FROM EMPLOYEE WHERE WORKDEPT =
'D11';
OR
INSERT INTO SESSION.ROWNUM1
(SELECT empno, firstnme, midinit, lastname, workdept, phoneno,
hiredate,
job, edlevel, sex, birthdate, salary, bonus, comm
FROM employee TB1,
TABLE (SELECT COUNT(*) + 1 AS ROW#
FROM employee TB2
WHERE TB2.EMPNO < TB1.EMPNO) AS TEMP_TAB
WHERE ROW# between 2 and 11);
OR
INSERT INTO SESSION.ROWNUM1
(SELECT SUBSTR(GENERATE_UNIQUE(),1,9) AS ID1, empno
FROM employee TB1,
TABLE (SELECT COUNT(*) + 1 AS ROW#
FROM employee TB2
WHERE TB2.EMPNO < TB1.EMPNO) AS TEMP_TAB);
SELECT * FROM SESSION.ROWNUM1;
CREATE INDEX SESSION.TEMPROWNUM1INDEX ON SESSION.ROWNUM1(empno);
DELETE FROM EMPLOYEE E2
WHERE EMPNO =
(SELECT EMPNO
FROM SESSION.ROWNUM1 E1
WHERE E2.EMPNO = E1.EMPNO);
SELECT * FROM EMPLOYEE;
II.
--AT COMMAND PROMPT
1. db2cmd
2. db2 CONNECT TO SAMPLE
3. db2 DECLARE GLOBAL TEMPORARY TABLE ROWNUM1 LIKE EMPLOYEE ON COMMIT
PRESERVE ROWS NOT LOGGED IN TEMP1
4. db2 INSERT INTO SESSION.ROWNUM1 SELECT * FROM EMPLOYEE WHERE
WORKDEPT = 'D11'
5. db2 SELECT * FROM SESSION.ROWNUM1;
Please take a few minutes to provide feedback on the quality of service
you
received from our staff. The Department of Education values your
feedback as
a customer. Commissioner of Education Jeanine Blomberg is committed to
continuously assessing and improving the level and quality of services
provided to you.Simply use the link below. Thank you in advance for
completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
-----Original Message-----
From: Mujeeb
[mailto:db2udbdba-ezmlmshield-x48201050.[Email address protected]
Sent: Saturday, October 27, 2007 9:27 AM
To: LazyDBA Discussion
Subject: RE: HI all Oracle to DB2 conversion problem
I need some more information on the table function example 2.
As in Oracle I take data into collection (bulk collect) and then use
the
collection as table in other queries, for example I take all the Childs
of a
parentid and use it through out function instead of querying multiple
times.
And assign the required output to another object (types) and then get
output
of the data by returning the collection.
In MSSQL we converted the functions by declaring tables and inserting
the
data into those tables to use in queries, same as above to store the
Childs.
Finally insert the required output into the table declared for
returning.
For the purpose only I given example of mssql how we are inserting the
data
into the declared table in the function and returning the table.
I hope I could somewhat able to put forward my requirement. Please give
me a
one simple example. It will be of a great help to me and I will be able
do
finish conversion in time.
Mujeeb
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page