Notes for beginners of DB2: new instance, database path does not exist, client connection.

First, add a database instance:

DB2 instances are mutually independent. Instances can be considered as database containers. By default, DB2 creates an instance named DB2 after it is installed. Here we need to create a new one. The command is as follows:

Open the command line in the db2 command line tool and enter:

Copy codeThe Code is as follows:
Db2icrt INSTNAME

The instance name is quite disgusting. It must be a name of less than 8 characters.

After the instance is created using the command line, you can use the Add instance function of the control center to add the instance you just created. In fact, this addition only adds existing instances to the control center of the GUI, rather than creating instances .... Therefore, it is important to note that in this control center, as long as the command is added, it seems that you have to be careful that it has not actually created anything, just to take the existing thing in for management.

In addition, the so-called instance node name is only used to indicate the name of a node of the instance. It seems that it is of little significance to a single server. It seems to be meaningful when every instance node corresponding to an instance forms a partition instance.

Then begin to create a database:

Okay. After the instance is created, we need to create a database on the instance. Because it is a Windows environment, you will encounter such an error for the first time.

Copy codeThe Code is as follows:
SQL1052N database path "D:/foo/bar" does not exist.

Note:

The path specified in the <path> parameter of this command is invalid. The path of this name does not exist, or
When the DB2_CREATE_DB_ON_PATHS registry variable is disabled, the path is specified (only for Windows)
.

According to this statement, you only need to execute this command:

Copy codeThe Code is as follows:
Db2set DB2_CREATE_DB_ON_PATH = YES

Unfortunately, I still got an error. Why? You can use this command to view the details:

Copy codeThe Code is as follows:
C:/Program Files/IBM/SQLLIB/BIN> db2set-all
[E] DB2PATH = C:/Program Files/IBM/SQLLIB
[I] DB2PROCESSORS =
[I] DB2INSTPROF = C:/Documents ents and Settings/All Users/Application Data/IBM/DB2/testdb2
[I] DB2COMM = TCPIP
[G] DB2_EXTSECURITY = YES
[G] DB2SYSTEM = SENDS-TV
[G] DB2PATH = C:/Program Files/IBM/SQLLIB
[G] DB2INSTDEF = DB2
[G] DB2ADMINSERVER = DB2DAS00

C:/Program Files/IBM/SQLLIB/BIN> db2set -?
-All: displays all the local environment variables that appear, as defined in the following items:
* Environment, represented by [e]
* User-level registry, represented by [u]
* Node-level registry, represented by [n]
* Instance-level registry, represented by [I], and
* Global Registry, represented by [g]

You can see that the so-called DB2_CREATE_DB_ON_PATH variable has an instance-level registry. Therefore, you can directly execute the above command to change the default instance of db2. To take effect on the Instance name we created, we must use this command:

Copy codeThe Code is as follows:
Db2set-I INSTNAME DB2_CREATE_DB_ON_PATH = YES

Note: In addition, you can use the configuration assistant that comes with DB2 to perform these configurations. The operations in the GUI are simple and you will not write them.

Okay, so far, the database has been successfully established, and the next step is the daily transaction.

Now let's look at the DB2 client.

IBM prefers to translate it into a client. Those who are used to MySQL will surely feel a little abrupt. The DB2 client has many different versions. In addition to the IBM Client, there are also clients obtained by third-party developers, we only discuss IBM's own things.

The following is IBM's statement:

IBM Data Server Client

The IBM Data Server Client is a complete installation Client for the IBM Data Server. In DB2 9.5, it fully supports all supported APIs. For example, it provides drivers for Ruby. In DB2 9, you must follow the post-installation (post-installation) download and configuration steps to add Ruby support. This client also provides a complete graphical tool. For example, if you install an IBM Data Server Client, you can obtain Configuration Assistant, IBM Add-ins for Visual Studio, language support, and so on.

If you plan to use this client to support ole db applications in a backend IDS Data Server, note that this interface does not support this operation. However, the ODBC support provided by this client can be used to make up for defects in such scenarios.

