Ingres Community OpenSource 9.3/10.1 Build notes

Last Updated: February 8, 2012  
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