Ingres Q & A
Here are some notes I have written when building the 9.3/10.1 public community source (build 151 or 160)
for Ingres on Solaris 10, 32-bit!
The incentive for doing this is financial, we no longer need to pay for a commercial license or support.
We have noticed that Ingres 9.3/10.1 is noticeably quicker than 2.6 which we used for many years,
this new build is extremely fast!
Strongly recommend reading the Migration Guide: http://esd.ingres.com/product/docs/Ingres_9.3/docs/Individual_Guides
The more adventurous amongst you may wish to try building a later version with the
10.1 community source code (build 120) which I have now compiled on Solaris 10 with
the Sun Studio 12 compiler.
Any specific build comments for each version are prefixed at the start of the line
with # followed by a version number, e.g. # 9.3 - Comment....
---------
Major issues noted so far (with the version number):
#1. 9.3: a DBMS crash on multiple-forked verifydb processes.
Only seen this once and it may have been from competing semaphore locks on TCB.
I have disabled verifydb & will see if it happens in the course of normal operations.
#2. 9.3: rollforwarddb hangs when there are no insert/update/delete transactions to apply on the database.
I think I have seen this a couple of times on inactive legacy databases, so in these special cases,
I just recover to the base checkpoint with: rollforwarddb '#cXX' -j dbname
#3. 9.3: E_CL2529_CS_SEM_OWNER_DEAD and E_DMA009_LK_MUTEX error. iidbdb becomes unavailable as a result.
Fortunately, a straightforward shutdown and restart worked fine.
#4 10.1: Edit src/cl/clf/ex_unix/exsignal.c and change the 'return OK;' to just 'return;'.
My Sun Studio 12 compiler was throwing up a (return void) error on this line of code....with 2200 subsequent errors!
Took me a while to find the cause, was a bit rusty on cc compilations!
---------
Download the 9.3 source: http://esd.ingres.com/product/Community_Projects/Ingres_Database
# Untar the source...(make sure gtar is in your PATH, mine is in /opt/csw/bin)
gtar xzvf ingres-9.3.0-160-gpl-src.tgz
# Rename the source directory to a short name such as ingres-9.3. The reason for this is that the
# pathnames may become too long otherwise and will prevent some of the 'ps' commands in the Ingres
# shutdown script from working correctly.
mv ingres-9.3.0-160-gpl-src ingres-9.3
Read source_dir/src/INSTALL before you start, most up-to-date notes are found at
http://code.ingres.com/ingres/main/src/INSTALL
Install flex 2.5.35
Install jam 2.5
Update PATH environment variable to include flex, jam
## With Ingres 9.3, I am using the Sun C 5.8 compiler, dated 2007/01/10
Update PATH environment variable to include /opt/SUNWspro/bin
## With Ingres 10.1, I am using the Sun C 5.10 compiler, dated 2009/06/03 in /bin/cc
# 10.1 - Update PATH to use /opt/sunstudio12.1/bin
Update PATH environment variable to include /usr/ccs/bin
Update LD_LIBRARY_PATH envronment variable to include /opt/SUNWspro/lib
# 10.1 - Update LD_LIBRARY_PATH: add in /opt/sunstudio12.1/lib
cd ingres-9.3
bash
export ING_ROOT=`pwd`
# Compile xerces 2.7
gtar xvf xerces-c-src_2_7_0.tar # to $ING_ROOT
export XERCESCROOT=$ING_ROOT/xerces-c-src_2_7_0
export XERCESLOC=$XERCESCROOT/lib
cd $XERCESCROOT/src/xercesc
./runConfigure -b 32 -p solaris; make
## Set environment for Ingres build
source $ING_ROOT/src/tools/port/jam/bldenv
# Location of VERS files is here
cd $ING_ROOT/src/tools/port/conf
## Edit VERS file for your platform before calling Jam
chmod u+w VERS.su9_us5
vi VERS.su9_us5 # Solaris
Change BUILD_ARCH ="32"; # 32-bit only
## Ingres will look for any file called VERS and use it by default.
# 10.1 - cp VERS.su9_us5 VERS
## Get ready to compile with jam
cd $ING_SRC/tools/port/jam
# Check platform: uname -s, uname -p & then echo $config_string at end of bldenv script
chmod u+w Jamdefs.a64_sol Jamdefs.usl_us5
edit Jamdefs.a64_sol to add -xarch=v8plus to CCMACH, CCLDMACH, and C++FLAGS
edit Jamdefs.usl_us5 to add -xarch=v8plus to CCMACH, CCLDMACH, and C++FLAGS
# 9.3 -- chmod u+w Jamdefs.su9_us5
# 9.3 -- edit Jamdefs.su9_us5 to add -xarch=v8plus to CCMACH, CCLDMACH, and C++FLAGS,
# 10.1 -- edit Jamdefs.usl_us5 to add -xarch=v8plus to CCMACH, CCLDMACH, and C++FLAGS
jam
mkjams
# 10.1 - Edit $ING_ROOT/src/cl/clf/ex_unix/exsignal.c so i_EXestablish() doesn't
# return a value as it causes a compile error!
cd $ING_SRC
nohup jam -a >& jam.out &
# May see a number of failed compiles related to lp64 bit stuff, that's expected.
# Wait an hour or so...
which buildrel # Do NOT continue if this does not show a result
## Build dependencies
buildrel -a
tartools -a
## Test sql command to check that the binary was built correctly
cd $ING_ROOT/build/bin
sql
## Timezone_name will be undefined, that's ok
## Install timezone file
cd $ING_ROOT/build/sig/tz
cp ~ingres/australasia.tz.20080313 .
iizic -v australasia.tz.20080313
export II_TIMEZONE_NAME=AUSTRALIA-VICTORIA
iizck | more
## Should see: Error locating ii.*.tz.AUSTRALIA-VICTORIA in PM config.dat file...that's ok
## Build a tarball for the subsequent ingbuild install
mkdir -p $ING_ROOT/release/r00
cd $ING_ROOT/release/r00
export II_RELEASE_DIR=`pwd`
buildrel
## Assembles many archives into a stub installer
cd $ING_ROOT/release/r00
tar cvf /xxx/yyy/zzz/ingres-9.3_stub.tar *
# lp64 stuff will be missing, that's expected
# [Optional]: Create a virgin installer for export to other machines
## Install/Setup Ingres
cd /xxx/yyy/zzz/ingres-9.3/build/files
# Update termcap file with any extra terminal entries (copy this from another installation)
## 10.1 Copy LICENSE file to install directory
# cd $ING_ROOT/src
# cp LICENSE.gpl $ING_ROOT/install/build/ingres/files/LICENSE
# 10.1 - ingsetenv II_CONFIG $ING_ROOT/install/build/ingres/files
## Install/Setup Ingres
export TERM_INGRES=xterm # or whatever you normally use
export II_SYSTEM=$ING_ROOT/install/build/
cd $II_SYSTEM/ingres
# 10.1 - mv install install.orig
# 10.1 - ln -s files install
utility/ingbuild
## ...Go through normal setup...
## Use Distribution of: /xxx/yyy/zzz/ingres-9.3_stub.tar
Choose CustomInstall, not PackageInstall
Turn off all the 64-bit options
# I normally Turn off C2 Security Auditing, Protocol Bridge, Star Distributed, Replicator, DTP for Tuxedo
Run SetUp option on Ingres Intelligent DBMS as a minimum
Disable backup transaction log
Resize your transaction log from the default 256 MB
Set CPUs to 2 # Use either 2 or the value from: psrinfo -p
Use default character set of ISO88591
Use ingresdate (default)
# Ingres will startup and iidbdb is created/sysmod
Creation of imadb is automatic, create demodb as well
Setup other components such as Terminal Monitors, Query and Reporting Tools, VisionPro etc
## I turned off II_LP64_ENABLED as cbf will return nothing otherwise.
ingprenv | sort
ingunset II_LP64_ENABLED
## Set other ingres internal settings (symbol.tbl)
ingsetenv II_ABF_ASSUME_VALUE Y
ingsetenv II_ABFDIR /xxx/yyy/zzz/install/build/ingres/abf
ingsetenv II_DATE_CENTURY_BOUNDARY 30
ingsetenv II_DATE_FORMAT DMY
ingsetenv II_DBMS_LOG xxxxxxxxx
ingsetenv II_DMFRCP_STOP_ON_INCONS_DB Y
ingsetenv II_EMBED_SET "programquit;dbmserror"
ingsetenv II_LOG_FILE /xxx/yyy/zzz/build/log
ingsetenv II_LOG_FILE_NAME ingres_log
ingsetenv II_MAX_SEM_LOOPS 2000
ingsetenv II_NUM_OF_PROCESSORS 2
ingsetenv II_NUMERIC_LITERAL float
ingsetenv II_PARAM_PASSING FORCEMAX
ingsetenv II_PATTERN_MATCH sql
ingsetenv II_QSWITCH 250
ingsetenv II_SHADOW_PWD /xxxx/yyy/zzz/ingres/bin/ingvalidpw
ingsetenv II_TM_ON_ERROR nocontinue
ingsetenv II_TM_SWITCH true
## A previously installed ingvalidpw can be moved (not copied) to a new location & reused.
## This file must still be owned & executable by root and is required for JDBC/ODBC connections with OS-authentication as part of C2 security.
ingprenv | sort
cbf
Turn off 'Remote command' system component - EditCount to 0
## Shutdown ingres and backup the new build distribution into a tarball,
## a point of reference if something goes AWOL.
## If you extract this tarball on a new machine, edit $II_SYSTEM/ingres/files/config.dat
## protect.dat, change all occurrences of the original hostname to the new machine.
## Update the symbol table with ingsetenv II_xxxx yyyy, update directory paths as required
## Change CBF setting for local_vnode in Name Server section to new hostname
## Change CBF setting for cache_name in DBMS Server section to new cache_name
## In CBF, delete & recreate location for primary logfile as required
Run cbf & set Security option:
I also set the following CBF options (your mileage may vary):
security_mechanism: ingres-> system
Turn off default_journaling in DBMS server
Turn on log escalations in DBMS server
Set qef_sort_mem to 10000000 in DBMS server
Set log_writer to 16 in DBMS server
Set stack_size to 524288 in DBMS server
Set opf_memory to 35000000 in DBMS server
Set psf_memory to 42000000 in DBMS server
Set qsf_memory to 125000000 in DBMS server
Set rdf_memory to 22282240 in DBMS server
Turn on DMF 4k Cache page size in DBMS server
Set hash_size to 29989 in Locking system
Set list_limit to 1500 in Locking system
Set lock_limit to 150000 in Locking system
Set resource_hash to 29989 in Locking system
Set resource_limit to 150000 in Locking system
Ingres Logging System: buffer_count from 35 to 128
Set gather_write to on in Recovery Server
Set log_writer to 5 in Recovery Server
Set stack_size to 262144 in Recovery Server
Ingres Net Server: set ib_encrypt_mode to on
Ingres Net Server: set ob_encrypt_mode to on
If you did mkvalidpw for OS authentication, you can use the existing ingvalidpw by moving
the binary and resetting the environment variable e.g.
ingsetenv II_SHADOW_PWD $ING_ROOT/build/bin/ingvalidpw
## If necessary, change II_INSTALLATION in the Ingres environment to 'te' (e.g. for a new test installation)
## Ensure you have enough shared memory resources beforehand! (syscheck -v)
ingsetenv II_INSTALLATION te
## If iidbdb needs to be relocated, must shutdown Ingres then delete existing iidbdb subdirectories:
/ingres/data/default,
/ingres/jnl/default,
/ingres/dmp/default,
/ingres/ckp/default,
/ingres/work/default,
## and finally startup Ingres and run: createdb -S iidbdb
## as per http://www.actian.com/kb/article/406074
## Ckpdb +j iidbdb
## Recreate databases, extend to data locations
## Reload group, users and dbprivs
## Start loading in databases after copydb/copyapp on old dbms server, use BINARY mode
## wherever possible
## Reload apps with copyapp, reports with sreport
## Rebuild apps with abfimage/makefile etc
## Note that the old 'date' format in 2.6 has now become 'ingresdate'
## Check the migration/reference guides for more info.
## My strong preference is to avoid using upgradedb, the utility can hang
## because internal catalogs such as iiattribute have changed since 2.6.
Ingres Q & A
To William's Home Page
© William Yuan 2014
