Use your Microsoft SQL Server 2000 skills to learn DB2 UDB V8

content:

Introduction System Structure Overview Instances and Server Windows Services Database Vault, Table Space, and File Group Object Names, View, and Index Forms, Triggers, and User-Defined Functions (UDF) Profile Security Tools The following steps? Reference information about the author's evaluation

subscription:

DeveloperWorks Newsletter

Raul F. CHONGDB2 Universal Database Consulting Services, IBM Toronto Laboratory 2003 July

This article shows how to use your current SQL Server knowledge to quickly get skills about DB2 UDB V8 for Linux, UNIX and Windows.

Introduction In today's information technology, new information is constantly impacting to us - new software products, new versions, and new features. One of the ways to keep up with these constant changes is to use existing knowledge in your hands. I will show you how to use your current Microsoft® SQL Server 2000 to quickly get skills about DB2 UDB V8 for Linux, UNIX and Windows. In this article, the term "SQL Server" refers to Microsoft SQL Server 2000, "DB2 UDB" refers to DB2 UDB V8 for Linux, UNIX and Windows. This paper focuses on the Windows platform. The first important difference between SQL Server and DB2 UDBs is that SQL Server is only supported on a Windows platform (Intel architecture). On the other hand, DB2 UDB can be supported in Linux, UNIX, and Windows platforms and many hardware architectures. The code for all these platforms is basically the same, only with a very small number of platform-specific modules. In addition, DB2 UDB products can also be extended to ISERIES® (AS / 400® and Z / OS® (mainframe)). The code base of the DB2 UDB member of iSeries and Z / OS is different from DB2 UDB for Linux, and UNIX and Windows uses their respective hardware architectures. However, the functions of these DB2 series are very similar. After you are familiar with DB2 UDB for Linux, after UNIX and Windows, you can boldly try to learn more about DB2 on other platforms. See the reference information section for more articles on how to continue to consolidate your skills. System Structure Overview The basic SQL Server structure is shown below. Figure 2 shows the structure of the DB2 UDB, please make two more comparisons. I will quote these two pictures in the entire article. Figure 1. Microsoft SQL Server 2000 Structure Figure 2. Linux, UNIX and Windows DB2 UDB V8 System Structure Instances and Services Although the term "example" is not often used in SQL Server terms, the concepts of examples are in SQL Server and DB2 UDB is similar. Users usually only referred to it as "server." For these two products, examples provide a separate environment, the database object is created, and the application runs on these objects. Since an example follows a "non-shared" architecture, an object is usually not interacting with objects of other instances. Figures 1 and 2 representation of the PROD example and DEV instance into two concepts on each other on each other. Although the concept of an example is the same in both products, it is completely different. In SQL Server, you can have a "default" instance (one only) and some named instances. The default instance is created when you first install SQL Server, and is identified by the computer name. Named instances are created after creating a default instance, and must be identified by computer_name // instance_name. To create a few SQL Server named instances on the same machine, you must use the installation CD for each new instance and install the GUI for each new installation. Note that during this process, copy SQL Server code for each instance you created, but the path is used. Only some files are shared between instances. To delete an instance, you must use the installation GUI to uninstall the instance. In DB2 UDB, "DB2" instance is created by default when the product is installed on the Windows platform.

