Michael Flower and I were chatting a couple days back and he was sharing some ideas with me around Ingres Journals. I asked him to write up some notes and I would post in the technology blog. For those of you who don’t know Michael - he is responsible for training here at Ingres and is a wealth of knowledge. In the future we will continue to pull tid bits of knowledge from him to share with you.. Below are his some of his notes around Ingres Journals

The Ingres Checkpoint files provide a backup capability to recover to a database (using the rollforwarddb command).

The Ingres Journal system allows a point-in-time recovery of a database (using the rollforwarddb –e flag)

By recovering a database checkpoint and the Journals, a journalled database can be recovered to a moment in time which is after the most recent checkpoint.

Online Analysis of Ingres Journals

We might sometimes need to identify the point-in-time to which we want to recover our database. Ingres provides methods of analysing a database’s journals, even while the database is online:

1. Ingres Journal Analyzer

2. The auditdb command

What Can the Journals Tell Us?

The ability to audit the journals provides us with other auditing benefits. What if we want to know:

• What updates has user Joe done recently?
• Who has deleted rows from table C?
• Who has run the most updates against table D?

Any standard DML statement (insert, update, delete) will be journalled, and this information can be extracted from the journals and analysed.

Steps to Extract Journals

The auditdb command can be used to extract table-specific information.

Step 1. Extract the Journal information into a File

auditdb dbname -file -table=tablename

This will create an audit file in the format tablename.trl

Step 2. Create a new target table to load the file

Create a table based on the original table, with the following extra column definitions:

date date not null with default,

username char(32) not null with default,

operation char(8) not null with default,

tran_id1 integer not null with default,

tran_id2 integer not null with default,

table_id1 integer not null with default,

table_id2 integer not null with default,

… then the columns from the original table

If using a version of Ingres which has ingresdate and ansidate, ensure that the ingresdate is used.

Note that the new audit table does not have to be on the same database as the original table.

Step 3. Load the file into the new table

Load the data from the audit file into the new table using the SQL copy command,

eg. copy table audit_tbl () from ‘originaltable.trl’

Step 4. Analyse the audit data.

Connect to the database and analyse the audit table

eg. select * from audit_tbl where username = ‘Joe’

Reference Guides

To find out more about database backup and recovery, see the Ingres DBA Guide - http://docs.ingres.com/dba/

The Ingres Command Reference Guide shows the full syntax for the Ingres operating system commands discussed in the document - http://docs.ingres.com/cmdref/

The Ingres SQL Reference shows the syntax of the SQL commands discussed in this document - http://docs.ingres.com/sqlref/

Example Script to automatically analyse Journal Data

This whole process can be automated with the help of copydb, eg.

A bash script (auddb.bash) to audit the contents of a particular table

#!/bin/bash

# Audit a table’s journals using QBF

# Syntax audddb.bash dbname tablename

#

# Author. M Flower 13/11/2008

# Need to add a check that both parameters have been defined

if test $# -lt 2

then

echo ‘Syntax Error.’

echo ‘Syntax: ‘ $0 ‘dbname tablename’

exit 1

fi

DBNAME=$1

TBLNAME=$2

# Define workfiles

COPYINFILE=$$copy.in

COPYOUTFILE=$$copy.out

TRLFILE=$$.trl

#cd to a temp directory,

cd `ingprenv II_TEMPORARY`

auditdb $DBNAME -file=$TRLFILE -table=$TBLNAME

# Check that the TRL file has been created

if test -f $TRLFILE

then

echo ‘Audit file created’

else

echo ‘Error; No audit file ‘ $TRLFILE

exit 1

fi

copydb $DBNAME $TBLNAME -with_tables -no_loc -noint -infile=$COPYINFILE -outfile=$COPYOUTFILE

# check that the copy.in script has been created

if test -f $COPYINFILE

then

echo ‘Copyin file created’

else

echo ‘Error; No copyin file ‘ $COPYINFILE

exit 1

fi

ed $COPYINFILE << !

/create table $TBLNAME

s/create table $TBLNAME/create table aud_$TBLNAME /

+1

i

date date not null with default,

username char(32) not null with default,

operation char(8) not null with default,

tran_id1 integer not null with default,

tran_id2 integer not null with default,

table_id1 integer not null with default,

table_id2 integer not null with default,

