Informix Dynamic Server 11.50 Fundamentals Exam 555 certification preparation, Part 7: Tools and utilities

With a good understanding of the tools and utilities that come with IBM® Informix® Dynamic Server (IDS), you'll find the database easier to monitor and administer. In this tutorial, learn about those tools. This is the seventh of a series of nine tutorials that will help prepare you for IDS exam 555.

Joo Guan Yap (yapjg@sg.ibm.com), Advanced Support Engineer, IBM

Joo Guan graduated from Nanyang University of Singapore with a Bachelor of Applied Science (Computer engineering) Degree in 1996 and was involved in various IT development projects. He joined IBM Informix support in 2001 and is now involved in Advanced Support work.



17 September 2009

Before you start

About this series

This complimentary series of nine tutorials has been developed to help you prepare for the IBM Informix Dynamic Server 11.50 Fundamentals certification exam (555). This certification exam will test your knowledge of entry-level administration of IDS 11.50, including basic SQL (Structured Query Language), how to install IDS 11.50, how to create databases and database objects, security, transaction isolation, backup and recovery procedures, and data replication technologies and purposes. These tutorials provide a solid base for each section of the exam. However, you should not rely on these tutorials as your only preparation for the exam.

About this tutorial

This tutorial describes the tools and utilities available in IBM Informix Dynamic Server (IDS) 11.50.

Objectives

At the end of the tutorial, you should be able to:

  • Describe onstat, oninit, and onmode utilities
  • Demonstrate knowledge of data movement utilities
  • Describe the sysadmin database and its functionality
  • Demonstrate knowledge of the features or functions available in OpenAdmin Tool and dbaccess

Prerequisites

This tutorial is written for users with basic knowledge of Structured Query Language (SQL).

System requirements

To understand and test the concepts discussed in this tutorial, you need a running instance of IDS 11.50.


The onstat, oninit, and onmode utilities

Major components of the server

IBM Informix Dynamic Server (IDS) consists of three major components, namely the oninit processes (virtual processors), the shared memory segments, and the disk space storage. Upon starting the server, an oninit process reads the necessary information from the configuration file and disk before creating the shared memory segments. The required virtual processors are then started, as specified in the configuration file.

Figure 1 shows the virtual processes interacting with the shared memory segments and disk susbsystem. Besides the buffer pool, which contains the data or index information, a large part of shared memory contains control data, which the virtual processes (or threads) use to coordinate and execute their activities. As for the disk subsystem, it contains the persistent information, which the virtual processes will read and write from. Persistent information is data that is not lost even after IDS has shut down .

Figure 1. Major component of IDS
Diagram illustrating the major components of IDS (the oninit processes (virtual processors), the shared memory segments, and the disk space storage) and their relationship

Modes of IDS

IDS has four principle operating modes:

Offline mode

In this mode, the server is not running and no shared memory segments or virtual processors are created. Using the following onstat command, the below output will be shown if the server is offline:

$ onstat -
shared memory not initialized for INFORMIXSERVER 'ol_115fc3'

Quiescent mode

In quiescent mode, the shared memory is initialized and the virtual processors are started. Administration that does not require SQL connection is allowed in this mode. No user connection is allowed in this mode.

Issuing the following onstat command will show the below output when the server is in this mode:

$ onstat - 
IBM Informix Dynamic Server Version 11.50.FC3 -- Quiescent -- Up 00:21:42 -- 149504 Kbytes

Administrative mode

Any type of administration activity that requires SQL should use this mode. This mode is identical to online mode, except that only admin users can connect to the server. Virtual processors and shared memory is initialized.

In this mode, only user informix and DBSA group can connect to the server by default. To enable other users to also connect, set the onconfig parameter ADMIN_USER_MODE_WITH_DBSA to 1 with the permitted user listed in the configuration parameter ADMIN_MODE_USERS. For example, the configuration can look like this:

ADMIN_MODE_USERS jgyap, kohcf, jagena, nopavis

At this mode, sending onstat will receive the following output:

$ onstat -
IBM Informix Dynamic Server Version 11.50.FC3 -- Single-User -- Up 00:23:07--149504 Kbytes

Online mode

This is the normal operating mode in which authorized activity of the various users is carried out. Virtual processors and shared memory is initialized.

Again, onstat will show you the online mode:

$ onstat -
IBM Informix Dynamic Server Version 11.50.FC3  -- On-Line -- Up 00:41:57 -- 149504 Kbytes

Transitory mode

It is possible for the server to be in a transitory mode, such as recovery mode and shutdown mode. Recovery mode is when the server is changing from offline to quiescent or online. Shutdown mode is when the server is going from online to quiescent.

In Microsoft® Windows®, the server runs as a service. Therefore, the server can be started (online mode) or stopped (offline mode) using the Services Tool accessible from your control panel or NET command. For example, you can issue the following command to start the server with the service name ol_115fc3:

NET start ol_115fc3

To stop the service:

NET stop ol_115fc3

Disk space initialization

The creation of shared memory segment is sometimes called shared memory initialization. There is another type of initialization that is called disk space initialization, where the initial chunk of the root dbspace is initialized in an internal format.

Disk space initialization is required to be done when a new instance is created. Use the following commands to do disk space initialization:

UNIX®:

oinit -i

Windows:

starts dbservername -i

Or use the Instance Manager program to create the new instance.

To skip the requirement to respond yes, use the y option:

UNIX:

oninit -iy

Windows:

starts dbservername -iy

To get a verbose trace during the disk initialization, use the v option:

UNIX:

oninit -ivy

Windows:

starts dbservername -ivy

To let the engine stay in quiescent mode after disk initialization, add the s option:

UNIX:

oninit -ivys

Windows:

starts dbservername -ivys

The server will automatically do the shared memory initialization as well and bring the engine online during disk initialization.

Note: If disk initialization is done on an existing instance, all the data in the old instance will be lost.

IDS mode management

When the engine is in offline mode, use the oninit utility to bring up the server to the desired mode. When it is no in offline mode, use the onmode utility.

Table 1 shows the oninit or onmode utility and its option to use in order to set the server to a desired mode.

Table 1. Mode management using oninit and onmode
Target modeOriginal mode: OfflineOriginal mode: Not offline
Offlinen/aonmode -k
Quiescentoninit -sonmode -s or onmode -u
Administrativeoninit -j -Uonmode -j -U
Onlineoninitonmode -m

Therefore, if the desired target mode is quiescent mode, and current mode of the server is offline, then issue the following command:

oninit -s

