Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    7

    Unanswered: Refresh User Maintained MQT

    Hi guys,

    first of all: Hi, this is Tom and I just registered here

    I've got a problem with MQTs here.

    I am trying to union 2 tables into 1 MQT (2-3 million rows). Using system maintained MQTs with REFRESH is pretty slow so I was changing the MQT to user defined.

    Now I would need to use REFRESH in some cases where a manual update would be too complex, so I use in

    case 1) DELETE and INSERT (which works)
    case 2) REFRESH - but this doesnt work for USER MAINTAINED MQTs

    What is the alternative for REFRESH? I tried LOAD but I cant manage to define a LOAD statement for an MQT called "test" which than contains the data like

    select * from table_a union select * from table_b

    How would that work?

    Thanks a lot and regards,
    Tom

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Tom,
    You don't mention which platform and version you are running on. For Z/OS, the command is
    "REFRESH TABLE tablename".
    As far as a load goes you would perform just like any other load. If you are on LUW, you can check the SQL reference for the exact command.

  3. #3
    Join Date
    Sep 2009
    Posts
    7
    Hi,

    its DB/2 on UNIX.

    REFRESH does not work on User Maintained MQTs.

    I cant figure out how to use LOAD to just copy data from table 1 to table 2 (both have the same types of columns)

    Thanks,
    Thomas

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by xtomx
    I cant figure out how to use LOAD to just copy data from table 1 to table 2 (both have the same types of columns)
    Code:
    declare c1 cursor for select whatever from table1;
    load from c1 of cursor replace into table2;
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2009
    Posts
    7
    Great! Thanks a lot!

    Now I used

    declare c1 cursor for select * from myschema.t_table

    and what I get is the error

    AN unexpected token "declare c1 cursor for select * from myschema." was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".

    What exactly is wrong here?

    Thanks a lot,
    Tom

Posting Permissions

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