Our community is at it again, this time releasing a really cool Query Tool . The Query Tool was shown at the UK IUA today and got rave reviews. Thought I would share some information about it and hopefully get some feedback on the tool as well.

Query Tool  is an Ingres data management application that was written with another Ingres tool - Ingres OpenROAD. It’s a simple tool for proto-typing and testing Ingres SQL that can support Unicode as well. The Query Tool allows developers to maintain and manipulate data in the Ingres Database by providing full QBF style tabular and simple editor interfaces. There also is a GUI based version which is an added bonus. The tool includes a number of assistants to help developers create select statements, views, and tables and also to hep migrate data into excel, etc. You can store SQL scripts as part of your database so you don’t have to go hunting them down later and can run the scripts in a terminal style interface with full support for the Ingres terminal monitor commands and query buffering.

For more information or how to get involved with Query Tool visit the Ingres wiki.

Give it a try and let us know what you think?

/deb

Last year I had the opportunity to meet Rick van der Lans. He specializes in lecturing and writing books about databases, data warehousing, and BI topics. He’s quite an individual and has been a great resource over the year to help guide some of our work particularly in the BI space and future database work. After talking with several of us here at Ingres, Rick decided to put pen to paper or rather fingers to the keyboard and has just completed his new book, The SQL Guide to Ingres. At 700+ pages, it’s sure to answer all your Ingres SQL questions. Rick was joined by a number of individuals here at Ingres who helped with the editing of the book and includes a forward by Emma McGrattan.

If you happen to be in London on Tuesday, June 9, stop by for the UK IUA event where Rick will be talking about his new book on Ingres and connect with other Ingres users as well as hear what is going on at Ingres up close.

/deb

I’ve been involved in open source for over a decade now and all I can say is nothing ever surprises me anymore. When I saw the announcement this week for the Open Database Alliance, I had to think - wassup? Looks like there are loads of users, customers, partners, contributors and who knows who else is not happy with the recent Oracle acquisition of MySQL. Now we have MariaDB, which is really MySQL with a little extra goodness added in for those that don’t want support from Oracle. That is the beauty of open source.

I do find it sort of interesting that MariaDB is doing the same thing to MySQL/ Oracle that Oracle did to Red Hat a few years ago. MariaDB is basically taking MySQL and adding their fixes and additional functionality and releasing to the market at MariaDB. No harm no foul. Oracle does the same thing, they take Red Hat Enterprise Linux, add their own fixes, couple unique patches just for Oracle and then releases it as Oracle Linux.

Oh well,I guess it all works out in the wash. But now come the questions - and trust me, there are alot. What happens to all the customers and partners that depend on MySQL? How do you know which version to use MariaDB vs MySQL, Which storage engine  do I use Maria, PBXT, Innodb, MyISAM, Falcom, Percona XtraDB,  etc?   How do I know if all the features I need will be included in future versions. I’ll stop there. It’s always sad to see a community reach the point that they split. Sounds like a lot of confusion and uncertainty for their community.

We at Ingres have been most fortunate in this area. The code has been together with out a fork for over 30 years and has over 10k commercial customers.  With a support team that has an average tenure of 12+ years and a customer satisfaction rating over 90%, we know what it means to support enterprises with business critical needs. True, we don’t have the same mindshare as MySQL, but we do have a great set of loyal customers, partners and community members. Today is the day we remember how important it is to stand together and listen to the needs of each other.

/deb

It feels great when you know you can deliver products to the market that people are really interested in; solutions that add value and not just set on the shelf. Today we shared 3 stories of how Ingres and Red Hat are helping customers save money and deliver robust solutions to their customers.  The industries range from the financial segment  (Biveroni Batschelet Partners AG - BBP), transportation industry (Allied Express),  and include  social media (Facebook- Connected Weddings) . In talking to these customers, there were several common threads among their requirements. They were all looking for a more cost effective means to providing services to their customers while ensuring secure, 24×7 access and  the ability to scale as their business grew. Sounds like a reasonable request but hard to find in today’s business climate.

Open Source is uniquely position to help customers with these issues and with more pressure from today’s economic climate, it’s no wonder we are seeing more customers turn to open source. Ingres and Red Hat have joined forces to help customers take out costs, to give them more control over their budgets. With all the budget cuts everywhere you look, it feels great to be part of a company that is helping people reduce costs without cutting heads. Feels even better when you have been part of two companies that focus on reducing costs and helping solve real problems.  (I used to work at Red Hat as well)