Simply put, if an option can be used for a DB2 client, it is available. DB2 Client is a superset of all content related to DB2 connectivity. As shown in, this client must be installed if any tool is required.

IBM Data Server Client is about 330 MB. You can use the db2iprune utility mentioned earlier to customize this client to reduce it to about 200 MB.

IBM Data Server Runtime Client

IBM Data Server Runtime Client is the best choice for lightweight Client deployment. It is smaller than its DB2 9 partner, and now provides IDS support like IBM Data Server Client. This client does not have any tools-as mentioned above, it was changed from its predecessor in DB2 8 (the CCA was removed from the DB2 8.2 Run-Time client. It is no longer part of the DB2 9 Runtime Client ). If you want to use Configuration Assistant to graphically configure connections in DB2 9.5, you cannot find this tool here. However, this client contains the DB2 CLP. This client contains all DB2 interfaces, such as Ruby drivers and. NET drivers. Like the IBM Data Server Client, this Client does not support establishing an ole db connection with the IDS Data Server. The IBM Data Server Runtime client occupies approximately 60-70 MB of disk space after installation.

If your memory needs are very tight, it is not suitable for clients with a large footprint, but it provides full support for any application connectivity scenarios. This client is the best choice if you support applications built in a variety of programming languages without any tools. If you support open-source applications such as Ruby on Rails without tools, this client is the only choice, because the driver does not include pre-compiled binary files for open-source APIs such as Ruby and PHP.

IBM Data Server Driver for JDBC and SQLJ

This driver is very similar to the driver in DB2 9, but some technical enhancements and fixes have been made (this driver is also used in the pureQuery environment ). Like all IBM data server connectivity options, this driver also supports IDS data servers. It uses db2jcc. jar single package delivery (if you need to Connect it to the DB2 for i5/OS or DB2 for z/OS data server, you need a valid DB2 Connect License ), the service is delivered in accordance with the JDBC 3.0 specification. DB2 9.5 also supports the JDBC 4.0 specification through a package called db2jcc4. jar.

If you need to use a lightweight, free-of-copyright re-release driver for Java-based applications without any tools, this is the best choice, because it supports advanced features such as connection concentration and automatic client re-routing. Because it only takes about 2 MB of space, I call the space used by the driver a fingerprint (fingerprint ). How common is the use of this driver? Billions of dollars per minute rely on it (or its earlier version ).

IBM Data Server Driver for ODBC, CLI, and. NET (for Windows only)

This driver is delivered in the way of DB2 9. In addition to the newly added. NET support, it also supports ODBC and CLI interfaces. This is an ideal deployment option for. NET applications, because you no longer need to use a large amount of memory to provide connectivity to. NET applications. In addition, a binary version is added for interfaces such as Ruby, Perl, and PHP, so you do not need to compile it yourself (although not mentioned in the driver name ), this greatly reduces the deployment time (and deployment work) of these applications ).

IBM Data Server Driver for ODBC, CLI, and. NET also provide some very good Windows enhancements. For example, a package can be installed to significantly enhance the deployment features on Windows.

The driver also provides a combination of modules, such as the DB2 Runtime Client. I recommend using these modules for installation, because it is very suitable for anyone who develops your application installation examples. If you do not need CLP or any other tool, I recommend this driver because it does not generate any instance management burden.

Now you should know exactly when this driver will be used. However, note that this driver does not support ole db. I think I need to point this out in particular. ODBC and OLE DB support should be normally correlated. To support an ole db application, install at least one DB2 Run-Time Client.

Note: although the name does not match, from the IDS perspective, this driver is only for delivery. NET, PHP, Perl, and Ruby programming interfaces (it is recommended that clients continue to use Informix SDK for C/C ++ applications ). This driver comes with a free release license.

