Upgrading to IDS version 10

capybarabowwowΛογισμικό & κατασκευή λογ/κού

30 Οκτ 2013 (πριν από 3 χρόνια και 9 μήνες)

189 εμφανίσεις

®


Information Management

© 2006 IBM Corporation

Upgrading to IDS version 10


Jerry Keesee, Director of the Informix Lab


Carlton Doe, Informix Technical Sales


Information Management

2


IDS v10


Why upgrade?


Migration to v10


Migration impact on existing applications


Customer Migration Use Case discussion


Q&A


Informix Dynamic Server 10.0

Focus on OLTP and Embedded

Performance. Reliability. Scalability.


Information Management

3

Technology Enhancements over IDS v7


Improved performance


Higher Availability


Increased Scalability


Enhanced Security


Simplified Administration


Easier Application Development


Informix Dynamic Server 10.0

Focus on OLTP and Embedded

Performance. Reliability. Scalability.


Information Management

4

Why upgrade?
IDS has been busy with v9/v10



IDS Versions


Key features by release

9.2

9.3

9.4

dynamic

locking

detached

idxs

update

statistics

dynamic

logging

onarchive

gone

raw

tables

buffer

manager

large

Chunk
& files

btree

scanners

HDR/ER

restartable

fast recovery

optimizer

directives

Config page size

Col Level

Encryption

ER Resync

PIT
-
TLR

Shmem

> 4 gig

10.0

Fuzzy

ckpts

Information Management

5

Performance Enhancements


IDS 10.0 provides the best performance of any IDS
engine ever produced!


15
-

20% Improvement over IDS 7.31


As evidenced by internal TPC
-
C benchmarks


As evidenced by our key customers and partners



Fuzzy Checkpoints


Shared Statement Cache


New Buffer Management System


B
-
Tree Scanner


Configurable Page Size


Memory for Non
-
PDQ Queries


External Optimizer Directives


Dynamic OPTCOMPIND






Information Management

6

High Availability


Restartable Fast Recovery


Improved Rollback Performance


Dynamic Log Creation


Improved Enterprise Replication (ER)


ER / HDR Interoperability


Table Level Restore


Online Index Drop and Rebuild


DRAUTO [out in v9; back in v10]


Resending Indexes in HDR


Alter of Replicated Tables


ER Templates


Online Resync of ER nodes

Information Management

7


Big Chunks


Improved Support for Large Objects


SHMEM >4G


Multiple Table Fragments in a Single DBspace





Scalable in all Dimensions

Deployment:

distributed across

1000s of sites

Huge data

volumes



Types of data
:

no limit to new data types

Transactions
:

1000’s of concurrent users

Information Management

8

Security Enhancements


Encrypted Network
Communication


Column Level Encryption


Secure environment check
before startup


Trigger Introspection


DataBlade/UDR registration
restriction


PAM Authentication


Denial of Service Attacks



Information Management

9

Embeddability
-

Simplified Administration


Redirected Restore


Full use of Tapes


Rename Chunks


New Unix Bundle Installer


No libraries in /usr/lib


Order of install


Add Chunks when first
chunk is full


Onstat enhancments


Explain enhancements




Information Management

10

Embeddability


Easier Application development

Enhanced SQL Support


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


Extensibility


Collections


Table Functions


Virtual Table interface


User Defined Data Types


High
-
Performance Programming
Support


Built
-
in functions for handling
complex data


Application Development
Enhancements


CSDK 2.90


.NET, OLE DB, ODBC & MS
Tooling


ESQL/C Enhancements


JDBC and WAS Integration



Information Management

11

Upgrading to v10
-

Migration facts


Migrating from prior versions of Informix
Dynamic Server


IDS 7.24, 7.31, 9.21, 9.3, 9.4 upgrade
directly to IDS v10


IDS 7.22, and IDS 7.20 require an interim
upgrade to IDS 7.31


IDS 9.14 requires an interim upgrade to IDS
9.30


Migrating from 32
-
bit to 64
-
bit uses the same
inline conversion process


All other migrations will require other
migration tools like dbexport/dbimport, HPL,
etc including different operating systems





Information Management

12

Converting to a Newer Database Server

Source Database Server

Target Database Server

Reference

Dynamic Server 9.40

Dynamic Server 10.00

Preparing for Migration and Converting
to Dynamic Server 10.0

Dynamic Server 9.30

Dynamic Server 9.20 or Dynamic
Server 9.21

Dynamic Server 7.3x

Dynamic Server 7.24 or
Workgroup Edition 7.24

Universal Server 9.14

Dynamic Server 9.30

The Version 9.30 Migration Guide

OnLine Dynamic Server 7.23

Dynamic Server 7.31

The Version 7.31 Migration Guide

OnLine Dynamic Server 7.22

