CURSORS AND manipulating data into a temporary table variable OR global table variable

Help T-SQL Server developer lost trying to implement some simple
cursor logic in DB2!

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
nebestAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
hi nebest,

This is what happens when Microsoft is allowed to play with the big boys.  They've taken the SELECT .. INTO .. statement and completely changed the functionality.

UDB/DB2 (by the way, the originators of the SQL language) uses SELECT INTO to put the selected object into a variable.  Microsoft changed that functionality and uses it to store the object(s) in another table.  In T-SQL, SELECT INTO mimics an INSERT.

You can duplicate a table's structure like this:

  CREATE TABLE new AS (SELECT * FROM old) DEFINITION ONLY;


My experience is that chasing a cursor around a database is slower, more prone to errors, and takes longer to code/debug than pure SQL.  The cursor approach does all of the same work, but you remove the RDBMS ability to operate on blocks of data by fetching and processing the rows one at a time.

The DB2 documentation is here:

  http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp

Select Reference (in the left window) and expand SQL.


Good Luck,
Kent
0
momi_sabagCommented:
how about trying to code all of the above like this :

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)

union all


select *
from  dbo.Master_SRC_With_Dups
where (Master_Id,distinctive_Field) in
       (select Master_Id, max(distinctive_Field)
      from dbo.Master_SRC_With_Dups
       group by Master_Id
       having count(*) >1)


you can also do it using table expressions, some thing like

with ids_table (master_id, distinctive_field, is_distinct)
as
select Master_Id, max(distinctive_Field) , case when count(*) =1 then 1 else 0 end
      from dbo.Master_SRC_With_Dups
       group by Master_Id


select *
from  dbo.Master_SRC_With_Dups t1
 inner join ids_table t2
on t1.master_id = t2.master_id
where t2.is_distinct = 1
 or   (t2.is_distinct = 0 and t1.distinctive_field = t2.distinctive_field)

which should run faster

0
Kent OlsenData Warehouse Architect / DBACommented:
Hi nebest,

> the data keeps changing so I'm not certain what the distinct field is.

That's a problem no matter what solution you choose.  It's one for which you need a good understanding.

If you only want 100 rows out of 1,000,000, write SQL that efficiently does that.

If there's a unique key, perhaps a posting timestamp, then the entire process reduces to the following query:

INSERT INTO sometable (Master_ID, recent_timestamp)
SELECT Master_ID,
  max (posting_timestamp) over (partition by Master_ID)
FROM Master_SRC_With_Dups;

That's a lot easier than chasing a cursor, and if the table is appropriately indexed, very, very fast.


Good Luck,
Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.