Some small notes for novice DB2: new instance, database path not present, client connection. _db2

First, add a database instance:

Instances of DB2 are independent of each other, and instances can be considered as containers for databases. When the default DB2 is installed, it builds an instance named DB2. We need to create a new one, and command to knock like this:

Open the command line in the DB2 command-line tool, and then enter:

Copy Code code as follows:

DB2ICRT Instname

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

You can then use the command line to create a good later to use the control center to add an instance of the function to add the instance just created. In fact, this addition just adds an existing instance to the control center of the GUI, rather than creating an instance .... So, it must be noted that in this control center, as long as the addition of the command, it seems to be careful that it actually did not create things, just put the things in the management.

In addition, when added, the so-called instance section name is only used to represent this instance of a node names only, it seems that for a single server does not make sense. It seems to be the case that each instance node of an instance is composed of a partition instance.

Then start building the database:

OK, we'll build the database on the instance after we've established the example. Because it's a Windows environment, you're bound to have a mistake like this the first time you build it.

Copy Code code as follows:

The sql1052n database path "D:/foo/bar" does not exist.

Description

The path specified in the "<path>" parameter of the command is invalid. The path to the name does not exist, or the
Db2_create_db_on_paths a path is specified when the registry variable is disabled (Windows only)
。

It's easy to follow this statement simply by executing the command:

Copy Code code as follows:

Db2set Db2_create_db_on_path=yes

But unfortunately, I still got an error, why? In fact, you use this command to look at the know:

Copy Code code as follows:

C:/Program Files/ibm/sqllib/bin>db2set-all
[E] Db2path=c:/program Files/ibm/sqllib
[I] db2processors=0,1,2,3
[i] db2instprof=c:/documents 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:
* environment, expressed in [E]
* User-level registry, represented by [u]
* Node-level registry, represented by [n]
* Instance-level registry, expressed in [I], and
* Global level registry, represented by [G]

See, the so-called Db2_create_db_on_path variable knowledge instance level registry, so you directly execute the above command to change the DB2 this default instance of things. In order to work on our new instance instname, we must use this command:

Copy Code code as follows:

Db2set-i Instname Db2_create_db_on_path=yes

Note: In addition, you can use the DB2 from the configuration Assistant to do these configurations, the GUI operation is simple, do not write.

OK, so far, the database has been successfully established, and then the day-to-day business.

Now let's look at the DB2 client.

IBM likes to translate it into a client. With accustomed to MySQL students will certainly feel a bit abrupt it. DB2 's clients also have many different versions, and besides IBM's own clients, there are also clients from Third-party developers, and this introductory article, we'll just explore IBM's own stuff.

Here's what IBM has to say:

IBM Data Server Client

The IBM Data Server client is a fully installed client that can be used for the IBM database server. In DB2 9.5, it fully supports all supported APIs. For example, it provides a driver for Ruby. In DB2 9, you need to add Ruby support through the post installation (post-installation) download and configuration steps. The client also provides a complete graphical tool. For example, if you install an IBM Data Server Client, you can get 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 back-end IDS data server, be aware that this interface does not support this use. However, you can use the ODBC support provided by this client to make up for defects in such scenarios.

In simple terms, if an option is available for a DB2 client, it is possible. DB2 Client is a superset of all content related to DB2 connectivity. As shown in the figure above, this client must be installed if any tool support is required.

IBM Data Server Client is approximately MB. You can use the Db2iprune utility previously mentioned to customize this client to reduce it to approximately MB.

IBM Data Server Runtime Client

IBM Data Server Runtime Client is the best choice for lightweight client deployments. It is smaller than its DB2 9 partner and now provides IDS support like the IBM Data Server Client. This client is not equipped with any tools-as mentioned earlier, it was changed by the predecessor of DB2 8 (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 the connection in DB2 9.5, you cannot find this tool here. However, this client contains the DB2 CLP. This client contains all the DB2 interfaces, such as Ruby drivers,. NET drivers, and so on. Like the IBM data Server client, this client does not support the establishment of OLE DB connections to the IDS data server. The IBM Data Server Runtime client accounts for approximately 60-70 MB of disk space after installation.

If your memory needs are tight, it is not appropriate to use this large footprint client, but it provides full support for any application connectivity scenarios. If you support applications built using a variety of programming languages and do not require any tools, this client is the best choice. If you support open source applications such as Ruby on Rails and do not need tools, the client is the only option because the driver does not include precompiled binaries 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). As with all IBM data server connectivity options, this driver also supports IDS data servers. It is delivered through a single package of Db2jcc.jar (if you need to connect it to DB2 for I5/os or DB2 for z/OS data servers, a valid DB2 connect license is required) and 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, royalty-free, republishing driver for your java-based applications, and you don't need any tools, this is the best choice because it supports things like a connection set (connection concentration), Advanced features such as client automatic rerouting. Because it takes only about 2MB of space, I refer to the space used by the driver as a fingerprint (fingerprint). How common is the use of this driver? There are billions of of dollars of money per minute to work on it (or its earlier version).

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