In Linux and UNIX, the default instance is named "db2instl". To create another instance on the same machine, you only have to execute the db2icrt command. For multiple DB2 UDB instances on a machine, only a copy of the DB2 UDB code. Figure 3 shows the default DB2 UDB instance "DB2" in the DB2 UDB Control Center GUI and other two instances created with the DB2icRT command. Figure 3. DB2 UDB Control Center GUI for DB2 UDB instance To reference a given DB2 UDB instance from the command line interface, use the DB2Instance environment variable. This variable allows you to specify the current activity instance that all commands will be applied. For example, if DB2Instance is set to a Prod, then you will issue a CREATE DATABASE MyDB1 command, then you will create and instance prods associated with the database. If you want to create the database on the instance DB2, you must first change the value of the DB2Instance variable to DB2. Another simple way to identify the instance you want to use is to use the DB2 UDB Control Center GUI as shown in Figure 3. You may have to add instances to the GUI by right-clicking on Instances and selecting Add to GUI, so you can see the new instance of the entry. After you create a DB2 UDB instance in Windows, a corresponding service as the instance name is also created. You can execute the DB2IDrop command to delete the DB2 instance. This command will not remove DB2 UDB code. If it is Windows, it removes the corresponding Windows service of the instance. In summary, the examples in SQL Server and DB2 UDB are conceptually, and different are implemented. SQL Server requires a copy of a different SQL Server code for each new instance, while DB2 UDB does not need. In SQL Server, Enterprise Manager GUI can use Enterprise Manager GUI to manage instances, while in DB2 UDB, use the Control Center GUI to achieve similar purposes. Windows Services Table 1 describes some of the main DB2 UDB Windows services. Table 1. DB2 UDB Windows Service

Service Name Description DB2 - - This is an engine for a given instance. It handles all SQL statements and manages the database. If the partition support is enabled, DB2 can support multiple partitions on different machines. Therefore, shows which partition you are processing. If the partition is not enabled, the partition number will not appear. DB2 Governor collects information about the application of the DB2 database, and monitors the operation based on your defined rules and takes action to some operations. For example, you can specify a rule to limit the time to one hour, and if this limit is exceeded, it is forced to connect to the database. DB2 JDBC Applet Server provides JDBC server support for DB2 applications using JDBC Type 3 drivers. DB2 License Server monitors whether it meets DB2 licenses. DB2 Remote Command Server supports inter-partition communication between multi-partition databases. DB2 Security Server When performing authentication on the client, authenticate the DB2 database user. DB2DAS - DB2DAS00 This is DB2 Administration Server. It supports local and remote database management requests. The Windows default name for DB2 Administration Server is DB2DAS00. This server uses with the tool directory server to manage jobs, warnings, etc. Figure 4 shows a DB2 UDB Windows service with a default DB2 instance and a PROD and DEV instance created with a DB2icrt command. Figure 4. DB2 UDB Windows service has understood the Windows service used by DB2 UDB, allowing us to associate these services with the services on SQL Server. Please see Table 2 below. Table 2. Map the SQL Server Windows service to DB2 UDB Windows services

SQL Server service matches DB2 UDB Windows service MSSQLServer service. This service represents the default SQL Server instance. DB2 - DB2-0 service. This service represents the default "DB2" instance. MSSQL $ DB2 - SQLSerVERAGENTDB2DAS - DB2DAS00. This service represents DB2 Administration Server (DAS) * SQLAGENT $ DB2DAS - DB2DAS00. This service represents DB2 Administration Server (DAS) * Microsoft Distributed Transaction Coordinator (MS DTC) Sync Point Manager (SPM) to support distributed work units. The database configuration parameter spm_name identifies the name of the synchronization point manager instance to the Database Manager. Microsoft Search has no matching DB2 UDB Windows service; however, Net Search Extender software can be used for similar purposes. * Each DB2 machine can only have a DAS. As shown in Figure 1, in SQL Server, one instance can contain multiple databases. Each database is a separate unit; however, the metadata about all user databases is saved in the master database, so the query can access the tables in two different databases. When creating an instance, several databases are also created by default, such as Tempdb, Master, Model, and Northwind, etc. SQL Server uses two levels to store metadata: instance level (using system tables in the Master database) and database level (using user database itself system table). Two databases of Master and Tempdb can be shared by other databases in the instance. Other views such as views (View) are also created by default when creating user databases. As shown in Figure 2, in DB2 UDB, an example can also accommodate multiple databases, each of which is real closed, independent unit. Each database has its own directory table space (storage metadata), temporary table space, and user table space. But no database can share metadata information across databases like the Master database in SQL Server. Similarly, there is no database to be shared in other databases like TEMPDB in SQL Server to maintain temporary data. The DB2 UDB contains a binarily called a system database directory, with an entry for all databases connected to the DB2 machine. This directory is saved at instance levels. Figure 2 shows the independence of each database in DB2 UDB. In addition, you can also see that each database has your own transaction log, this is also very similar to SQL Server. Databases are not created by default when creating an instance. You need to use the Create Database command to explicitly create a database. You can also create a database using the Control Center shown in Figure 5 and Figure 6. Figure 5. Creating a DB2 UDB Database in Figure 6 using the Control Center GUI Figure 6. Creating a DB2 UDB database using the Control Center GUI (continued) In Figure 6, you can also see what happens when you click Show Command. All DB2 UDB Control Center GUI screens will display the SQL statements or commands that are actually executed in the background.

