Transcript Slide 1

Unix DBA Cross Training
Informix and DB2
Kate Tomchik
The Home Depot, USA
International Informix Users Group
Board Of Directors
Platform: Unix
Friday, December 8, 2006 • 16:20 p.m. – 17:30 p.m.
1
Unix DBA Cross Training
Informix and DB2
Informix to DB2 and back again
If you know one of these, learn how to
get around in the other.
2
Why I became a DB2 DBA…
3
What Informix instances are
running?
/>cd /INFORMIXTMP
/INFORMIXTMP> ls -l VP*
srwxrwxrwx 1 root informix 0 Jul 28 2005 VP.in02.0101s
srwxrwxrwx 1 root informix 0 Jul 28 2005 VP.in02.0301s
srwxrwxrwx 1 root informix 0 Jul 28 2005 VP.in51.0101s
Each name after the VP. is a instance name. This lists two, in02
and in51. in02 has two TCP/IP socket names, so therefore is
has two names listed in the directory.
4
What Informix instances are
running?
See main PID of each instance running:
ps -ef | grep oninit | grep " 1 "
informix 1927 1 0 Jul 28 ? 243:26 oninit
informix 1492 1 0 Jul 28 ? 803:06 oninit

5
What DB2 Instances are
running?
> db2ilist
#-- not always set up
calvin:/opt/hd/db/db2/instance/in11> db2ilist
in32
in33
in11
> # This shows active instances:
> ps -ef | grep db2sysc | grep -v grep
calvin: /home/lkt01> ps -ef | grep db2sysc | grep -v grep
in11 876768 917730 0 Jul 27
- 0:09 db2sysc 0
in33 1056790 1392730 0 Jul 27
- 0:09 db2sysc 0
in32 1933360 2170886 0 Aug 12
- 0:01 db2sysc 0
6
Typical Informix processes
>ps –fu informix
informix 10977 10976 0 Mar 7 ?
informix 10998 10977 0 Mar 7 ?
informix 10993 10977 0 Mar 7 ?
informix 10984 10977 0 Mar 7 ?
informix 11011 10977 0 Mar 7 ?
informix 11009 10977 0 Mar 7 ?
informix 11018 10977 0 Mar 7 ?
informix 10981 10977 0 Mar 7 ?
informix 11005 10977 0 Mar 7 ?
informix 11017 10977 0 Mar 7 ?
informix 10999 10977 0 Mar 7 ?
informix 10976 1 0 Mar 7 ?
informix 11019 10977 0 Mar 7 ?
informix 10990 10977 0 Mar 7 ?
informix 11004 10977 0 Mar 7 ?
2:01 oninit
0:25 oninit
0:26 oninit
0:27 oninit
0:24 oninit
0:27 oninit
0:24 oninit
0:27 oninit
0:25 oninit
0:25 oninit
0:25 oninit
1:56 oninit
0:24 oninit
0:27 oninit
0:26 oninit
7
Typical DB2 processes
calvin: /home/kate> ps -fu infi11 | grep db2
in11 647364 876768 0 Jul 27
- 0:36 db2gds 0
in11 786632 876768 0 Jul 27
- 0:04 db2ipccm 0
in11 811222 647364 0 Aug 13
- 0:04 db2dlock (DB014) 0
in11 876768 917730 0 Jul 27
- 0:09 db2sysc 0
in11 901346 876768 0 Jul 27
- 0:00 db2syslog 0
in11 934120 647364 0 Jul 27
- 0:00 db2srvlst 0
in11 983280 876768 0 Jul 27
- 0:00 db2tcpcm 0
in11 999672 876768 0 Jul 27
- 0:00 db2resync 0
in11 1016056 876768 0 Jul 27
- 0:00 db2spmrsy 0
in11 1040638 647364 0 Jul 27
- 0:00 db2spmlw 0
in11 1261798 647364 0 Aug 13
- 0:06 db2loggw (DB014) 0
in11 1327176 786632 0 Jul 27
- 2:42 db2agent (instance) 0
in11 1491052 647364 0 Jul 27
- 0:02 db2cart 0
in11 1556610 647364 0 Jul 27
- 1:19 db2agent (DB014) 0
in11 1564804 647364 0 Jul 27
- 0:03 db2agent (idle) 0
in11 1753190 647364 0 Aug 13
- 0:00 db2pfchr 0
in11 1884196 647364 0 Aug 13 - 0:00 db2event DB2DETAILDEADLOCK)
0
in11 2015258 647364 0 Aug 13
- 0:00 db2pfchr 0
in11 2023660 647364 0 Aug 13
- 0:00 db2loggr (DB014) 0
in11 2433208 647364 0 Aug 13
- 0:00 db2pclnr 0
8
Kate’s First rule of DBA
support
When user calls with a problem verify the
database is up.
If not up, restart it.
If it is up, try bouncing it anyway. 
9
Start the Informix Instance
>su – informix; oninit -v # --- Informix
hobbes: /users/informix> oninit -v
Checking group membership to determine server run modesucceeded
Reading configuration file '/opt/isv/informix/etc/in51'...succeeded
Creating /INFORMIXTMP/.infxdirs ... succeeded
Creating infos file "/opt/isv/informix/etc/.infos.in51" ...
"/opt/isv/informix/etc/.conf.in51" ... succeeded
… (lots more good stuff)
Checking location of physical log...succeeded
Initializing dataskip structure...succeeded
Checking for temporary tables to drop
Forking onmode_mon thread...succeeded
Verbose output complete: mode = 5
10
Start the DB2 Instance
>su – {instance}; db2start
#--- db2 {run as id of instance}
calvin: /opt/hd/db/db2/> su – in60
calvin: /opt/instance/in60> db2start
08-16-2005 13:28:17
0 0 SQL1063N
DB2START processing was successful.
SQL1063N DB2START processing was
successful.
11
Start DB2 Instance
(processes)
calvin: /opt/hd/db/db2/instance/in60> ps -fu in60
UID
PID PPID C STIME TTY TIME CMD
in60 1335306 7127088 0 13:28:16
- 0:00 db2ipccm 0
in60 1687694 7192802 0 13:18:55 pts/0 0:00 -ksh
in60 1695826 4866232 0 13:28:17
- 0:00 db2srvlst 0
in60 2252828 7127088 0 13:28:17
- 0:00 db2spmrm 0
in60 3645576 4866232 0 13:28:17
- 0:00 db2resyn 0
in60 4530428 1687694 1 13:28:24 pts/0 0:00 ps -fu in60
in60 4603946 7127088 0 13:28:16
- 0:00 db2tcpcm 0
in60 4628634 7127088 0 13:28:16
- 0:00 db2tcpcm 0
in60 4866232 7127088 0 13:28:16
- 0:00 db2gds 0
in60 6955014 4866232 0 13:28:17
- 0:00 db2spmlw 0
in60 7127088 4997122 0 13:28:16
- 0:00 db2sysc 0 12
Stop an Informix Instance
> onmode -k
# --- in informix
hobbes: /users/informix> onmode -k
This will take IBM Informix Dynamic Server OFF-LINE Do you wish to continue (y/n)? y
There are 1 user threads that will be killed.
Do you wish to continue (y/n)? Y
Hobbes: /users/informix> ps –fu informix
informix 11417 10291 1 10:48:57 pts/0
informix 10291 10289 1 10:43:45 pts/0
0:00 ps -fu informix
0:00 -ksh
13
Stop a DB2 Instance
>db2stop [force] # --- run as userid of
instance, force detaches users
calvin: /opt/hd/db/db2/instance/in60
> db2stop force
08-16-2005 13:22:17
0 0 SQL1064N DB2STOP
processing was successful.
SQL1064N DB2STOP processing was successful.
> ps -fu in60
UID
PID
PPID
C STIME
TTY
TIME CMD
in60 1687694 7192802 0 13:18:55 pts/0 0:00 -ksh
in60 4538434 1687694 1 13:27:02 pts/0 0:00 ps –fu in60
14
Db2_kill
> db2_kill # db2 utility
calvin: /opt/hd/db/db2/instance/in60> db2_kill
calvin: ipclean: Removing DB2 engine and client's IPC resources
for in60.
calvin: db2_kill [] completed ok
NOTE: It is a good practice to run db2_kill after every running of
db2stop to clean up any shared memory segments prior to a
db2 restart.
Be aware that db2_kill of any flavor should be a last
resort for your method of stopping the instance (using
it instead of db2stop), because if any process holds
data - you may lose or corrupt the data.
15
Hard Kill an Instance (dangerous)
Informix:
> ps -ef | grep oninit # --- find #1 owned
process
> kill -9 {PID}
DB2:
> ps -ef | grep db2sysc {0} # --- 0 process
for multiprocessor engine
 kill -9 {PID}
