Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Location
    Australia
    Posts
    7

    DECLARE C1 cursor/LOAD from C1

    Hi All,

    My system: DB2 v9.5_FP0 (Wintel).

    This is not a problem, however, I seek guidance/advice on my solution. I load data from various databases into a monitor database for performance reporting purposes.

    Question: Do I need to 'declare cursor' and provide the user & pw for each table within the same database or is there a better method?

    I use the following method:

    db2 declare C1 cursor database %db% user %user% using %pw% for select * from SYSTOOLS.STMG_DBSIZE_INFO
    db2 load from C1 of cursor messages %loadMsgLog% insert into %schema%.DBSIZE NONRECOVERABLE

    db2 declare C1 cursor database %db% user %user% using %pw% for select current timestamp as snapshot_time, t.* FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE(-1)) AS T
    db2 load from C1 of cursor messages %loadMsgLog% insert into %schema%.MEMUSAGE NONRECOVERABLE

    ...etc etc for several other tables within the same database.

    Thanking you in advance.
    Mark.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2maf View Post
    Hi All,

    My system: DB2 v9.5_FP0 (Wintel).

    This is not a problem.
    Actually, that may be a problem (running Fix-pack 0). Go to the DB2 fix-pack download site and get the latest fix pack for version 9.5.

    You don't "need" to declare a cursor, you just create a script using export and import, or invoke these commands using a DB2 Admin Stored Procedure (you will have to look up the specs on that).

    Sample export:
    export to EMPLOYEE.DEL of DEL select * from EMPLOYEE

    Sample load:
    load from EMPLOYEE.DEL of DEL insert into EMPLOYEE nonrecoverable

    Use nonrecoverable to avoid logging and to avoid taking a backup after the load. You may have to do a "set integrity" on the table after the load.

    There are many options on the Load command, and also you can use import command (which is similar but slower).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2010
    Posts
    335
    You could also use Federation for your cursors. The Connection-Information will be stored inside the database and you could use the Cursor without User-Credentials. I usally use Scripts like this

    create nickname <schema>.load_tab for <SOURCE_TABLE> ;
    declare load_cur cursor for select * from <schema>.load_tab with ur;
    load from load_cur of cursor replace into <TARGET_TABLE> nonrecoverable;
    drop nickname <schema>.load_tab;

  4. #4
    Join Date
    Sep 2010
    Location
    Australia
    Posts
    7
    Thanks for your advice folks. I was on the right track and just wanted to make sure.

    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •