Clearcase Defaults

Every time I start up Clearcase, the rightmost pane displays a bunch of text starting with the following paragraphs:

Dynamic View

Your project's source files are stored in one or more ClearCase data repositories called VOBs (versioned object bases). This view allows you to access those source files.

Each time you check out, modify, and check a file back in, ClearCase creates a new version that records the changes. Version control is just one of the features that enable your team to manage changes and coordinate access to sources.

The text goes on and on about dynamic views. All of these may be interesting information. However I don’t need to know that any more. I want to see the contents of the select folder in Clearcase.

So each time I launch Clearcase, I have to realize this, find the Windows splitter bar, and move it down to expose the pane with the folder contents. This is very poor design. Why is Clearcase wasting my time? Can’t I set it up to hide this stupid view?

I am going to do some research. There is no reason why I should have to fight with the tool every time I launch it. Shame on the IBM Rational user interface team responsible for Clearcase.

Clearcase Labels

Our project uses Clearcase labels to control versioning in software releases. Development puts the label on files that get changed and need to be sent out to customers. Then configuration management (CM) picks up the files with the labels that development specifies. CM proceeds to use the new files to produce a build. Our project is in maintenance mode. So for the most part, we just change a few files here and there to fix bugs. However this year we added a bunch of new features to the applications. We also upgraded our tools which resulted in a lot of files changing. Development needed to label a lot of files in Clearcase. It was not intuitively obvious how to do this quickly.

Here is the process I normally use to apply labels to changed files in Clearcase. I use Clearcase Type Explorer to create a new label. Then I manually find the changed files using Clearcase Explorer. I get the properties of the file, and click on the Labels tab. At that point I added the label I previously created with Type Explorer. This works fine for a small amount of files. However sometimes we have a lot of files to add.

Previously we had a Java developer on staff who took over our build scripts. In fact he rewrote them using Ant. Part of the build involved putting a label on all the latest files. He figured out how to programmatically label all the files in our project using Ant. Unfortunately this developer has moved on to a new project. So when a current developer needed to label a large directory full of files, he followed in the Java guy’s footsteps and wrote an Ant script to do this.

All of this sounds very strange. I would think it is a common operation to label multiple files in Clearcase. I would hope that the solution is not to label them one by one. And I would also think that IBM does not want every customer to write some custom code to do this automatically. Why can’t I select multiple files in Clearcase Explorer, and apply a label to all of them?

Clearcase Access

IBM provides the Clearcase source control tool as part of the Rational Suite. This product is highly configurable, and perfect for revision control in the enterprise. However some Clearcase policy as demonstrated in the Software Maintenance blog are clearly counterproductive.

Developers make use of different views in the Clearcase environment to work different code sets or version. On bigger projects these views can grow quickly in number. Clearcase administrators need to develop policies to keep the view universe in check. However these policies should be created and enforced to the benefit of the developer user community.

Clearcase is, after all, a tool to be used by software developers. Its purpose is to maintain version control of files. It is meant as an aid to software development. Of course it benefits configuration management as well. However, it must serve its original purpose for it to be put to its best use.

Clearcase Woes

Every time I boot up my computer these days I get an albd server error. This is the first thing that is supposed to run when you start Clearcase. From past experience I know Clearcase Explorer does not work when I get the albd server error. So I manually choose Control Panel in Clearcase. Then I attempt to start Clearcase. For a long time the status is “start pending”. Finally Clearcase is started. So I launch Clearcase Explorer. It comes up so I am feeling better. However when I try to mount a VOB, I get no VOB choices.

This Clearcase has been a headache ever since we switched to using it for source code control. We only do it because our client has standardized on it. Yes I imagine Clearcase is powerful. However I am looking for a solution that works with minimal effort and upkeep from me. My job is not to muck around with Clearcase settings. I am supposed to code new features for our customer, with the occasional debugging of problems in the software we write.

Last time I had Clearcase problems, I went to our local Clearcase guy. He was able to get it working on my machine. However this is not his main job. The right way is to submit a trouble ticket with our client’s help desk. That will take forever. I need my source code control now. So I check out any web pages I can find on the albd server. It seems this process is required for VOBs and views. I could figure that out based on the things not working on my machine.

There is some promise from the IBM Knowledge Collection. They have a whole lot of ideas on how to debug a broke down albd server. The key advice is to check out the logs. So I go to Event Viewer on Windows. I see a whole bunch of Clearcase errors. They fall under to categories: (1) can’t find albd server on a specific host, and (2) can’t find albd. Nice details on that last error description huh? So I guess I need to find out what is wrong with the server that albd cannot find. Too bad that is not a server controlled by our team. I might just have to submit a trouble ticket after all. Bad show Clearcase.

Clearcase Halted

I return to work from vacation today. Reported to a new office location too. When I logged in, I saw some error flash by about the Clearcase VOB not being able to be mounted. OK. I had other work to deal with in the morning. When things settled down, I launched Clearcase Explorer to check up on Clearcase. I got an error message stating there was a problem with licensing. It recommended I run the licensing program. I tried to run the Rational License tool. There were no entries in the license list for Clearcase. Clicking a button for help took me to an IBM web page. I don't want to buy any more licenses. My customer has a huge Clearcase license already.

Now I had heard other people encountering Clearcase problems too. Their fix was to kill Blackice. I killed the Blackice Windows process. Then I stopped the Blackice service. Clearcase still had a problem. Then I remembered that I logged into a virtual machine and ran Clearcase fine. So I went to my virtual session and exited the Clearcase Explorer. Now I was able to run Clearcase on my own machine. But there were no files to be viewed. I tried to mount the VOB for my view. The VOB Mount dialog box came back with no VOB choices. I felt like I was in some real trouble here.