.

$

a

copy table aud_$TBLNAME () from ‘$TRLFILE’ pg

.

w

q

!

# Run the amended copy.in

# Note that the DBNAME could be changed here.

# Maybe introduce another parameter TARGETDB

sql $DBNAME < $COPYINFILE

# Remove workfiles

rm $TRLFILE $COPYINFILE $COPYOUTFILE

#Launch qbf against

qbf -t $DBNAME aud_$TBLNAME

Today we launched the a new update for the Ingres Database as well as a new name. With a previous name that included a year number, we were always getting some feedback around our naming strategy and decided it was time to return to our roots. The base numbering scheme has always reflected the current version and the decision was made to make that more visibility - therefore Ingres Database 9.2.

In this update we opened up our development process with quite a bit of collaboration from our partners, customers and numerous community members. The feature list for this update was driven from code sprints from our user groups, co-developed bundles and appliances that we did with Alfresco and JasperSoft and numerous suggestions from our customers and partners.

Ingres Database 9.2 Focus areas:

Internationalized Applications

• Support for the UTF-8 Character Set
• Support for Unicode upper and lower case

Improved Availability

• Roll forward re-start
• Logging of backups
• Logging of roll forward
• Logging of database verification

Application Development

• Support for scrollable cursors
• New numeric functions
• New string functions

Content Management System Support

• LOB Locator Support
• Improved performance of string comparisons

Supportability Features

• Better defaults for large systems
• Default storage structures for indexed tables
• Improved exception handling and tracing
• New product demos to help new users

We at Ingres believe that our products should deliver value to our customers and in today’s economic times, everyone can certainly use a product that helps shave dollars off their bottom line without sacrificing functionality.

Give Ingres Database 9.2 a try - Download it now and let us know what you think by emailing us at products@ingres.com.

/deb

Being around open source the last 10 years or so I have seen the basic principles of open source working in alot of different industries. I am always being asked if I have seen others outside the software development space taking advantage of open source and if so what projects. I saw this post and thought I would share this article.  Have you seen other examples of open source popping up in unique areas - If so please share them with us. We love to see successful examples of open source.

How Open source Biology May Rock your world..

http://ostatic.com/173578-blog/how-open-source-biology-may-rock-your-world

Here at OStatic, we’re always interested in efforts to apply open source principles to efforts outside the software realm. Drew Endy, an open source biologist currently working at Stanford, is a good example of why. Along with several researchers at MIT, Endy is working on synthetic biology and the engineering of standardized biological components and devices, known as BioBricks. Endy is also founder of the BioBricks Foundation, a non-profit organization founded by engineers and scientists from MIT, Harvard, and U.C. San Francisco, focused on open source biotechnology. Just as open source software is often shared in online repositories, the BioBricks Foundation has a registry online for open source biological parts. Here’s more on how these efforts may change the world, following open source principles.

/deb

Woohoo!  Ingres makes Java development easy!  OK, so we don’t make Java development easy, but hey, who does?  What we do is make Java development and deployment easier.  We do that with the Ingres Consolidated Application Foundation for Eclipse (CAFÉ).  Today, Ingres CAFÉ was named the winner in this year’s LinuxWorld Software Excellence Competition in the Application Development Tool category.  I’d like to congratulate our Ingres CAFÉ team on a job well done!  We have a talented and dedicated Open Source team that has put in a lot of effort.  Ingres CAFE truly reflects the full spirit of Open Source Development.  Ingres CAFÉ is built on the Eclipse framework and is comprised of numerous OSS components including the Ingres Database

You can read more about CAFÉ on our CAFÉ wiki.   But I digress!

Congratulations, team!  The team is managed by Andrew Ross of Ingres Corporation.  Samrat Dhillon is the lead developer of a team from Ingres, Carleton University, and Talent First. Support has also been contributed by Google Summer of Code.  Thanks guys for your hard work and dedication for putting this great product together and getting it ready in time for today’s show!

If you’re in the bay area, stop by Moscone North in San Francisco and check out CAFÉ at the Ingres booth in the Open Source Alliance Pavillion!

Ingres CAFÉ:  Download, Develop, Deploy! [ Download Software ]

Today, when computers present at almost each work space, any average worker is on frontier - he must independently accept, send, or process data. It’s comfortable to accumulate data in a database, but average person cannot:

