DECLARE CURSOR DATABASE option

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank Swarbrick - 22 Jan 2008 22:00 GMT

I like the new feature that allows you to load data from a table in another
database without using data federation (servers, nicknames and user mappings
not required). One feature I miss, however, with doing it the "old" way is
the use of CREATE with the LIKE option. I don't suppose there is some
similar feature for the CREATE statement. Something like:

CREATE TABLE local_scheme_name.table_name
LIKE remote_schema_name.table_name
OF DATABASE remotedb
USER remote_user
USING remote_password;

I don't see it documented, but I can hope can't I?

(Give an inch and they ask for a mile!)

Frank

Reply to this Message

Serge Rielau - 22 Jan 2008 22:26 GMT

> I like the new feature that allows you to load data from a table in another
> database without using data federation (servers, nicknames and user mappings
[quoted text clipped - 11 lines]
>
> (Give an inch and they ask for a mile!)

Indeed they do.
No that feature does not exist..
Now, given that create table isn't a day-to-day operation (unlike LOAD),
what's wrong with db2look or db2move?

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Frank Swarbrick - 23 Jan 2008 00:37 GMT

>>> On 1/22/2008 at 3:26 PM, in message

<5vn8t2F1nbqatU1@mid.individual.net>,

>> I like the new feature that allows you to load data from a table in
> another
>> database without using data federation (servers, nicknames and user
> mappings
>> not required). One feature I miss, however, with doing it the "old" way

> is
>> the use of CREATE with the LIKE option. I don't suppose there is some
[quoted text clipped - 13 lines]
> Now, given that create table isn't a day-to-day operation (unlike LOAD),
> what's wrong with db2look or db2move?

I just thought that it would be handy. Not really necessary, as you say.

Thanks,
Frank

Reply to this Message

Lennart - 23 Jan 2008 12:07 GMT

On Jan 22, 11:00 pm, "Frank Swarbrick"
<Frank.Swarbr...@efirstbank.com> wrote:

> I like the new feature that allows you to load data from a table in another
> database without using data federation (servers, nicknames and user mappings
> not required).

Do you have a link to where this is described?

/Lennart

[...]

Reply to this Message

Frank Swarbrick - 23 Jan 2008 16:58 GMT

>>> On 1/23/2008 at 5:07 AM, in message

<0752652f-e99c-42b0-b638-e227857a0b55@j78g2000hsd.googlegroups.com>,

> On Jan 22, 11:00 pm, "Frank Swarbrick"
> <Frank.Swarbr...@efirstbank.com> wrote:
[quoted text clipped - 5 lines]
>
> Do you have a link to where this is described?

There is information about it in the Data Movement Utilities manual for DB2
version 9 under the heading "Moving data using the CURSOR file type".
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.
db2.udb.admin.doc/doc/c0005437.htm

Strangely, this option does not appear to be described in SQL Reference
Volume 2 for either DB2 9.1 or DB2 9.5.

Anyway, here is an example. This will load every row from table
SAFEBOX.TEST in remote database 'core' to table SAFEBOX.TEST in database
'mydb'. Database 'core' must be cataloged to the server that 'mydb' resides
on.

CONNECT TO mydb USER myuserid;
DECLARE mycurs CURSOR
DATABASE core
USER coremgr
USING ********
FOR SELECT * FROM SAFEBOX.TEST;
LOAD FROM mycurs OF CURSOR
REPLACE INTO SAFEBOX.TEST;
CONNECT RESET;

And here is the same example using the ADMIN_CMD SP:
CALL SYSPROC.ADMIN_CMD('LOAD FROM (DATABASE core SELECT * FROM safebox.test)
OF CURSOR MESSAGES ON SERVER REPLACE INTO safebox.test');

When using the SP you cannot supply a user ID and password, so the user ID
and password. It uses the user ID and password of the local server you are
logged on, so the same credentials must be in place on the remote server.
This is documented here:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.
db2.udb.admin.doc/doc/r0023577.htm

specifically: "When the DATABASE database-alias clause is included prior to
the query statement in the parentheses, the LOAD command will attempt to
load the data using the query-statement from the given database as indicated
by the database-alias name, which is defined on the server. It must point to
a database exist on the server, and is a different database that the
application is currently connected to. Note that the LOAD will be executed
using the user ID and password explicitly provided for the currently
connected database (an implicit connection will cause the LOAD to fail)."

Frank

Reply to this Message

Lennart - 24 Jan 2008 07:19 GMT

On Jan 23, 5:58 pm, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
wrote:

> >>> On 1/23/2008 at 5:07 AM, in message
>
[quoted text clipped - 54 lines]
>
> Frank

Thanx a lot for the info Frank. I'm a bit busy at the moment, but I
will have a closer look as soon as possible.

/Lennart

Reply to this Message

bwmiller16@gmail.com - 24 Jan 2008 16:00 GMT

> On Jan 23, 5:58 pm, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
> wrote:
[quoted text clipped - 64 lines]
>
> - Show quoted text -

How many other gems are in DB2 that I didn't know about! Even when I
try to keep-up on the vast topic of DB2 it is impossible.

Thanks to Frank for asking this question.

-B

Reply to this Message

Frank Swarbrick - 25 Jan 2008 18:01 GMT

>>> On 1/24/2008 at 9:00 AM, in message

<e2cab84c-186f-4ecc-a1a4-c818ac31778f@d4g2000prg.googlegroups.com>,

> How many other gems are in DB2 that I didn't know about! Even when I
> try to keep-up on the vast topic of DB2 it is impossible.
>
> Thanks to Frank for asking this question.

Now you have me wondering where on earth I learned about this new feature.
It is in the "What's New" for version 9 in chapter 12 under "Load from
cursor with remote fetch". But I'm not sure if that's where I actually
heard about it first. I think perhaps someone posted about it in this
newsgroup.

Reply to this Message