Got similar stories on how your solution is helping reduce costs with open source - Please share.

/deb

I can’t tell you the number of times I hear people say they want to know how a particular product is performing, how sales numbers are coming together or trouble tickets match up to various products. You can typically find out this type information if YOU have ACCESS to the right data sources within your company. How many times have you been told - I can run that report for you but can’t give you access? Either you’re not allowed to have access to all the information or the cost of individual licenses to the various BI tools are prohibited for all employees.

We at Ingres released an update to the Ingres Icebreaker BI Appliance this week. Not only did we update the Ingres database, an updated version of Jaspersoft, we include a reporting appliance. What is a reporting appliance you may ask? It allows you to connect to salesforce, download a local copy of your salesforce data and can now run reports locally against the data and also against other data marts you have stored locally.  We use this feature here at Ingres to track product performance against product revenue and incoming leads. It’s a great feature that has been added and now we can offer access to this information to a broader group of individuals without having individual licenses to salesforce.com. Another cool feature is we can restrict access to certain components of data to various individuals.

In today’s business climate, providing employees with the right tools to do their job is critical. Why not provide them with cost effective, easy to use tools as well? Check out the new Ingres Icebreaker BI Appliance. Where else can you get a full suite of BI tools (Jaspersoft), business critical database (Ingres) and the linux operating system in a single install with integrated maintainence?

/deb woods

Emma and I have been doing a number of analyst briefings recently and it’s always great when your message gets picked up. Couple good reports have come out supporting Ingres as the only open source database for business critical workloads and wanted to share them.

Gartner’s Donald Feinberg’s published a report today entitled “Cost Optimization with Open Source DBMSs.” In the reprot, Feinberg addresses the question, “How much can I save with an open-source database management system (DBMS)?”

Within the report, Feinberg states it is still a risk to use an open source DBMS for mission-critical applications, with one important exception:

“The use of open-source DBMSs for mission-critical applications carries far more risk - the exception is Ingres. Ingres has been available for over 25 years and has a broad base of customers with mission-critical applications today. Issues of scalability, reliability and maturity are not a problem for the Ingres DBMS. If your organization wants to use one fo the other two open soruce DMBSs, MySQL or Postgres Plus, for mission-critical applications, you may encournter many issues as the database grows in size and the number of concurrent users increases. Clients have reported issues with crashing, locking and lack of functionality in supporting large mission-critical applications.”

Andy Hayler published an article discussing the strength of Ingres in mission critical workloads. Excerpt from Andy’s article: Since Ingres was one of the top-tier commercial databases, and has over ten thousand commercial customers no-one can accuse it of being unproven. The steady progress of open-source databases mean that more and more companies will start to consider them seriously, a trend that can only be accelerated by the tough economic climate, where saving money is a priority.

Donald, Andy - Thanks again for the great feedback on Ingres.

/deb woods

It’s always great to see contributions come in from the community especially when they are in great demand. Recently we received a contribution from Cédric Pasquotti that helps get PHP developers get started easier on Ingres. Cedric has worked to bundle the Ingres database with PHP and Apache all in a single install called EasyIngres to help users get started much faster with the latest and greatest. It’s a nice install with an easy setup that anyone can work with. Take a look and let us know what you think..

You can find the download here:

http://esd.ingres.com/product/Community_Projects/Development_Tools/Windows_32-Bit/EasyIngres/

Thanks again Cedric..

/deb

Today we have a post from Thomas Glaser from the Ingres Support team. He has been doing some work recently with C# on Linux(mono) with Ingres and wanted to share some things he learned along the way.

Hello World,

I’ve recently come across the challenge to query an Ingres Database using C# on Linux (Mono). Mono is, as you know, the Linux port of the Microsoft .NET Framework. Most Applications run on it but for various reasons there might be problems. Such reasons are:

• NotImplementedExceptions. The MONO port is not 100% finished
• P/Invoke. There might be applications that leave the abstract world of .NET and do immediate OS/specific calls. There is no way that Mono can address this; these Applications won’t run.

So how did the Ingres .NET Data provider do? Not too bad actually, given that Mono is not an officially supported platform. There were three problems:

1. Our provider can do Distributed Transactions. This is not implemented in Mono
2. There are variable names in the provider starting with an underscore. This breaks strict CLI compliance
3. There were some inheritance problems with embedded classes

