Login Register Actian.com  

Actian Community Blogs

Old

Checking the Existence of Database Assets

Posted 2015-11-19 at 09:04 AM by rhann
Updated 2015-11-19 at 11:59 AM by rhann (Forgot to show the \macro command)

The Ingres tm terminal monitor—better known as sql—has a macro facility that provides, among other things, conditional execution.

The following example checks to see if the myproc procedure exists and if it does (tuplecount—that is, row count—is greater than 0), it skips everything between the \branch and the next-step label (\mark):
\macro
SELECT distinct procedure_name
FROM iiprocedures
WHERE procedure_name = 'myproc'
AND procedure_owner = dbmsinfo('username');
...
rhann's Avatar
Ingres Community
Posted in Ingres, Vectorwise
Views 1764 Comments 0 rhann is offline Edit Tags
Old
Rating: 2 votes, 5.00 average.

New Concise QEP Display Format

Posted 2014-05-27 at 04:19 AM by rhann
Updated 2014-05-28 at 09:19 AM by rhann (Added the update about the new distinctive label in SC930 logs)

Seeing its query execution plan (QEP) is essential to anyone attempting to undestand and improve the performance of a slow query. Anyone who has used Ingres for a little while will have discovered how to display and read a query execution plan like this:

Code:
QUERY PLAN 3,1, no timeout, of main query

                        K Join(customernr)
                        Heap
                        Pages 2 Tups 9
                        D569 C1425
...
rhann's Avatar
Ingres Community
Posted in Ingres
Views 5859 Comments 1 rhann is offline Edit Tags
Old
Rating: 2 votes, 5.00 average.

Quick-n-Dirty Debugging of Session Temporary Tables

Posted 2014-03-18 at 02:35 PM by rhann
Updated 2014-03-19 at 05:07 AM by rhann (Added note about calling Run_Monitor() directly)

I use session temporary tables a lot and sometimes I over-reach and end up having to figure out where it all went wrong.

This time my task involved a complex series of operations on five temporary tables culminating in a relational DIVIDE (which is not intuitive at the best of times). Something was going wrong somewhere and none of my clever guesses were correct.

Ideally I'd like to run the application with gdb to allow me pause to query the tables at various points...
rhann's Avatar
Ingres Community
Posted in Ingres
Views 4649 Comments 2 rhann is offline Edit Tags
Old

Naming Conventions

Posted 2014-01-31 at 07:50 AM by rhann
Updated 2014-02-01 at 02:45 AM by rhann (Minor typos and a misplaced winding-up)

While working on my database diagramming tool I've been forced to take a critical look at my own naming practices.

Proposing a naming convention has been a sure-fire way to provoke bitter wrangles for as long as I've been working with computers, going back to the days of assembly language programming.

I detest all naming conventions. I assert with great conviction that in almost 40 years in the industry I've never seen one that was a net benefit. Every single prescriptive...
rhann's Avatar
Ingres Community
Posted in Uncategorized
Views 4544 Comments 3 rhann is offline Edit Tags
Old

More on ER Diagrams for Ingres Databases

Posted 2013-12-15 at 06:31 AM by rhann
Updated 2014-03-26 at 01:47 PM by rhann (Fixed broken image link)

As I mentioned in my previous post, SchemaSpy doesn't work as well as I'd like. I spent a couple days trying to fix it up and to get it to cooperate better with Ingres but I ran into multiple problems.

It became clear that the quickest way to get correct ER diagrams at run time was going to be to roll my own.

Happily, if the foreign key constraints are declared, I can generate something pretty decent with not much effort (500 lines of heavily commented C):
...
rhann's Avatar
Ingres Community
Posted in Ingres, Vectorwise
Views 2307 Comments 4 rhann is offline Edit Tags
© 2011 Actian Corporation. All Rights Reserved