These commands can be saved in the script for future execution, or copied and executed from the Command Line Processor, CLP) tool, or the Command Center GUI tool. These two tools are equivalent to SQL Server's ISQL and Query Analyzer. Databases in an instance usually do not interact with each other; however, this purpose can be achieved if the application has this requirement. You can view the reference information section to get an article about the federal. The container, table space, and file groups are in the DB2 UDB, and the container is where data is actually stored, and can be divided into files, directories, and bare devices. There is no "container" concept in SQL Server; but uses a file to store data. In DB2 UDB, the tablespace is a logical object, used as a layer between logic tables and physical containers. When you create a table space, you can associate it and a specific buffer pool (database cache) and associate to a specific container. This gives you the flexibility in your management and performance. For example, if there is a "hot" table, you can define it in its own table, and this table space is associated with its own buffer pool. This helps ensure that the data of the table is always cache in memory. When you create a database with a default option, three tablespaces are created, as described in Table 3. Table 3. When creating a database, the DB2 UDB table space table space name created by default describes the directory table space of the syscatspace contains metadata. TempSpace1 is used to perform system temporary tablespaces such as connection and sort operations. The name of the table space can be changed. UserSpace1 This table space is optional that stores user tables when you do not explicitly specify table spaces when you create a table. Because the database is a stand-alone unit, you cannot share the table space across the database. Also because the tablespace is only known in one database, two different databases can have the same name. You can see in Figure 2, the database mydb1 has a table space called MyTBLS, and the database MYDB2 also has a table space of the same name. SQL Server uses file groups in a similar manner similar to DB2 UDB table space. Two different databases can have the same name file group, this is similar to the table space. As shown in Figure 1, the database MYDB1 has a file group named myFileGroup, and the database MYDB2 also has a file group of the same name. The DB2 UDB table space can be divided into SMS (System Management Space) or DMS (Database Management Space). The SMS table space is managed by operating system and can only be a directory. They will automatically grow, so they provide better performance and do not require too much management. This is the same as the behavior of the SQL Server data file (if you allow automatic growth). The DMS table space is managed by DB2 UDB, which can be a file or bare device. This type of table space can achieve optimal performance, but requires certain management. For example, you need to specify the amount of space you want to assign to the table space in advance because the growth is not automated. The location and size of the transaction log can be specified in the database configuration file. Table 4 below shows how the SQL Server database corresponds to the DB2 UDB database or table space. Table 4. SQL Server database how to correspond to DB2 UDB database or table space

