epcim
10/20/2015 - 10:12 AM
db2.howto.md
DB2 related DB operation / tutorials
Maintenance
get DDL
db2look -xe -l -e -dp > /tmp/db.ddl
run SQL script
db2 -tvsf script.sql
QUIESCE database (before backup etc.)
db2 QUIESCE db XYZ
db2 backup db XYZ to /tmp/xyz
db2 UNQUIESCE db XYZ
export all tables
...
for i in cat alltables; do db2 "export to table_$i.del of DEL select * from $i"; done
db2 "select trim(TABSCHEMA)||'.'||TABNAME from syscat.tables where type = 'T' and TABSCHEMA not like 'SYSIBM%'" > alltables
...
import from delimited ascii
IMPORT FROM "c:\XXXY\data_in_delimited_asci_file.del" of del xml from
"/myz/xml/data/dir/"a INSERT INTO CLIENTS (ID,NAME, STATUS, CONTACT);
data_in_delimited_asci_file.del:
3333,NatureTrail,Walking boot,394334,3434.23,<XDS FIL="Coment32323.xml"/>,
backup
db2 backup db TEPS to /opt2/cl_backups
archive logs
db2 archive log for db tdwdb
migrate data
db2migrate
db2move
db2move TDWDB export -tn "ITMUSER"."K65_FILE_SYSTEMS_H" -d /TDWDB/export/
db2move TDWDB export -tn "K65_FILE_SYSTEMS_H" -d /TDWDB/export/
db2move TDWDB export -tn "K65_FILE_SYSTEMS_H" -l /TDWDB/export/
db2move TDWDB LOAD -lo INSERT -l /TDWDB/export/
db2pd -
db2 '? SQL0968C'
db2 terminate
Configuration
get some cfg info
su - db2inst1
db2 list db directory
db2 get db cfg for TDWDB
db2 get db cfg for TDWDB |grep -i archmet
db2 get db cfg for TEPS |grep -i archmet
db2adutl query logs db tdwdb
db2 update database configuration for ldapdb2 using LOGFILSIZ 100000
db2 update database configuration for tdw using LOGFILSIZ 100000
Operation
create database
create database mydb using codeset UTF-8 territory US
create table
create table items (
id int primary key not null,
branchname varchar(30),
itemname varchar(30),
sku int,
srp decimal(7,2),
comments xml
);
describe
db2 describe table xyz
#db2adult
db2adutl q logs db tdwdb
db2adutl query full db tdw
db2adutl query full db tdwdb
db2adutl query full db tdwdb|more
db2adutl query full db teps
db2adutl query full db teps|more
db2adutl query logs db tdwdb
db2adutl query logs db tdwdb|more
db2adutl query logs db teps|more
Some other examples
db2 archive log for db tdwdb
db2cc
db2 connect to TDW
db2 connect to tdwdb
db2 connect to TDWDB
db2 connect to teps
db2 get db cfg for tdwdb
db2 get db cfg for TDWDB
db2 get db cfg for tdwdb|grep FIL
db2 get db cfg for TDWDB |grep -i archmet
db2 get db cfg for TDWDB|more
db2 get db cfg for teps
db2 get db cfg for TEPS
db2 get db cfg for TEPS |grep -i archmet
db2 list applications
db2 list db directory
db2 list history backp all for teps
db2 list history backup all db teps
db2 list history backup all for db teps
db2 list history backup all for tdw
db2 list history backup all for tdwdb
db2 list history backup all for teps
db2 list history backup all for tws
db2 list tablespace for tdwdb
db2 list tablespaces
db2 log achive for db tdwdb
db2 log achive for tdwdb
db2 log archive for db tdwdb
Tutorials
Changing hostname of the DB2 server
Technote (FAQ)
Question
Changing hostname of the DB2 server. What updates in DB2 need to be made? Cause
Changing the DB2 server's hostname. Answer
Perform the following on the DB2 server:
Login as the dasadm user on UNIX/Linux or Local Administrator on Windows. Stop the DB2 Administration Server (DAS):
db2admin stop
Login as the instance owner on UNIX/Linux or Local Administrator on Windows. Stop the DB2 instance:
db2stop
Change the server's hostname. On a Windows system, a reboot is required before this change will take effect. Do not reboot the Windows Server at this time - you must make the DB2 Configuration changes first.
Login as user ROOT if DB2 is on UNIX/Linux and Local Administrator on Windows. Update the DB2SYSTEM registry variable while in the instance home directory:
UNIX/Linux db2iset -g DB2SYSTEM=<new hostname> Windows db2set -g DB2SYSTEM=<new hostname>Locate db2nodes.cfg in one of the following directories:
UNIX/Linux: /sqllib/db2nodes.cfg
All Windows flavors running DB2 v8 and v9.1: Program Files\IBM\SQLLIB\DB2\db2nodes.cfg
Windows XP and 2003 running DB2 v9.5: Documents and Settings\All Users\Application Data\IBM\DB2<DB2COPY>\DB2\db2nodes.cfg
NOTE: Application Data is a hidden folder
Windows Vista and later operating systems: ProgramData\IBM\DB2<DB2COPY>\DB2\db2nodes.cfg
Note that only DB2 ESE has a db2nodes.cfg. If you are running other editions (WorkGroup Server, or Personal Edition) then you can skip this step and the next.
- In the db2nodes.cfg file change to .
Run the following command to list the current hostname/system name that is cataloged:
db2 list admin node directory show detail
If there are no Admin Node Directory entries then you can skip the next 2 steps. It simply means that you haven't started the Control Center. The Admin Node Directory will get updated the next time the Control Center is started, based upon the current hostname.
Uncatalog the current hostname using this command:
db2 uncatalog node
Catalog the admin node with the new hostname using this command:
db2 catalog admin tcpip node remote system
can be anything you want it to be (8 characters or less). It does not relate directly to the hostname itself.
Update the admin configuration file using these commands:
db2 update admin cfg using DB2SYSTEM db2 update admin cfg using SMTP_SERVER
At this time, you should restart your server if running Windows. Note that if your instances are set to auto-start, you may get error messages. These will occur if you have DB2_EXTSECURITY enabled. To correct this, you will need to update the DB2_ADMINGROUP and DB2_USERSGROUP registry entries using the db2extsec command. See the 'note' at the end of this technote for more details.
Login as the dasadm user on UNIX/Linux or Local Administrator on Windows. Start the DB2 Administration Server (DAS):
db2admin start
Login as the instance owner on UNIX/Linux or Local Administrator on Windows. Start the DB2 instance:
db2start
In Control Center find the old hostname under the "All Systems" folder and right click, choose Remove.
In Control Center add the new hostname. Right click on the "All Systems" folder and choose "Add". Click on "Discover" to locate the new hostname.
NOTE: Starting in v9.1 FP2, DB2 supports the use of domain groups for extended security. Therefore, when you change the computer name and the computer groups DB2ADMNS and DB2USERS are local computer groups, you must update the DB2_ADMINGROUP and DB2_USERSGROUP global registries. Please see the Related URL for instructions on how to do this.
If Windows Extended Security has been enabled then you will need to tell DB2 to lookup the WIndows local groups DB2ADMNS and DB2USERS via the new hostname. For example if the hostname is changed to QASERVER then execute:
db2extsec -a QASERVER\DB2ADMNS -u QASERVER\DB2USERS