Hi everybody,

I'm using DB2 UDB 8.1.7.445 on a WinXP (just for developing, sure...) and I have to parse a file of ~750 KB by an SQL stored procedure. Pure parsing, tons of SUBSTR and nothing more. I noticed that during the parse under <db2installdir>\<nodedir>\<instancedir>\SQLT0001 .0 directory DB2 creates a temporary TLB file that grow up reaching 6/7 GB per session. This makes time of parsing raise up horribly spending about 300 secs for only 750 KB (I think the most of time is for filesystem accessing by generating this file).

1. Anybody knows what kind of file is the TLB?
2. Any suggestion on how to stop creating this file or making it smaller than 6/7 GB?

thank you very much

PS: Sorry for my bad english, hoping it's good enough to make me understood... :-)

I believe TLB is just a temporary table. DB2 spills out to temporary space when operations can't be done in memory, for example a large sort.

i've searched more, and i think it's something like that you said, J Petruk. TLB means "translation lookaside buffer" and it seems to be an API of the operation system used by anyone in needs. probably DB2 uses the TLB to keep memory of the operations done (but i still think that 6 GB of TLB to parse 750 KB it's quite exaggerated...).

now the question is if it's possible to inhibit the usage of TLB, and how to do it...

I'm not sure the .TLB file is related to the TLB you've found information on.

Most likely your SP code can be optimized to reduce temporary space utilization. Would you mind posting it?

about my SP, i can't post it because it has about 930 rows... but i've uploaded it on geocities and if anyone wants to see it can donwload it by clicking here - the SP it's quite simple to be explained:

the problem is we must approach a massive data load from a flat data file, without tie ourselves to a DB vendor... so we can't use the Load utility from IBM. i've developed an SP that recieve in input a CLOB (the flat data file) and parse it to insert data into DB. the SP must be SQL99 compilant.

it has about 150 constants (declare with default) that rappresent the map of the fields in each record of the data file and 150 variables to put the values parsed in. it gets 120 chars (size of one record) each time from the CLOB and parse this string to gather the values of fields. after parsed the record it inserts the data into the table, and so on, until the end of the CLOB is reached.

each record must be parsed with an average of 10 SUBSTR. my test uses a data file with about 6400 records (in a file of 750 KB) and - the test - DOES NOT perform inserts into tables - i'd like to test performance of the pure parsing first.

i don't think it's possible to optimize this kind of SP, because it's already quite simple

when i do the test on my local UDB, this TLB files appears, making my test end on a 57011 disk full error. the same thing seems to happen on an AIX system (but i've not access to that filesystem, so i don't know if the 57011 is provocated by a file similar to the TLB on windows).

some considerations:

1. the CLOB in input is declared with a size of 100 MB. i don't think this could be the problem, because if i give a file of 32 KB instead of 750 KB CLOB(100000000), CLOB(32000) and VARCHAR(32000) has the same behavior.

2. the code i uploaded contains a commit every 100 record parsed, but i've tried commit every 10 and 1000 records (even with only one commit at the end) and the results are very similar, few ms of difference. i don't think the TLB contains - b.e. - rollback infos because even with no inserts (only parsing) it's generated at the same size.

3. i've tried to propose a solution with a parsing in Java, but it wasn't accepted because we have critical needs of perfomance (final goal is to parse up to 80 GB in ONE hour, in the worse case).

4. i remind that we cannot use the DB2 utility Load because we can't tie up with a DB vendor: the procedure must be portable from DB2 to Oracle to SQL Server etc. and so compatible with SQL99 standards.

One thing I've noticed that, when you use SUBSTR on a CLOB the result is a CLOB of the same length which is created each time you call the function. Another potential space hog could be the code page conversion that may be happening when you pass the CLOB from your application.

Are you saying that, even if you change the input parameter declaration in the stored proc from CLOB to VARCHAR (32000) the problem persists? Or are you saying that you pass a VARCHAR(32000) to the same stored proc?

If it's the former then it's the stored procedure caller that may be to blame.

Did you test the performance of parsing in java, or is it just a speculation of the kind "Everyone knows that java is slow"?

Did you consider C? perl? awk?

Funny how people often cripple themselves with unrealistic requirements...

Agreed. Probably a separate topic but in my experience the cost of avoiding "vendor tie in" is not worth it. This is a great example - using LOAD would take minutes, instead all this development effort, troubleshooting, etc is tying up a resource and for what? So you can be prepared to move off of a vendor product you probably won't move off of?

this it's true, but i don't think it's the problem: if my original CLOB is declared for 100MB, this means that even only one SUBSTR sould generate 100MB of TLB, but this doesn't happens.

i mean that i've changed the input parameter of the SP. with a 32 KB file, with every type it spends about 234 ms, regular like a swiss clock. over the 32 KB (only with CLOBs, of curse) the time (the TLB file creation...) grows up exponentially.

i call SP with Java, and Java doesn't support CLOBs type. i used an InputStream setting on the callableStatement an AsciiStream (i found this solution from IBM).

Everyone (but not me) knows that java is slow. i'm only a poor keyboard-tipping-slave... when my boss talked me about the problem, i started with Java test (i've reached, without any kind of optimization, the goal of 36 GB parsed in 1 hour, on my bad workstation, monoprocessor and an EIDE harddisk...), but when my boss saw it he told me "java sucks, SP rules". ...say, i'm only trying to bring him evidences that HE sucks... ;-)

yes, but not yet started any study

i agree with you, but we don't have a pure commercial brain: maybe someone wants a customer to finance a product that he thinks it's for him, but someone wants to resell it to everybody...

but 30 minutes ago maybe i won my battle, convincing my boss using the Load utility... but i'm going to open a new thread, because i've some questions about Load util too... :-) sorry, but i'm a newbe on DB2...