However, if the engine is not in offline mode (It's either in quiescent or administrative mode), then issue one of the following two options:

For graceful shutdown:

onmode -s

Fore Immediate shutdown:

onmode -u

In graceful shutdown, existing user sessions are allowed to continue execution, but new connections are not allowed. However, in immediate shutdown, current sessions are terminated immediately and transactions are rolled back.

Use the -U option to allow users other than informix and those belonging to DBSA group to connect to the server when it is in administrative mode. For example, the following command allows users jgyap, nopavis, and chinth to connect to the server when it is in administrative mode:

onmode -j -U jgyap nopavis chinth

To remove the users, issue this command:

onmode -U " "

Other oninit options

During start up, the server cleans up temporary tables that are left over from the last shutdown. To instruct the server not to carry out this clean up, you can use the following command:

oninit -p

The server provides replication facilities such as HDR and Enterprise Replication (described in Part 9 of this series). To start up the server without initializing the HDR and Enterprise Replication, use the following command:

oninit -D

To start the HDR server in standard mode, use this command:

oninit -S

Use the following command to get the detail about the oninit itself:

Listing 1. oninit detail
$ oninit -version
Program Name:   oninit
Build Version:  11.50.FC3
Build Number:   N091
Build Host:     apris
Build OS:       SunOS-sparc 5.9
Build Date:     Tue Oct 28 00:17:11 CDT 2008
GLS Version:    glslib-4.50.FC3

Other onmode options

Besides using the onmode utility to change the server mode when it is not offline, the onmode utility has a number of others functions. Let's explore a few.

The server provides the capability to dynamically change some of the configuration. For example, the limit of the maximum number of sessions can be changed with this command:

$ onmode -wm LIMITNUMSESSIONS=200
Value of LIMITNUMSESSIONS has been changed to 200.

The -wm option changes the memory content but not the configuration file. This change will therefore be lost upon reboot of the server.

To change it more permanently, use the following option:

$ onmode -wf LIMITNUMSESSIONS=200
Value of LIMITNUMSESSIONS has been changed to 200.

Another interesting function that onmode utility can do is to dynamically turn on the SQL explain. For example, to turn on SQL explain for session 201, issue the following command:

onmode -Y 201 1

While session 201 is run, onstat -m shows the location where the sqexplain file is located:

Listing 2. onstat -m output
$ onstat -m

IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 17 days 08:06:25 
-- 522240 Kbytes

Message Log File: /usr2/support/products/sr_11.50.FC3/tmp/online.log

10:55:58  Checkpoint Completed:  duration was 0 seconds.
10:55:58  Fri Aug  7 - loguniq 21, logpos 0xe1f044, timestamp: 0x1c12d1 Interval: 3742

10:55:58  Maximum server connections 1
10:55:58  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 2
, Llog used 1

11:11:01  Checkpoint Completed:  duration was 0 seconds.
11:11:01  Fri Aug  7 - loguniq 21, logpos 0xe20044, timestamp: 0x1c12de Interval: 3743

11:11:01  Maximum server connections 1
11:11:01  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 2
, Llog used 1

11:26:04  Checkpoint Completed:  duration was 0 seconds.
11:26:04  Fri Aug  7 - loguniq 21, logpos 0xe27018, timestamp: 0x1c1355 Interval: 3744

11:26:04  Maximum server connections 1
11:26:04  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 
23, Llog used 7

11:30:46  Explain file for session 201:/usr2/as_support/jgyap/sqexplain.out.201

In the sqexplain.out.201 file, you will able to check the query plain executed by the 
session 201.

To turn off the SQL explain, issue the following command:

onmode -Y 201 0

IDS also allows the dynamic addition and removal of a virtual processor. To add two more cpu virtual processors, you can use the following command:

onmode -p +2 cpu

Check the online.log to see that the operation is successfully executed:

Listing 3. Checking the online.log
$ onstat -m

IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 17 days 22:30:38 
-- 522240 Kbytes

Message Log File: /usr2/support/products/sr_11.50.FC3/tmp/online.log

01:23:50  Checkpoint Completed:  duration was 0 seconds.
01:23:50  Sat Aug  8 - loguniq 22, logpos 0xc34018, timestamp: 0x1c9916 Interval: 3808

01:23:50  Maximum server connections 1
01:23:50  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 
26, Llog used 8

01:28:50  Checkpoint Completed:  duration was 0 seconds.
01:28:50  Sat Aug  8 - loguniq 22, logpos 0xc35044, timestamp: 0x1c991f Interval: 3809

01:28:50  Maximum server connections 1
01:28:50  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 
2, Llog used 1

01:43:53  Checkpoint Completed:  duration was 0 seconds.
01:43:53  Sat Aug  8 - loguniq 22, logpos 0xc36044, timestamp: 0x1c992b Interval: 3810

01:43:53  Maximum server connections 1
01:43:53  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 
2, Llog used 1

01:55:11  Dynamically added 2 cpu VPs

To take the processors away when no they are no longer needed, issue the following command:

onmode -p -2 cpu

Similarly, check the online.log using onstat -m to determine if onmode operation is successful.

Another way to check if the operation is successful is to use onstat -g glo. Listing 4 provides a sample of its output:

Listing 4. onstat -g glo output
$ onstat -g glo

IBM Informix Dynamic Server Version 11.50.FC3     -- On-Line -- Up 17 days 23:16:17 
-- 522240 Kbytes

MT global info:
sessions threads  vps      lngspins
0        24       11       157068

          sched calls     thread switches yield 0   yield n   yield forever
total:    30052583        24391872        114838    18595853  104014
per sec:  52              42              0         31        0

Virtual processor summary:
 class       vps       usercpu   syscpu    total
 cpu         5         160.06    10.67     170.73
 aio         2         0.53      5.69      6.22
 lio         1         1.30      7.84      9.14
 pio         1         0.03      1.26      1.29
 adm         1         0.01      0.01      0.02
 msc         1         0.02      0.01      0.03
 total       11        161.95    25.48     187.43

Individual virtual processors:
 vp    pid       class       usercpu   syscpu    total     Thread    Eff
 1     9258      cpu         38.60     3.87      42.47     3728.55    1%
 2     9259      adm         0.01      0.01      0.02      0.00       0%
 3     9260      cpu         44.39     2.66      47.05     8483.41    0%
 4     9261      cpu         76.98     4.12      81.10     8657.88    0%
 5     9262      lio         1.30      7.84      9.14      1357.97    0%
 6     9263      pio         0.03      1.26      1.29      92.94      1%
 7     9267      aio         0.41      3.72      4.13      328.48     1%
 8     9268      msc         0.02      0.01      0.03      0.23      13%
 9     9269      aio         0.12      1.97      2.09      258.58     0%
 10    26623     cpu         0.03      0.01      0.04      11.39      0%
 11    26624     cpu         0.06      0.01      0.07      10.37      0%
                 tot         161.95    25.48     187.43

By monitoring the number of virtual processors (vps column in the "Virtual processor summary" section) for the cpu class before and after the onmode -p command, you can verify whether the onmode command was executed successfully.

To list out all the options available, you can issue the following:

Listing 5. List of available options
$ onmode
usage:  onmode [-abBCCcDdFIjklMmnOpQRrSsuWYyZz] |
               [-wf <onconfig parameter>=<value>] |
               [-wm <onconfig parameter>=<value>]
      -a <kbytes>    Increase shared memory segment size.
      -b <version>   Revert Dynamic Server disk structures.
      -BC [1|2]  Change server large chunk mode
      -c [block | unblock]   Do Checkpoint. Block or unblock server.
      -C {start <count>|stop <count>|threshold <size>|
          duration <seconds>|rangesize <size>|alice <mode>|
          compression <low|med|high|default>} Tune B-tree scanner.
      -D   <max PDQ priority allowed>
      -d   {standard|{primary|secondary <servername>}} set DR server type
           DR secondary only:
           {idxauto {on|off}} set DR automatic index repair mode
           {index <database>:[owner.]<tablename>#<indexname>} DR repair index
           {add RSS <servername> <optional password>} add RSS server
           {change RSS <servername> <password>} change RSS server password
           {delete RSS <servername>} remove RSS server  definition
           {RSS <source Node> <optional password>} set RSS server type
           {set SDS primary <alias> [force]} define SDS primary server alias
           {clear SDS primary <alias> [force]} remove SDS primary server alias
           {make primary <alias> [force]} make server into the MACH11 primary
      -e {on|off|enable|flush} configure or flush shared statement cache.
      -F   Free unused memory segments
      -I   stop verbose error trapping
      -I <iserrno> [<session ID>]   trap specified error for session ID
      -j   Change to single-user mode
      -k   Shutdown completely
      -l   Force to next logical log
      -M   <decision support memory in kbytes>
      -m   Go to multi-user on-line
      -n   Set shared memory buffer cache to non-resident
      -O   Override space down blocking a checkpoint
      -p <+-#> <class>   Start up or remove virtual processors of
           a specific class
      -Q   <max # decision support queries>
      -R   Rebuild the /INFORMIXDIR/etc/.infos.DBSERVERNAME file
      -r   Set shared memory buffer cache to resident
      -S   <max # decision support scans>
      -s   Change to quiescent mode
      -u   Change to quiescent mode and kill all attached sessions
      -W   {STMT_CACHE_NOLIMIT {0|1} | STMT_CACHE_HITS <#>}   Sets ssc
           parameters.
      -wf <onconfig parameter>=<value>   Update the value
          of the specified configuration parameter and save
          the new value to the onconfig file.
      -wm <onconfig parameter>=<value>   Update the value of
          the specified configuration parameter without saving
          the new value to the onconfig file. The new value is
          written to server memory only and will not be retained
          once the server is restarted.
      -y   Do not require confirmation
      -Y <sid> <0|1|2> [filename] Set or unset dynamic explain
            0=off  1=plan + statistics on  2=only plan on
            filename is a valid argument only when setting the
            dynamic explain or dynamic explain statistics on
      -Z <address> heuristically complete specified transaction
      -z <sid>   Kill specified session id

Monitoring IDS using onstat utility

When the server starts, it creates structure control blocks in the shared memory segments. These control blocks are used internally by the server for the various operations. Thus, by reading the control structures, you are able to monitor the state or different events of the server. The onstat utility is primarily used for that purpose.

Figure 2 shows the onstat utility reading the different C language structures in the shared memory. These structures are information related to sessions, locks, buffers, logs, and so on. The onstat utility reads these structures and displays them in a readable format.

Figure 2. IDS shared memory overview
Diagram illustrating the structure control blocks in the shared memory segments

For a start, the simplest onstat command is as follows:

$ onstat -

IBM Informix Dynamic Server Version 11.50.FC3     -- On-Line -- Up 17 days 08:12:09 --
522240 Kbytes

It shows that the version of IDS running is 11.50.FC3 and that it had been in online mode for 17 days, 8 hour, 12 minutes, and 9 seconds. The memory it is using is 522240KB.

To find out about sessions information, use the -g ses option:

Listing 6. -g ses option
$ onstat -g ses

IBM Informix Dynamic Server Version 11.50.FC3     -- On-Line -- Up 00:00:45 -- 
149504 Kbytes

session                                  #RSAM    total      used       dynamic
id       user     tty    pid    hostname threads  memory     memory     explain
21       informix -      0      -        0        12288      11688      off
20       informix 93     28925  acme     1        106496     84920      off
18       informix -      0      -        1        348160     329960     off
17       informix -      0      -        1        299008     274344     off
16       informix -      0      -        1        417792     344576     off
3        informix -      0      -        0        16384      13272      off
2        informix -        0        -        0        12288      11688      off

As shown in Listing 6, a list of sessions and their information are displayed. Session id in the first column is a unique number used by the engine to track each session. The user id, pid of the client, and the hostname that the connection is from are also shown. One interesting column is the RSAM thread, which shows how many threads are spawned for that session. It is possible to have multiple threads associated with one session. Usually, for one client session, there will be one corresponding sqlexec thread at the database server end. This thread is responsible for receiving the instruction from the client and sending information back to it. The server might spawn additional threads for a session if required. One such example of additional thread is the scan thread, used for scanning disk for data.

You can obtain the details for a given session:

Listing 7. Getting info about session 20
$ onstat -g ses 20

IBM Informix Dynamic Server Version 11.50.FC3     -- On-Line -- Up 00:02:04 -- 
149504 Kbytes

session           effective                          #RSAM    total    used       dynamic
id       user     user      tty      pid    hostname threads  memory   memory     explain
20       informix -         93       28925  acme     1        106496   84920      off

tid      name     rstcb            flags    curstk   status
47       sqlexec  1115375c8        Y--P---  7839     cond wait  netnorm   -

Memory pools    count 2
name         class addr             totalsize  freesize   #allocfrag #freefrag
20           V     11258d040        102400     20768      94         12
20*O0        V     1125f1040        4096       808        1          1

name           free       used           name           free       used
overhead       0          6576           scb            0          144
opentable      0          3608           filetable      0          832
log            0          16536          temprec        0          21664
keys           0          176            gentcb         0          1584
ostcb          0          2816           sqscb          0          19112
sql            0          72             rdahead        0          1120
hashfiletab    0          552            osenv          0          2648
sqtcb          0          7128           fragman        0          352

sqscb info
scb              sqscb            optofc   pdqpriority sqlstats optcompind  directives
1125be0c0        1125d5028        0        0           0        2           1

Sess       SQL            Current            Iso Lock       SQL  ISAM F.E.
Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain
20         -              db1                CR  Not Wait   0    0    9.24  Off

Last parsed SQL statement :
  select * from t1

The additional information you can obtain includes detail of the thread associated with the session, the detail of the memory used as in the "Memory pools section", and the detail of the SQL statement parsed or executed. The column "SQL ERR" and "ISAM err" shows you if this session encountered any errors when executing the SQL statement.

To view dbspace and chunk information, use the -d option:

Listing 8. -d option
$ onstat -d

IBM Informix Dynamic Server Version 11.50.FC3     -- On-Line -- Up 00:03:43 
-- 149504 Kbytes

Dbspaces
address      number   flags      fchunk   nchunks  pgsize   flags    owner    name
111444028    1        0x40001    1        1        2048     N  B     informix rootdbs
 1 active, 2047 maximum

Chunks
address      chunk/dbs     offset     size       free   bpages     flags pathname
1114441c0    1      1      0          100000     29151             PO-B- /support/rootchk
 1 active, 32766 maximum

NOTE: The values in the "size" and "free" columns for DBspace chunks are
      displayed in terms of "pgsize" of the DBspace to which they belong.

Expanded chunk capacity mode: always

This onstat displays the output into two main sections: dbspaces and chunks.

In the "Dbspaces" section, the important field to take note is "fchunk", which indicates the first chunk number (which is the identifier for the chunk in the "Chunks" section) of the dbspace. The "nchunks" tells you how many chunks this dbspace has altogether.

From the "Chunks" section, the size of the chunk and the amount of free space available are shown in the "siz" and "free" columns, respectively. As the special "NOTE" written at the bottom of the output indicates, the unit for "size" and "free" column are in pages of dbspace that it belongs to. For example, for the chunk 1 in this case, its size is 100000 pages, and size of each page for dbspace 1 is 2048 bytes, as shown in the pgsize column in the "Dbspace" section. Thus, the size of chunk 1 is 204 800 000 bytes.

There are many other options available. To list them, you can use the following command:

Listing 9. List of available options
$ onstat --
usage: onstat [ -abBcCdDfFgGhjklLmOpPRstTuxXz ] [ -i ] [ -r [<seconds>] ]
              [ -o [<outfile>] ] [ <infile> ]

    --    Print this help text
    <infile>
          Read shared memory information from specified dump file
    -a    Interpreted as onstat -mcuxskbPFhRtdGflLpO; onstat -g all; onstat -XC
    -b    Print buffers
    -B    Print all buffers
    -c    Print configuration file
    -C    Print b-tree scanner requests
    -d [update]
          Print spaces and chunks
          update - Ask server to update BLOB chunk statistics
    -D    Print spaces and detailed chunk stats
    -f    Print dataskip status
    -F    Print page flushers

    -g <cmd>   MT COMMAND or ENTERPRISE REPLICATION COMMAND (see below)

    MT COMMANDS:
        act   Print active threads
        afr <pool name|session id>
              Print allocated pool fragments
        all   Print all MT information
        ath   Print all threads
        buf Print profile information related to buffer pools.
        ckp   Print checkpoint statistics
        cmsm  Print Connection Manager statistics
        con   Print conditions with waiters
        cpu   Print CPU info for all threads
        dbc   Print dbScheduler/dbWorker thread info
        ddr   Print DDR log post processing information
        dic   Print dictionary cache information
        dis   Print a list of database servers and the status of each
        dll   Print dynamic library statistics
        dmp <address> <length>
              Dump <length> bytes of shared memory starting at <address>
        dri   Print data replication information
        dsc   Print a list of distribution cache information
        env [ all | [<session-id>] ] [<variable-name>[,<variable-name>...]]
              Display environment variable settings.
        ffr <pool name|session id>
              Print free pool fragments
        glo   Print MT global information
        his [<ntraces>]
              Prints SQL statement tracing information for <ntraces>
                    no <ntraces> = Complete output from trace buffer
        idxscan Print index scan profiles
        imc   Print information about connected MaxConnect instances
        iob   Print big buffer usage by IO VP class
        iof   Print disk IO statistics by chunk/file
        iog   Print AIO global information
        iov   Print disk IO statistics by vp
        ipl   Print index page logging status
        lap   Print light append information
        lmx   Print all locked mutexes
        lsc   Print Light Scan information
        mem [<pool name>|<session id>]
              Print pool statistics.
        mgm   Print Memory Grant Manager information
        nbm   Print block map for non-resident segments
        nsc [<client id>]
              Print net shared memory status
        nsd   Print net shared memory data
        nss [<session id>]
              Print net shared memory status
        ntd   Print net dispatch information
        ntm   Print net message information
        ntt   Print net user thread access times
        ntu   Print net user thread profile information
        opn [<tid>]
              Print open tables
        plk   Print partition lock profiles
        pos   Print /INFORMIXDIR/etc/.infos.DBSERVERNAME file
        ppf [<partition number> | 0]
              Print partition profiles
        pqs [<session id>]
              Print statistics for an active query
        prc   Print information about SPL routine cache
        proxy [all | [<proxy id> [<txn id> [<op num>]]] ]
              Print updatable secondary related information
        qst   Print queue statistics
        rbm   Print block map for resident segment
        rea   Print ready threads
        rss [verbose | log | <RSS Srv name>]   Print RSS server related
              information
        rwm   print Read/Write Mutex lists
        sch   Print VP scheduler statistics
        sds [verbose | <SDS server name>]   Print SDS related information
        seg   Print memory segment statistics
        ses [<session id>]
              Print session information
        sle   Print all sleeping threads
        smb   Print smart-large-object usage
        smx [ses]   Print smx related information
        spi   Print spin locks with long spins
        sql [<session id>]
              Print SQL information
        src <pattern> <mask>
              Search memory for <pattern>, where <pattern>==(memory&<mask>)
        ssc [pool|all]
              Prints ssc pool summary, or statement cache summary and
              entries, including key only entries (all)
        stk <tid>
              Dump the stack of a specified thread
        stm [<sesson id>]
              Prints all prepared statements approximate memory usage in a
              session
        stq [<session id>]
              Print stream queue information
        sts   Print max and current stack sizes
        tgp   Print generic page thread profiles
        tpf [<tid> | 0]
              Print thread profiles
        ufr <pool name|session id>
              Print pool usage breakdown
        vpcache Print CPU VP memory block cache statistics
        wai   Print waiting threads
        wmx   Print all mutexes with waiters
        wst   Print thread wait statistics

    ENTERPRISE REPLICATION COMMANDS:
        cat [scope | replname ]
              Print Enterprise Replication global catalog information
        cdr   Print Enterprise Replication statistics
        cdr config [parameter_name] [long]
        cdr config CDR_ENV [variable_name] [long]
              Print Enterprise Replication configuration information
              Not specifying a parameter will display the name of and
              information about all available parameters.
        dtc   Print statistics for the Enterprise Replication delete table
              cleaner
        dss [ UDR | UDRx ]
              Print statistics about data sync threads and user-defined data
              types
        grp [ A|E|Ex|G|L|Lx|M|Mz|P|pager|R|S|Sl|Sx|T|UDR|UDRx ]
              Print statistics about the Enterprise Replication grouper
        nif [ all | sites | serverid | sum ]
              Print statistics about the Enterprise Replication network
              interface
        que   Print statistics for the Enterprise Replication high-level queues
        rcv [serverid]
              Print statistics about the Enterprise Replication receive manager
        rep [replname]
              Print events that are in the queue for the schedule manager
        rqm [ ACKQ | CNTRLQ | RECVQ | SENDQ | SYNCQ | SBSPACES | FULL | BRIEF |
            VERBOSE ]
              Print statistics of the Enterprise Replication low-level queues
        sync  Print the Enterprise Replication synchronization status

    -G    Print global transaction ids
    -h    Print buffer hash chain info
    -i    Interactive mode
    -j    Print interactive status of the active onpload process
    -k    Print locks
    -l    Print logging
    -L    Print distribution of available locks on the lock free lists
    -m    Print message log
    -o    Put shared memory into specified file (default: onstat.out)
    -O    Print Optical Subsystem memory and staging cache information
    -p    Print profile
    -P    Print partition buffer summary
    -r    Repeat options every <seconds> seconds (default: 5)
    -R    Print LRU queues
    -s    Print latches
    -t    Print TBLspaces
    -T    Print tablespace information
    -u    Print user threads
    -x    Print transactions
    -X    Print entire list of sharers and waiters for buffers
    -z    Zero profile counts

Data movement utilities

Export and import database objects

The dbschema utility can be used to export various database objects such as tables, stored procedures, sequences, and so on, without unloading the data.

For example, to obtain the schema of the table t1 in database db1, you can use the following command:

Listing 10. Obtain table schema
dbschema -t t1 -d db1

DBSCHEMA Schema Utility       INFORMIX-SQL Version 11.50.FC3

{ TABLE "informix".t1 row size = 4 number of columns = 1 index size = 0 }
create table "informix".t1
  (
    c1 integer
  );

revoke all on "informix".t1 from "public" as "informix";

To get schemas for all the tables in db1, issue the following command:

dbschema -t all -d db1

To get the stored procedure sp1 in db1:

dbschema -f sp1 -d db1

Similarly, to get all the stored procedures in db1:

dbschema -f all -d db1

If you want to export the schema of the whole database, issue the following command:

dbschema -d db1

To recreate the objects in another database, simply run the dbschema output (without the header) using any user front-end tools such as dbaccess or the OpenAdmin Tool.

To suppress the header of dbschema output, use the -q option:

Listing 11. Suppress dbschema output header
$ dbschema -q -t t1 -d db1

{ TABLE "informix".t1 row size = 4 number of columns = 1 index size = 0 }
create table "informix".t1
  (
    c1 integer
  );

revoke all on "informix".t1 from "public" as "informix";

Table 2 lists the other important options to take note of:

Table 2. Other important options of dbschema
OptionsDisplay
-hdDistribution of a column, a table, or all the tables
-seq Syntax of sequence
-p Permission of user
-s Synonyms

Export and import table data

To export and import a data into a table, you can use one of the following:

  • SQL statements UNLOAD and LOAD
  • HPL
  • dbload
  • onload/onunload

For instance, to export the data for table t1 in database db1, you can use the following command:

unload to 'my.dat' select * from t1;

Typically, the file content will look like that in Listing 12:

Listing 12. Example file content
$ more my.dat
1|
2|
3|
4|
5|
6|
7|
8|
9|
10|

Note that the columns of the table are delimited by the vertical bar (|).

To change the delimiter to the dollar ($) sign, you can issue the following command:

Listing 13. Change delimiter
unload to 'my.dat' delimiter '$' select * from t1;

$ more my.dat
1$
2$
3$
4$
5$
6$
7$
8$
9$
10$

To load the data from the file to the table t1 in db1, simply run the following command in db1:

load from 'my.dat' insert into t1;

To load the file you unloaded with the $ delimiter:

load from 'my.dat' delimiter '$' insert into t1;

Export and import database or table in binary format

The onunload and onload utilities allow you to export and import the database or table data in binary format. This method provides the fastest transfer speed among the utilities discussed so far. However, handling transfer in binary format results in several restrictions, such as (but not limited to):

  • The source and target databases must be in a database server with the same version.
  • The source and target database servers must be residing on the OS of the same page size, same representation of numeric data, and same byte alignment for structures and union.
  • Cannot transfer between GLS and non-GLS locale.

onunload and onload can be used to export the whole database or a specific table to either a tape or a file.

To unload a whole database to a file /tmp/tape, issue the following command:

onload -t /tmp/tape db1

To import the database into the target database server from a file that is transferred to the /tmp2 directory on that machine:

onunload -t /tmp2/tape db1

dbload

The dbload utility provides similar functionality to that of a LOAD SQL statement, but with more flexibility. However, the cost of better flexibility is a hit in performance and is as easy to use as the LOAD SQL statement.

To use dbload, you need to first create a command file. The command file describes input data file and mapping of the input data file fields to the columns of the target table.

For example, say you have a command file name "mycmd" that looks like this:

FILE orders.unl DELIMITER '|' 10; 
INSERT INTO orders;

Here, the command file describes that the name of the input file is orders.unl, the delimiter use is |, there are 10 fields, and this data is to be loaded to the orders table.

To run the dbload against the database mydb, issue the following command:

dbload -d mydb -c mycmd

Let's explore how dbload can provide more flexibility. Consider a command file with content as shown in Listing 14:

Listing 14. Command file content
FILE orders.unl DELIMITER '|' 10; 
INSERT INTO new_orders (col1, col2, col5, col6, col3) 
	VALUES (f01, f03, f07, f05, 'new_constant');

This command file uses the orders.unl input file also. However, it is now inserting the data into the new_orders table. As the dbload internally assigns names to the columns of the input command file, starting with f01, you can use these internally named fields in the INSERT statement. As demonstrated here, the INSERT SQL is loading the field f01 into col1 column of new orders table, f03 into col2, f07 into col5, f05 into col6, and the string 'new_constant' into col3.

In addition, you can specify the number of bad rows to ignore when loading by using the -e option. Use the -i option for a specific row.

High Performance Loader (HPL)

High Performance Loader (HPL) provides an efficient and flexible way to load or unload a large amount of data. To appreciate its flexibility and efficiency, let's take a quick look at how the load and unload are being done.

To use HPL to load the data, a set of metadata components needs to be defined first. The components are:

  • Device array - describes the source, which can either be a file, tape, or pipe(UNIX). There is no 2GB limitation on the source.
  • Format - describes the source file's layout (such as whether it is delimited or fixed-length record) and the data type of fields.
  • Filter - specifies the criteria for the records to be accepted and thus loaded into the target.
  • Map - describes which fields of the file are to be mapped to the columns of the target table.

From these components, HPL is able read from the device array, understand the source using the description in the format, allowing the records that meet the filter to pass through and insert the records to the correct column by using the map.

The flexibility comes from varying the properties of these components. For example, the case of characters can be specified in the map, or you can write your own custom-conversion in a map. The records to accept or reject can be easily configured in the filter.

There are two modes in which HPL can do its loading: deluxe and express. In express mode, the load is significantly faster, but it is not as flexible as deluxe mode.

For unload, the component filter is not used. Instead, the query component is used. This is the component that describes the SELECT statement, which is used to determine the rows to unload. The HPL then uses the map to decide which column of the field to be mapped to which field on the device array. Likewise, the format describes the layout and data type of the fields.

These components of HPL are stored in the onpload database.

You can use either ipload to start an HPL GUI interface or the onpladm utility, which is the command-line version of it. After the load or unload jobs have been completely specified using either ipload or onpladm, the onpload utility can be used to run them directly from the command line.

As HPL is multi-threaded, it allows a large number of the loading or unloading of tables to be executed in parallel and thus effectively improves the throughput of the process.


Automatic monitoring, maintenance, and administrative tasks

IDS provides automatic and flexible monitoring, maintenance, and administrative tasks through the combination of the Scheduler, SQL Admin API functions, and information stored in a system database called "sysadmin." In addition, query drill-down functionality provides a means of monitoring SQL queries in finer granularity. Let's visit these components in detail.

Scheduler

IDS administrative jobs can broadly be categorized into two groups: task and sensor. A task is a job that executes one of the following:

  • An SQL statement or a group of SQL statements
  • A stored procedure
  • A C or Java™ technology routine

A sensor is a special kind of task that is used to collect desired information.

Tasks or sensors that run only during startup of the server are called startup tasks or startup sensors, respectively.

The IDS scheduler runs the tasks and sensors at specified times or intervals. It takes the information about the task and sensor in the ph_task table in the sysadmin database.

To stop the Scheduler, run the following command:

Listing 15. Stop the Scheduler
$ dbaccess sysadmin -

Database selected.

> execute function task ("scheduler stop");

(expression)  Successfully shutdown scheduler

1 row(s) retrieved.

To start it, run this command:

Listing 16. Start the Scheduler
$ dbaccess sysadmin -

Database selected.

> execute function task ("scheduler start");

(expression)  Successfully started 2 threads

1 row(s) retrieved.

Running onstat -g ath will show the thread of the scheduler threads (dbScheduler, dbWorker1, and dbWorker2):

Listing 17. onstat -g ath
$ onstat -g ath

IBM Informix Dynamic Server Version 11.50.FC3     -- On-Line -- Up 1 days 00:45:40 
-- 149504 Kbytes

Threads:
 tid     tcb         rstcb         prty status              vp-class     name
 2       112038808   0             1    IO Idle             4lio*        lio vp 0
 3       1120582a8   0             1    IO Idle             5pio*        pio vp 0
 4       1120782a8   0             1    IO Idle             6aio*        aio vp 0
 5       1120972a8   0             1    IO Idle             7msc*        msc vp 0
 6       1120c62a8   0             1    IO Idle             8aio*        aio vp 1
 7       1120e6548   11152e028     3    sleeping secs: 1    3cpu         main_loop()
 8       112058508   0             1    running             1cpu*        tlitcppoll
 9       112078c40   0             2    sleeping forever    1cpu*        tlitcplst
 10      1120978f8   11152e878     1    sleeping secs: 1    1cpu         flush_sub(0)
 11      112097be0   11152f0c8     1    sleeping secs: 1    3cpu         flush_sub(1)
 12      11218b028   11152f918     1    sleeping secs: 1    3cpu         flush_sub(2)
 13      11218b310   111530168     1    sleeping secs: 1    3cpu         flush_sub(3)
 14      11218b5f8   1115309b8     1    sleeping secs: 1    1cpu         flush_sub(4)
 15      1121e76a0   111531208     1    sleeping secs: 1    3cpu         flush_sub(5)
 16      1121e7988   111531a58     1    sleeping secs: 1    1cpu         flush_sub(6)
 17      1121e7c70   1115322a8     1    sleeping secs: 1    3cpu         flush_sub(7)
 18      1122365b0   111532af8     2    sleeping secs: 1    1cpu         aslogflush
 19      1122d9370   111533348     1    sleeping secs: 99   3cpu         btscanner_0
 35      112572d50   111534c38     3    sleeping secs: 1    1cpu*        onmode_mon
 36      1122fb970   1115343e8     3    sleeping secs: 1    3cpu         periodic
 45      1125f6520   111536d78     1    cond wait  bp_cond  3cpu         bf_priosweep()
 261     112947808   1115375c8     1    cond wait  netnorm  1cpu         sqlexec
 267     112362d48   111535488     1    sleeping secs: 152  3cpu*        dbScheduler
 268     112c42d20   111535cd8     1    sleeping forever    3cpu         dbWorker1
 269     11249e4c0   111536528     1    sleeping forever    1cpu         dbWorker2

In Listing 17, you can see the dbScheduler thread, which constantly manages the sensors or tasks, and the dbWorker threads—the threads that execute the sensor and task (put on queue for them by the dbScheduler).

The sysadmin database

sysadmin is a system database created automatically during server initialization. It contains information that the Scheduler uses to run the task or sensor. At the heart of the sysadmin database is the ph_task table, which basically records information about a task or sensor and when to run it. Additionally for sensor, it contains information about what to collect and where to store it.

Let's take a look at a built-in example:

Listing 18. Task example
tk_id               1
tk_name             mon_command_history
tk_description      Monitor how much data is kept in the command history table
tk_type             TASK
tk_sequence         23
tk_result_table
tk_create
tk_dbs              sysadmin
tk_execute          delete from command_history where cmd_exec_time < (
                            select current - value::INTERVAL DAY to SECOND
                            from ph_threshold
                            where name = 'COMMAND HISTORY RETENTION' )
tk_delete             0 01:00:00
tk_start_time       02:00:00
tk_stop_time
tk_frequency          1 00:00:00
tk_next_execution   2009-08-05 23:46:05
tk_total_executio+  23
tk_total_time       0.20672
tk_monday           t
tk_tuesday          t
tk_wednesday        t
tk_thursday         t
tk_friday           t
tk_saturday         t
tk_sunday           t
tk_attributes       404
tk_group            TABLES
tk_enable           t
tk_priority         0

From the tk_type, you know that this is a task and its name is "mon_command_history".

This task will execute the action as specified in tk_execute, which is:

Listing 19. Task specified in tk_execute
delete from command_history where cmd_exec_time < (
                            select current - value::INTERVAL DAY to SECOND
                            from ph_threshold
                            where name = 'COMMAND HISTORY RETENTION' )

If you run the following subquery, this SQL, in effect, deletes the rows from command_history where the cmd_exec_time is before 2009-07-06 09:13:49.000:

Listing 20. Subquery to delete rows
select current - value::INTERVAL DAY to SECOND
                            from ph_threshold
                            where name = 'COMMAND HISTORY RETENTION';

select current as now,value
from ph_threshold
 where name = 'COMMAND HISTORY RETENTION'

Output:

(expression)

2009-07-06 09:29:21.000



now    2009-08-05 09:29:21.000
value  30 0:00:00

In this example, you also see another table in sysadmin database being used—ph_threshold . This table is used to keep threshold, which will trigger the delete statement, which is 30 days before the point when the task is executing.

tk_start_time indicates this task will run at 2 am daily from Monday to Sunday since tk_monday to tk_sunday are all marked true.

Let's now look at an example of a sensor:

Listing 21. Sensor example
tk_id               6
tk_name             mon_vps
tk_description      Process time of the Virtual Processors
tk_type             SENSOR
tk_sequence         117
tk_result_table     mon_vps
tk_create           create table mon_vps (ID integer, vpid smallint, num_ready
                    smallint, class integer,  usecs_user float, usecs_sys float
                    )
tk_dbs              sysadmin
tk_execute          insert into mon_vps select $DATA_SEQ_ID,  vpid, num_ready,
                    class, usecs_user, usecs_sys FROM sysmaster:sysvplst
tk_delete            15 00:00:00
tk_start_time
tk_stop_time
tk_frequency          0 04:00:00
tk_next_execution   2009-08-05 07:46:05
tk_total_executio+  117
tk_total_time       8.074843700000
tk_monday           t
tk_tuesday          t
tk_wednesday        t
tk_thursday         t
tk_friday           t
tk_saturday         t
tk_sunday           t
tk_attributes       405
tk_group            CPU
tk_enable           t
tk_priority         0

Note that the tk_type field value indicates that this task is a sensor. And tk_execute indicates the information to capture and keep them in mon_vps table. The tk_result_table specifies the schema of the mon_vps table.

By default, the sysadmin database resides in the rootdbs. To move it out to another dbspace, use the following SQL Admin API:

EXECUTE FUNCTION task("reset sysadmin","dbs1");

Checking the online.log:

Listing 22. Check online.log
onstat -m

10:09:46  SCHAPI: thread dbScheduler task mon_checkpoint(0-457) shutting down
10:09:46  SCHAPI: thread dbWorker1 task mon_config_startup(3-38) shutting down
10:09:46  SCHAPI: thread dbWorker2 task mon_memory_system(8-231) shutting down
10:09:46  SCHAPI: 'sysadmin' database will be moved to 'dbs1'. See online message log.
10:09:46  Building 'sysadmin' database ...
10:09:53  Unloading Module <SPLNULL>
10:09:57  Loading Module <SPLNULL>
10:10:05  Unloading Module <SPLNULL>
10:10:05  Loading Module <SPLNULL>
10:10:05  'sysadmin' database built successfully.
10:10:05  SCHAPI: Started dbScheduler thread.
10:10:05  SCHAPI: Started 2 dbWorker threads.

SQL Admin API functions

Traditionally, administration tasks, such as management of dbspaces, chunks, logical logs, or physical logs, are done using utilities such as onspaces, onparams, and onmode. However, with the introduction of SQL Admin API functions, a set of UDRs now provides similar functionality. The DBA can now execute these administration tasks from a remote machine as long as it can run the user routine just like a query. Also, the result and audit trail of the action can be kept in a command_history table in the sysadmin database.

Two UDRs called task and admin are used. Their functionality is identical, except the result return value is different. For task, the return status is in the form of character:

EXECUTE FUNCTION task('create dbspace', 'dbspace2', '/CHUNKS/dbspace2');
(expression)  created dbspace number 2 named dbspace2

For admin, return status is an integer, which is kept in the cmd_ret_status field of the command_history table:

EXECUTE FUNCTION admin('create dbspace', 'dbspace2', '/CHUNKS/dbspace2');
(expression)  107

IDS user interfaces

IDS provides two user interfaces, namely dbaccess and the OpenAdmin Tool (OAT). This section of the tutorial provides a quick overview of these frontend tools.

dbaccess

The dbaccess utility provides quick and flexible access to the database engine. It can run in a menu driven mode or interactive mode. In addition, it can be used as part of a script.

To run dbaccess, ensure that the following environment variables are set correctly:

  • INFORMIXSERVER - Server name to be connected to
  • INFORMIXDIR - Directory where IDS is installed
  • PATH - The path which contains dbaccess
  • INFORMIXSQLHOSTS - File describing connection information
  • TERM - Code for terminal (for example, vt100)
  • TERMCAP - Typically, the one supplied by database server in $INFORMIXDIR/etc/termcap

Menu-driven interactive mode

Let's first get an overall idea of the menu options available. Table 3 lists the available main menu options, which include their own sets of options:

Table 3. dbaccess menu overview
Main menu optionSub-optionAction
Query-language


NewType new SQL statements in text editor.

RunExecute current SQL statements.

ModifyModify current SQL statements in SQL editor.

Use-editorSwitch to a system editor to enter or modify SQL statements.

OutputSend output from an SQL editor to printer, file, or page.

ChooseChoose and load a file to the text editor.

SaveSave SQL statements to a file.

InfoDisplay an SQL statement from the database.

ExitReturn to the main menu or command line.
Connection


ConnectConnect to a database server and select a database.

DisconnectDisconnect from a database environment.

ExitReturn to the main menu or command line.
Database


SelectSelect a database.

CreateCreate a database.

InfoRetrieve database information.

DropDelete an existing database.

CloseClose the current database.

ExitReturn to the main menu or command line.
Table


CreateCreate a table.

AlterAlter a table.

DropDelete a table from the database.

InfoDisplay table information.

MoveMove a table from current database to another.

ExitReturn to the main menu or command line.
Sessionn/aDescribe the database server and host computer.
Exitn/aEnd database access.

To start the menu-driven dbaccess, enter:

dbaccess

You will see the following screen:

Figure 3. dbaccess - Query-language menu
Screenshot of the menu-driven dbaccess - query-language

To traverse and highlight a different menu option, use the left or right arrow key. When the desired menu option is highlighted, use the Enter key to select it. Alternatively, you can key in the alphabet capitalized in the menu option. For example, if you want to choose Database, simply key in d or D.

After selecting Database, you then come to the following screen, which prompts the database action to be carried out:

Figure 4. dbaccess select database
Screenshot of the menu-driven dbaccess - select database

If you choose Select, a list of databases will be shown, as Figure 5 illustrates:

Figure 5. dbaccess database listing
Screenshot of the menu-driven dbaccess - database listing

Use the down arrow key to traverse the list to the desired database. Optionally, you can key in the database name. If you enter db1, you will see the following screen:

Figure 6. dbaccess database selected
Screenshot of the menu-driven dbaccess - database selected

Take note of the following line:

 ----------------------- db1@ol_115fc3 ---------- Press CTRL-W for Help --------

It shows that the database selected is db1 and the instance (or server) name is ol_115fc3.

At this point, to go up one level in the menu, choose Exit to exit current menu level. You will get back to the top menu with the following screen:

Figure 7. dbaccess - Main menu
Screenshot of the menu-driven dbaccess - main menu

Choose Query-language. You will see the following screen:

Figure 8. dbaccess - SQL menu
Screenshot of the menu-driven dbaccess - SQL menue

At this point, you have a list of SQL actions you can perform.

As you are here for the first time, choose New:

Figure 9. dbaccess - SQL entry
Screenshot of the menu-driven dbaccess - SQL entry

Key in the SQL statement that you want, and press the Esc key when done:

Figure 10. dbaccess - Run SQL
Screenshot of the menu-driven dbaccess - run SQL

As "Run" is highlighted, pressing the Enter key will give you the SQL result as below:

Figure 11. dbaccess - Run SQL result
Screenshot of the menu-driven dbaccess - result from run SQL

If you want to modify it, there are two ways to do so. If you chose the Modify option, it will bring you back to screen similar to when you choose New, but this time with the SQL statement you just ran. You can also use the option Use-editor to modify the query. In UNIX, the editor will default to vi and on Windows to Notepad. Using a text editor provides you a faster way to change the SQL. When modification is completed in the editor, remember to save and exit, and it will bring you back to the dbaccess screen with the changed SQL.

The dbaccess utility is able to display table information. If you go back to the main menu again and select the Table option followed by Info menu option, a list of tables in the database will be displayed. Let's say table t1 is chosen, as illustrate in Figure 12:

Figure 12. dbaccess - Table information
Screenshot of the menu-driven dbaccess - table information

From here, you can display table information such as column detail, indexes, fragmentation, and so on.

Interactive non-menu mode

To run the dbaccess in this mode, simply use the following command:

$ dbaccess db1 -

Database selected.

You will get a ">" prompt. Issue the SQL and end it with a semicolon. Press Enter to run the SQL:

Listing 23. Run the SQL
> select * from t1;


         c1

          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10

20 row(s) retrieved.

Send the interrupt key. (The key combination for interrupt is usually Ctrl+C, but it depends on your terminal I/O options. Use stty -a to check your setting for interrupt.)

You can run a UNIX command in this mode simply by placing an exclamation point (!) before the command.

Batch command input in UNIX

You can pipe SQL into the dbaccess utility as follows:

Listing 24. Pipe SQL into the dbaccess utility
$ echo "select * from t2" | dbaccess db1

Database selected.

         c1

          1
          2

2 row(s) retrieved.

Database closed.

You can also run it in batch command input in UNIX, like this:

$ dbaccess db1 <<!

When the prompt is returned, enter the desired SQL statements, separated by a semicolon. When ready to run, enter the exclamation point (!), as in Listing 25:

Listing 25. Enter and run SQL statements
> select * from t1;
> !

Database selected.

         c1

          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10

20 row(s) retrieved.

It is possible to place a list of SQL statements in a command file and run them from dbaccess.

For example, let's say you have a command file name mytest.sql with the following SQL statements:

update t1 set c1=20;
select * from t1;

To run it, follow the commands in Listing 26:

Listing 26. Run SQL statements from dbaccess
$ dbaccess db1 mytest.sql

Output:

Database selected.


20 row(s) updated.



         c1

         20
         20
         20
         20
         20
         20
         20
         20
         20
         20
         20
         20
         20
         20
         20
         20
         20
         20
         20
         20

20 row(s) retrieved.


Database closed.

OpenAdmin Tool for IDS

OpenAdmin Tool for IDS (OAT) is a PHP-based browser application that allows you to manage multiple IDS instances across different geographical locations. With the use of SQL Admin API and sysmaster database information, OAT is able to monitor and carry all the monitoring and administrative tasks on remote IDS instances.

It allows (but is not limited to) the following tasks:

  1. Space administration on dbspace, chunks, physical log, and logical logs
  2. Server administration on onconfig parameters
  3. Enterprise replication administration
  4. Performance analysis
  5. SQL Toolbox, which allows viewing of database objects and executing SQL statements
  6. Managing Task Scheduler

OAT is an open-source project, and thus allows users the possibility to expand its functionality if required. It can be downloaded free of charge (see Resources.)

Install and configure OAT

After downloading OAT, run the installer.

On the login screen, enter the appropriate server details:

Figure 13. OAT login screen
Screenshot of the OAT login screen

You can get a visual of the geographical distribution of the various instances and their statuses if you entered the latitude and longitude information. All of this is available in one shot in your favorite browser.

Figure 14. OAT Health Center
Screenshot of the OAT Health Center

On the left pane, you will see the options for functionalities the tool provides, namely:

  1. Health Check - gives a list of alerts to DBA and a dashboard for resources (in other words, cpu, lock) usage
  2. Logs - logs content for Admin API, IDS, and OnBar activities
  3. Task Scheduler - provides information for setup and execution details of Task Scheduler
  4. Space Administration - disk space management
  5. Server Administration - includes setup for MACH, configuration for IDS, virtual processor management, and Auto Update Statistics Information
  6. Enterprise Replication - Enterprise Replication management
  7. Performance Analysis - allows you to specify reports on various resources or SQL, and monitor current sessions and their details
  8. SQL Toolbox - includes a schema browser to view the schema of the database object and also a SQL Editor for running SQL statement to be run

Choosing the Space Administration, you will see the following:

Figure 15. OAT space management
Screenshot of the OAT space management page

On this page, you can manage dbspace and chunks.

If you click on one of the dbspace, the following page will be loaded, which gives details of that dbspace:

Figure 16. OAT dbspace detail
Screenshot of the OAT dbspace detail page

The SQL Explorer shows the details of the SQL statement executed when you enable tracing:

Figure 17. OAT SQL detail
Screenshot of the OAT SQL detail page, Active Summary tab

This screen gives us a summary of the SELECT, UPDATE, DELETE, and INSERT operations.

You can drill into each SQL for its detailed, as illustrated in Figures 18 -20:

Figure 18. OAT SQL detail
Screenshot of the OAT SQL detail page, SQL tab, Query Info subtab
Figure 19. OAT SQL detail
Screenshot of the OAT SQL detail page, SQL tab, Detail Statistics subtab
Figure 20. OAT SQL detail
Screenshot of the OAT SQL detail page, SQL tab, Query Tree subtab

Conclusion

This tutorial has introduced the various tools and utilities that are available with IDS to ease administration tasks. At the end of this tutorial, you should be able to:

  • Use oninit and onmode utilities to change the mode of the database server
  • Use onstat to monitor the database server
  • Use the available data movement utilities to move database objects and data between databases
  • Describe functionality of sysadmin database and SQL Admin API
  • Demonstrate knowledge of the features available in dbaccess and OAT

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=428683
ArticleTitle=Informix Dynamic Server 11.50 Fundamentals Exam 555 certification preparation, Part 7: Tools and utilities
publish-date=09172009