Transcript Migration Steps

Upgrading and Migrating Informix Databases
Information Management Technology Ecosystems
© 2010 IBM Corporation
Agenda
• Impact of Migrations on Business
• Migration Planning
• Pre and Post Migration Steps
• Type of Informix Migrations
• In-place Upgrades
• Reversion
• Non-in-place Upgrades
What’s new in 11.70?
2
© 2010 IBM Corporation
Agenda
• Impact of Migrations on Business
• Migration Planning
• Pre and Post Migration Steps
• Type of Informix Migrations
• In-place Upgrades
• Reversion
• Non-in-place Upgrades
• What’s new in 11.70?
3
© 2010 IBM Corporation
Important to understand the Business Impact!
• Business decisions depend less on the technologies and more on
how the business functions
• MUST account for the following during the planning phase:
• Business process disruption
• When and how long of an outage tolerable?
• Mitigation planning:
• What happens if the migration fails?
• Staffing (operational and technical):
• Will ALL the required individuals be available?
• Training:
• Who will need to test the new version?
• Cost:
• Can you afford more disk or faster network or ....?
4
© 2010 IBM Corporation
Agenda
• Impact of Migrations on Business
• Migration Planning
• Pre and Post Migration Steps
• Type of Informix Migrations
• In-place Upgrades
• Reversion
• Non-in-place Upgrades
• What’s new in 11.70?
5
© 2010 IBM Corporation
Migration Planning: Space Requirements
Requirements for a Direct Upgrade
• Root Chunk (Chunk 0) should have at least 10% free (>= 6 MB)
• 3000 free pages of logical log space to rebuild sysmaster
• 2000 KB of free space per database is needed for each dbspace
• Plan for 20% free disk space in nonroot dbspaces
Requirements for a NEW Installation of Informix 11
• 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
** Look in Installation guide for details
© 2010 IBM Corporation
Migration Planning: Develop a BACKOUT Plan
• Create a backup before migration
• Be ready to revert in case of major problems after upgrade
• Test the back-out procedures on a test database
• Incorporate the back-out plan into the migration plan document
• 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)
• An HDR secondary server
© 2010 IBM Corporation
Migration Planning: Informix Environment Setup/Changes
• Upgrades provides the most opportune time to make changes to your
Informix environment for better performance and administration
• Some changes to consider (if not already implemented)
• Setup links for storage space chunks
• Easier to change the underlying physical device
• Fragment data for large tables over several dbspaces
• Associate each dbspaces with a separate physical device
• Take advantage of new features provided in the new version
• Move CRON jobs from scripts to the database scheduler
• Learn to administer Informix using the Open-Admin tool
• Create a new $ONCONFIG file to be used in the new instance
• Use a copy of Version 11.70 onconfig.std to populate its parameters
• Do not use old version except to populate new config file
© 2010 IBM Corporation
Migration Planning: Migrating from OLDER versions
• Review the new reserved words in the Informix Migration Guide
• Make sure that schema changes are not needed
• Some are common words like ADMIN, TASK, ACTIVE, LIMIT, TEST, SAMPLING,
CROSS, FULL, RIGHT
• May collide with names of existing tables, columns, functions, variables, and other
objects
• See 11.70 Migration Guide for the list of new reserved words in 11.70, 11.50, 11.10,
10.00, 9.40, 9.30, 9.21, and 7.31
• Although you can use almost any word as an SQL identifier, syntactic
ambiguities might occur if you use an SQL reserved word
• A new system database called “sysadmin” introduced in IDS 11.10
• If you have a database called “sysadmin”, you must rename it prior to upgrading to
11.10, 11.50, or 11.70
• Starting in 11.10, DBSECADM role is reserved for LBAC
administrative work
© 2010 IBM Corporation
Migration Planning: TEST EVERYTHING!!!
• Test all aspects of the migration process thoroughly
•
•
•
•
Migration (upgrade)
Reversion (onmode –b)
Recovering from Failures (restores, conversion guard)
Pre and Post migration procedures (onchecks, benchmarks, …)
• Test the new version of Informix in an environment that closely
matches production
• Conduct Functional test
• Conduct Volume tests if possible
• Test client and all operational aspects of your system
•
•
•
•
•
10
Client access
Major application functions, reporting, key SQL statements.
Stored procedures, triggers, distributed transactions
Backup and restore
For migration from 9.x or 10.x: test any UDRs and DataBlade functions
© 2010 IBM Corporation
Migration Planning: Create a MIGRATION PLAN
• Create a detailed migration plan which details each step
•
•
•
•
•
•
When
Who
What (exact steps/command)
How Long
Stop time
In case of problems with this step
• Include contact information of ALL individuals participating, all
departments affected, any company-wide groups
• Test the actual migration plan for success
• Always add additional time to the migration plan to account for and
resolve unexpected events or problems
• Usually in the range of 33-66%
ALWAYS CREATE A MIGRATION PLAN
DOCUMENT!
11
© 2010 IBM Corporation
Direct migration paths for migration to the newer version
Source database server version
Target database server version
11.50, 11.10, 10.00, 9.40, 7.31
You can migrate directly to Version 11.70
9.30, 9.21
You must first migrate to Version 11.50
7.30
You must first migrate to Version 10.00,
9.40, or 7.31
7.24
You must first migrate to Version 10.00 or
9.40
Universal Server Version 9.14
You must first migrate to Version 9.30
Dynamic Server Version 9.20
OnLine Dynamic Server 7.23
You must first migrate to Version 7.31
OnLine Dynamic Server 7.22
OnLine 5.1 or earlier versions
12
© 2010 IBM Corporation
Agenda
• Impact of Migrations on Business
• Migration Planning
• Pre and Post Migration Steps
• Type of Informix Migrations
• In-place Upgrades
• Reversion
• Non-in-place Upgrades
• What’s new in 11.70?
13
© 2010 IBM Corporation
PRE-migration steps - Tasks
 Remove outstanding in-place ALTERs:
 Time-consuming to find out which tables need it.
 Only affects ability to revert (conversion does work).
 Do a dummy update, for example:
 UPDATE tab1 SET col1 = col1 WHERE 1=1;
 Close all open transactions
 onmode –s, onmode –l, onmode –c, onmode –ky
 oninit –s (bring server up in Quiescent mode)
 If using HDR – disable the connection by making the Primary a stand-alone
