Create a cursor and fill it dynamically

[Wiggy]

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

[Rhino]

"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

[Gert van der Kooij]

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.

[William G. Yoder]

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