Thursday, December 12, 2013

The DB2 10.5 Year End Quiz

This is the last quiz for 2013 and before I take a break ("vacation"). When looking at the English language version of the DB2 10.5 Information Center, what do these four (4) pages have in common?

Where condition in SQL warehousing transform operator:

SEND procedure - Send E-Mail to an SMTP server:

UTL_SMTP module:

 The SAMPLE database:

Any guesses? It is about the samples and the current season...

Enjoy the holidays and all the best for 2014!

Wednesday, December 4, 2013

DB2: Nothing to hide...

Today, while teaching a DB2 class, I was asked whether it is possible to hide all columns of a table. The rationale behind the question was that then all application developers would be forced to specify the columns during INSERT. Well, my first reaction was that at least one column needs to be visible. How does it look like?

Testing is faster than looking into the manual for IMPLICITLY HIDDEN columns, so here is  a quick test:
db2 => create table hideme(id int implicitly hidden, s varchar(60) implicitly hidden)
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL20422N  The statement failed because the table "HLOESER.HIDEME" would only
contain hidden columns.  SQLSTATE=428GU

db2 => create table hideme(id int, s varchar(60) implicitly hidden)
DB20000I  The SQL command completed successfully.
db2 => insert into hideme values(1)
DB20000I  The SQL command completed successfully.
db2 => insert into hideme(id,s) values(2,'Hey')
DB20000I  The SQL command completed successfully.
db2 => select * from hideme


  2 record(s) selected.

db2 => select id,s from hideme

ID          S                                                          
----------- ------------------------------------------------------------
          1 -                                                          
          2 Hey                                                        

  2 record(s) selected.

At least one column must be visible as assumed.