OnLine 5.1x

Information Management

13

Migrating from prior versions of IDS

Information Management

14

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!




Information Management

15


Pre
-
Migration


Install and configuration


Validate your target environment


Perform conversion


Validate data integrity







Informix Dynamic Server 10.0

Focus on OLTP and Embedded

Performance. Reliability. Scalability.


Information Management

16

Extensive Platform Support

HP
-
UX on PA
-
RISC and
Itanium.

Tru64 on Alpha

Windows
2000, 2003
and XP on x86



AIX on pSeries

Linux on xSeries,
zSeries, pSeries* and
iSeries*

Solaris on SPARC

IRIX

Intel EM64T/AMD64

http://www
-
306.ibm.com/software/data/informix/ids/requirements.html

Information Management

17

System Requirements

IDS provides industrial
-
strength capabilities with a relatively small footprint,
ensuring the best performance with less hardware
.

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


Information Management

18

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;


Information Management

19

Also plan for …


Networking issues


IP addresses (if using a new box)


Client connectivity


Application compatibility


3
rd

Party Applications (Storage Managers, Monitoring Tools, Scripts)


Replication


Extra scripts to run. Refer to Migration Guide for details.


Testing plan or Staging environment


Try it out before you apply on your production system


Back
-
Out Plan


In case of emergency. Always good to prepare for contingencies


Save data that can help Tech Support diagnose issues that may arise



Information Management

20

IDS 10.00 Migration: Planning (cont.)


Testing/Staging Plan


Test the actual migration plan for success, and to estimate time to complete


Test all aspects of your system including


Client access


Major application functions, Reporting, Key SQL Statements


Stored Procedures, Triggers, Distributed transactions


Backup and Restore


Simulated user load


For 9.x migrations


test any UDR’s and DataBlade functions



Back
-
Out Plan


Consider steps required to back
-
out of the migration at each step in the process


Take a level
-
0 archive before migration starts


Make sure the archive is good
-

use archecker to check it.


Consider taking another form of backup


such as:


An offline OS level backup of the chunks


A dbexport of key databases (just in case)



Information Management

21

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.


Information Management

22

IDS 10.00 Migration: Checklist


During times of peak usage,


online.log snippet, with some checkpoint durations in it.


onstat

aF,
-
g all,
-
g stk all.


During times of peak usage, run the following onstats repetitively with
-
r repeat option for a period of about 3 to 5 minutes:


onstat
-
u, to see the total number of sqlexecs used.


onstat
-
p, for read and write cache rates, to detect deadlocks and the
number of sequential scans.


onstat
-
g nta, a consolidated output of
-
g ntu, ntt, ntm and ntd.


onstat
-
g nsc,
-
g nsd,
-
g nss for status of shared memory connections.


onstat
-
P,
-
g tpf,
-
g ppf


vmstat, iostat and sar, for cpu utilization. Run these repetitively for a period
of about 3 to 5 minutes. Check man pages for usage.


timex of all regularly run queries.

Information Management

23


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.




Ensure you take care of …

Information Management

24


Pre
-
Migration


Install and configuration


Validate your target environment


Perform conversion


Validate data integrity







Informix Dynamic Server 10.0

Focus on OLTP and Embedded

Performance. Reliability. Scalability.


Information Management

25

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 on LINUX also package 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


Information Management

26

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


Information Management

27


Pre
-
Migration


Install and configuration


Validate your target environment


Perform conversion


Validate data integrity







Informix Dynamic Server 10.0

Focus on OLTP and Embedded

Performance. Reliability. Scalability.


Information Management

28

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


Information Management

29


Pre
-
Migration


Install and configuration


Validate your target environment


Perform conversion


Validate data integrity







Informix Dynamic Server 10.0

Focus on OLTP and Embedded

Performance. Reliability. Scalability.


Information Management

30

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


Information Management

31


Pre
-
Migration


Install and configuration


Validate your target environment


Perform conversion


Validate data integrity







Informix Dynamic Server 10.0

Focus on OLTP and Embedded

Performance. Reliability. Scalability.


Information Management

32

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 pre
-
migration for before/after picture comparison

Information Management

33

Migration of an IDS running on a different OS


Pre
-
migration Steps for the Source Database Server


Save copies of the current configuration files


Take a level 0 backup of the database server


Choose one of the following migration tools to unload the databases


Dbexport and dbimport


UNLOAD, dbschema and LOAD


UNLOAD, dbschema and dbload


Bring the source database server offline


Install and configure the Target database server


Use dbimport, LOAD or dbload to load the databases into the target
database server.


Post
-
migration Steps for the Target Database Server


Take a level 0 backup of the database server


Run UPDATE STATISTICS

Information Management

34

Choosing a Migration method


Migration on Same Operation System