Finally I broke down and called over our Clearcase guru. It is not his job to fix my Clearcase problems. But he is always willing to lend a hand. He exited Clearcase Explorer and ran the Homebase (a program I never seem to launch). Then he said my server was not running. He used the tool to launch the server. It then showed that the server was running. He typed some commands at the Windows command prompt to mount my VOB. Then things were peachy. Why did I have to bring the Clearcase expert in? Maybe it is time for me to read the manual or take some Clearcase training. I don't know. I just do not have a good feeling for this software.

Rose and Clearcase

Our development team has use of the enterprise Rational suite. We use Rational Rose, Clearcase, and Clearquest frequently. I am in design mode right now. So I use Rose heavily. All of the Rose files are checked into Clearcase. The project is pretty big. Luckily most parts are split out into separate catalog (cat) files. I only load those units which I need. However I have some painful problems due to the slow networks here. It takes a long time to check files in and out of Clearcase.

Today I have determined what I call a severe perform or functional problem with Clearcase. I make use of the Clearcase support which is integrated into Rose. When I chose to check out a file, I get to enter the Clearcase comment for the check out. I like to enter very descriptive comments so I can reconstruct exactly what I did later by just looking at the comments. So I get to the comment dialog within Rose. Then I switch Windows tasks to another application to figure out what to type in for a comment. By the time I switch back to Rose, the screen has hung. It never comes back.

Now I will confess that this is not a crucial problem. But it is no fun to have to kill the Rose application with the Windows task manager. It is very slow launching it again and loading the units I need. I wonder if this is a problem that I should submit to IBM. For now I have a work around. Prior to checking anything out, I figure out what my check out comment will be. That way I do not have to switch tasks. And although Rose is slow when performing Clearcase tasks, at least the app eventually completes the task and comes back.

Aside from this problem, I find both Rose and Clearcase working pretty well. I imagine the enterprise suite license costs a lot of money. The product is provided by our customer so I do no know the details. I just assume the cost is high. For such a high price, I should not have to suffer from such bugs. As a developer, I can understand that perfect code is impossible to write. So I will but the IBM developers a little slack for now.

Crystal Reports

We have a few consults from Oracle Corporation working for our client. They are not directly on our project. They have their own separate contract. However they work on the same system our team maintains. Recently the manager from the Oracle side wanted to extract a bunch of database change information from Rational ClearQuest. And it was not going well. You would think it would be easy to export the data from the tool. It was just not working out.

The test manager from our team heard about this problem and volunteered to help. I think this manager uses ClearQuest on a daily basis. The Oracle consultant explained what she was trying to accomplish, and how the tool was not cooperating. The test team manager understood the problem and explained that there was a limit to the amount of information you could easily export from the ClearQuest spreadsheets. The recommendation from the test manager was to use Crystal Reports to programmatically extract the data.

Now I do not know how the Oracle consultant is going to get the job done. But I do know that she could not come out and say she would use Crystal Reports. That would not be the Oracle way. Their team’s job is to make money for Oracle. But it is also to promote the use of new and expensive Oracle products. They are supposed to be the experts on Oracle technology. If they had to revert back to a product from a competitor like Crystal Reports, it would look very bad. The real problem is that the IBM ClearQuest tool was not making life easy.

In the end I think the consultant was not able to extract the data she wanted in a meaningful way. The task was delegated to another Oracle consultant who manually grabbed the information from ClearQuest. But the output was not easily to work with. Maybe everybody just does not know how to work with IBM Rational ClearQuest well. I have to believe that people who use this tool need to occasionally export the data from the spreadsheets the application displays.

BlackICE Defender

My project recently moved to a new building. This happened because a new company won the bid for the maintenance contract. The change did not impact me much. I merely switched companies. A number of other long time developers on the project did the same. However we all suffer from a new problem on the project. Clearcase access has become very slow. Part of this slowness is due to the fact that we now need to make many network hops to get to the Clearcase server. The slowness is very painful.

One day the delay was so severe that a developer asked the Configuration Management team to intervene. Our CM team has some connections with the team that runs the Clearcase server. The answer turned out that the network was not our main problem. It was the BlackICE Defender service running in the background. When this service was disabled, Clearcase performance improved drastically. Our CM guy stated that IBM has dropped support for Clearcase to coexist with BlackICE.

From what I recall, BlackICE was an application for virus protection. I did a little more research and was surprised by what I found. Apparently BlackICE is now a product distributed by IBM. And they were in fact withdrawing support for the project. However this did not seem to imply that it would not longer coexist with Clearcase. Instead it seemed like IBM was dropping it from its product line. This was very confusing and even suspicious.

For now I am living with the Clearcase performance problems. BlackICE Defender is installed on my workstation for a reason. It is not up to a developer to be disabling system components they know nothing about. This problem should be addressed at an enterprise level. Our CM Team was disappointed that the Clearcase server team did not escalate this issue to an enterprise wide level. There must be more to this story than meets the eye.

Luckily our management team is working on some alternate solutions to the network performance problems. I am hoping that this shall also solve my issues with Clearcase.

View Expiration

Our client requires that we standardize on using IBM Rational Clearcase for source code control. This product seems to have more power than our previous source code control software (PVCS Version Manager). But with great power sometimes comes great complexity. In other words, some things that were easy before have become more complicated. Some of these complications are caused by the Clearcase system administrators. Unfortunately the administrators are not the local configuration management guys on our project. They are enterprise wide. That adds all kinds of difficulty when things go wrong.

I will give you a concrete example. We create views to access different version of source control we keep in Clearcase. Some views I use every day. Many views are accessed infrequently. The administrators have put in a policy where views not accessed in the last 30 days get disabled, and then get deleted after 60 days. This is a reasonable policy. If you do not use a view, there is no real need to waste disk space. However the implementation of the disabling and deleting is broken. When I do not access my view frequently, the view gets corrupted by an automated expiration program.

