If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > DECLARE C1 cursor/LOAD from C1

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,713
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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;
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On