-1

I have a table with millions of records which holds information about a user, his or her documents in a BLOB, and a column holding the file size per row. While reporting I need to extract all these records along with their attachments and store them in a folder. However, the constraint is that the folder size should not exceed 4GB.

What I need, is to fetch records only till that record, where the summation of file sizes is less than 4GB. I have hardly any experience in databases, and do not have any DB expert to refer.

for eg - say i need to fetch only records till sum(fileSize) < 9

Name fileSize
A       1
B       2
C       3
D       2
E       9
F       4

My query needs to return records A,B,C and D. Also, i need to store the rowID/uniqueID of the first and last record for another subsequent process. The DB being used is IBM DB2.

Thanks!

Peter Schuetze
  • 16,185
  • 4
  • 44
  • 58
zeus1208
  • 78
  • 2
  • 9
  • 2
    Why are you tagging with Oracle, Plsql, sql-server if the database is DB2? – OldProgrammer Oct 24 '13 at 15:24
  • could you please show some code of what you have been trying please? and you should probably give some more Details on the tables involved, and what DATABASE you are actually using – Malachi Oct 24 '13 at 16:11
  • my bad for multiple tags... actually on production we will be using DB2, we have to create this on SQLServer and send it to DB2 DBA (who will not create, only convert. We do not have access to a DB2 database) and this query will be within a procedure. so the PLSQL tag. oracle was me going overboard. So, in a gist, I need to this query on SQL-Server which will later be converted to DB2. – zeus1208 Oct 25 '13 at 03:23
  • #winces# which versions of DB2/SQL Server? The DB2 query is likely to be different than the SQL Server one, depending on exact versions available. Why are you developing against one, but actually deploying against another? Why aren't you also developing against a DB2 instance (note that there are free versions of DB2 available for essentially this exact reason)? Please update your question to include new information, don't put it in comments (and clean up your tags, while you're at it). We also need all relevant columns, and expected output (and initial attempts) would be nice. – Clockwork-Muse Oct 25 '13 at 09:25

1 Answers1

-1

So here is some trick how you can find your file size. and in procedure you can manage with data.

select length(file_data) from files

where length(file_data)<99999999;

LENGTH(FILE_DATA)

        82944
        82944
        91136

3 rows selected.

select dbms_lob.getlength(file_data) from files

where length(file_data)<89999;

DBMS_LOB.GETLENGTH(FILE_DATA)

                    82944
                    82944

2 rows selected.

dbms_lob.getlength() vs. length() to find blob size in oracle

hope this helps....

Community
  • 1
  • 1
Strauteka
  • 198
  • 1
  • 10