Useful DB2 administrative functions and views

Did you know that there are about 80 (eight-zero) administrative views in the SYSIBMADM schema in DB2 that are ready for use? I have used several of them and also looked into the documentation, but 80 is quite a lot. (Almost) All of them are documented in the DB2 Knowledge Center in the "Built-in routines and views" section.

The routines live in the SYSPROC schema, administrative views can be found in the schema SYSIBMADM. Given that insight it is easy to construct a simple query to find all available views:

SELECT viewname from syscat.views where viewschema='SYSIBMADM'

Depending on your version and fixpack level of DB2 the result will vary. Speaking of fixpack level, do you know how to find out what your system is running by using SQL? The view ENV_INST_INFO may help in that case because it returns instance-related information such as the instance name, the DB2 version, fixpack, and build level:

SELECT * FROM SYSIBMADM.ENV_INST_INFO

Are you connected to, e.g., an Advanced Workgroup Server Edition (AWSE) of DB2 or an Enterprise Server Edition (ESE)? Find out by querying the product information using the view ENV_PROD_INFO. It returns the installed product, the kind of active licenses, and more:

SELECT * FROM SYSIBMADM.ENV_PROD_INFO

Next in the list of useful views with system information is ENV_SYS_INFO. It can be utilized to find out more about the operating system, the type of hardware, installed CPU and memory, etc.:

SELECT * from SYSIBMADM.ENV_SYS_INFO

Last, but not least in my list of views with basic system information are DBMCFG and DBCFG. As the name implies can these views help to retrieve the current instance (database manager / dbm) or the current database (db) configuration. So it is easy to find out whether the self-tuning memory manager (STMM) is active or where diagnostic logs are stored.

That's it for today, I am back to playing with more of those views (and routines)...

What's Going On? - DB2 Workload Management: Identification of Activities

In an earlier blog post I had written about why Workload Management is needed. It's not just something for the database system or on the operating system level, it is really useful and done in "real life". But what is managed in the system, how are you able to identify activies in a DB2 database system? I am going to explain that today.

Before I dig deeper into the identification, first we need to clarify what is meant with "activity". It could be almost anything going on in the database system that is related to a single database and could be both user- and system-related tasks. The important distinction is that is on the database level, not for a DB2 instance. Identification of activities deals with three questions: WHO is doing WHAT on my database and WHERE is that data located?

The WHO can be answered by looking at the connection properties such as:

• Who is the user and which group does the user belong to? 
• Is the user operating in a special role?
• From where is the user connecting, does the machine have a name, is it from a specific application?

In DB2 these properties can be checked and used for identification of an activity by defining a workload (CREATE WORKLOAD). The workload object deals with the WHO. The WHAT and WHERE aspect of identification are handled by work classes. They are defined as part of a so-called work class set (CREATE WORK CLASS SET). Each work class can be used to identify an activity based on the type of work it is performing on the database and its related costs (the WHAT part). The type of work could be any combination of LOADing data, defining, altering or dropping objects (DDL), or read or write operations as part of select, insert, update, and delete statements (DML). The cost is what has been estimated by the DB2 compiler/optimizer and is the expected overall execution cost (timeron cost) or the cardinality (how many rows are we expecting in the result). Note that the actual cost of an activity is dealt with as part of controlling and managing the activities which I plan to describe in a later article.

By specifying a "data tag" for a work class, it can be related to storage groups or tablespaces and their priority. This is how activity can be identified by from WHERE the data is processed.

Because multiple work classes in a work class set could identify the same activity, the individual work classes can be ordered/positioned within the set. That way a work class with several properties could pick a very specific activity whereas other activities would be mapped to more general work classes.

Using the concepts of WORKLOAD and WORK CLASS SET it is possible to identify an activity. They help to understand what is going on in the DB2 database system. It is the prerequisite for actively controlling and managing the activities in the system by assigning resources.

Watch this! Move your DB2 monitoring to the in-memory interface (WLM monitoring)