The new. NET deployment options of DB2 9.5 go far beyond the runtime environment for applications. If you want to deploy the most lightweight. NET development environment, you can download the independent IBM Add-ins for Visual Studio (about 30 MB) and this driver (about 10 MB), you will get a dedicated.. NET integrates the Visual Studio of the IBM data server development environment. Remember, if you use this method, IBM Add-ins for Visual Studio and IBM Data Server Driver for ODBC, CLI, and. NET must be at the same code level. For example, if you plan to support Visual Studio 2008 IDE, you must install these components at the Fix Pack 1 level. If Fix Pack 2 makes functional changes to any of these components, make sure that all these components are at the Fix Pack 2 level.

IBM Data Server Driver for ODBC and CLI

This Driver actually works with IBM Data Server Driver for ODBC, CLI, and. NET has the same functions, features, and advantages. The only difference is that it does not support.. NET or provides Windows-centered deployment enhancements. If you need to support the same scenario described in the previous section, you can use this driver, but be careful not to use it for. NET applications. This driver is available from DB2 9 and comes with a copyright-free release license.

IBM Data Server Add-ins for Visual Studio

In the past, IBM Data server Add-ins for Visual Studio was included as a Windows-based DB2 Client or server image for general availability (in other words, I will not introduce technical content here ). In DB2 9, this plug-in was previously called IBM Database Add-ins for Visual Studio 2005. In DB2 9.5, It is renamed to reflect the plug-ins that can be used for Visual Studio 2005 or Visual Studio 2008, so version information is removed from the plug-in name.
In DB2 9.5, although the DB2 Client and server image are still attached with this plug-in, it can now be used as an independently downloaded image, which is about 30 MB.

Whatever IBM says, it is imperative to download the client, DB2 clients can look for: http://www-01.ibm.com/software/data/db2/ad/

To allow the client to connect to the server, you also need to set the communication settings for the instance. Select an instance and set the communication settings of the corresponding instance to TCP/IP in the control center, and set the port you want. In addition, you also need to set the SVCENAME variable in the communication part Of the DBM configuration in the control center. If you want to use the command, it seems like this:

Copy codeThe Code is as follows:
Update dbm cfg using svcename 50001 DEFERRED;

After the above configurations are complete, you can use the client to connect:

Here I use the runtime client, so we need to use the command line (select the command line tool and enter db2 to enter the interactive interface ):

Copy codeThe Code is as follows:
Db2 => catalog tcpip node MYDB2 remote 192.168.0.11 server 50000
// The catalog tcpip node command indicates that a node is established in the tcpip connection mode.
// MYDB2 is the node name. You can specify the remote server
// Specify the service name or port number for the hostname or ip address.
DB20000I The catalog tcpip node command completed successfully.
DB21056W Directory changes may not be valid tive until the directory cache is refreshed.
Db2 => catalog db MYTEST as MYTESTALIAS at node MYDB2
// List a database under your node. Note that a different alias must be created here. Otherwise, the server cannot be found.
DB20000I The catalog database command completed successfully.
DB21056W Directory changes may not be valid tive until the directory cache is refreshed.
Db2 => connect to MYTESTALIAS user USERNAME using PASSWORD

Database Connection Information

Database server = DB2/NT 9.7.0
SQL authorization ID = USERNAME
Local database alias = MYTESTALIAS

The reason for this is that the client also maintains a catalog. You need to create a local node to save the information of a database server, and then add a corresponding database directory to connect.

For details about this part, refer to the reference section:

When you access a database on the server from a client in DB2, you cannot directly use the connect command. Instead, you must first establish a communication node and then establish a database connection based on the node. The specific operations on the command line are as follows:

-> Db2 catalog tcpip node ABC remote serverName server 50000
-> Db2 catalog db databaseName at node ABC
-> Db2 connect to databaseName user Uid using Pwd

Note:

ABC in catalog tcpip node ABC is the name of a node you can start from. The name cannot be repeated with the existing name.
ABC in catalog db databaseName at node ABC refers to the name you mentioned earlier.
ServerName server name (remote database)
DatabaseName Database Name
Uid Username
Pwd Password
In addition, if the client has already established a node with the same server, if you want to connect to another database on the server, you do not need to create another node and use the same node directly. In addition, the port after the server name is not necessarily 50000 when the node is created, depending on the Database Engineer's current settings.
Related commands:
List db directory list accessible db
List node directory list Accessible Nodes
Other important DB2 commands