(onmode –d standard)
 Perform a Level-0 archive/backup before migration and verify it
 Stop all CRON jobs that connect to the database server
 Change/Remove the port(s) of the instance in the sqlhosts file so that applications
and users cannot connect to Informix during the upgrade
 Flush Enterprise Replication queues with any transactions
 Backup all transaction logs including the current before shutting down the source
server for upgrade
 Drop sysmaster to avoid sysmaster conversion issues
14
© 2010 IBM Corporation
PRE-migration steps - Verification
 Ensure dbspace flags are normal
 Verify the integrity of data
 Check reserve pages (oncheck –cr)
 Check extents (oncheck –ce)
 Check system catalog tables (oncheck -cc <database_name>)
 Check data (oncheck -cD <database_name>)
 Check indexes (oncheck -cI <database_name>)
 Check sbspaces (oncheck –cs <sbspace_name>, oncheck –cS <…>)
 Ensure you have read the Informix Machine and Release notes for the new
Informix 11.70 version – prior to migration
 UNIX/Linux – Check and modify, if necessary, any required kernel or system
parameters prior to the upgrade (release notes)
 Check that all dbspaces and the root chunk (chunk 1) have adequate space for an
upgrade
 Check that there are adequate log space for conversion activities
 Verify that source server is off-line
15
© 2010 IBM Corporation
PRE-migration steps - Capture information
 During peak times, save
 online.log snippet, with some checkpoint durations included
 onstat –aF, -g all, -g stk all
 During peak times, run the following repetitively and save the info







onstat -u  total number of sqlexec used
onstat -p  instance-wide profile
onstat -g nta  consolidated output of -g ntu, ntt, ntm & ntd
onstat -g nsc, -g nsd, -g nss  status of shared memory connections
onstat -P, -g tpf, -g ppf  table profiles
vmstat, iostat and sar  O/S and system reports
timex  timings for all regularly run queries
 Save distributions dbschema -d -hd for all critical tables
 Save access plans for all regularly used queries using set explain on
 Save oncheck –pr, -pe and –pc output
 Save all the environment variables that are set using env Unix command
 Backup the $INFORMIXDIR directory