Since its first days as a database management system, DB2 has been been changed. It has been extended by new features to serve customer requirements and has been adapted to the state of the art in hardware and software technology. One major new feature has been the introduction of the DB2 Workload Management in version 9.5 and related more comprehensive monitoring with finer granularity (in-memory metrics monitoring) in version 9.7. As with many product changes, it takes a while for customers to really put them to use and reap the benefits, especially when the existing functionality still works.

Thus I was happy when I saw a new article on IBM developerWorks describing how to move off the (old) snapshot monitoring interfaces in DB2 and to the in-memory metrics monitoring. What is included in the article is an overview of the advantages of the newer interface. This should get you motivated to read the rest of the article (and then to migrate if not done yet). It contains a side-by-side comparison of old and new interfaces and has many sample SQL queries. The queries demonstrate how to obtain DB2 runtime metrics using the old and new interface for some popular monitoring tasks. You can find the documentation of the SQL interface to the in-memory metrics in the DB2 Knowledge Center in this overview. Most of the pages in the manual have further SQL samples to get you started.

So take a look, it will also help you with one of the upcoming DB2 quizzes on this blog.

Improved db2look in DB2 to mimic database environments

Some of the advertised improvements in the recent DB2 10.1 Fixpack 4 apply to the long existing tool db2look. Two new options have been added: "-createdb" and "-printdbcfg". The first is used to generates the CREATE DATABASE command and its options, the second to generate statements to reapply the database configuration.

As it is new, I wanted to test it myself. First I created a database "lt" (as in "Look Test") with non-standard options. Next was to invoke db2look:

db2look -d lt -createdb -printdbcfg -o lt.out
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: HLOESER
-- Output is sent to file: lt.out
-- Binding package automatically ...
-- Bind is successful
-- Binding package automatically ...
-- Bind is successful

The generated output file starts with the usual environment and version information, then follows the section to recreate the database:

--------------------------------------------------------
-- Generate CREATE DATABASE command
--------------------------------------------------------

CREATE DATABASE LT
        AUTOMATIC STORAGE NO
        USING CODESET ISO8859-1 TERRITORY de
        COLLATE USING IDENTITY
        PAGESIZE 8192
        DFT_EXTENT_SZ 32

...

;

As you can see, I didn't use automatic storage, used a local, non-Unicode codepage and German territory, an identity collation and 8 kByte pages. Thereafter follow the parameters for the catalog, temporary, and user  tablespaces (not shown). After the database creation is completed, the next is the CONNECT statement:

CONNECT TO LT;



Once the database connection is established, another new section starts. It reapplies the database configuration:

--------------------------------------------------------
-- Generate UPDATE DB CFG commands
--------------------------------------------------------

-- The db2look command generates the UPDATE DB CFG statements
-- to replicate the database configuration parameters based on
-- the current values in the source database.
-- For the configuration parameters which support AUTOMATIC,
-- you need to add AUTOMATIC to the end
-- if you want the DB2 database to automatically adjust them.

--UPDATE DB CFG FOR LT USING ALT_COLLATE      ;

UPDATE DB CFG FOR LT USING STMT_CONC          OFF ;

UPDATE DB CFG FOR LT USING DISCOVER_DB        ENABLE ;

UPDATE DB CFG FOR LT USING DFT_QUERYOPT       5 ;
UPDATE DB CFG FOR LT USING DFT_DEGREE         1 ;
...



Right now the enhancements are only available in the just recently released fixpack of DB2 10.1. As with other improvements, I would expect it to be available for the newer DB2 10.5 release soon.

Tuning your DB2 CLP environment: Customize appearance and editor

Tuning World Bodensee via Wikipedia

Over the last weekend, the annual Tuning World Bodensee was guest at the Messe Friedrichshafen (exhibition center and fair grounds). More than 100,000 people interested in car tuning traveled to Friedrichshafen. "Tuning" can be trying to get more performance out of engine or to customize the car to the personal style. With DB2, you can customize the command line processor to your personal style and preferences. Let's have a look at the available tuning options.

