DB2 Basic commands

How to connect to DB2 through CMD, you can connect to db2 using db2admin through CMD, open CMD as administrator then run the next command

runas /user:db2admin db2cmd.exe

---------------------------

Some applications like Crystal Report can't connect to remote DB2 instance, in this case we should catalog remote DB2 DB to act as if it is local DB!

To catalog remote DB we should create node point to remote server then catalog DB by name inside this node.


Steps:
db2cmd
db2 catalog tcpip node <NodeName> remote <IP> server <Port>
db2 catalog db <DB Name> at node <NodeName>

Note: we can catalog DB with alise name
db2 catalog db <DB Name> as <NewDB_Name> at node <NodeName>

To list available nodes 
db2 list node directory


To list available nodes and available DBs

db2 list db directory

To un-catalog DB / Node
db2 uncatalog db <DB Name>
db2 uncatalog node <Node Name> 

Create Database
db2 create db <DB Name>

To Connect to DB
db2 connect to books user db2admin using your_password

To View available tables after connect to DB
list tables

Backup database
db2 backup database <DB Name>   to "c:\mrafie" exclude logs without prompting 

note: when backup "TEST1" DB the backup name will be <DB Name>.0.DB2.xxx.TimeStamp

Drop Database
db2 drop db <DB Name>

Restore DB
db2 restore db <DB Name> from "c:\mrafie"

Connect to exist DB
db2 connect to <DB Name>

Notes: some times when connect to DB you get error "Error roll-forward pending" and you can solve this error using the next command
db2 rollforward db <DB Name> to end of logs and stop

View Current DB2 version
db2level

View DB2 instances (in case you have many DB2 versions installed)
db2ilist 

Stop/Start DB2 instance 
db2stop
db2start

Change DB configuration
db2 connect to test1
update database configuration using DLCHKTime 5000 immediate;

Change DB2 Instance configuration 
update DB config using DLCHKTime 5000 immediate;

View DB2 memory/processor usage 
db2pd -osinfo

View DB2 license info
db2licm -L






Sample Task

db2 Create database with name "strestst" then backup it and restore to new database with name "newstres"

1)
db2 CREATE DATABASE "strestst" AUTOMATIC STORAGE YES ON 'c:' DBPATH ON 'c:' USING CODESET 1256 TERRITORY AA

2)
db2 CONNECT TO "strestst"
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
db2 CONNECT RESET
db2 DEACTIVATE DATABASE "strestst"
db2 BACKUP DATABASE "strestst" TO "c:\rafie" COMPRESS EXCLUDE LOGS WITHOUT PROMPTING


3)
db2 CREATE DATABASE "newstres" AUTOMATIC STORAGE YES ON 'c:' DBPATH ON 'c:' USING CODESET 1256 TERRITORY AA

4)
db2 RESTORE DATABASE strestst FROM "c:\rafie" TAKEN AT 20181108112835 INTO "newstres" WITHOUT PROMPTING

5)
db2 ROLLFORWARD DATABASE "newstres" TO END OF LOGS AND COMPLETE



Get DB2 Port on Windows

On the Windows DB2 server, open a DB2 Command Window and execute the command

db2 get database manager configuration | findstr /i svce

This should provide some output like:

 TCP/IP Service name                          (SVCENAME) = db2c_DB2
 SSL service name                         (SSL_SVCENAME) =

SVCENAME is the port that DB2 is listening on. To resolve this name to an actual port number, you need to refer to the services file, which is located at %SystemRoot%\system32\drivers\etc\services.

1. For Linux
2.  Open a command prompt.
3. Enter cd /usr/etc.
4. Enter cat services.
5. Scroll through the list of services until you find the connection port number for the database instance of the remote database. The instance name is usually listed as a comment. If it is not listed, complete the following steps to find the port:
   1. Open a DB2® command prompt.
   2. At the DB2 command prompt, verify that you are on the correct instance:

      get instance

      DB2 reports the current instance.
   3. Run the following command to find the service name for your instance:

      get dbm cfg | grep SVCE

      DB2 reports the service name. For example:

      TCP/IP Service name          (SVCENAME) = db2cdb2inst24

   4. Use the service name to find the port number in the services file. For example, enter a command similar to this one:

      grep service_name /etc/services

      DB2 returns the information. For example:

      service_name   50012/tcp   # Connection port for DB2 instance instance

Grant Access to User

Example 1: Grant SELECT privileges on table DSN8A10.EMP to user PULASKI.

   GRANT SELECT ON DSN8A10.EMP TO PULASKI;

Example 2: Grant UPDATE privileges on columns EMPNO and WORKDEPT in table DSN8A10.EMP to all users at the current server.

   GRANT UPDATE (EMPNO,WORKDEPT) ON TABLE DSN8A10.EMP TO PUBLIC;

Example 3: Grant all privileges on table DSN8A10.EMP to users KWAN and THOMPSON, with the WITH GRANT OPTION.

   GRANT ALL ON TABLE DSN8A10.EMP TO KWAN,THOMPSON WITH GRANT OPTION;