SQL Server Database DB2 UDB Database or Table Space MASTERSYSCATSPACE (directory table space); in DB2 UDB, this information is only saved on the database level. Model does not have the same database / table space; however, DB2 provides a tool called "DB2LOOK", which is used to copy the structure of the database to a script file to create a new database in the future. MSDB Tool Directory database, it can be created at installation, or you can create a Northwindsample Database Pubssample Database TEMPDBTEMPSPACE1 table space DistributionDBTEMPSPACE1 table space DistributionDB2 UDB provides built-in replication support for all DB2 UDB Series after installation. Replication of relational management systems involving non-DB2 requires information information integrator software. The structure of the object name SQL Server object name consists of four parts, as shown below: [SQL Server Instance]. [Database Name]. [Owner]. [Object Name] Please note: The first three parts of these names are optional. Depending on the context of the object. In DB2 UDB, the structure of the object consists of two parts: schema_name.Object_name mode name is used to group objects logically. The model name is not necessarily matched with the user identity. Any user with an Implicit_Schema privilege can create an object using a non-existing mode. For example, suppose "Peter" has an Implicit_Schema privilege and executes the following command: CREATE TABLE World.Tablea (LastName Char (10)) In this case, the table World.Tablea is created, where World is a newly created mode. If Peter does not explicitly indicate this mode, the table peter.tablea is also created because the default uses the connection ID. In DB2 UDB, you always explicitly connect to a database before issuing commands, which explains why the database name is not part of an object name structure. Tables, views, and index tables, views, and indexes are basically the same in SQL Server and DB2 UDB. For the Creating GUI, when using SQL Server Enterprise Manager GUI tool, you must create a chart before you create an index, and when you use DB2 UDB Control Center, you can create an index directly from the GUI without the intermediate steps. SQL Server uses the Master database to store information about other user databases, but the user database itself is a separate unit, just like in DB2 UDB. Therefore, in two products, tables, views, or indexes can have the same name in two different databases. SQL Server allows to connect to different databases (and SQL Server); DB2 UDB is only available when explicitly activating the federal. The main purpose of the stored procedure, trigger, and user-defined function (UDF) is to describe the DB2 UDB environment by comparison with the SQL Server environment. Talking about the use of stored procedures, triggers and UDF logic logic is a quite a wide range of topics, so this is just a brief introduction. First, both products support all these objects.

Just SQL Server uses its proprietary Transact-SQL (T-SQL), while DB2 uses SQL Process Language (SQL PL), both are extensions of SQL / PSM standards. In SQL Server, Transact-SQL actually applies everything, including implementing the SQL Server command. In DB2 UDB, the situation is not the case. DB2 uses a different API to implement its command, and because the SQL PL stored procedure is first converted to C, it requires a C compiler to develop these stored procedures. Transact-SQL is an interpretation language executed in this unit in the SQL Server engine. When running it, it will become an explanatory byte code and optimized at each run. The C language implementation of the stored procedure can provide performance advantages because the code is only compiled once (especially when running in a non-protected mode); however, it also adds to the demand for the C compiler. In the future release of DB2 UDB, on the basis of providing existing functions, it is desirable to support SQL PL stored procedures without the need for a C compiler, which allows users to choose how they want to implement SQL PL stored procedures. With DB2 UDB, you can use inline SQL PL in the triggers and functions, which means you don't need a C compiler. A subset of SQL PL statements can be supported by this method. Profile SQL Server stores its configuration information at instance level and database level. Using Enterprise Manager GUI, right-click a given instance or database and select Properties to display a window with possibly configuring options. In DB2 UDB, configuration parameters are also stored in instance levels (called database manager profiles) and database levels (called database profiles). With Control Center, if you right-click a given instance and select Configure Parameters, you will see the window shown in Figure 7. Figure 7. DB2 UDB Database Manager Configuration Parameters (Instance Level) At the database level, right-click on a given database and select Configure Parameters, the window shown in Figure 8 will appear. Figure 8. Database configuration parameters (Database Level) DB2 UDB provides many parameters that can be used to configure the system; however, if you like to automatically configure the system with a simple way, use the autoconfigure command (or Configuration Advisor GUI), It sets the database manager and database configuration parameters as an optimal value based on some of the information you provide. Figure 9 shows the Configuration Advisor. Figure 9. DB2 UDB Configuration Advisor In addition to the configuration file, DB2 UDB usually also uses the DB2 registry variable to a platform-specific configuration. Note that the DB2 registry variable does not have any relationship with the Windows registry. Users can use the db2set command to view and change these variables. Security SQL Server uses the concept of authentication, roles, and privileges to achieve security. Support for two ways of authentication:

