CURSORS AND manipulating data into a temporary table variable OR global table variable
SQLBusiness...@gmail.com
cursor logic!
I need to create a "table object (variable)" OR "TEMP variable" OR
"create and drop a table in a procedure" in DB2 to eliminate duplicate
records from my query.
In the procedure I simply want to insert unique rows into this
temporary table ... using a cursor to insert distinct rows by using
the (FETCH FIRST 1 ROW ONLY) and (ORDER BY) clauses; into my result
set. I know this can be done with a group by but the code gets too
messy(and the qery becomes inefficient) (also a temp table would make
sense ince the result set is less than a 100 rows out of millions of
records), and the data keeps changing so I'm not certain what the
distinct field is.
Can anyone help with code or pseud-code or link to a good tutorial. I
cannot find simple db2 cursor examples or examples on how to create
temporary tables or global variables. Please help!
My code in T-SQL would look like:
**************************************************************************
create procedure returnUniqueMasterRecords
as
insert into #distinct_Data
select * from dbo.Master_SRC_With_Dups where Master_Id in
(select Master_Id from dbo.Master_SRC_With_Dups
group by Master_Id
having count(*) =1)
declare @Var_ID varchar(30)
DECLARE MasterDup_cursor CURSOR FOR
select distinct Master_Id from dbo.Master_SRC_With_Dups where
Master_Id in
(select Master_Id from dbo.Master_SRC_With_Dups
group by Master_Id
having count(*) >1)
order by Master_Id desc
OPEN MasterDup_cursor
FETCH NEXT FROM MasterDup_cursor
INTO @Var_ID
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #distinct_Data
select top 1 * from dbo.Master_SRC_With_Dups where Master_Id =
@Var_ID
order by distinctive_Field desc
FETCH NEXT FROM MasterDup_cursor INTO @Var_ID
END
CLOSE Tower_cursor
DEALLOCATE Tower_cursor
select * from #distinct_Data
return 0
**************************************************************************
I queried the database version in the event that this makes a
difference:
SERVEICE_LEVEL FIXPACK_NUM
DB2 v8.1.1.112 12
SELECT service_level, fixpack_num FROM TABLE
(sysproc.env_get_inst_info())
as INSTANCEINFO
Serge Rielau
I see lots of room for optimization in your logic.
What you are looking for is the DECLARE GLOBAL TEMPORARY TABLE statement.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
--CELKO--
SELECT X.master_id, etc.,
FROM (SELECT master_id, etc.,
ROW_NUMBER()
OVER(PARTITION BY master_id
ORDER BY distinctive_field DESC) AS rn
FROM Foobar) AS X
WHERE X.rn = 1;
Did you notice that you used the dummy name "distinctive_field" and
not "distinctive_column"? That's why you are looking for cursors and
temp tables! You are not writing SQL yet, but only faking a magnetic
tape file system in an old procedural language with SQL. You don't
know that columns are nothing like fields, that tables -- unlike files
-- can be virtual, etc.
Your entire approach to SQL is wrong and you need to stop programming
until you get a basic education in RDBMS.
jefftyzzer
Maybe this will help:
CREATE PROCEDURE DGTT_EXAMPLE()
SPECIFIC DGTT_EXAMPLE
INHERIT SPECIAL REGISTERS
CALLED ON NULL INPUT
DYNAMIC RESULT SETS 1
BEGIN
-- Declare variables
DECLARE V_NO_DATA SMALLINT DEFAULT 0;--
DECLARE V_DEADLOCK_OR_LTO SMALLINT DEFAULT 0;--
DECLARE V_RETURN CHAR(31) DEFAULT 'SELECT * FROM
SESSION.DISTINCT_DATA';--
-- Declare conditions
DECLARE C_DEADLOCK_OR_LTO CONDITION FOR SQLSTATE '40001';--
-- Declare cursor for dynamic SQL statement for the result set
DECLARE C_RETURN CURSOR WITH RETURN TO CALLER FOR S_RETURN;--
-- Declare handlers
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET V_NO_DATA = 1;--
DECLARE CONTINUE HANDLER FOR C_DEADLOCK_OR_LTO
SET V_DEADLOCK_OR_LTO = 1;--
DECLARE GLOBAL TEMPORARY TABLE
SESSION.DISTINCT_DATA
(
MASTER_ID BIGINT,
ETC....
)
ON COMMIT PRESERVE ROWS
NOT LOGGED
ON ROLLBACK DELETE ROWS
WITH REPLACE;--
INSERT INTO
SESSION.DISTINCT_DATA
(
MASTER_ID,
ETC....
)
VALUES
(
ETC.....
);--
CREATE INDEX SESSION.DD_N1 ON SESSION.DISTINCT_DATA (MASTER_ID) ALLOW
REVERSE SCANS;--
CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE
SESSION.SESSION.DISTINCT_DATA WITH DISTRIBUTION AND DETAILED INDEXES
ALL');--
<<DO MORE STUFF HERE>>
-- Prepare the result set cursor and return its contents to the SP
caller
PREPARE S_RETURN FROM V_RETURN;--
OPEN C_RETURN;--
END;
--Jeff
jefftyzzer
Typo: the RUNSTATS line should read with one less "SESSION" in it,
i.e.,
CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE SESSION.DISTINCT_DATA WITH
DISTRIBUTION AND DETAILED INDEXES
ALL');--
--Jeff