1. view the local node directory
In the command window, enter db2 list node directory.

2. cataloguing a TCP/IP Node
Command window: db2 catalog tcpip node <node_name> remote

3. Cancel node Cataloguing
Db2 uncatalog node <node_name>

4. view the system database directory
Db2 list database directory

5. view the local database directory
Db2 list database directory on <drive letter>
If the database is in the local database directory but not in the system database directory, you can right-click <database> in the control center and choose add, enter the name of the database to be added or click the refresh button to select a database. After joining the database, you can access the database.

6. cataloguing Database
Db2 catalog database <db_name> as <db_alias> at node <node_name>

7. Cancel database Cataloguing
Db2 uncatalog database <db_name>

8. Test the remote database connection.
Db2 connect to <db_alias> user <user_id> using <password>

 

Next, an obvious problem is user authentication and its permissions.

Unlike MySQL Orancle, DB2 directly integrates the user authentication of the operating system for user authentication. Therefore, db2 users are operating system users. After the user authentication is successful, the user permissions are verified.

For user permission settings, you can directly use the control center to configure a table, database, or even instance. The table permission is 'select' and so on. We all know that the database permission should be described as follows:

 

Database Permissions
Each database permission allows an authorization ID with this permission to perform certain types of operations on the entire database. Database permissions are different from privileges. The latter allows you to perform specific operations on specific database objects (such as tables or indexes.

These are database permissions.

ACCESSCTRL
Allow the owner to grant and revoke all object privileges and database permissions (except for audit routine privileges) and ACCESSCTRL, DATAACCESS, DBADM, and SECADM permissions.
BINDADD
Allows the owner to create a new package in the database.
CONNECT
Allow the owner to connect to the database.
CREATETAB
Allows the owner to create a new table in the database.
CREATE_EXTERNAL_ROUTINE
The owner creation process is allowed for database applications and other users.
CREATE_NOT_FENCED_ROUTINE
Allows the owner to create unprotected user-defined functions (udfs) or processes. CREATE_EXTERNAL_ROUTINE will be automatically granted to any user who has been granted the CREATE_NOT_FENCED_ROUTINE permission.
Note: The database manager does not prevent unprotected udfs or processes from accessing its memory or control blocks. Therefore, users with this permission must carefully test their udfs to make them very strict and then register them as unprotected udfs.
DATAACCESS
Allow the owner to access the data stored in the database table.
DBADM
The owner is allowed to act as the database administrator. In particular, it grants the Owner all database permissions except ACCESSCTRL, DATAACCESS, and SECADM.
EXPLAIN
Allow the owner to describe the query scheme without having the privilege to access the data in the table referenced by the query scheme.
IMPLICIT_SCHEMA
Allows any user to implicitly CREATE a mode (use the CREATE statement to CREATE an object and specify a mode name that does not exist ). SYSIBM becomes the owner of the implicitly created mode and grants the PUBLIC permission to create objects in this mode.
LOAD
Allow the owner to load data into the table.
QUIESCE_CONNECT
Allow the owner to access the database when the database is paused.
SECADM
The owner is allowed to act as the security administrator of the database.
SQLADM
Allows the owner to monitor and adjust SQL statements.
WLMADM
The owner is allowed to act as the workload administrator. In particular, the owner of the WLMADM permission can create and delete Workload Manager objects, Grant and revoke Workload Manager privileges, and execute Workload Manager routines.
Only authorized IDs with the SECADM permission can be granted the ACCESSCTRL, DATAACCESS, DBADM, and SECADM permissions. All other permissions can be granted by an authorization ID with ACCESSCTRL or SECADM permissions.

To remove any database permissions from the PUBLIC, the authorization id with the accessctrl or SECADM permission must be explicitly revoked.
 

This part of permission is a big topic and has the opportunity to write it later. You can refer to the documentation here to configure this part:

Http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp? Topic =/com.ibm.db2.luw.admin.sec.doc/doc/c0005524.html