topic Re: Fetching huge data into an internal table in Application Development and Automation Discussions

Fetching huge data into an internal table

Former Member — Mon, 22 Oct 2012 10:15:48 GMT

Hello all,

I have below requirement.

I have to take a backup of all the entries of a table in a file (either in presentation or application server). Since the data is so huge when i fetch the entries into the internal table it might result in a Dump as it may run out of memory. So is there a way i can fetch the data part by part and append in the same file.

All your help will be very much appreciated.

Best regards,

Praveenkumar T.

Re: Fetching huge data into an internal table

former_member193464 — Mon, 22 Oct 2012 10:38:03 GMT

Hi,
Use PACKAGE SIZE in SELECT statement and then append into your internal table to avoid dump for fetching huge amount of data.

If the data you have to download is from one table . You can also download directly from se11 , just execute the table with no conditions,give the maximum number of hits and columns from number of entries , select all and download(shift + F8) to the format you need. I havent tried it with huge amount of data but i think it should work.

Re: Fetching huge data into an internal table

HermannGahm — Mon, 22 Oct 2012 14:08:15 GMT

Hi,

sounds like a DBA task to me. What about exporting on DB level and storing it on the db server?

Why would you want to do it in ABAP and have it on the app server level or frontend?

If you want / have to do it with ABAP SELECT ... INTO TABLE ... PACKAGE SIZE... ENDSELECT is your friend.

Kind regards,

Hermann

Re: Fetching huge data into an internal table

volker_borowski2 — Mon, 22 Oct 2012 16:43:09 GMT

Hi,

be sure, that a backup of ALL entries is all you need.

Is the table client dependant?

Do you need to rescue ALL clients?

What will you do to the table? A structural conversion?

You will need to save the DDIC Source in additon.

So why not use transports as a backup?

Add as virtual system DUM (dummy) to your STMS config.

It does not need to be on any path or track, you only need it to send transports of copies to it.

Create a transport of COPYs.

Include

R3TR TABL tablename

to the object list and assign DUM as the target system and release the transport.

This is your structure export.

Check that the datafile is indeed created on OS Level and the export log.

Create a new transport of COPYs.

include

R3TR TABU tablesname

to the object list and assign DUM as the target system and release the transport.

This is your data export for the client you are logged into (if the table is client dependent).

Check that the datafile is indeed created on OS Level.

Repeat the last step untill all clients are processed.

Test the functionality of re-importing the stuff you need on QAS, to make sure,

that you do proper handling of the required actions.

You can do a client copy of only users and authorisations to a new empty client.

After this you can import the transport of copies (of the data, not the structure)

to the new client and crosscheck in SE16.

On the other hand: Some DBs have snapshot capabilities meanwhile, so on MAXDB or

ORACLE I would check out, if this would be a fast was to set back a DB.

This usually requires only minutes to set back and you are sure, everything is consistent, so

you do not need to make sure that you captured all tables involved.

For MAXDB you should be on recent Version 7.7 patches.

For ORACLE the feature is called flashback database.

You should be on recent Version 11.2 patches for this.

Volker

Re: Fetching huge data into an internal table

HermannGahm — Mon, 22 Oct 2012 17:34:53 GMT

Hi Volker,

very interesting ideas. Yes you are right the op should have given more information what the purpose is... . I like your additional ideas to the question.

Kind regards,

Hermann

Re: Fetching huge data into an internal table

volker_borowski2 — Tue, 23 Oct 2012 17:11:22 GMT

Hi Hermann,

well, to operate DB snapshots has become quite comfortable meanwhile.

And there are lots of usefull things you can do with them.

The only pitfalls it has is that you always need to set back the entire DB,

which might not always is what you really like to do. So there might

be need to proceed with exports and imports somtimes, and I always

did not like to re-invent the wheel, so I often use transports if appropriate.

Devlopers usually know how they work, so I do not need to explain, what I am

doing with my database tools underneath THEIR system .

But if they are doing stuff to the big beasts, I always like to have my DB exports in addition.

On Oracle, you can even use other flashback features,

but not all of them are supported in SAP environment.

But i.e. flashback query is a "MUST KNOW" when running oracle.

Volker

Re: Fetching huge data into an internal table

Former Member — Wed, 24 Oct 2012 08:21:19 GMT

Hello all,

Thanks for your responses. Since i needed an abap solution for this, below is the logic which helped me solve the issue.

OPEN DATASET <file name> FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

OPEN CURSOR l_c1 FOR SELECT * FROM <Table name>.

WHILE l_c1 IS NOT INITIAL.

FETCH NEXT CURSOR l_c1 INTO TABLE <Internal table> PACKAGE SIZE p_pkg.

* p_pkg is the maximum number of records to be fetched every time.

IF sy-subrc EQ 0.

LOOP AT <internal table> INTO <work area>.

TRANSFER <work area> TO <file name>.

ENDLOOP.

ELSE.

CLOSE CURSOR l_c1.

ENDIF.

ENDWHILE.

CLOSE DATASET <file name>.

Best Regards,

Praveenkumar T