# May need to kill each node’s parent
process when not a single node env
16
Kate’s 2nd Rule of DBA Support
If one user is stopping 100 users from
getting to their tables, that user must
die.
(Of course, I only mean the offending
process)
17
Kill a user attachment in
Informix
> onstat –u {Identify correct sessionid}
> onmode –z {SSID}
hobbes:/users/informix> onstat –u | grep sally
address flags sessid user
tty
wait
tout
locks
e4ccdc78 Y--P--- 819941 sally ttyp3 e533a800 0 999,998
nreads nwrites
999,998
1
hobbes:/users/informix> onmode –z 819941
18
Kill a user attachment in DB2
db2 connect to {database}
db2 list applications show detail
# identify bad process and get the Appl. Handle &
Coordinator PID/Thread.
db2 “force application (AH)”
NOTE: you really need the quotes and ()
19
Kill a user attachment in DB2
Example
> db2 list applications show detail
Auth Id
Application Appl.
Application Id
Handle
IN60 db2bp
471
*LOCAL.in60.060606150430
> db2 "force application (471)"
DB20000I The FORCE APPLICATION command
completed successfully.
DB21024I This command is asynchronous and may
not be effective immediately.
20
Kate’s 3rd Rule : Check the Error
logs – DB2
>{db2 path}sqllib/db2dump/db2diag.log
calvin:/opt/hd/db/db2/instance/in11/sqllib/db2dump>ls -l
-rw-rw-rw- 1 in11 sysadm
4821 Jul 27 07:46 in11.nfy
-rw-rw-rw- 1 in11 sysadm
46803 Jul 27 12:04 db2diag.log
-rw-r----- 1 in11 sysadm 5242044 Jul 27 07:36
db2eventlog.000
db2diag.log – main log of database activity
in11.nfy - contains notifications based on NOTIFY level
db2eventlog.000 - similar to /tmp/af files of IFX since they
are created during database errors, but these are not
21
readable. Send to IBM when opening a PMR.
inis32.nfy (v8 file)
Typical Entries:
ADM7514W Database manager has stopped.
2005-11-02-18.57.12.883227 Instance:in32 Node:000
PID:573572(db2star2) TID:1 Appid:none
base sys utilities startdbm Probe:911
ADM7513W Database manager has started.
2005-11-02-13.12.15.570194 Instance:in32 Node:000
PID:2457760(db2agent (CQMASTR2) 0) TID:1
Appid:*LOCAL.in32.0800F2181219
database monitor sqm.evmgr::log_ev_err Probe:2 Database:db036
ADM2004E The Event Monitor "DB2DETAILDEADLOCK" was not
activated because there was not enough storage space in the
database monitor heap. To remedy this problem, increase the
MON_HEAP_SZ DBM configuration parameter and restart the
instance.
22
Db2diag.log – Typical Entries
2005-08-02-15.01.16.014367 Instance:in32 Node:000
PID:2318574(db2agent (DB033) 0) TID:1 Appid:*LOCAL.in32.0003D2190114
database utilities sqlubcka Probe:0 Database:DB033
Starting a full database backup.
2005-08-02-15.01.53.519377 Instance:in32 Node:000
PID:2318574(db2agent (DB033) 0) TID:1 Appid:*LOCAL.in32.0003D2190114
database utilities sqlubcka Probe:128 Database:DB033
Estimated size of backup in bytes:
0x0FFFFFFFFFFF5008 : 0x0000000008489000
.....H..
2005-08-02-15.01.53.524680 Instance:in32 Node:000
PID:2318574(db2agent (DB033) 0) TID:1 Appid:*LOCAL.in32.0003D2190114
database utilities sqlubcka Probe:128 Database:DB033
Actual size of backup in bytes:
0x0FFFFFFFFFFF5010 : 0x0000000008405000
.....@P.
2005-08-02-15.01.53.529787 Instance:in32 Node:000
PID:2318574(db2agent (DB033) 0) TID:1 Appid:*LOCAL.in32.0003D2190114
database utilities sqlubcka Probe:130 Database:DB033
Backup Complete.
23
Logfiles - Informix
> onstat –c | grep MSGPATH
# could be named anything.
> view {MSGPATH}
hobbes: /usr/informix/bin> onstat -c | grep
MSGPATH
MSGPATH
/usr/informix/in07.log
24
Example Log File - Informix
Sun Jun 11 07:20:02 2006
07:20:02 Checkpoint Completed: duration was 0 seconds.
07:20:02 Checkpoint loguniq 205864, logpos 0x4018, timestamp: 0x5bc1aa47
07:20:02
07:20:03
i004
07:30:10
07:30:10
Maximum server connections 2
Level 0 Archive started on r001, l001, d002, d003, d004, i001, i003,
Checkpoint Completed: duration was 0 seconds.
Checkpoint loguniq 205864, logpos 0xc018, timestamp: 0x5bc1ae99
07:30:10 Maximum server connections 2
07:40:16 Fuzzy Checkpoint Completed: duration was 0 seconds, 1 buffers not
flushed,
timestamp: 0x5bc1b084.
07:40:16 Checkpoint loguniq 205864, logpos 0xe0d0, timestamp: 0x5bc1b084
07:40:16 Maximum server connections 2
07:47:44 Archive on r001, l001, d002, d003, d004, i001, i003, i004
Completed.
25
Kate’s last support law: Call IBM when
first two laws don’t find the issue
IBM support calls create PMRs {Problem
ManagementRecords} These are now used for
Informix as well.
You will want to have the following:
1)
Hardware type and operating system version
>uname –a # ex AIX 5.1
2)
Complete version name and fixpak
3)
Configuration file output, environment variables
set, schema output
4)
Ability to send the log files via ftp to IBM.
26
DB2 Version and Fixpak
> su - in11 # enter password
> db2level
calvin:/opt/hd/db/db2/instance/in11> db2level
DB21085I Instance “in11" uses "32" bits and DB2 code
release SQL08013" with level identifier "02040106".
Informational tokens are "DB2 v8.1.1.24", "s030728",
"U488481", and FixPak "3".
Product is installed at "/usr/opt/db2_08_01".
27
Informix Version
> onstat –
hobbes:/home/informix> onstat Informix Dynamic Server Version 9.40.FC1
- On-Line -- Up 438 days 16:21:59 -10224988 Kbytes
-
28
View the Configuration Files
Informix:
>view $INFORMIXDIR/etc/onconfig
# changes can be made here
>view $INFORMIXDIR/etc/sqlhosts
> onstat –c #start up configuration
DB2: Instance variables
> db2 get dbm cfg | more
#- you scroll through the file. Updates cannot be
made directly to the file, you use a utility
> db2 update dbm cfg using {parameter} {value}
DB2: Database variables
> db2 get db cfg for {database} | more
29
DB2 Environment Variables
(some)
calvin:> db2set -all
[i] DB2COMM=tcpip
[i] DB2AUTOSTART=YES
[g]
DB2_EEE_LICENSE_POLICY=1125904201875
456
[g] DB2ADMINSERVER=db2as
calvin:> env | grep DB2
DB2INSTANCE=ad11
30
Informix Environment Variables
(some)
> onstat –g env
INFORMIXDIR /usr/informix
ONCONFIG in51
INFORMIXSERVER in51
PATH $INFORMIXDIR/bin:${PATH}
TERMCAP $INFORMIXDIR/etc/termcap
DBTEMP /tmp
31
Schema of the Database
DB2 Database Schema (remember
alex):
>db2look –d {database} –a –l –e –x –o {output
file}
Informix:
>dbschema –d {database} –ss
32
Get Databases for an Instance
Must run as the instance owner:
> db2 "list db directory"
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias
Database name
Local database directory
Database release level
Comment
Directory entry type
Catalog node number
= DB060
= DB060
= /opt/hd/db/db2/dbdir/db060
= 9.00
=
= Indirect
=0
33
Database Connection State
> db2 "get connection state"
Database Connection State
Connection state
= Connectable and Unconnected
Connection mode
=
Local database alias =
Database name
=
34
Connect to a Database
INFORMIX
> dbaccess {database} {command.sql}
Gives error if the instance is not running
DB2
> db2 connect to db060
Database Connection Information
Database server
= DB2/6000 7.2.6
SQL authorization ID = IN60
Local database alias = DB060
35
Statistics for a table – DB2
DB2 statistics is runstats (see IBM
documentation Command Reference for
full details)
> runstats on table {schema}.{table}
with distribution and detailed indexes all
shrlevel change”
36
Statistics for table - Informix
Update statistics commands (see
documentation for full command ) using
SQL file to run against the database
>dbaccess {database} file.sql
File.sql:
Update statistics {low, medium, high} for
table {owner}.{table} [distributions
only]
37
Current path of Instance
Not similar to >echo $INFORMIXDIR
Since DB2 instance path does not show
where the source code lies.
> db2path
calvin:/opt/hd/db/db2/instance/in11>
db2path
/opt/hd/db/db2/instance/in11/sqllib
38
Some parameters in the DBM
Database Manager Configuration
Node type = Enterprise Server Edition with local and remote
clients
Diagnostic error capture level
(DIAGLEVEL) = 3
Notify Level
(NOTIFYLEVEL) = 2
Diagnostic data directory path
(DIAGPATH) =
/opt/hd/db/db2/instance/in32/sqllib/db2dump
Default database path
(DFTDBPATH) =
/opt/hd/db/db2/instance/in32
Max number of existing agents
(MAXAGENTS) = 400
TCP/IP Service name
(SVCENAME) = in32
No. of int. communication buffers(4KB) (FCM_NUM_BUFFERS) = 4096
39
Backup a Database – DB2
Db2 “backup db {database} {online}
{incremental} use {backup mgr}
Multipartition:
Db2_all “<<+0<db2 backup db {database}
{online} {incremental} use {B/up mgr}
Example
Db2 “ backup db db060 online use adsm”
40
Backup a Database - Informix
To a tape drive:
>ontape –s
(at prompt, enter level of archive)
Using onbar (i.e. to TSM):
>onbar –b –L 0
41
Get list of past backups
> db2 list history backup all for {database}
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ -------------B D 20050927020010001 N A S0000509.LOG S0000510.LOG
---------------------------------------------------------------------------Contains 7 tablespace(s):
00001 SYSCATSPACE
00002 INVDATA4K
00003 INVINDEX4K
00004 INVDATA8K
00005 INVINDEX8K
00006 INVDATA16K
00007 INVINDEX16K
---------------------------------------------------------------------------Comment: DB2 BACKUP DB010 ONLINE
Start Time: 20050927020010
End Time: 20050927020043
---------------------------------------------------------------------------42
00013 Location: adsm/libtsm.a
Get list of last backup Informix
>onstat –g arc
hobbes:/users/informix> onstat -g arc
Informix Dynamic Server Version 7.31.UD3
-- On-Line -- Up 3
days 09:05:32 -- 26024 Kbytes
num DBSpace Q Size Q Len Buffer partnum size scanner
Dbspaces - Archive Status
name number level date
log
r001
1
0
06/12/2006.01:00 2289
d001 2
0
06/12/2006.01:00 2289
l001
3
0
06/12/2006.01:00 2289
log-position
0x21018
0x21018
0x21018
# to see other backups, you need to look at a file such as ixbar
43
Check quality of the backup –
DB2
Checksum:
Db2adutl verify full taken at {datetime}
db {database}
Example:
Db2adutl verify full taken at
20040512171343 db db010
44
Restore Database – DB2
Db2 “restore database {database} use
{ex: tsm} taken at {date/time}
NOTE: This is the start time from the
history output.
45
Example Restore – DB2
Get last good backup date/time:
>db2 list history backup all for db010
Check attached users:
>db2 list applications
Force users off:
>db2 force applications all
Run restore with nohup:
>nohup db2 “restore database db010 use tsm taken
20040512171343”
Verify completed:
>db2 “connect to db010”
46
Restore Informix database
>onbar –r [ dbspaces list]
-or>ontape -r
47
Run SQL Against a Table –
DB2