1. output 3-dimensional constructions from database onto a screen (convert into format and protocol of X11)
2. enter data from a browser (adjust proprietary web-server to a particular DBMS)
3. copy information from a remote database (use other sources of information to make own data subset)
4. switch-on and switch-off notebook several times during one long transaction, lasting days or even weeks

This paper describes proposed solutions for the above problems, and author would be interested in opinions, comments and possible implementation from the Ingres community. Details are stated in separate document (we will refer below to pages of that document). All problems, except 2-nd, can be solved with usage of proprietary formats of data transferring, and are formulated as transfer of XML only for unification with 2-nd problem.Solution of 1-st problem - interface ‘model - window system’ - is described in previous publication. To direct DBMS to change its data format from XML to X11 and to take away invisible surfaces, postfix ‘PROJECTING’ is used in operator ‘SELECT’. In particular case of two-dimensional data, objects must be written using data schema (what is understandable for users) instead of pretentious (and redundant) data types, which user does not master. Single difference from previous publication is that author proposes to send X11 data to client in XML-wrapper in purpose of unification with XML.To solve2-nd problem, it’s necessary to provide possibility to install DBMS and immediately use it, like user install and use programs “Teleport”, “FlashGet”, browser, etc. DBMS itself must accept XML, and place data from it into tables under some agreements. My proposals about these agreements are: Read the rest of this entry »

Progress in office technologies stopped and it has occurred because user, capable to write primitive programs, cannot:

• display 3-dimensional data in window of own program
• move 3-dimensional objects by commands in a foreign program (in such as ‘Microsoft Office’), and see changes on display

Objects can be drawn by mouse independently, downloaded from internet, obtained as a result of calculations, or as output from hardware. In any case, there is a need for:

• binary format for file, which contains 3D-objects, moreover objects must be written as triangles (which are understandable for users) instead of languages 3DMLW, 3DXML, COLLADA, because user does not master formulating and considering of own problems on these languages
• engine inside operating system, which:
  1. allows to load into self these binary files
  2. return objects in format X11 [1] at request of them, that program, addressed to this engine, could send obtained data to the X-server without any own calculations or changes (without referencing to OpenGL, DirectX, which user never will masters!)
  3. supplies two ways to change the coordinates of objects (both ways create commands in format X11 to control X-Server, which are immediately forwarded to the X-server):
     • by query language (that allow a program to change coordinates)
     • accepting events in format X11 [1], which are also commands to change coordinates, but are send by mouse (that allows to change coordinates by mouse)

It will be enough for user to call one function, we shall name it as ‘printg’, in which user specify what 3D-objects from database engine must appear on a screen (this function being called, automatically forwards all mouse motion commands of objects and motion of camera into engine; all messages about motion of objects, obtained from engine, automatically forwarded into X-server). It will be enough to call another function, we shall name it as ‘request’, to send order to change coordinates on query language (change immediately is displayed on screen, because messages from engine are automatically forwarded to X-server after call of ‘printg’). And these two functions can be called in any program. This is breakthrough in office technology. Read the rest of this entry »

So I’m trying to work a bit on the Mac OS X port of Ingres that’s had a recent update and some important changes. There’s a few things that don’t just come “out of the box”:

1. You’ll have to install as rootTo do this the easy way, just open a term and run the command “su -”. This will prompt you for your current user password to make sure that you really mean it.
2. You need to set DYLD_LIBRARY_PATHMine is set to /lib:/usr/lib:/usr/local/lib:$II_SYSTEM/ingres/libYou can safely replace any directives to set LD_LIBRARY_PATH with setting DYLD_LIBRARY_PATH
3. You need to up your shared memory kernel settingsCreate the file /etc/sysctl.conf and add the following lines if you have 4G memory:kern.sysv.shmall=4096000kern.sysv.shmseg=8kern.sysv.shmmni=256

   kern.sysv.shmmin=1

   kern.sysv.shmmax=1024000000

4. You need to be running LeopardWe know this may be a bit of a hassle, and it could be remedied with some work, but the current people working on the port are all running Leopard and we’re trying to focus time on getting the port working well on Leopard first. If you’re interested in helping out, please let me know!

Here’s a few things I did that just make life easier based on my experience:

• Create an “ingres” user via the good old System Preferences app’s Accounts section
• Install in /opt
• Permanently change my term’s option for termcap interaction with the function keys to “Strict VT-100 keypad behavior” on the Advanced tab of my preferences

To make your life easy, but maybe slightly bloated, run the ingres_express_install.sh script and let it rip by. Once finished, login as the ingres user, set your env (including DYLD_LIBRARY_PATH), and create your first database:

• createdb -umsale mymacdb

Replace the msale with the username you use to do development on the local box and then that user will be able to perform DBA actions for that database. If you want to do your development/dba work as ingres, just leave out the -u option.

You can now connect to your database via the JDBC tool of your choice to interact via ANSI-friendly SQL (I use SQuirrel due to its Toad-like UI) using the JDBC driver at $II_SYSTEM/ingres/lib/iijdbc.jar.

Let us know if you have any issues with the install and we’ll do the best we can to help out. We know there is plenty of work that still needs to be done to get this port reliable and ready as a solid development platform on the Mac.

If you are a student with experience developing C or Obj-C on the Mac, we would love to get you involved in our Google Summer of Code project to get Ingres healthy on Leopard.

Recently Chip Nickolett shared some performance tips with me and I thought I would pass them along to others..

On Storage Structures -I use BTREE for almost everything.

• I’ve found that unless you are just writing to a table that HEAP tables tend to do full table scans, even with indexes.
• ISAM works well but you can easily run into locking issues and contention due to overflow pages. I’ve also seen much more sort activity that with BTREE.
• I use HASH when I need to separate people for concurrency reasons (e.g., a trouble ticket table with 200 people accessing / updating the table at any one time), but have found that even for things that should be a direct match there are often scans on the HASH table and sorting due to the query plan selected. Not a big deal, but because the HASH tables are generally larger this increases I/O.

If there is a good “primary key” that has a lot of leading-edge granularity I will often use this for the storage structure. Sometimes it makes sense to use a “clustering key” to localize data and minimize I/O. For example, at an insurance company years ago the policy table was 4+ GB and almost everything caused a table scan. I modified this to BTREE on company (there were three companies), branch (each company had ~50-100 branches), and state. There was a unique index on policy # that was used both for access and to maintain uniqueness. This approach improved query performance by something like two orders of magnitude because we were doing more direct access and much less I/O. Constraints I tend to avoid these since they are implemented as secondary indexes and sometimes redundant to the storage structures and/or indexes selected.

Read the rest of this entry »

The latest release of the DbLinq project now comes with official support for the Ingres database! For those of you that are not familiar with it, Linq is an O/R (Object-Relational) mapping tool with some similarities to Hibernate. Linq is type-safe, queries get compiled into MSIL on the fly, and your C# WHERE clauses are translated into SQL and sent to the DBMS server for execution. In short, it makes design of data access layers safer and faster. In C# 3.0, linq code looks like this:

var q = from p in db.Products
where p.ProductName == “Pen”
select p.ProductID;

The cool thing is, that you get full Intellisense support, so it is virtually impossible to spell anything wrong. What you also get is the ability to refactor an existing database into a Linq mapping class or a *.dbml file. So to get started with Linq on Ingres, just take the following easy steps:

1. Have Visual Studio 2008 (the free Express Edition suffices)
2. Download and compile DbLinq version 0.17
3. Generate a mapping file from your existing Ingres database using the Visual Metal tool
4. Start a new project in Visual Studio, add the mapping file and references to DbLinq.dll, DbLinq.Ingres.dll and Ingres.Client.dll
5. Enjoy!

Read the rest of this entry »

When I talk to customers or colleagues in IT about appliance based computing, I get a couple different responses. I get the “Huh?” look. I get some futuristic absurdity that makes me re-think why I asked this person in the first place. Or, I get a slightly similar yet different definition I’m familiar with. It makes me recognize how badly we as customers and ISVs need a definition to agree on.

There are pros and cons to the early days of a definition, particularly in software. Vendors are usually guilty of twisting, turning, and manipulating a definition in order to fit their agenda. While customers grab a definition they like and apply it to their world, only to shut out potentially better technology. As more vendors & customers enter the fray, bodies get created, conferences are held, and enough mass is generated to ensure a standard is established. But this takes time and is that really needed? I’m not sure. Read the rest of this entry »