So I find myself unable to recreate views I need infrequently. I then have to submit a trouble ticket. For some reason I have found that my trouble tickets remain unserviced for weeks unless I start making calls and escalating the bad service. This is no way to live as a developer. So the last time this happened, I hit up the administrator as to how he fixed the problem. He showed me that the viewed were stored in a certain directory on the network. When the view gets corrupted, he just renames the directory that corresponds to my view name. Then I am able to recreate the view through the normal process.

I have already used this trick to "fix" views that had been corrupted by the administrator policy. We had it good in the PVCS Version Manager days. I never had to deal with nonsense like this. At times I wish we could go back to simpler days. The fault does not lie with the IBM Rational Product itself. It is the organization that has erroneously implemented an enterprise policy that has created problem for me and my team. Their poor customer service only adds to the problem. It would be great if I could act like a normal consumer and fire this organization, replacing them with someone better. Too bad I do not have the clout at this time to do this for my entire customer organization. But if I had my way, we would be showing some teams the door.

View Setup and Configuration

A team normally has separate views with the same config spec. View profiles can automate view setup for a team. The view_server is a process which runs on the host where Clearcase storage resides. This host must have Clearcase installed. You can place the view_server on the same host which contains the VOB, which reduces traffic, but is not recommended so as not to degrade the VOB.

The viewtag is the name of the view. It must be a unique identifier. It also must take the form of a valid directory name. Here is some guidance on the config spec for a view:
  • Each view gets a default config spec
  • The default retrieves the most recent version on the main branch
  • Can be modified using the "cleartool edcs" command
  • Do not change while files are opened in the IDE
  • Is case sensitive

In most simple projects, the development takes place on the main branch. The default config spec is taken from the file located at $ATRIAHOME/default_config_spec. CHECKEDOUT in a config spec allows modification of elements. And \main\LATEST allows creation of new elements. Note that a config spec can include other files.

I have more reading to do in my Clearcase Manual book. When I have read and studied the book some more, I will post again and share what I have learned.

Visual C++ Integration and the CLI

Clearcase integrates with Microsoft Visual C++ (VC++). However is supports only one version of Visual C++ at a time. It accomplishes this integration by adhering to the Microsoft Common Source Code Control (SCC) specification. Icons in VC++ get customized with Clearcase. New context menus items are added for files. There is a new source control toolbar in the IDE (Integrated Development Environment). And there is a new tab in the output window called "Source Control".

A Clearcase view can be assigned to a drive letter like a network drive. There is a "share from Clearcase" option which allows links from one VOB to another VOB. You should not allow Clearcase to auto merge any "resource.h" file from VC++. Clearcase cannot correctly detect some conflicts with this file. When adding files to Clearcase, you must check out the parent directory first.

There are a number of files that you should not add to Clearcase. In general the files that should not be added are those that are generated by the IDE. These files can have extensions such as .aps, .bsc, .clw, .dll, .exe, .exp, .lib, .ncb, .obj, .opt, .pch, .pdb, .res, and .tlb. The full list is extensive. But following the rule of not checking in generated files is usually sufficient.

Clearcase also integrates with other Microsoft tools such as Visual Basic. However I work exclusively with Visual C++ so I will not go into this integration.

The cleartool command starts the command line interface tool. It has an extensive set of commands. It operates in two modes: single command mode and interactive prompt mode. With the single command mode, you type "clearcase " to run every command. With the interactive prompt mode, you issue the cleartool command which causes the "cleartool>" prompt to be displayed. You can then issue sub commands without typing cleartool each time. In this mode you can end a line with a caret (^) to act as a continuation character. You should also quota any arguments that include a space character.

Commands as well as arguments have abbreviations. Here are some additional properties of the single command mode of the CLI:
  • The command is first processed by the command shell
  • The command shell expands environment variables
  • The C run time library interprets quotes to delimit arguments
  • The Clearcase commands expand special characters such as the asterisk (*)

Clearcase commands return 0 for success, and a non-zero for errors.

Welcome Back Clearcase

I recently started a new job. They haven't given me a computer yet. So to pass the time I broke out my old Clearcase Manual. Now this book is for version 3.2 of Clearcase running on Windows NT. But many of the topics are relevant to any version of Clearcase. So I thought I would share some facts I have relearned while reading the book.

The Windows version of Clearcase is integrated with Windows Explorer. You can issue the "cleartool man" command to see the reference pages. Clearcase is both a version control system and a configuration management system. It is designed for use by teams. Clearcase has a command line interface (CLI) as well as a GUI one. The Clearcase Home Base allows access to most of the tools in Clearcase.

Clearcase objects have properties which are separate from Windows properties. A VOB (versioned object base) is a public storage area. A view is a private storage area for one user. There are two types of views: snapshot and dynamic. You need to use a view to access the VOB. Starting the view activates it so that data appears as a directory to Windows. You must mount at least one VOB and start at least one view to use Clearcase.

The config spec contains the rules that a view uses to select a version. A view can have only one checkout of a given element. Once checked out, only you and the administrator can undo your checkout. There are two types of checkouts: reserved and unreserved. Reserved checkouts are the default, and give an exclusive right to extend a branch to a new version. Unreserved checkouts allow multiple views to get a copy on the same branch, and requires a merge on checkin. The Merge Manager is a GUI tool to help resolve conflicts during the merge.

The Version Tree Browser is a tool which, among many other things, allows you to:
  • compare versions of a file
  • view meta data (such as labels)
  • view a history of events on an object

A View Profile is an optional feature that allows common Clearcase information to be shared by a development team.

Goodbye For Now

Next week I start my new job. My official title will be "associate". Does not sound too impressive. However I guess I am not important enough to be a partner yet. The project at my new job is primarily backed by a huge Oracle 9 database. They have plans to move to Oracle 10 in the latter part of this year. But this means I will not be working with IBM DB2 as I had previously planned.

