By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,007 Members | 1,248 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,007 IT Pros & Developers. It's quick & easy.

Create a cursor and fill it dynamically

P: n/a
Hi,

It's probably easiest if I describe what I'm trying to do:

I have several tables I want to base a query on. In addition I have
some dynamic data that I want to join against that consists of several
records of information.

I could just create a temp table of the dynamic data and join it
against my tables, but I thought there was a way to create a cursor
(not based on a table i.e. with a select statement), fill it with the
dynamic data, and join the cursor against the other tables to get my
desired results.

The reason I wanted to do it this was for performance. I'd rather not
incur the hit of writing a temp table to a hard drive, so I thought
the cursor would keep it in memory (of course as long as it doesn't
force memory to get paged out to disk).

I'm not that familiar with DB2. I've read a little about temporary
tables and the temporary global table space. Would using a temp table
be more efficient than my proposal? If so, could you please explain
why? Does the DB not write a temp table to disk if it doesn't need to?

Thanks very much in advance,
William G. Yoder
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"Wiggy" <sp*****@yoder.org> wrote in message
news:cd**************************@posting.google.c om...
Hi,

It's probably easiest if I describe what I'm trying to do:

I have several tables I want to base a query on. In addition I have
some dynamic data that I want to join against that consists of several
records of information.

I could just create a temp table of the dynamic data and join it
against my tables, but I thought there was a way to create a cursor
(not based on a table i.e. with a select statement), fill it with the
dynamic data, and join the cursor against the other tables to get my
desired results.

The reason I wanted to do it this was for performance. I'd rather not
incur the hit of writing a temp table to a hard drive, so I thought
the cursor would keep it in memory (of course as long as it doesn't
force memory to get paged out to disk).

I'm not that familiar with DB2. I've read a little about temporary
tables and the temporary global table space. Would using a temp table
be more efficient than my proposal? If so, could you please explain
why? Does the DB not write a temp table to disk if it doesn't need to?

A table function may be a better solution to your problem. They are capable
of reading data that is not in a DB2 table, such as in a flat file or some
other format. You can then join data found by the table function to other
data from DB2 tables.

I don't recall how table functions handle memory and/or disk space but it
should be in the DB2 manuals somewhere.

Rhino
Nov 12 '05 #2

P: n/a
In article <cd**************************@posting.google.com >, Wiggy
(sp*****@yoder.org) says...
Hi,

It's probably easiest if I describe what I'm trying to do:

I have several tables I want to base a query on. In addition I have
some dynamic data that I want to join against that consists of several
records of information.


Something like this:

WITH temp1 (col1, col2, col3) AS
(VALUES ( 0, ?AA?, 0.00),
( 1, ?BB?, 1.11),
( 2, ?CC?, 2.22)
)

This example is copied from Graeme Birchall's SQL Cookbook which you
can find at
http://ourworld.compuserve.com/homep...l/HTM_COOK.HTM

Hope this helps.
Nov 12 '05 #3

P: n/a
Gert & Rhino. Thanks very much for the responses!

sp*****@yoder.org (Wiggy) wrote in message news:<cd**************************@posting.google. com>...
Hi,

It's probably easiest if I describe what I'm trying to do:

I have several tables I want to base a query on. In addition I have
some dynamic data that I want to join against that consists of several
records of information.

I could just create a temp table of the dynamic data and join it
against my tables, but I thought there was a way to create a cursor
(not based on a table i.e. with a select statement), fill it with the
dynamic data, and join the cursor against the other tables to get my
desired results.

The reason I wanted to do it this was for performance. I'd rather not
incur the hit of writing a temp table to a hard drive, so I thought
the cursor would keep it in memory (of course as long as it doesn't
force memory to get paged out to disk).

I'm not that familiar with DB2. I've read a little about temporary
tables and the temporary global table space. Would using a temp table
be more efficient than my proposal? If so, could you please explain
why? Does the DB not write a temp table to disk if it doesn't need to?

Thanks very much in advance,
William G. Yoder

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.