This article was reprinted from the IBM DeveloperWorks site.
In the world of information technology today, we are constantly bombarded with new information -- new software products, new versions, new features.
The first important difference to note between SQL Server and DB2 UDB is that SQL Server is only supported on the Windows platforms (Intel architecture). DB2 UDB, on the other hand, is supported on Linux, UNIX and Windows platforms and in a variety of hardware architectures. The code for all of these platforms is essentially the same with very minor platform-specific modules. In addition, the DB2 UDB product extends to the iSeries™ (AS/400®) and z/OS™ (mainframe) world. The iSeries and z/OS DB2 UDB members do not have the same code base as DB2 UDB for Linux, UNIX and Windows because those products leverage their respective hardware architectures. However, functionality across the DB2 family is very similar. After you are comfortable with DB2 UDB for Linux, UNIX and Windows, you can venture out and learn more about DB2 on these other platforms. See the resources section for more articles on how you can continue building your skills.
Overview of the system structures
Figure 1 below shows the basic SQL Server structure. Compare it with Figure 2, which shows the structure of DB2 UDB. I'll be referring to these figures throughout the article.
Instances and servers
The concept of an instance is similar in SQL Server as in DB2 UDB, although the term
"instance" is not used often in SQL Server terminology. Normally, users simply refer to it as a
"server". For both products, an instance provides an independent environment in which database
objects are created, and applications are run on those objects. Instances follow a shared-nothing
architecture, and thus objects from one instance do not normally interact with objects from another
instance. Figures 1 and 2 show the PROD and DEV instances as conceptually two separate boxes.
Though the concept of an instance is the same on both products, its implementation is quite
different. In SQL Server you can have the "default" instance (only one) and named instances. A
default instance is the instance created on first installation of SQL Server and can be
identified by the computer name. A named instance, on the other hand, is an instance
created after the default instance has been created and has to be identified in the format
To create several SQL Server named instances on the same machine, you must use the installation CD for each new instance, and you use the install GUI for each new installation. Note that in this process, you will be copying the SQL Server code for each instance you create, though in different paths. Only some files are shared between instances. To drop an instance you must uninstall the instance using the install GUI.
In DB2 UDB, after installing the product in the Windows platform, the instance "DB2" is created
by default. In Linux and UNIX, the default instance name is called "db2inst1". To create another
instance in the same machine, you simply execute the command
name>. For DB2 UDB instances on the same machine, there is only one copy of the
DB2 UDB code.
Figure 3 shows the default DB2 UDB instance "DB2" and two other instances created with the
db2icrt command from the DB2 UDB Control Center GUI.
To reference a given DB2 UDB instance from a command line interface, use the environment
variable DB2INSTANCE. This variable lets you specify the current active instance to which all
commands would apply. For example, if DB2INSTANCE is set to PROD, and then you issue the command
create database MYDB1, you will create a database associated with instance PROD. If
you wanted to create this database on instance DB2 instead, then you would first have to change the
value of the DB2INSTANCE variable to DB2.
Another easy way to identify the instance you want to work with is by using the DB2 UDB Control Center GUI as shown in figure 3. To see an entry for the new instance in this tool, you may have to add the instance to the GUI by right clicking on Instances and choosing Add.
After you create a DB2 UDB instance on Windows, a corresponding service is created with the same
name as the instance name. You can drop a DB2 instance by executing the command
<instance name>. This command will not remove the DB2 UDB code. In the case
of Windows, it does remove the corresponding Windows services for the instance.
In summary, instances for SQL Server and DB2 UDB are conceptually the same; it is in the implementation where the difference arises. SQL Server requires a different copy of the SQL Server code per new instance, while DB2 UDB doesn't. In SQL Server, the Enterprise Manager GUI can be used to manage instances, while in DB2 UDB the Control Center GUI can be used for a similar purpose.
Some of the main Windows services for DB2 UDB are described in Table 1.
Service Name Description DB2 - <instance name>-<partition number> This is the engine for a given instance. It processes all the SQL statements and manages databases. DB2 can support multiple partitions of a database in different machines if partitioning support is enabled. Thus <partition number> indicates which of these partitions you are dealing with. If partitioning is not enabled, the partition number does not appear. DB2 Governor Collects information for applications connected to DB2 databases and, based on rules you define, will monitor and take action on some operations. For example, you can specify a rule to limit the time spent by a unit of work to one hour, and force the connection to the database if this limit is exceeded. DB2 JDBC Applet Server Provides JDBC server support for DB2 applications that use the JDBC type 3 driver. DB2 License Server Monitors DB2 license compliance. DB2 Remote Command Server Supports inter-partition communication for multipartitioned databases. DB2 Security Server Authenticates DB2 database users when the authentication is performed at the client computer. DB2DAS - DB2DAS00 This is the DB2 Administration Server. It supports local and remote database administrative requests. The Windows default name for the DB2 Administration server is DB2DAS00. This server is used in conjunction with the tools catalog database to manage jobs, alerts, and so on.
Figure 4 shows the DB2 UDB Windows services with the default DB2 instance, and with the PROD and DEV instances that were created with the db2icrt command.
Now that you understand the Windows services that DB2 UDB uses, let's map these services to SQL Server's. This is shown in Table 2.
*There is only one DAS per DB2 machine.
SQL Server Service Matching DB2 UDB Windows Service MSSQLServer service. This service represents the default SQL Server instance DB2 - DB2-0 service. This service represents the default instance 'DB2' MSSQL$<instance_name> DB2 - <instance_name> SQLServerAgent DB2DAS - DB2DAS00. This service represents the DB2 Administration Server (DAS) * SQLAgent$<instance_name> DB2DAS - DB2DAS00. This service represents the DB2 Administration Server (DAS)* Microsoft Distributed Transaction Coordinator (MS DTC) Sync Point Manager (SPM) is used to support distributed units of work. The database configuration parameter SPM_NAME identifies the name of the sync point manager instance to the database manager. Microsoft Search There is no matching DB2 UDB Windows service; however, the Net Search Extender software is used for a similar purpose.
In SQL Server, an instance can contain several databases, as shown in Figure 1. Each database is an independent unit; however, metadata about all user databases is kept in the master database, therefore, queries can access tables in two different databases. When an instance is created several databases are created by default, like tempdb, master, model, Northwind, etc.
SQL Server uses two levels to store metadata: the instance level (using system tables in the master database), and the database level (using system tables in the user database itself). The master and tempdb are databases that are shared by other databases in the instance. Other objects like views are also created by default when creating a user database.
In DB2 UDB, an instance can also contain several databases, as shown in Figure 2. Each database is a truly closed and independent unit. Each database has its own catalog table space (storing metadata), temporary table space, and user table space. There is no database that shares metadata information across databases the way that the SQL Server's master database does. Likewise, there is no database that is shared among other databases to hold temporary data the way SQL Server's tempdb does. DB2 UDB does contain a binary file known as the system database directory that contains entries of all the databases you can connect from your DB2 machine. This directory is kept at the instance level.
Figure 2 illustrates the independence of each database in DB2 UDB. In addition, from this figure you will see that each database has its own transaction logs, which is similar in SQL Server.
When an instance is created no databases are created by default. You need to explicitly create a
database using the
create database command. You can also create a database using the
Control Center as shown in Figures 5 and 6.
In Figure 6, you can also see what happens when you click Show Command. All DB2 UDB Control Center GUI screens let you see the SQL statement or command that is actually executed in the background. These commands can be saved in scripts for execution at a later time, or can be copied and executed from the Command Line Processor (CLP) tool or Command Center GUI tool. These tools are equivalent to SQL Server's iSQL and Query Analyzer respectively.
Databases within an instance normally don't interact with each other; however, if your application has such a requirement, this can be supported by enabling federation support. You can review the resources section for an article about federation.
Containers, table spaces and file groups
In DB2 UDB, containers are where the data is physically stored, and can be classified into files, directories and raw devices. In SQL Server, the concept of 'container' does not exist; however, files are used to store data.
In DB2 UDB, a table space is the logical object used as a layer between logical tables and physical containers. When you create a table space, you can associate it with a specific buffer pool (database cache) as well as to specific containers. This gives you flexibility in managing performance. For example, if you have a "hot" table, you can define it in its own table space associated to its own buffer pool. This helps ensure the data of this table is always cached in memory.
When a database is created with default options, three table spaces are created as described in table 3.
|Table space Name||Description|
|SYSCATSPACE||Catalog table space containing metadata.|
|TEMPSPACE1||System temporary table space used to perform operations such as joins and sorts. The name of this table space can be changed.|
|USERSPACE1||This table space is optional and can be used to store user tables when a table space is not explicitly indicated at table creation time.|
Because databases are independent units, table spaces are not shared across databases. Because they are only known within a database, two different databases can have table spaces of the same name. You can see this in Figure 2 where database MYDB1 has a table space named MYTBLS and database MYDB2 has a table space of the same name.
SQL Server uses file groups in a similar fashion as DB2 UDB table spaces. Similarly to table spaces, two different databases can have file groups of the same name. This is shown in Figure 1 where database MYDB1 has a file group named MyFileGroup and database MYDB2 has a file group of the same name.
DB2 UDB table spaces can be classified as SMS (system-managed spaces) or DMS (database-managed spaces). SMS table spaces are managed by the operating system and can only be directories. They grow automatically as needed, thus it provides good performance with minimum administration. This is equivalent to the behavior of SQL Server data files (if you allow for automatic growth). DMS table spaces are managed by DB2 UDB, and can be files or raw devices. This type of table space allows for best performance, but some administration is required. For example, you need to specify ahead of time the amount of space you want to allocate for the table space, because growth is not automatic.
The location and size of transaction logs can be specified in the database configuration file.
Table 4 below shows how SQL Server databases map to DB2 UDB databases or table spaces.
|SQL Server Database||DB2 UDB database or table space|
|Master||SYSCATSPACE (catalog table space); in DB2 UDB, this information is kept only at the database level.|
|model||There is no equivalent database/table space; however, DB2 provides a tool called 'db2look' which could be used to copy the structure of a database into a script file, and could be later executed to create a new database.|
|msdb||Tools catalog database, which can be created at installation, or after
installation with the command
|tempdb||TEMPSPACE1 table space|
|distribution||DB2 UDB provides built-in replication support for all the DB2 UDB family. Replication involving a non-DB2 relational management system requires the Information Integrator software.|
SQL Server object names have a four-part structure as follows:
[SQL Server Instance].[Database Name].[Owner].[object name]
Note that the first three parts of these names are optional, depending on the context in which the object is used.
In DB2 UDB, objects have a two-part structure:
The schema name is used to group objects logically. The schema name does not have to match to a user id. Any user with a privilege called IMPLICIT_SCHEMA can create an object using a non-existing schema. For example, suppose that "Peter" has IMPLICIT_SCHEMA privilege and executes this command:
CREATE TABLE WORLD.TABLEA (lastname char(10))
In this case, the table WORLD.TABLEA is created, where WORLD is the newly created schema. If Peter had not explicitly indicated the schema, then the table PETER.TABLEA would have been created, because the connection ID is used by default.
In DB2 UDB you always explicitly connect to a database before issuing commands, this explains why a database name is not part of the object name structure.
Tables, views, and indexes
Tables, views, and indexes are much the same in SQL Server and in DB2 UDB. In respect to the creation GUIs, with SQL Server's Enterprise Manager GUI tool you must first create a diagram before creating an index, while with DB2 UDB Control Center, you can create an index directly from the GUI with no interim steps.
SQL Server uses the master database to store information about other user databases, but the user databases themselves are independent units just as in DB2 UDB. Thus, in both products, tables, views or indexes can have the exact same name in two different databases. SQL Server allows you to join tables of different databases (and SQL Servers); DB2 UDB provides this support only when federation is explicitly activated.
Stored procedures, triggers and user-defined functions (UDFs)
The main purpose of this article is to describe the DB2 UDB environment in comparison with the SQL Server environment. Moving into database server logic with stored procedures, triggers and UDFs is a vast topic, and thus is covered only briefly here.
First of all, all of these objects are supported in both products. SQL Server uses its proprietary Transact-SQL (T-SQL), while DB2 uses SQL Procedural Language (SQL/PL), both extensions of the SQL/PSM standard. In SQL Server, Transact-SQL is used for practically everything, including implementing SQL Server commands. In DB2 UDB this is not the case. DB2 uses different APIs to implement its commands, and it needs a C compiler to develop SQL/PL stored procedures as these are first translated into C. Transact-SQL is an interpreted language executed natively in the SQL Server engine. When run, it becomes interpreted byte code and requires optimization each time it is run. A C implementation of stored procedures may provide performance advantages because the code is compiled once (especially if run in unfenced mode); however, it also adds the requirement of having this compiler. In a future release of DB2 UDB, support for SQL/PL stored procedures without the need of a C compiler is expected along with the current support, which would allow users to choose the type of SQL/PL stored procedure they want.
With DB2 UDB, you can use inline SQL/PL in triggers and functions, which means you do not need a C compiler. A subset of SQL/PL statements are supported in this way.
SQL Server stores its configuration information at the instance level and at the database level. Using the Enterprise Manager GUI, right clicking on a given instance or database and choosing Properties displays a window with all possible configuration options. With DB2 UDB, configuration parameters are also stored at the instance level, known as the database manager configuration file, and at the database level, known as the database configuration file.
Using the Control Center, if you right click on a given instance and choose Configure Parameters, you will see the window shown in Figure 7.
At the database level, right clicking on a given database, and choosing the Configure Parameters displays the window shown in Figure 8.
DB2 UDB provides many parameters you can use to configure your system; however, if you would
like an easy way to configure the system automatically, use the
(or the Configuration Advisor GUI) which sets the database manager and database configuration
parameters to optimal values based on some information you provide. Figure 9 shows the
In addition to configuration files, DB2 UDB also uses DB2 Registry variables normally for
platform-specific configurations. The DB2 Registry variables have no relationship whatsoever to
the Windows registry. Use the command
db2set to review and change these
SQL Server uses the concepts of authentication, roles and privileges to implement security.
Two modes of authentication are supported:
- Windows authentication.
The Windows Operating system authenticates the login id and password, and passes only the login id to SQL Server, which matches it to the sysxlogins system table.
- Mixed mode authentication.
SQL Server authenticates the login id and password against the information it has in the sysxlogins system table.
Once authenticated, the user cannot perform any operation against SQL Server, unless he or she has been assigned a role or a given privilege. Roles are used to group users into a single unit to which permissions can be applied. Thus, rather than granting individual permissions to several users, you can create a role consisting of all of these permission, and then you would assign the users this role.
In DB2 UDB, users do not exist within the database, but are rather managed by the operating system. Thus, DB2 UDB authentication is somewhat similar to SQL Server's Windows authentication; however, no database login information is kept in any database table. Any operating system user can potentially use DB2 UDB; however, unless they have been granted a given DB2 UDB authority or privilege, there is not much they can do. Granting and revoking authorities and privileges can easily be handled through the Control Center GUI. You first may have to add a user or group to the Control Center from the available operating system users or groups.
Also in DB2 UDB the term "roles" is not used; instead, DB2 UDB uses the term "authorities", which are similar to SQL Server's fixed server and database roles. DB2 UDB does not support MS SQL Server's user-defined database roles; however an operating system group can be assigned authorities and privileges to implement this. The authorities supported with DB2 UDB are: SYSADM, SYSCTRL, SYSMAINT, DBADM and LOAD.
The SYSADM, SYSCTRL and SYSMAINT authorities cannot be granted using the GRANT SQL statement. These special authorities can only be set from the database manager configuration file.
A DB2 UDB user with SYSADM authority would be equivalent to a SQL Server user with sysadmin role.
DB2 UDB also uses the term "privilege" which is equivalent to SQL Server's permissions. There are database privileges (connect, createtab, and so on) and database object privileges (schema, table, view, and so on). Figure 10 shows DB2 UDB security information obtained from the Control Center GUI. Most of the tabs in the figure correspond to the privileges supported by DB2 UDB.
Table 5 below maps SQL Server security concepts with those in DB2 UDB.
|SQL Server||DB2 UDB|
|Two modes of authentication: Windows authentication and Mixed mode authentication||One mode of authentication handled by the operating system; it's closest to SQL Server's Windows authentication.|
|Roles||Authorities (All predefined; there are no user-defined authorities.)|
We have briefly discussed some of the tools used in SQL Server and DB2 UDB; however, there are many more we will not cover in this article. Figure 11 shows you the DB2 UDB menus. This should give you some indication of the tools and topics not covered in this article.
Using the command line
If you prefer using a command line interface, for SQL Server you are used to the iSQL utility. The equivalent interface in DB2 UDB is the command line processor (CLP).
Figure 12 shows theDB2 UDB CLP utility. We first execute from a command prompt window the command db2cmd to open a command window. This will initialize the DB2 environment required to run the CLP. We then invoke the CLP by executing the command db2.
SQL Server and DB2 UDB both provide command line interfaces that are also GUI tools. SQL Server has the Query Analyzer. The equivalent in DB2 UDB is the Command Center, shown in Figure 13.
This article introduces you to DB2 UDB V8 for Linux, Windows and UNIX by leveraging your current knowledge of Microsoft SQL Server 2000. Given that SQL Server is supported only in the Windows platform, the focus of the article has been kept to this platform; however, all of what you have learned here about DB2 UDB (less a few details) is also applicable to the Linux and UNIX platform. Again, because the Windows platform is being used, we have explained most of the concepts using the GUI tools for both products. However, all of what has been explained has an equivalent command.
As a final review of the SQL Server and DB2 UDB architecture, compare figures 14 and 15. They show the corresponding database objects using the SQL Server Enterprise Manager and DB2 UDB Control Center respectively.
I would suggest you review the article 'A Colorful introduction to DB2 UDB Version 8 for UNIX, Linux and Windows' which describes in a bit more detail the concepts covered in this article. Good luck with DB2 and happy reading!
- 'An Introduction to DB2 for OS/390 and z/OS System Structures for DB2 Distributed Users'
- 'Leverage your Distributed DB2 skills to Get Started on DB2 UDB for iSeries (AS/400)'
- 'Porting to DB2 Universal Database Version 8 from Microsoft SQL Server 2000'
- IBM DB2 Migration Toolkit for MS SQL Server 7.0 and 2000
- The Federation - Database Interoperability
- DB2 Porting zone
- Administration Made Easier: Scheduling and Automation in DB2 UDB
- Focus area on DB2 UDB and Visual Studio
About the author
|Raul F. Chong is a database consultant from the IBM Toronto Laboratory and works primarily with IBM Business Partners. Raul has worked for five years in IBM, three of them in DB2 Technical Support, and two of them as a consultant specializing in database application development and migrations from other RDBMSs to DB2 UDB.|
This was first published in January 2004