Use Direct Conversion/Reversion (when allowed between the IDS versions)


Migration on Different Operation Systems


Dbexport

and
dbimport


Use these utilities to move an entire database


UNLOAD
,
dbload

and
dbschema


To move selected columns and tables, use UNLOAD statement. Use
dbload to change the data format


UNLOAD

and
LOAD

statements


To move selected columns and tables, use UNLOAD statement. Use
LOAD when you do NOT want to change the data format


onload

and
onunload

utilities


To unload data in page
-
sized chunks use onunload utility


Use onload to move data to an identical database server on computer of
same type


High Performance Loader

(HPL)


To move selected columns and tables or an entire database, use HPL


Consulting provides automated migration services using HPL

Information Management

35

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

Information Management

36

Reverting from Dynamic Server v10


Determine Whether Reversion Is Possible


Does the schema file contain SQL statements or Features like long identifiers
that the earlier database server does not support?


Have any new SPL routines been created in IDS v10, or have you imported
existing routines using dbimport?


Have tables or indexes using expression fragmentation had expressions
changed or new fragments added?


Have any new new objects and extensions that the old database server does
not support been created. Eg: Stprocs(), UDRs, UDTs, etc.,


Have any new check constraints or triggers been created?




[refer to Migration Guide for details]


Pre
-
Reversion Steps


Check and Configure available space


Save copies of the current configuration files


Verify the integrity of data using oncheck


Take a level 0 backup of the database server


Remove pending in
-
place alters


Remove usage of any new feature adoption


Remove BladeManager extensions


If necessary, disable HDR


Information Management

37

Reverting from Dynamic Server v10 (Contd.)

Run the reversion utility
onmode
-
b

<older database server number>


Check for the "Reversion Complete" message in the online
message log


Post
-
Reversion Steps


Modify the configuration parameters and reset necessary env
variables


Reset environment variables


If necessary, remove any Communications Support Module
settings


Run Update statistics


Use oncheck to verify the integrity of data.


Take a level 0 backup of the database server


Return the target database server to online mode



Information Management

38

Application Migration
-

Reserved Words

New for 9.4


COLLATION


CROSS


FULL


INSTEAD


RESTART


RIGHT


New for v10


ACTIVE


DIRECTIVES


ENCRYPTION


HINT


INACTIVE


INLINE


INOUT


LOAD


New for 9.x


CACHE



COSTFUNC



ITEM



SELCONST



INNER



JOIN



LEFT


LOCKS



RETAIN



RAW



STANDARD



AVOID_EXECUTE



USE_SUBQF



AVOID_SUBQF



ONLINE


OPTCOMPIND


PARTITION


PASSWORD


SAVE


TABLE


TEMPLATE


TEST


TYPEID


TYPENAME


TYPEOF


WAIT


XADATASOURCE


XID

Information Management

39

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

Information Management

40

“… Table level restore enables
us to implement a BCP plan
without the need for duplicate
servers.”






David A. Link

IS Manager,

West Corporation

“To sum it all up,

IDS 10.0 is a DBA’s dream come true.”


Gary Ben
-
Israel

CIO, National Institute for Testing and Evaluation

Jerusalem, Israel

“Companies that need their data and
transaction resources to be available
24x 7 will be pleased with the release
of IDS 10.0. With this new release,
organizations can tune their database
and manage index issues online,
without interrupting service..”






Gerd Kaluzinski

Senior Systems Engineer

BYTEC GmbH

Information Management

41

Customer v10 MIGRATION USE CASE Discussion

BYTEC GmbH
-

Hermann
-
Metzger
-
Strasse 7
-

88045 Friedrichshafen

gerd.kaluzinski@bytec.de




Informix Dynamic Server 10.0

Focus on OLTP and Embedded

Performance. Reliability. Scalability.


Information Management

42

BYTEC


Customer profiles



Here the most recent migrations from our customers: [earlier at v7]

9.21.UC3
-
> 10.00.UC3 on Linux RedHat Advanced Server 3 (soon 4)

9.21.UC4
-
> 9.40.UC6 on Suse

9.30.FC3
-
> 9.40.FC6 on Solaris 8

9.21.FC2
-
> 9.40.FC6 on HP
-
UX 11i



All systems running 95% OLTP



IDS instance size between 40 GB and 200 GB with ~150
-

650
Connections to the database



One is a system running with BaaN. This one has 40'000 tables. The
other ones are in the range from 500 to 3000 tables



The number of dbspaces goes from 8 to 24 & the number of chunks
goes from 12 to 42. All instances had upto 10 databases.


BYTEC GmbH
-

Hermann
-
Metzger
-
Strasse 7
-

88045 Friedrichshafen

Information Management

43

BYTEC


Customer profiles (contd ..)


