HTML Advanced Tutorial - Declarations
22 Jul
Posted by ProCOM 
on July 22, 2007 – 2:57 pm - 764 views
(No Ratings Yet)
Loading ...If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!
This page deals with the how to define a valid XHTML document.
Document type declarations
At the very top of your web pages, you need a document declaration. That’s right, you need it.
Without specifying a doctype, your HTML just isn’t valid HTML and most browsers viewing them will switch to ‘quirks mode’, which means they will think that you don’t know what the hell you’re doing and make up their own mind on what to do with your code. You can be the greatest HTML ninja ever to have walked the earth. Your HTML can be flawless and your CSS simply perfect, but without a document declaration, or a wrong document declaration, your web pages can look like they were put together by a short-sighted, one-eyed infant gibbon with learning difficulties.
The document declaration for XHTML 1.0 Strict looks like this:
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Strict//EN”
“http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”> The following is the document declaration for XHTML 1.1, which may seem preferable, being the latest version of XHTML, but there are a few problems, which will be explained in just a minute…
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.1//EN”
“http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd”> If you just can’t let go of HTML 4 or if you’ve got some kind of Netscape 4 fetish, you can use XHTML 1.0 Transitional:
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN”
“http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”> The only reason you should use this is if you have an unusual need to accommodate older, rarely used browsers. Transitional XHTML 1.0 allows old HTML 4 presentational elements that may result in better presentation in browsers such as Netscape 4 but using such elements will be detrimental to the efficiency and possibly accessibility of your web pages.
Finally, if you’re one of those wacky people who use frames, the XHTML 1.0 Frameset document type declaration looks like this:
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Frameset//EN”
“http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd”> Note that the DOCTYPE tag is a bit of a rebel and demands to be written in upper case and adorned with an exclamation mark. It also breaks the rules in that it is the only tag that doesn’t need closing.
Language declarations
You should identify the primary language of a document either through an HTTP header or with the xml:lang attribute inside the opening html tag. Although this is not necessary to produce a valid XHMTL document, it is an accessibility consideration. The value is an abbreviation, such as ‘en’ (English), ‘fr’ (French), ‘de’ (German) or ‘mg’ (Malagasy).
The declaration for a document with primarily English content for example would look like this:
<html xmlns=“http://www.w3.org/1999/xhtml” xml:lang=“en”>
After declaring a primary language, if you use languages other than that in your content, you should further use the xml:lang attribute in-line (such as <span xml:lang="de">HTML Hund</span>).
Content types
The media type and character set of an HTML document also needs to be specified, and this is done with an HTTP header such as:
Content-Type: text/html; charset=UTF-8 The first part (in this example, the text/html bit) is the MIME type of the file, and this lets the browser know what media type a file is and therefore what to do with it. All files have some kind of MIME type. A JPEG image is image/jpeg, a CSS file is text/css and the type generally used for HTML is text/html.
The second part of the HTTP header (in this example, the UTF-8 bit) is the character set.
Perhaps the easiest way to set an HTTP header (or mimic it) is to use an ‘HTTP-equivalent’ meta tag in the HTML, which would look something like this:
<meta http-equiv=“Content-Type” content=“text/html; charset=UTF-8″ />
Slightly more complicated, but preferable (due to it being a proper HTTP header AND cutting down on HTML), is to send the header by using a server-side scripting language. With PHP, you might use something like this:
<? header(”Content-Type: text/html; charset= UTF-8″); ?>
If you don’t want to (or can’t) use a server-side scripting language, you might be able to go straight to the server with an ‘.htaccess’ file. Most servers (Apache compatible) can have a small text file with the file name ‘.htaccess’ that sits in the root directory and with the following line in it, you can associate all files with the extension ‘.html’ with a MIME type and character set:
AddType text/html;charset=UTF-8 html Character sets include ‘ISO-8859-1′ for many Western, Latin based languages, ‘SHIFT_JIS’ for Japanese and ‘UTF-8′, a version of Unicode Transformation Format, which provides a wide range of unique characters used in most languages. Basically, you should use a character set that you know will be recognised by your audience. Unless you are using a Latin-based language (including English), where ISO-8859-1 can be used and is mostly universally understood, you should use UTF-8 because it can display most characters from most languages and is the safest code to use because it will work on most people’s computers.
You can read more about character sets elsewhere on the web.
XHTML should be served by the MIME type application/xhtml+xml. That’s what it is - an XML application. Unfortunately, most browsers don’t have the first clue what this is. So it is generally accepted that it’s ok to use the MIME type text/html. According to the W3C, and further highlighted by the Web Standards Project, flavours of XHTML 1.0 may be served as text/html, but XHTML 1.1 should not, which is why the examples across this site are XHTML 1.0 Strict, assuming a text/html MIME type. But you can (and perhaps should) serve the correct MIME type to those browsers that accept it with a bit of server-side fiddling.
This site uses PHP to serve XHTML 1.1 with an application/xhtml+xml MIME type to those browsers that understand and render the type (such as Mozilla) and XHTML1.0 Strict with the text/html type to other browsers (such as IE). The script, placed at the top of the very top of every page looks a little something like this:
<?
if(stristr($_SERVER[”HTTP_ACCEPT”],”application/xhtml+xml”)){
header(”Content-Type: application/xhtml+xml; charset=UTF-8″);
echo(’<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.1//EN” “http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd”>’);
} else {
header(”Content-Type: text/html; charset=UTF-8″);
echo (’<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Strict//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”>’);
}
?>
This checks to see if the browser accepts the application/xhtml+xml MIME type and if it does, that MIME type is sent and the XHTML1.1 document type is written to the HTML. If the MIME type isn’t recognised then the text/html MIME type is sent and the XHTML1.0 Strict document type is written in the HTML.
Other than peace of mind that you know you’re doing the right thing and preparing yourself for the way to do things in the future, the immediate benefit of using this method is that Mozilla will treat your files as XML applications and simply won’t work if your XHTML isn’t up to scratch ie, isn’t well formed. You can then debug without having to run the document through a validator.
CSS Advanced Tutorial - The Display Property
22 Jul
Posted by ProCOM on July 22, 2007 – 2:44 pm - 522 views
(No Ratings Yet) Loading ...A key trick to the manipulation of HTML elements is understanding that there’s nothing at all special about how most of them work. Most pages could be made up from just a few tags that can be styled any which way you choose. The browser’s default visual representation of most HTML elements consist of varying font styles, margins, padding and, essentially, display types.
The most fundamental types of display are inline, block-line and none and they can be manipulated with the display property and the values inline, block and none.
inline does just what it says - elements that are displayed inline follow the flow of a line. Strong, anchor and emphasis elements are traditionally displayed inline.
block puts a line break before and after the element. Header and paragraph elements are examples of elements that are traditionally displayed block-line.
none, well, doesn’t display the element, which may sound pretty useless but can be used to good effect with accessibility considerations (see Accessible Links), alternate stylesheets or advanced hover effects.
The original default style-sheet for this site for example, manipulates a few traditionally in-line and block-line elements to fit the design.
h1 {
display: inline;
font-size: 2em;
}
#header p {
display: inline;
font-size: 0.9em;
padding-left: 2em;
}
This enabled the title ‘htmldog.com’ and the tag-line to be displayed next to each other rather than above and below each other while maintaining optimum accessibility.
#navigation, #seeAlso, #comments, #standards {
display: none;
}
The above code is used in the print-only styles to basically ‘turn-off’ those elements, such as navigation, which are insignificant to the single page.
display: none and visibility: hidden vary in that display: none takes the element completely out of play, where as visibility: hidden keeps the element and its flow in place without visually representing its contents. For example, if the second paragraph of 3 were set to display: none, the first paragraph would run straight in to the third whereas if it were set to visibility: hidden, there would be a gap where the paragraph should be.
Tables
Perhaps the best way to understand the table-related display property values is to think of HTML tables. table is the initial display and you can mimic the tr and td elements with the table-row and table-cell values respectively.
The display property goes further by offering table-column, table-row-group, table-column-group, table-header-group, table-footer-group and table-caption as values, which are all quite self-descriptive. The immediately obvious benefit of these values is that you can construct a table by columns, rather than the row-biased method used in HTML.
Finally, the value inline-table basically sets the table without line breaks before and after it.
Getting carried away with CSS tables can seriously damage your accessibility. HTML should be used to convey meaning, so if you have tabular data it should be arranged in HTML tables. Using CSS tables exclusively could result in a mash of data that is completely unreadable without the CSS. Bad. And not in a Michael Jackson way.
Other display types
list-item is self descriptive and displays as in the way that you would usually expect an li HTML element to. To work properly then, elements displayed this way should be nested in a ul or ol element.
run-in makes an element either in-line or block-line depending on the display of its parent. It doesn’t work on IE or Mozilla based browsers. Very helpful.
compact also makes the element inline or block-line depending on the context. It doesn’t work that well either…
marker is used exclusively with the :before and :after pseudo elements to define the display of the value of the content property. The automatic display of the content property is already marker, so this is only useful if you are overriding a previous display property for the pseudo element.
CSS Advanced Tutorial - Page Layout
22 Jul
Posted by ProCOM on July 22, 2007 – 2:41 pm - 497 views
(No Ratings Yet) Loading ...Layout with CSS is easy. If you are used to laying out a page with tables, it may at first appear difficult, but it isn’t, it’s just different and actually makes much more sense.
You need to look at each part of the page as an individual chunk that you can shove wherever you choose. You can place these chunks absolutely or relative to another chunk.
Positioning
The position property is used to define whether an element is absolute, relative, static or fixed.
The value static is the default value for elements and renders the position in the normal order of things, as they appear in the HTML.
relative is much like static, but the element can be offset from its original position with the properties top, right, bottom and left.
absolute pulls an element out of the normal flow of the HTML and delivers it to a world all of its own. In this crazy little world, the absolute element can be placed anywhere on the page using top, right, bottom and left.
fixed behaves like absolute, but it will absolutely position an element in reference to the browser window as opposed to the web page, so, theoretically, fixed elements should stay exactly where they are on the screen even when the page is scrolled. Why theoretically? Why else - this works great in browsers such as Mozilla and Opera, but in IE it doesn’t work at all.
Layout using absolute positioning
You can create a traditional two-column layout with absolute positioning if you have something like the following HTML:
<div id=“navigation”>
<ul>
<li><a href=“this.html”>This</a></li>
<li><a href=“that.html”>That</a></li>
<li><a href=“theOther.html”>The Other</a></li>
</ul>
</div>
<div id=“content”>
<h1>Ra ra banjo banjo</h1>
<p>Welcome to the Ra ra banjo banjo page. Ra ra banjo banjo. Ra ra banjo banjo. Ra ra banjo banjo.</p>
<p>(Ra ra banjo banjo)</p>
</div>
And if you apply the following CSS:
#navigation {
position: absolute;
top: 0;
left: 0;
width: 10em;
}
#content {
margin-left: 10em;
}
You will see that this will set the navigation bar to the left and set the width to 10 em’s. Because the navigation is absolutely positioned, it has nothing to do with the flow of the rest of the page, so all that is needed is to set the left margin of the content area to be equal to the width of the navigation bar.
How bloody easy. And you aren’t limited to this two-column approach. With clever positioning, you can arrange as many blocks as you like. If you wanted to add a third column, for example, you could add a ‘navigation2′ chunk to the HTML and change the CSS to:
#navigation {
position: absolute;
top: 0;
left: 0;
width: 10em;
}
#navigation2 {
position: absolute;
top: 0;
right: 0;
width: 10em;
}
#content {
margin: 0 10em; /* setting top and bottom margin to 0 and right and left margin to 10em */
}
The only downside to absolutely positioned elements is that because they live in a world of their own, there is no way of accurately determining where they end. If you were to use the examples above and all of your pages had small navigation bars and large content areas, you would be okay, but, especially when using relative values for widths and sizes, you often have to abandon any hope of placing anything, such as a footer, below these elements. If you wanted to do such a thing, it would be necessary to float your chunks, rather than absolutely positioning them.
Floating
Floating an element will shift it to the right or left of a line, with surrounding content flowing around it.
Floating is normally used to position smaller elements within a page (in the original default CSS for this site, the ‘Next Page’ links in the HTML Beginner Tutorial and CSS Beginner Tutorial are floated right. See also the :first-letter example in Pseudo Elements), but it can also be used with bigger chunks, such as navigation columns.
Taking the HTML example below, you could apply the following CSS:
#navigation {
float: left;
width: 10em;
}
#navigation2 {
float: right;
width: 10em;
}
#content {
margin: 0 10em;
}
If you do not want the next element to wrap around the floating objects, you can apply the clear property. clear: left will clear left floated elements, clear: right will clear right floated elements and clear: both will clear both left and right floated elements. So if, for example, you wanted a footer to your page, you could add a chunk of HTML with the id ‘footer’ and then add the following CSS:
#footer {
clear: both;
}
And there you have it. A footer that will appear underneath all columns, regardless of the length of any of them.
This has been a general introduction to positioning and floating, with emphasis to the larger ‘chunks’ of a page, but remember, these methods can be applied to any element within those chunks too. With a combination of positioning, floating, margins, padding and borders, you should be able to represent ANY web design and there is nothing that can be done in tables that can not be done with CSS.
The ONLY reason for using tables for layout at all is if you are trying to accommodate ancient browsers. This is where CSS really shows its advantages - it results in a highly accessible page a fraction of the weight of an equivalent table-based page.
CSS Advanced Tutorial - At-Rules
22 Jul
Posted by ProCOM on July 22, 2007 – 2:36 pm - 496 views
(No Ratings Yet) Loading ...At-rules encapsulate a bunch of CSS rules and apply them to something specific. Woo.
Importing
The import at-rule will bolt on another style sheet. For example, if you want to add the styles of another style sheet to your existing one, you would add something like:
@import url(addonstyles.css); This is often used in place of the link element to link a CSS file to an HTML page, by essentially having an internal style sheet that looks something like this:
<style type=“text/css” media=“all”>@import url(monkey.css);</style>
The benefit of this is that older browsers, such as Netscape 4.x don’t have a clue about at-rules and so won’t link to the style-sheet, which, if you have well-structured markup, will leave functional (although un-styled) plain HTML.
Media types
The media at-rule will apply its contents to a specified media, such as print. For example:
@media print {
body {
font-size: 10pt;
font-family: times new roman, times, serif;
}
#navigation {
display: none;
}
} The media-type can be:
all- for every media under, over, around and in the sun.aural- for speech synthesizers.handheld- for handheld devices.print- for printers.projection- for projectors.screen- for computer screens.
You can also use braille, embossed, tty or tv.
Note: having said all of that, the only media-types currently supported by IE are all, screen and print.
Character sets
The charset at-rule simply sets the character set encoding of an external stylesheet. It would appear at the top of the stylesheet and look something like @charset "ISO-8859-1";
Font faces
The font-face at-rule is used for a detailed description of a font and can embed an external font in your CSS.
It requires a font-family descriptor, which the font can be referenced to, the value of which can be an existing font name (so overwriting that font when conditions are met) or it can be a completely new name. To embed a font, the src descriptor is used. Other descriptors added to the font-face at-rule become conditions for that embedded font to be used, for example, if you were to add a font-weight: bold style to the at-rule, the src of the font-family will only be applied to a selector with the font-family property if the font-weight property is also set to bold.
You might use a font-face at-rule like this:
@font-face {
font-family: somerandomfontname;
src: url(somefont.eot);
font-weight: bold;
}
p {
font-family: somerandomfontname;
font-weight: bold;
}
This will apply the somefont.eot font to paragraphs (it would not if the p selector was not set to font-weight: bold).
Support for embedded fonts is patchy at best. Mozilla based browsers don’t support them and have no immediate plans to do so. Only Internet Explorer seems to have any kind of decent support and this is by no means straightforward. To embed fonts with IE, you need to use Microsoft’s WEFT software, which will convert the characters of a TrueType font into a condensed OpenType font (and this can then only be used on the URI that you specify). Because of this limited (and quite complicated) compatibility, it is best not to use fonts that do not have an adequate alternative system font.
Pages
The page at-rule is for paged media and is an advanced way to apply styles to printed media. It defines a page block that extends on the box model (see Margins and Padding) so that you can define the size and presentation of a single page.
There are a number of conventions that apply to page at-rules, such as there is no padding or border and this isn’t a computer screen we’re talking about - pixels and ems as units aren’t allowed.
There are a number of specific properties that can be used, such as size, which can be set to portrait, landscape, auto or a length. The marks property can also be used to define crop marks.
@page {
size: 15cm 20cm;
margin: 3cm;
marks: cross;
}
page at-rule, which would take the form of @page :pseudo-class { stuff }.
:first applies to the first page of the paged media.
:left and :right apply to left-facing and right-facing pages respectively. This might be used to specify a greater left margin on left-facing pages and a greater right margin on right-facing pages.
There are a number of other facets specific to the page at-rule such as page-breaks and named pages, but seeing as this at-rule works on hardly any browser, you’ve probably wasted enough time reading this part anyway. It’s a nice enough idea though…
CSS Advanced Tutorial - Pseudo Elements
22 Jul
Posted by ProCOM on July 22, 2007 – 2:34 pm - 430 views
(No Ratings Yet) Loading ...Pseudo elements suck on to selectors much like pseudo classes, taking the form of selector:pseudoelement { property: value; }. There are four of the suckers.
First letters and First lines
The first-letter pseudo element applies to the first letter of an element and first-line to the top line of an element. You could, for examples create drop caps and a bold first-line for paragraphs like this:
p:first-letter {
font-size: 3em;
float: left;
}
p:first-line {
font-weight: bold;
}
Before and after
The before and after pseudo elements are used in conjunction with the content property to place content either side of an element without touching the HTML.
The value of the content property can be open-quote, close-quote, no-open-quote, no-close-quote, any string enclosed in quotation marks or any image using url(imagename).
blockquote:before {
content: open-quote;
}
blockquote:after {
content: close-quote;
}
li:before {
content: “POW: “
}
p:before {
content: url(images/jam.jpg)
}
Sounds great, dunnit? Well, as with so many things (-sigh-), most users won’t be able to see the before or after effects because IE just can’t be bothered with them. Lazy lazy lazy.
CSS Advanced Tutorial - Specificity
22 Jul
Posted by ProCOM on July 22, 2007 – 2:29 pm - 602 views
(No Ratings Yet) Loading ...If you have two (or more) conflicting CSS rules that point to the same element, there are some basic rules that a browser follows to determine which one is most specific and therefore wins out.
It may not seem like something that important, and in most cases you won’t come across any conflicts at all, but the larger and more complex your CSS files become, or the more CSS files you start to juggle with, the greater likelihood there is of conflicts turning up.
If the selectors are the same then the latest one will always take precedence. For example, if you had:
p { color: red; }
p { color: blue; }p elements would be coloured blue because that rule came last.
However, you won’t usually have identical selectors with conflicting declarations on purpose (because there’s not much point). Conflicts quite legitimately come up, however, when you have nested selectors. In the following example:
div p { color: red; }
p { color: blue; }
It might seem that p elements within a div element would be coloured blue, seeing as a rule to colour p elements blue comes last, but they would actually be coloured red due to the specificity of the first selector. Basically, the more specific a selector, the more preference it will be given when it comes to conflicting styles.
The actual specificity of a group of nested selectors takes some calculating. Basically, you give every id selector (”#whatever”) a value of 100, every class selector (”.whatever”) a value of 10 and every HTML selector (”whatever”) a value of 1. Then you add them all up and hey presto, you have the specificity value.
phas a specificity of 1 (1 HTML selector)div phas a specificity of 2 (2 HTML selectors; 1+1).treehas a specificity of 10 (1 class selector)div p.treehas a specificity of 12 (2 HTML selectors and a class selector; 1+1+10)#baobabhas a specificity of 100 (1 id selector)body #content .alternative phas a specificity of 112 (HTML selector, id selector, class selector, HTML selector; 1+100+10+1)
So if all of these examples were used, div p.tree (with a specificity of 12) would win out over div p (with a specificity of 2) and body #content .alternative p would win out over all of them, regardless of the order.
The Unofficial Informix FAQ
22 Jul
Posted by ProCOM on July 22, 2007 – 2:19 pm - 808 views
(No Ratings Yet) Loading ...General
- Is product x (or version y of product x) still supported? Are there plans to stop supporting it? See the Informix Product Lifecycle document for descriptions of the different support categories and an Excel- or Acrobat- format list of products.
- What patches do I need for platform x?The release notes (in $INFORMIXDIR/release) contain a list of patches which have been determined by Informix Product Development to be necessary for running the product. This list is created prior to release of the product, so any new patches identified after the release of the product (e.g. as a result of specific customer problems) will not be listed in the release notes, but will probably be listed in the release notes of the next version. Therefore, if you are looking for “the definitive list of patches that should be installed”, there is no such thing. Informix generally recommends that the customer consult their OS vendor for a list of recommended patches.
You may be able to find more helpful information at the following vendor sites:
- SunSolve Online
- HP Electronic Support Center (US, Canada, Asia-Pacific, and Latin-America)
- HP Electronic Support Center (Europe)
- How can I tell what OS patches are already installed?
- Solaris: Run “/bin/showrev -p” or see /var/sadm/patch
- HP: Run “/usr/sbin/swlist” as root
- What documentation does Informix have on Year 2000 compliance?
This varies by Operating System.
Which versions support DBCENTURY?
Do I need to upgrade my engine and/or my front-end for DBCENTURY?See Informix’s webpages Year 2000 and Informix Products and Year 2000 Support in Client API Products for information on Year 2000 compliance.
Note that all Informix products are “Year 2000-compliant” in that they have always stored dates with the complete 4-digit century (actually, as an integer representing the number of days from December 31, 1899). What DBCENTURY adds is the ability to have 2-digit years expanded automatically to the appropriate century, as determined by the user.
2-digit years are expanded to 4-digits when the string is converted to a DATE type. If you are using 4GL, for example, the statement:
LET date_variable = "9/9/99"
causes the expansion to be done by 4GL before assigning the value to date_variable. However, the statement:
INSERT INTO orders (order_date) VALUES ("9/9/99")
sends the string to the engine and causes the expansion to be done by the engine. Whether you need to recompile your 4GL applications with a DBCENTURY-aware version of 4GL, or whether it is sufficient to upgrade the engine and set DBCENTURY in the engine when initializing, will depend on which side is doing the string-to-date conversion.
Generic SQL
- How can I do case-insensitive searches, e.g. search for “Smith”, “smith”, “SMITH”, or even “sMiTh” ?
- Break each word down to the individual letters, and search for the regular expression using both upper- and lower-case letters, e.g. to search for lname equal to “Smith”:
- Using an UPPER() function (see next question below), convert both the column and the filter value to uppercase, e.g.
- Create a dummy column to store an all-uppercase copy of the value, e.g.
SELECT * FROM customer WHERE lname MATCHES "[Ss][Mm][Ii][Tt][Hh]";
The IIUG Software Repository, 4GL (noncaseqry) contains a 4GL function which will create this clause for you.
SELECT * FROM customer WHERE UPPER(lname) = "SMITH";
ALTER TABLE customer ADD (uc_lname CHAR(20)); UPDATE customer SET uc_lname = UPPER(lname); SELECT * FROM customer WHERE uc_lname = "SMITH";
If you are not using Illustra or Universal Server, this is not an easy thing to do. If the data is stored in mixed-case, your options are:
Option 3 is probably the best, because it is the only one which will make use of an index on lname. Option 1 will do a sequential scan (or possibly a scan of the entire index) to compare the value of lname to the regular expression. Option 2 will also do a sequential scan or scan of the index, perform UPPER() on each lname value, and compare the result to “SMITH”. These will be slow.If you are using Universal Server or Illustra, you can create a user-defined function, e.g. UPPER(), and create an index on UPPER(lname). This would allow you to store lname in mixed-case, and still use the index to search for “Smith”. A query such as:
SELECT * FROM customer WHERE UPPER(lname) = "SMITH";
would use an index on UPPER(lname).
Informix-SQL and 4GL have built-in functions UPSHIFT() and DOWNSHIFT() which convert a character string to uppercase/lowercase.
Version 7.3 engines now have built-in functions UPPER() and LOWER() to convert character strings to uppercase/lowercase.
For pre-7.3 engines, see the IIUG Software Repository, Misc (upper_spl, upshift_spl) for sample stored procedures to convert character strings to uppercase. These can easily be modified to convert strings to lowercase.
Version 7.3 engines provide these functions for Oracle compatibility. The functions work exactly as the Oracle functions do, except for the DATE functions, due to differences in the date types.
For pre-7.3 engines, check the IIUG Software Repository, SQL (orclproc).
CREATE PROCEDURE Cut (string VARCHAR(255), delimiter CHAR(1))
RETURNING VARCHAR(255);
DEFINE i INTEGER;
DEFINE loc INTEGER;
DEFINE res VARCHAR(255);
LET loc = FindStr(string, delimiter);
IF loc = 0 THEN
RETURN string;
END IF;
LET res = '';
FOR i = 1 TO loc - 1
LET res = res || string[1,1];
LET string = string[2,255];
END FOR;
RETURN res;
END PROCEDURE;
CREATE PROCEDURE FindStr(str VARCHAR(255), ch CHAR(1))
RETURNING INTEGER;
DEFINE i INTEGER;
FOR i = 1 TO length(str)
IF str[1,1] = ch THEN
RETURN i;
END IF;
LET str = str[2,255];
END FOR;
RETURN 0;
END PROCEDURE;
When I upgraded from OnLine 4.x to 5.0 (or later), performance on queries WHERE char_col = 14 slowed to a crawl. Why?Contrary to what you might expect, a query “WHERE char_col = 14″ does not first convert 14 to the character string “14″ and then search for that. (I believe that version 4.x did that, and it was the fix to that bug - because it is a bug - that caused performance to slow down on upgrades from 4.x to anything else.) Instead, each value of char_col is converted from CHAR to INT, and then compared with 14. Why?
Remember that an index on a character column is sorted based on the character values, starting from the first character of the field. Therefore, in a char(5) column, the following values are sorted in this order: ” 14″, ” 21″, “0014″, “099″, “14″, “14.0″. Four of these values are equal to 14, and should be returned by a query WHERE column = 14. If, however, the value 14 was first converted to CHAR, resulting in the value “14″, only the fifth value above would be returned. Since the values equating to 14 can be spread throughout the index, the index cannot be used to find them. This query will use a sequential scan, or in the best case, an index scan, converting every value of char_col to INT before testing. For this reason, this use of implicit type conversion should be avoided. If the data is in character format, it should be compared with a character string, e.g. “14″.
NULL does not have a type, and therefore cannot be treated as a constant in a SELECT statement. The way to work around this is to create a stored procedure which returns a null of the datatype you want.
Both FLOAT and SMALLFLOAT are very limited in the precision they support, which depends on how the specific computer internally stores floating point numbers. If the value contains more digits than the floating-point representation on the computer can support, the least-significant digits are treated as zeros. The erroneous value displayed is usually not actually an error in the display (although there have been bugs entered against ISQL and DB-Access for not displaying enough decimal places in the Query-Language option), but in the way it is stored. This is documented most thoroughly in the manual Informix Guide to SQL: Tutorial, version 7.1, p. 9-10, and also in the Informix Guide to SQL: Reference, version 7.1, p. 3-15.
The following SQL query demonstrates a problem in date/datetime arithmetic:
SELECT order_date + 1 UNITS MONTH FROM orders
This returns error -1267 “The result of a datetime computation is out of range” because one of the values of order_date is May 31, 1994. Adding a month to this would result in June 31, 1994, which is an invalid date. This is in accordance with ANSI standards.Couldn’t they have just returned June 30, 1994, you may ask. Yes, they could have, but you probably didn’t really want to do that anyway. What happens when you add a month to June 30, 1994? You get July 30, 1994, which is not the same as if you added two months to May 31, 1994.
What you probably want is a procedure based on business rules; for example, a procedure that finds the last day of a month.
OnLine
- How can I re-organize my table to consolidate my extents?
How can I move my table from one dbspace to another?
ALTER FRAGMENT ON TABLE table1 INIT IN targetdbs;
First of all, it is important to realize that Dirty Read affects only queries, not updates and deletes. It is not possible to do a “Dirty Update”; that is, you cannot choose to ignore a locked row when doing an update. To do that, you would have to declare a simple cursor using Dirty Read, and then update individual rows based on the primary key, ignoring any lock errors, like so:
WHENEVER ERROR CONTINUE -- because the UPDATE will fail on a lock
SET ISOLATION DIRTY READ
DECLARE c1 CURSOR FOR SELECT unique-key WHERE { whatever your criteria are }
FOREACH c1 INTO x
UPDATE table WHERE unique-key = x
END FOREACH
The only isolation level that has an impact on updates and deletes is Repeatable Read (more on this later); otherwise isolation level on updates and deletes is Committed Read.Next, think about how you are accessing the rows. Keep in mind that if the row is locked, the engine cannot read it, even just to determine whether it meets your criteria or not. Therefore, if any row which your session needs to read is locked, your query will fail with a lock error. The way to avoid a problem is to use an index to go directly to the row(s) you need. If your query uses a sequential scan, it will eventually fail if even one row in the table is locked, regardless of what that one row is. Also, be aware that even if your query uses an index, if your index cannot isolate the row you want, you will have to scan the rows returned by the index, which will fail if any of these rows are locked. It is very important to make sure that your queries use the best access path, which usually means an index which takes you directly to your row, and not requiring reading any other rows.
Ex.1. User1 updates rows where customer_num = 10. User2 attempts to update rows where customer_num = 20. Table has no indexes, therefore user2 does a sequential scan, and fails when he reaches customer_num 10, which is locked by user1. Although user1’s row contains customer_num 10, not 20, the engine cannot verify that the row does not meet the criteria, because it is locked. If there were an index on customer_num, user2 could use the index to go directly to customer_num 20, never having to read the row locked by user1.
Ex.2. User1 updates rows for city=”Menlo Park”, state=”CA”. User2 attempts to update rows for city=”San Francisco”, state=”CA”. State is indexed. User2 reads the index on state, which shows three rows with state=”CA”. User2 must then read the data rows pointed to by this index, and gets an error when he reaches the Menlo Park row locked by User1. If the index were on (state, city), user2 could go directly to the “San Francisco” row, and never try to read the row locked by user1.
So, how does Repeatable Read affect updates? The definition of Repeatable Read is that if the user were to re-run the same query/update/delete within that transaction, the same results would be returned. That is, data cannot be changed in any of the rows that were selected, nor can any more rows be added that would fit the criteria of the query/update/delete. For example, if I update all rows where customer_num = 10, then until I commit the transaction, not only can you not update any of those rows, but you cannot insert any new rows with customer_num = 10. This means that the adjacent index item must be locked to prevent you inserting this new row; this is very similar to Adjacent Key Locking (see the below-mentioned TechNotes article for more info).
For a full description of Adjacent Key Locking and Key Value Locking, see TechNotes 1994, Volume 4, Issue 3&4: B+ tree Item Locking in Informix-OnLine 5.x and Informix-OnLine Dynamic Server.
When you get a lock error on a FETCH, your next FETCH will re-attempt to fetch that same row, hoping that the row has been released. There is no way to change this behavior.
What you can do is use two cursors, with different isolation levels, to skip locked rows. Here’s a 4GL code fragment I wrote which will do that:
DEFINE c_num INT
DEFINE c_rec RECORD LIKE customer.*
WHENEVER ERROR CONTINUE
SET ISOLATION TO DIRTY READ
DECLARE c1 CURSOR FOR SELECT customer_num FROM customer
FOREACH c1 INTO c_num
SET ISOLATION TO COMMITTED READ
DECLARE c2 CURSOR FOR SELECT * FROM customer WHERE customer_num = c_num
OPEN c2
FETCH c2 INTO c_rec.*
IF sqlca.sqlcode = 0 THEN
DISPLAY c_rec.customer_num
ELSE
DISPLAY "error ", sqlca.sqlcode, ": skipping to next record"
END IF
SET ISOLATION TO DIRTY READ
END FOREACH
08:50:57 mt_shm_init: can't create virtual segment 08:50:57 shmat: [EINVAL][22]: shared memory base address illegal 08:50:57 using 0x80600000, needs 0x080800000
If I change the SHMBASE to the needed value, I get the same message, only with different values. What’s wrong?This error message is usually returned when some kernel parameter (usually SHMMAX) is not set high enough either to allocate the needed shared memory in a single shared memory segment, or to allocate the memory at all (usually the single segment). Try increasing SHMMAX and re-build your kernel.
The theoretical maximum size of a chunk is 1,048,576 pages, regardless of whether you are on a 64-bit OS. This is because of the page numbering convention used by OnLine: the page address is represented in hex as CCCPPPPP — 3 hex digits for chunk number, 5 hex digits for logical page number in the chunk. So the maximum page number in a chunk is 0xFFFFF, or 1048575 (since the first page number is 0, this equates to a maximum of 1048576 pages). This means that if your port uses a 2KB pagesize, the maximum size of a chunk is 2GB. If your port uses a 4KB pagesize, the maximum size of a chunk is 4GB. On some OS’s, lseek may fail if a value over 2GB is passed to it; this will limit your chunk size to 2GB. For this reason also, the offset+size of the chunk may be limited to 2GB, meaning that you cannot create a 2GB chunk with a .5GB offset, or any other combination that would add up to more than 2GB.
The maximum supported chunk size is 2 GB. This is documented in the Informix-OnLine Dynamic Server Administrator’s Guide. For Version 7.1, it is on page 14-5.
When your table grows, new extents are allocated to that table from the free space in the dbspace. When rows are deleted, these pages are not released. Even if all the pages in an extent are empty, the extent remains allocated to the table. The only way to reclaim the extent is to re-build the table, possibly by using ALTER TABLE or by exporting it.
Increase (or set) MI_WEBMAXHTMLSIZE in your web.cnf file.
The Webdriver allocates 8k of memory for storing the results of a app-page request. It then dynamically allocates more memory as needed, up to the value of MI_WEBMAXHTMLSIZE.
MI_WEBMAXHTMLSIZE defaults to 32K and can be modified by adding it to the web.cnf file. The syntax is:
MI_WEBMAXHTMLSIZE n
where n is the maximum number of BYTES that you want to allocate to a Webdriver thread.
In some versions of Web DataBlade, there is a MAXROWS attribute for the MISQL tag to limit the number of rows returned. This is documented in the Informix Web DataBlade User’s Guide. In later versions, this has been superceded by WINSIZE. Check the release notes for the Web DataBlade.
Archiving
- Does ontape support archiving (or logical log backup) to disk?
- What is the maximum tape size that can be used with ontape?
- Can I take an archive from OnLine version x and restore it on OnLine version y?
- Can I use onunload to unload a table from OnLine version x and onload it on OnLine version y?
- Can I use OnBar’s point-in-time recovery to recover a dbspace up to a specific time?
Not only is this question frequently asked, it is frequently answered incorrectly.
YES - ontape supports disk files. If you need documentation, see page 12-6 in the INFORMIX-OnLine Dynamic Server, Archive and Backup Guide, Version 7.1:
A user with one tape device might want to redirect a logical-log backup to disk file while the tape device is in use for an archive.
This has been issued as Tech Alert 6125. Tell this to any Informix person who tells you this is not supported.
The maximum TAPESIZE value is 2147483647 (2 Gig), but TAPESIZE is specified in Kbytes, making the maximum tape size 2 TB (Terabytes).
Some platforms limit to 2GB the amount that can be written ( using write() ) on a single open(). Since ontape uses open() and write(), this will limit the tape size to 2GB; this is an OS, not Informix, limitation.
If you are using a Unix file for your tape device, however, you may be limited to 2GB. This is bug 76256, which is fixed in 7.24 and later versions.
No. It is only supported to restore an archive taken with the same version of OnLine.
No. Onload only works on onunload files taken from the same version of OnLine. See the Informix-OnLine Dynamic Server Administrator’s Guide, in the chapter OnLine Utilities, for onload/onunload:
Constraints That Affect onload and onunload
The original database and the target database must be from the same release of OnLine. You cannot use onunload/onload to move data from one release level to another.
No. Although OnBar can be used to restore a specific dbspace, and also provides point-in-time recovery, they cannot be used together to bring a dbspace up to a point-in-time earlier than the rest of the system. A warm restore of a dbspace must include a logical recovery which brings the dbspace in sync with the rest of the instance.
ESQL/C
- Can I compile ESQL/C code for use with C++ ?
- You break out the ESQL/C code from the C++ code; they must be in separate modules.
- You do not use structs containing function members in the code that ESQL/C is aware of: no member functions or classes.
- You do not use C++ comments in the code processed by ESQL/C.
There is no supported way to use ESQL/C with C++. However, it is possible to combine ESQL/C code with C++ if:
See the IIUG Software Repository, ESQL/C (esql_c++) for detailed directions.
Informix Universal Server - Kerry’s Perspective
22 Jul
Posted by ProCOM on July 22, 2007 – 2:10 pm - 386 views
(No Ratings Yet) Loading ...The original of this article appears at The Esperanto Group. Some slides from “introducing Informix as a general database platform. In HTML format.” are there as well.
Informix Universal Server
Kerry Sainsbury (kerry@kcbbs.gen.nz), 8 March 1999
Ever since Informix “released” Universal Server at the end of 1997, I’ve been keen to get my hands on it. Now that a trial edition has been made available at www.intraware.com, I finally got the opportunity to try it out. This document describes some of the very cool features of “Universal Server” that distinguish it from the regular “Dynamic Server”, yet doesn’t shirk the important task of highlighting some of the things that really *suck* about the product.
This document also works as a tutorial, so if you want to download the product and fire-up dbaccess, feel free!
Row Types
Informix Universal Server (IUS) gives us the concept of ‘row types’. These are approximately the equivalent of ‘classes’ in an object-oriented environment, just without any methods:
create row type person_t ( name varchar(30), birthdate date );
‘row types’ support inheritance. Here’s the definition of a cartoon-character class, which inherits all the properties of the ‘person_t’ type, and adds a ‘popularity’ indicator in the form of an integer:
create row type character_t ( popularity int ) under person_t;
These ‘row types’ are, like classes, just definitions however. In order to actually store some instances of the classes, we need to create a database table in which to put them. The following table, called ‘character’, stores rows of the ‘character_t’ class:
create table character of type character_t;
Inserting data into this table is done the same way as a regular SQL’92 table:
insert into character ( name, birthdate, popularity )
values ("Mickey", "02/02/1946", 10 );
insert into character ( name, birthdate, popularity )
values ("Donald", "03/03/1953", 9 );
Classes (ie: ‘row types’) can include other classes within them. That is, they support both inheritance (’is-a’) and composition (’has-a’) models. The follow example first creates an ‘address’ class, then an ‘employee’ class which includes an address, and finally an employee table to store the employee objects.
create row type address_t ( description varchar(30),
city varchar(30) );
create row type employee_t ( startdate date, salary int,
address address_t ) under person_t;
create table employee of type employee_t;
Now when we want to insert a row into the ‘employee’ table, things start getting a little more interesting. The insertion of the ‘address’ portion of the ‘employee’ needs to be explicitly labelled with the ‘row(….)::address_t’ notation:
insert into employee ( name, birthdate, startdate, salary, address )
values ("Joe Bloggs", "7/9/1963", "7/10/1984", 90000,
row("101 Toontown Sq, Disneyland", "Anaheim CA")::address_t );
At this point we encounter the first limitation of IUS — I’m damned if I can figure out how one can insert a row with partial row type information for the address. I seem unable to ignore the ‘city’ portion of the address, for example. You have to specify the full ‘address’ information.
Let’s move on to some cool stuff now, and worry about the implications of the above limitation later.
Next up we create a new sub-class of ‘employee’, especially for managers. Then we create a ‘management’ table, in which to place our ‘management’ objects. The key thing to notice is that we will create the new table ’under’ the employee table - previously we had only created new ‘row types’ under other ‘row types’.
create row type management_t ( stresslevel integer ) under employee_t; create table management of type management_t under employee;
Now let’s put a row into the ‘management’ table - that ‘row(…)::address_t’ notation’s still there, remember.
insert into management (stresslevel, name, birthdate,
startdate, salary, address )
values (99, "Walt Disney", "7/9/32", "7/10/45", 12500000,
row("Rich Dude Hills", "Beverly Hills CA")::address_t );
And let’s do a simple SELECT, to see what we’ve got in management.
select * from management;
Just the ‘Walt Disney’ row is returned, but what if we SELECT from ‘employee’:
select * from employee;
Cool - Table inheritance! Not only do we see our ‘Joe Bloggs’ row, we also see the ‘Walt Disney’ row we inserted into the ‘management’ table. Very excellent Informix! And just to prove that we can extract just the ‘employee’ data if we need to, we can use the following syntax:
select * from only(employee); { Now just get employees }
One issue with these ‘row types’ is that you need to have a development tool that understands how to interpret these special data types. If you don’t, you’ll need to select the nested row type information columns individually, eg:
select name, birthdate, startdate, salary,
address.description,
address.city from employee;
Updating tables is fairly straight-forward. The only ‘tricky’ part is updating the ‘nested row type’ columns, like address. The following example fixes the address’s city information in the employee table:
update employee set address.city = "Auckland" where address.city = "Oakland"
In order to update the entire ‘address_t’ information, you’ll need to use the same ‘row(…::address_t) notation we used in the ‘insert’:
update employee
set address = row(address.description,
'Auckland')::address_t
where address.city = "Oakland"
Tables don’t have to be created ‘of type x_t’. You can create ‘regular’ database tables which just use ‘row types’ as column definitions:
create table ad_agencies( name varchar(30),
address address_t, contact person_t );
insert into ad_agencies values
("Saachi and Saachi",
row("321 Expensive Location", "City")::address_t,
row("Mrs Shipley", "11/04/1942")::person_t );
Stuff that sucks
There is no ‘alter row type’ command, in fact, if you have a ‘typed table’ (like the ‘employee’ table which was created ‘…of type employee_t’), the only column adjustment ‘alter table’ can do is ‘drop type’.
‘drop type’ retains all the data in the table, but replaces the ‘row type’ structures with regular database columns. Our ‘employee’ table would change from (startdate date, salary int, address address_t) to (startdate date, salary int, description varchar(30), city varchar(30));
The reason you might want an ‘alter row type’ is if, for example, you decide it would be a good idea to alter ‘person_t’ to include the person’s shoe size. Not unreasonable, huh?
The bad news is that you can’t change the definition of ‘person_t’ without quite a bit of work. You’ll have to alter every table which features ‘person_t’ so that it doesn’t include ‘person_t’, insert a column to hold the new data, and recreate the row-type. For simple tables like ‘character’, this isn’t too difficult:
alter table character drop type; alter table character add( shoesize integer before popularity );
…but then we get to employee and management (and their associated row types)
“alter table employee drop type” doesn’t work, presumably because the ‘management’ table is ‘under’ it. “alter table management drop type” doesn’t work, presumably because it’s ‘under’ employee.
In order to make this work, you would have to:
- Select the data from every table which uses the ‘person_t’ row type, and put it into a temporary tables.
- Alter the temporary tables, placing a new ’shoe_size’ column after the ‘birth_date’ column in each of the tables.
- Drop all the original tables.
- Drop all the person_t row type descendants
- Drop the ‘person_t’ row type.
- Recreate the original tables, using the structure of the new temporary tables.
- Create the new ‘person_t’ row type.
- Recreate all the person_t row type descendants.
- Alter the tables, addings ‘person_t’ row types (and descendants) as appropriate.
Just absurd! Even if somehow you managed to keep all the above items straight, I’m sure the referential integrity contraints would drive you nuts anyway!
I’m not sure it would work anyway. What happens if you have a non-typed table, like the ‘ad_agencies’ table, and you attempt to remove the ‘employee_t’ type - you’re going to end up with two ‘name’ columns (one for the name of the agency, and another for the name of the employee). Yuck!
Of course none of this matters if you create databases which never need to be changed :-)
DISTINCT DATA TYPES
IUS adds what it refers to as ‘distinct’ data types. They are an approximation of SQL92 ‘domains’, which act as aliases to built-in datatypes:
create distinct type phone_number_t as char(7); create table informant(name char(10), phone_number phone_number_t);
This is fine, but doesn’t really go far enough. You can’t specify ‘not null’. You can’t specify a default value. Most importantly, there is no ‘alter distinct type’, so if you decide that phone numbers need to be longer, you’re simply out of luck.
Somewhat assumingly, there is a ‘drop type’ command, which is quite happy to execute, even if there are tables which use the type! Recreating the dropped type won’t let you recover your table either :-(
drop type phone_number_t restrict; select * from informant; // bang!!
OPAQUE DATA TYPES
…are custom data types, which the engine knows nothing about. You define:
- A data structure for how the data is stored on disk
- Support functions to determine how to convert between the disk format and the user format
- Secondary access methods that determine how the index on this data type is built, used, and manipulated
- User functions that use the data type
- A row in a system catalog table to register the opaque type in the database
- Methods to control use of operators, aggregates, and casting.
This is all defined via a C-based API. I did not investigate this facility - I couldn’t see myself ever needing it!
SETS
IUS has some simple support for ’sets’, eg:
create table item( item_number integer,
description varchar(40),
colours set (char(10) not null ));
insert into item values ( 1234, 'gardening clogs',
"set{'red','blue','green'}" );
insert into item values ( 5678, 'pruning hosery',
"set{'yellow'}" );
What we’ve defined here the fact that the ‘item’ table contains a set called ‘colours’. We then inserted some values into the table. The big thing that’s missing, is that we don’t have the ability to specify what the valid values for the set are — we can insert ‘Tuesday’ into our set of colours, and nothing will complain! There should be (but isn’t) a syntax something like the following:
create set colours_t char(10) of
{'red', 'blue', 'green', 'yellow', 'magenta'}
which would then support:
create table item( item_number integer,
description varchar(40),
colours colours_t not null);
Enough of my fantasys however. The ’set’ support that does exist lets us do things like list the items which are available in red:
select * from item where 'red' in colours;
… and list the items which have more than one colour available:
select * from item where cardinality ( colours ) > 1;
There are some variations on sets - ‘multisets’ allow a value to be repeated in the set, and ‘lists’ are ordered sets, eg:
create table city_temps( city_name char(30),
avg_temps LIST(integer not null));
What’s naff about sets?
When you update a collection type, you must update the entire collection! You can’t update individual items in the collection. Doh!
I can’t see how I can access the elements of the various ’set’ types. There was no syntax I could find that would tell me what colours a given ‘item’ was available in.
I was expecting, at least, some syntax which would return a pseudo-join result set, like this:
item_number description colours 1234 gardening clogs red 1234 gardening clogs blue 1234 gardening clogs green 5678 pruning hosery yellow
I was unable to find any syntax to access the ‘ordered’ information in a ‘list’ set. How can I iterate through the list? Find the first, or last element in the list? I figure I must be missing something!
STORED PROCEDURES
IUS stored procedures support method overloading. We’re finally back at something cool! Let’s create a procedure which calculates a 1% bonus for employees.
create procedure bonus( emp employee_t ) returning money; return emp.salary * 0.01; end procedure;
If we run the procedure, we see that both regular employees (Joe Bloggs) and management (Walt Disney) get the same bonus:
select name, salary, bonus(employee) from employee
Now let’s create another ‘bonus’ procedure, but this once takes a ‘management_t’ row type as an argument, instead of the ‘employee_t’ the previous one did. Notice that we do not have to drop the existing ‘bonus’ procedure:
create procedure bonus( emp management_t ) returning money; return emp.salary * 0.5; end procedure;
Running the same SELECT statement now gives the regular employees a 1% bonus, while management get a 50% bonus:
select name, salary, bonus(employee) from employee
While this is very nice stuff, I have to wonder why the procedures have been created seperately from the row-type information. One of the big things about the OO paradigm is the way data and procedures are drawn together, so I would’ve expected the syntax to be more like:
select name, salary, employee.bonus() from employee
Perhaps it’s a SQL3-ism.
OBJECT IDs - ‘ref’ and ‘deref’
I read Michael Stonebraker’s “Object-Relational DBMSs - Tracking the next great wave’ book when creating this document. In his book he talks about the ability to include a reference to another object in a table, eg:
create row type department_t( name string, manager ref(management_t) references management, workers set(ref(employee_t) references employees ) );
The above example defines a department, which directly references a row in the management table, and a set of rows in the employee table.
This techique would move much of the need for joins, and be generally rather sexy. In fact Stonebraker says that in order for row types to be useful, an object-relational db must include these ‘ref’ and ‘deref’ functions.
So guess what’s not in IUS?
Summary
The release I downloaded (9.14.TC1 for Windows NT, 17 Apr 1998) has some very interesting technology underneath it, but I was disappointed at its lack of suitability for any ‘real-world’ database - meaning, one in which the schema may need to be altered!
Finally, I need to credit Mary Mudie’s “Informix-Universal Server” chapter in “Informix Unleashed”, which helped provide some of the examples I’ve used in this document.
Kerry Sainsbury
Understanding and Using Informix Stored Procedures
22 Jul
Posted by ProCOM on July 22, 2007 – 2:06 pm - 1,490 views
(1 votes, average: 4 out of 5) Loading ...Understanding and Using Informix Stored Procedures
Rafal Czerniawski
Introduction
Since the introduction of stored procedures in version five of the Informix engine there has been a lot of discussion about their use, benefits, limitations and their impact on the applications’ performance. They have gained acceptance in the client/server environment but their usefulness in the single host type environment has been seriously underestimated. As with any tools, before using them it is important to consider whether it is the right tool for the job at hand. To be in the position to make this decision you need to understand its workings, how well it integrates with your environment and development tools, the type of application you are developing and whether it is a small scale application or large mission critical OLTP system. When used appropriately stored procedures can be a very powerful tool both in the client/server environment and single host type environment.
The aim of this article is to provide a high level overview of stored procedures and their features, particularly the advantages and disadvantages of their use from the two perspectives: client/server and single host type environment. I will also discuss issues to consider before using stored procedures on a large scale. Hopefully by the end of this article you will be in the position to decide whether stored procedures are the right for you.
Overview of Stored Procedures
Stored procedures can be thought of as user defined program modules stored in the database and executed by the database engine. They are the simplest form of remote procedure calls where the remote client sends a message to the server, which then executes the procedure. Stored procedures are written in Stored Procedure Language which is a superset of SQL with additional looping and conditional statements. When a stored procedure is created it’s source and the compiled executable is stored in the system catalog tables. From the administration point of view they are like any other database objects which can be created and dropped using ‘create’ and ‘drop’ statements. Their permissions can be administered using grant statements. The source of stored procedures can be extracted from the system catalogs using ‘dbschema -f’ utility. To fully appreciate the benefits and shortcomings of stored procedures it is important to understand what happens at the time stored procedure is created and executed.
Stored procedures are created using a ‘create procedure’ statement. The following steps take place at the time of creation:
1. The source is parsed and stored in the SYSPROCBODY system table.
2. The SQL is extracted from the source of stored procedure and a dependency list built. The dependency list is an index to all database objects accessed by the stored procedure. It is used by Informix at the time of execution to verify that all objects referenced still exist and have not changed.
3. The extracted SQL is optimised to produce a query plan, just like any other SQL.
4. The dependency list and query plan are stored in the system table: SYSPROCPLAN. Note that while the stored procedure is created Informix places exclusive locks on some of the system tables.
Stored procedures are executed using ‘execute procedure’ statement. Following steps take place during execution:
1. Retrieve the dependency list, pcode and query plan for the stored procedure from the system catalog tables if they are not already in the stored procedure cache.
2. Verify that the objects in the dependency list have not changed since the time the stored procedure was created.
3. If no changes were detected execute procedure.
4. If there were changes to the database object, the SQL is re-optimised and a new query access plan is stored in the catalog tables. The procedure is then executed.
The SQL in the stored procedures is optimised at the time of its creation except in the following cases:
1. When the structure of the tables referenced by the procedure or their indices have changed it will be re-optimised at the first execution after the change.
2. If stored procedure uses temporary tables it will be re-optimised every time it is executed. This has been fixed in later versions of 5.x
3. If the stored procedure does not contain any DML statements (e.g. select, insert, update, delete) it will be re-optimised every time it is executed. This has been fixed in later versions of 5.x. The re-optimisation of SQL during stored procedure execution should be avoided as it results in exclusive locks on the SYSPROCBODY table and can lead to serious concurrency problems.
Stored Procedures System Tables
The following system tables are used to store information about stored procedures:
SYSPROCEDURES - General information
SYSPROCBODY - Pcode executable
SYSPROCPLAN - Dependency list and query plans
SYSPROCAUTH - Security authorisation
Security and Stored Procedures
Stored procedures can operate in two security modes at the time of execution:
1. Owner Privileged Procedures
These are stored procedures created by the user with ‘resource’ authority on the database. The stored procedure inherits all the permissions on the objects owned by the creator plus permissions on other objects that been granted to the creator with the grant option. In case of owner privileged stored procedures Informix will check all permissions on referenced objects at the time of execution.
2. DBA Privileged Procedures
These are stored procedures created by the user with ‘dba’ authority on the database or with a dba keyword in the create statement. In this case the stored procedure has ‘dba’ privileges for the duration of it’s execution. When dba privileged procedure is executed there is no need to check the permissions on referenced objects.
Benefits of Using Stored Procedures
For the purpose of this article it is useful to look at the benefits of using procedures from the perspective of the environment you will use to deploy your application. That is whether it is client/server or a single host type environment. Note that the benefits gained from using stored procedures in a single host type environment are also applicable to client/server.
Advantages in Client/Server Environment
1. Provide means for partitioning the application logic between the server and client. For example CPU intensive work such as calculations could be implemented in the stored procedures and the results returned back to the client for presentation.
2. Improve application performance by reducing network traffic. One of the biggest performance bottlenecks in the client /server environment is the network. With use of stored procedures you can substantially reduce network traffic. There are two reasons for this:
a. Instead of sending whole SQL statements only the stored procedure name and arguments have to be sent to the server.
b. In a situation where you have a transaction which consists of multiple SQL statements the intermediate results from the SQL have to be sent between the server and the client. If stored procedures are used then all transaction work can be done on the server and only the final result returned to the client.
3. Allow encapsulation and enforcement of business rules on the server. For example if business rules are controlled through stored procedures then they can be enforced regardless of what tool is used to update the database.
4. Improved application maintenance. If business rules and application logic are implemented in the stored procedures then any changes to business rules are localised. This means that application changes can be done on the server without a need to distribute them to all the clients.
5. Provide more robust transaction control. When using stored procedures transaction control can be implemented on the server instead of the client. If transactions are controlled on the client there exists a possibility of transactions being left open if the client crashes. Open transactions might prevent archiving of logical logs and lead to long transactions. This problem has been addressed in later versions of release 5 where Informix will terminate any open sessions without client connection.
6. Allow to extend the functionality of some 4GL development tools. Some of the Client/Server development tools are not very good in handling complex database interactions such as denormalised data.
7. Improve application security. In ODBC environments any application users can access the database using desktop tools. Using stored procedures you can eliminate the need to grant direct access to all tables and control it through the procedures.
8. Can be used to invoke Unix programs from the PC client on the server using SPL ‘SYSTEM’ command. For example a stored procedure can be used to send Unix mail or update files on the Unix host.
Advantages in Single Host Environment
Note that all the benefits of stored procedures in the single host environment are also applicable to client/server environment
1. Allow implementation of business rules and database integrity checking in the database by disallowing programs and users from accessing the tables directly.
2. Encourage code reuse. Commonly used functionality can be implemented in the stored procedures which can be reused by any programs.
3. Improve performance. When SQL is executed through 4GL or E/SQL, the SQL statement is sent through a pipe to the Informix engine and then parsed, optimised and security checked before it’s execution. With stored procedures the above work needs to done only at the time of it’s creation. Generally stored procedures with four or more SQL statements provide best improvements.
4. Improve application security. If databas