I read the DB2 Package Cache Monitor Table that countains the SQL Statements that are have been used in the Database. Some of them are nearly 32k bytes long.
I store the result set in a Listbox, so a single cell of the listbox contains the big SQL Statement. When i truncate the String to 20k every thing works fine.
Is there a limit for a single cell?
Another problem is, that a Listbox can contain only 255 columns. I have tables in the DB2 Database that have a lot more columns (ex. >700),
so I must limit my application. Is there another way to “oversize” a listbox?
[quote]a Listbox can contain only 255 columns. I have tables in the DB2 Database that have a lot more columns (ex. >700),
so I must limit my application.[/quote]
My first impression is that someone needed to rationalise the database design!
700 fields in one table???
You might show 2 or 3 listboxes side by side (UI nightmare), and synchronise the scroll position when one is scrolled.
Or allow the user to select which of the 700 fields they are actually interested in before retrieving the data, so you only have to show perhaps 10…20 of the fields. It will speed up searches too.
You are right, bad db design. But this is the table with the most columns. There are a lot of other tables in a big DB2 production environment with more then 255 columns.
Because this limit is known, I can promt a message that there are more then 255 columns.
My main problem is, that the big char string (>20K) causes the application to die without any message. If i would know the limit of a cell in bytes, I can also truncate the data and give a warning message to the user.
I dont know, and so far no other responses
But I would say the same as before- instead of blindly trying to display whatever happens to be in the database (who can READ 20K of text in one cell??)
Take the first 50 or so characters, append ‘…’ and show that.
eg
[quote]‘The top 50 ways you know your boss is t…’
‘Harvard alumni says he only ever wante…’[/quote]
If the user wants to see the full thing, find another way to display the data they actually want to see, such as a popup window with a text area on it.
Yeah, I have to read 30k of data, cause it is possible a bad SQL Statement, so I must have a look on it. My Tool uses the DB2 Catalog to generate the Select Statement for a dynamicly selected table, so I don’t know what the SQL turned back.Normaly for 90% of the tables in a database there is no Problem to show the data, but some where realy big or “worse” so I need to know the limits.
To solve my problem, I will try what the limit of bytes in a single cell will be and then show only strings in that length and give a warning message to the user.
So after a few test the problem becomes clearer: Not the lisbox causes the dead of the application, the recordset is the problem.
I use a odbc database connection to DB2. In DB2 there are different Sytem Views for example the SYSIBMADM.SNAPDYN_SQL View wich as a Field that is defined as CLOB(2M) a long character field contains up to 2 megabytes SQL Statement. What I have tested is a table with 2 columns shows like this :
db2 create table joerg.test_large (f1 char(10), f2 varchar(30000)) in space32k
I inserted 2 rows in the table with different length in the F2 Column:
Test1 has length 13345 and Test2 has length 30000
When I select * from joerg.test_large the application dies.
When I select f1,f2 from joerg.test_large where f1 = ‘TEST1’ everything works fine.
When I select f1,substr(f2,1,10000) from joerg.test_large everything works fine.
When I select f1,substr(f2,1,20000) then application dies or returns a Null Value.
It is not really possible to give an educated guess to your problem, but Jeff already gives you a Clue, andheres another:
Intead of putting large text in a Cell, do like Feedback: display the text in a large TextArea. GUI have to be tested.
How can you even think you can read >20K of sql commands in a Cell ? You really need larger Control (like TextArea).
Just a thought. The DB2 varchar type is limited to roughly 32000 characters, IIRC. If your string is encoded as one or another unicode type, is is possible that select f1,substr(f2,1,20000) is interpreted by either ODBC or DB2 as exceeding th 32000 character. (40000)?
I stand by my statement. According to this IBM page, the limit of a varchar field is 32704 bytes. There are conditions where it could be even less. This is DB2 11 on Z/OS. The limit for a varchar field on Windows with DB2 9.7 is 32672.
Yes, varchar is max 32k But the column STMT_TEXT in the Db2 LUW View SYSIBMADM.SNAPDYN_SQL is NOT a varchar field, it is a CLOB with the size of 2 Megabyte.
But i got the problem also when trying to receive 32k string from my test table.
I wil open a request to xojo because the ODBC implementation seems to has some errors.
