+ Reply to Thread
Results 1 to 8 of 8

DECLARE CURSOR DATABASE option

  1. DECLARE CURSOR DATABASE option

    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


  2. Re: DECLARE CURSOR DATABASE option

    Frank Swarbrick 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). 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!)

    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
    --
    Serge Rielau
    DB2 Solutions Development
    IBM Toronto Lab

  3. Re: DECLARE CURSOR DATABASE option

    >>> On 1/22/2008 at 3:26 PM, in message
    <5vn8t2F1nbqatU1@mid.individual.net>,
    Serge Rielau wrote:
    > Frank Swarbrick 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). 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!)

    > 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?


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

    Thanks,
    Frank


  4. Re: DECLARE CURSOR DATABASE option

    On Jan 22, 11:00 pm, "Frank Swarbrick"
    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

    [...]

  5. Re: DECLARE CURSOR DATABASE option

    >>> On 1/23/2008 at 5:07 AM, in message
    <0752652f-e99c-42b0-b638-e227857a0b55@j78g2000hsd.googlegroups.com>,
    Lennart wrote:
    > On Jan 22, 11:00 pm, "Frank Swarbrick"
    > 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?


    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/infoce...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/infoce...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


  6. Re: DECLARE CURSOR DATABASE option

    On Jan 23, 5:58 pm, "Frank Swarbrick"
    wrote:
    > >>> On 1/23/2008 at 5:07 AM, in message

    >
    > <0752652f-e99c-42b0-b638-e227857a0...@j78g2000hsd.googlegroups.com>,
    >
    > Lennart wrote:
    > > On Jan 22, 11:00 pm, "Frank Swarbrick"
    > > 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?

    >
    > 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/infoce...x.jsp?topic=/c....
    > 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/infoce...x.jsp?topic=/c....
    > 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


    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


  7. Re: DECLARE CURSOR DATABASE option

    On Jan 24, 2:19*am, Lennart wrote:
    > On Jan 23, 5:58 pm, "Frank Swarbrick"
    > wrote:
    >
    >
    >
    >
    >
    > > >>> On 1/23/2008 at 5:07 AM, in message

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

    >
    > > Lennart wrote:
    > > > On Jan 22, 11:00 pm, "Frank Swarbrick"
    > > > 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?

    >
    > > 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/infoce...x.jsp?topic=/c....
    > > 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/infoce...x.jsp?topic=/c.....
    > > db2.udb.admin.doc/doc/r0023577.htm

    >
    > > specifically: "When the DATABASE database-alias clause is included priorto
    > > 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

    >
    > 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- Hide quoted text -
    >
    > - 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

  8. Re: DECLARE CURSOR DATABASE option

    >>> On 1/24/2008 at 9:00 AM, in message
    ,
    wrote:
    >
    > 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 Thread