So I expect my posts to this blog will be limited unless there is another drastic change in employment. It is sad but I think I shall uninstall my copy of DB2 for Windows. I have already shared the little bit I know about IBM tools in previous posts, mostly about the DB2 database. And who knows? Maybe the new project shall start using another hot IBM tool such as Websphere.

I have already tossed out my book on Understanding DB2. Luckily I did not pitch my library full of Oracle books. They shall come in handy during the next gig. If you want to keep up with my exploits when my next job takes me through a move to the Oracle 10g database, check out my Oracle Development blog. Cheerio for now.

Performance, Problems, and Misc

DB2 writes transactions to the log buffer. This buffer is flushed periodically. The flush happens when either the buffer pool is cleaned, or when it is full. DB2 has the ability to read pages from disk into the buffer pool before the application needs them. This is called "prefetching". It is highly recommended that the RUNSTATS program be run periodically to ensure optimal performance.

Frequently you will receive error messages when using the DB2 database. For example you might get the SQL0911N if there is a deadlock that is detected and resolved by DB2. The "SQL" in the SQL0911N error number means that this is an error that comes from the Database Manager. This is the most common error you will encounter as a database developer.

It the event that you suspect that an error is a DB2 bug, technical support may request that you run the db2support utility. It collects all kinds of information and stores it in a file named "". Guess what type of file this is?

The system catalog is contained in the SYSIBM schema. There are read only views for SYSIBM tables in the SYSCAT schema. For example, SYSCAT.TABLES is a view for the underlying SYSIBM.SYSTABLES table.

There is an entity called the Communications Database (CDB) in DB2. It is currently a set of system table with connection information. They are stored in the host database. The CDB used to be a separate database in prior versions of DB2. However it is not part of the catalog.

The DB2 Model

There are a number of listeners which monitor incoming network traffic to the DB2 host:
  • db2ipccm - listens for local connections
  • db2tcpcm - listens for TCP/IP connections
  • db2tcpdm - listens for TCP/IP discovery requests

The listener assigns a coordinator agent to work on behalf of a client. DB2 engine dispatch units (workers) are implemented as threads in the Windows version of DB2. The client runs in a different address space than the DB2 engine. This ensures that the database continues even when a client goes down.

The first process to be run when the instance starts is db2sync. It is the system controller. By default there is one coordinator agent per database connection. An application shall get an error if it needs an agent but no free ones are available. DB2 has a Connection Concentrator which allows more database connections than coordinator agents. This is a good option for applications with small amounts of transactions. It improves performance in this scenario.

There are two types of shared memory in DB2 - instance level and database level. Instance level shared memory is used for the following functions:

  • snapshots
  • event monitoring
  • audit logging
  • tracing

Database level shared memory is used for the following functions:

  • buffer pools
  • the lock list
  • the sort area
  • database heap (log buffer, catalog cache)
  • package caching
  • utility heap (backup and restore)

Locking and Backup

DB2 acquires a lock on every record being updated. DB2 has a call level interface (CLI) which is a C/C++ API for database usage. The LOCK TABLE command locks all records in a table until the transaction is COMMITed. You can set the LOCKTIMEOUT parameter. When a wait reaches the LOCKTIMEOUT value, a SQL0911 will be issued and the transaction rolled back.

DB2 has a deadlock detector which randomly chooses one of the transactions causing the deadlock, and it rolls that transaction back. DB2 maintains a lock list. When the list gets full, it shall replace all row level locks with one table level lock. This is called Lock Escalation.

Here are some guidelines to avoid deadlock problems:

  • COMMIT often
  • Issue COMMITs even when you are only doing reads
  • Specify FOR FETCH ONLY (also known as FOR READ ONLY)
  • Perform any database changes near the end of your transaction

DB2 has a load utility. It only has the ability to insert data into a table, but not a view. This technique is faster than an import of data. It writes directly to the database. Constraint checking is skipped. However the load will reject any rows that do not comply with the table's column definitions. The db2look utility will generate the DDL for any given database object. The RUNSTATS utility updates statistics for tables and indexes.

The database transaction log keeps track of changes to database objects and data. The ROLLFORWARD command will replay the log for the database to catch back up after a crash. DB2 has the ability to copy a database by writing a copy of all data to disk. This is called "splitting a mirror". There is an INSPECT utility which shall check for data integrity problems and inconsistencies in the database.

DB2 Storage, SQL, Security

The smallest storage unit in a DB2 database is a page. A row in a table cannnot span across multiple pages. Consecutive pages are grouped together into extents. Tables and indexes are stored in table spaces. These tables spaces are logically divided into containers. You can issue the LIST TABLESPACES command to see their names. Not every database action results in the database actually going to the disk. Data is cached in memory called the Database Buffer Pool.

Note that autocommit is on by default in the following tools: the Command Line Processor, the Command Windows, and the Command Editor. You can limit the number of rows returns in a SELECT statement by appending criteria like FETCH FIRST 3 ROWS ONLY to the WHERE clause.

Inner joins in DB2 work just like they do in Oracle. You add a WHERE clause criteria joining columns from the two tables, separated by an equals sign. However outer joins in DB2 have a syntax which differs from Oracle. You use the LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN keywords in the WHERE clause. Hopefully these are self explanatory.

DB2 has a function that can be used like this: COALESCE(colname, 0). It acts like the NVL function in Oracle. If the "colname" column has a NULL value, the value supplied (0 in this case) is substituted instead of the NULL. DB2 has ROWNUMBER() OVER() which acts like ROWNUM in Oracle. It is a pseudo column which is a number representing the row number in the results rowset.

You can perform set operations on 2 or more query results sets. UNION ALL will include all duplicate rows. And INTERSECT will only return unique values.

PUBLIC is a special group that everyone belongs to. When granting privileges, WITH GRANT OPTION will allow the grantee to also grant the privilege to other users as well. A user requires the table space USE privilege to create tables in that table space. However users only need EXECUTE privilege for packages they wish to run. They do not need privileges for each of the objects referenced by the package. To see the list of privileges you have, issue the GET AUTHORIZATIONS command.