All the recent versions of DB2 provide three environment variables to tune the editing experience in the interactive DB2 CLP: DB2_CLP_EDITOR, DB2_HIST_SIZE, and DB2_CLPPROMPT. The first variable, DB2_CLP_EDITOR, is used to specify an external editor to be used for editing SQL statements. On my Linux system, I did the following:

export DB2_CLP_EDITOR=gedit

Now you can edit previous statements using the EDIT command. "EDIT 1" would call the editor with the first statement in the command history, "E 1" would do the same. To know which statements are available, use the HISTORY command or its short version "H". The maximum number of available commands is determined by the variable DB2_HIST_SIZE. It accepts numbers from 1 to 500.

export DB2_HIST_SIZE=100

To reduce the number of statements listed with the HISTORY commands, you can limit it: "H 10" would return the last 10 statements in history, "H R 5" would return the last five in reverse order. Instead of the option "R" you could also use the full word "REVERSE", e.g., "H REVERSE" or "HISTORY REVERSE". Editing commands is fun, but actually executing them is probably why they were edited. To execute a specific statement from the history, you can utilize RUNCMD. The short version is just "R" and a valid parameter would be the number corresponding to a "historic" statement.

Both RUNCMD and EDIT, if not invoked with a number, will pick the newest statement in history. Both also accept negative numbers with "-1" being the most recent statement.

What is left is to "decorate" the command line processor in your personal style. DB2_CLPPROMPT is used to modify the command prompt. It accepts different tokens and most characters. Here is my version which prints the current database name followed by "=> ":

export DB2_CLPPROMPT="DB: %d => "

Here is a small sample session with the bew prompt:

 DB:  => connect to hltest

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = HLOESER
 Local database alias   = HLTEST

DB: HLTEST => values 'Good Morning'

1         
------------
Good Morning

  1 record(s) selected.

DB: HLTEST => e -1
DB: HLTEST => values 'That''s it, good bye!'
Do you want to execute the above command ? (y/n) y

1                 
--------------------
That's it, good bye!

  1 record(s) selected.

DB: HLTEST => h r
4    h r
3    values 'That''s it, good bye!'
2    values 'Good Morning'
1    connect to hltest
DB: HLTEST =>

Trimming the fun? LTRIM and RTRIM extended

One of the newer features in DB2 is extended trimming capability in the functions LTRIM and RTRIM. Since Fixpack 2 of DB2 10.1 both functions have an optional second parameter which is used to specify the characters to be removed from the beginning or end of a string. Before it was only possible to remove blanks. If the second parameter is omitted, blanks are removed by default.

To trim your calories you could do the following:

db2 => values rtrim('All I eat: marzipan, vegetables, fruits',' ,abefgilrstuv')

1                                     
---------------------------------------
All I eat: marzipan                   

  1 record(s) selected.

The enhanced LTRIM and RTRIM functions can be used together with other functions of course:
db2 => values replace(ltrim('jogging and eating are great',' adgijno'),'are','is')

1                          
----------------------------
eating is great            

  1 record(s) selected.

The examples are just some food for thought about what is possible.

(SOLVED) DB2 Quiz: What function do I call?

Today, it is time for another DB2 quiz. I am going to present you the output of a DB2 function:

1 
---
YES

  1 record(s) selected.
Which built-in function did return this result? I invoked the function this way "values funcname", no parameters were involved. The function was added to DB2 in version 10.1.

Added on April 3rd:
As you can see from the comments, the quiz has been solved. I called the administrative function ADMIN_GET_INTRA_PARALLEL(). The function returns either YES or NO depending on whether the database application will run with intraparallel parallelism enabled or not. In DB2 10.1 several enhancements were made to exploiting parallelism. In addition to the database manager (dbm) switch INTRA_PARALLEL to enable or disable parallelism, it is also possible to call the new procedure ADMIN_SET_INTRA_PARALLEL() to configure parallelism for a specific connection.

MySQL-style LIMIT and OFFSET in DB2