The InPlace migration took up to 12 hours including oncheck after migration on
the big systems. We had up to 2 hours without access, after this we allowed
read
-
only until the checks have passed.



Prepare for:

Pseudo updates to 9.40 take a lot of LOCKS and you can run into OVERLOCKS.

Physical log must be big enough and/or PLOG_OVERFLOW_PATH set. If



physlog overflow occurs during migration you have to start again.

Some new reserved words have been introduced (ref, status, ...) and so you


have to check your application on a test server against this.


Benefits:

Response times in application have been reduced without changing the application.

A backup concept, where every night a dbexport blocked the database was changed by
using the new feature "table level restore".





** Reversion was not tested on these systems as there was no requirement


BYTEC GmbH
-

Hermann
-
Metzger
-
Strasse 7
-

88045 Friedrichshafen

Information Management

44

BYTEC’s Migration Checklist


1. Preparation



Shutdown User Connections (Dataconsistency)


Create and Check last Backup (ontape, dbexport)


Increase Parameter LOCKS up to 1'000'000


Increase Physical Log used for conversion and



set Environment PLOG_OVERFLOW_PATH


oncheck
-
cDI (each database)


oncheck
-
cr / oncheck
-
cR


oncheck
-
cc


Check installed products



(Checks can be made the days before migration)


BYTEC GmbH
-

Hermann
-
Metzger
-
Strasse 7
-

88045 Friedrichshafen

Information Management

45

BYTEC’s Migration Checklist


2. First Steps




Update statistics Low drop Distributions (each database)


Remove CDR (cdr delete server)


Unregister Datablades from all databases


Deactivate your RC
-
Scripts


Make sure all logs have been backed up (no U
-----
)


Change LTAPEDEV in ONCONFIG to /dev/null


Shutdown using onmode
-
ucky


Save a copy of „onconfig“ and „sqlhosts“


Backup INFORMIXDIR (for example using tar)


BYTEC GmbH
-

Hermann
-
Metzger
-
Strasse 7
-

88045 Friedrichshafen

Information Management

46

BYTEC’s Migration Checklist


3. Install new software





Check permissions (INFORMIXDIR)


Install the new Products (Tools
-
Engine
-
SDK)


Modify UNIX
-
Kernel (see Release Notes)


Check KAIO
-
settings (see Release Notes)



(for example on HP /etc/privgroup)


Make changes in onconfig


restore sqlhosts



BYTEC GmbH
-

Hermann
-
Metzger
-
Strasse 7
-

88045 Friedrichshafen

Information Management

47

BYTEC’s Migration Checklist

4. Start new server





oninit
-
v to see all steps


Check online.log to see Conversion and Messages


Execute ONMODE
-
BC 1 and ONMODE
-
BC 2 (BigChunks)


oncheck
-
cr / oncheck
-
cc


oncheck
-
cI (each database)


oncheck
-
cD (each database)


update statistics (each database)


update statistics high (on leading indexcolumns)

BYTEC GmbH
-

Hermann
-
Metzger
-
Strasse 7
-

88045 Friedrichshafen

Information Management

48

BYTEC’s Migration Checklist


5. Backup and Documentation




Take the first backup of the new version


Initiate CDR


Register Datablades


Activate RC
-
Scripts


Make a snapshot of your installation (onstat
-
a)


BYTEC GmbH
-

Hermann
-
Metzger
-
Strasse 7
-

88045 Friedrichshafen

Information Management

49

Review of Migrations

Following the checklist all Migrations went without problems within the
calculated time


Among these Migrations have been Hospitals, where the complete
Migration with more than 50 Chunks could be made within 2 hours of
downtime.


Other customers situated in Metal Industries, Fashion manufacturers as
well as Public Sites (Libraries and Universities) have been migrated
InPlace.



BYTEC GmbH
-

Hermann
-
Metzger
-
Strasse 7
-

88045 Friedrichshafen

Information Management

50

Review of Migrations

Here one example of Configuration, which has been migrated. You see
all kind of Dbspaces used at customersite. Medical images are stored
in Smartblobs, Doctor
-
Letters are in Blobspaces:


Dbspaces

address number fags fchunk nchunks flags name

892ba7d8 1 0x40001 1 1 N B rootdbs

89f18ad0 2 0x40001 2 25 N B datadbs

89f18c20 3 0x40011 12 20 N BB blobdbs

89f18d70 4 0x42001 22 1 N TB tempdbs

89f18ec0 5 0x40001 24 3 N B logdbs

89f19018 6 0x42001 23 1 N TB tempdbs2

b3b7aea8 7 0x48001 52 12 N SB sbspace


BYTEC GmbH
-

Hermann
-
Metzger
-
Strasse 7
-

88045 Friedrichshafen

Information Management

51

Information Management

52