HPL 9.40 UC5 express load problem
This thread is actually a continuation or actually an underlying
problem of a previous thread I posted yesterday "IDS 9.40 UC5/linux
update statistics crashes engine onlarge table".
I am copying a snapshot of a table from an HPUX system running IDS 9.21
FC7 with 15 million rows to a linux system running IDS 9.40 UC5 to
begin migration testing.
I used HPL to unload the data from the original server lets call it
hpux1. There were no errors in the unload job and the results were as
follows:
Database Unload Completed -- Unloaded 15612487 Records Detected 0
Errors
I created the table without indexes on the new server lets call it
linux1. I moved the unload flat files from hpux1 to linux1 using sftp
in binary mode. (I have checked the files for any transfer problems in
case that comes up there are none). I did a word count on the files
residing on linux1 to ensure the numbers matched what was unloaded from
hpux1 and the results were:
15612487 total
I created a load job on linux1 using ipload and the "gui", started the
load in the new table and it completed without error, the results
being:
13:32:13 Records Processed -> 14460000
Table 'rate_deck_entry' will be read-only until level 0 archive
Database Load Completed -- Processed 14461533 Records
Records Inserted-> 14461533
Detected Errors--> 0
Engine Rejected--> 0
Wed Dec 15 13:32:15 2004
The numbers dont match. This has been done twice both with the same
result ( have 2 different logfiles with the same results) using the
same flat files after completely rebuilding the instance both times. I
did not realize the numbers did not match until today after trying to
solve a crashing problem when running update statistics on this table.
Select max(entry_id) for both servers turns up some strangness as well:
hpux1 - 234940490
linux1- 2088513660
Anyone seen this before or have any ideas?
Here is the schema used to create the new table before the load:
create table "informix".rate_deck_entry
(
entry_id serial not null ,
rate_deck_id integer,
rate_id integer,
sequence_number integer,
start_digits char(20),
stop_digits char(20),
start_date datetime year to second,
stop_date datetime year to second,
start_time integer,
stop_time integer,
sunday smallint,
monday smallint,
tuesday smallint,
wednesday smallint,
thursday smallint,
friday smallint,
saturday smallint,
rate float,
weight float,
minimum_seconds smallint,
increment_seconds smallint,
description char(50),
type char(24),
status smallint,
date_added datetime year to second,
who_added char(24)
);
Our system and db config for linux1 are as follows:
Fedora Core 1 (with standard mainline kernel)
KERNEL VERSION : 2.4.28
GLIBC VERSION: glibc-2.3.2-101.4
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up 1
days 00:57:14 -- 241060 Kbytes
Configuration File: /opt/informix/etc/onconfig.repsvr01b
#**************************************************************************
## Licensed Material - Property Of IBM
#
# "Restricted Materials of IBM"
#
# IBM Informix Dynamic Server
# (c) Copyright IBM Corporation 1996, 2004 All rights reserved.
#
# Title: onconfig.std
# Description: IBM Informix Dynamic Server Configuration Parameters
#
#**************************************************************************
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace nameROOTPATH /dev/chunks/rootdbs # Path for device containing root
dbspace
ROOTOFFSET 0 # Offset of root dbspace into device
(Kbytes)
ROOTSIZE 393216 # Size of root dbspace (Kbytes)
# Disk Mirroring Configuration Parameters
MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH # Path for device containing mirroredroot
MIRROROFFSET 0 # Offset into mirrored device (Kbytes)
# Physical Log Configuration
PHYSDBS physdbs # Location (dbspace) of physical log
PHYSFILE 393110 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 43 # Number of logical log files
LOGSIZE 2000 # Logical log size (Kbytes)
# Diagnostics
MSGPATH /usr/informix/online.log # System message log file path
CONSOLE /dev/console # System console message path
# To automatically backup logical logs, edit alarmprogram.sh and set
# BACKUPLOGS=Y
ALARMPROGRAM /usr/informix/etc/alarmprogram.sh # Alarm program path
TBLSPACE_STATS 0 # Maintain tblspace statistics
# System Archive Tape Device
TAPEDEV /dev/null # Tape device path
TAPEBLK 32 # Tape block size (Kbytes)
TAPESIZE 10240 # Maximum amount of data to put on tape
(Kbytes)
# Log Archive Tape Device
LTAPEDEV /dev/null # Log tape device path
LTAPEBLK 32 # Log tape block size (Kbytes)
LTAPESIZE 10240 # Max amount of data to put on log tape
(Kbytes)
# Optical
STAGEBLOB # Informix Dynamic Server staging area
# System Configuration
SERVERNUM 0 # Unique id corresponding to a OnLineinstance
DBSERVERNAME repsvr01b # Name of default database server
DBSERVERALIASES repsvr01b_shm # List of alternate dbservernames
NETTYPE # Configure poll thread(s) for nettype
DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env.
RESIDENT 1 # Forced residency flag (Yes = 1, No =
0)
MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor
NUMCPUVPS 4 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one
NOAGE 1 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors
# Shared Memory Parameters
LOCKS 200000 # Maximum number of locks
BUFFERS 10000 # Maximum number of shared buffers
NUMAIOVPS 24 # Number of IO vps
PHYSBUFF 32 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
CLEANERS 127 # Number of buffer cleaner processes
SHMBASE 0x10000000L # Shared memory base address
SHMVIRTSIZE 200000 # initial virtual shared memory segmentsize
SHMADD 100000 # Size of new shared memory segments
(Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes).
0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 127 # Number of LRU queues
LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaninglimit
LRU_MIN_DIRTY 1 # LRU percent