Client/Server, DB Objects

If you have the DB2 Connect software installed, then you have access to the Database Connection Services (DCS) directory. It contains the information you need to connect to hosts such as OS/390, z/OS, or iSeries. Issuing the "list db directory" command will show you the databases that you can connect to. Then to connect to one of these databases you issue the command CONNECT TO dbname USER username USING password.

Alternatively, you can use a DB2 Connect Gateway which sits between the client and host. This gateway machine requires DB2 Connection Enterprise Edition. You can use Search Discovery to look for DB2 servers on the network. When setting up a new machine to connect to remote hosts, you can use the Configuration Assistant tool to export the configuration of a client which is already set up. The configuration info gets stored in an access profile file. You can then import this on the machine you are setting up to speed configuration.

Next I want to share a little bit about the objects in an IBM DB2 database. A buffer pool is memory which caches recently used database information. There are two types of table spaces: system-managed and database-managed. The system managed table spaces are managed by the operating system. While the database-managed ones are managed by DB2. SYSCATSPACE is the table space containing the system catalogs, which store the information on database objects. The system catalog tables use the following schemas: SYSIBM, SYSCAT, and SYSSTAT.

When specifying database objects, the database uses the authorization ID by default if you do not explicitly state the schema name. The authorization ID is the one used when connecting to the database. Regarding database column types, CHAR has a maximum length of 254 characters. And the TIMESTAMP type encompasses both date and time down to the nanosecond granularity.

You can clone a table by issuing a CREATE TABLE tablename LIKE sourcetable. This does not copy the constraints, indexes, or trigger of the source table. It also does not copy the table data. It only duplicates the table structure. Another way to clone a table is to issue a CREATE TABLE tablename AS (SELECT * FROM sourcetable) DEFINITION ONLY.

The identity column is one which automatically generates unique numbers for sequences. There can be a maximum of one identity column per table. The syntax for creating the column is columname INTEGER GENERATED ALWAYS AS IDENTITY. The values used by the identity column do not get affected by a rollback. Once such a sequence is generated, that number is used up regardless when whether you COMMIT or ROLLBACK.

You can implement unique constraints on a table by creating a unique index on it. Materialized Query Tables (MQTs) are tables created with data from a query. Prior to DB2 version 8, MQTs were known as automatic summary tables (ASTs). Temporary tables in DB2 exist for one connection.

Disks which hold DB2 data are divided up into extents. A table scan is when DB2 scans all data pages in all extents. A view is a virtual table. The view definition is stored in SYSCAT.VIEWS. The INSTEAD OF trigger performs operations on the actual tables which back a view. Stored procedures can be written in the C, Java, and SQL PL languages. A fenced stored procedure runs in its own address space separate from the DB2 engine. Fenced stored procedures run slower than unfenced ones, but provide more protection.

Instances and Databases

The key environment variables in DB2 are DB2INSTANCE and DB2NODE. As mentioned in previous post Sharing More Basics, the central repository is the DB2 profile registry. This has nothing to do with the Microsoft Windows registry. The command db2ilist will give you a list of all instances. You have the ability to set any of the profile registry values using environment variables. However it is strongly recommended that you use the db2set command.

The instance is the environment in which databases are contained in DB2. However you should note that dropping an instance does not automatically drop the databases contained in that instance. The default instance is aptly named "DB2" in the Windows version of IBM DB2. The instance level is also known as the Database Manager level.

There is a background process called the Database Administration Server (DAS). It provides the ability to manager the database with GUI tools. In versions of DB2 prior to 8.2, the DAS used to be a separate instance.

DB2 Tools

IBM calls fixed to its database product Authorized Program Analysis Reports (APARs). These fixes are distributed in FixPaks.

You can open a DB2 command window by issuing the db2cmd command. The DB2 command line processor (CLP) operates in interactive or non-interactive modes. In interactive mode, you can enter commands directly. In non-interactive mode, you must precede each command with "db2". You may also need to enclose commands in quotes when using the non-interactive mode of the CLP. The backslash character allows multiple line command, and these lines are terminated with the terminator character. The default terminator character is the exclamation point.

Developers can code functionality in User Defined Functions (UDFs). These functions can be written in C/C++, Java, or SQL PL languages. SQL PL is IBM's extension to the SQL standard.

Now for some miscellaneous information. The db2pd command invokes a statistics tool. A satellite is a DB2 server that is a member of a group of other servers with are similarly configured.

Sharing More Basics

Issuing the db2 command invokes the Command Line Processor (CLP) tool. In the world of DB2, the instance is the environment in which databases run. The DB2INSTANCE environment variable let's you know the current active instance. There is a Database Administration Server (DAS) which lets you administer your database with GUI tools.

DB2 settings are stored in a repository called the DB2 Profile Registry. Note that this has nothing to do with the Microsoft Windows registry. Global variables in the profile registry apply to all DB2 instances. You can issue the "db2set -all" command to enumerate all the DB2 registry variables.

There is a file called the Database Manager Configuration file that stores instance level variables. Database level variables are stored in the Database Configuration file. There are a number of default table spaces created in a DB2 database:
  • SYSCATSPACE - holds the catalog (data dictionary)
  • TEMPSPAE1 - system temporary space
  • USERSPACE1 - default user table space

As mentioned in my first post sharing what I learned, the Enterprise Server Edition of DB2 support partitioning. This partitioning applies to the database level (as opposed to the instance).

Sharing What I Learned

Previously I had thought my next assignment was going to be a in DB2 shop. So I did a lot of reading, and recently started playing around with a DB2 installation on my machine. I thought I would share some of the basic information on DB2 I learned. It is broad in scope. But it is not too deep as I am still very much a beginner.

