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.

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).

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;

Thanks for your advice folks. I was on the right track and just wanted to make sure.

Regards,
Mark.