An "ancient" but not yet well-known feature in DB2 is the support of MySQL/PostgreSQL-style LIMIT and OFFSET in SELECT statements and searched UPDATE/DELETE. Unfortunately, it is not really documented and I am working on getting some more documentation added (think about the "mostly harmless" as in the Hitchhiker's Guide to the Galaxy).

To demonstrate what is possible, I created a small table full of good stuff:
db2 => create table sweets(id int, desc varchar(60), quant int)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(1,'dark chocolate',4)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(2,'marzipan bar',1)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(3,'almond cookies',10)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(4,'granola bar',1)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(5,'nut chocolate',1)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(6,'chocolate-covered peanuts',1)
DB20000I  The SQL command completed successfully.
db2 => select * from sweets limit 5
SQL0104N  An unexpected token "limit" was found following "".  Expected tokens
may include:  "FETCH FIRST ROWS ONLY".  SQLSTATE=42601

By default, DB2 does not know about LIMIT and OFFSET either. But setting the DB2_COMPATIBILITY_VECTOR to MYS (as in MySQL) or "04000" enables the syntactic alternative to the FETCH FIRST n ROWS ONLY. After restarting DB2 the SELECT works:

db2 => select * from sweets limit 5

ID          DESC                                                QUANT
----------- --------------------------------------------------- -----------
          1 dark chocolate                                                4
          2 marzipan bar                                                  1
          3 almond cookies                                               10
          4 granola bar                                                   1
          5 nut chocolate                                                 1

  5 record(s) selected.

The following two examples show the use of the OFFSET, i.e., where to start in the result set. I combined it with ordering on the quantity.

db2 => select quant,desc from sweets order by num limit 3 offset 2

QUANT       DESC                                                       
----------- ------------------------------------------------------------
          1 nut chocolate                                              
          1 chocolate-covered peanuts                                  
          4 dark chocolate                                             

  3 record(s) selected.

db2 => select quant,desc from sweets order by num limit 2,3

QUANT       DESC                                                       
----------- ------------------------------------------------------------
          1 nut chocolate                                              
          1 chocolate-covered peanuts                                  
          4 dark chocolate                                             

  3 record(s) selected.

Note that in the second example a special abbreviated syntax is used. The first number is the offset where to start, followed by the number of rows to return.

As said, there is not much in the DB2 Information Center (yet). Serge has some more background in his blog about different ways of paging through result sets.

DB2 fixpacks, support, APARs, and other information

About a week ago I had asked how you typically find information about DB2 fixpacks. With the new DB2 10.1 Fix Pack 2 and Data Studio 3.2 just released (and I back from travel), here is the way I usually trying to stay in the loop.

A good starting point is the IBM Support Portal. It requires a so-called "IBM ID" to manage a profile. There you can define RSS news feeds or email subscriptions to many of the IBM products, including the Information Management offerings. Information you can subscribe to include new or update Technotes (example: updated Technote on recommended fix packs for Data Server Client Packages), on fixes (example: IC84157, Crash recovery may fail if the mirror log...), product and packaging information (example: Mobile Database now included...), etc.

Once the new fixpack is available I usually first read the Fix pack Summary in the DB2 Information Center. It describes the high-level changes in the fixpack.

On the support pages you will also find an overview of the available fix packs for the different supported versions of DB2. When you click on one of the fixpacks, there are additional links leading to, e.g., the list of security vulnerabilities, HIPER and special attention APARs fixed in DB2 (here V10.1, FP2) or the list of the fixes (fix list) for that release. By the way: HIPER stands for High Impact or PERvasive, i.e., bugs with critical impact. APAR is Authorized Program Analysis Report and basically is a formal description of a product defect. Customers usually open a PMR (Problem Management Report) which may lead to an APAR (or not).

DB2 10.1 Fixpack 2 is available

An overview of available fixpacks by DB2 version, including the fixpack 2 for DB2 10.1, can be found at this support page. A summary of the enhancements in the fixpack is, as usual, in the DB2 Information Center.

Law License Enforcement options in DB2

Yesterday I received an interesting question regarding licensed features in DB2 and how the license is enforced. Someone was able to create an MQT even though the DB2 edition does not include support for it. But why? I try to explain.

The DB2 Information Center has an overview of features by edition. DB2 utilizes a license management server to control what features can be used and for reporting and auditing of compliance. The db2licm tool is the interface to the server and is used to add licenses, list features, and much more. Traditionally, DB2 had a "soft stance" on license enforcement, e.g., unlicensed features could be used without the software complaining (only the lawyers).

By customer request that eventually changed, so that compliance could be enforced on company level (and lawyers and finance could focus on keeping the books clean). From my time as DB2 developer I remember discussions on how to implement certain hard stops. In DB2 V9.5 hard enforcement of license compliance was offered for the first time AFAIK. I found these two DB2 9.5 fixpack notes on FP2 and enforcement of pureXML and storage features (which I was involved in) and FP4 changes on enforcement of WLM, CPU, and memory usage.

When installing DB2, by default enforcement is set to SOFT as it seems:
hloeser@ems:~$ db2licm -l show detail
Product name:                     "IBM Database Enterprise Developer Edition"
License type:                     "Developer"
Expiry date:                      "Permanent"
Product identifier:               "db2dede"
Version information:              "10.1"

Product name:                     "DB2 Advanced Enterprise Server Edition"
License type:                     "Developer"
Expiry date:                      "Permanent"
Product identifier:               "db2aese"
Version information:              "10.1"
Enforcement policy:               "Soft Stop"

The behavior can be changed using the "-e" (enforcement) option of db2licm:
hloeser@ems:~$ db2licm -e db2aese HARD

LIC1412W  A hard stop enforcement policy has been set. This enforcement
      policy stops unlicensed requests.

Explanation:

You issued the db2licm command with the -e parameter, to update the
enforcement policy, and specified the value HARD. (For example, db2licm
-e db2ese HARD.) The value HARD specifies that unlicensed requests will
not be allowed.

User response:

As a mechanism for you to keep track of, and differentiate, the DB2
database products and features installed on your system, it is
recommended that you register the license key for each DB2 database
product and feature.

If you want unlicensed requests to be logged but not restricted, change
the enforcement policy to SOFT. For example, db2licm -e db2ese SOFT



LIC1411I  Enforcement policy type updated successfully.

As I have AESE installed, it does not really allow me to show you an example. But you could try to compress or create an MQT on DB2 Express-C. Here is the output after switching the enforcement:
hloeser@ems:~$ db2licm -l
Product name:                     "IBM Database Enterprise Developer Edition"
License type:                     "Developer"
Expiry date:                      "Permanent"
Product identifier:               "db2dede"
Version information:              "10.1"

Product name:                     "DB2 Advanced Enterprise Server Edition"
License type:                     "Developer"
Expiry date:                      "Permanent"
Product identifier:               "db2aese"
Version information:              "10.1"
Enforcement policy:               "Hard Stop"

As mentioned, SOFT has been the traditional way and is the default in DB2. However, to be compliant and to be on the safe side for audits, switching to HARD is a good idea.

BTW: You can generate a compliance report using db2licm:
hloeser@ems:~$ db2licm -g comp.txt

LIC1440I  License compliance report generated successfully.Anything troublesome in there...?

Updated Redbook for DB2 10: High Availability and Disaster Recovery Options

Well, there is not much to say about this existing Redbook that has been updated to reflect DB2 10.1 for Linux, UNIX, and Windows and current technologies. The "High Availability and Disaster Recovery Options for DB2 for Linux, UNIX, and Windows" Redbook describes and explains technologies like IBM Tivoli TSA, PowerHA SystemMirror, Microsoft Windows Failover Cluster, WebSphere Q Replication or InfoSphere CDC.

With close to 600 pages it also requires your high availability...

DB2 10.1 - the first fixpack is out

FP1 for DB2 10.1 for Linux, UNIX, and Windows is now available. Here are the two important links:

• The so-called Fix Pack Summary in the DB2 Information Center,
• and here in the overview DB2 Fix Packs by version you can find all the available fixpacks from DB2 10.1 to DB2 8.2

Some of the new features or enhancements for DB2 10.1 were already included in DB2 9.7 FP6 and needed to be ported. An example of this is the support for XML type for global variables and in compiled SQL functions.

DB2 Information Center: English, deutsch, español, italiano, etc.

Today I discovered an interesting feature of the DB2 Information Center that I will now use often. As all of you know, the DB2 Information Center is available in many languages and the content is displayed according to the preferred language setting of your browser. The DB2 Information Center even has a page on how to set this up. However, when I work with customers or partners in a different country or I don't even use my machine, switching languages requires some clicks.

So I was very positively surprised and happy to find out that I can just specify a "lang=xx" (xx being the country code) parameter to the base URL of the DB2 Information Center. Thus with one of the following URLs you have the Information Center in

• your standard preferred language,
• in English,
• auf Deutsch,
• en Español,
• en français, etc.

Now using a specific language is simpler which allows for faster cross-checking of wording between your language and the English version of the DB2 Information Center.

New Redbook on pureScale: Delivering Continuity and Extreme Capacity with the IBM DB2 pureScale Feature

A new redbook covering many aspects of using pureScale with DB2 10.1 has been published last Friday. All the details on the related website.

Partial early blog posting

Two of the many performance enhancements in DB2 10.1 are called PED (partial early distinct) and PEA (partial early aggregation). Work is done as early as possible, though only partially, to reduce data volume already in an early stage and speed up the total query. Having said that, I am done. :)