This driver is delivered in DB2 9, and includes support for ODBC and CLI interfaces in addition to newly added. NET Support. This is a very good deployment option for. NET applications, because you no longer need to use large amounts of memory just to provide connectivity to. NET Applications. In addition, binary versions of interfaces such as Ruby, Perl, and PHP are added, eliminating the need to compile yourself (although not mentioned in the driver's name), which greatly reduces the deployment time (and deployment) of these applications.

IBM Data Server Driver for ODBC, CLI, and. NET also provides some very good Windows enhancements. For example, an installation wrapper is included that can significantly enhance the deployment features on Windows.

This driver also provides a set of merge modules, such as DB2 Runtime Client. I recommend using these merge modules for installation, because it is ideal for anyone who develops your application's installation paradigm. If you do not need the CLP or any other tools, I recommend using this driver because it does not create any administrative burden on the instance.

Now, you should know very well when to use this driver. However, it is important to note that this driver does not contain support for OLE DB. I think I need to specifically point out that ODBC and OLE DB support are usually interconnected. If you need to support an OLE DB application, install at least one DB2 run-time Client.

Note: Although the name is somewhat inconsistent, from an IDS perspective, this driver only delivers support for the. NET, PHP, Perl, and Ruby programming interfaces (it is recommended that the client continue to use the Informix SDK for C + + applications). This driver is accompanied by a royalty free license.

The new. NET Deployment option for DB2 9.5 goes well beyond the ability to provide a run-time environment for applications. If you want to deploy the most lightweight. NET development environment, you can download the standalone IBM Add-ins for Visual Studio (approximately MB) and this driver (approximately MB), and you will get an integrated IBM data server development environment specifically for. NET. Visual Studio. 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 the Visual Studio 2008 IDE, you must install these components at the Fix Pack 1 level. If Fix Pack 2 makes functional modifications to any of these components, you must make sure that all of these components are at the Fix Pack 2 level.

IBM Data Server Driver for ODBC and CLI

This driver actually provides the same functionality, features, and benefits as IBM Data Server Driver for ODBC, CLI, and. NET, and the only difference is that it absolutely does not support. NET or provides Windows-centric deployment enhancements. If you need to support the same scenario described in the previous section, you can use this driver, but note that it cannot be used with. NET applications. This driver is available from DB2 9 and is shipped with a royalty-free license.

IBM Data Server add-ins for Visual Studio

IBM Data server Add-ins for Visual Studio used to be a windows-based DB2 Client or server image, in the past just for general availability (in other words, I don't introduce technical content here). In DB2 9, this plugin was formerly known as IBM Database add-ins for Visual Studio 2005. In DB2 9.5, it was renamed to reflect the Plug-ins available for Visual Studio 2005 or Visual Studio 2008, so the version information was removed from the plug-in name.
In DB2 9.5, although DB2 Client and server images are still shipped with this plug-in, it can now be used as a stand-alone image, approximately MB in size.

Regardless of what IBM says, it's imperative that the download client is important, and DB2 clients can find it here: http://www-01.ibm.com/software/data/db2/ad/

In order for the client to connect to the server, you also need to set the communication settings for the instance. Here Select the example in the control center of the corresponding instance of the communication settings set to TCP/IP, and set the port you want. There is also a need to set the svcename of the communication portion of the DBM configuration, which can be set in the control center, which seems to be the case if the command is to be used:

Copy Code code as follows:

UPDATE DBM CFG USING SVCENAME 50001 DEFERRED;

The above are configured to connect with the client later:

I'm using runtime client here, so I need to use the command line (select the command-line tool to enter DB2 into the interactive interface):

Copy Code code as follows:

DB2 => Catalog TCPIP node MYDB2 remote 192.168.0.11 server 50000
Catalog TCPIP node command indicates that nodes are established in TCPIP connection mode
MYDB2 is the node name, you can arbitrarily specify remote server
hostname or Ip,server develop service name or port number
Db20000i the CATALOG TCPIP NODE command completed successfully.
db21056w directory changes may isn't effective until the directory cache is refreshed.
DB2 => Catalog db MYTEST as Mytestalias at node MYDB2
Catalog a database under your node, note that there must be a different alias, or there will be a link error that cannot be found on the server.
db20000i the CATALOG DATABASE command completed successfully.
db21056w directory changes may isn't effective 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

As for why, in fact, because the client maintains a catalog of its own, you need to create a new local node to hold the information of a database server before adding a corresponding database directory to connect to.

For this section, refer to this quote:

When accessing the server-side database from the client in DB2, the Connect command cannot be used directly, and the communication node must be established, and then the database connection can be established on node basis. The specific actions 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

Description

The ABC in catalog TCPIP node ABC is a knot named by you, and the knot name cannot be repeated with the existing knot name.
Catalog DB databaseName at node ABC refers to the knot name you started in the front.
ServerName Server name (remote database)
DatabaseName database name
Uid User Name
PWD Password
In addition, if the client has already established node with the same server, then if you want to connect to another database on the server, you do not need to create another node, just use the same node. In addition, the port after the server name is set up is not necessarily 50000, to see the DB engineers at the time of the setup.
Related instructions:
List DB directory lists accessible db
List node directory lists accessible nodes
Some of the other important DB2 commands

1. View the Local node directory
Command Window ENTER: DB2 list node Directory

2. Cataloging a TCP/IP node
Command window: DB2 catalog TCPIP node <node_name> remote

3. Cancel the node catalog
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 < letter >
A database in the local database directory that is not available in the System database directory can be accessed by selecting < database > right-clicking in the control center and then entering the database name you want to add or clicking the Refresh button to select the database.

6. Catalogue Database
DB2 Catalog Database <db_name> as <db_alias> at node <node_name>

7. Cancel the database catalog
DB2 Uncatalog Database <db_name>

8. Testing the connection of a remote database
DB2 Connect to <db_alias> user <user_id> using <password>

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

and MySQL orancle different, DB2 for the user's authentication is a direct integration of the operating system of the user authentication, therefore, DB2 users is the operating system users. After successful user authentication, the user permissions are validated.

As for the setting of user rights, you can configure a table, database, or even instance directly using the control center. Table permissions are select, and so on, which is clear to everyone, and database permissions to illustrate:

Database permissions
Each database permission allows the authorization identity that owns the permission to perform a specific type of operation on the entire database. Database permissions differ from privileges, which allow specific operations to be performed on a particular database object, such as a table or index.

These are database permissions.

Accessctrl
Allows the owner to grant and revoke all object privileges and database permissions (except those for audit routines) and Accessctrl, DataAccess, Dbadm, and SECADM permissions.
Bindadd
Allows the owner to create a new package in the database.
CONNECT
Allows the owner to connect to the database.
Createtab
Allows the owner to create a new table in the database.
Create_external_routine
Allows the owner to create procedures for use by the database's applications and other users.
Create_not_fenced_routine
Allows the owner to create unprotected user-defined functions (UDF) or procedures. The create_external_routine will be automatically granted to anyone who has been granted create_not_fenced_routine permission.
Note: The database manager does not prevent unprotected UDF or procedures from accessing its storage or control blocks. Therefore, users with this permission must test their UDF very carefully to make it particularly tight, and then register it as an unprotected UDF.
DataAccess
Allows the owner to access data stored in a database table.
Dbadm
Allows the owner to act as a database administrator. In particular, it grants the owner all other database permissions except Accessctrl, DataAccess, and SECADM.
EXPLAIN
Allows owners to describe query scenarios without requiring them to have privileges to access data in the tables referenced by these query scenarios.
Implicit_schema
Allows any user to implicitly create a pattern (creates an object using the Create statement and specifies a schema name that does not yet exist). SYSIBM becomes the owner of the implicitly-created schema and grants public privileges to create objects in this mode.
LOAD
Allows the owner to load data into a table.
Quiesce_connect
Allows the owner to access the database when it is in a standstill state.
SECADM
Allows the owner to act as a security administrator for the database.
SQLAdm
Allows the owner to monitor and adjust SQL statements.
Wlmadm
Allows the owner to act as a 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 identities with SECADM permissions can grant Accessctrl, DataAccess, Dbadm, and SECADM permissions. All other permissions can be granted by an authorization identity that has Accessctrl or SECADM permissions.

To drop any database permissions from public, an authorization identity with Accessctrl or SECADM permissions must explicitly revoke the permission.

About permissions This part is a big topic, have the opportunity to write later, this part of the content can refer to the documentation here to configure:

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