VARCHAR, Trailing Spaces, and Unique Constraint

Another look at VARCHAR. This time, the implication of the 'important-ness' of trailing space(s) and constraint.

Let's start from a table with 3 rows:

db2 => select c1, length(c1) from smallt ;

C1   2
---- -----------
B              1
B              2
B              3

  3 record(s) selected.


As you can see, these three rows are 'unique' in the sense that they have different length. In Oracle, they are three distinct rows with all the 'expected' properties.

Can we create a unique index on it?

Let's try:


db2 => create unique index uqi on smallt (c1 );
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0603N  A unique index cannot be created because the table contains data
that would result in duplicate index entries.  SQLSTATE=23515


NOPE!

Another example of the 'useless' trailing spaces in VARCHAR.

VARCHAR and Equality Comparison

This is a follow-up on the previous post.

Now that we know trailing space(s) is 'not that important' as far as DB2 is concerned, what would you expect in a simple '=' comparison?

Let's do a little test:



db2 => create table smallt (c1 varchar(4)) ;
DB20000I  The SQL command completed successfully.

db2 => insert into smallt values 'B', 'B ', 'B  ';
DB20000I  The SQL command completed successfully.

db2 => select length(c1), c1 from smallt ;

1           C1
----------- ----
          1 B
          2 B
          3 B

  3 record(s) selected.


We have three rows with different 'length' due to the trailing space(s) we inserted. If we compare this column to itself, ie, c1 = c1, how many rows would you expect?

Three?

Let's see ...



db2 => select * from smallt t1, smallt t2 where t1.c1 = t2.c1 ;

C1   C1
---- ----
B    B
B    B
B    B
B    B
B    B
B    B
B    B
B    B
B    B

  9 record(s) selected.


Need I say more? ;)

Varchar: When a limit is unlimited

How much do you know about 'varchar' type in DB2?

I thought I knew, until I ran a little test and got a nasty surprise.

Here's a simple test case to illustrate:


db2 => create table smallt ( c1 varchar(1));
DB20000I  The SQL command completed successfully.

db2 => insert into smallt values('B             ') ;
DB20000I  The SQL command completed successfully.


db2 => select * from smallt ;

C1
--
B

  1 record(s) selected.



See the 'problem'?


This behaviour can be very disturbing for those used to Oracle DB. Another potential 'hidden' bug for Oracle developer doing DB2.

BLEVEL: How High Can You Go?

While investigating index splits, I encountered a fairly 'mind-numbing' number. But first, here's the basic set up on a database with default page size 4K:

CREATE TABLE LONGT (V1 CHAR(254)) ;
CREATE INDEX IDX_LT ON LONGT ( V1);

And populate this table with 50,000 rows of ascending 'numbers', starting with 1.

Guess what BLEVEL you get?

Drumroll please ... it is ... 5!

I'm beginning to wonder if this BLEVEL has the same meaning as the BLEVEL I have been exposed to ... ;)

Optimizer: Fake it like you mean it

There is another useful utility that lets you fool the optimizer into thinking it has more resource than there is in the hardware. The utility is db2fopt.

This is useful if you are testing the optimizer in a test environment that is considerably less powerful than the production environment. eg, the sortheap in the production system might have 80000 pages, but your tiny test machine might max out at 2000. What do you do then? You can use db2fopt to fake it:

db2fopt <database> update opt_sortheap 80000

Easy.

It is a bit unfortunate that only these 4 are available to be 'faked' as of this writing ( for DB2 LUW v10)

• opt_buffpage
• opt_sortheap
• opt_locklist
• opt_maxlocks

Replicating a database's 'statistics'

db2look has a useful option '-m' that allows you to 'replicate' a database's statistics, which is not unlike Oracle's DBMS_STATS.EXPORT_XXX_STATS. One advantage it has over the export is that it gives you an insight into the stats components required, in the form of individual UPDATE statements. The other advantage is that you don't have to prepare a table to be 'exported' or 'imported' into.

eg:

db2look -d sample -t t1 -m -o test.sql

This will 'look' into the database 'sample', pull stats only for table 't1', and redirects the output to file 'test.sql'.

One word:  Convenient!

ColumnStore Index in SQLServer (2012)

This is obviously neither Oracle nor DB2 feature, but I like it so much that I have put it in my wishlist. For traditional row-based RDBMS, OLAP kind of activities can really hurt performance. On the flip side, newer column-based databases excel in OLAP, but suffer in OLTP type of activities. Forget about throwing in SSD and terrabytes of RAM to 'solve' the issues ( eg: Oracle's Exadata, IBM's pureSparta ), this new kind of index offers partial relief to traditional RBDMS in OLAP system. 'Best' of both worlds at a fairly low cost.

Ellison, are you listening?