Select/Read/Update in packages

[Former Member]

Hello Masters.

I'm having a problem with a report that is supposed to update a field in a table.

This table has 3.4 million lines and I have to update all of the lines, because of a new field I've just appended.

The program is giving me an error of lack of space to increase my local table (TSV_TNEW_PAGE_ALLOC_FAILED

) and the only way I'm seeing to solve this problem is to read this table in smaller packages; after I have all these smaller packages I can go on with the rest of the updating.

I'm I thinking right? What do you advise me to do? How can I do several selects to pick up all the data in the end? Is there a way to do a select for the first 200 thousand and then the next 200 thousand or something like that? How can I know if I'm already at the end of the major table?

Thank you in advance for your help.

Regards.

Jorge Ferreira

[Former Member]

Just if anyone needs this answer:

What I've done and it solved my problem was:

SELECT my_fields FROM my_dbtable
    INTO my_local_table
    WHERE my_conditions.

  do whatever i have to do with my_local_table.
  update my db_table from my_local_table

add 1 to count.
if count = 100000.
  COMMIT WORK.
  clear count.
endif.

ENDSELECT.

if count ne 0.
  COMMIT WORK.
endif.

This code was courtesy of Manuel Santos.

[mvoros]

Hi,

maybe you can use some old field of your table to divide lines into categories. The values of this field will distribute lines of your table into smaller chunks. For example if table contains year you will run your report for each year in the table.

Another way is that you will use addition UP TO n ROWS in your SELECT statement. So you will select first 10 000 lines where the new field is still initial. Then you will update these lines and do SELECT statement again. After initialization of the new field in first 10 000 line you will select next 10 000 lines. This method will not work if initial value is allowed value of your new field. You will be still loading same lines again and again.

Cheers

[Former Member]

Just if anyone needs this answer:

What I've done and it solved my problem was:

SELECT my_fields FROM my_dbtable
    INTO my_local_table
    WHERE my_conditions.

  do whatever i have to do with my_local_table.
  update my db_table from my_local_table

add 1 to count.
if count = 100000.
  COMMIT WORK.
  clear count.
endif.

ENDSELECT.

if count ne 0.
  COMMIT WORK.
endif.

This code was courtesy of Manuel Santos.