IBM Software Group Presentation Template
Download
Report
Transcript IBM Software Group Presentation Template
®
Information Management
IBM Informix May 2006 Chat with the Lab
IDS 10: Upgrading and New Features
IBM Software Group
© 2005 IBM Corporation
Information Management
Informix Dynamic Server 10.0
Performance. Reliability. Scalability.
Topics
New features in 10.00.XC2 through XC5
BAAN enhancements
Migrating to v10
Q&A
2
Information Management
Informix Dynamic Server 10.0
Performance. Reliability. Scalability.
Migrating to v10
Why Upgrade?
Migration matrix
Pre-Migration
Install and configuration
Validate your target environment
Perform conversion
3
Information Management
Why upgrade? IDS has been busy with v9/v10
detached
indexes
raw
tables
buffer
manager
Fuzzy
ckpts
HDR/ER
Config page size
Col Level
Encryption
large
Chunk
& files
dynamic
logging
dynamic
locking
9.2
update
statistics
ER Resync
optimizer
directives
onarchive
gone
9.3
btree
scanners
PIT-TLR
restartable
fast recovery
9.4
Shmem
> 4 gig
10.0
IDS Versions – Key features by release
4
Information Management
Migrating from prior versions of IDS
5
Information Management
Upgrading to v10 - Conversion Steps
Five easy steps to remember for data migration:
1. Get the source server ready by following pre-migration steps
2. Install the target database server and copy over relevant
$INFORMIXDIR/etc configuration from source to target
3. Validate your environment settings are pointing at target
4. CONVERT by starting target server
5. Validate data integrity and take level 0 backup
CONVERSION COMPLETE!
6
Information Management
System Requirements
Requirements for a new Installation of IDS v10
500 MB of disk space
256 MB of RAM
Plan for:
Adequate disk space to house schema
Adequate disk space to house temporary tables
Adequate CPU’s for application needs
Plan for about 20% disk space overhead
** Lookup Installation guide for details
IDS provides industrial-strength capabilities with a relatively small footprint,
ensuring the best performance with less hardware.
7
Information Management
Space Required for Upgrade
Root Chunk (Chunk 0) should have at least 10% free.
3000 Free pages of Logical Log Space to rebuild SysMaster
2000 KB of Free Space per Database is need for each DB Space
Partition Headers should not be full
Use oncheck –me to compress extents
Determine how much space is needed by running:
DATABASE sysmaster;
SELECT partdbsnum(partnum) DBSpace_Num,
TRUNC(COUNT(*) * 2000) Free_Space_Needed
FROM sysdatabases
GROUP BY 1, ORDER BY 1;
SELECT dbsnum DBSpace_Num, sum(nfree) Spc_Avail
FROM syschunck
GROUP BY 1, ORDER BY 1;
8
Information Management
IDS 10.00 Migration: Checklist
SQL query plans for all regularly used queries (especially
complex queries) using SET EXPLAIN ON.
dbschema -d -hd for all critical tables. The output will have
distribution information.
oncheck -pr output that dumps all the root reserved pages
Copy of onconfig configuration file. Note: oncheck –pr will not
dump all the configuration parameters so it’s better to have a
copy of onconfig file.
A list of all the environment variables that are set using ‘env’ unix
command.
9
Information Management
Ensure you take care of …
Remove Outstanding In-Place Alters
Close All Transactions
Verify the integrity of the data with oncheck
Flush ER Queues with any transactions, if applicable
Active HDR servers – disable it, if applicable
Level-0 archive/backup before migration
UNIX/Linux – may require Kernel Parameters to be modified
Verify source server is offline
**
Recommended documented steps to shutdown current server properly:
Run onmode -sy, onmode -l, onmode -c and then onmode -kuy.
10
Information Management
v10 Installer – easier install with GUI/Silent support
Installer can be invoked by:
1.
installserver [preferred] Uses bundled
JVM, if JAVA not found in the PATH
2.
Installserver –javahome [for users that
want to use a local java]
3.
Java –cp IIF.jar run [for users who want
to invoke directly – advanced]
4.
Installserver –legacy [extracts a legacy
distribution]
Additionally LINUX also packages the
following RPMs [since 9.40.UC5]
Product.rpm
GLS.rpm
Message.rpm
For enhanced usability, a wrapper script is
provided that invokes the RPMs in the
correct sequence
11
Information Management
Copy & customize configuration to target
Unix or Linux
Windows
$INFORMIXDIR/etc/$ONCONFIG
%INFORMIXDIR%\etc\%ONCONFIG%
$INFORMIXDIR/etc/onconfig.std
%INFORMIXDIR%\etc\onconfig.std
$INFORMIXDIR/etc/oncfg*
%INFORMIXDIR%\etc\oncfg*
$INFORMIXDIR/etc/sm_versions
%INFORMIXDIR%\etc\sm_versions
$INFORMIXDIR/aaodir/adtcfg
%INFORMIXDIR%\aaodir\adtcfg.*
$INFORMIXDIR/dbssodir/adtmasks
%INFORMIXDIR%\dbssodir\adtmasks.*
$INFORMIXDIR/etc/sqlhosts
%INFORMIXDIR%\etc\ixbar.servernum
$INFORMIXDIR/etc/tctermcap
$INFORMIXDIR/etc/termcap
$INFORMIXDIR/etc/ixbar.servernum
12
Information Management
Set Environment variables of Target
Verify that the following environment variables are set
to the correct values to migrate to the Target server
INFORMIXSERVER
ONCONFIG
PATH
INFORMIXSQLHOSTS
13
Information Management
Perform Conversion
Start Dynamic Server 10.00
** No “oninit –ivy” please - will initialize disk!
Monitor the online message log
Wait for “Conversion Completed Successfully”
status
** Do not bounce engine before conversion is complete
** Install and configure any Datablade Modules prior to
conversion
14
Information Management
Post Migration
Update Statistics (Low to drop, High on
sysmaster, normal on your databases)
Verify the Integrity of the Data [oncheck;
archecker]
For ON-Bar, Rename the sm_versions.std File
Make an Initial Backup of Dynamic Server
10.00
Tune Dynamic Server 10.00 for Performance
Enable HDR and ER, if applicable
** Rerun the pre-migration checklist described in premigration for before/after picture comparison
15
Information Management
IDS 10.00 Migration: Using Replication
Using Enterprise Replication to assist
migration to 10.00
Pros:
Allows mix of 7.3 and 10.00
versions
New transactions will queue
up until the migrated server is
available
Cons:
May get complicated to
setup/administer
Table level replication only
16
Information Management
Application Migration - Reserved Words
New for 9.x
New for 9.4
CACHE
COLLATION
ONLINE
COSTFUNC
CROSS
OPTCOMPIND
ITEM
FULL
PARTITION
SELCONST
INSTEAD
PASSWORD
INNER
RESTART
SAVE
JOIN
RIGHT
TABLE
LEFT
TEMPLATE
LOCKS
TEST
RETAIN
New for v10
TYPEID
STANDARD
TYPENAME
AVOID_EXECUTE
TYPEOF
USE_SUBQF
WAIT
AVOID_SUBQF
RAW
XADATASOURCE
XID
ACTIVE
DIRECTIVES
ENCRYPTION
HINT
INACTIVE
INLINE
INOUT
LOAD
17
Information Management
Application Migration and Client APIs
ESQL/C
Simply upgrade to the latest version of CSDK and run the application. The
new libraries will get loaded providing you enhanced functionality (like
encryption).
In order to get new functionality like PAM, applications need to be modified
ODBC/.NET/Java
Upgrade to a newer version of CSDK/JDBC driver and run the application
4GL (v 7.32.UC3 )
c4gl upgrades required applications to be recompiled/relinked. The version
7.32.UC3 removes this requirement. Simply upgrade to this version and run
your c4gl applications (supported 7.30.xc6 onwards) . P-code versions still
require recompilation.
In order to get new functionality like DYNAMIC ARRAY, applications need to
be changed
18
Information Management
Informix Dynamic Server 10.0
Performance. Reliability. Scalability.
Focus on OLTP and Embedded
Technology Enhancements over IDS v7
Improved performance
Higher Availability
Increased Scalability
Enhanced Security
Simplified Administration
Easier Application Development
19
Information Management
Improved Performance Highlights
8-15% Improvement over IDS 7.31
Fuzzy Checkpoints
Helps solve long checkpoint problem in 7
Shared Statement Cache
Statement cache is now global rather than per connection
New Buffer Management System
Combines IDS victim selection with DB2’s minimal I/O
B-Tree Scanner
Improves btree delete performance
Improved Replication Performance
Apply on Target 50% faster
Other Performance Improvements
Query optimizer improvements for special cases, up to 50% increase in
rollback speed, better hash join and aggregate performance, memory
allocation algorithms are much faster, hold cursors can now execute in
parallel
20
Information Management
High Availability Highlights
Restartable Fast Recovery
Fast recovery possible even if recovery crashes
Improved Rollback Performance
Up to 50% faster for long rollbacks
Dynamic Log Creation
Eliminates server hangs due to long transactions
Improved Enterprise Replication (ER)
Improved performance
Quick queue recovery
Large transaction support
ER / HDR Interoperability
HDR systems can participate in ER systems too.
Other Improvements
Collection support, multiple smart blob stable queues
(logged/unlogged)
21
Information Management
Increased Scalability Highlights
Big Chunks
Maximum chunk size raised to 4TB
Maximum instance size is 128 PetaBytes
All utilities now handle large files
New Buffer Manager
Combines IDS and DB2 strengths
Other improvements
Improved support more CPUs.
Reduced use of latches
Spin Lock for temporary partition improved
22
Information Management
Enhanced Security Highlights
Encrypted Network Communications
Communication between client/server and server/server
now encrypted
Uses cryptographic libraries from OpenSSL
Optionally encrypts ER traffic
Useful for long haul networks, traffic sent over internet
Port can be configured for just ER traffic
No need for trusted hosts
23
Information Management
Simplified Administration Highlights
Redirected Restore
Recovery on non-identical
systems
Full use of Tapes
Will write to end of tape
Rename Chunks
Rename during restore
New Unix Bundle Installer
No libraries in /usr/lib
Order of Install
Add Chunks when first
chunk is full
Now can add disk space if
chunk 0 is completely full
Onstat enhancements
Can now display
environment variables, list of all
prepared statements in a
session,
Explain enhancements
Explain turned on
dynamically
Log Management
Logs added/dropped online
Dynamic Lock Allocation
Locks table shrinks/grows
dynamically
24
Information Management
Application Development: Enhanced SQL Highlights
Support for Long Identifiers
Updated Unicode Support
Sequences
Triggers on Select and Views
Order by not in select list
ANSI SQL-99 Joins
Describe input
Unions in Sub-queries
Names for Return Values
Multiple OUT parameters
Improved support for long character strings
Multi-nationalization
25
Information Management
Application Development: Extensibility Highlights
Collections
Table Functions
Virtual Table interface
User Defined Data Types
High-Performance Programming Support
Built-in functions for handling complex data
26
Information Management
Application Development: Enhanced APIs
CSDK 2.90
JDBC Enhancements plus WAS integrations
OLE DB Enhancements
ODBC Enhancements
ESQL/C Enhancements
Native .Net Provider
27
Information Management
Informix Dynamic Server 10.0
Performance. Reliability. Scalability.
New Features in 10.00.XC2 through 10.00XC5
28
Information Management
New Features in 10.00.XC2
First release of the IDS Express Edition
Restricted functionality in the Express Edition:
Limited to 2 CPUs
Memory limited to 4 GB
High-Availability Data Replication not available
Enterprise Replication not available
Parallel operations not supported
Parallel queries, backup/restore, High Performance Loader
Available on Windows and Linux (AMD,Intel)
No 64 bit support
29
Information Management
New Features in 10.00.XC3
ANSI-joins in distributed queries perform better
Tables now joined in the remote database rather than joining tables locally
Transaction support for XA-compliant external data sources
Conforms to X/Open XA interface standards for prepare/commit/rollback
MQ DataBlade module
Presents MQ data as a relational table
Table is read/write
New DBCREATE_PERMISSION configuration parameter to restrict
the ability to create databases
If set only user Informix and the user specified can create databases
New secure default directory for the DUMPDIR configuration
parameter
Previously DUMPDIR defaulted to /tmp, now it defaults to
$INFORMIXDIR/tmp on UNIX and %INFORMIXDIR%\tmp on Windows
30
Information Management
New Features in 10.00.XC3 - Continued
Table-level restore for smart large object columns
TLR now supports Smart Large Objects for restore from level-0 archive
AES cipher support for network encryption
Network encryption now supports aes, aes128, aes192, and aes256 keys
New Enterprise Replication commands to show statistics information
The “cdr stats rqm” command shows info about the reliable queue manager
The “cdr stats recv” command shows info about the receiver parallelism and
latency statistics by source node
Client SDK included in Dynamic Server installation process
Install now lets you install CSDK ver 2.90.XC3 simultaneously with 10 or
separately
Enhanced Support for Retrieving Subsets of Query Results
New keyword FIRST only returns the first “N” rows of results
New keyword SKIP in projection list skips the first “N” rows of results
Keywords can be combined to skip N rows then return following first M rows
31
Information Management
New Features in 10.00.XC3 - Continued
Ordering subsets of query results in collection-derived tables
ORDER BY clause now supported in collection subqueries
Data is sorted before FIRST/SKIP is applied
J/Foundation upgrade to JRE 1.4.2
J/Foundation component now includes JRE 1.4.2
New default directory for ADTPATH configuration parameter
Previously ADTPATH defaulted to /tmp, now it defaults to $INFORMIXDIR/aaodir on
UNIX and %INFORMIXDIR%\aaodir on Windows
New UNSECURE_ONSTAT configuration parameter
UNSECURE_ONSTAT can be set to allow users other than DBA to use onstat
Set to 1 to allow anyone to use onstat
If missing or value other than 1 only allow DBA’s to use onstat
For more info see:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.s
qls.doc/sqls02.htm
32
Information Management
New Features in 10.00.XC4
TRUNCATE table support
This command allows all rows to be deleted from a table and its indexes
very quickly
Enterprise Replication direct synchronization
The new “cdr sync replicate” and “cdr sync replicateset” commands perform
synchronization between a reference server and one or more targets
These commands check consistency and optionally repair inconsistent rows
Enhanced support for IPv6
OS support for Internet protocol version 6 (IPv6) is automatically checked for
Secure local connections
The new SECURITY_LOCALCONNECTION config parameter makes
connections more secure
Set to 0 for no extra security
Set to 1 to verify that user running program matches database user
Set to 2 the same as 1 but will also source IP is really coming from the
indicated client program. Requires OS support for DOCTCP or IPCSTR
33
Information Management
New Features in 10.00.XC4 - Continued
Secure DataBlade module paths
The DB_LIBRARY_PATH config param defines paths which the database
can load datablades
Parallel backup and restore is more efficient
The BAR_SORT_DBS env variable improves backup/restore when scope is
not the whole system
Set to any value to enable to functionality
DB-Access stops a process after the first error
Invoked as “dbaccess –a”
Informix Interface for TSM supports HP-UX (Itanium)
Onbar can now be used with the Tivoli storage manager on HP-UX (Itanium)
New default value for IFX_EXTEND_ROLE configuration parameter
IFX_EXTEND_ROLE default changed from 0 to 1
1 means only those granted extend role can register external functions
such as in datablades
34
Information Management
New Features in 10.00.XC5
Demo server now uses TCP instead of shared memory connections
Allows demo database to be connected to remotely
Install of demo server now checks uniqueness of SERVERNUM
Checks to see if any other demo instance has same servernum
If silent install changes the port number it will log the change in its log
MQ datablade 2.0 now supported on Linux 32 bit, Linux pSeries 64 bit,
and Solaris 64 bit
JRE 1.4.2 upgrade for ISA and ISMP
Previous JRE has security hole
New Binary data type support - var binary and binary18 for Portal
Required by Websphere Portal
35
Information Management
Informix Dynamic Server 10.0
Performance. Reliability. Scalability.
BAAN Benchmark Performance Enhancements
36
Information Management
BAAN Benchmark Performance Features in
10.00.XC5
Improved internal latch performance
Avoids grabbing latches whenever possible
Don’t sequential scan user table on every prepare statement
Now keeps a cache of users with DBA privileges
Do not sort if only one row being returned
Improved ASF polling performance
Uses pollset OS interface (when available) rather than select() or poll()
Enable with FASTPOLL = 1 in config file
Not supported on AIX, DEC, and SGI
Avoid hash joins when using first rows optimizer directive
In this case nested loops will always be faster
May be able to avoid a sort this way
37
Information Management
BAAN Benchmark Performance Features in
10.00.XC5 - Continued
Improved memory management when using lots of memory (> 20GB)
Searching the memory cache for a best fit block can be slow
This search can be disable by setting VP_MEMORY_CACHE_KB to 0
Be sure (VP_MEMORY_CACHE_KB * num cpu vps) < (40% SHMTOTAL)
This feature can be changed dynamically with
“onmode -wm VP_MEMORY_CACHE_KB=<value>"
SINGLE_CPU_VP configuration performance improvement
Improved performance when searching bit maps
Uses IFX_SORT_POOLS
Now treats "col1>=X and col1 <= X" as "col1=X“
This reduced query time on some queries from >50 sec. to about 10 sec.
38
Information Management
BAAN Benchmark Performance Features in
10.00.XC5 - Continued
Start/Stop keys not set correctly for some query predicates
Composite indexes might perform slowly with range predicates
For instance, given an index defined on columns (a, b, c, d), and the filters
“a = 2 and b >=1 and b <= 7 and c >= 10 and c <= 20” previously we would
only use filters on columns (a,b). Now we use (a, b, c).
A side effect is that explain output has changed
In the above case the output would change from saying “Key-First Filters”
to saying “Index Key Filters”.
Improved performance when using many prepare statements
Previously if many prepared statements were open at commit time it would
take a long time to commit.
39
Information Management
40
Information Management
41