Windows certification. Windows operating system authentication login ID and password, and only the login ID is passed to SQL Server, and the latter matches this identity with the SysxLogins system table. Certification of the mixing method. SQL Server authenticates the login and password according to its information in the SysxLogins system table. Once authenticated, users cannot do anything about SQL Server unless he / she is assigned a role or a given privilege. The role is used to group the user as a separate unit, and apply the license on this unit. Therefore, you don't have to grant a separate permissibility to several users. Instead, you can create a role that includes all of these permissions, then assign this role to the user. In DB2 UDB, the user does not exist in the database, but is managed by the operating system. Therefore, DB2 UDB certification is a bit similar to Windows authentication of SQL Server to some extent; however, the database login information will not be saved in any database table. Any operating system user may potentially use DB2 UDB; however, unless they are given a given DB2 UDB permission or privilege, they can do much. Permissions and privileges can be easily handled and revoked by Control Center GUI. You may first have to add a user or group from the available operating system user or group to the Control Center. In addition, the term "role" is not used in DB2 UDB; but the term "authority" is used, this is similar to the fixed server and database role of SQL Server. DB2 UDB does not support user-defined database roles of MS SQL Server; however, an operating system group can be given permissions and privileges to implement role functions. The permissions supported by DB2 UDB include: sysadm, sysctrl, sysmaint, dbadm, and load. With the Grant SQL statement, you cannot give sysadm, sysctrl, and sysmaint permissions. These special permissions can only be set from the Database Manager profile. DB2 UDB users with SYSADM privileges are equivalent to SQL Server users with sysadmin roles. DB2 UDB also uses the term "privilege", which is equivalent to SQL Server permission. There are database privileges (Connect, CreateTab, etc.) and database object privileges (Schema, Table, View, etc.). Figure 10 shows the 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. Figure 10. DB2 UDB Security The following Table 5 takes one of the security concepts of SQL Server and one of the DB2 UDBs. Table 5. Put the security concept of SQL Server and one of the DB2 UDB

SQL Server DB2 UDB two authentication methods: Windows authentication and hybrid approach authentication by operating system processing; very close to Windows authentication of SQL Server. Role permissions (all predefined, no user-defined permissions.) Permissions privileges We have simply discussed some of the tools used in SQL Server and DB2 UDB; however, there are many tools that do not involve this article. . Figure 11 shows the menu of DB2 UDB. This should make you a general understanding of the tools and themes discussed in this article. Figure 11. DB2 UDB menu Use the command line If you prefer to use the command line interface, you should use the ISQL utility for SQL Server. The correspondence interface in the DB2 UDB is the command line processor (CLP). Figure 12 shows the DB2 UDB CLP utility. We first execute the command db2cmd from the command prompt window to open the command window. This will initialize the DB2 environment you need to run the CLP. Then we call the CLP by executing the command db2. Figure 12. DB2 UDB CLP (Command Line Processor) SQL Server and DB2 UDB provide a command line interface that is also the GUI tool. There is Query Analyzer in SQL Server. In the DB2 UDB, it is a Command Center as shown in Figure 13. Figure 13. DB2 UDB Command Center ends the text using your existing Microsoft SQL Server 2000 knowledge to introduce you to DB2 UDB V8 for Linux, Windows and UNIX. Since SQL Server is only supported by the Windows platform, this article focuses on this platform; however, all you learned about DB2 UDB knowledge (not very detailed) can also be applied to Linux and UNIX platforms. Moreover, since the Windows platform is used, we use the GUI tools for two products to explain most of the concepts. However, all explanations have an equivalent command. As a final review of SQL Server and DB2 UDB architecture, let us compare the plot 14 and Figure 15. They use SQL Server Enterprise Manager and DB2 UDB Control Center respectively display their respective database objects. Figure 14. MS SQL Server Enterprise Manager - Summary Figure 15. DB2 UDB Control Center - Summary Next, what should I do? I suggest you see the article "Introduction to DB2 UDB V8 For UNIX, Linux and Windows", which introduces the concept covered by this article in more detail. I hope this article will bring you a fun and wish you a smooth use of DB2! References "Introduction to DB2 Distributed User DB2 OS / 390 and Z / OS" "" "" "PORTING TO DB2 Universal Database Version 8 from Microsoft SQL Server 2000 "IBM DB2 MIGRATION Toolkit for MS SQL Server 7.0 and 2000 Federal - Database Interoperability DB2 Transplantation Speed ​​For Management Be easier: DB2 General Database Scheduling and Automation About DB2 UDB and Visual Studio Zone