0

How can I export a large volume of data from a DB2 database to a CSV file without consuming much client memory?

Data volume : The result-set contains 100 Million Records, 14GB in size when written to CSV file. As of today.

I am running the db2 export command like so :

db2 "EXPORT TO /mounted/nfs/location/file.csv OF DEL MODIFIED BY NOCHARDEL SELECT * FROM LARGE_TABLE WHERE ID = 123"

This is run via a shell script (.sh file), in a VM of a private cloud.

The problem is that 14GB of VM memory is being consumed when this command is running. And if the data in the table grows any more, the VM will not have enough resources to run this process. I don't really understand why it needs to bring the result-set into the client memory.

Can't DB2 write to the target without consuming so much memory on the VM? Preferably consuming less than 1GB of memory.

I am using an IBM db2 db of version 11.1.3.1

I went through the documentation and it doesn't mention anywhere that the client memory is consumed for the export process https://www.ibm.com/docs/en/db2/11.1?topic=commands-export

Are there no command line arguments to perform this in batches or without consuming client memory?

Yes, as a last resort, I can manually do a batch write with the FETCH & OFFSET options in SQL, but I wanted to check if there are any existing options to do this in a simpler way?

I am avoiding the FETCH & OFFSET because the real SQL query has GROUP-BY operations, and I'm guessing it's going to perform the aggregations each time I run it. Which would put unnecessary load on the DB.

It has a similar structure to this sample query:

SELECT LT.K1, LT.K2, SUM(LT.VAL)
FROM LARGE_TABLE LT
WHERE LT.K3 IN (123, 456)
GROUP BY LT.K1, LT.K2

Note that there is no primary key on this table, so there is no ORDER BY clause which would have helped with fetching in batches.

Improve this question
8
  • You cant expect a decent answer without details. If you are not allowed to post the sql, make one up that is similar, change names of tables and fields. You need to make it easy for someone else to help you.
    – Rohit Gupta
    Commented Apr 26 at 10:08
  • @RohitGupta, I have added a sample query. Please let me know if any other details can help.
    – mew
    Commented Apr 26 at 11:05
  • What indexes do you have on LARGE_TABLE? What is the access plan, is statistics relevant?
    – Lennart - Slava Ukraini
    Commented Apr 27 at 8:30
  • There is an index on the K3 column, which is used in the where clause. But I'm not sure if that is relevant. The primary goal of the question is to find out what is the best way to export large volumes of data out of DB2, regardless of the query. A generic solution which could work for SELECT * FROM XYZ WHERE XYZ.col1 = 'foo', where XYZ has no keys to order by. By the questions in the comments, do I assume that the only way db2 can export is by fetching all the rows of the result-set of the query into the client memory?
    – mew
    Commented Apr 27 at 12:58
  • 1
    Try to localise the problem. Write to local file-system not NFS-mounted (temporarily to compare). Check what options exist for the NFS-mounted file system to relating to memory-usage. Next, export to a pipe, use separate (i.e. non-DB2) process to consume the pipe contents and write to the NFS-mounted-file-system. Compare behaviours? Upgrade Db2 v11.1 client (i.e. to 11.1.4.7) compare results. Upgrade to v11.5.9.0 (on client), rebinding CLI packages, to compare behaviour.
    – mao
    Commented Apr 30 at 19:29

1 Answer 1

Reset to default
0

You have GROUP BY LT.K1, LT.K2 but no indexes on those columns?

Db2 has to sort the result set to do the grouping. It will use virtual memory and temporary tablespaces for that.

If you do an explain of the select query, I suspect you'll see a sort in there.

You may want to consider an index on LT.K1, LT.K2, and LT.VAL as well.

e.g

CREATE [UNIQUE] INDEX LT_IX2
      ON LARGE_TABLE(K1, K2)
      INCLUDE(VAL)
Improve this answer
2
  • Interesting, didn't know that it utilizes the virtual memory of the client host for that. Thanks, I'll run the explain command and get back. But also, during testing, it was noticed that the command still consumes the similar amounts of memory without any GROUP BYs, e.g., with a query like SELECT LT.K1, LT.K2, LT.VAL FROM LARGE_TABLE. Wondering why that may be the case...
    – mew
    Commented May 16 at 15:15
  • Db2 will use the CPU and Memory resources on the VM server (host). Depending on your Db2 configuration, Db2 will be reading the data into a bufferpool (memory) and if your bufferpools are set to AUTO, they will expand as necessary. Db2 also has to track the locks on the data rows. For a select you will have a shared lock, normally at a row-level, however the locks can be escalated to a table shared-lock if the memory allocated to the locklist exceeds the limits set in your config. You may need to consult your DBA to check the DBM an DB config settings.
    – Greg
    Commented May 17 at 2:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.