P.S.: This may be only partial information on PED and PEA, but it helps you speed up query performance and is light in reading...

Index something non-existing - functional indexes in DB2

After discussing hidden (non-visible) columns last week, indexing something non-existing fits into the same theme. Let's shed some light into this seemingly mysterious topic...

XML documents and XML-based data modeling have been around for quite some years now. DB2 started its XML support with the XML extender in the late 1990ies (version 7), DB2 pureXML shipped initially with version 9.1. Advantages of using XML include flexibility of what is included in a document (or set of documents) and how sparse data can be represented. In relational NULL values or other "empty" values need to be stored even if data for a property (column) does not exist, in XML documents that particular property could be just left off, i.e., nothing is stored. However, the "nothing is stored", the non-existing data introduced a problem for searching efficiently in some use cases. How do you find all customers that do not have a certain property, e.g., have not received the marketing letter yet or do not own a car?

To address this kind of problem, DB2 10.1 now allows certain functional indexes over XML data (when does it come for relational data - any guesses...?). One of the functions allowed in such an index definition is fn:upper-case(). That way an index supports case insensitive searches over strings:

CREATE INDEX customers_email_idx ON customers(cinfo) GENERATE KEYS USING XMLPATTERN '/customer/contact/email/fn:upper-case(.)' AS SQL VARCHAR(80); 