Good news is, it seems to be rather straightforward to fix this. If the patch (see below) is applied on top of the current community version of the Ingres data provider (get it here), it compiles fine and seems to work as well. What I’ve tested so far is:

• Connecting to a local database
• Selecting some data using a IngresDataReader

This worked fine and returned the expected results. There is a patched binary version available here as well.
Should you fancy trying it out, please beware: Mono is in no way supported by Ingres. Do it at your own risk!

If you have any thoughts on this, feel free to contact me (Thomas.Glaser@ingres.com)

Best regards,
Thomas

Here is the patch:
———————————————————————–

Index: ingres/assemblyinfo.cs
===================================================================
— ingres/assemblyinfo.cs (revision 1228)
+++ ingres/assemblyinfo.cs (working copy)
@@ -17,7 +17,7 @@
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")] // culture neutral
[assembly: System.Runtime.InteropServices.ComVisible(true)]
-[assembly: System.CLSCompliant(true)]
+[assembly: System.CLSCompliant(false)]

//Allows strong-named assemblies to be called by partially trusted code.
//Without this declaration, only fully trusted callers would be able use
Index: ingres/connection.cs
===================================================================
— ingres/connection.cs (revision 1228)
+++ ingres/connection.cs (working copy)
@@ -692,11 +692,14 @@
(ToInvariantLower(persistSecurityInfo) != “true” &&
ToInvariantLower(persistSecurityInfo) != “yes”))
_connectionString = _connectionStringSanitized;
+
+ if (System.Environment.OSVersion.Platform != PlatformID.Unix)
+ {
+ transaction = EnlistDistributedTransactionIsNeeded();

- transaction = EnlistDistributedTransactionIsNeeded();
-
- if (transaction != null)
- EnlistDistributedTransaction(transaction, false); // implicit enlistment
+ if (transaction != null)
+ EnlistDistributedTransaction(transaction, false); // implicit enlistment
+ }
}

Index: provider/advan/metadata.cs
===================================================================
— provider/advan/metadata.cs (revision 1228)
+++ provider/advan/metadata.cs (working copy)
@@ -1489,7 +1489,7 @@
///
/// Table reference of the SQL statement.
///
- public sealed class Table : MetaData.DbTableDefinition
+ public sealed class Table : DbTableDefinition
{
///
/// Constructor for the table definition.
@@ -1648,7 +1648,7 @@
///
/// Column reference of the SQL statement.
///
- public sealed class Column : MetaData.DbColumnDefinition
+ public sealed class Column : DbColumnDefinition
{
///
/// Constructor for column definition.

With all the pressure in the marketplace, everyone is looking for ways to cut cost and get their budgets in line to solve this economic downturn. Just cutting costs doesn’t solve the problem. People need to look at different ways to solve problems. Open Source has been doing just that in a number of areas and can continue to bring value to the market place. We have seen open source being used in numerous business critical industries such as: financial, health care, transportation, manufacturing, and entertainment. One can find open source in just about every industry and each year adoption is increasing and we now are seeing the influence of the open source movement affecting areas other than just software. Open Source is much more than just software at a fraction of the cost, it’s all about changing the way IT procures software, how it develops and deploys solutions and how they are managed all the way through the life cycle. It is about choice. We are seeing great change across the US with a much more open style in the White House.

Information Week’s Charles Babcock wrote about a group of open source leaders who drafted a letter to Mr. Obama to secure his support for more Open Source projects. We have all been witness to Mr. Obama’s vision for a more open society, based on change and hope. We believe that open source can help deliver this vision by providing that transparency and opportunity for change to the marketplace. We have asked our future leaders to take a look at the advantages that open source can bring to this country. Why would we continue to be locked in to paying upfront fees for licensed products before we know if they work, why continue to be forced to pay renewal fees, why are there fees to transfer ownership of products, why can’t I just pay for what I use? Everyday we find more and more users asking these questions - The time is NOW for Open Source to provide answers to these questions.

We at Ingres are working on transitioning a number of key business critical applications everyday to open source projects. Our goals are simple - provide innovative solutions to the market place while reducing costs with our focus on business critical applications. We don’t have hidden fees, our development is out in the open, you pay for what you use, we believe in collaboration, we believe in choice and transparency in everything we do. I guess you would say we believe in Change.

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