Transcript No Slide Title

The Why's and
How's
of Migrating to
Foundation
Carlton Doe
Senior Systems Engineer
NewCo Software and
Solutions, Inc.
Who I Am
• 10 + years of experience as DBA and engine
admin mainly in retail environments
• Co-founded, presided over, and currently sit on
the Board of Directors of the International
Informix Users Group (IIUG)
• Written two Informix Press books:
?
ISBN 0-13-605296-7
ISBN 0-13-080533-5
Informix
user.conference
2
What I’m Going to Cover
•
•
•
•
•
•
•
•
•
•
•
•
3
What is IDS and Foundation?
Migration issues
ONCONFIG parameters
SQL statement cache
Max Connect
Fuzzy Checkpoints
Smart large objects Dbspaces
Simple/complex datatypes and casting
User Defined Routines and Functions
Collections
DataBlades and the Blade Manager
Informix
Java in the engine
user.conference
What Is IDS and Foundation?
INFORMIX-Dynamic Server
7.x
•
•
•
•
•
Parallelism built-in
Parallel Data Query
SQL92 Entry Level
Enterprise Replication
Many, many more...
IDS - UDO
9.1x
•
•
•
•
•
•
•
•
•
•
•
•
•
•
SQL 3 Support
DataBlade Support
DataBlade Developer Kit
User Defined Routines
User Defined Datatypes
User Defined Indexing
R-Tree Indexing
Extended B-Tree Support
Row Types
Collections
(sets, multiset, lists)
Inheritance
Polymorphism
Partitioning with new data types
Schema Knowledge
Informix
user.conference
4
What Is IDS and Foundation?
INFORMIX-Dynamic Server
7.x
+
IDS - UDO
9.1x
=
IDS 9.2 +
Informix
user.conference
5
Then What Is Foundation?
Java
VM
Web
DataBlade
Excalibur
Text
Datablade
Object
Translator
Office
Spigot
Informix Dynamic Server
Informix
user.conference
6
Which Version Is Loaded??
(IDS)
Odra: onstat Informix Dynamic Server 2000 Version 9.21.UC2
(Foundation)
Ebro: onstat Informix Dynamic Server 2000 Version 9.21.UC2
(MSGPATH)
Wed Oct 4 07:49:52 2000
07:49:52 Booting Language <builtin> from module <>
07:49:52 Loading Module <BUILTINNULL>
07:49:57 Informix Dynamic Server 2000 Version 9.21.UC2 Software Serial
Number AAB#J500705
07:50:12 Informix Dynamic Server 2000 Initialized -- Shared Memory Initialized
07:50:15 Physical Recovery Started
07:50:25 Physical Recovery Completed: 0 pages restored
Informix
user.conference
7
Which Version Is Loaded??
Ebro: cd /usr/informix/9_21/extend
Ebro: ls -l
drwxr-xr-x 4 informix informix 1024 Jul 19 08:08 ETX.1.30.UC5
drwxr-xr-x 4 informix informix 1024 Jul 19 08:07 TXT.1.10.UC5
drwxr-xr-x 4 informix informix 1024 Jul 19 09:02 VTS.1.20.UC1
drwxr-xr-x 2 informix informix 1024 Jul 19 07:42 ifxbuiltins.1.1
drwxr-xr-x 2 informix informix 1024 Jul 19 07:42 ifxmngr
drwxr-xr-x 2 informix informix 1024 Jul 19 07:42 ifxrltree.2.00
drwxr-xr-x 4 informix informix 1024 Jul 19 07:49 krakatoa
drwxr-xr-x 4 informix informix 1024 Jul 19 07:42 LLD.1.20.UC2
drwxr-xr-x 4 informix informix 1024 Aug 22 13:36
WEB.4.10.UC1
Informix
user.conference
8
Migration Issues
Informix
user.conference
9
Migration Issues: Things to Know
• You can not directly migrate from < 7.x versions
• Informix STRONGLY suggests that you first move
to the latest available 7.x version then move to 9.x
• While you may want to export/import your data,
in-place upgrades have been quite successful -just remember to wear your belt and suspenders
• HP-UX 11.0 - RUN_AS_ROOT.server script is
wrong
10
Links created for one library goes into /usr/lib instead
of /usr/lib/pa20_64; if left alone, engine will not come
up (“library not found” error). A “defect”Informix
has been
user.conference
entered
Migration Issues: Things to Know
• With IDS, CPU and user-defined VPs are run as
user “informix” while other VPs are still run as
root. This prevents UDRs from having root
access
• If you have auto-start scripts, you should modify
them so they “su” to “informix” before starting
the engine
• Install engine after version 2.2 client products
for “finderr” to work. The order is:
• tools
• network
• engine
Informix
user.conference
11
Migration Issues: Things to Know
• Precision change in float / smallfloat
to decimal conversion
smallfloat 8 -> 9
float 16 -> 17
• Default behavior of “create index” is now to
create detached indexes. 7.x attached indexes
are supported by the upgrade process
• Use the “DEFAULT_ATTACH” variable to
temporarily mimic 7.x behavior, it will be
discontinued however in a future release
Informix
user.conference
12
Migration Issues: Things to Know
• If using DRDA Gateway version 7.31.UC1 with
IDS 7.x, you must get the DRDA patch to avoid
defect #132619
• HADR has changed. DRAUTO is no longer
supported so after a HADR failure condition
is declared, the secondary server goes
to read-only mode
• You must manually convert the secondary
server to “standard” mode “onmode -d
standard”
• No changes to DRINTERVAL, DRTIMEOUT,
Informix
user.conference
DRLOSTFOUND
13
Migration Issues: Things to Know
• 9.2 ER and HPL -- may not support named types.
This should be in place for 9.3
Test dbexport / dbimport to see if more complex
named types work
• Shut down HADR / ER before upgrading
Informix
user.conference
14
Migration Issues: Things to Know
• System catalog changes - columns added, moved, data
types changed. New tables added and some dropped.
Sysindexes and systables now a view rather than tables
• Long identifiers!!
The 8/18 rule is dead (the crowd cheers!!)
user ids - 32 characters identifiers - 128 characters
table name, server name, database name, column name,
index name, synonym name, constraint name, procedure
name, dbspace name, blobspace name, optical cluster
name, trigger name, type name, routine language name,
access method name, operator class name, trace message
class name, trace message name, procedure variable
names
Informix
user.conference
15
Migration Issues: Things to Know
• Each user connection uses about 5% more
shared memory. Watch your shared memory
allocations - both instance and operating
system
• This can be offset by using MaxConnect
Informix
user.conference
16
Migration Issues:
New Reserved Words
•
•
•
•
•
•
•
•
•
CACHE
COSTFUNC
ITEM
SELCONST
INNER
JOIN
LEFT
LOCKS
RETAIN
Informix
user.conference
17
Migration Issues:
New ONCONFIG Parameters
•
•
•
•
•
•
•
•
•
•
•
ALLOW_NEWLINE
BLOCKTIMEOUT
DD_HASHMAX
DD_HASHSIZE
PC_HASHSIZE
PC_POOLSIZE
SBSPACENAME
SYSSBSPACENAME
STMT_CACHE
STMT_CACHE_SIZE
VPCLASS
•
•
•
•
•
•
•
•
•
JDKVERSION
JVPHOME
JVPLOGFILE
JVPPROPFILE
JVPJAVAVM
JVPJAVAHOME
JVPJAVALIB
JVPCLASSPATH
JVMTHREAD
Informix
user.conference
18
Migration Issues:
New ONCONFIG Parameters
VPCLASS
• Enables you to designate and create uniquely
named, user-defined, or system classes of VPs.
User-defined VPs have the same functional power
as CPU VPs
• User-defined VPs should be created to execute
user- defined routines and/or DataBlades
• Some DataBlades (Verity, Excalibur Text) require
their own VP as does the JVM
• User-defined VPs can be added/dropped on the fly
with the “onmode -p class_name” command
Informix
user.conference
19
Migration Issues:
New ONCONFIG Parameters
VPCLASS
Syntax tree:
VPCLASS name,num=X,[max=X,aff=(XY),noyield,noage]
• VP name is not case sensitive, options are order
independent, no whitespace, 128 char max/entry
• Must declare multiple JVPs to execute Java UDRs
in parallel (Unix)
• If using “noyield”, only declare “1” vp since the
UDR will execute serially, causing others to queue
for their turn
Informix
user.conference
20
Migration Issues:
New ONCONFIG Parameters
Use the VPCLASS parameter to replace the CPU
and AIO ONCONFIG parameters
VPCLASS cpu,num=3,max=10,noage
Comment out AFF_SPROC, AFF_NPROCS,
NOAGE, NUMCPUVPS, SINGLECPUVP
parameters
VPCLASS aio,num=5,max=10
Comment out NUMAIOVPS parameter
Informix
user.conference
21
Migration Issues: How do I
Migrate?
1. Stop database processing and force a change
to a new logical log
onmode -sy; onmode -l; onmode -c; onmode -ky;
2. Back up the instance(s) and critical configuration
files in $INFORMIXDIR
etc/
$ONCONFIG (s)**
ONCONFIG.std
sm_versions
sqlhosts**
tctermcap
termcap
22
/aaodir/adtcfg
/dbssodir/adtmasks
Informix
user.conference
Migration Issues: How do I
Migrate?
3. Restart the instance(s) and put them into singleuser mode to ensure all open transactions are
properly rolled back
oninit -sv
4. Verify data and index integrity with the oncheck
utility
(-cr, -ce -cc, -c [ I / D ] db_name)
5. Shut the instance(s) down again and create level 0
backup(s) and/or dbexports
6. Install the new IDS / Foundation software and
change relevant scripts, global parameters, config
files
Informix
user.conference
23
My Recommended Directory
Structure
7_3
9_21
scripts
koetari -- symbolic links
disks
/usr/informix
(also tapes)
odra -- symbolic links
ISA
Environment Variables:
INFORMIXDIR
ONCONFIG
INFORMIXSQLHOSTS
logs (current logs)
config_files
max_conn
24
/old_logs
Informix
user.conference
Migration Issues: How Do I
Migrate?
7. Change ALARMPROGRAM to [ null | no_log.sh ] if
using On-Bar or LTAPEDEV to /dev/null
8. Install any DataBlades
9. Restart each instance into quiescent mode and
monitor the MSG_PATH file. Sysmaster and
reserved pages conversion is automatic
(see $INFORMIXDIR/etc/dummyupds7x.sql). When
completed, a notice is written to MSG_PATH
10. Execute an “update statistics high” command on
the sysmaster database. Standard “update stats”
command on all others
Informix
user.conference
25
Migration Issues: How Do I
Migrate?
11. Verify data integrity with the oncheck
command
12. Change LTAPEDEV / ALARMPROGRAM back
to original value
13. Create a level 0 back-up
14. Let the users back in
If there are any problems -- RESTORE from tape
Informix
user.conference
26
SQL Statement Cache
Informix
user.conference
27
SQL Statement Cache:
Introduction
As implied, prepared and optimized SQL statements are
stored in a section of the virtual portion of shared memory.
Users executing identical DML SQL statements can pull the
pre-parsed query plan and even query data structures from
the cache
More complex queries (multiple columns, many filters in the
WHERE clause) benefit most from caching
Turned off by default, the STMT_CACHE $ONCONFIG
parameter and the “SET STATEMENT CACHE” SQL
command control how the caching process works.
Can be overridden with the “onmode -e” command
Informix
user.conference
28
SQL Statement Cache: How It
Works
1. Incoming SQL statements are hashed to a value
which is compared to other hash values in the
SSC section of the virtual portion of shared
memory
Caveats:
• statements only optimized at the PREPARE phase
• host variable names/placeholders are not included
in the hash algorithm
• white spaces and case of the statement is
significant
• session-specific parameters (OPTCOMPIND,
OPT_GOAL) are significant and will affect hash
value
Informix
user.conference
29
SQL Statement Cache: How It
Works
2. If hash value matches an existing value, the
cached copy is used. The existing copy
is NOT re-optimized
3. If a match does not exist, the statement is
evaluated for inclusion in the SSC
Qualifications for inclusion in the SSC:
• contains DML statements using built-in data
types and operators
• no UDRs/UDFs
• local database connections only
• no explicit temporary tables
Informix
user.conference
30
SQL Statement Cache: How It
Works
Qualifications for inclusion in the SSC (cont):
• “select *” statements can be fully expanded
to include all columns in the listed tables
• is not a statement generated by a stored
procedure
• no imbedded subselects in the SELECT statement
Informix
user.conference
31
SQL Statement Cache:
Enabling and Sizing
onmode -e {ENABLE|ON|OFF|FLUSH}
• ENABLE - Allows statement caching to take place. This
does not turn caching on, but allows it to be turned on
• ON - Turns on statement caching. Caching must first be
enabled through the STMT_CACHE parameter or
“onmode” command
• OFF - Turns off the SSC and immediately disables
sharing. Cached statements that are in use remain in
the SSC until their use count reaches zero
• FLUSH - Active statements are marked and
subsequently flushed when they are released. When
the SSC is turned off, sharing is immediately disabled
Informix
user.conference
32
SQL Statement Cache:
Enabling and Sizing
STMT_CACHE $ONCONFIG parameter:
0 - SQL Statement Cache disabled (default)
1 - SSC enabled though sessions must explicitly call
for its use
2 - SSC enabled for all sessions unless explicitly denied
on a session-by-session basis
Set the STMT_CACHE environment variable to “0” (off)
or “1” (on)
Execute a “set statement cache [ on | off ]”
command
Informix
user.conference
33
SQL Statement Cache:
Enabling and Sizing
STMT_CACHE_SIZE $ONCONFIG parameter:
determines the size of the SSC in KBs,
default = 524 kb
If SSC is full and all statements are active, if
additional statements qualify for inclusion the
SSC will expand to hold them. As a statement’s
user counts drop to zero, the SSC will flush them
and reduce back to STMT_CACHE_SIZE
Informix
user.conference
34
SQL Statement Cache:
Monitoring and Tuning
• “onstat -g cac stmt”
displays size of the SSC
the cached statements,
who’s currently
executing a statement,
and the number of times
it’s been used
• The “hash” column is
not the statement’s hash
value, rather the hash
bucket in which the
statement resides
• To tune, monitor the size
of the SSC over time,
then resize as
appropriate
Informix
user.conference
35
SQL Statement Cache:
Monitoring and Tuning
Use the STMT_CACHE_DEBUG environment
variable to trace what happens with any given SQL
statement
export STMT_CACHE_DEBUG=debug_value:path_to_file
debug_value can be “1” (basic) or “2” (extended)
Informix
user.conference
36
SQL Statement Cache:
Monitoring and Tuning
SSC identity (OK:status=1): select * from yoyo
SSC link (semantic error): select * from yoyo
SSC identity (stmt not found): create table yoyo (col1 int) in mustang_2
SSC qualify (invalid stmt type): create table yoyo (col1 int) in mustang_2
SSC identity (stmt not found): insert into yoyo values (1)
SSC qualify (OK): insert into yoyo values (1)
SSC key insert (OK): insert into yoyo values (1)
SSC full insert (OK): insert into yoyo values (1)
SSC identity (stmt not found): insert into yoyo values (2)
SSC qualify (OK): insert into yoyo values (2)
SSC key insert (OK): insert into yoyo values (2)
SSC full insert (OK): insert into yoyo values (2)
SSC identity (OK:status=1): insert into yoyo values (2)
SSC link (OK): insert into yoyo values (2)
SSC identity (stmt not found): drop table yoyo
SSC qualify (invalid stmt type): drop table yoyo
Informix
user.conference
37
What Are Fuzzy
Checkpoints???
Informix
user.conference
38
Checkpoints
IDS introduced “fuzzy” operations and
checkpoints to increase transactional throughput
Two types of checkpoints:
• Full or “sync”
• Fuzzy
Informix
user.conference
39
Steps of a Sync Checkpoint
1. Engine blocks threads from entering “critical sections”
of code
2. The page cleaner thread flushes the physical log buffer
to log on disk
3. The page cleaner threads flush to disk all modified pages
in the buffer pool (chunk write)
4. The checkpoint thread writes a checkpoint record
to the logical log buffer
5. The logical log buffer is flushed to the current logical log
file on disk
6. The physical log on disk is logically emptied
(current entries can be overwritten)
7. The checkpoint thread updates the reserved pages
with the checkpoint record information
Informix
user.conference
40
What Causes Sync Checkpoints
to Occur?
•
•
•
•
•
•
Physical log becomes 75% full
“onmode -c” or “-ky”
Administrative actions (adding dbspaces, altering tables)
A backup or restore operation using ontape or ON-Bar
End of fast recovery or full recovery
Reuse of a logical log containing the oldest fuzzy
operation not yet synced to disk
• LTXHWM reached with fuzzy transactions
• Through the onmonitor menu
Good and bad:
logically and physically data, interrupts user activity,
expensive (I/O hit), must tune to reduce duration
Informix
user.conference
41
Fuzzy Checkpoints
Record “results” of fuzzy operations in the logical
log
Goal: reduce or eliminate checkpoint
interruptions, eliminate the physical log
(over 3 - 4 phases)
Cost: increased logical recovery time
Note: Fuzzy checkpoints are not used
in HADR environments
Informix
user.conference
42
Fuzzy Checkpoints
Things to know:
• Inserts, updates, delete DML statements
• Currently supports “built-in” data types only
(character, numeric values)
• Logged databases
• Not “old” pages. Determined by page timestamp
(4 byte, cycles 2 gb -> -2 gb -> 0)
• No BEFORE images are generated in the physical
log
• During “fuzzy” checkpoints, buffered information
on fuzzy operations is **not** flushed to disk
• After a fuzzy checkpoint, disks are not
physically consistent
Informix
user.conference
43
Steps of a Fuzzy Checkpoint
1. Engine blocks threads from entering “critical sections”
of code
2. The page cleaner threads flush to disk all **non-fuzzy**
modified pages in the buffer pool (chunk write)
Note: MLRU queues still full
3. A Dirty Page Table (DPT) is constructed containing
entries (buffer addresses) for fuzzy operations
4. The DPT is flushed to the current logical log on disk
4A. The page cleaner thread flushes the physical and
logical log buffers to disk
5. The checkpoint thread updates the reserved pages
with the checkpoint information including the Log
Sequence Number (LSN).
6. The physical log on disk is logically emptied
(current entries can be overwritten)
Informix
user.conference
44
Log Sequence Number (LSN)
• A value representing a position in the logical log
(log # and log position)
• Stored in each dirty buffer heading containing
fuzzy operation results along with a
newly-stored timestamp
• The oldest LSN is tracked in shared memory
because it contains the oldest fuzzy operation.
Oldest LSN can not be further back than 1
logical log and 2 checkpoints
• Logs after the LSN can not be freed for re-use
until a checkpoint has occurred
Net Net -- large logical logs are GOOD!!!
Informix
user.conference
45
What Causes Fuzzy Checkpoints
to Occur?
•
•
•
•
Checkpoint interval has elapsed
Physical log becomes 75% full
onmode -c fuzzy
Reuse of a logical log containing the last
checkpoint (either fuzzy or sync)
Informix
user.conference
46
Recovery - Sync Checkpoint
1. Physical log data used to return all disk pages
to original “synced” state (physical restore)
2. The most recent checkpoint (sync) record is
located in the logical log files
3. All subsequent logical log records
are rolled forward
4. Uncommitted transactions are rolled back
Informix
user.conference
47
Recovery - Fuzzy Checkpoint
1. Physical restore proceeds as normal however
disk pages are not physically consistent since
there are modified pages that were not logged
in the physical log
2. Logical recovery begins in two phases:
• Phase A -- reapply DPT records
• Phase B -- post checkpoint records
Informix
user.conference
48
Recovery - Fuzzy Checkpoint
Phase A - Logical Recovery:
1. Locate DPT created before last fuzzy checkpoint
2. Selectively apply fuzzy operations from the
DPT/logical log from the oldest remaining logical
log up to the most recent checkpoint
Conditional update based on page timestamp - if
DPT record is “newer” than page timestamp, then
the change is applied
Result: similar to end of physical restore when
using sync checkpoints
Informix
user.conference
49
Recovery - Fuzzy Checkpoint
Phase B - Logical Recovery:
1. Beginning with last checkpoint, roll forward
subsequent logical log records.
2. Uncommitted transactions are rolled back
Result: similar to the logical restore when using
sync checkpoints. Disks are physically and
logically consistent to last recorded transaction
Informix
user.conference
50
Fuzzy Checkpoints
• Buffer structures, logical log records, and
reserved pages are automatically upgraded
when migrating to 9.x
• Fuzzy checkpoints enabled by default. Set the
NOFUZZYCKPT environment variable to true/on/1
to disable them prior to initializing shared
memory
• Set the TRACEFUZZYCKPT environment variable
to trace fuzzy checkpoints. It will affect instance
performance but will log in the MSGLOG the
number of buffers that remain dirty after a
checkpoint is completed. It also lists the location
Informix
of the old LSN
user.conference
51
Large Objects, SBSpaces
and
Dynamic Lock Allocation
Informix
user.conference
52
Large Objects and SBSpaces
IDS has 4 types of LOs:
• Simple - BYTE & TEXT
• Smart - CLOB & BLOB
I/O size
Buffer
pool
usage
Simple
Simple
configurable by
page size
partition blobs use
the buffer pool,
blobspace blobs
use private buffers
Updating inserts and deletes
only ; there are no
direct updates
Data
one large-object
row/blob can be referenced
mapping by one data row
Smart
Smart
configurable by page size, the data
is still stored in database server
sized pages, but the I/O is
configurable
you can choose between light I/O or
normal buffering when the smartlarge-object is opened
updates are done in place or moved
as needed
one large-object can be referenced
by one or more sources
Informix
user.conference
53
Large Objects and SBSpaces
Simple
Logging
Method
Size
Locking
Granularity
Reads and
writes
Smart
logging depends
logging can be turned on or off at the
on the table where large-object level
the large-object is
stored
2 GB maximum
4 TB maximum
all or nothing
can lock portions or all of the object
only the whole
all or a portion of the large-object can
large-object can
be read
be read
Fragmentation all large-objects
ability to store the large-objects for
for a table are
one table in multiple sbspaces
stored in the same
blobspace
• Access to C/BLOBs is faster due to “portion-level” addressing
• Default characteristics are easy to override
• With sufficient resources, C/BLOBs are transactionally recoverable
Informix
user.conference
54
Large Objects and SBSpaces
Smart LOBs are stored in “smart” BLOBSpaces
(sbspace) which contain meta-data as well as
user data
header (reserved) pages
User Data
metadata area
Size and location of
the metadata area is
configurable at
sbspace and/or
chunk creation
Metadata is always
logged regardless of
database or sbspace
logging mode
Informix
user.conference
55
Large Objects and SBSpaces
onspaces -S name -g pageunit -p pathname -o offset
-s size [ -m pathname offset ] -Ms mdsize -Mo mdoffset
-Df default list
-Mo mdoffset offset, in kbs, into the disk partition where
metadata will be stored
-Ms mdsize the size, in kbs, of the metadata area for the
initial chunk, remainder is user data space -- not
recommended though possible. Each SLO needs
~500 bytes of metadata space
-Df default list default specifications for objects stored
in the sbspace. Comma separated, in double quotes (“ “)
Informix
user.conference
56
Large Objects and SBSpaces
Tag
ACCESSTIME
AVG_LO_SIZE
Values
ON or
OFF
For
Windows
NT:
4 to 2^31
Default
OFF
8
For UNIX:
2 to 2^31
BUFFERING
ON or
OFF
LOCK_MODE
RANGE or BLOB
BLOB
57
ON
Description
ON – instance tracks access time
of SLOs stored in the sbspace.
Average size, in kbs, of SLOs
stored in the sbspace. Used to
calculate metadata area size if -Ms
option is not specified.
Error 131 is returned if you run out
of metadata area in the sbspace.
To allocate additional chunks to
the sbspace that consist of
metadata area only, use the -Ms
option.
ON - instance uses resident
portion buffer pool for SLO I/O
operations.
OFF - instance uses light I/O
buffers in the virtual portion
(lightweight I/O operations) –
PREFERRED!!!!
RANGE - only a range of bytes in
the SLO is locked.
BLOB - the entire SLO is locked.
Informix
user.conference
Large Objects and SBSpaces
Tag
LOGGING
Values
ON or
OFF
Default
OFF
EXTENT_SIZE
4 to
2^31
16
MIN_EXT_SIZE
2 to
2^31
4 to
2^31
4
NEXT_SIZE
16
Description
ON - instance logs changes to the
SLOs in the user data. Create a
level-0 backup of the sbspace after
creating/changing
Size, in kbs, of the first disk
allocation for SLO storage when
you create the table.
Let the system select the
EXTENT_SIZE value.
Minimum amount of space, in kbs,
to allocate for each SLO.
Size, in kbs, of the next disk
allocation for smart large objects
when the initial extent becomes
full.
Let the system select the
NXT_SIZE value.
Create a level 0 backup after creating a sbspace
Informix
user.conference
58
Large Objects and SBSpaces
To add a chunk, you can specify whether the
chunk will contain only user data, metadata,
or both:
onspaces -a sbspace -p pathname -o offset -s size
[-Ms mdsize -Mo mdoffset | -U ]
[ -m pathname offset ]
To drop the sbspace:
onspaces -d sbspacename -f
(force)
Informix
user.conference
59
Large Objects and SBSpaces
SLOs can be distributed in round-robin form
among sbspaces like “regular” data
create table products
(prod_sku int,
prod_desc clob,
prod_photo blob)
put prod_desc in (sbspace_1, sbspace_2),
prod_photo in (sbspace_9, sbspace_10),
fragment by expression
(prod_sku < 1000 in db_1,
prod_sku >= 1000 in db_2);
Creates a 72 byte
descriptor “handle”
as opposed to 56
bytes with “simple”
LOs
Informix
user.conference
60
Large Objects and SBSpaces
Informix recommends that you convert “simple”
LOs to their “smart” counterparts as soon
as feasible
You can, in many cases, use the alter table
command to make the change(s):
alter table t_name modify col_name [ clob | blob ]
put column_name in (sbspace(s));
Informix
user.conference
61
Large Objects and SBSpaces
Monitoring / tuning
oncheck -cs -cS / -ps -pS to print/view metadata
-ce / -pe has additional sbspace information
onstat -g smb s | c | h | e | t
smb s
sbspace summary information
smb c
sbspace chunk information
smb fdd LO file descriptor table
smb lod LO header table
smb t
timing statistics for enabled server,
not well documented
smb e
undocumented
onstat -k has additional columns to document SLO byte
range locks
onstat -d has additional chunk-related columns for sbspaces
Informix
user.conference
62
Large Objects and SBSpaces
Backing up and restoring SLOs and sbspaces now
operates like regular dbspaces -- at the page level.
As a result, you can now have granular and truly
incremental SLO/sbspace backups.
Sbspace descriptor partition
header (reserved) pages
Chunk adjunct partition
Level 1 backup partition
metadata area
Level 2 backup partition
LO header partition
user-data free-list partition
Informix
user.conference
63
Large Objects and SBSpaces
Other Sbspace
spaces header
Metadata
pages
Archive
sbspace
descriptor pages pages
Backup:
• Metadata LO headers are read and a list made of pages to be
backed up
• All individual page timestamps are compared too since updates
can occur without affecting the LO header timestamp
• Qualifying data is sent to the backup program through large
buffer accesses
Restore:
• Sbspace header and metadata information is restored
• Archive desriptor pages contain page addresses and extent
sizes for the sbspace pages that follow. This is held in shared
memory during the operation
• Sbspace pages are restored to their correct places
Informix
user.conference
64
Dynamic Lock Allocation
In IDS, the locking mechanism changes. LOCKS is
now a starting allocation (resident portion)
If the lock table fills, additional locks are allocated (x
16) minimum allocation: smaller of current lock table
or 100,00 (virtual portion)
To prevent lock table flooding, range locks will be
upgraded to the whole SLO if a single transaction
uses > 10% of lock table
“Byte range” locks are what lock portions of a SLO.
They are sorted lists of rowids, partition #s, and byte
address ranges
Informix
user.conference
65
Dynamic Lock Allocation
• Range locks can be joined or split as needed if ranges to
be locked adjoin, overlap, or become separate
• Ranges locks are not stored in the lock hash table, rather
an “intent” lock is placed for the LO header. Byte range
locks are linked to associated hash intent lock then sorted
by byte number
• Byte ranges locks allocated if:
• < 50% of lock table is being used
• 50% <= lock table <= 70%, whole SLO lock will be
attempted, if unsuccessful, byte lock allocated
• > 70% whole SLO locks only, existing locks will be
upgraded automatically
Informix
user.conference
66
MaxConnect
Informix
user.conference
67
MaxConnect
Max Connect is a middle-ware communications
component that removes the overhead of
managing user connections from the instance
Helps you get scale your user connections past
the effective 2,000 user connection limit without
any change or resource impact to the database
server
It supports 7.3 and 9.2x+ Unix ports of the engine
but accepts user connections from any platform
NO CHANGE to existing applications!!
Informix
user.conference
68
MaxConnect Performance
BaaN IVc World record:
• 11,445 BRUs on HP N4000 (8CPU, 16GB) and IDS
• 21% better than Oracle on identical HP system
• Beat Oracle’s 64-CPU Sun E10000 result
• 18,650 database connections using 98 transports
!!
Informix
user.conference
69
MaxConnect Architecture
MaxConnect
IDS/Fnd
MaxConnect
SQL
Clients
For large, hardwarepartitionable SMP systems
SQL
Clients
MaxConnect
IDS/Fnd
MaxConnect
Database server system
Informix
user.conference
70
MaxConnect Configuration
• A MaxConnect instance connects to only one
DB server instance
• Connections are managed through the
SQLHOSTS file with a new connection
protocol -- imc
• Clients change hostname and portaddress to
point to the MaxConnect instance, instance
name remains the same
• MaxConnect instance has an ADMIN entry, a
client-facing entry, and a DB server-facing entry
• DB instance has a DBSERVERALIAS and a
DBSERVERNAME entry
Informix
user.conference
71
MaxConnect Configuration
• Client SQLHOSTS file:
ebro_tcp
ontlitcp
maxcon_1
1526
• MaxConnect SQLHOSTS file:
ebro_tcp
ontlitcp
ebro_db
ontliimc
ebro_adm onsoctcp
maxcon_1
cronus
maxconn_1
1526
1575
1600
• Server SQLHOSTS file:
ebro
onipcshm cronus ebro_ph
(DBSERVERNAME)
ebro_tcp
ontliimc
cronus 1575 (DBSERVERALIAS)
Informix
user.conference
72
MaxConnect Configuration
NETTYPE entry changes:
ipcshm,1,20,CPU
# tlitcp,5,200,CPU
tliimc,1,2000,CPU
MaxConnect instance environment variables:
IMCONFIG - location of MaxConnect config file default is $INFORMIXDIR/etc/IMCconfig
IMCSERVER - Client-side MaxConnect instance
name
IMCADMIN - Admin-side MaxConnect instance
name
Informix
user.conference
73
MaxConnect Configuration
MaxConnect config file contains:
IMCLOG - location of MaxConnect message log
IMCWORKERTHREADS - # of worker threads,
range = 1 -> 64
IMCWORKERDELAY - amt of time MaxConnect will
wait and pool messages to transmit to DB server
default = 0, range = 0 -> 100,000 ms
IMCTRANSPORTS - # of transport connections to DB
server. Default = 2, range = 1 -> 64, ratio = 1/100
users
Informix
user.conference
74
MaxConnect Tuning
• Use “onstat -g imc”, the imcadmin utility, or the
ISA to monitor and tune MaxConnect
• Tune the NETTYPE sessions parameter by
monitoring the q-exceed and alloc/max values.
May also need to tweak the
IFX_NETBUF_PVPOOL_SIZE variable
• Tune IMCTRANSPORTS by monitoring blocked
or partial writes
• Tune IMCWORKERTHREADS by monitoring
AvgQlen
If > 5, increase number of threads
• Tune IMCWORKERDELAY by summing and
comparing historgrams against AvgQlen
Informix
user.conference
75
Complex and User-Defined
Data Types
Informix
user.conference
76
Complex and User-Defined Data
Types
Data Types
Existing Built-in
Types
Extended Data
Types
User-Defined
Complex
New Built-in
Types
Opaque
Boolean
Int8
Serial8
Lvarchar
Distinct
Collection
Multiset
Row Data Type
Named
List
Set
Unnamed
Informix
user.conference
77
Built-in Data Types
New Built-In:
• int8 (8 bytes)
• serial8 (8 bytes)
• Range is -9,223,372,036,854,775,807
to 9,223,372,036,854,775,807
• must add UC to serial8 to ensure uniqueness
• one serial8 and serial per table
• boolean
valid values: “t”, “f”, null. case sensitive
• lvarchar (variable length character data type;
32k maximum)
Informix
user.conference
78
Complex and User-Defined Data
Types
Data Types
Existing Built-in
Types
Extended Data
Types
User-Defined
Complex
New Built-in
Types
Opaque
Boolean
Int8
Serial8
Lvarchar
Distinct
Collection
Multiset
Row Data Type
Named
List
Set
Unnamed
Informix
user.conference
79
Complex Data Types - Row
Row DataTypes
analogous to C structure, come in two “kinds”
NAMED - strongly typed, ID’ed by name, has inheritance, used
to build columns and tables
UNNAMED - weakly typed, ID’ed by structure, no inheritance,
used to build columns
Can contain built-in, collection, opaque, distinct, another
row type data types
• Caveat: no serial or serial8
ADVANTAGES
Less coding
Refers to a group of elements
by a single name
Intuitive
DISADVANTAGES
More complex
Not simple SQL
Sys Adm is more complex
No “alter type” statement, must
drop and recreate
Informix
user.conference
80
Complex Data Types - Row
Row DataTypes
Named:
create row type name_t
(fname char(20), lname char(20));
create row type address_t
(street_1 char(20), street_2 char(20),
city char(20), state char(2), zip char(9));
create table student
(student_id serial,
name name_t,
address address_t,
company char(30));
Unnamed:
ROW (a int, b char (10))
Note: is also equal to
ROW(x int, y char(10))
create table part
(part_id serial,
cost decimal,
part_dimensions row
(length
decimal,width decimal,
height decimal, weight
decimal));
Informix
user.conference
81
Complex Data Types - Row
Using Named Row Types in SQL statements:
Use of datatype
keyword
Insert statement:
insert into student values (1234,
row (“John”,”Doe”)::name_t, row ("1234 Main Street","",
"Anytown","TX","75022")::address_t, "Informix Software")
Select statement:
Cast the row type!
select * from student where name.lname matches "Doe”;
Result set:
Access data with
student_id
name
address
company
1234
ROW('John
','Doe
ROW('1234 Main Street ','
Informix Software
')
,'Anytown
‘dot’ notation
','TX','75022
')
Informix
user.conference
82
Complex Data Types - Row
To drop a named row type:
drop row type address_t restrict;
Informix
user.conference
83
Complex Data Types - Collections
Grouping of elements of the same datatype
(char, int), max size = 32 KB
Used when
• The data is meaningless without the context of
the other members in the collection
(e.g., golf scores, to do list, set of names)
• Individual data elements are not likely to be
directly queried by position
• The maximum number of data elements is less
than 32
Can be null
Informix
user.conference
84
Complex Data Types - Collections
Three kinds of collections:
Set - unordered, no duplicates allowed
set {“apple”, ”orange”, ”grapefruit”, “plum”}
Multiset - unordered, duplicates allowed
multiset {“apple”, “orange”, “grapefruit”, “apple”,
“plum”, “grapefruit”}
List - ordered, duplicates allowed
list {“apple”, “orange”, “grapefruit”, “apple”,
“plum”, “grapefruit”}
Informix
user.conference
85
Complex Data Types - Collections
create table class
(class_id serial, class_name varchar(60), description lvarchar,
prereqs set(char(20) not null));
Insert syntax is similar to named row types:
insert into class values (300, “Performance and Tuning”, “Covers advanced
information on tuning the Informix Dynamic Server”,
(SET{“RDD”,”BSQL”}));
Use the “in” keyword to query values in a collection
select * from class where (“ASQL”) in prereqs;
define xyz char(20)
define set_var set(char(20))
select prereqs into set_var from class where class_id = 300
foreach del_set_cursor for
select * into xyz from table(set_var)
if xyz matches “RDD” then
delete from table(set_var) where current of del_set_cursor
end if
86
end foreach
Informix
user.conference
Complex Data Types - Collections
You can not update one element in a collection,
you must replace the whole collection:
update class set prereqs = (set{“RDD”,”ASQL”,”BSQL”})
where class_id = 300;
update class set prereqs = set_char where class_id =
300;
Informix
user.conference
87
Complex and User-Defined Data
Types
Data Types
Existing Built-in
Types
Extended Data
Types
User-Defined
Complex
New Built-in
Types
Opaque
Boolean
Int8
Serial8
Lvarchar
Distinct
Collection
Multiset
Row Data Type
Named
List
Set
Unnamed
Informix
user.conference
88
User-Defined Data Types - Distinct
Two user-defined data types (UDTs):
Distinct
• data type modeled on an existing data type
• has a unique name to distinguish it from other similar
“types”
• inherits the internal structure from the source type
• might have operations and casts defined over its
source type
Opaque
• data type that is unknown to the database server
• you must define the internal structure, functions,
and operations
Informix
user.conference
89
User-Defined Data Types - Distinct
create distinct type dollar as decimal;
create distinct type aus_dollar as decimal;
create table sales
( sku int,
sales_date date,
us_sales dollar,
aus_sales aus_dollar);
insert into sales values (1234, today, 15.0::dollar,0::aus_dollar);
insert into sales values (5678, today, 0::dollar, 75.0::aus_dollar);
select sku, (sum(us_sales) + sum(aus_sales))
from sales where sales_date = today
group by 1;
error: 674 - routine (plus) can not be resolved
90
Informix
user.conference
User-Defined Data Types - Distinct
Need to create some UDFs that handle the type and
value conversion for you:
create function usdlr_to_ausdlr(parm1 dollar)
returning aus_dollar
specific usd_to_ausd;
return (parm1::decimal * 1.8)::aus_dollar;
end function;
create function ausdlr_to_usdlr(parm1 aus_dollar)
returning dollar
specific ausd_to_usd;
return (parm1::decimal / 1.8)::dollar;
end function;
select sku, (sum(us_sales) +
sum(ausdlr_to_usdlr(aus_sales))::dollar)
from sales where sales_date = today
group by 1;
91
Informix
user.conference
User-Defined Data Types - Opaque
An opaque data type stores a single “value” that
cannot be divided into components by the engine.
Implemented as C or Java structures and
manipulated by a set of routines written in C or
Java
An opaque “value” is stored in its entirety by the
engine without any interpretation of the contents
or its structure
All access to an opaque type is through functions
written by the user. You define the storage size of
the data type and input and output routines
Informix
user.conference
92
User-Defined Data Types - Opaque
1. Create the C / Java data structure to represent
the internal data structure
2. Write the support functions in C / Java
3. Register the opaque data type with the “create
opaque type” statement
create opaque type type_name ( internallength = length,
alignment = num_bytes);
length is in bytes, alignment = 1,2,4,8 bytes (default = 4)
create opaque type type_name ( internallength = variable,
maxlen = length);
length - default = 2 KB, max value = 32 kb
4. Register the support functions with the “create
function” statement. If in Java, will be stored
in sbspace(s)
Informix
user.conference
93
User-Defined Data Types - Opaque
create opaque type my_type(internallength=8, alignment=4);
create function support_in(lvarchar)
returning my_type with (not variant);
external name “/funcs/my_type.so”
language C
end function;
create implict cast (lvarchar as my_type with support_in);
5. Grant access to the opaque data type and
support functions
6. Write any user-defined functions needed to
support the opaque data type - input, output,
destroy, compare, aggregates, send, receive,
etc.
7. Provide any customized secondary-access
Informix
user.conference
methods for creating indexes
94
Casts and Casting
Informix
user.conference
95
Casts and Casting
• Casts allow you to make comparisons between values of
different data types or substitute a value of one data type
for a value of another data type.
create function ausdlr_to_usdlr(parm1 aus_dollar)
returning dollar
specific ausd_to_usd;
return (parm1::decimal / 1.8)::dollar;
end function;
• Engine provides a number of “built-in” casts (int to
decimal, numeric to char, etc.) for most built-in datatypes
• Must create user-defined casts for user-defined types.
Must be unique with respect to source and target data
types
• Can not create casts for collections, LOBs, or unnamed
row types
Informix
user.conference
96
Casts and Casting
Two kinds of user-defined casts:
explicit
. . . where price >= (cast aus_dollar as dollar) . . . .
. . . return (parm1::decimal / 1.8)::dollar;
create explicit cast (aus_dollar as dollar with
aus_dlr_to_us_dlr);
implicit
create implicit cast (aus_dollar as dollar);
create implicit cast (aus_dollar as dollar with
aus_dlr_to_us_dlr);
Automatically invoked when:
one data type is passed to a user-defined routine whose
parameters are of another data type.
expressions are evaluated that need to operate on two similar
Informix
data types.
user.conference
97
Casts and Casting - Implicit Casts
select sum(us_sales) + sum(aus_sales) from sales;
# 674: Routine (plus) can not be resolved.
create implicit cast (aus_dollar as dollar);
select sum(us_sales) + sum(aus_sales) from sales;
(expression) 120.00
drop cast (aus_dollar as dollar);
create implicit cast (aus_dollar as dollar with ausdlr_to_usdlr);
select sum(us_sales) + sum(aus_sales) from sales;
(expression) 80.00
Informix
user.conference
98
Casts and Casting - Explicit Casts
select sum(us_sales) + sum(aus_sales) from sales;
# 674: Routine (plus) can not be resolved
create explicit cast (aus_dollar as dollar);
select sum(us_sales) + sum(aus_sales) from sales;
# 674: Routine (plus) can not be resolved
select sum(us_sales) + sum(aus_sales)::dollar from sales;
(expression) 120.00
drop cast (aus_dollar as dollar);
create explicit cast (aus_dollar as dollar with
ausdlr_to_usdlr);
select sum(us_sales) + sum(aus_sales)::dollar from sales;
(expression) 80.00
Informix
user.conference
99
Casts and Casting
Previous examples were “straight” casts. You
can also create cast “functions” to cast types
with dissimilar data structures
1. write the cast function in C / Java / SPL
create function opaque_to_opaque (input_arg my_type_1)
returns my_type_2
return cast(cast(input_arg as lvarchar) as my_type_2);
end function;
2. register the cast function with the
“create function” command
3. register the cast with the “create cast” command
create explicit cast (my_type_1 as my_type_2 with
opaque_to_opaque);
Informix
user.conference
100
User-defined Routines
Informix
user.conference
101
UDRs
There are UDFs and UDPs
Can be internal or external in nature. Internal are
written in SPL, external can be written in C or
Java.
If written in Java, compile into a class then .jar file.
When registered in the engine, .jar file will be
brought into a sbspace then executed when needed
by the JVM in the engine
If written in C, create a shared object library in a
directory owned by “informix” with 755 permissions
Informix
user.conference
102
UDRs
UDRs should be concise and precise. Don’t want
engine wasting resources plowing though
verbose code. The values returned should be
relevant
from a business perspective (e.g. proper domain)
Make sure you include error handling in the code
**ALWAYS** use a “specific” name, or alias,
for every UDR to properly identify each one - used
to drop, grant, or revoke permissions as well as
to permit function overloading
Informix
user.conference
103
UDRs
Function overloading occurs when two or more
functions have the same name but different
signatures
signature = UDR name and parameter list
create function plus (in_1 dollar, in_2 aus_dollar) . .
create function plus (in_1 aus_dollar, in_2 lira) . . .
create function plus (in_1 lira, in_2 aus_dollar) . . .
{Note: these probably should be formalized into
user-defined aggregates}
When properly registered, instance will use the
data types passed to determine which UDR
should be used
Informix
user.conference
104
UDRs
create function routine_name (param_list)
{ returns | returning } typename
[ specific specific_name ]
[ with (internal | handlesnulls | [ [not] variant]
| class=“vp_class”) ]
external name ‘full_path_name_of_file‘
language language [ [not] variant ]
end function;
in the $ONCONFIG file:
VPCLASS fince_vp ,num=2 # vps for finance
UDRs
or
onmode -p +2 fince_vp
105
USE UDVPs FOR UDRs!!!!
Informix
user.conference
UDRs
To drop UDRs, must either use full signature
drop function plus(lire, aus_dollar);
or the “specific” name
drop specific function lir_to_ausdlr;
Informix
user.conference
106
UDRs
Default behavior of UDRs is single-threaded, can
be “paralellized” if conditions are met:
• only in DSS environments (PDQPRIORITY > 1)
• parallelizable keyword is added to UDR
creation statement
• external UDRs only (C / Java) that use PDQ
thread-safe SAPI calls (see DataBlade manual
for list of SAPI calls)
• no complex types in input parameters
or return values
• multiple fragments must be scanned
Informix
user.conference
107
UDRs
• Not directly called (singleton execution)
• not an interator - called in loops to return multiple
values similar to with resume in SPL
• on some UDRs, you can add “selectivity” and
“cost” modifiers that will affect optimizer
activities and query plans used. Not really
familiar with this
Informix
user.conference
108
UDRs
create function bigger(int,int) returns int
specific big_paralel
with(parallelizable,class=“test_vps”) external
name
“/path/plludr.bld(bigger)” language C;
alter function bigger (int,int) with (add
parallelizable,
add class=“test_vps”);
Can monitor the parallel execution of UDRs through
• “set explain out” command
• the ISA
• onstat -g mgm
• onstat -g ses sess_id
Informix
user.conference
• onstat -g ath
109
DataBlades and
the Blade Manager
Informix
user.conference
110
DataBlades
• Datablades extend the functionality of the engine
by adding new UDTs, casts, interfaces, UDRs, error
messages, clientcode (where necessary)
to manipulate the UDTs
• Functionality available through SQL, SPL, API calls
to external functions
• Can be mixed and matched to support any
application
• While the code is loaded at an instance level, blade
registration occurs at a database level. With
registration information contained in the database
system tables, if you drop the database, blades
Informix
become unregistered
user.conference
111
DataBlades
Install the blade in $INFORMIXDIR/extend
(tar, cpio, ?)
May have its own “install” script to brand
binaries, use serial # / license key from engine
install unless third-party requires unique key
setup
Use the Blade Manager to manage blade
registration graphical version available with NT
and ISA command line only on Unix / Linux
Is case sensitive
Informix
user.conference
112
DataBlades
BladeManager supports the following commands:
list [database] - print list of registered DataBlade modules
set user [user]
set server [server]
show modules - print list of DataBlade modules on server
show databases - print list of databases on server
show servers - print list of servers
show client - print DataBlade modules with files on client
register [module] [database]
unregister [module] [database]
add client [module] - install DataBlade module's client files
del client [module] - remove DataBlade module's client files
info [module] - print information about a DataBlade module
del logs - delete all the log files
show log - prompt to display a log
show last log - display log from last action
help, ?
Informix
bye, quit, exit
user.conference
set confirm on|off
113
DataBlades
mustang>show database
Databases on server:
agg1
dcs_demo
dps_demo
ireach
kiosk
media360
my_test_db
visclass20
mustang>list media360
DataBlade modules registered in database media360:
lld.1.20.tc2
TXT.1.10.TC5
ifxrltree.2.00
ifxbuiltins.1.1
web.4.10.TC1
VTS.1.20.TC1
Informix
video.2.00.TC2
user.conference
114
Introduction to Java
in the Server
Informix
user.conference
115
Java in the Server
J/Foundation is the marriage of the Java and
SQL languages
Can write internal or external Java routines that
are accessed natively by the engine
Applet
JavaBean
JavaBean
RMI | Corba | IIOP
Web Server
Java DBDK
JavaBean
EJB
Applet
JavaBean
JavaBean
Java
EJB
JVM
116
EJB
Informix
user.conference
Java in the Server
Informix provides a “Type 4” JDBC driver
Type 1 JDBC-ODBC bridge provides JDBC access
via most ODBC drivers
Type 2 A native API and partly Java driver that
converts JDBC calls into calls on the client API for
Oracle, Sybase, Informix, DB2 etc.
Type 3 A net protocol, all Java driver that translates
JDBC calls into a DBMS independent net protocol
which is then translated to a DBMS protocol by a
server
Type 4 A native protocol all-Java driver converts
JDBC calls into the network protocol used by
DBMSs directly. This allows a direct call from the
client machine to the DBMS server
Informix
user.conference
117
Java in the Server,
How It Works
Java UDRs execute on Java Virtual Processors
• Java VPs have the same capability as CPU VP
• It can process any SQL query
• Avoids the inter-process communication
overhead for executing Java UDRs
• The Java Virtual Machine (JVM) is embedded
directly into the VP code
Java runs threads in parallel
Dynamically load Java VPs
• Increase JVPs to balance application load
Informix
user.conference
118
Java in the Server,
How It Works
New ONCONFIG Parameters:
VPCLASS jvp,num=1
JVPJAVAHOME /usr/java
JVPHOME /opt/informix/extend/krakatoa
JVPPROPFILE /opt/informix/extend/krakatoa/.jvpprops
JVPLOGFILE /opt/informix/jvp.log
JDKVERSION 1.1
Informix
user.conference
119
Questions???
Informix
user.conference
120