The current version of DB2 that I am running is 9.5. However DB2 version 8.2 was code named "Stinger". I installed DB2 on Microsoft Windows XP Professional. This is a version that shares a code base with their Linux and UNIX versions. This code base is known as DB2 UDB LUW (Linux UNIX Windows).

The targeted platform for the project I was going to join was the z/OS. The version of DB2 for that platform is a different code base than the DB2 UDB LUW. The version I installed was DB2 Personal Edition. However there is also an Enterprise Server Edition (ESE) that supports database partition feature (DPF).

Back to Oracle

I started trying to learn IBM DB2 because my next project was going to be one backed by a DB2 database. Through some unfortunate set of events, it looks like that project will not be picking me up. Therefore I am going back to the project I have worked on for the last 8 years or so. And that project is all Oracle.

Therefore I imagine I will not be posting as much on this blog any more. Maybe in my last 2 weeks of employment at my current company I shall play around with the sample database I installed. And if I find anything interesting, I will share it here. However I will not be getting to dig into the details of how things work in the world of DB2. It is a shame. Finally after installing DB2 for Windows 3 times, I got to the point where everything seemed to be working. Oh well. C'est la vie.

Perhaps I will write a couple posts on everything I learned from the book Understanding DB2. It was chock full of information for newbies such as me.

Third Time is a Charm

This may be hard to believe. But I uninstalled and reinstalled IBM DB2 on my machine for the 3rd time. And guess what? This time it is looking pretty good.

I chose DB2 Personal Edition. For the DB2 Administration Server, I selected my domain account. It seemed strange that I needed to enter my password for this account though. Is DB2 storing my password somewhere? What if my password changes? I certainly hope this does not break the DB2 database.

When the install was complete, it reminded me to use port number 50000 to connect to the DB2 instance. I don't think I will need to remember this information. I am running a copy of the database locally. Why would I need to know what port the database is listening on? I am already on the machine when I run programs which need to connect to the database. Strange...

Now here is where the beauty comes in. I chose to install the sample database after the installation was complete. To my complete surprise, the sample database actually installed without error. This is a first. I guess by now it does not take much to impress me. The installation of the sample database took over 1 minute. So I was a bit worried. However I never got that far in the past, so I figured something must have been going right.

Currently I am using the Command Line Processor to issue SQL commands on the data in the sample database. It helps to have the Control Center open to spy on the structure of the tables in the sample database. My background is in the Oracle database. However I am finding that my basic SQL skills are pretty much still valid in DB2. I believe the next step is to review the important parts of the book "Understanding DB2" that I recently finished. It is my hope that I now have all the access to execute the commands that gave me errors before.

Return of Problems

Having reinstalled DB2 on my machine, I was hoping to get some practice with the database. These hopes came crashing down pretty quickly. I chose to install the sample database as part of the post-installation "First Steps". This choice led to an SQL 1092N. Apparently my domain user does not have the authority to create the sample database. Clicking on the SAMPLE database overview link was not encouraging. It just took me to a web page that stated, "The topic that you have requested is not available." LOL.

I thought maybe there was something wrong specific to the sample database. So I tried to use the DB2 Control Center to create my own database. This led back to the same SQL 1092 N. My next theory was that maybe my domain account did not have the correct rights. So I checked and found that I am a member of administrators on my machine. I am also a member of the groups DB2ADMNS and DB2USERS. These groups should be defunct since I chose to not use Windows groups for DB2 security. Now I was completely confused.

As a last ditch effort, I logged into my local machine with the db2admin account I specified when installing DB2 this last time. I created directory C:\DB2 and chose it as Default Path for the database I was creating. This resulted in a new error stating that the "path does not exist". Well I went back one last time and chose directory C:\ as the Default Path. Finally a database got created. The command completed successfully. But I don't want to log onto my workstation locally every time I create a database. And I bet I cannot log in with my normal ID and do anything with this database. Is it time to reinstall again, choosing my domain account as the User Information provided for the DB2 Administration Server? Who knows?

Uninstall Time

I decided it was time to unintall IBM DB2 from my computer. There were too many problems, based in part due to choices made during installation. I was a bit worried that maybe I would not be able to reinstall due to my trial license being over. There was one warning message during uninstall - "DB2 databases will not be removed". That was fine. I later used Windows Explorer to manually delete the database files. It was surprisingly quick to uninstall the whole DB2 software. It took a couple minutes total with no reboot of my PC needed.

Luckily the reinstall worked pretty well. At first I was back to the same issues when I first did an install. But I reviewed my post on Installing DB2. That helped me decide to choose Personal Edition of DB2. And this time around I chose a Typical installation, and accepted almost all the defaults. For example I chose a local user account of db2admin. However I once again ran into problems choosing a password for this account. It kept giving me errors like I mentioned in DB2 Install Continued. Note to self - always choose a short password that is complex like Pass2008 (upper case, lower case, numbers).

This time around I skipped installation of the DB2 Tool Catalog. Apparently this is for the "Task Center" and "Scheduler". Not sure what exactly these 2 items do. But I won't need any backups for my test database. So I hope this was the right decision. I also decided not to mess around with the notifications option. The most important install decision was to disable operating system security. This got me into so much trouble after the last install.

As a last step bonus, I decided to install the IBM Database Add-Ins for Visual Studio 2005. This step took a long time. But I have recently installed Visual Studio 2005 on my machine. So I hope to take advantage of this feature.

Reinstall or Deinstall

I am finding that a lot of functions in my DB2 database do not work. Much of this is due to the fact that my Windows account is not a member of the DB2 windows groups. And I can't change this since I do not administer the Windows accounts on the domain. My short term plan is to reinstall DB2, and opt not to use Microsoft Windows groups to manage DB2 permissions.

On a sad note, I might even just uninstall the whole DB2 database from my machine. Previously I had received notification that I was going to transfer to a project at work that used DB2 exclusively. Today I found out that this transfer is not guaranteed. Apparently I need to go back and interview with the team to see if I can get a position.