SELECT * FROM customers WHERE 
XMLEXISTS('$CINFO/customer/contact/email[fn:upper-case(.)="HENRIK.LOESER AT GMAIL.COM"]');  

The other supported function is fn:exists() which allows to index existence or non-existence of an element or attribute. Thus, an index can be utilized to search even for something that is not (directly) stored in the database, i.e., implied information.

CREATE INDEX customers_db2vers_idx ON customers(cinfo)

  GENERATE KEYS USING XMLPATTERN '/customer/sw/fn:exists(db2version)'

  SQL AS VARCHAR(1);

SELECT * FROM customers WHERE
XMLEXISTS('$CINFO/customers/sw[not(fn:exists(db2version))]');

The above query would return all customers who don't have any DB2 version of software installed. Maybe they are reading this article and then will install DB2 soon...?!

Another quiz: How to index something non-existing?

To start off this week with the right attitude, here is another quiz:

Imagine that I would like to store address data and information about my contacts ("friends"?). I want to keep track of when I met them. In my database I want to efficiently query whom of my contacts I met on a specific date. From database theory I know that indexing the data would help. But I would also like to search for persons I never met. How could I support that with an index in DB2? How could the data be stored?

SQL statements will be in the solution... [Update: The solution has been posted.]

Implicitly hidden columns in DB2