© 2010 IBM Corporation
PRE-migration steps - Capture information
Save ALL configuration files
Unix / Linux / OS X
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
© 2010 IBM Corporation
POST-migration steps – Checks and Tasks
 Run Update Statistics
 On specific system catalog tables in every database
 For procedures in every database – run without PDQ enabled
 Low to drop distributions.
 Then continue to apply normal site procedures
 Or use Auto Update Statistics (AUS)
 Verify the integrity of the data [oncheck –ce; cr; cc]
 If using ON-Bar, rename the sm_versions.std file
 Make an initial Level-0 backup of the target server
 Tune Informix for performance as necessary
 Enable HDR and ER, if applicable. Perform ER conversion tasks.
 Rerun the pre- and post-migration checklist for before/after picture comparison
 Register any DataBlade modules
 Re-enable the scheduler to run
 Remove the ‘stop’ file from INFORMIXDIR/etc/sysadmin
 Recycle Informix (shutdown ‘onmode –yuck’, bring back online with ‘oninit –v’)
 Re-enable client connections
18
© 2010 IBM Corporation
POST-migration steps – Considerations
• New sysadmin database automatically created in IDS 11
• Created in rootdbs by default
• System data automatically collected by default
• Dbcron scheduled tasks are stored in the sysadmin database tables
• Use “RESET SYSADMIN” command to move the syadmin database to another
dbspace (prevent your rootdbs from filling up!)
execute function sysadmin:task (“RESET SYSADMIN”,
“new_dbspace_name")
• Turn on new features or make ONCONFIG changes slowly
• Most new features that are turned on by default (it’s a good thing!)
• Look at the new, improved onconfig.std for explanations of most new ONCONFIG
parameters
© 2010 IBM Corporation
Agenda
• Impact of Migrations on Business
• Migration Planning
• Pre and Post Migration Steps
• Type of Informix Migrations
• In-place Upgrades
• Reversion
• Non-in-place Upgrades
• What’s new in 11.70?
20
© 2010 IBM Corporation
Informix Migration Types: In-Place & Non-in-place
• In-Place Migration
• Uses your existing test and production hardware
• Same operating system, page size, code pages
• Uses direct conversion/reversion (when supported between the
Informix versions)
• Also used when upgrading (Migrating) to Informix 11 on a different
machine, compatible OS
• Non-in-Place Migration
• Upgrading (Migrating) to Informix 11 on a different OS
• Different operating systems, page sizes, code pages
• Install Informix 11, create instance, create databases/tables,
load/insert data
• Must use data movement (data migration) utilities
© 2010 IBM Corporation
Informix Migration Types: In-Place
• Simplest upgrade method
• Also known as “Conversion”
• Automatic, by starting the new server
• When to use it:
• If architecture/platform remains the same.
• If existing dbspaces are well laid out; extents are under control.
• Can be used to migrate from 32 to 64-bit
• Windows installation is slightly different
© 2010 IBM Corporation
Direct Conversion Examples
• Direct conversion is possible, but you may want to
upgrade in steps –upgrade OS, then Informix
• Upgrading from 7.31.UD8, Solaris to 11.50.FC3, Solaris
• Going from Solaris 8 to Solaris 10
• Upgrading from 10.00.UC7, Linux to 11.50.FC3, Linux
• Going from Red Hat 2.6.9-42.0.8.ELsmp on an i686 to Red Hat
2.6.9-34.ELsmp on an x86_64
• Upgrading from 10.00.TC6 to 11.50.FC3
• Going from Windows XP to Windows x64
© 2010 IBM Corporation
Informix Migration Types: In-Place
1.
Get the source server ready
2.
Install the target server in a new directory and copy relevant configuration settings
•
Verify environment variables
INFORMIXSERVER, INFORMIXDIR, ONCONFIG, PATH INFORMIXSQLHOSTS (optional)
3.
Run Pre-migration procedures
4.
Perform a Level-0 (full) archive of the instance to be upgraded
5.
Bring down (stop) the source server gracefully. (example: onmode –s, onmode –l, onmode
–c, onmode –ky)
6.
Change your environment settings to point to the new directory path (11.5)
•
Convert by bringing up (starting) target server (oninit –v)
7.
•
8.
9.
Verify environment variables now point to new instance
DO NOT USE “oninit –i” as this will initialize disk!
WAIT for the automatic conversion process to complete
•
Wait for “Conversion Completed Successfully” status
•
Then wait again while the database server builds the system database
Validate data integrity
10. Run Post-migration procedures including UPDATE STATISTICS
11. Take level-0 backup – DONE!
© 2010 IBM Corporation
Agenda
• Impact of Migrations on Business
• Migration Planning
• Pre and Post Migration Steps
• Type of Informix Migrations
• In-place Upgrades
• Reversion
• Non-in-place Upgrades
• What’s new in 11.70?
25
© 2010 IBM Corporation
Reversion after a Successful Migration
• An Informix server that’s gone through a successful conversion,
must go through reversion to fall back to the previous version
• First requires the removal of new databases/features
• Reversion does not result in data loss or unloading/reloading new
data!
• Once you specify a target server for reversion
• Informix checks if reversion is possible
• May include the requirement to drop certain database objects not
allowed in the previous version
• E.g. New databases or stored procedures
• The reversion process has its own check list - check the Migration
Guide’s “Reverting from Informix Version 11.70” to check if
reversion is possible
26
© 2010 IBM Corporation
Reversion – Pre 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
• If necessary, disable HDR
• Flush ER Queues with any transactions, if applicable
27
© 2010 IBM Corporation
Reversion - Checklist
• Be aware of:
• Large chunks (> 2GB) / new databases
• New features such as index self-join, truncate table etc
• New reserved words
• XA data sources and types
• Secure auditing masks/external spaces
• Long identifiers
• Stored procedures, created or imported with dbimport
• In-place ALTERs (complete them)
• DataBlade modules
• Have fragmentation expressions changed or were new fragments added?
• New triggers or check constraints?
• Have any new objects and extensions that the old database server does not
support been created?
• UDRs, UDTs, etc.
28
© 2010 IBM Corporation
Reversion – Command
• The Informix utility onmode is used to perform a reversion
• The onmode utility will inform you what you must drop or delete, and
if reversion is even possible
• Invocation:
onmode -b 11.50
onmode -b 10.00
onmode -b 9.4
release
onmode -b 9.3
onmode -b 9.2
onmode -b 7.3
onmode -b 7.2
 revert
to Version 11.50
 revert to Version 10.00
 revert to any Version 9.4
 revert
to any Version 9.30
 revert to Version 9.21
 revert to Version 7.31
 revert to Version 7.24
© 2010 IBM Corporation
Reversion - Post Steps
• Modify changed configuration parameters
• Reset environment variables
• If necessary, remove any Communication Support Module
settings
• Execute Update statistics
• Use oncheck to verify the integrity of data
• Take a level-0 backup of the database server
• Return the original database server to on-line mode
30
© 2010 IBM Corporation
Agenda
• Impact of Migrations on Business
• Migration Planning
• Pre and Post Migration Steps
• Type of Informix Migrations
• In-place Upgrades
• Reversion
• Non-in-place Upgrades
• What’s new in 11.70?
31
© 2010 IBM Corporation
Informix Migration Types: Non-in-Place
BASIC steps
• When to use it:
• Moving to new architecture or to
different server
• For example, Unix  Linux or
Solaris  AIX
• Desire to take advantage of new
page sizes / larger chunks
• Changing code pages
• Optimization of database spaces
and extent allocations
• Caveats:
• More planning and setup time
• Physically moving data requires
disk space and time
• Pre-migration step on Source Server
• Take a level-0 backup
• Pre-migration step for the Target Server
• Install and configure the target server using
information from source
• Migration Steps:
1. Choose a tool to unload the source database
2. Move the data to the target server
3. Use chosen tool to load the databases into the
target server
4. Redirect clients and application from source to
target server
5. Run Post-migration steps including UPDATE
STATISTICS
6. Take a level-0 backup of the target database
server
7. Bring the source server offline – DONE!
© 2010 IBM Corporation
Informix Migration Tools: DBEXPORT/DBIMPORT
• Imports and exports a database to a text file that is stored on disk or tape
• Because data is unloaded/load in text format, it is slow
• Can create files larger than 2 GB
• dbexport
• Unloads an entire database into text files (ASCII) and creates a schema file
• Exports dates in four-digit years unless the environment variable DBDATE is set
• Uncompresses compressed data
• dbimport
• Re-creates the database schema in another Informix environment and loads data
• Ability to edit the schema file to modify the database
• Can create an ANSI-compliant database (includes unbuffered logging)
• Can establish transaction logging for a database (unbuffered or buffered logging)
• Can specify the dbspace where the database will reside
• Limitations:
• Unload and loads an entire database, including its schema
• Must disable SELECT triggers before exporting a database
• Must re-enable compression and recompress after importing data, if applicable
• Does not work seamlessly when DataBlades are present
33
© 2010 IBM Corporation
Informix Migration Tools: DBACCESS LOAD/UNLOAD
• Use the SQL LOAD and UNLOAD statements to move data
• Easy to use and flexible
• Can unload/load files larger than 2 GB if the platform supports it
• Because data is unloaded/load in text format, it is slow
• Use on RAW tables or with logging off.
• LOAD
• Ability to load selected rows and columns from a table into a text file
• UNLOAD
• Ability to unload selected rows and columns from a table into a text file
• Lets you manipulate the data as you unload it
• Limitations:
• LOAD only accepts specified data formats
• UNLOAD to files on disk only – not tape
• For locales that support multibyte code sets, the declared size (in bytes) of any column that
receives character data must be large enough to store the entire data string
• LOAD has low flexibility - No commit interval, cannot skip rows, file structure and data
format must be as expected
34
© 2010 IBM Corporation
Informix Migration Tools: ONLOAD/ONUNLOAD
• Provide the fastest way to move data between computers that use the same database
server and the same platform
• Faster than dbexport/dbimport, dbload, or LOAD/UNLOAD
• onunload
• Unloads data in binary form in disk-page units
• Writes a database or table into a file on tape or disk
• onload
• Loads data that was created with the onunload command
• Ability to create database/table before load
• Must have the same scope as the corresponding onunload command that unloaded
the same table(s)
• Creates a database without logging
NOT a general migration tool
• Limitations:
• Use between computers that have the same version of Informix and computer type
• Must turn off logging for the database during a load operation, if applicable
• Inflexible
• May fail for unclear reasons
35
© 2010 IBM Corporation
Informix Migration Tools: DBLOAD
• Loads data into databases or tables
• Transfers data from one or more text files into one or more existing tables
• Data normally unloaded to file using DBACCESS UNLOAD
• Not faster than DBACCESS LOAD
• Provides great deal of flexibility
• Can use with data in a variety of formats (different DBMS)
• Can specify a starting point in the load ( ignore x number of rows)
• Can specify a commit interval
• Can limit the number of bad rows read
• Can manipulate a data file that you are loading or access a database while
it is loading
• Limitations
• Requires a command file to control the input (specifies tables, load files, …)
• Cannot use on secondary servers in high-availability cluster
36
© 2010 IBM Corporation
Informix Migration Tools: High-Performance Loader (HPL)
• Load and unloads large quantities of data efficiently and in parallel to or from a
database
• Can migrate columns, tables, or databases
• Useful when moving extremely large tables
• Allows for the exchange of data with tapes, data files, and programs
• Can manipulate and filter the data as you perform load and unload operations
• Can split large files or use named pipe
• Named pipe can be created on the source machine only (One (1) pipe only)
• Supports codeset conversion
• Can be used when crossing architectures - using ASCII output
• Faster than dbexport/dbimport, unload/load/dbload
• Supports EXPRESS (Exclusive Lock) and DELUXE (Shared Lock) mode
• Limitations:
• Difficult to set up – requires configuration per table
• onpladm utility makes it easier to setup HPL load/unload jobs
37
© 2010 IBM Corporation
Informix Migration Tools: External Tables - LOAD Example
• Load data to/ from the
filesystem
• Fastest load and unload of
massive data sets
• Ease of Use
• Use similar to regular tables
• Can be used in Stored procedure
for load and unload
• No need of DBA privilege to do
Load/Unload using external tables
CREATE EXTERNAL TABLE empdata
(
empname
char(40),
empdate
date
)
USING
(DATAFILES
(
"DISK:/work/empdata.unl"
),
FORMAT "DELIMITED",
REJECTFILE
"/work/errlog/empdata.rej",
MAXERRORS 100,
DELUXE
);
SELECT empname, empdate FROM empdata
INSERT INTO employee SELECT * FROM empdata
INSERT INTO empdata SELECT emp_name, emp_date FROM employee
© 2010 IBM Corporation
Informix Migration Tools: External Tables - UNLOAD Example
SELECT product_id, SUM(quantity) volume, SUM(price) value
FROM product_sales
WHERE DATE(sales_time) = TODAY
GROUP BY product_id
INTO EXTERNAL product_sales_summary
USING
(
FORMAT 'INFORMIX',
DATAFILES ('DISK:/data/latest/sales_sum.dat')
);
© 2010 IBM Corporation
Informix Migration Tools: DBSCHEMA
• Displays the SQL statements (the schema) that are necessary to replicate a
specified table, view, or database
• Use it to generate DDL to recreate, using DBACCESS, a database/object in
another Informix environment
dbschema –d dbname –ss > filename
• Can also be used to:
• Display the distributions that the UPDATE STATISTICS statement creates.
• Display the schema for the Information Schema views
• Display the distribution information that is stored for one or more tables in
the database
• Display information on user-defined data types and row types
• Limitations:
• Can unintentionally increment sequence objects in the database
• Must disable SELECT triggers
• Must correctly set GLS environment variables
40
© 2010 IBM Corporation
Informix Migration Tools: Distributed Queries (DQ)
INSERT INTO dbname@target:owner.table
SELECT * FROM dbname@source:owner.table
• A type of query that can be used to query more than one database in the same instance
or across multiple instances residing on the same/different host computers
• Must setup all servers involved to enable server-to-server communications over the
network
• Execute with indexes, constraints and triggers DISABLED/DROPped
• For large tables, use scripts to partition the table and process in parallel
• Low stats and distributions on lead columns are created during index builds
• Advantages
• Simpler and safe to set up, lets Informix take care of conversion issues
• Can parallelize queries
• Does not require intermediate disk space
• Limitations
• Source and target(s) must have the same transaction-logging mode
• Does not support User Defined Types (UDT) or Datablades
41
© 2010 IBM Corporation
Informix Migration Tools: Enterprise Replication
• Not an option listed in the Migration Guide
• But it can be used under certain conditions to avoid an extended outage
during migration
• Advantages:
• Flexible, table-level migration
• Allows mix of 7.3, 9.x, 10.x and 11.x versions (rolling upgrades)
• Can work with most DataBlades and UDTs:
• ER must be explicitly supported by the DataBlade
• Time Series does NOT support ER
• New transactions will queue up until the migrated server is available
• Disadvantages:
• May get complicated to setup/administer
• New ER templates starting in Informix 10 make it a little easier
• Table-level replication only
• Requires primary keys on all tables
• Network traffic may impact
© 2010 IBM Corporation
Agenda
• Impact of Migrations on Business
• Migration Planning
• Pre and Post Migration Steps
• Type of Informix Migrations
• In-place Upgrades
• Reversion
• Non-in-place Upgrades
• What’s new in 11.70?
43
© 2010 IBM Corporation
New Features in Migration to Version 11.70
• Can migrate from Informix Version 11.50 11.10, 10.0, 9.40,
or 7.31 directly to Informix Version 11.70
• dbschema utility enhancement for omitting the specification
of an owner
• Can use the new dbschema utility –nw option to generate
the SQL for creating an object without specifying an
owner
• dbexport utility enhancement for omitting the specification
of an owner
• Can use the new dbexport utility –nw option to generate
the SQL for creating a database without specifying an
owner
© 2010 IBM Corporation
Seamless installation and Smarter configuration
• New installation application, using the new ids_install
command, makes it easier to install and configure Informix
products and features
• A typical installation now has improved default settings to
quickly install all of the products and features in the
software bundle, with preconfigured settings
• The custom installation is also smarter than before and
allows you to control what is installed
• Both types of installations allow you can create an instance
that is initialized and ready to use after installation
• Must use a custom installation setup if you want to
configure the instance for your business needs
© 2010 IBM Corporation
Changes to Installation commands
• New uninstallids command
• Removes the server, any bundled software, or both
• To remove specific products
• uninstall/uninstall_server/uninstallserver
• uninstall/uninstall_clientsdk/uninstallclientsdk
• uninstall/uninstall_connect/uninstallconnect (formerly
uninstallconn)
• uninstall/uninstall_jdbc/uninstalljdbc.exe or java -jar
uninstall/uninstall_jdbc/uninstaller.jar (depending on how you
install the JDBC driver)
• Must use ids_install to install Informix with or without
bundled software
• Depreciated commands
• installserver
• installclientsdk
• installconn
© 2010 IBM Corporation
Generating Storage Spaces and Logs with dbschema
SQL administration API format
• Can now generate the schema of
storage spaces, chunks, and physical
and logical logs with the dbschema
utility
• Choose to generate:
• SQL administration API commands
dbschema -c dbschema1.out
• onspaces and onparams utility
commands
dbschema -c –ns dbschema2.out
• For migrations, generate the schema
before unload data using the dbexport
and dbimport utilities
# Dbspace 1 -- Chunk 1
EXECUTE FUNCTION TASK ('create dbspace',
'rootdbs', '/export/home/informix/data/rootdbs',
'200000', '0', '2', '500', '100')
# Dbspace 2 -- Chunk 2
EXECUTE FUNCTION TASK ('create dbspace',
'datadbs1', '/export/home/informix/data/datadbs',
'5000000', '0', '2', '100', '100')
# Physical Log
EXECUTE FUNCTION TASK ('alter plog', 'rootdbs',
'60000')
# Logical Log 1
EXECUTE FUNCTION TASK ('add log', 'rootdbs',
'10000')
onspaces/onparams format
# Dbspace 1 -- Chunk 1
onspaces -c -d rootdbs -k 2 -p
/export/home/informix/data/rootdbs -o 0 -s 200000
-en 500 -ef 100
# Dbspace 2 -- Chunk 2
onspaces -c -d datadbs1 -k 2 -p
/export/home/informix/data/usrdbs -o 0 -s 5000000
-en 100 -ef 100
# Logical Log 1
onparams -a -d rootdbs -s 10000
© 2010 IBM Corporation
48
© 2010 IBM Corporation
Appendix
• Reserved Words
49
© 2010 IBM Corporation
Reserved words (partial)
New for 9.x
•
•
•
•
•
•
•
•
•
•
•
•
•
•
CACHE
COSTFUNC
ITEM
SELCONST
INNER
JOIN
LEFT
LOCKS
RETAIN
RAW
STANDARD
AVOID_EXECUTE
USE_SUBQF
AVOID_SUBQF
New for 9.4
• COLLATION
• CROSS
• FULL
• INSTEAD
• RESTART
• RIGHT
New for IDS 10
•
•
•
•
•
•
•
•
ACTIVE
DIRECTIVES
ENCRYPTION
HINT
INACTIVE
INLINE
INOUT
LOAD
New for IDS 10 (cont)
•
•
•
•
•
•
•
•
•
•
•
•
•
•
ONLINE
OPTCOMPIND
PARTITION
PASSWORD
SAVE
TABLE
TEMPLATE
TEST
TYPEID
TYPENAME
TYPEOF
WAIT
XADATASOURCE
XID
© 2010 IBM Corporation
Reserved words (partial)
New for 11
•
•
•
•
•
•
•
•
•
•
•
•
•
•
ADMIN
AVOID_INDEX_SJ
INSERTING
IDSSECURITYLABEL
INDEX_SJ
REFERENCES
SAMPLING
SELECTING
SYSDBCLOSE
SYSDBOPEN
TASK
UPDATING
USELASTCOMMITTED
WITH
Full list in the IBM Informix Migration Guide
© 2010 IBM Corporation
Reversion Example
$onmode -b 7.3
This will make all necessary modifications to disk
structures so that the IBM Informix Dynamic Server
space will be compatible with INFORMIX-OnLine
Version 7.3
Do you wish to continue (y/n)? Y
Beginning process of reverting system to 7.3 ...
Checking database stores0 for revertibility ...
Database stores0 is revertible ...…
** WARNING: Value for config parameter LRU_MIN_DIRTY
will be truncated from decimal value 50.000 to
integer value 50.Please check onconfig parameter
value before reinitializing the Server.
© 2010 IBM Corporation
Reversion Example (cont)
... dropping 'sysmaster' database
Starting reversion of database stores0 ...
Database stores0 SUCCESSFULLY reverted ...
Starting reversion of database stores1 ...
Database stores1 SUCCESSFULLY reverted ...
Starting reversion of database stores2 ...
Database stores2 SUCCESSFULLY reverted ...
Starting reversion of database stores3 ...
Database stores3 SUCCESSFULLY reverted ...
Started the Reversion of the Database Tablespace
The Reversion of the Database Tablespace is Finished
Reverting Partition Header Pages Started
Reverting Partition Header Pages Succeeded
Reversion complete.
Install INFORMIX-OnLine Version 7.3 before reinitializing
OnLine.
© 2010 IBM Corporation
Choosing a Method to move data
• dbexport and dbimport
• Use these utilities to move an entire database
• dbaccess UNLOAD, dbload and dbschema
• To move selected columns and tables, use the UNLOAD statement. Use dbload to
change the data format
• dbaccess UNLOAD and LOAD statements
• To move selected columns and tables, use the UNLOAD statement. Use LOAD when
you do NOT want to change the data format
• High Performance Loader (HPL)
• To move selected columns and tables or an entire database
• External Tables
• To move selected columns and tables
• Enterprise Replication
• To move selected columns and tables using replicates
• Distributed Queries
• To move selected columns and tables without using disk storage
54
© 2010 IBM Corporation
Informix Migration Tools: External Tables Syntax
•
DEFAULT and DELUXE
keywords only during LOAD
•
In 11.50.xC6, the keywords
EXPRESS and DELUXE are
not honored
•
Server decides the mode
internally:
• Chooses EXPRESS for raw
tables with no indexes,
DELUXE otherwise
• If the table is defined with a
contradictory clause say
DELUXE, a message is written
to the online.log:
• 23:26:38 Switching
load on target table
ssajip.t1 to EXPRESS
table_options is
.--,------------------------------.
|
|
|----+-FORMAT--'-+-DELIMITED-----+-'---+------+-------------|
|
+-INFORMIX------+
|
|
+-FIXED---------+
|
|
+-DB2-----------+
|
|
|
+-+-DELUXE--+----------------------------+
| +-EXPRESS-+
|
|
|
|--ESCAPE--------------------------------+
|
|
'-+-DBDATE-----‘date_format’------+-’
+-DBMONEY---‘currency'--+
+-DELIMITER--'field_delimiter'--+
+-RECORDEND--'record_delimiter'-+
+-MAXERRORS--num_errors---------+
+-REJECTFILE--'filename'--------+
`-+---SIZE----+----num_rows-----‘
+--NUMROWS--+
© 2010 IBM Corporation
Informix Migration Tools: External Tables
• Load data from the filesystem
• Faster load and unload of
massive data sets
• Internal tests shows 2x for
unload and 3x for load of
large tables over HPL
• Ease of Use
• Syntactically, an external table can be used in an SQL statement in place
of a regular table.
• External table can be used in Stored procedure for load and unload
• No need of DBA privilege to do Load/Unload using external tables
• Most requested feature for XPS users
© 2010 IBM Corporation
Informix 11.7 Bootcamp
Upgrading and Migrating Informix Databases
Information Management Technology Ecosystems
© 2010 IBM Corporation