db2 connect to {database}
db2 “select * from {table-name}”
Example:
>db2 “select substr(tabname,1,18) as
table_name from syscat.tables where
type=‘T’ order by 1”
# gets system tables
48
External SQL File – DB2
Create a file that contains each SQL command separated
by a “;”
calvin: /usr/app/bin> cat count.sql
> select count(*) from syscat.tables where type='T';
> select count(*) from syscat.indexes;
Run an sql file:
>db2 –tvf {sql file} –z {output file}
Example:
>db2 –tvf count.sql –z count.out
49
External SQL file - Informix
>cat file.sql
select count(*) from errorlog
>dbaccess {database} {file.sql}
hobbes:/users/informix> dbaccess db005 file.sql
Database selected.
(count(*))
4063
1 row(s) retrieved.
Database closed.
50
Tables space info – DB2
db2 “list tablespaces”
Tablespaces for Current Database
Tablespace ID
Name
Type
Contents
State
Detailed explanation:
=0
= SYSCATSPACE
= System managed space
= Any data
= 0x0000
Normal
Tablespace ID
Name
Type
Contents
State
Detailed explanation:
=1
= TEMPSPACE1
= System managed space
= Temporary data
= 0x0000
Normal
Tablespace ID
Name
Type
Contents
State
Detailed explanation:
=2
= USERSPACE1
= System managed space
= Any data
= 0x0000
Normal
51
Tablespace info – DB2
User created tablespace:
Tablespace ID
Name
Type
Contents
State
Detailed explanation:
Normal
=3
= PR001TAAQB
= Database managed space
= Any data
= 0x0000
System managed space: automatic expansions up to the max size
of the file system.
Database managed space: dba allocates additional space
52
Tablespace Detailed info –
DB2
> db2 “list tablespaces show detail” | more
Tablespace ID
Name
Type
Contents
State
Detailed explanation:
Normal
Total pages
Useable pages
Used pages
Free pages
High water mark (pages)
Page size (bytes)
Extent size (pages)
Prefetch size (pages)
Number of containers
=3
= PR001TAAQB
= Database managed space
= Any data
= 0x0000
= 100000
= 99968
= 99968
=0
= 99968
= 4096
= 32
= 32
=1
##Includes used pages information and page size for additional allocations.
53
Tablespace info - Informix
>onstat –d
hobbes: /home/kate> onstat -d
Informix Dynamic Server Version 7.31.UD3
Kbytes
-- On-Line -- Up 48 days 15:48:33 -- 26024
Dbspaces
address number flags fchunk nchunks flags owner name
d2215158 1
1
1
1
N
informix root
d2215738 2
1
2
1
N
informix data
d22157f8 3
1
3
1
N
informix logs
d22158b8 4
2001
4
1
NT
informix temp
4 active, 2047 maximum
Chunks
address chk/dbs offset size
free
bpages flags pathname
d2215218 1 1 0
5000
2713
PO- /usr/informix/data/in05root
d2215420 2 2 5000
30000 29195
PO- /usr/informix/data/in05data
d2215528 3 3 35000 25053 0
PO- /usr/informix/data/in05logs
d2215630 4 4 92400 10000 9947
PO- /usr/informix/data/in05temp
4 active, 2047 maximum
54
Add disk space – DB2
Expand the containers for a tablespace.
>alter tablespace {tbspacename} extend (all
containers {expand amount});
#Every container is expanded the same amount
and data is balanced across all containers.
Expand amount is in pages. Can take many
minutes.
55
Add Disk Space - Informix
>onspaces –a {spacename} –p {pathname} –o
{offset} –s {size in kb NOT pages}
>onspaces –a data –p
/usr/informix/data/in05data –o 120106 –s
10000
#takes space 60053 to 65053 on the onstat –d
command
# NOTE: this is using raw disk
56
Get Stored Procedure SQL –
DB2
Db2 “export to {filename} of del select text
from syscat.procedures where
procname=‘{ProcName}’”
Example
>db2 “export to filename.sql of del select text
from syscat.procedures where
procname=‘ADJUSTAMT”
# creates procedure code in filename.sql
57
Stored Procedure SQL Informix