On Monday I posted a small quiz about an SQL error reported by DB2. In the comments were guesses about the solution, including generated columns, triggers, or the table actually being a view causing the error. However, the reason for the processing problem is a different one and it was also mentioned in the comments: The presence of an implicitly hidden column.

The concept of implicitly hidden columns was first introduced in DB2 9.5 for the purpose of easier and faster optimistic locking. A column defined as IMPLICITLY HIDDEN is not returned as part of a SELECT statement if not explicitly referenced. In DB2 9.5 and DB2 9.7 this feature was limited to a ROW CHANGE TIMESTAMP column type to implement the optimistic locking. With DB2 10.1 this has changed as you can see at the CREATE TABLE statement and a special page with an introduction to the concept of hidden columns. Now, you can hide all kinds of columns.

Here is how I defined the table that I used in the quiz:

db2 => create table ih(id int unique not null, s varchar(30), s2 varchar(30) not null implicitly hidden)
DB20000I  The SQL command completed successfully.

As you can see, the third column is hidden. Then I performed an insert which resulted in the reported error:

db2 => insert into ih values(1,'a')
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0407N  Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,
TABLEID=257, COLNO=2" is not allowed.  SQLSTATE=23502

What is interesting to note is the following:

• Best practice and strongly advised is to have a default value defined for hidden columns to not run into such errors.
• In computer science many enumerations start with zero, not one. The column numbers, table identifiers, and tablespace identifiers in DB2 are no exception. Hence is the column with COLNO=2 the THIRD column which could mislead you. In the description for the error SQL0407N is actually a SQL query provided that helps resolve the mystery.
• Similar issues are possible with so-called data movement utilities like IMPORT and LOAD as you might have or not have data for the hidden column.

In the quiz I showed the output of DESCRIBE with only two columns reported.
db2 => describe select * from ih

 Column Information

 Number of columns: 2

 SQL type              Type length  Column name                     Name length
 --------------------  -----------  ------------------------------  -----------
 496   INTEGER                   4  ID                                        2
 449   VARCHAR                  30  S                                         1
 Performing a DESCRIBE TABLE shows all three columns.

db2 => describe table ih

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 No   
S                               SYSIBM    VARCHAR                     30     0 Yes  
S2                              SYSIBM    VARCHAR                     30     0 No   

  3 record(s) selected.

Finally, if you are sick of all the hidden stuff, you can act as a magician and let the columns reappear again:

db2 => alter table ih alter s2 set not hidden
DB20000I  The SQL command completed successfully.
db2 => describe select * from ih

 Column Information

 Number of columns: 3

 SQL type              Type length  Column name                     Name length
 --------------------  -----------  ------------------------------  -----------
 496   INTEGER                   4  ID                                        2
 449   VARCHAR                  30  S                                         1
 448   VARCHAR                  30  S2                                        2

Now I will disappear by altering my state, coffee is waiting...

A small DB2 SQL quiz - why the SQL error?

I have been playing with some new DB2 10.1 features and thought that a small SQL quiz at the beginning of the week is appropriate. Consider the following SQL statements and the related output:

db2 => insert into ih values(1,'a')
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0407N  Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,
TABLEID=257, COLNO=2" is not allowed.  SQLSTATE=23502
db2 => select * from ih

ID          S                            
----------- ------------------------------

  0 record(s) selected.

db2 => describe select * from ih

 Column Information

 Number of columns: 2

 SQL type              Type length  Column name                     Name length
 --------------------  -----------  ------------------------------  -----------
 496   INTEGER                   4  ID                                        2
 449   VARCHAR                  30  S                                         1


How was the table defined or why is it giving an error? Any guesses? Post your answer and explanation as comment. I will post the solution in a couple days. [Edit: done and linked]