Example 4: Grant the SELECT and UPDATE privileges on the table DSN8A10.DEPT to every user in the network.

   GRANT SELECT, UPDATE ON TABLE DSN8A10.DEPT
     TO PUBLIC;

Example 5: Grant ALTER privileges on table DSN9910.EMP to role ROLE1:

   GRANT ALTER ON TABLE DSN9910.EMP TO ROLE ROLE1;

View Table Columns/Types

DESCRIBE TABLE orders;

Db2 ALTER TABLE ALTER COLUMN

1 2 3

ALTER TABLE orders     ALTER COLUMN created_date     SET DATA TYPE TIMESTAMP;

1 2 3

ALTER TABLE orders     ALTER COLUMN note     SET DATA TYPE VARCHAR(255);

1 2 3

ALTER TABLE orders     ALTER COLUMN created_date     SET DEFAULT CURRENT_TIMESTAMP;

1 2 3

ALTER TABLE orders     ALTER COLUMN created_date     SET DEFAULT CURRENT_DATE;

Export DB2 Table 

export to file myfile.ixf and use file format ixf and log export messages to msg.txt

db2cmd
db2 connect to DB1 user db2admin
db2 export to myfile.ixf of ixf messages msgs.txt select * from staff

Import DB2 Table 

import from file myfile.ixf and use file format ixf and log import messages to msg.txt

db2cmd
db2 connect to DB1 user db2admin
db2 import from myfile.ixf of ixf messages msg.txt INSERT_UPDATE into staff

DB2 Import Options

- CREATE - first create the target table before inserting the data.
- INSERT - Data is inserted into the target table.
- INSERT_UPDATE - Data is inserted into the target table. if matching primary keys, row is updated with the new data.
- REPLACE - The target table is first truncated, then data is inserted.
- REPLACE_CREATE - If target table already exists, it is truncated. If it does not exist, it is first created.

Task Export table and reimport it in another database

db2cmd

db2 connect to xxx user db2admin

db2 EXPORT to APPFORM.ixf OF ixf SELECT * FROM PASSPORTS.APPFORM WHERE 1=2

db2 LOAD FROM APPFORM.ixf OF ixf REPLACE INTO PASSPORTS.APPFORM NONRECOVERABLE INDEXING MODE INCREMENTAL

SQL1092N "USERID does not have the authority to perform the requested command or operation."

To enable the domain user ID to access the database, complete the following steps.

1. Add the domain user ID to the local group DB2ADMNS.
2. With a user having SYSADM privileges or local administrator authority, open a DB2 command window and run the following commands from the prompt. (Alternatively, you can open a DB2 administrator command window.)

   db2set DB2_GRP_LOOKUP=LOCAL,TOKENLOCAL        
   db2 update dbm cfg using sysadm_group DB2ADMNS
   db2stop                                        
   db2start

3. Restart the DB2 Windows services with the login ID set to the domain user ID.

Fix Operation not allowed for reason code "1" on table xxx

db2 set integrity for  PASSPORTS.CITIZEN immediate checked

Fix SQL0668N  Operation not allowed for reason code "7" on table xxx

db2 REORG TABLE PASSPORTS.PURCHASEORDER

The REORGCHK command provides table or index statistics that indicate whether reorganization may result in improved usage or disk space, and/or improved performance.

db2 reorgchk

or

db2 reorgchk update statistics on table system




How to Select Date in format yyyy-MM-dd?

Select CHAR(myAPPISSUEDATE, ISO) from MyTable;

DB21061E Command line environment not initialized.

This happens when the DB2CLP environment variable is not set. 

at the prompt enter these commands:

db2cmd -i -w db2clpsetcp
echo %DB2CLP%

To configure the DB2 client to behave as Unicode application, you have to set the DB2CODEPAGE registry variable to '1256'.

Following are the steps to set the DB2CODEPAGE registry variable to '1208':

1. Open a DB2 Command window or Command Line Processor (CLP) by navigating through the following path:
   Start > IBM DB2 > DB2Copy1 > Command Line Tools > Command Window
2. Set the DB2CODEPAGE registry variable to '1256'.
   db2set DB2CODEPAGE=1256

Get error when upgrade database:

db2 upgrade database alxdb

SQL1006N The code page "0140722399268602I" of the application does not match

the code page "0000000000000000I" of the database.

we can set the correct code page using db2codepage=1256 or

set it using system variable

and command will success

 

Determine code page for a DB2 database

db2 get db cfg for db1

if need increase Tablespace size

   

ALTER TABLESPACE TSASNCA

     AUTORESIZE YES INCREASESIZE 10 PERCENT

SQL5035N  The connection failed because the database product has been updated

and the database needs to be upgraded  SQLSTATE=55001

Steps:

1. Launch 'DB2 Command Window - Administrator'

2. Run a command similar to the following:

db2 upgrade database <databasename>

for more information check
https://www.db2tutorial.com/