Dbschema –d db001 –f all
DBSCHEMA Schema Utility
INFORMIX-SQL
Version 7.31.UD3
Copyright (C) Informix Software, Inc., 1984-1998
Software Serial Number AAC#J799807
create procedure
"informix".sqlnkforkeypfk45(i_pkCatalog
varchar(18), i_pkSchema
varchar(18),
i_pkTableName varchar(18),
58
Instance Creation – DB2



You must have root access to create
You must have user ids created for each
instance, where as in Informix you could
always just use Informix
You may also create a fenced user id for the
instance. This ID does not have system
administration (sysadm) privileges
> db2icrt -u {instance fenced id} {instance}
59
Instance Creation – Informix
# must be informix to create
> export ONCONFIG=in01
> export INFORMIXSERVER=in01
> export INFORMIXDIR=/opt/isv/informix
> export PATH=$INFORMIXDIR/bin:$PATH
> vi $INFORMIXDIR/etc/in01 (set up
configuration file)
> oninit –i
60
Some Important System Tables –
DB2
Prefix with owner/schema “syscat”
Syscat.Tables
Syscat.Indexes
Syscat.Dbauth
Syscat.Tabauth
Syscat.Dependancies
Syscat.Tablespaces
Syscat.tbspaceauth
61
Important system tables Informix
“informix”.systables
“informix”.sysindexes
“informix”.syscolumns
Many others, just to a select * from
“informix”.systables to see them all.
62
RTFM – Where to find the
manuals
INFORMIX:
www.iiug.org – has everything!! Content added daily.
Check out the links on the right side to IBM.
# direct link to online docs
http://www306.ibm.com/software/data/informix/pubs/library/
DB2: # link to v8 Unix docs
http://www-306.ibm.com
/software/data/db2/udb/support/manualsv8.html
63
Performance Tuning
Clearly, there are numerous sessions at
this conference with suggestions for
performance tuning. But, if there was
only one thing I could teach you,
before the end of this discussion, to
improve performance on a system
you’ve never seen before, what would
it be?
64
Answer…
Increase memory to database (i.e. add
buffers).
65
Informix Performance Tuning for
Beginners
ADD BUFFERS:
vi onconfig
# increase BUFFERS by 10000 at a time
# bounce informix instance for them to take affect
# Shared Memory Parameters
LOCKS
300000
BUFFERS
220000
shared buffers
# Maximum number of locks
# Maximum number of
Bounce engine (or you can also add it dynamically)
66
DB2 Performance Tuning for
Beginners
ADD BUFFERS:
Db2 “select bpname, npages, pagesize from syscat.bufferpools”
Db2 “alter bufferpool ibmdefaultbp size 10000”
Will happen dynamically if possible, otherwise will give you error that
you need to bounce instance.
> db2 select bpname, npages, pagesize from syscat.bufferpools
BPNAME
NPAGES
PAGESIZE
-----------------------------------------------------------------------------IBMDEFAULTBP
10000
4096
67
Monitoring Environment
Informix – Check out all the onstat functions
 Onstat -- # double dash displays all options.
DB2 – for 8.2 use db2pd as the instance owner
> su – in11
> db2pd -help
68
Name one DB2 feature I
would like to see in Informix…
AUTOCONFIGURE!!
To use (example):
> db2 “connect to {database}”
> db2 “autoconfigure using mem_percent 70 TPM
1000 num_remote_apps 1000 APPLY NONE”
Has many parameters and it gives you a report of
how the database should be tuned.
69
Name one Informix feature I
would like to see in DB2…
This is personal preference, but I LOVE
the fact that when you set up HDR in
Informix (HADR in DB2) you can read
from the backup server and do queries!
The failover box becomes a useful way of
reducing the reporting load from the
production server.
70
To Advanced beyond Beginner
Status
Recommendation: Take a class if you need
to work in a new language (sorry, this hour
didn’t count!)
Warning: The beginning DB2 class shows
everything through a GUI interface.
Oh, and by the way - the mainframe Z/OS
DB2 does not even have the same system
71
table names as the Unix version.
Why conferences are
important..
72
Session H08
Unix DBA Cross Training
Informix & DB2
Kate Tomchik
The Home Depot, Lead DBA
International Informix Users Group
Board of Directors
[email protected]
73