IBM Informix 11.5 Informix Database Administration

IBM Informix 11.5
Informix Database
Administration
I
Information Management Partner Technologies
Contents
1.
INTRODUCTION ...........................................................................................3
2.
SUGGESTED READING...............................................................................3
3.
INVOKING AND USING THE DB-ACCESS UTILITY ...................................5
3.1
3.2
3.3
3.4
4.
ADMINISTERING INFORMIX SERVERS WITH OAT.................................11
4.1
4.2
4.3
4.4
5.
REMOTE ADMINISTRATION USING SQL COMMANDS.....................................30
USING THE ADMIN() FUNCTION ...............................................................31
USING THE TASK() FUNCTION ..................................................................33
WORKING WITH THE DB SCHEDULER ...................................................34
7.1
7.2
7.3
8.
ONMODE UTILITY .....................................................................................28
ONINIT UTILITY.........................................................................................29
ONSTAT UTILITY .......................................................................................29
ONPARAMS UTILITY ..................................................................................30
WORKING WITH THE ADMINISTRATION API..........................................30
6.1
6.2
6.3
7.
ACCESSING OAT ....................................................................................11
HEALTH CENTER AND THE LOGS...............................................................13
SPACE ADMINISTRATION AND SERVER ADMINISTRATION ..............................16
OTHER OAT FEATURES...........................................................................19
WORKING WITH THE COMMAND LINE UTILITIES..................................28
5.1
5.2
5.3
5.4
6.
INTRODUCTION TO DB-ACCESS ..................................................................5
STARTING AN INFORMIX INSTANCE ..............................................................5
CREATING THE DEMONSTRATION DATABASE ................................................6
PUTTING THE DB-ACCESS UTILITY TO WORK ...............................................8
AUTO UPDATE STATISTICS EVALUATION WITH THE SCHEDULER ..................34
USING THE OAT SCHEDULER INTERFACE ..................................................36
AUTO UPDATE STATISTICS CONFIGURATION ..............................................39
USING DATA COMPRESSION...................................................................41
8.1
8.2
ENABLING COMPRESSION USING THE ADMIN API........................................41
ENABLING COMPRESSION USING OAT......................................................44
2
1.
Introduction
In this lab, you will learn the basics of administering an Informix instance using
some of the command line utilities as well as the new OpenAdmin Tool (OAT).
OAT for Informix is a PHP-based Web browser administration tool that provides
the ability to administer multiple database server instances from a single location.
Exciting features in the OpenAdmin Tool include:
Health Center: Analyze the current state of the server with detailed statistics and
alert information.
Logs: View previous administration commands, recent messages from the online
log, and the latest backup and recovery information from the ON-Bar Activity log.
Task Scheduler: View and update Scheduler tasks to be run at the current
server.
Space Administration: Manage server space for your system with tools to
administer dbspaces, chunks, checkpoints, and the physical and logical logs.
Server Administration: Configure and validate your servers, as well as
administer high availability clusters (MACH clusters).
Performance Analysis: Get high quality and detailed performance statistics
about your databases with the SQL exploration of each table, statement, or
transaction. Query drill down and session exploration provide detailed
information about every aspect of transactions and statement types. View over
20 system performance reports, such as memory pools, the five slowest
transactions, and server memory usage.
SQL Toolbox: Explore your databases, view table statistics, browser schemas,
run queries, and view query plans.
2.
Suggested Reading
IBM Informix Administrator’s Guide
This publication describes concepts and procedures for configuring,
administering, and using Informix.
www.ibm.com/support/docview.wss?uid=swg27010058
3
IBM Informix Administrator’s Reference
This publication provides reference material for Informix Administrators. It
contains comprehensive descriptions of configuration parameters, the systemmonitoring interface (SMI) tables in the sysmaster database, the syntax of
database server utilities such as onmode and onstat.
www.ibm.com/support/docview.wss?uid=swg27010058
IBM Informix DB-Access User’s Guide
This publication describes how to use the DB–Access utility to access, modify,
and retrieve information from Informix database servers.
www.ibm.com/support/docview.wss?uid=swg27010058
OpenAdmin Tool website
This website has comprehensive information about OpenAdmin Tool. It also
includes demos and links to download the latest release
www.openadmintool.org
OpenAdmin Tool tutorial series on developerWorks
A collection of articles that show you how to administer and manage Informix
databases and instances using OAT, as well as migrating to OAT from Informix
ISA.
www.ibm.com/developerworks/views/data/libraryview.jsp?search_by=oat%
20series
Build plug-ins for IBM's OpenAdmin Tool for INFORMIX
This article provides a step-by-step guide to creating plug-ins for IBM's
OpenAdmin Tool for Informix.
www.ibm.com/developerworks/data/library/techarticle/dm0808vonbargen/index.html
4
3.
Invoking and Using the DB-Access Utility
3.1
Introduction to DB-Access
DB–Access provides a user interface for entering, executing, and
debugging Structured Query Language (SQL) statements and Stored
Procedure Language (SPL) routines.
You can use DB–Access for the following aspects of database
processing:
¾ Issuing ad hoc queries and other SQL statements.
¾ Connecting to one or more databases, transferring data
between the database and external text files, and displaying
information about a database.
¾ Displaying system catalog tables and the Information Schema,
which are explained in the IBM Informix Guide to SQL:
Reference.
3.2
Starting an Informix instance
¾ Login into the VMWare image as the informix user.
Note: Refer to the document entitled “VMware Basics” for
information about how to start and login to the virtual machine
Image.
¾ Right-click on the desktop area and select Open Terminal.
5
¾ Change to the “scripts” directory.
cd /home/informix/scripts
¾ Set the environment for the database server by typing the
following command in the command terminal.
. setDemo
¾ Enter the following command in the command terminal to see if
the Informix Server is up and running.
onstat o If the Informix server is NOT running then you will see
the following message.
Shared memory not initialized for INFORMIXSERVER
‘demo_on’
o If the Informix Server is running then you will see a
message similar to the following.
IBM Informix Dynamic Server Version 11.5.UC1W1DE -On-Line – Up 00:00:15 – 38204 Kbytes
¾ Enter the following command to start the server ONLY if it is
NOT already running.
oninit
3.3
Creating the demonstration database
The DB–Access utility includes a demonstration database called
stores_demo. This database illustrates a relational schema with
information about a fictitious wholesale sporting-goods distributor.
SQL statements in this lab are based on the stores_demo database.
The syntax of the command to create a demonstration database is:
dbaccessdemo dbname –log –dbspace dbspace_name
6
where:
dbname
Name of the stores demo database.
-log
Requests transaction logging for the demonstration
database.
-dbspace
Requests a particular dbspace location for the
demonstration database.
dbspace_name Houses the demonstration database. If you do not
specify a dbspace name, by default, the data for the
database is put in the root dbspace.
¾ Using the same terminal window opened in the previous
section, enter the following command to create a demonstration
database called “stores”.
dbaccessdemo stores –log
You will see the messages similar to the ones shown in the
following screen shot.
¾ You will be prompted to enter Y or N to a question about
copying example scripts. Enter “N” and press Enter.
7
3.4
Putting the DB-Access utility to work
In this exercise, you will practice invoking the DB-Access utility and
use it for simple tasks such as connecting to the STORES database,
looking at a table definition, and running a simple query against the
table.
¾ Enter the following command in the command window to invoke
DB-Access and connect to STORES database.
dbaccess stores
¾ Using the interactive menus at the top of the window, determine
the name of the dbspace that the STORES database was
created in:
Select Database -> Info -> dBspace
Information similar to the following should be displayed:
8
¾ Go back to the main menu by selecting Exit twice from the
interactive menu at the top.
¾ To look at the list of tables in the database:
Select Table -> Info
¾ To look at the columns in the ORDERS table:
Select the ORDERS table from the list and press Enter. Select
Columns option in the top menu and press Enter.
Information similar to the following should be displayed:
¾ Try running a simple query against the table to view data. Go
back to the main menu and select:
Query-language > New
¾ Type the following SELECT statement in the open area where
you see the cursor:
select * from orders;
9
¾ Press the ESC key when you are finished typing the statement.
This switches from editing mode to interactive mode.
¾ Select Run from the top menu options and press Enter.
The rows in the table should now be displayed on the screen,
one at a time. Select Next from the top menu to see the next
row.
10
4.
Administering Informix Servers with OAT
OpenAdmin Tool is a valuable tool for viewing information about and
administering the database servers in your environment.
In this section, you will explore the various features available in OAT.
In reality, you probably do not need instructions about how to
administer servers with OAT because the OAT GUI is very user
friendly and self-explanatory. Feel free to experiment with the tool.
These exercises are designed to highlight a few tasks that can be
done with OAT.
4.1
Accessing OAT
The OpenAdmin tool can be accessed through a web browser in your
environment.
¾ Double-click on the OpenAdmin Tool for IDS icon on the
desktop.
11
¾ This will launch the Firefox web browser with OAT login page.
¾ On the login page, click the Get Servers button to get a list of
available servers.
This action lists the available severs (connections).
¾ Select “demo_on” from the list of available connections.
¾ Selecting a connection displays the server details on the right
side along with the login information. Click the Login button.
¾ If successful, you are taken to the OpenAdmin Tool Home
Page, as shown in the following screen shot.
12
4.2
Health Center and the Logs
Explore the Health Center menu and the Logs menu.
4.2.1
Displaying the list of alerts for the server
¾ Click Alerts under the Health Center menu on the left side.
This will display any alerts in the system. As you notice, Alerts
are color-coded by severity.
13
4.2.2
Using the Dashboard
¾ Clicking the Dashboard option displays the memory usage
information and the number of transactions information. (Note:
You may need to click the Refresh button in order to display the
graphs)
¾ This information is collected once every 10 seconds by default.
You can change the refresh rate by dragging the pointer on the
Refresh Rate bar to desirable location. The Refresh Rate bar is
at the top left corner of the window.
14
¾ Drag the pointer to all the way to the right. This changes the
refresh rate to every 60 seconds.
¾ Now click the Space tab to display dbspaces and lock
information.
4.2.3
Viewing the Admin Command Log
¾ Click the Admin Command option under the Logs menu to
display the admin commands log. This log contains the return
messages from invocations of the ADMIN() or TASK()
administrative functions that were recently executed.
15
4.2.4
Viewing the messages in the Online Log
¾ Click the Online Messages option under the Logs menu to
display the messages in the online log. Messages are displayed
in ascending order by date.
4.3
Space administration and server administration
The Space Administration menu options allow you to view / edit
dbspaces, chunks, and recovery logs.
The Sever Administration menu options allow you to administer MACH
clusters, server configuration, user privileges, virtual processors, and
auto update statistics.
You will now explore some of these options.
4.3.1
Displaying dbspace information
¾ Click the DBSpaces option under the Space Administration
menu to display pie charts of the dbspaces defined. The textual
representation of the same information is also shown below the
graphs.
16
¾ Click the first dbspace in the table (i.e. rootdbs), to display
detailed information about the space utilization of this dbspace.
17
4.3.2
Adding space to a dbspace
Add a chunk called online_root02 of size 50M to rootdbs
Click the Admin tab while you are in the rootdbs detailed view.
¾ On the Admin page, enter the following details under Add
Space to [rootdbs]
Path:
/data/IBM/informix/demo/server/online_root02
Offset:
0 (default)
Size:
50 M
File Creation: Create File if None Exists (default)
¾ Click the Add Chunk button.
This will add a chunk of size 50M to the rootdbs dbspace. You will
also notice a message at the top of the window that says a new chunk
has been added to the dbspace.
4.3.3
Displaying chunk I/O statistics
¾ Click the Chunks option under the Space Administration
menu and then click the Chunk IO tab option on the Chunks
page.
¾ This will display the chunk I/O statistics of all the chunks in the
dbspaces.
18
4.4
Other OAT Features
OAT is a great tool for performance monitoring and troubleshooting.
The Performance Analysis menu has various options that allow you
to control SQL statement tracing, obtain detailed query information,
generate system reports, and examine what all the connections
(sessions) are doing at a given point in time.
4.4.1
Displaying and changing the server configuration
¾ Click the Configuration option under the Server
Administration menu. By default, this displays all the server
configuration parameters that are defined in the ONCONFIG
file.
19
¾ Select the Show Dynamic Only option from the drop down list
at the top of the window to display only dynamic parameters.
Changes to dynamic parameters take effect right away; they do
not require an instance re-start.
20
¾ Select the Show Recommendations option from the drop
down list at the top of the window to display the parameters
with some recommendations from OAT.
¾ As you notice, all parameters with recommendations are
highlighted in yellow.
¾ Change the EXPLAIN_STAT parameter value to the recommended
value. Click the EXPLAIN_STAT parameter from the list.
¾ The recommended value is 1 (ON). It is currently set to 0 (OFF).
¾ Change the value to 1 and select the Change in Memory and File
Save Option. Click the Save button. Saving the change to memory
and the ONCONFIG file allows you to use this option next time the
instance is started. If you didn’t save it to the ONCONFIG file, the
server will use whatever the existing value in the file is after a
restart.
21
4.4.2
SQL Tracing administration
OAT is a great tool for performance monitoring and troubleshooting.
The Performance Analysis menu has various options that allow you
to control SQL statement tracing, obtain detailed query information,
generate system reports, and examine what all the connections
(sessions) are doing at a given point in time.
¾ Click the SQL Explorer menu option. This displays the SQL
Tracing Options. By default, SQL Tracing is turned off.
¾ Click the “Admin” button on the dialog
¾ Modify the tracing parameters as seen in the following
screenshot and the click Save.
22
4.4.3
Query drill down
¾ Click the SQL Explorer menu option again, after enabling SQL
Tracing. By default this option displays the SQL Tracing
summary information by statement type (if you don’t see any
statements there, wait a little and click SQL Explorer again, or
use DB-Access to issue a few select statements)
¾ Click the SQL tab to display the SQL statements sorted in
descending order by the number of times they have been
executed.
23
¾ Click on the magnifying glass next to the first statement to get
more details:
¾ Click on any of the magnifying glasses again to get even more
details.
Note: The statements in your environment may be different than the
ones in the screen shot above.
4.4.4
Generating system reports
¾ Click the System Reports menu option to go to the System
Reports main page. This page displays a variety of reports that
you can generate individually. You can also build a report from
many smaller reports.
24
¾ Create a sample report with the Server Configuration,
Computer Resource Information, and Computer OS
Information.
¾ Select the check box next to these items and click the Create
Report button.
¾ OAT creates a consolidated report and displays it in a separate
browser window.
25
4.4.5
Display database information
¾ Click the Databases menu option under the SQL Tool Box
menu to display the Databases page on the right side.
¾ This page displays a list of the databases on the selected
database server, as well as a chart of the space used by each
database. Click the Graph or Data buttons to change the view
of database space usage.
¾ Click the STORES database to connect to the database and
see the list of tables in the database.
¾ You have to provide a username and password to connect to
the database. Use user “informix” and password “informix”.
26
4.4.6
SQL Editor
¾ Click the SQL Editor Menu option to bring up the SQL Editor
Page. The SQL editor page is used to run ad-hoc SQL queries.
¾ Enter the following query in the query editor and click the Run
Query button.
select * from state order by code;
27
¾ The query results will be displayed on the screen.
5.
Working with the Command Line Utilities
In this exercise, you will get to use important command line utilities
with Informix.
Enter each of the commands described in this section with the options
that are discussed.
5.1
onmode utility
The onmode utility is used to shut down an operating instance or to
change the operating mode of the instance.
It is also used to instantiate and manage High Availability Data
Replication (HDR) and related functionality, as well as manage shared
memory changes.
5.1.1
onmode –j
¾ onmode –j changes the instance mode to single-user. Only
DBSA / Informix can connect to the instance in this mode. This
mode is very useful when you need to perform administrative
tasks on the instance that require restricted access.
28
5.1.2
onmode -m
¾ onmode –m brings the database server online from quiescent
mode or from administration mode.
5.1.3
onmode –k
¾ onmode –k takes the server off-line.
5.2
oninit utility
The oninit utility is used to initialize and/or start the instance from an
offline mode. It can also be used to switch operating modes while the
instance is operational.
5.2.1
oninit
¾ Enter oninit command to bring the instance online.
5.2.2
oninit -version
¾ The oninit -version command displays the version of the
Informix server being used.
5.3
onstat utility
The onstat utility is one of the most useful administration utilities in
Informix. It reads shared-memory structures and provides statistics
about the database server at the time that the command is executed.
You can combine multiple onstat option flags in a single command.
The contents of shared memory might change as the onstat output
displays. The onstat utility does not place any locks on shared
memory, so running the utility does not affect performance.
5.3.1
onstat –l
The onstat -l command displays information about the physical
and logical log buffers and log files.
As you see, 5 logical logs were created by default. In the next
exercise, you will add an additional logical log and use onstat -l
again to check the number of log files.
29
BONUS: Using the Informix documentation or another source, try to
determine what each of the options in the Flags column in output
refers to.
5.4
onparams utility
The onparams utility is used to add and/or remove logical logs, resize
and/or relocate the log files, as well as create and/or change buffer
pool settings.
5.4.1
Adding a logical log file with onparams
Enter the following onparams command to add a logical log file with a
size of 2000 KB to the rootdbs dbspace:
onparams –a –d rootdbs –s 2000
Enter the onstat -l command again to check the number of logical
log files. There should now be 6 logical log files.
6.
Working with the Administration API
6.1
Remote administration using SQL commands
¾ The SQL Administration API enables you to perform remote
administration using SQL for tasks such as managing
dbspaces, managing the server configuration, running routine
jobs, and performing system validation.
¾ Use the EXECUTE FUNCTION statement to invoke the built-in
ADMIN( ) or TASK( ) functions to accomplish administrative
tasks, which are equivalent to executing various administrative
command line utilities.
¾ When using the ADMIN( ) or TASK( ) functions, items in the
argument list specify the utility and its equivalent command-line
arguments.
30
6.2
Using the ADMIN() function
The ADMIN() function returns an integer indicating the success or
failure of the command that was executed. It also inserts a row into the
sysadmin:command_history table which contains a list of all
commands that the Administration API ran. The table also contains the
results of the commands.
If > 0, the command succeeded and a row was inserted into
command_history table.
If = 0, the command succeeded, but a row could not be inserted into
into the command_history table.
If < 0, the command failed, but a row was inserted into the
command_history table.
You can use the integer return code to query the command_history
table to review the results of that function invocation.
6.2.1
Checking Extents with the ADMIN() function
The following command instructs the database server to check the
extents of a dbspace. Note that if no dbspace is specified, all
dbspaces are checked. This particular function invocation would be
equivalent to running the oncheck command line utility.
EXECUTE FUNCTION admin('check extents').
Execute this function in the DB-Access utility and check the
command_history table for the output of the function.
¾ Start the dbaccess utility and connect to the sysadmin
database from the command window by entering the following
command:
dbaccess sysadmin
¾ Select Query-Language > New and enter the following
EXECUTE FUNCTION statement:
EXECUTE FUNCTION admin('check extents');
31
¾ Press the ESC key to exit from editing mode and select the
Run option to run the statement and execute the function.
¾ Note down the expression number returned after the execution
of the function. You will use that number in the following step.
¾ Select Query-Language > New and enter the following SQL
statement.
select * from command_history where
cmd_number = expression_number;
NOTE: Replace expression_number in the SQL statement
above with the actual number returned in the previous step.
¾ Press the ESC key to exit out of editing mode and select the
Run option to run the statement.
¾ If the SQL is executed successfully, you will see the command
return status has a value of “0” and the command return
messages that say the validation was successfully completed.
¾ Exit from the DB-Access utility.
32
6.3
Using the TASK() function
The TASK() function returns a string indicating the status of command
just entered.
6.3.1
Dropping a logical log using TASK() function
The following statement instructs the database server to drop the
logical log file that can be uniquely identified with the “log_number”.
EXECUTE FUNCTION task(“drop log”, log_number)
Use the DB-Access utility to drop the additional logical log that was
created in the previous section.
¾ Start the DB-Access utility and connect to the sysadmin
database in the command window by entering the following
command:
dbaccess sysadmin
¾ Select Query-Language > New and enter the following
EXECUTE FUNCTION statement.
EXECUTE FUNCTION task(“drop log”, 6);
NOTE: In the previous command, we assume that the log
number of the log that was created in the previous exercise is
6. If in doubt, try using the onmonitor utility to check the log
number of the newly created logical log.
¾ Press the ESC key to exit out of editing mode and select the
Run option to run the statement.
¾ If the command is executed successfully, you will see a
message that says that the log file was successfully dropped.
¾ Exit from the DB-Access utility.
33
7.
Working with the DB Scheduler
The Scheduler manages and executes scheduled maintenance,
monitoring, and administration tasks. It also enables you to monitor
activities (for example, space management or automatically backing
up any new log data at timed intervals since the last log backup) and
create corrective actions that run automatically.
The Scheduler manages:
¾ Tasks, which provide the means for running a specific job at a
specific time or interval.
¾ Sensors, which collect and save information.
¾ Startup tasks, which run only once when the database server
starts.
¾ Startup sensors, which run only once when the database starts.
7.1
Auto Update Statistics Evaluation with the
Scheduler
Auto Update Statistics is a predefined task in Informix 11.5 that
monitors database activity and determines the necessary update
statistics commands based on the current automatic update statistics
policies.
There is an entry in the ph_task table that tells the Scheduler what
task to perform and the time when it needs to perform this task.
Query the ph_task table to examine information about the Auto
Update Statistics task.
¾ Start the DB-Access utility and connect to the sysadmin
database in the command window by entering the following
command.
dbaccess sysadmin
¾ Select Query-Language > New and enter the following SQL
statement.
select * from ph_task where tk_name like 'Auto Update
Statistics Evaluation%';
34
¾ Press the ESC key to exit out of editing mode and select the
Run option to run the statement.
¾ Running the SQL statement above displays the information
stored in the ph_task table for a task with the name Auto
Update Statistics Evaluation.
¾ Select the Next option to see more information about the
schedule.
35
7.2
Using the OAT Scheduler Interface
The Task Scheduler is the interface created to work with the Informix
DB Scheduler. It displays the scheduled task details and information
about previously executed tasks. It also allows you to create new
tasks and schedule them to run at a later time, as well as make
changes to existing tasks.
7.2.1
Displaying information about the tasks
¾ From the OAT window, click the Scheduler option under the
Task Scheduler menu to display information about the tasks
that are scheduled to run at a later time.
¾ The information includes task name, start time, stop time, run
frequency, days when the task is enabled, and information
about whether the task is enabled or disabled.
¾ Click Auto Update Statistics Evaluation, to display the details
about the AUS task.
36
7.2.2
Making changes to tasks
Try disabling the execution of the Auto Update Statistics Evaluation
task on Saturdays and Sundays.
¾ While on the Auto Update Statistics Evaluation task details
page, select the Disabled option from the drop down menu for
Saturday and Sunday and click the Save button.
¾ Click the Scheduler option under the Task Scheduler menu to
go back to the tasks summary page.
¾ You should notice that the task execution is now disabled for
Saturdays and Sundays.
37
7.2.3
Checking the task execution statistics
¾ Click the Task Runtimes option under the Task Scheduler
menu to display the task execution statistics such as the
number of executions, average time it took to run the task, total
time, last run time and last execution status.
38
7.3
Auto Update Statistics Configuration
The Auto Update Statistics option under the Server Administration
menu allows you to configure this feature in OAT.
¾ Click the Auto Update Statistics option. This will display the
results of the Auto Update Stats Evaluation and Results
tasks by database.
¾ Click the Info tab at the top of the page. This displays the
Evaluation and Refresh schedule information as well as the
parameters that are used by these two tasks.
39
¾ Click the Alerts tab to display AUS Alerts.
¾ Click the List tab to display the list of commands that will be
executed by the Refresh task the next time it runs.
40
¾ Click the Config tab to display and change the values of AUS
configuration parameters. Just for fun, change the
AUS_CHANGE parameter value to “20” and click Save.
¾ This brings you back to the AUS main page.
8.
Using Data Compression
In this section, you will use the new data compression feature in
Informix, introduced in 11.50.xC4. All compression features are
controlled through the SQL Admin API.
There are four steps when compressing a table:
¾ Enable compression
¾ Compress the table
¾ Repack the table (moving the data to the front of the dbspace)
¾ Shrink (freeing the unused space for use by other tables in that
dbspace)
8.1
Enabling compression using the Admin API
First, create a sample table. It has to have at least 2000 rows and
must not contain out-of-row data (e.g. blobs).
41
¾ Create and load a sample table “comptab” with data by running
the following script:
dbaccess stores
/home/informix/scripts/sqls/createComptab.sql
You can use the oncheck –pt command to get the number of data
pages used by the table. Run this command and fill in the first row of
the following table with the results:
oncheck -pt stores:informix.comptab | grep
"data pages"
Number of data pages before
compression
Number of data pages after compression
Compression rate in percent (%)
# data pages after compression
* 100
# data pages before compression)
You will now compress the table stores:comptab
¾ In the same terminal window, start DB-Access and connect to
the stores database:
dbaccess stores ¾ Enable compression:
execute function sysadmin:task(“enable
compression”);
42
¾ Compress, repack and shrink the table:
set isolation to repeatable read;
execute function sysadmin:task(“table compress
repack shrink”, “comptab”);
NOTE: Before you executed the previous COMPRESSREPACK-SHRINK command, you set the isolation level to
Repeatable Read (RR). The reason for this is that if you are
doing an online repack or online uncompress at an isolation
level below RR, there is a possibility of seeing phantom rows in
table scans. To avoid this, you can use the RR isolation level.
This does not impact compress, shrink, repack_offline, or
uncompress_offline, only repack and uncompress (online
variants).
NOTE: You can also use the compress, repack and shrink
commands separately.
¾ Use the oncheck –pt command again to fill in the second
row of the previous table. Compute the compression rate by
using the formula shown in the table.
¾ You can also experiment and uncompress the table again using
the following command:
execute function
sysadmin:task(“table uncompress”, “comptab”)
¾ Optionally, use the estimate_compression command to
compare the output to what you computed in your table (how
does it relate?):
execute function sysadmin:task(“table
estimate_compression”, “comptab”)
43
When compressing a larger table, you can use the “onstat –g dsk”
command to monitor the progress, where the OP column will show
one the following flags, depending on the compression action being
performed:
(1 = create_dictionary, 2 = compress, 4 = repack, 8 = repack_offline,
16 = shrink, 32 = uncompress, 64 = uncompress_offline,
128=estimate_compression, 256 = purge_dictionary)
8.2
Enabling Compression Using OAT
OAT is also capable of enabling data compression. Try compressing
or uncompressing a table using the OAT interface.
¾ Under the Space Administration menu item on the left, you will
find an option called Compression:
¾ Click on the STORES database. If a table is compressed, you
will see a checkmark in the Compress column.
Examine the different statistics available for the tables, such as
the amount of used pages, number of total pages, number of
rows, and whether the table is compressed.
44
Click the small icon ( ) next to the USAGE column for the
comptab table. (open a terminal and type dbaccess stores
/home/informix/scripts/sqls/createComptab.sql if
the comptab table does not exist yet)
¾ From here, you can execute all the (un)compress, repack and
shrink options:
45
¾ If the table is compressed, select the Uncompress option and
click OK to uncompress the table. Alternatively, try compressing
the table if it is uncompressed. From the resulting window, click
Go To Task List to see the result of that action. In the screen
shot below, the uncompress operation was successful.
¾ Click on the Databases tab again to check if the number of
pages that are used by the table has changed as a result of the
(un)compress operation:
46
This concludes the lab. If you have completed all the exercises and have extra
time, feel free to continue exploring with the utilities/functionality described here.
Otherwise, you can close any open windows.
47
© Copyright IBM Corporation 2010
All Rights Reserved.
IBM Canada
8200 Warden Avenue
Markham, ON
L6G 1C7
Canada
Printed in Canada
01/2010
Information concerning non-IBM products was obtained from the
suppliers of those products, their published announcements or
other publicly available sources. IBM has not tested those products
and cannot confirm the accuracy of performance, compatibility or
any other claims related to non-IBM products. Questions on the
capabilities of non-IBM products should be addressed to the
suppliers of those products.
IBM, IBM (logo), and Informix are trademarks or registered
trademarks of International Business Machines Corporation in the
United States, other countries, or both.
Linux is a trademark of Linus Torvalds in the United States, other
countries, or both
UNIX is a registered trademark of The Open Group in the United
States, other countries, or both
The information in this publication is provided AS IS without
warranty. Such information was obtained from publicly available
sources, is current as of January 2010, and is subject to change.
Any performance data included in the paper was obtained in the
specific operating environment and is provided as an illustration.
Performance in other operating environments may vary. More
specific information about the capabilities of products described
should be obtained from the suppliers of those products.
Windows is a trademark of Microsoft Corporation in the United
States, other countries, or both.
Other company, product, or service names may be trademarks or
service marks of others.
References in this publication to IBM products or services do not
imply that IBM intends to make them available in all countries in
which IBM operates. The following paragraph does not apply to the
United Kingdom or any other country where such provisions are
inconsistent with local law:
INTERNATIONAL BUSINESS MACHINES CORPORATION
PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY
OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING,
BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NONINFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A
PARTICULAR PURPOSE.
Some states do not allow disclaimer of express or implied
warranties in certain transactions, therefore, this statement may not
apply to you.
This information could include technical inaccuracies or
typographical errors. Changes are periodically made to the
information herein; these changes will be incorporated in new
editions of the publication. IBM may make improvements and/or
changes in the product(s) and/or the program(s) described in this
publication at any time without notice.
Any performance data contained herein was determined in a
controlled environment. Therefore, the results obtained in other
operating environments may vary significantly. Some
measurements may have been made on development-level
systems and there is no guarantee that these measurements will be
the same on generally available systems. Furthermore, some
measurement may have been estimated through extrapolation.
Actual results may vary. Users of this document should verify the
applicable data for their specific environment.
48