Stuck in SQL1092N

I tried to issue the command "list applications". The result was a SQL1092N. DB2 is kind enough to inform me what this error means: does not have the authority to perform the requested command. Great.

My hunch is that this problem traces back to when I installed DB2 on my Windows Professional workstation. I chose to enable operating system security for DB2 object. This option probably works fine. However it requires that you be able to add yourself to the DB2ADMNS Windows group. Due to circumstances beyond my control, I cannot do this. I had thought I overcame this drawback by adding Everyone to this group. This did not solve me problem.

Normally when I am faced with challenges like this, I turn to Google (i.e. Google the problem and looks for others with the same problem and suggested solution). The most promising recommendation was to issue an "update dbm cfg using SYSADM_GROUP" command. This way I could specify another group which I already belong to as the administrator. I tried this, and got a SQL5001N error. This error means does not have the authority to change the database manager configuration file. It appears I am stuck in a conundrum here.

My second to last resort, as advised by one of the Google search results, is to manually update the dbm config file. But this somehow feels wrong. The very last resort is to uninstall and reinstall DB2. However I am concerned about how this will work with my 90 day trial license. With my luck, it won't even allow me to reinstall. Come sing the DB2 woes with me.

db2sampl mystery

So I am reading my "Understanding DB2" book. I come across a section on the sample database. Previously I had problems creating the sample database. So I thought I finally was getting lucky. Apparently I just needed to issue the db2sampl command. However when I try that, I just get a bunch of error messages no matter what I do.

Don't get me wrong. I do not give up easily. I searched my DB2 installation for anything that looked like a sample database. Even though I was able to locate a db2sampl directory on my file system, I could not make heads or tails of it contents. There were a number of files types like xsd, xml, html, and even gif. I included one of the image files I found in this post. Who knows? Maybe this is a picture of a person who is stored in the sample database. I may never know.

This experience is just another setback. I can learn how to create my own database. But having a pre-built sample would help speed things up. What I have learned is that nothing is every easy with this IBM DB2 installation.

Connecting Client and Server

My next assignment will have me working with a huge IBM DB2 database on the z/OS. So I have been trying to learn more about DB2. I installed a copy of DB2 on Windows XP. But now I am trying to get a handle on client/server connectivity with DB2.

So far the book "Understanding DB2" has helped me with a number of DB2 topics. However it only seems to confuse me when trying to explain connectivity configuration. The book mentions the catalog command. However I am not sure what this command is supposed to do. I guess it might mean show me the contents (of a database). Or maybe it shows me the databases available. The book seems to indicate that catalog is more of an active command. But I am still clueless.

Luckily I have a local DB2 database on Windows XP. So things should be simple here. But I want to understand. It might be time to get another book. Tried looking up catalog on the web but that was fruitless. I started feeling better when the book mentioned using TCP/IP to connect to a server. But in the same paragraph it was mentioning APPC as if I should know what that means. Wikipedia helped out in explaining that this stood for IBM's Advanced Program to Program Communication, and that this was a part of SNA. I just hope the next project uses TCP/IP.

On a final note, I would say that the whole idea of directories was confusing as well. There seems to be a whole hierarchy of directories that work in DB2. Again maybe it is that my book is weak on this topic. But I just don't get it. Sometimes it sucks to be the new guy in a complex technology such as DB2.

A Little Progress

I used the Control Center wizard to create a new database table. This looks like a promising start. Some of the data types seemed unfamiliar to me. Good old VARCHAR was there. But I did not see a lone NUMBER type. So far I have been switching between SMALLINT and DECIMAL to fulfill my NUMBER needs (if you cannot tell, my recent background is Oracle).

After getting a table created, I wanted to run some SQL commands. Both the Command Line Processor and Command Window told me I was not connected to a database. So I tried "CONNECT toolsdb;" without success. Then I figured I needed CONNECT TO so I issued a "CONNECT TO toolsdb;". But it still resulted in an error.

In desperation I chose the Help facility. It bought me to the IBM site on the web. Took a while to realize that you do not end single commands with a semicolon on the Command Line Processor line. Finally I was able to connect to the database via "CONNECT TO toolsdb". Now we are in business. I don't have any data in my table yet. But given my bad luck so far, I am happy even small results such as these.

Control Center Blues

I continue to explore the different IBM DB2 tools available to me. While reading my book "Understanding DB2", I came to understand that Control Center tools have a way to set the properties of the tool. This includes the Control Center itself.

Since I had Control Center running, I decided to give it a try to see what options are available to me. I choose "Tool Settings" from the Tools menu. Nothing happens. I try it again. No luck. So I figure it might be due to the fact that I had Control Center running for a long time. I exited it and started it back up again. Apparently the Tool Settings menu option does nothing on my DB2 installation.

I am starting to get disappointed with this tool's quality. But hey. My next project is a huge data store backed by IBM DB2. Let's hope that DB2 for the z/OS is better than the one for Windows.

Database Creation Thwarted

To guide my introduction to DB2, I have been reading a book entitled "Understanding DB2". The book recommended I follow the First Steps tools that comes with DB2 to create a sample database. Sounds like sound advice. So I click the "Creating the SAMPLE Database" link to get an overview of the process. Here is what I get:

The topic that you have requested is not available. The link may be wrong,
or you may not have the corresponding product feature installed. This online
help only includes documentation for features that are installed.

Ouch. IBM falls down on the job. So much for the overview. I decided to go ahead and try to create the sample database anyway. After I click the "Create SAMPLE Database" button, I get the following message.

This is just not going well. I am glad that I am running DB2 as a free trial. I would be pissed if I had paid money and got this level of product. I sure hope this does not indicate how my overall experience with DB2 is going to be. Guess I will have to man up and create a sample database from scratch on the command line.

db2pd Reveals Secrets

My company provides a computer to do my work for our client. This computer is on the client's network. And this network is on lock-down for security purposes. There is the ability to go out to the Internet. However most good sites are on lock down.

I talked my manager into getting me a separate computer which I could hook up to our company's network. That way I could search the Internet and not get blocked all the time. The business case for this PC is so that I can research software topics I need.

So I figured this PC was a good place to install IBM DB2 to give it a test drive. I have been slowly looking through the utilities that come with DB2. I opened up a DB2 command window, from which I started the db2pd tool to check out some statistics. In fact I started it with the following option:
db2pd -osinfo

Imagine my surprise when this command revealed that my TotalCPU count was 2. I guess I have a dual CPU machine. It is almost a little embarassing that I do not know the hardware that I run on. Maybe that a sign that I am getting old. Either way this was a positive surprise. Anyways I continue to check out the administrative tools that come with DB2. I have much to learn.

DB2 Install Continued

So I am in the middle of installing IBM DB2 for Windows on my workstation. And it asks me to name my DB2 Copy. Does that make any sense at all? The install just says that this is the place where products are installed. Does that mean the directory where the DB2 executable is? Or are we talking where the data for instances/databases is located? I decided to just accept the default values. Let's hope I made a good choice.

The install allowed me to choose the name of a new user on my machine for the DB2 Administration Server (DAS). A little check mark on the bottom of that page enabled me to use this same user for the rest of the DB2 services. That seemed a bit sneaky. But maybe this is the most common configuration. Again, I accepted the default choices.

Now here is where I got into some trouble. I chose a password for the DAS user. I kept getting an error message that the password was too short. This happened even for one that was 12 characters long. OK. So I typed in the mother of all passwords. The result? Password too long. WTF? Knowing a little about password policies from other databases and operating systems, I decided as a last resort to try a password that was stronger (upper/lower case, numbers, etc). That one worked. Time for IBM to debug their install software a little more?

Another part of the installation that threw me was setting up notifications. I wanted the database to e-mail me when some things went wrong. But I needed to provide an unauthenticated SMTP Server. Hey. I am not a system administrator. And although I have company e-mail, I am sure those servers are authenticated. So I had to skip this option. Bummer. Even a quick search on Google did not give me a lead to install a free SMTP Server on my workstation.

A feature I initially liked was the ability to create the DB2ADMNS and DB2USERS groups on my workstation. I figured this would give me control over who could do what. But it turns out it was not that easy. When I tried to use Windows to add myself to these groups, I needed to enter a user name and password for someone who could administer the domain I was on to complete the task. Again, I am not a sys admin. I started to think I would never get the access needed to use my own database. Then I recalled a trick from somewhere. I gave "Everyone" access to these two groups. A hack but let's hope it works. You would think I should be able to add a user from a domain to my local workstation group without being an administrator of the domain. Might have to issue a trouble ticket to Microsoft on this problem.

Another regret I had during the install was that I did not have Microsoft Visual Studio 2005 installed already. Part of the IBM DB2 install allowed you to install IBM DB2 Add-Ins to VS2005. Too bad. Let's hope I can figure out how to write Visual Studio app which connect to DB2 without the Add-Ins.

Installing DB2

My company provides me with a PC to do things like checking company e-mail. This PC is separate from the secure one on which I do work for our client. I figure the company PC is the best place to install a practice version of IBM DB2. It also helps that my company PC has a huge hard drive with not much on it.

I also have access in my company to a lot of technical books online. One in particular titled "Understanding DB2" has helped guide me along my first DB2 install. It pointed me to "setup.exe". Of course I should have known that was the app to run for installation.

Checking the requirements for DB2, I counted myself lucky that my work computer had Windows XP Professional installed. My home PC does not have Professional so it is probably not ready to host DB2. One troubling part of the install was when I clicked on a link to see the DB2 Disk and Memory Requirements. This led me to a web page that stated "The topic that you have requested is not available." Lucky I am not a paying customer of IBM yet. Otherwise my confidence would be dropping at this point.

One nice thing about the DB2 project (and maybe any other IBM ones) is that there are APARs which document known issues. Now that sounds professional and well tracked. I did need to make some hard choices for the install. For example, I had a number of DB2 versions to choose from:
  • Enterprise Server
  • Workgroup Server
  • Express
  • Personal Edition
All I was looking for was something for myself which was not too crippled. But the right choice was not intuitively obvious. My DB2 book helped guide me to Personal Edition. This version does not allow connections from other computers, which is fine for me. OK now let me take the plunge and install this thing.

The Leap to DB2

These days it seems that database companies give away their software for free to developers. This good will comes back to them when a developer can influence the choice of databases at work where the company might shell out big bucks for the project. Imagine my surprise when I found out that IBM does not give free database licenses to developers. Strange indeed.

Well at least IBM has a try before you buy program. I have 90 days from when I install DB2 until I have to pay for the thing. So I figure I might as well get the latest and biggest version they have. The download was 500 Meg. And wouldn't you know it? IBM made me register to even qualify for the download. This is not feeling too developer friendly.

I unzipped the DB2 installation file and it created a whole hierarchy of directories. Not exactly sure where to start. Guess it is time to read some documentation. Or I could click one of these executables in one of the directories. Good luck to me.

New to IBM

My current project is ending in one month. The next project is going to be something new for me. It is programming for the IBM z/OS and DB2 database. Both of these are new to me. So I am starting this blog to chronicle my introduction to the tool set that IBM provides to developers.

Some programming friends of mine who are mainframe developers have told me that I am finally going to do some real work now. I guess my old job of PC application software does not count. Good thing I have a lot of experience. So I do not think a new environment should take too long to learn.

The first step will most likely be to get familiar to the DB2 database. I have done Informix, Microsoft SQL*Server, and a whole lot of Oracle. How much different could it be?